SQL Server 2005 Transaction Log

Question

SQL Server 2005 Transaction Log

Dipen-312652

SSC Veteran

Points: 269
More actions
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

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

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

Joshua Jones-214598 SSChasing Mays Points: 637 More actions · Answer 1

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

Dipen-312652 SSC Veteran Points: 269 More actions · Answer 2

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

Joshua Jones-214598 SSChasing Mays Points: 637 More actions · Answer 3

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

Dipen-312652 SSC Veteran Points: 269 More actions · Answer 4

Dipen-312652

SSC Veteran

Points: 269

April 6, 2006 at 9:50 am

#631278

Hi,

RECOVERY=FULL

Regards

Dipendra

Joshua Jones-214598 SSChasing Mays Points: 637 More actions · Answer 5

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

Dipen-312652 SSC Veteran Points: 269 More actions · Answer 6

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

Joshua Jones-214598 SSChasing Mays Points: 637 More actions · Answer 7

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

D Simmons SSC Journeyman Points: 75 More actions · Answer 8

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

Sameer Raval Hall of Fame Points: 3214 More actions · Answer 9

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

Dipen-312652 SSC Veteran Points: 269 More actions · Answer 10

Sameer,

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

Regards

Dipendra

john_s50 SSC Veteran Points: 213 More actions · Answer 11

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

kaung Newbie Points: 3 More actions · Answer 12

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

sonal_anjaria SSC Eights! Points: 854 More actions · Answer 13

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

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