Database shrink is not changing file size??

[zypadman]

We have an SQL database that was 40 Gb...we remove one table that was 35GB. We were expecting to see the database size change from 40GB to 5GB. Then I ran "Shrink database" and thought that this would reflect the new file size but the DB is still 40GB? Is there something else I need to do to show the actual DB size? Any help would be great!!

Thanks!

 

[SQLSister]

First a database will never shrink below the intial size it was when you created it using the DBCC SHRINKDATBASE command. This could be your problem. If it is, you must shrink the individual files using DBCC SHRINKFILE.

"NOTHING is more important in a database than integrity." ESquared

 

[zypadman]

the initial size was around 3Gb so that does not seem to be the same issue...is there some maintenance or indexing that needs to be done to resolve the size of the DB?

Thanks!

 

[ptheriault]

You could try running DBCC UPDATEUSAGE.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[N2Code]

I have come across the same problem myself many times. I have found that if you back up the database first, then shrink it, it will reduce in size. It has something to do with the log file refusing to shrink unless there is a backup of it.

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.

 

[mrdenny]

You may also need to wait for SQL to "catch up". When SQL drops a table it doesn't 0 out the data. I beleive that it does it in the back ground. You may need to wait for this cleanup process to complete before you can shrink the file.

If you wait a day it should shrink.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLWilts]

Just another thought here - and I'm sure that you have considered this already, but how did you remove the table? Is the transaction log your real problem here, and not the DB file?

Just adding my proverbial sabot

 

[zypadman]

To remove the table I just right clicked and deleted table from within the management studio...is there a better way that I should have done this? Any other suggestions for getting the size to reflect the correct size of the DB?

 

[SQLWilts]

What is the physical size of the mdf file?
Have you tried the "dbcc updateusage" command above?
Try using this using EM (too many commands):
right click the DB, choose all tasks/shrink database then click the files button.
Leave the default radial button selected (compress pages then truncate free space) click OK. Once done, go back there again and select the third radial button (shrink file to) and choose a figure JUST GREATER THAN the minimum size. It will take a while to run - but do that and then post back.

 

[zypadman]

Size of the .MDF file is 40GB...but should be around 5GB now that the table has been deleted. What is the correct syntax for the dbcc updateusage command...it did not work for me?

 

[SQLWilts]

Try this

USE <PutyourDBNameHere>;
GO
DBCC UPDATEUSAGE ('YourDBName') WITH NO_INFOMSGS; 
GO

 

[SQLWilts]

Well, did it work?

 

[zypadman]

Yes...thank you for your help!!

 

[SQLBill]

You also need to remember, when you use Enterprise Manager or SSMS or some other GUI, you need to refresh your view.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875