Problems with DBCC SHRINKFILE

Hello,
we are using successful MS SQL Server 2000. But the database is very very
huge, about 239 GB. The last 5 days im trying to shrink the database, but i
have no success. After cleaning and deleting lot's of rows, i startet
severall times DBCC SHRINKFILE. The only result is that i have 49 GB
available space. But i need this free space physically on my disk partition.
After 24 hours running DBCC SHRINKFILE sql server abortet the operation with
a timeout. Any idea how i can shrink my database files? I need the physical
space on the disk because my log file cannot grow.
I hope anybody can help me.
Regards,
Djordje Gladovic
Software-Engineering
WK EDV GmbH
D-86633 Neuburg a. d. Donau
Phone +49 (0)8431 6704-15
Facsimile +49 (0)8431 6704-22
mailto:gladovic.d@.wk-edv.de
http://www.wk-edv.deIf your database is huge and you specify a large amount for the shrink
operation it can take a very long time. Try specifying a smaller amount so
it can finish in a reason amount of time and do it in smaller chunks. If
your log file is too large then you might want to make sure you don't have
any long running open transactions with DBCC OPENTRAN. If you do the log
file can not be truncated. Are you issuing regular Log backups?
--
Andrew J. Kelly SQL MVP
"Djorde Gladovic WK-EDV GmbH" <gladovic.d@.wk-edv.de> wrote in message
news:OVn$5xaaEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Hello,
> we are using successful MS SQL Server 2000. But the database is very very
> huge, about 239 GB. The last 5 days im trying to shrink the database, but
i
> have no success. After cleaning and deleting lot's of rows, i startet
> severall times DBCC SHRINKFILE. The only result is that i have 49 GB
> available space. But i need this free space physically on my disk
partition.
> After 24 hours running DBCC SHRINKFILE sql server abortet the operation
with
> a timeout. Any idea how i can shrink my database files? I need the
physical
> space on the disk because my log file cannot grow.
> I hope anybody can help me.
> Regards,
> Djordje Gladovic
> Software-Engineering
> WK EDV GmbH
> D-86633 Neuburg a. d. Donau
> Phone +49 (0)8431 6704-15
> Facsimile +49 (0)8431 6704-22
> mailto:gladovic.d@.wk-edv.de
> http://www.wk-edv.de
>|||Hi,
In addition to Andrew's and Satyas post, Since the database is huge, you
could set the database to single user before shrinking the files.
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 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
"Djorde Gladovic WK-EDV GmbH" <gladovic.d@.wk-edv.de> wrote in message
news:OVn$5xaaEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Hello,
> we are using successful MS SQL Server 2000. But the database is very very
> huge, about 239 GB. The last 5 days im trying to shrink the database, but
i
> have no success. After cleaning and deleting lot's of rows, i startet
> severall times DBCC SHRINKFILE. The only result is that i have 49 GB
> available space. But i need this free space physically on my disk
partition.
> After 24 hours running DBCC SHRINKFILE sql server abortet the operation
with
> a timeout. Any idea how i can shrink my database files? I need the
physical
> space on the disk because my log file cannot grow.
> I hope anybody can help me.
> Regards,
> Djordje Gladovic
> Software-Engineering
> WK EDV GmbH
> D-86633 Neuburg a. d. Donau
> Phone +49 (0)8431 6704-15
> Facsimile +49 (0)8431 6704-22
> mailto:gladovic.d@.wk-edv.de
> http://www.wk-edv.de
>|||Hari,
Why do you keep insisting people place the db in single user mode? Other
than the fact you won't have blocking issues is there something I am
missing?
--
Andrew J. Kelly SQL MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OQCxf4baEHA.3996@.TK2MSFTNGP12.phx.gbl...
> Hi,
> In addition to Andrew's and Satyas post, Since the database is huge, you
> could set the database to single user before shrinking the files.
> 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 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
>
> "Djorde Gladovic WK-EDV GmbH" <gladovic.d@.wk-edv.de> wrote in message
> news:OVn$5xaaEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > we are using successful MS SQL Server 2000. But the database is very
very
> > huge, about 239 GB. The last 5 days im trying to shrink the database,
but
> i
> > have no success. After cleaning and deleting lot's of rows, i startet
> > severall times DBCC SHRINKFILE. The only result is that i have 49 GB
> > available space. But i need this free space physically on my disk
> partition.
> > After 24 hours running DBCC SHRINKFILE sql server abortet the operation
> with
> > a timeout. Any idea how i can shrink my database files? I need the
> physical
> > space on the disk because my log file cannot grow.
> >
> > I hope anybody can help me.
> >
> > Regards,
> >
> > Djordje Gladovic
> > Software-Engineering
> >
> > WK EDV GmbH
> > D-86633 Neuburg a. d. Donau
> > Phone +49 (0)8431 6704-15
> > Facsimile +49 (0)8431 6704-22
> > mailto:gladovic.d@.wk-edv.de
> > http://www.wk-edv.de
> >
> >
>|||What has single_user mode to do with anything? It may reduce the time shrink
takes to run by a small amount but at the expense of having the entire
database offline for the duration of the shrink.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OQCxf4baEHA.3996@.TK2MSFTNGP12.phx.gbl...
> Hi,
> In addition to Andrew's and Satyas post, Since the database is huge, you
> could set the database to single user before shrinking the files.
> 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 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
>
> "Djorde Gladovic WK-EDV GmbH" <gladovic.d@.wk-edv.de> wrote in message
> news:OVn$5xaaEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > we are using successful MS SQL Server 2000. But the database is very
very
> > huge, about 239 GB. The last 5 days im trying to shrink the database,
but
> i
> > have no success. After cleaning and deleting lot's of rows, i startet
> > severall times DBCC SHRINKFILE. The only result is that i have 49 GB
> > available space. But i need this free space physically on my disk
> partition.
> > After 24 hours running DBCC SHRINKFILE sql server abortet the operation
> with
> > a timeout. Any idea how i can shrink my database files? I need the
> physical
> > space on the disk because my log file cannot grow.
> >
> > I hope anybody can help me.
> >
> > Regards,
> >
> > Djordje Gladovic
> > Software-Engineering
> >
> > WK EDV GmbH
> > D-86633 Neuburg a. d. Donau
> > Phone +49 (0)8431 6704-15
> > Facsimile +49 (0)8431 6704-22
> > mailto:gladovic.d@.wk-edv.de
> > http://www.wk-edv.de
> >
> >
>|||Hi Paul / Andrew,
(Correct me if I am doing anything wrong)
I faced 2 issues while trying to shrink a 112 GB database online:-
1. Users experiencing slow response
2. Due to the open transactions the file was not getting shrink. Due to this
I was forced
to execute the DBCC SHRINKFILE command 2 times.
To avoid this what I used to do is:-
1. Select a day when user access is less ( Sunday night)
2. Set the database to single user
3. Backup the transaction log
4. Shrink the file
By doing this the file shrink properly.
Note:
For smaller databases I have never faced any issues.
--
Thanks
Hari
MCDBA
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ucbhxJfaEHA.2972@.TK2MSFTNGP12.phx.gbl...
> What has single_user mode to do with anything? It may reduce the time
shrink
> takes to run by a small amount but at the expense of having the entire
> database offline for the duration of the shrink.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OQCxf4baEHA.3996@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > In addition to Andrew's and Satyas post, Since the database is huge, you
> > could set the database to single user before shrinking the files.
> >
> > 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 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
> >
> >
> > "Djorde Gladovic WK-EDV GmbH" <gladovic.d@.wk-edv.de> wrote in message
> > news:OVn$5xaaEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > > Hello,
> > >
> > > we are using successful MS SQL Server 2000. But the database is very
> very
> > > huge, about 239 GB. The last 5 days im trying to shrink the database,
> but
> > i
> > > have no success. After cleaning and deleting lot's of rows, i startet
> > > severall times DBCC SHRINKFILE. The only result is that i have 49 GB
> > > available space. But i need this free space physically on my disk
> > partition.
> > > After 24 hours running DBCC SHRINKFILE sql server abortet the
operation
> > with
> > > a timeout. Any idea how i can shrink my database files? I need the
> > physical
> > > space on the disk because my log file cannot grow.
> > >
> > > I hope anybody can help me.
> > >
> > > Regards,
> > >
> > > Djordje Gladovic
> > > Software-Engineering
> > >
> > > WK EDV GmbH
> > > D-86633 Neuburg a. d. Donau
> > > Phone +49 (0)8431 6704-15
> > > Facsimile +49 (0)8431 6704-22
> > > mailto:gladovic.d@.wk-edv.de
> > > http://www.wk-edv.de
> > >
> > >
> >
> >
>|||Sure users can get a slow response if the hardware is mediocre since there
is a lot of activity going on during a shrink. But at least they can still
work. If you have the opportunity to do this when no users are in the
system then there should be no blocking or open transactions anyway. If the
open transactions are open so long that they interfere with the shrink file
you have more serious problems than shrinking the database.
--
Andrew J. Kelly SQL MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eTcBIuhaEHA.3356@.tk2msftngp13.phx.gbl...
> Hi Paul / Andrew,
> (Correct me if I am doing anything wrong)
> I faced 2 issues while trying to shrink a 112 GB database online:-
> 1. Users experiencing slow response
> 2. Due to the open transactions the file was not getting shrink. Due to
this
> I was forced
> to execute the DBCC SHRINKFILE command 2 times.
> To avoid this what I used to do is:-
> 1. Select a day when user access is less ( Sunday night)
> 2. Set the database to single user
> 3. Backup the transaction log
> 4. Shrink the file
> By doing this the file shrink properly.
> Note:
> For smaller databases I have never faced any issues.
> --
> Thanks
> Hari
> MCDBA
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:ucbhxJfaEHA.2972@.TK2MSFTNGP12.phx.gbl...
> > What has single_user mode to do with anything? It may reduce the time
> shrink
> > takes to run by a small amount but at the expense of having the entire
> > database offline for the duration of the shrink.
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:OQCxf4baEHA.3996@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > >
> > > In addition to Andrew's and Satyas post, Since the database is huge,
you
> > > could set the database to single user before shrinking the files.
> > >
> > > 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 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
> > >
> > >
> > > "Djorde Gladovic WK-EDV GmbH" <gladovic.d@.wk-edv.de> wrote in message
> > > news:OVn$5xaaEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > > > Hello,
> > > >
> > > > we are using successful MS SQL Server 2000. But the database is very
> > very
> > > > huge, about 239 GB. The last 5 days im trying to shrink the
database,
> > but
> > > i
> > > > have no success. After cleaning and deleting lot's of rows, i
startet
> > > > severall times DBCC SHRINKFILE. The only result is that i have 49 GB
> > > > available space. But i need this free space physically on my disk
> > > partition.
> > > > After 24 hours running DBCC SHRINKFILE sql server abortet the
> operation
> > > with
> > > > a timeout. Any idea how i can shrink my database files? I need the
> > > physical
> > > > space on the disk because my log file cannot grow.
> > > >
> > > > I hope anybody can help me.
> > > >
> > > > Regards,
> > > >
> > > > Djordje Gladovic
> > > > Software-Engineering
> > > >
> > > > WK EDV GmbH
> > > > D-86633 Neuburg a. d. Donau
> > > > Phone +49 (0)8431 6704-15
> > > > Facsimile +49 (0)8431 6704-22
> > > > mailto:gladovic.d@.wk-edv.de
> > > > http://www.wk-edv.de
> > > >
> > > >
> > >
> > >
> >
> >
>|||I agree with Andrew. The most drop in transaction throughput you should see
while doing a shrink on adequate hardware is about 20%.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uST5OYmaEHA.3112@.tk2msftngp13.phx.gbl...
> Sure users can get a slow response if the hardware is mediocre since there
> is a lot of activity going on during a shrink. But at least they can
still
> work. If you have the opportunity to do this when no users are in the
> system then there should be no blocking or open transactions anyway. If
the
> open transactions are open so long that they interfere with the shrink
file
> you have more serious problems than shrinking the database.
> --
> Andrew J. Kelly SQL MVP
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eTcBIuhaEHA.3356@.tk2msftngp13.phx.gbl...
> > Hi Paul / Andrew,
> >
> > (Correct me if I am doing anything wrong)
> >
> > I faced 2 issues while trying to shrink a 112 GB database online:-
> >
> > 1. Users experiencing slow response
> > 2. Due to the open transactions the file was not getting shrink. Due to
> this
> > I was forced
> > to execute the DBCC SHRINKFILE command 2 times.
> >
> > To avoid this what I used to do is:-
> >
> > 1. Select a day when user access is less ( Sunday night)
> > 2. Set the database to single user
> > 3. Backup the transaction log
> > 4. Shrink the file
> >
> > By doing this the file shrink properly.
> >
> > Note:
> >
> > For smaller databases I have never faced any issues.
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> >
> > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > news:ucbhxJfaEHA.2972@.TK2MSFTNGP12.phx.gbl...
> > > What has single_user mode to do with anything? It may reduce the time
> > shrink
> > > takes to run by a small amount but at the expense of having the entire
> > > database offline for the duration of the shrink.
> > >
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > > news:OQCxf4baEHA.3996@.TK2MSFTNGP12.phx.gbl...
> > > > Hi,
> > > >
> > > > In addition to Andrew's and Satyas post, Since the database is huge,
> you
> > > > could set the database to single user before shrinking the files.
> > > >
> > > > 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 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
> > > >
> > > >
> > > > "Djorde Gladovic WK-EDV GmbH" <gladovic.d@.wk-edv.de> wrote in
message
> > > > news:OVn$5xaaEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > > > > Hello,
> > > > >
> > > > > we are using successful MS SQL Server 2000. But the database is
very
> > > very
> > > > > huge, about 239 GB. The last 5 days im trying to shrink the
> database,
> > > but
> > > > i
> > > > > have no success. After cleaning and deleting lot's of rows, i
> startet
> > > > > severall times DBCC SHRINKFILE. The only result is that i have 49
GB
> > > > > available space. But i need this free space physically on my disk
> > > > partition.
> > > > > After 24 hours running DBCC SHRINKFILE sql server abortet the
> > operation
> > > > with
> > > > > a timeout. Any idea how i can shrink my database files? I need the
> > > > physical
> > > > > space on the disk because my log file cannot grow.
> > > > >
> > > > > I hope anybody can help me.
> > > > >
> > > > > Regards,
> > > > >
> > > > > Djordje Gladovic
> > > > > Software-Engineering
> > > > >
> > > > > WK EDV GmbH
> > > > > D-86633 Neuburg a. d. Donau
> > > > > Phone +49 (0)8431 6704-15
> > > > > Facsimile +49 (0)8431 6704-22
> > > > > mailto:gladovic.d@.wk-edv.de
> > > > > http://www.wk-edv.de
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>