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 ShrinkFile

How long does it take to execute DBCC Shrinkfile(DB_FILE, emptyfile) on a 10GB datafiles? If you put your datafiles together with the tempdb datafiles on the same logical drive do we have a performance issue?

Thanks

The time taken for this activity is depends.

THe other part of ur question, Temp db should always be in seperate physical drive if possible for better performance. Datafile is Random read/write but temp db will have moreor less sequential read./write. So if u keep both in same drive it will have performance hit

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/tempdb_capacity_planning_index.mspx

http://msdn2.microsoft.com/en-us/library/ms345368.aspx

Madhu

dbcc shrinkfile

hi, can I shrink the database file, not only the trans log file?You can shrink individual files using DBCC SHRINKFILE. See the Books Online
for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"js" <js@.someone.com> wrote in message
news:udr2WIcWFHA.1044@.TK2MSFTNGP10.phx.gbl...
> hi, can I shrink the database file, not only the trans log file?
>|||Hi JS
Check for DBCC SHRINKDATABASE if you want to shrink the size of the data
files in the specified database
http://msdn.microsoft.com/library/d...
pd1.asp
If you are trying to reduce the size of a single file, then u need to check
DBCC SHRINKFILE
http://msdn.microsoft.com/library/e...asp?frame=true
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"js" wrote:

> hi, can I shrink the database file, not only the trans log file?
>
>|||Thanks,
When to shrink database and when to shrink file usually?
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
> Hi JS
> Check for DBCC SHRINKDATABASE if you want to shrink the size of the data
> files in the specified database
> http://msdn.microsoft.com/library/d...r />
_3pd1.asp
> If you are trying to reduce the size of a single file, then u need to
> check
> DBCC SHRINKFILE
> http://msdn.microsoft.com/library/e...s.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "js" wrote:
>|||Another question:
how to show the database files in size use query? Thanks.
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
> Hi JS
> Check for DBCC SHRINKDATABASE if you want to shrink the size of the data
> files in the specified database
> http://msdn.microsoft.com/library/d...r />
_3pd1.asp
> If you are trying to reduce the size of a single file, then u need to
> check
> DBCC SHRINKFILE
> http://msdn.microsoft.com/library/e...s.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "js" wrote:
>|||js
exec sp_helpdb 'databasename'
"js" <js@.someone@.hotmail.com> wrote in message
news:ednshxhWFHA.228@.TK2MSFTNGP12.phx.gbl...
> Another question:
> how to show the database files in size use query? Thanks.
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
http://msdn.microsoft.com/library/d...s_dbcc_3pd1.asp[c
olor=darkred]
http://msdn.microsoft.com/library/e...asp?frame=true
>|||Usually never. You really only want to shrink the files when they have
grown due to a one time data load or similar type operation. Since
operations such as reindexing require lots of free space int he files you
should always have plenty of free space available.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"js" <js@.someone@.hotmail.com> wrote in message
news:Oizi9mhWFHA.2448@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> When to shrink database and when to shrink file usually?
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:950B4DD6-B2B1-4AC8-991A-A8C9F4FB6778@.microsoft.com...
>|||In addition to sbcc shrinkfile, you can use dbcc showcontig to determine the
level of data and index fragmentation. Much space within a table can be
wasted if you perform frequent updates and deletes on a table with a
clustered index or insert rows in an order other than the clustered order.
Also, you can use dbcc indexdefrag to defragment indexes prior to shrinking
the file or database.
"js" <js@.someone.com> wrote in message
news:udr2WIcWFHA.1044@.TK2MSFTNGP10.phx.gbl...
> hi, can I shrink the database file, not only the trans log file?
>|||BTW - Do you know that you're a month ahead of the rest of the world?
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Tue, 14 Jun 2005 22:50:50 -0400, "js" <js@.someone.com> wrote:

>hi, can I shrink the database file, not only the trans log file?
>sql

DBCC Shrinkfile

I ran a dbcc shrinkfile after deleting data in order to take thte db down in size and it has been running for 2 days. Can I canel the command? Will the file be partially shrunk? Last year when we did this I ran a re-index command. Will I have to run a bakup or truncate log command? I have been trying to get the client to upgrade from SQL 7 but they just have not done it yet.follow up question. If I cancel how long will it take to finish the cancel operation?

DBCC Shrinkfile

Hi,
Can someone tell me of some the reasons why DBCC Shrinkfile does not shrink
the size of the log file in my SQL Server 2000 server? After backing up the
log file, I tried to shrink the physical size of the log file to 2000 MB but
it gave me an error (which I can't remember) even though the actual used size
(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
and this time it worked.
TIA.When you have problems shrinking, you should check for open
transactions using dbcc opentran
You also need to keep in mind that an active portion of the
log can't be impacted. So if you have an active portion of
the log at the end, you won't be able to shrink. You can
check this using dbcc loginfo(YourDatabase). A status of 2
indicates it's active.
Make sure you aren't needlessly shrinking. It should be more
of an ad hoc process - after some out of control process
blows out the size of the log type of thing.
You can find more information in the following article and a
more thorough explanation of what you see with dbcc loginfo:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
-Sue
On Wed, 11 Oct 2006 14:19:02 -0700, Rob
<Rob@.discussions.microsoft.com> wrote:
>Hi,
>Can someone tell me of some the reasons why DBCC Shrinkfile does not shrink
>the size of the log file in my SQL Server 2000 server? After backing up the
>log file, I tried to shrink the physical size of the log file to 2000 MB but
>it gave me an error (which I can't remember) even though the actual used size
>(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
>and this time it worked.
>TIA.

DBCC SHRINKFILE

I have recently changed the indexes ina large table in our database. I have
a lot of excess unused space ,approx 64GB, in the .MDF and want to give it
back to Windows 200 Server.
Our .MDF is not in the SQL DATA directory it is on another volume.
Is there a way to specify a path to another volume when using DBCC SHRINKFILE?
Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long time.
TIA,
-BenDBCC SHRINKFILE doesn't care where the file is as it uses the logical
filename. There is no reason you need to use SHRINKDATABASE.
--
Andrew J. Kelly SQL MVP
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:33AB1ADE-1D27-45F0-A054-B6F4B5F88727@.microsoft.com...
>I have recently changed the indexes ina large table in our database. I
>have
> a lot of excess unused space ,approx 64GB, in the .MDF and want to give
> it
> back to Windows 200 Server.
> Our .MDF is not in the SQL DATA directory it is on another volume.
> Is there a way to specify a path to another volume when using DBCC
> SHRINKFILE?
> Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long
> time.
> TIA,
> -Ben