Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Transactin Log Issue Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 12:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
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!
Post #1444567
Posted Friday, April 19, 2013 1:36 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 5,976, Visits: 12,887
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.


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

Post #1444588
Posted Friday, April 19, 2013 3:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
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.
Post #1444619
Posted Friday, April 19, 2013 4:37 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 42,493, Visits: 35,562
Please read through this - Managing Transaction Logs


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1444637
Posted Friday, April 19, 2013 5:11 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 5,976, Visits: 12,887

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.


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

Post #1444650
Posted Saturday, April 20, 2013 6:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
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?
Post #1444771
Posted Sunday, April 21, 2013 9:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
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
Post #1444800
Posted Sunday, April 21, 2013 4:36 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 5,976, Visits: 12,887
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.


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

Post #1444827
Posted Sunday, April 21, 2013 9:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
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
Post #1444843
Posted Monday, April 22, 2013 7:33 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 5,976, Visits: 12,887
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'



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

Post #1444984
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse