Shrinking the DATA File - Too Much Space is Allocated 1

[JohnBates]

Hi experts,

This DB's size is 9,377 MB
Space Available is 8,395 MB

T-Log is 1MB
Simple Recovery model
Auto grow by 10 percent

SQL Server 2000 SP3a

There is only 1GB of data, so I need to free-up some disk space back to the operating system.

When I ran this in Query Analyzer, I cancelled it after 25 minutes (there was nothing else running on the server at this time):

DBCC SHRINKFILE (DBA_Data, 4000)

By not specifying NOTRUNCATE, I want all freed space to be returned to the operating system.

I guess I have a couple of questions:
1) is this the correct way to reduce the size of the data file?
2) About how long should it take to only free-up approx 4 GB of space ?

Thanks, John

 

[JayKusch]

What was the original size of the DB when it was created. You are not permitted to shrink the DB below its initial build size. BUT ... I figure you would get a message on you command in QA though!

Thanks

J. Kusch

 

[JayKusch]

You may want to go into Enterprise Manager and right click on the DB and choose "All Tasks".."Shrink DB". This will only take a few seconds to do. Then you can kick you DB out by 200-500 MBs until it is at 4GB. To do this ... right click on the DB and choose "Properties". Go to the "data files" table and under the "space allocated" edit the number and add the 200-500 MB. Click ok ... see if how long it takes to complete and adjust the next addition of MBs as needed.

Thanks

J. Kusch

 

[JohnBates]

thanks JayKusch,

I very well may have specified a size of 10GB when I created this db.

But it has allocated way more space than is needed for this db and I need it elsewhere.

Of course SHRINKDATABASE was the very first thing I tried.

Just now I reran the
DBCC SHRINKFILE (DBA_Data, 4000, TRUNCATEONLY)

(notice I used the TRUNCATEONLY option this time)

It completed in 30 secs - but did not reduce the size of the db, no change. Wow - looks like I may have to backup the db, delete it, then restore to a new, smaller db.
Very frustrating.

John

 

[JayKusch]

It will take some time for SQL Server to release the unused space to the OS again. Give it time ... I would seriously wait 30-60 minutes to let SS do it job.

Thanks

J. Kusch

 

[JayKusch]

As to your backup and restore plan ... won't work! Even though you create a smaller initial DB to restore to, you backup of the 9GB+ DB will allocate that space out again and you are back where you started from. A backup, backs up all the allocated and UNallocated space of the DB.

Thanks

J. Kusch

 

[JohnBates]

Jay,

do you think I should use my original command (without TRUNCATEONLY) when I have an hour or 2 to spare?

Thanks, John

 

[JayKusch]

If you ran the command already. I am betting you will see the DB gradually release the unused space. So re-issuing it when you have a spare hour or 2 will not make a difference.

Thanks

J. Kusch