September 22, 2005 at 11:26 am
We have SQL Server 2000 (SP 3). I am trying to find out the best method to automate the following Shrink Database Manual Process. To manually shrink a database, I right click on the Database, select "All Tasks" and select "Shrink Database." Then the Shrink Database Dialog displays. For "Maximum free space in files after shrinking" I enter 10% and I check "Move pages to beginning of file before shrinking." Then I click OK.
How may I automate this exact process? I believe a DBCC command would have to be used and then set up as a SQL Job. What is the exact DBCC Command and parameters to use to accomplish the manual process?
Thanks in advance, Kevin
September 22, 2005 at 1:03 pm
Look up DBCC SHRINKDATABASE and DBCC SHRINKFILE in the BOL.
Then use one of those commands (I prefer the shrinkfile) in a scheduled job.
-SQLBill
September 22, 2005 at 1:04 pm
I'll be curious to see how others respond to this. The short answer is, of course, yes, the job can be automated. You could set up a scheduled task that runs the following bit of T-SQL, for example:
EXEC sp_MSforeachdb @command1 = 'DBCC SHRINKDATABASE'
What I'm less sure about is if this is a good idea.
September 22, 2005 at 5:35 pm
Not a good idea, if you have the drive space, to constantly grow and shrink the database, as it leads to pretty evil os-level file fragmentation. Best practice is to set the size to a reasonable value and leave it there.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply