dbcc shrinkfile - how long will it take

Hi,
We are shrinking a large databse using dbcc shrinkfile and it has been
running for hours. Is there any way of determining how long the job still has
left to run (the equivalent of the Oracle dynamic view v$session_longops)?.
Thanks,
Andy
Not that I am aware of. I avoid this by shrinking in increments of 100MB.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy
|||Hi,
In SQL Server we can not exactly say how the process is going to run. But as
Kevin mentioned you could try
shrinking the files by providing a lower value (500 MB or soo...). Ensure
that you do a Backup LOG command
before shrinking the tranasction log.
Thanks
Hari
Sql server MVP
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
> has
> left to run (the equivalent of the Oracle dynamic view
> v$session_longops)?.
> Thanks,
> Andy
|||Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
but even that is just a percentage complete with an elapsed-time based
extrapolation of the completion time. Basically, there are far too many
variables to consider to have a hope of being able to predict the run-time -
the worst ones being blocking, the starting state of the file/database, and
how much work shrink needs to do. For instance, if another process takes a
lock that shrink needs, shrink will wait forever for that lock.
Have you checked to make sure it is actually progressing and isn't blocked?
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> Hi,
> We are shrinking a large databse using dbcc shrinkfile and it has been
> running for hours. Is there any way of determining how long the job still
has
> left to run (the equivalent of the Oracle dynamic view
v$session_longops)?.
> Thanks,
> Andy
|||Not sure if this is related to the original post but I have SQL2000 SP4
install and have tried running DBCC SHRINKDATABASE (MyDB). This results is a
lock that is not released. Ent. Mgr shows "spid 58 (blocked by 58)" the Wait
Type is "PAGEIOLATCH_SH".
I think this maybe a bug in the SP4? I haven't had this problem with a DB
shrink before.
Regards,
John
"Paul S Randal [MS]" wrote:

> Not in SQL Server 2000. We've put progress reporting in for SQL Server 2005,
> but even that is just a percentage complete with an elapsed-time based
> extrapolation of the completion time. Basically, there are far too many
> variables to consider to have a hope of being able to predict the run-time -
> the worst ones being blocking, the starting state of the file/database, and
> how much work shrink needs to do. For instance, if another process takes a
> lock that shrink needs, shrink will wait forever for that lock.
> Have you checked to make sure it is actually progressing and isn't blocked?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Squirrel" <Squirrel@.discussions.microsoft.com> wrote in message
> news:956637B6-5C96-4F71-9630-E13702C94D78@.microsoft.com...
> has
> v$session_longops)?.
>
>