June 14, 2004 at 5:48 am
I have disk space concerns and have been running dbcc shrinkdatabase (dbname,5) to free up space.
Rather than do this regularly I used the maintenance plan wizard to create a scheduled job.
I picked 5% change free space and 5% for amount of free space left after shrink.
Ran the job created by the plan and my 2.4GB database grew to 3.9GB.
Can you help explain this please? I would of thought the wizard would create a shrinkdatabase command. (Using sql7)
Thanks.
June 14, 2004 at 7:41 am
What all is your maintenance plan doing? Is it also 'defragging' the indexes? This can cause the database to grow also (log space). Shrinking a database (or file) might not have immediate action. The empty space needs to be 'grouped' at the end of the log file space.
A=active I=inactive
Log files can look like this:
I I I A A I
Shrinking can only be done to the Inactive portion after the active portion. It can take a while to get it like this:
A A I I I I
Everything I have read and experienced is that Shrinking a database is not instantaneous.
Read up in the BOL on the transaction log and shrinking the logs and database files.
-SQLBill
June 23, 2004 at 7:30 am
I run a DBMaint plan also that shrinks the index freespace to 10% and shrinks the database to 20% freespace. After it ran I checked and there was 3gb free out of 8gb database (nearly 40% free).
I don't know what gives. Before we upgraded hardware, I would just turn on the AutoShrink option over a weekend and it would work OK.
With my 50gb database, I don't run a dbmaint plan, I just schedule a dbcc index rebuild.
Its seems that the overall freespace increases when there are more indexes.
I haven't found a better answer as yet.
June 25, 2004 at 4:01 am
If you are using SQL Server 7 then there was a "feature" with the SHRINK DATABASE or SHRINK DATAFILE that could cause the database to expand if you had TEXT, nTEXT or image data - it bit us hard.
Check out http://support.microsoft.com/default.aspx?scid=kb;en-us;308627&Product=sql
to see if it is relevant to your situation
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply