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)?.
>
>

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 ha
s
left to run (the equivalent of the Oracle dynamic view v$session_longops)?.
Thanks,
AndyNot 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 200
5,
> 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, an
d
> 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)?.
>
>

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,
AndyNot 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...
> > 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
>
>

DBCC SHRINKDATABASE

In my company, we use this command to shink databse, but it was not like our
expectation after it was done because the size was reduced less than
expectation. Could someone tell me if there are some factor to impact this
operation? What can I do to shrink much more size of database. Thanks.
Jerry Mu
See DBCC SHRINKFILE, section 'The File Does Not Shrink' on BOL.
Run the query listed there to see if sufficient free space is available
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS AvailableSpaceInMB
FROM sys.database_files;
Hope this helps,
Ben Nevarez
Senior Database Administrator
"Iter" wrote:

> In my company, we use this command to shink databse, but it was not like our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>
|||Also, see the following article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Ekrem ?nsoy
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>
|||Are you sure there is free space in the database? What is your goal in
shrinking the database? Reclaiming disk space temporarily? Unless your
database is large only because of a very unusual data load, and that data is
now gone, I would not allocate that disk space to something else just yet.
If your database is large because of normal activity, then shrinking it will
only mean that later it has to grow again, and you do not want this to be an
unexpected event that happens during peak activity, because you will have a
lot of unhappy users. Better off leaving the file large...
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>
|||Also I second the recommendation to read Tibor's article, posted by Ekrem.
It echoes my suggestions but in much more detail.
A
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>

DBCC SHRINKDATABASE

In my company, we use this command to shink databse, but it was not like our
expectation after it was done because the size was reduced less than
expectation. Could someone tell me if there are some factor to impact this
operation? What can I do to shrink much more size of database. Thanks.
Jerry MuSee DBCC SHRINKFILE, section 'The File Does Not Shrink' on BOL.
Run the query listed there to see if sufficient free space is available
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS AvailableSpaceInMB
FROM sys.database_files;
Hope this helps,
Ben Nevarez
Senior Database Administrator
"Iter" wrote:
> In my company, we use this command to shink databse, but it was not like our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>|||Also, see the following article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Ekrem Ã?nsoy
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>|||Are you sure there is free space in the database? What is your goal in
shrinking the database? Reclaiming disk space temporarily? Unless your
database is large only because of a very unusual data load, and that data is
now gone, I would not allocate that disk space to something else just yet.
If your database is large because of normal activity, then shrinking it will
only mean that later it has to grow again, and you do not want this to be an
unexpected event that happens during peak activity, because you will have a
lot of unhappy users. Better off leaving the file large...
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>|||Also I second the recommendation to read Tibor's article, posted by Ekrem.
It echoes my suggestions but in much more detail.
A
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>sql