Transactin Log Issue

Question

Transactin Log Issue

poratips

Hall of Fame

Points: 3994
More actions
April 19, 2013 at 12:56 pm

#274184

Hi,
I am having issue with out Transaction logs as it's keep getting full even after shrinking the file.
We have Sql server 2008 and it's also run MS CRM application.
We have T -Log Back up is set up to every 2 hrs.
I have changed my recovery model from FULL to SIMPLE then Shrink the file and did full back up and set up again to FULL Recovery Mode but still it's growing fast.
Appreciated any help!

Viewing 13 posts - 1 through 12 (of 12 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

first of all there is no point repeatedly shrinking the log if it just having to grow again - and shrinking a log does not remove data from it.

From what you describe the first thing I would do here is back the log up more frequently.

If your disk can handle the size the log is growing to with 2 hourly backups don't worry about it. Set the log to the size it needs to be to handle your activity, set a sensible growth factor and leave it there.

---------------------------------------------------------------------

poratips Hall of Fame Points: 3994 More actions · Answer 2

Thanks George.

I agreed with you for the first one not to shrink frequently.

I can set up the log back up hourly instead of every two hrs.

Now when you say " Set the log to the size it needs to be to handle your activity, set a sensible growth factor and leave it there. " I have currently set up 10%, unrestricted growth, what you suggest for Initial size and growth ideal settings?

We have MS CRM application running on this DB.

Thanks.

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

Please read through this - Managing Transaction Logs[/url]

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

george sibbald SSC Guru Points: 104210 More actions · Answer 4

Now when you say " Set the log to the size it needs to be to handle your activity, set a sensible growth factor and leave it there. " I have currently set up 10%, unrestricted growth, what you suggest for Initial size and growth ideal settings?
We have MS CRM application running on this DB.

see kimberly tripps blog that gail refers to in her blog referred to above.

Basically the size should be enough to handle your largest amount of log activity between log backups, so if your largest log backup is a couple of GB say, the log needs to be slightly larger than that. Growth factor should not be a %, actual MB value should be relative to size of log and minimise the no. of vlfs created.

---------------------------------------------------------------------

poratips Hall of Fame Points: 3994 More actions · Answer 5

Thanks Gail and George, appreciate your valuable tips.

I read that link and it has very useful information about T-log. I am just little confuse about setting up log size.

Is it need to be consider Database size also or only transaction?

From that link, I have to consider largest transaction, log back up size too, right?

poratips Hall of Fame Points: 3994 More actions · Answer 6

Thanks George.

Basically the size should be enough to handle your largest amount of log activity between log backups, so if your largest log backup is a couple of GB say, the log needs to be slightly larger than that. Growth factor should not be a %, actual MB value should be relative to size of log and minimise the no. of vlfs created.

Looking to last couple of days Log Backup:

From yesterday, I have largest Log Back up size is - almost 3 GB (6 PM)and before that - around 1 GB (4 PM)

Currently I have Log file set up: Initial Size is 3 MB and Growth is 10% Unrestricted.

Could you please guide me what should be the ideal size I should change it for Initial size and I will change it from % to What size?

Thanks

george sibbald SSC Guru Points: 104210 More actions · Answer 7

Initial size 3mb? Are you still shrinking the log? If so stop doing that.

Presuming 6GB is your largest log backup personally I would grow the log to 8GB in one chunk and set the growth factor at 128MB, then monitor the log for at least a week.

---------------------------------------------------------------------

poratips Hall of Fame Points: 3994 More actions · Answer 8

Thanks George.

I don't know but now it show from DBCC SQLPERF(LogSpace)

Log Size (MB)Log Space Used (%) Status

14198.8 0.64554840

george sibbald SSC Guru Points: 104210 More actions · Answer 9

ok, we seem to have gone from a 3mb log to a 14GB one overnight. Just change the growth factor but otherwise leave the logs alone. HAve you changed the backup frequency to be more often?

After a week run this script whilst connected to the database to get the largest log backup and decide a size from there

declare @dbname sysname

set @dbname = db_name()

print @dbname

select backup_start_date,backup_finish_date,backup_size/1048576 as 'size in MB'

from msdb..backupset

where database_name = @dbname and type = 'L'

---------------------------------------------------------------------

poratips Hall of Fame Points: 3994 More actions · Answer 10

Thanks once again.

I have changed log backup frequency to every hour and

I was looking log backup size on my backup drive and it shows for each hour after changing:

8 AM 7013 KB

9 AM24897 KB

10 AM45385 KB

11 AM43220 KB

12 PM419318 KB

I have just checked - select name,log_reuse_wait_desc from sys.databases and log_reuse_wait_desc shows Log_backup and also checking to VLfs, total files around 490 and 33 files with status = 2.

Also ran, DBCC SQLPERF(LogSpace):

Log Size (MB) Log Space Used (%) Status

14198.80.17199950

poratips Hall of Fame Points: 3994 More actions · Answer 11

Thanks George, I have changed the Log Backup Frequency to one hour.

I ran again DBCC SQLPERF(LogSpace) and it shows around 14 GB but I was looking Backup Log on my backup drive it also shows.

see the result:

Log Size (MB) Log Space Used (%) Status

14198.8 2.486978 0

I have just checked - select name,log_reuse_wait_desc from sys.databases and log_reuse_wait_desc shows Log_backup and also checking to VLfs, total files around 490 and 33 files with status = 2.

I have changed T-log backup frequency to every one hour, now backup log size is every hour is:

8 AM 7013 KB

9 AM 24897 KB

10 AM 45385 KB

11 AM 43220 KB

12 PM 419318 KB

Thanks

george sibbald SSC Guru Points: 104210 More actions · Answer 12

those are very small backups . Track it for at least a week and see if you get any huge log backups, these usually occur when re-indexing is done.

---------------------------------------------------------------------