SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactin Log Issue


Transactin Log Issue

Author
Message
poratips
poratips
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 666
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!
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23210 Visits: 13698
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
poratips
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 666
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210627 Visits: 46250
Please read through this - Managing Transaction Logs

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
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23210 Visits: 13698

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
poratips
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 666
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
poratips
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 666
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
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23210 Visits: 13698
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
poratips
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 666
Thanks George.
I don't know but now it show from DBCC SQLPERF(LogSpace)
Log Size (MB) Log Space Used (%) Status

14198.8 0.6455484 0
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23210 Visits: 13698
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'



---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search