locked
SQL 2000, Increase on transaction log file RRS feed

  • Question

  • Hello,

    we have a SQL 2000 Server with an application that creates many transactions. The size of the LDF  file increases rapidly. I set up a maintenance plan to do database maintenance in order to back up the data every night. My problem is that I can't get the old transaction log files on the server. I get a new file every time I have scheduled a backup of the transaction log files.
    This behaviour results in the volume to get full every weekend. I then have to delete older backup files and restart the backup job and the application manually. How can I make sure, that these files get deleted by the maintenance job automatically?

    Best regards,

    Florian
    Monday, January 18, 2010 1:42 PM

Answers

  • You need a second maint cleanup task and configure the right file extension (.trn) for that.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    • Proposed as answer by Lekss Wednesday, January 20, 2010 12:20 AM
    • Marked as answer by Xiao-Min Tan – MSFT Friday, February 5, 2010 3:41 AM
    Tuesday, January 19, 2010 8:39 AM
  • Hi,

    What’s the recovery model for your datable? Based on your recovery mode, we suggest you use the different backup strategy to backup your database and avoid the transaction log file increasing rapidly.

    For simple recovery, you could schedule a full database backup task and clear up task.
    To reduce the size the transaction log, you need to run the DBCC SHRINKFILE command after performing the backup. You could create a job to automatically run it. For more information about DBCC SHRINKFILE, refer to http://msdn.microsoft.com/en-us/library/aa258824(SQL.80).aspx.

    For full recovery model, it may be better to a daily full backup and hourly transaction log backup. Then, perform a cleanup task to delete the old backup files (.bak and .trn) older than 3 days.  Lastly, you need to define a reasonable schedule  for all the tasks above.

    References:
    SQL Server 2000 Backup and Restore
    http://msdn.microsoft.com/en-us/library/cc966495.aspx
    How to schedule a job (Enterprise Manager)
    http://msdn.microsoft.com/en-us/library/aa177009(SQL.80).aspx
    How to stop the transaction log of a SQL Server database from growing unexpectedly, please go to:
    http://support.microsoft.com/default.aspx/kb/873235

    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Wednesday, January 20, 2010 9:30 AM

All replies

  • The option for deleting backup files in maint plans you find the the Maintenance Cleanup task.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Monday, January 18, 2010 9:32 PM
  • Tibor,

    thank you for your reply. I get the database backup files deleted automatically, but not the TRN files. How would the sql script looklike to enable that?

    Best regards,

    Florian
    Tuesday, January 19, 2010 7:19 AM
  • You need a second maint cleanup task and configure the right file extension (.trn) for that.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    • Proposed as answer by Lekss Wednesday, January 20, 2010 12:20 AM
    • Marked as answer by Xiao-Min Tan – MSFT Friday, February 5, 2010 3:41 AM
    Tuesday, January 19, 2010 8:39 AM
  • Hi,

    What’s the recovery model for your datable? Based on your recovery mode, we suggest you use the different backup strategy to backup your database and avoid the transaction log file increasing rapidly.

    For simple recovery, you could schedule a full database backup task and clear up task.
    To reduce the size the transaction log, you need to run the DBCC SHRINKFILE command after performing the backup. You could create a job to automatically run it. For more information about DBCC SHRINKFILE, refer to http://msdn.microsoft.com/en-us/library/aa258824(SQL.80).aspx.

    For full recovery model, it may be better to a daily full backup and hourly transaction log backup. Then, perform a cleanup task to delete the old backup files (.bak and .trn) older than 3 days.  Lastly, you need to define a reasonable schedule  for all the tasks above.

    References:
    SQL Server 2000 Backup and Restore
    http://msdn.microsoft.com/en-us/library/cc966495.aspx
    How to schedule a job (Enterprise Manager)
    http://msdn.microsoft.com/en-us/library/aa177009(SQL.80).aspx
    How to stop the transaction log of a SQL Server database from growing unexpectedly, please go to:
    http://support.microsoft.com/default.aspx/kb/873235

    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Wednesday, January 20, 2010 9:30 AM
  • Hi,

    How about my suggestions? Did you resolve your problem?

    If there are any more questions, please let me know.
    Thanks.
    ***Xiao Min Tan***Microsoft Online Community***
    Monday, January 25, 2010 9:14 AM