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 KingHave 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
>

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 KingHave 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','truncateo
nly')
go
dbcc shrinkfile('logical_ldf_name','truncateo
nly')
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
>

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

DBCC DBREindex

Attempting to schedule a job to run DBCC DBREindex for all
of the tables in a database. I'm rebuilding all indexes
for various tables - job example:
BCC DBREINDEX( unq_id )
DBCC DBREINDEX( user_adr )
DBCC DBREINDEX( user_group )
DBCC DBREINDEX( user_slice_approval )
DBCC DBREINDEX( userpriv )
DBCC DBREINDEX( wg )
DBCC DBREINDEX( wg_acc )
DBCC DBREINDEX( wg_dates )
DBCC DBREINDEX( WG_FTE )
DBCC is is return the following message:
Executed as user: NT AUTHORITY\SYSTEM. ...ontact your
system administrator. [SQLSTATE 01000] (Message 2528)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator. [SQLSTATE 01000]
(Message 2528) DBCC execution completed. . If DBCC
printed error messages, contact your system administrator.
[SQLSTATE 01000] (Message 2528) DBCC execu... The step
failed.
What am I doing wrong?Tell us what do you see in errorlog?
>--Original Message--
>Attempting to schedule a job to run DBCC DBREindex for
all
>of the tables in a database. I'm rebuilding all indexes
>for various tables - job example:
>BCC DBREINDEX( unq_id )
>DBCC DBREINDEX( user_adr )
>DBCC DBREINDEX( user_group )
>DBCC DBREINDEX( user_slice_approval )
>DBCC DBREINDEX( userpriv )
>DBCC DBREINDEX( wg )
>DBCC DBREINDEX( wg_acc )
>DBCC DBREINDEX( wg_dates )
>DBCC DBREINDEX( WG_FTE )
>DBCC is is return the following message:
>Executed as user: NT AUTHORITY\SYSTEM. ...ontact your
>system administrator. [SQLSTATE 01000] (Message 2528)
>DBCC execution completed. If DBCC printed error messages,
>contact your system administrator. [SQLSTATE 01000]
>(Message 2528) DBCC execution completed. . If DBCC
>printed error messages, contact your system
administrator.
>[SQLSTATE 01000] (Message 2528) DBCC execu... The step
>failed.
>What am I doing wrong?
>.
>|||If I look at the HISTORY for the JOB showing step details
this is what I see:
The job failed. The Job was invoked by User
LSMASTER\JBraga. The last step to run was step 4 (rebuild
index - index2.sql). The job was requested to start at
step 4 (rebuild index - index2.sql).
Executed as user: NT AUTHORITY\SYSTEM. ...ontact your
system administrator. [SQLSTATE 01000] (Message 2528)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator. [SQLSTATE 01000]
(Message 2528) DBCC execution completed. If DBCC printed
error messages, contact your system administrator.
[SQLSTATE 01000] (Message 2528) DBCC execution completed.
If DBCC printed error messages, contact your system
administrator. [SQLSTATE 01000] (Message 2528) DBCC
execution completed. If DBCC printed error messages,
contact your system administrator. [SQLSTATE 01000]
(Message 2528) DBCC execution completed. If DBCC printed
error messages, contact your system administrator.
[SQLSTATE 01000] (Message 2528) DBCC execution completed.
If DBCC printed error messages, contact your system
administrator. [SQLSTATE 01000] (Message 2528) DBCC
execution completed. If DBCC printed error messages,
contact your system administrator. [SQLSTATE 01000]
(Message 2528) DBCC execu... The step failed.
Thank you,
Joe
>--Original Message--
>Tell us what do you see in errorlog?
>>--Original Message--
>>Attempting to schedule a job to run DBCC DBREindex for
>all
>>of the tables in a database. I'm rebuilding all
indexes
>>for various tables - job example:
>>BCC DBREINDEX( unq_id )
>>DBCC DBREINDEX( user_adr )
>>DBCC DBREINDEX( user_group )
>>DBCC DBREINDEX( user_slice_approval )
>>DBCC DBREINDEX( userpriv )
>>DBCC DBREINDEX( wg )
>>DBCC DBREINDEX( wg_acc )
>>DBCC DBREINDEX( wg_dates )
>>DBCC DBREINDEX( WG_FTE )
>>DBCC is is return the following message:
>>Executed as user: NT AUTHORITY\SYSTEM. ...ontact your
>>system administrator. [SQLSTATE 01000] (Message 2528)
>>DBCC execution completed. If DBCC printed error
messages,
>>contact your system administrator. [SQLSTATE 01000]
>>(Message 2528) DBCC execution completed. . If DBCC
>>printed error messages, contact your system
>administrator.
>>[SQLSTATE 01000] (Message 2528) DBCC execu... The step
>>failed.
>>What am I doing wrong?
>>.
>.
>|||These are all informational messages. Nothing wrong. Add WITH NO_INFOMESSAGES and I think you get
rid of them.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"jbraga" <jbraga@.lifespan.org> wrote in message news:0c2601c3af7e$e8e94e50$a501280a@.phx.gbl...
> Attempting to schedule a job to run DBCC DBREindex for all
> of the tables in a database. I'm rebuilding all indexes
> for various tables - job example:
> BCC DBREINDEX( unq_id )
> DBCC DBREINDEX( user_adr )
> DBCC DBREINDEX( user_group )
> DBCC DBREINDEX( user_slice_approval )
> DBCC DBREINDEX( userpriv )
> DBCC DBREINDEX( wg )
> DBCC DBREINDEX( wg_acc )
> DBCC DBREINDEX( wg_dates )
> DBCC DBREINDEX( WG_FTE )
> DBCC is is return the following message:
> Executed as user: NT AUTHORITY\SYSTEM. ...ontact your
> system administrator. [SQLSTATE 01000] (Message 2528)
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator. [SQLSTATE 01000]
> (Message 2528) DBCC execution completed. . If DBCC
> printed error messages, contact your system administrator.
> [SQLSTATE 01000] (Message 2528) DBCC execu... The step
> failed.
> What am I doing wrong?
>