DBCC SHRINKDATABASE not completing

We're running SQL 2005 Enterprise with a very large database (198,893,696
KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
following deletion of about 4 million records, but found that starting late
last week it is no longer completing - even after 12 hours. (It would
normally take 30 minutes). Other operational steps are running ok. We're
not seeing any error entries. Ideas? Thank you.Hi Jeffrey
First of all, you should seriously reconsider running DBCC SHRINKDATABASE on
a daily basis. It is an incredibly resource intensive operation, that can
end up hurting as much as help. Take a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx
If you have to use DBCC SHRINKDATABASE, you can look in the
sys.dm_exec_requests view, and look at the percent_complete column to verify
that the operation is making progress, and get a rough idea how much longer
it will take.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.|||Why shrink today, only to have it grow again tomorrow with the 4M
insert/delete operations' Also it would seem that 4M records in a 200GB
database isn't that much anyway.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.