Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2010
    Posts
    6

    Unanswered: SQL 2008 Mirroring running out of disk space due to transaction logs

    Last month I mirrored a SQL 2008 db to another server running sql 2008. Everything was fine until last week. The backups went from 60GB to 220GB in about 1 weeks time. I checked the mirror and noticed it is in a suspended state. I then checked the folder holding the transaction log file for the principal db and noticed it was over 150gb in size but the actual db is only 2gb. On the mirror server I am seeing a 2GB file which matches the principal db but I am am also seeing a 48GB file which has the same name as the principal db with a "_1" after it. I have a second file which is only a few meg and it is being mirrored on the same servers with no issue. I am getting desperate and planning to drop the mirror before my prodcution servers comes to a halt. Suggestions?

    I see the followin on the Principal server:
    Log Name: Application
    Source: MSSQLSERVER
    Date: 12/28/2010 10:28:16 AM
    Event ID: 1453
    Task Category: Server
    Level: Error
    Keywords: Classic
    User: N/A
    Computer: DB.lp.local
    Description:
    'TCP://SQLREPL.lp.local:5022', the remote mirroring partner for database 'UTOPIA', encountered error 5149, status 3, severity 25. Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.

    and the following on the mirror server
    Log Name: Application
    Source: MSSQLSERVER
    Date: 12/28/2010 10:28:16 AM
    Event ID: 5149
    Task Category: Server
    Level: Error
    Keywords: Classic
    User: N/A
    Computer: SQLREPL.lp.local
    Description:
    MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\UTOPIA_1.LDF '.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The errors you see are the result of "auto-grow" failure on the log device on the mirror. If your PRINCIPAL does not have "UTOPIA_1.ldf", and your mirror does, - you shouldn't have had mirroring running to begin with. Prerequisites for mirroring include backing up your prod database, and then restoring it on intended mirror, which would have retained not only the number, size, but also logical names of all devices, including log. Are you sure nobody messed with your mirror over Christmas (Grinch maybe)?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Dec 2010
    Posts
    6
    Thanks for responding. I followed a technical document to set up the mirror. I performed a backup and a restore which included the log file prior to establishing the mirror. You're correct, I do not have an "_1" on the principal but I do have one on the mirror. I must have missed something but I don't understand why it was okay all this time and only grew in the past 7 days. So, what next? I am running out of space fast. Can I remove the mirror? What happens to that huge 150gb ldf file?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    So, what next?
    I think you need to redo it. It's also a good practice. I also suggest to set up a test environment for it (2GB, you can set it up on a virtual machine with 2 instances, or 2 virtual machines that can see each other, - the latter will be even better).
    I am running out of space fast. Can I remove the mirror?
    See answer above.
    What happens to that huge 150gb ldf file?
    It''l be there, until you drop the database.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Dec 2010
    Posts
    6
    You said the 150gb ldf file would be there until I drop the database. I'm not that experienced so I tried to google the term "drop" and it appears to mean delete, as in delete the database. Were you referring to dropping the mirrored database? as in remove mirroring?

    thanks

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    After you "remove" the mirroring, the mirror instance will still have the database with 150gb log. So I assume you'd like to "delete" it, since you'll have to re-restore it from the principal even if you want to redo the mirroring. Either way - drop=delete.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Dec 2010
    Posts
    6
    The principal db is the one with 150gb transaction log. I would prefer not to delete the db since this is a prod system. Is there any other way to get rid of that transaction log? Can it be deleted?

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    OK, you need to remove mirroring (alter database ... set partner off), then set your prod db to simple recovery mode, then remove the large log file, then reset the db back to full recovery mode, then do a full db backup, then a transaction log backup, and then you can redo the mirroring.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Dec 2010
    Posts
    6
    Thanks. Drive is down to 9mb from 160gb. I appreciate your help fast responses in such a time sensitive situation.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Sure.

    BTW, make sure to set up transaction log backup on the principal, once you re-enable mirroring. And make sure you know exactly who's the PRINCIPAL and who's the MIRROR at any given point.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Dec 2010
    Posts
    6
    The transaction log file is now 1,024kb. I was afraid to delete it so I followed your instructions and placed the db into simple recovery mode and selected the shrink files option and selected log file. In a few seconds the log file had shrunk from 150GB to 1,024kb. Thanks for the tip on backing up the log file, I understand now how important that is. I will attempt to reestablish the mirror over the weekend. Going forward I need to set up an alerting mechanism for sql and for disk space in general.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •