Post reply

SQL Server 2005 Transaction Log

  • April 5, 2006 at 5:42 am

    #112790

     Hi,

    My company uses SQL Server 2005. We have a database which is not that large but the transaction log is now around 17GB and increasing daily. The Autogrow setting is enabled.

    What steps do I need to perform to truncate and then shrink the log as I would like to add some commands to a job that runs on a weekly basis to bring the .LDF file size down.

    Regards

    Dipendra

  • April 6, 2006 at 9:11 am

    #631260

    Hi Dipendra,

    Is your database being backed up regularly? Also, what is the recovery model for the database? Its probably a better idea to get a handle on why the transaction log is growing out of control and solving that problem than trying to initiate a SQL job to control the log size.

     

    Cheers,

    Joshua Jones

  • April 6, 2006 at 9:24 am

    #631268

    Hi,

    Thanks for your response. For backup purposes, we are using CA Brightstor Arcserv 11.5 and using the SQL Server agent to backup the database daily therefore we would use Brightstor to restore a database. Autogrowth is enabled on the db.

    The initial size of the log is 17181 MB which is just over 17GB

    The autogrowth setting is set to the following setting:

    By 10 percent, restricted growth to 2097152 MB

    Regards

    Dipendra

     

  • April 6, 2006 at 9:41 am

    #631276

    Thanks for the info. One thing; when I referred to the recovery model, I meant the setting for the database. On SQL 2005, there's a few ways to check:

    1. sp_helpdb 'database_name_here'

         --In the first result set, under the "status" column, will be a string that looks like '...Recovery=VALUE...'; interested in the value there.

    or

    2. select [name], recovery_model_desc from sys.databases where [name] = 'database_name_here'

         --this will also tell you the Recovery model for the database.

    The value should be either FULL, SIMPLE, or BULK_LOGGED. This will help figure out what the issue is.

    Cheers,

    Joshua Jones

  • April 6, 2006 at 9:50 am

    #631278

    Hi,

    RECOVERY=FULL

    Regards

    Dipendra

  • April 6, 2006 at 9:59 am

    #631284

    Ok, that's good to know.

    You said that the database is being backed up daily; so there's one full backup of the database happening every 24 hours or so, correct? No transaction log backups happening throughout the day?

     In the FULL recovery model, the transaction log is basically holding all transactions for the database in the log until the next backup happens. It sounds like you need to either A)re-evaluate the recovery model choice, and switch to SIMPLE or BULK_LOGGED, or B)implement regular transaction log backups. Once one of those decisions has been made, you'll be able to get the transaction log size under control. You will definitely want to check the entry in Books Online under 'Selecting a Recovery Model'.

    Cheers,

    Joshua Jones

  • April 6, 2006 at 10:04 am

    #631285

    Hi,

    The db is backed up daily - Monday to Friday and no Transaction log backups are happening during the day.

    As my SQL Server knowledge is slightly limited, what is the best recovery model to use.

    There is really only one user accessing the db

    Regards

    Dipendra

  • April 6, 2006 at 10:18 am

    #631297

    The best recovery model to use is different for each scenario. Basically, if there are very few users, and therefore very little transactions, you probably would be best switching to SIMPLE. However, please note that this means no "Point in time" recovery; if the database becomes corrupt/lost, etc. you will have to restore from the last full backup, and accept that there will be data loss.

    I strongly suggest you read the Books Online entries to make sure you're getting what you need. If this is a small but CRITICAL database, and you cannot afford data loss, you won't want to use the SIMPLE recovery model. I don't know what your particular requirements are, so my recommendation is a shot in the dark. But it will solve the transaction log size problem .

    The other thing to note: If you do switch to SIMPLE, you'll want to go through the exercise of shrinking the log to a more manageable size (using DBCC SHRINKFILE is easy). Leave autogrow on, and watch the log file for a few days, making sure it doesn't grow out of control again (which could be a sign of a different problem).

    Cheers,

    Joshua Jones

  • April 7, 2006 at 12:34 am

    #631410

    Hi,

    I'm taking a wonderful webcast from MSDN that is covering exactly what you are experiencing.  The web address to the presenter (Kimberly Tripp) is:

    http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=4daca6d9-c52b-4f85-a47c-20f252883e20

    The classes are free but you must register with MSDN (which is also free).  The first in the series is "Creating a Recoverable Database".  In it she explains (better than me) that transaction logs can fragment and grow due to excessive VLFs (virtual log files).  To see if a database has many of these you can execute the query command DBCC LOGINFO.  The number of rows returned in the result equate to the number of VLFs.  Anything over 50 is considered excessive.  She gives detail explanations on how to gain control over the database, log file and proper recovery methods to choose from based on how your database is being used.

    Hope this information is helpful.

    Donna Simmons

     

  • April 7, 2006 at 9:56 am

    #631540

    Dipen,

    After reading your post, what I would like to suggest you is this.

    In your case better stay with full recovery model. Reason for that is with every day full backup also your transaction growth is sizable(for 24 hour period) and if for any reason your server goes down you will loose data after previous full backup. Better write a job that takes transaction log every few hour, infact I will recommand every hour and by doing so you gain 2 major advantage first tr. log will not grow to that size and second in any case you will not loose more than hour data. 

     

     

    Kindest Regards,

    Sameer Raval [Sql Server DBA]
    Geico Insurance
    RavalSameer@hotmail.com

  • April 7, 2006 at 10:05 am

    #631546

    Sameer,

    Thanks for your reply. How do I go about writing this job as my DBA knowledge is very limited.

    Regards

    Dipendra

  • April 13, 2006 at 7:59 am

    #632381

    I recently noticed that the backup database command in sql2005 does not truncate my transaction log like it does in sql2000.

    That could be the cause of your ever increasing transaction log file.

    Can anyone else confirm this?

    If you backup the transaction log after backing up the database, the log should truncate properly (the file won't shrink, but it will stop growing).

    Thanks

    John

  • December 2, 2009 at 1:33 pm

    #1086741

    Dipen:

    That is a very good suggestion to create a Job to do hourly backups of the Transaction Logs. However, how large should we allow the size to grow until we have to do a write over?

    Sincerely,

    Ken Aung

    Sys Admin

  • December 9, 2009 at 8:27 am

    #1089892

    John,

    I am not sure about SQL 2000 in 2005, I don't believe the Transaction log is automatically truncated when you do a backup, you have to physically shrink it.

    Sincerely,

    Sonal.

  • December 9, 2009 at 9:11 am

    #1089957

    You have noticed that you're answering a 3 year old thread?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply