Tuesday, March 27, 2012

DBCC SHRINKDATABASE

We previously had a problem (due to the fact the previous DBA made every
index a non-clustered instead of clustered) that caused our database file
size to grow out of control. We had databases that should have been around
10gb growing to well over 50gb. I made everything a clustered index and
reindex every table and the database size dropped to 30gb but with roughly
20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command t
o
reclain that free space and on some database it's dropping the size from 30g
b
down to 10gb with a few gb of free space which is great, but on some it's no
t
reclaiming that space. I've tried using the command with the truncate only
option, specifying like 5 percent free space and I just can't find why on
some databases it will reclaim that space (more success witht he truncate
only option than the others) and other databases it's retaining like 80% of
the database size as free space. I'm using the update usage command as well
to get things in line. Any help would be appreciated. Thanks.Hi
Try using DBCC SHRINKFILE instead
http://msdn.microsoft.com/library/d...
b51.asp
You may also want to check out other posts where SHRINKDATABASE has not
changed the size such as http://tinyurl.com/uz2om
John
"brogers5884" wrote:

> We previously had a problem (due to the fact the previous DBA made every
> index a non-clustered instead of clustered) that caused our database file
> size to grow out of control. We had databases that should have been aroun
d
> 10gb growing to well over 50gb. I made everything a clustered index and
> reindex every table and the database size dropped to 30gb but with roughly
> 20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command
to
> reclain that free space and on some database it's dropping the size from 3
0gb
> down to 10gb with a few gb of free space which is great, but on some it's
not
> reclaiming that space. I've tried using the command with the truncate onl
y
> option, specifying like 5 percent free space and I just can't find why on
> some databases it will reclaim that space (more success witht he truncate
> only option than the others) and other databases it's retaining like 80% o
f
> the database size as free space. I'm using the update usage command as we
ll
> to get things in line. Any help would be appreciated. Thanks.

No comments:

Post a Comment