locked
How long for shrink data files from 80 GB to 3 GB RRS feed

  • Question

  • Dear all,

    We're using sql server 2005 enterprise with sp2. We have 80 GB database and will delete most of the large binary content(varchar(max)) and finally our database will reduce to 3 GB data. However, the data file size will remain at 80 GB after deletion and therefore we need to shrink the data file.

    1.) Do this shrinking data file process lock all the database and such that online processing is prohibited?

    2.) How long for this process for shrinking 80GB to 3 GB? Can 2 hours finish?

    3.) Any hints we can speed up this shrinking process?

    Thanks a lot

    Wallace

    Friday, August 17, 2012 10:47 AM

Answers

  • In simple way we can say that the reason why you cannot tell how much time it will take exaclty, you cannot speed it up because the shrinking is an single threaded  processes and also it depends on the Load on the DB& server resources, but you can run during the Non business hours so that the Impact willbe less ,you might also see some performance issue....during the business hours if you run so...

    also -

    The Shrink Operation Is Blocked

    It is possible for shrink operations to be blocked by a transaction that is running under a row versioning-based isolation level. For example, if a large delete operation running under a row versioning-based isolation level is in progress when a DBCC SHRINK DATABASE operation is executed, the shrink operation will wait for the delete operation to complete before shrinking the files. When this happens, DBCC SHRINKFILE and DBCC SHRINKDATABASE operations print out an informational message (5202 for SHRINKDATABASE and 5203 for SHRINKFILE) to the SQL Server error log every five minutes in the first hour and then every hour after that. For example, if the error log contains the following error message:

    DBCC SHRINKFILE for file ID 1 is waiting for the snapshot

    transaction with timestamp 15 and other snapshot transactions linked to

    timestamp 15 or with timestamps older than 109 to finish.

    This means that the shrink operation is blocked by snapshot transactions that have timestamps older than 109, which is the last transaction that the shrink operation completed. It also indicates that the transaction_sequence_num, or first_snapshot_sequence_num columns in the sys.dm_tran_active_snapshot_database_transactions dynamic management view contains a value of 15. If either the transaction_sequence_num, or first_snapshot_sequence_num columns in the view contains a number that is less than the last transaction completed by a shrink operation (109), the shrink operation will wait for those transactions to finish.

    To resolve the problem, you can do one of the following tasks:

    Terminate the transaction that is blocking the shrink operation.

    Terminate the shrink operation. If the shrink operation is terminated, any completed work is retained.

    Do nothing and allow the shrink operation to wait until the blocking transaction completes.

    ref->http://msdn.microsoft.com/en-us/library/ms189493%28v=sql.90%29.aspx


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    • Proposed as answer by Shulei Chen Thursday, August 23, 2012 8:28 AM
    • Marked as answer by Shulei Chen Monday, August 27, 2012 10:17 AM
    Friday, August 17, 2012 9:11 PM
  • Dear all,

    We're using sql server 2005 enterprise with sp2. We have 80 GB database and will delete most of the large binary content(varchar(max)) and finally our database will reduce to 3 GB data. However, the data file size will remain at 80 GB after deletion and therefore we need to shrink the data file.

    1.) Do this shrinking data file process lock all the database and such that online processing is prohibited?

    2.) How long for this process for shrinking 80GB to 3 GB? Can 2 hours finish?

    3.) Any hints we can speed up this shrinking process?

    Thanks a lot

    Wallace

    You may find out that you cannot shrink the file - because the table still has that space allocated to the large binary column.  This will show up as unused space in the database - but still allocated.

    If you run into this situation - you will need to drop the column, clean the table (DBCC CLEANTABLE) and add the column back.  Or, you could build a brand new table, copy the data from the old table to the new table and drop the old table.


    Jeff Williams

    • Proposed as answer by Shulei Chen Thursday, August 23, 2012 8:28 AM
    • Marked as answer by Shulei Chen Monday, August 27, 2012 10:17 AM
    Sunday, August 19, 2012 6:56 PM

All replies


  • 1.) Do this shrinking data file process lock all the database and such that online processing is prohibited?

    may lock the table its currently processing.. 

    2.) How long for this process for shrinking 80GB to 3 GB? Can 2 hours finish?

    check Precent_complete column on  sys.dm_exec_requests DMV, locate the SPID of the shrink process..

    3.) Any hints we can speed up this shrinking process?

    well first of all.. shrinking db is bad idea , see the link 
    http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Friday, August 17, 2012 10:58 AM
  •  you can shrink the database using

    USE [IP3_MasterV2]
    GO
    DBCC SHRINKDATABASE(N'dbname', 5 )
    GO

    to reclaim the disk space after deleting the data you can use

    http://blog.sqlauthority.com/2008/01/11/sql-server-reclaim-space-after-dropping-variable-length-columns-using-dbcc-cleantable/

    Friday, August 17, 2012 10:59 AM
  • Hi Wallace,

    Answers to your questions as follow:

    1) Shrinking data files will not lock the database but user may experience slowness.

    2) It depends on your CPU and Memory, But I think it should not take more than 30 mins.

    3) I suggest you to skink your data file in batches as follow:

    USE [<Your Database>]
    GO
    DBCC SHRINKFILE ('filename', target_size)
    GO
    DBCC SHRINKFILE ('filename', target_size)
    GO
    DBCC SHRINKFILE ('filename', target_size)
    E.g. 
    DBCC SHRINKFILE ('C:\MyData.mdf', 70000)
    GO
    DBCC SHRINKFILE ('C:\MyData.mdf', 60000)
    GO
    DBCC SHRINKFILE ('C:\MyData.mdf', 50000)
    GO
    DBCC SHRINKFILE ('C:\MyData.mdf', 40000)
    GO
    DBCC SHRINKFILE ('C:\MyData.mdf', 30000)
    GO
    DBCC SHRINKFILE ('C:\MyData.mdf', 20000)
    GO
    DBCC SHRINKFILE ('C:\MyData.mdf', 10000)
    GO
    DBCC SHRINKFILE ('C:\MyData.mdf', 3500)
    GO


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by AniqaSQL Friday, August 17, 2012 11:11 AM
    Friday, August 17, 2012 11:00 AM
  • I second this, BTW don't forget to rebuild your indexes after a shrink

    Geert Vanhove

    Friday, August 17, 2012 11:00 AM
  • Hi Ravi,

    He is asked the question about Shrinking the data file not LogFile.


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Friday, August 17, 2012 11:01 AM
  • @greetDCOD

    very valid point..

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Friday, August 17, 2012 11:01 AM
  • In simple way we can say that the reason why you cannot tell how much time it will take exaclty, you cannot speed it up because the shrinking is an single threaded  processes and also it depends on the Load on the DB& server resources, but you can run during the Non business hours so that the Impact willbe less ,you might also see some performance issue....during the business hours if you run so...

    also -

    The Shrink Operation Is Blocked

    It is possible for shrink operations to be blocked by a transaction that is running under a row versioning-based isolation level. For example, if a large delete operation running under a row versioning-based isolation level is in progress when a DBCC SHRINK DATABASE operation is executed, the shrink operation will wait for the delete operation to complete before shrinking the files. When this happens, DBCC SHRINKFILE and DBCC SHRINKDATABASE operations print out an informational message (5202 for SHRINKDATABASE and 5203 for SHRINKFILE) to the SQL Server error log every five minutes in the first hour and then every hour after that. For example, if the error log contains the following error message:

    DBCC SHRINKFILE for file ID 1 is waiting for the snapshot

    transaction with timestamp 15 and other snapshot transactions linked to

    timestamp 15 or with timestamps older than 109 to finish.

    This means that the shrink operation is blocked by snapshot transactions that have timestamps older than 109, which is the last transaction that the shrink operation completed. It also indicates that the transaction_sequence_num, or first_snapshot_sequence_num columns in the sys.dm_tran_active_snapshot_database_transactions dynamic management view contains a value of 15. If either the transaction_sequence_num, or first_snapshot_sequence_num columns in the view contains a number that is less than the last transaction completed by a shrink operation (109), the shrink operation will wait for those transactions to finish.

    To resolve the problem, you can do one of the following tasks:

    Terminate the transaction that is blocking the shrink operation.

    Terminate the shrink operation. If the shrink operation is terminated, any completed work is retained.

    Do nothing and allow the shrink operation to wait until the blocking transaction completes.

    ref->http://msdn.microsoft.com/en-us/library/ms189493%28v=sql.90%29.aspx


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    • Proposed as answer by Shulei Chen Thursday, August 23, 2012 8:28 AM
    • Marked as answer by Shulei Chen Monday, August 27, 2012 10:17 AM
    Friday, August 17, 2012 9:11 PM
  • Dear all,

    We're using sql server 2005 enterprise with sp2. We have 80 GB database and will delete most of the large binary content(varchar(max)) and finally our database will reduce to 3 GB data. However, the data file size will remain at 80 GB after deletion and therefore we need to shrink the data file.

    1.) Do this shrinking data file process lock all the database and such that online processing is prohibited?

    2.) How long for this process for shrinking 80GB to 3 GB? Can 2 hours finish?

    3.) Any hints we can speed up this shrinking process?

    Thanks a lot

    Wallace

    You may find out that you cannot shrink the file - because the table still has that space allocated to the large binary column.  This will show up as unused space in the database - but still allocated.

    If you run into this situation - you will need to drop the column, clean the table (DBCC CLEANTABLE) and add the column back.  Or, you could build a brand new table, copy the data from the old table to the new table and drop the old table.


    Jeff Williams

    • Proposed as answer by Shulei Chen Thursday, August 23, 2012 8:28 AM
    • Marked as answer by Shulei Chen Monday, August 27, 2012 10:17 AM
    Sunday, August 19, 2012 6:56 PM