Re: How to reduce database size/ getting back unused space to OS

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 07/02/04 

Date: Fri, 2 Jul 2004 10:35:29 -0400

First, run sp_helpfile to get the file sizes.

If your data file is what's taking up the space, I usually like to do the
following things...

USE SMS_DB
GO

sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
tables being locked... otherwise:

sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
the clustered indexes only

--Then, shrink the file(s):

DBCC SHRINKFILE(1, 1, truncateonly)

...

If it's your log file(s)...

You can either backup the log, or if you don't mind losing it, run:

BACKUP LOG SMS_DB WITH TRUNCATE_ONLY

Then, you can run:

USE SMS_DB
GO

DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2

"Sheetu" <Sheetu@discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
>
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
>
> Thanks in advance
>
> :) Sheetu
>

Quantcast