October 11, 2011 at 3:51 pm
Hi,
In SQL Server 2008 R2, do we have the capability to set the log shipping backup job to run when log size reaches to a particular size Instead of every 15 mins or 5 mins?
Thanks
October 14, 2011 at 1:13 pm
not sure why you would want too
if the log isnt growing that big set it to 15 minutes
If you think is going to get that big set it 5 minutes
October 14, 2011 at 2:47 pm
Don't know too much about the functionalities in R2, but Agree with the prior post on "why", however if you want to add that check into the mix...
Write a simple TSQL function/check to determine the Log File Size. Add simple logic to your logshipping SQL Agent job that runs before the actual backup. If the log file size exceeds your threshold, run it. Otherwise it should skip it and run again at the next scheduled time.
Not the best solution, but would work.
Need to remember as well if you edit any aspect of the actual Log Shipping process via the GUI you'll overwrite the job and lose your "added" changes, so you'll need to add it back in.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 15, 2011 at 8:19 am
you could do that. Set up logshipping in the normal way then disable the backup job.
As stated above write a process to check log full percentage which then executes the sql agent log shipping backup job via sp_start_job, or alternatively use a performance alert which kicks off the log backup job.
BUT, I wouldn't do this. If the log fills rapidly the backups might not keep up and the threshold would be missed.
Back the log up on the normal timed process and have this procedure as a failsafe if you really feel the need for it.
---------------------------------------------------------------------
October 15, 2011 at 8:50 am
Yeah, I don't recommend it either George 🙂 Just saying it should work and I would never disable the backup job anyway, I'd leave that running on it's regularly scheduled interval (i.e. every 15mins), but including a check like this would give the ability to "skip" the log backup if it wasn't really needed.
You could use this code to check the sizes...
SELECT
db.[name] AS [DBName] ,
db.log_reuse_wait_desc AS [LogReuseWaitDesc] ,
ls.cntr_value AS [Log Size (KB)] ,
lu.cntr_value AS [Log Used (KB)] ,
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Log Used %]
FROM master.sys.databases AS db
INNER JOIN master.sys.dm_os_performance_counters AS lu ON
db.name = lu.instance_name
INNER JOIN master.sys.dm_os_performance_counters AS ls ON
db.name = ls.instance_name
WHERE db.state = 0
AND lu.counter_name LIKE 'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE 'Log File(s) Size (KB)%'
AND db.[name] IN ('YourDB')
AND db.recovery_model_desc = 'FULL'
ORDER BY ls.cntr_value DESC
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply