DBCC shrinkdatabase

16 Nov 2006 12:59 PM

news.microsoft.com

Hi All
I am currently in a catch 22 situation. I need to shrink my database before
i can back it up ( as the time it takes to back up now has gone over the
time allocate to the backup process) .
Is it safe to run the DBCC SHRINKDATABASE (DBName,0,TRUNCATEONLY) command on
a production database ( 450GB with 9000 + connections ) ?
Any info with this regard will be highly appreciated.
Thanks
Elrond

16 Nov 2006 3:36 PM

Kevin3NF

The backup process only backs up the data...not the empty space in the MDF
file.

Shrink only removes the empty space, so I don't think it will gain you
anything.

Unless I am incrorect in the above, you may want to investigate some 3rd
party utilities that compress the backup as it is done, which saves time and
drive space.

Red gate makes SQL Backup ($295/server)
Quest sells SQL Litespeed (price based on version/processors, but higher
than red gate)

Both are good products.

--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm

Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com

Show quote

"news.microsoft.com" <some***@microsoft.com> wrote in message
news:OxE4j8XCHHA.4832@TK2MSFTNGP06.phx.gbl...
> Hi All
> I am currently in a catch 22 situation. I need to shrink my database
> before i can back it up ( as the time it takes to back up now has gone
> over the time allocate to the backup process) .
> Is it safe to run the DBCC SHRINKDATABASE (DBName,0,TRUNCATEONLY) command
> on a production database ( 450GB with 9000 + connections ) ?
> Any info with this regard will be highly appreciated.
> Thanks
> Elrond
>

17 Nov 2006 12:55 AM

Andrew J. Kelly

Kevin is right on the money. The time to backup a db is not affected by the
amount of free space only the data. I would use one of the 3rd party tools
to compress the backups on the fly. You may also want to look at using some
sort of hardware backups using the SAN or filegroup backups.

--
Andrew J. Kelly SQL MVP

Show quote

"news.microsoft.com" <some***@microsoft.com> wrote in message
news:OxE4j8XCHHA.4832@TK2MSFTNGP06.phx.gbl...
> Hi All
> I am currently in a catch 22 situation. I need to shrink my database
> before i can back it up ( as the time it takes to back up now has gone
> over the time allocate to the backup process) .
> Is it safe to run the DBCC SHRINKDATABASE (DBName,0,TRUNCATEONLY) command
> on a production database ( 450GB with 9000 + connections ) ?
> Any info with this regard will be highly appreciated.
> Thanks
> Elrond
>