Log backup basesd on last commited LSN?

Question

Log backup basesd on last commited LSN?

pshaship

SSCertifiable

Points: 6125
More actions
September 27, 2010 at 12:07 pm

#147097

Hi,
We have SQL Server 2005 and configured log shipping and the database is generating log backup of >200 MB every 15 mins. But our Network team wants to keep the log file size <100 MB and there will be network performance issues if we send >200 MB log backup files.
And the Oracle Team is sending their log file when it's size reached 90 MB using Data guard. Similarly, in SQL Server 2005, can tell the database engine to take a log backup when the log is 90 MB then do the Transaction log backup using Min or Max LSN numbers?
Any one tried this method? Is that possible to achieve in SQL Server 2005?
or is it possible in SQL Server 2008?
Thanks

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

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

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 1

You might have configured the intervel for log backups in log shiping....try by to set it some what less interval from present interval

This might help you!!!

Thank You.

Regards,
Raghavender Chavva

pshaship SSCertifiable Points: 6125 More actions · Answer 2

I'm looking for other options rather than running the log shipping backup jobs in scheduled time intervals like 5 mins or 10 mins

Thanks

Derrick Smith SSChampion Points: 10833 More actions · Answer 3

I suppose you could run a job every minute or so that checks the log size on the database..something like this would check to see if the log is over 90mb:

IF (SELECT FILEPROPERTY(name, 'SpaceUsed') FROM sys.database_files

WHERE TYPE = 1) > 11520 --Multiply comparison number by 128, i.e. 90 x 128 = 11520

BEGIN

--backup log command here

END

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

another option is you can set up an alert in SQLAgent on log threshold. Set the log size at 100mb and get the alert to fire off the log backup job when it is 90% full.

Monitor this for a period to confirm it fulfills your needs though, because if the log backup takes longer to run than the log takes to fill by 90%, the threshold will be missed and the log continue to fill.

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

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 5

Log backups are configurred as part of the logshipping, So I think its not possible to setup a thresh hold or a job in Logshipping configuration.

If it is individual log backup schedule we can setup above suggestions.

Thank You.

Regards,
Raghavender Chavva

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

logshipping creates a SQLAgent job to backup the log. So set the alert to execute that job. The job itself can be disabled so it doesn't run to a timed schedule.

The OP will have to experiment to see which is the best solution for him.

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

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 7

george sibbald (9/28/2010)
logshipping creates a SQLAgent job to backup the log. So set the alert to execute that job. The job itself can be disabled so it doesn't run to a timed schedule.
The OP will have to experiment to see which is the best solution for him.

Here I got a query, what about the restore schedule ?

Can it be linked with backup job(Which will run depends on some scenarios not as per the schedules).

If database restore satisfies then it will work.

Thank You.

Regards,
Raghavender Chavva

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

the copy and restore jobs run on time schedules but are driven by 'what was the last log I copied\restored from logshipping directory so which is the next one to copy\restore', therefore they would still function correctly even when the backup job is not running at set times.

I would suggest the copy job be run on a frequent basis to limit the possible no. of backups to copy in one go.

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

paul.knibbs SSCoach Points: 15320 More actions · Answer 9

Question is, why would doing this help in any way? Whether you're sending 200Mb of data every 15 minutes, or sending 90Mb every 7 minutes when the log file gets that large, the bandwidth usage is exactly the same. The only way to reduce bandwidth usage would be to reduce the number of transactions occurring in the data.

Oliiii SSCertifiable Points: 5328 More actions · Answer 10

Add step to the backup job to compress the backup and a step to the restore job to decompress it.

Log backup are very easy to compress so you'll make everyone happy and you won't have to fiddle with asynchronous stuffs.

Try using 7Zip if you can since it's usualy better than the rest compression wise.

ourehman SSC Veteran Points: 267 More actions · Answer 11

If you are using SQL 2008 enterprise edition or SQl2008R2 you may choose compressions for backups if you are not already doing that. that may reduce the backup file size.