none
Log file is too large RRS feed

  • Question

  • I administer an application that runs on SQL 2000, without being an SQL expert myself. I have observed that if I create a maintenance plan then the log file gets trimmed, but otherwise the log file keeps growing. All was going well at one site, but recently the log file has become huge. The server is also running out of disk space. Is that the problem? I'm not sure how to control this. Can I delete a log and then it will re-create? Two smaller databases share the same maintenance plan and these logs are small. Thank you.

    Wednesday, May 28, 2008 4:34 PM

Answers

  • No you should not delete the transaction logs. Change the recovery model of the database to simple or if thats not possible you need to take regular t-log backups to ensure that the log file does not grow abnormally. Refer these links where similar topic was discussed,http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2522242&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2728787&SiteID=1

    - Deepak
    Wednesday, May 28, 2008 5:45 PM
    Moderator
  • Hi,

    Transaction log backup will backup the commited transactions from the log file and it will allow those space to reuse. Consider you have a scenario that there are more uncommited transactions in the log file, in that time even if you take log backup, it will backup the commited one. You can check the open transactions using the below command

    DBCC OPENTRAN('dbname')

    You can also use the below command to check log reuse wait status

    SELECT name,log_reuse_wait_desc from sys.databases where name='yourdbname'


    Have you configured the third database for log shipping or mirroring??
    Thursday, May 29, 2008 3:58 AM
    Moderator

All replies

  • No you should not delete the transaction logs. Change the recovery model of the database to simple or if thats not possible you need to take regular t-log backups to ensure that the log file does not grow abnormally. Refer these links where similar topic was discussed,http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2522242&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2728787&SiteID=1

    - Deepak
    Wednesday, May 28, 2008 5:45 PM
    Moderator
  • I read these articles. I have always kept the log from growning by backing it up with maintenance. My problem is that it stopped working for this one database. Two other databases using the same maintenance plan still get trimmed, but not the third, which is also the largest. I suspect that somehow that log lacks enough space to reorganize itself. The log is 60 GB and only 1GB remains on the drive. If my hunch is correct, how much room do I need when i next perform the log backup?

    Thursday, May 29, 2008 1:21 AM
  • Hi,

    Transaction log backup will backup the commited transactions from the log file and it will allow those space to reuse. Consider you have a scenario that there are more uncommited transactions in the log file, in that time even if you take log backup, it will backup the commited one. You can check the open transactions using the below command

    DBCC OPENTRAN('dbname')

    You can also use the below command to check log reuse wait status

    SELECT name,log_reuse_wait_desc from sys.databases where name='yourdbname'


    Have you configured the third database for log shipping or mirroring??
    Thursday, May 29, 2008 3:58 AM
    Moderator
  • Deepak (and others) - Thanks to your links, I found KB272318 for my SQL 2000 database, and, following these instructions, successfully shrank the log file. Thank you....

    Sunday, June 1, 2008 7:13 AM