locked
Locking RRS feed

  • Question

  • I have a SQL 2005 SP2 database that has a 300MB transaction log file.  I do a full backup and restore it to my SQL 2008 R2 2 node cluster.  The restore happens fine, but the transaction log is over 6GB in size and starts filling up.  I have not yet pointed the application to the database.  Attempts to backup the transaction log fails to truncate.  Transaction log continues to grow until it fills up the drive.  sp_lock shows 2 spids with a value of -2.  Other databases seem to restore fine to this same server.  This seems like an issue with the existing database.

    Does anyone have any ideas as to why this is happening?

    Monday, December 6, 2010 10:13 PM

Answers

  • Are you restoring it on top of an existing database? What is the log size of the existing db? It does seem strange to go from 300MB to 6GB when you restore. However, the log can grow during restore because the database needs to be recovered. And if there were a lot of transactions in progress, that can take a long time and a lot of logging to reconcile them during the restore.

    Take a look in sys.databases at the value in the log_reuse_wait_desc for this database. What is the value?

    Also, why did you use the subject "Locking"; is there more going on that you didn't tell us about?


    HTH, Kalen Delaney www.SQLServerInternals.com
    • Marked as answer by WeiLin Qiao Thursday, December 16, 2010 12:37 PM
    Monday, December 6, 2010 10:36 PM

All replies

  • Are you restoring it on top of an existing database? What is the log size of the existing db? It does seem strange to go from 300MB to 6GB when you restore. However, the log can grow during restore because the database needs to be recovered. And if there were a lot of transactions in progress, that can take a long time and a lot of logging to reconcile them during the restore.

    Take a look in sys.databases at the value in the log_reuse_wait_desc for this database. What is the value?

    Also, why did you use the subject "Locking"; is there more going on that you didn't tell us about?


    HTH, Kalen Delaney www.SQLServerInternals.com
    • Marked as answer by WeiLin Qiao Thursday, December 16, 2010 12:37 PM
    Monday, December 6, 2010 10:36 PM
  • Were there any open transactions (DTC or otherwise) when the database was backed up? It is possible that all uncommitted transactions are being rolled back during the recovery phase.


    Suhas De
    --------------------------------------------------------------------------------
    Please mark solved if your question is completely answered; vote it as helpful to help others find a solution quicker.
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/b/suhde
    Tuesday, December 7, 2010 9:48 AM