DBCC ShrinkDatabase on large DB

Hi,
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian King
Have you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateonly')
go
dbcc shrinkfile('logical_ldf_name','truncateonly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
sql