Interrupt DBCC SHRINKDATABASE

BACKGROUND:
We have a large (330 GB) OLTP database. After archiving a
lot of records, we still have the same size of db and full
backups (BackupExec). The database has 2 filegroups, 24
data files, a log file over 4 logical drives. Log is
small, gets backup every hour and its file is no issue.
The data files range from 50 GB down to 70 MB since drives
and files were added as needed. Files auto grow, but do
not auto shrink. All maintenance is done manually via
Query Analyzer during scheduled times. A replicated
database with a subset of the information is used for OLTP
during these times.
Our goal was to shrink the database to recover OS space
and minimize backup time. This would be done with NO
LOAD. We scheduled 5 to 24 hours (based on some rough
estimates).
The SHRINKDATABASE has been running for 26 hours when load
was shifted back. It has now been running 31 hours. We
may need to cancel (using stop on Query Analyzer. However,
we hesitate to do so and risk corruption.
I found one reference in a PPT about shrink saying "may be
stopped without losing work". However, this is not a
mainstream URL:
http://www.microsoft.com/uk/partner/strategy/server_busines
s_agility/customers/business-
intelligence/downloads/RDBMSFeaturesandDataWarehousing.ppt
I would like an official, "Yes, if you started the DBCC
SHRINKDATABASE in the Query Analyzer, you can stop it by
using the Cancel Query. There will be now rollback time
or risk of corruption".
The documentation for these long running commands should
have a section on Cancellation.
Even an unofficial response is welcome.
Thanks,
SMI had what sounds like the same problem on SQL 2000. I calculated that the
database would take a week to come up. Not acceptable.
I had to use this solution 3 times: It worked just fine each time. I did
not get fired
It comes down to running this command;
UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='DBNAME'
then restart the server.
It set the database in emergency mode, which bypasses all recovery stuff
http://support.microsoft.com/default.aspx?scid=kb;EN-US;165918
"Spencer Morley" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote in
message news:023001c34f1d$32c14a40$a101280a@.phx.gbl...
> BACKGROUND:
> We have a large (330 GB) OLTP database. After archiving a
> lot of records, we still have the same size of db and full
> backups (BackupExec). The database has 2 filegroups, 24
> data files, a log file over 4 logical drives. Log is
> small, gets backup every hour and its file is no issue.
> The data files range from 50 GB down to 70 MB since drives
> and files were added as needed. Files auto grow, but do
> not auto shrink. All maintenance is done manually via
> Query Analyzer during scheduled times. A replicated
> database with a subset of the information is used for OLTP
> during these times.
> Our goal was to shrink the database to recover OS space
> and minimize backup time. This would be done with NO
> LOAD. We scheduled 5 to 24 hours (based on some rough
> estimates).
> The SHRINKDATABASE has been running for 26 hours when load
> was shifted back. It has now been running 31 hours. We
> may need to cancel (using stop on Query Analyzer. However,
> we hesitate to do so and risk corruption.
> I found one reference in a PPT about shrink saying "may be
> stopped without losing work". However, this is not a
> mainstream URL:
> http://www.microsoft.com/uk/partner/strategy/server_busines
> s_agility/customers/business-
> intelligence/downloads/RDBMSFeaturesandDataWarehousing.ppt
> I would like an official, "Yes, if you started the DBCC
> SHRINKDATABASE in the Query Analyzer, you can stop it by
> using the Cancel Query. There will be now rollback time
> or risk of corruption".
> The documentation for these long running commands should
> have a section on Cancellation.
> Even an unofficial response is welcome.
> Thanks,
> SM
>|||You absolutely can stop shrink at any time with no rollback cost and no risk
of corruption.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spencer Morley" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote in
message news:023001c34f1d$32c14a40$a101280a@.phx.gbl...
> BACKGROUND:
> We have a large (330 GB) OLTP database. After archiving a
> lot of records, we still have the same size of db and full
> backups (BackupExec). The database has 2 filegroups, 24
> data files, a log file over 4 logical drives. Log is
> small, gets backup every hour and its file is no issue.
> The data files range from 50 GB down to 70 MB since drives
> and files were added as needed. Files auto grow, but do
> not auto shrink. All maintenance is done manually via
> Query Analyzer during scheduled times. A replicated
> database with a subset of the information is used for OLTP
> during these times.
> Our goal was to shrink the database to recover OS space
> and minimize backup time. This would be done with NO
> LOAD. We scheduled 5 to 24 hours (based on some rough
> estimates).
> The SHRINKDATABASE has been running for 26 hours when load
> was shifted back. It has now been running 31 hours. We
> may need to cancel (using stop on Query Analyzer. However,
> we hesitate to do so and risk corruption.
> I found one reference in a PPT about shrink saying "may be
> stopped without losing work". However, this is not a
> mainstream URL:
> http://www.microsoft.com/uk/partner/strategy/server_busines
> s_agility/customers/business-
> intelligence/downloads/RDBMSFeaturesandDataWarehousing.ppt
> I would like an official, "Yes, if you started the DBCC
> SHRINKDATABASE in the Query Analyzer, you can stop it by
> using the Cancel Query. There will be now rollback time
> or risk of corruption".
> The documentation for these long running commands should
> have a section on Cancellation.
> Even an unofficial response is welcome.
> Thanks,
> SM
>|||Hi Spencer,
Firstly, you don't say how big the database is or what other load is running
on it. Shrink will wait for locks it cannot immediately acquire so there may
be something blocking it. This is especially likely with long-running,
pervasive update queries.
Shrink will always run quicker without a concurrent workload, so quiescing
the database (using single-user mode, for example) should decrease the
runtime.
The wizard option you mention is the same underlying code as shrinkdatabase,
and so can be run online and can also be safely interrupted with no risk of
corruption or losing already completed work.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote in
message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> While, we have been running the SHRINKDATABASE for 98
> hours now! Production does not seem to be affected.
> Assume this is because we have ample CPU, memory, and I/O?
> Should I expect this kind of run time on a database of
> this size?
> The process has shunk 6 files so far and we have gained
> about 2 GB per file. These are the files on the filegroup
> that is more static, so we expect better result on the
> other files. Also, these are the files on the RAID 5.
> Once the work on the current file is complete, we will
> move to those on the RAID 10, thus speedier?
> I plan to let this process run to completion, but we have
> a number of db's of simular size that will need to be
> shrunk as archiving is done.
> My question:
> Is there a way to force priority to a shrink (single user
> mode, etc) that will can be used while db is out of
> production that would be quicker?
> Also, if I use the "Database Maintenance Plan
> Wizzard", "Update Data Optimization Information"
> page, "Remove unused space from database files" option to
> shrink will it faster? Is the lower level process
> essentially the same as a SHRINKDATABSE? Can it be run
> while database is in use?
>|||yah but this database is in recovery mode, no?
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23Mi22eiUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> Hi Spencer,
> Firstly, you don't say how big the database is or what other load is
running
> on it. Shrink will wait for locks it cannot immediately acquire so there
may
> be something blocking it. This is especially likely with long-running,
> pervasive update queries.
> Shrink will always run quicker without a concurrent workload, so quiescing
> the database (using single-user mode, for example) should decrease the
> runtime.
> The wizard option you mention is the same underlying code as
shrinkdatabase,
> and so can be run online and can also be safely interrupted with no risk
of
> corruption or losing already completed work.
> Regards,
> Paul.
> --
> Paul Randal
> DBCC Technical Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote
in
> message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> > While, we have been running the SHRINKDATABASE for 98
> > hours now! Production does not seem to be affected.
> > Assume this is because we have ample CPU, memory, and I/O?
> >
> > Should I expect this kind of run time on a database of
> > this size?
> >
> > The process has shunk 6 files so far and we have gained
> > about 2 GB per file. These are the files on the filegroup
> > that is more static, so we expect better result on the
> > other files. Also, these are the files on the RAID 5.
> > Once the work on the current file is complete, we will
> > move to those on the RAID 10, thus speedier?
> >
> > I plan to let this process run to completion, but we have
> > a number of db's of simular size that will need to be
> > shrunk as archiving is done.
> >
> > My question:
> > Is there a way to force priority to a shrink (single user
> > mode, etc) that will can be used while db is out of
> > production that would be quicker?
> >
> > Also, if I use the "Database Maintenance Plan
> > Wizzard", "Update Data Optimization Information"
> > page, "Remove unused space from database files" option to
> > shrink will it faster? Is the lower level process
> > essentially the same as a SHRINKDATABSE? Can it be run
> > while database is in use?
> >
> >
>|||I don't see where recovery mode is mentioned in the original post..
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roy in (H.E.Double Toothpicks)" <Roy@.DoubleToothpicks.com> wrote in message
news:O0Pn1vnUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> yah but this database is in recovery mode, no?
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23Mi22eiUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> > Hi Spencer,
> >
> > Firstly, you don't say how big the database is or what other load is
> running
> > on it. Shrink will wait for locks it cannot immediately acquire so there
> may
> > be something blocking it. This is especially likely with long-running,
> > pervasive update queries.
> >
> > Shrink will always run quicker without a concurrent workload, so
quiescing
> > the database (using single-user mode, for example) should decrease the
> > runtime.
> >
> > The wizard option you mention is the same underlying code as
> shrinkdatabase,
> > and so can be run online and can also be safely interrupted with no risk
> of
> > corruption or losing already completed work.
> >
> > Regards,
> >
> > Paul.
> >
> > --
> > Paul Randal
> > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > "Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote
> in
> > message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> > > While, we have been running the SHRINKDATABASE for 98
> > > hours now! Production does not seem to be affected.
> > > Assume this is because we have ample CPU, memory, and I/O?
> > >
> > > Should I expect this kind of run time on a database of
> > > this size?
> > >
> > > The process has shunk 6 files so far and we have gained
> > > about 2 GB per file. These are the files on the filegroup
> > > that is more static, so we expect better result on the
> > > other files. Also, these are the files on the RAID 5.
> > > Once the work on the current file is complete, we will
> > > move to those on the RAID 10, thus speedier?
> > >
> > > I plan to let this process run to completion, but we have
> > > a number of db's of simular size that will need to be
> > > shrunk as archiving is done.
> > >
> > > My question:
> > > Is there a way to force priority to a shrink (single user
> > > mode, etc) that will can be used while db is out of
> > > production that would be quicker?
> > >
> > > Also, if I use the "Database Maintenance Plan
> > > Wizzard", "Update Data Optimization Information"
> > > page, "Remove unused space from database files" option to
> > > shrink will it faster? Is the lower level process
> > > essentially the same as a SHRINKDATABSE? Can it be run
> > > while database is in use?
> > >
> > >
> >
> >
>|||true, i inferred that.
Still, I guess that it is in recovery mode, although the guy didn't notice
or say that.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:Os8udJsUDHA.1004@.TK2MSFTNGP12.phx.gbl...
> I don't see where recovery mode is mentioned in the original post..
> --
> Paul Randal
> DBCC Technical Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Roy in (H.E.Double Toothpicks)" <Roy@.DoubleToothpicks.com> wrote in
message
> news:O0Pn1vnUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> > yah but this database is in recovery mode, no?
> >
> > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > news:%23Mi22eiUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> > > Hi Spencer,
> > >
> > > Firstly, you don't say how big the database is or what other load is
> > running
> > > on it. Shrink will wait for locks it cannot immediately acquire so
there
> > may
> > > be something blocking it. This is especially likely with long-running,
> > > pervasive update queries.
> > >
> > > Shrink will always run quicker without a concurrent workload, so
> quiescing
> > > the database (using single-user mode, for example) should decrease the
> > > runtime.
> > >
> > > The wizard option you mention is the same underlying code as
> > shrinkdatabase,
> > > and so can be run online and can also be safely interrupted with no
risk
> > of
> > > corruption or losing already completed work.
> > >
> > > Regards,
> > >
> > > Paul.
> > >
> > > --
> > > Paul Randal
> > > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > "Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net>
wrote
> > in
> > > message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> > > > While, we have been running the SHRINKDATABASE for 98
> > > > hours now! Production does not seem to be affected.
> > > > Assume this is because we have ample CPU, memory, and I/O?
> > > >
> > > > Should I expect this kind of run time on a database of
> > > > this size?
> > > >
> > > > The process has shunk 6 files so far and we have gained
> > > > about 2 GB per file. These are the files on the filegroup
> > > > that is more static, so we expect better result on the
> > > > other files. Also, these are the files on the RAID 5.
> > > > Once the work on the current file is complete, we will
> > > > move to those on the RAID 10, thus speedier?
> > > >
> > > > I plan to let this process run to completion, but we have
> > > > a number of db's of simular size that will need to be
> > > > shrunk as archiving is done.
> > > >
> > > > My question:
> > > > Is there a way to force priority to a shrink (single user
> > > > mode, etc) that will can be used while db is out of
> > > > production that would be quicker?
> > > >
> > > > Also, if I use the "Database Maintenance Plan
> > > > Wizzard", "Update Data Optimization Information"
> > > > page, "Remove unused space from database files" option to
> > > > shrink will it faster? Is the lower level process
> > > > essentially the same as a SHRINKDATABSE? Can it be run
> > > > while database is in use?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Why would the db be in recovery mode?
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roy in (H.E.Double Toothpicks)" <Roy@.DoubleToothpicks.com> wrote in message
news:#29aFlvUDHA.2312@.TK2MSFTNGP12.phx.gbl...
> true, i inferred that.
> Still, I guess that it is in recovery mode, although the guy didn't
notice
> or say that.
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:Os8udJsUDHA.1004@.TK2MSFTNGP12.phx.gbl...
> > I don't see where recovery mode is mentioned in the original post..
> >
> > --
> > Paul Randal
> > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Roy in (H.E.Double Toothpicks)" <Roy@.DoubleToothpicks.com> wrote in
> message
> > news:O0Pn1vnUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> > > yah but this database is in recovery mode, no?
> > >
> > > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > > news:%23Mi22eiUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> > > > Hi Spencer,
> > > >
> > > > Firstly, you don't say how big the database is or what other load is
> > > running
> > > > on it. Shrink will wait for locks it cannot immediately acquire so
> there
> > > may
> > > > be something blocking it. This is especially likely with
long-running,
> > > > pervasive update queries.
> > > >
> > > > Shrink will always run quicker without a concurrent workload, so
> > quiescing
> > > > the database (using single-user mode, for example) should decrease
the
> > > > runtime.
> > > >
> > > > The wizard option you mention is the same underlying code as
> > > shrinkdatabase,
> > > > and so can be run online and can also be safely interrupted with no
> risk
> > > of
> > > > corruption or losing already completed work.
> > > >
> > > > Regards,
> > > >
> > > > Paul.
> > > >
> > > > --
> > > > Paul Randal
> > > > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > > "Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net>
> wrote
> > > in
> > > > message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> > > > > While, we have been running the SHRINKDATABASE for 98
> > > > > hours now! Production does not seem to be affected.
> > > > > Assume this is because we have ample CPU, memory, and I/O?
> > > > >
> > > > > Should I expect this kind of run time on a database of
> > > > > this size?
> > > > >
> > > > > The process has shunk 6 files so far and we have gained
> > > > > about 2 GB per file. These are the files on the filegroup
> > > > > that is more static, so we expect better result on the
> > > > > other files. Also, these are the files on the RAID 5.
> > > > > Once the work on the current file is complete, we will
> > > > > move to those on the RAID 10, thus speedier?
> > > > >
> > > > > I plan to let this process run to completion, but we have
> > > > > a number of db's of simular size that will need to be
> > > > > shrunk as archiving is done.
> > > > >
> > > > > My question:
> > > > > Is there a way to force priority to a shrink (single user
> > > > > mode, etc) that will can be used while db is out of
> > > > > production that would be quicker?
> > > > >
> > > > > Also, if I use the "Database Maintenance Plan
> > > > > Wizzard", "Update Data Optimization Information"
> > > > > page, "Remove unused space from database files" option to
> > > > > shrink will it faster? Is the lower level process
> > > > > essentially the same as a SHRINKDATABSE? Can it be run
> > > > > while database is in use?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>