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

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
:) SheetuFirst, 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
>|||Sheetu,
You can use DBCC SHRINKDATABASE or SHRINKFILE to shrink the physical files.
To actually get the space back you need to avoid the TruncateOnly which can
only truncate back to the last extent in use in the file. So, 1 extent in
use down at the 26.95 GB mark will limit the shrinkage.
Shrinking is a heavy process, since pages and extents must be moved to free
up the end of the files so that they can be returned to the Operating
System. This should be done rarely and only if the space is truly unneeded
in the database. (Otherwise it will all get reexpaned into with the
overhead involved in that.)
Russell Fields
"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
>|||> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all
of
> the clustered indexes only
Ah, you must have indexes on all of your tables. ;-)
I maintain systems where this is not true (and I do not have the power to
change it), so I build this based on a query for tables that have indexes.
Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
A|||Adam,
Thanks for your remedy.
I am afraid to go ahead as it will rebuild the indexes as well as defragging all the clusttered. Is there any risk to do so or any other way to do.
:) Sheetu
"Adam Machanic" wrote:
> 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
> >
>
>|||"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:C4C33EA0-863A-4D88-B9FB-A777890648FE@.microsoft.com...
> Adam,
> Thanks for your remedy.
> I am afraid to go ahead as it will rebuild the indexes as well as
defragging all the clusttered. Is there any risk to do so or any other way
to do.
>
First of all, you shouldn't do both. Rebuilding the indexes will defrag
them. Second, you need to know that the rebuild will lock the tables,
whereas the defrag will allow queries to keep happening. So if you're going
to be doing this during business hours while users are actively querying (or
if you have a database with 24/7 uptime), you need to use defrag only.
That's the only risk I can think of. I have never seen data corruption
or anything of that nature as a result of index upkeep.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u20kiKEYEHA.1048@.tk2msftngp13.phx.gbl...
> > sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag
all
> of
> > the clustered indexes only
> Ah, you must have indexes on all of your tables. ;-)
I'm working on it, but unfortunately the previous person working on the
databases apparently didn't like indexes (or those pesky constraint things!)
> I maintain systems where this is not true (and I do not have the power to
> change it), so I build this based on a query for tables that have indexes.
> Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
I've been meaning to write a query like that but for the moment I just
live with the error messages... I figured the OP could live with them too ;)
Do you mind posting your query?
... By the way, this is the second time I've had to post this message; I
seem to be dropping a lot of posts in these newsgroups recently. Any ideas
on how to diagnose and/or fix the problem?