September 2, 2009 at 7:59 am
I have a database on a server that is low on disk space. It is a CF_Variables database where the mdf file is out of control (size wise). If I truncate tables in that database, will that automatically decrease the size of the MDF file? Or is there something I need to do afterwards?
Thank you.
Rog
September 2, 2009 at 9:06 am
Roger Abram (9/2/2009)
If I truncate tables in that database, will that automatically decrease the size of the MDF file?
No
Or is there something I need to do afterwards?
Shrink the data file, then rebuild all the indexes afterwards to fix the fragmentation that the shrink caused. Be aware that the rebuild may grow the file again.
When you say the mdf is growing out of control, is the amount of data growing? Do you foresee that storage requirement increasing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2009 at 9:15 am
Thank you. What would the best command to shrink the database?
Looks like the data needs to cleared out from time to time.. it's tracking user action on the web.
September 2, 2009 at 9:29 am
Roger Abram (9/2/2009)
Thank you. What would the best command to shrink the database?
DBCC SHRINKFILE
look up the full syntax in Books Online
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2009 at 9:40 am
That said, shrinking the DB on a regular basis is not a good idea. Once you've got the size under control rather don't shrink the file regularly, just free up space and then allow the space to be reused.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply