Prevent TLog backup if no new transactions? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Prevent TLog backup if no new transactions?

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-02-27 : 06:17:02

Can I check if the TLog has anything to backup, or not, and not create the backup if there is nothing to do?

Our sites are completely quiet during the night, and some sites get deactivated - but continue to make Tlog backups, which is a bit of a waste really!

(This follows on from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140513

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-27 : 07:18:59

I suppose you could read the tran log (select from fn_dblog) and check that there's been no user-stuff between the last log backup and now. Won't be trivial, there'll be stuff like checkpoint, ghost cleanup, etc that may be running (and logging) even if there's no user activity.

Personally I'm not fond at all of irregular log backups. If it's a disaster situation, I don't want to be worrying about whether or not there was a 1:15 log backup or not, or whether there was an ad-hoc one sometime between the regular ones when I'm sorting out backups that need restoring.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-02-27 : 12:34:51

I think I may have the second point covered. We name our Backup files with Database name + Date/Time, my plan is to add a sequence number too - so it would be easy to see that all files were to hand.

I was hoping that deducing whether a backup was needed, or not, might be easier though ...

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-02-27 : 12:43:42

I agree with Gail. Also, the .trn file will be quite small if there wern't any transactions...

But, if I were going to implement that, I'd probably check a table rather than the t-log itself. Your script can select the max(id) or max(date) from your most volatile table to see if a log backup is in order.

Why not just change the backup schedule though...could run your log backups from 6 am til 11 pm or something...or every 15 minutes during the day but only hourly at night etc.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-02-27 : 12:44:52

more i think of it, i believe you just want an excuse to poke around the log file with fn_dblog

Kristen
Test

22859 Posts

Posted - 2010-02-27 : 13:06:33

"more i think of it, i believe you just want an excuse to poke around the log file with fn_dblog"

Foiled!

Yes, I could easily change the log scheduled to log-less at night (have considered it in fact)

However, its a little harder for me to do that on a database-by-database basis; we have databases that are created, and then have very little, or even NO, traffic ... but they get the same every-15-minutes TLog backups. Its a huge number of files, but not very much total size. But it IS a lot of files; and log entries in MSDB.

Our DB backups are automated. If I create a new DB then within 15 minutes it will be added to the backup system (in our Admin DB) with a default backup strategy (Full once a day, Tlogs every 15 minutes [unless SIMPLE R.M.]). I can change that strategy ... but I would prefer that it was reactive to what the database actually generated as Tlogs, rather than someone having to explicitly do something.

I might create a DEV database, and it sit there doing diddly-squat 90% of the time - and then get a whole heap of data imports that sky-rocket the TLog.

So ... step 1 would be to add some "different backup scenarios" - e.g. to have the TLogs change to hourly during the night.

(We already have some special cases - DB names that start with "RESTORE_" don't get backed up at all - so we can do a temp-restore just to extract some Oops-deleted-data

without initiating a multi-GB backup chain ... and we have DB Suffixes for _DEV, _TEST, _LIVE, _TEMP that can cause other default behaviours (No automated INDEX rebuilds on TEST / TEMP))

Step 2 would be to see if I can credibly detect that a TLog has enough data to make it worth backing up.

Kristen
Test

22859 Posts

Posted - 2010-02-27 : 13:07:16

P.S. Could I use the "Tlog is 75% full" alert?

Perhaps a "1% full alert" that flags the database for backup on the next 15 minute run?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-27 : 13:22:49

quote:
Originally posted by russellWhy not just change the backup schedule though...could run your log backups from 6 am til 11 pm or something...or every 15 minutes during the day but only hourly at night etc.

That's probably the better way. Watch out for those index rebuilds though.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-02-27 : 13:37:00

I wonder if the "Tlog is getting full" alert would help with the Index Rebuilds - let the 75% full alert cause an emergency TLog backup.

We have some (string + chewing gum, I'm afraid) code in our Index Rebuild routine that increases Tlog backups to every 2 minutes in order than the LDF does not get extended. But backing up on the 75%-full alert would be a better solution - we currently get lots of "empty" Tlog backups during Index rebuilds where SQL is "thinking" or just making one large transaction perhaps.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-28 : 03:21:45

quote:
Originally posted by Kristen

I wonder if the "Tlog is getting full" alert would help with the Index Rebuilds - let the 75% full alert cause an emergency TLog backup.

Maybe. Depends. If the transaction log is 75% full because a single index rebuild is holding an open transaction and using 75% of the log, a log backup will do nothing.

Why not switch to bulk-logged for the duration of index rebuilds?

--
Gail Shaw
SQL Server MVP

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-02-28 : 15:06:49

how 'bout something like this?


Declare @last_bkp_lsn int
Declare @current_lsn int
DECLARE @LSN NVARCHAR(46)
Declare @tbl table (id int identity(1, 1), i varchar(10))
DECLARE @stmt VARCHAR(256)

SELECT	top 1 @last_bkp_lsn = Convert(int, Left(Convert(nvarchar(32), last_lsn), 8)) from msdb..backupset where database_name = db_name() and server_name = @@SERVERNAME order by  backup_start_date desc
print	@last_bkp_lsn

SET @LSN = (SELECT TOP 1 [Current LSN] FROM fn_dblog(NULL, NULL))

SET	@stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 1, 8) + ' AS INT)'
INSERT	@tbl EXEC(@stmt)
SET	@stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 10, 8) + ' AS INT)'
INSERT	@tbl EXEC(@stmt)
SET	@stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 19, 4) + ' AS INT)'
INSERT	@tbl EXEC(@stmt)

SET	@current_lsn = Convert(int,
	(SELECT i FROM @tbl WHERE id = 1) + '' + (SELECT i FROM @tbl WHERE id = 2) + '' + (SELECT i FROM @tbl WHERE id = 3)
)
PRINT	@current_lsn

IF	@current_lsn > @last_bkp_lsn
BEGIN
	print 'execute log backup here'
END
ELSE
BEGIN
	print 'Don''t execute log backup'
END

I used the sample here (http://killspid.blogspot.com/2006/07/using-fndblog.html) to determine the last_lsn. Rest is mine.

Might just want to use the Right(lsn , 4) to check...not sure.

anyway, it's a start.

Kristen
Test

22859 Posts

Posted - 2010-02-28 : 16:46:10

Just off to bed and then away for two days, so it will be a little while before I can take a look.

Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-02-28 : 17:06:12

see ya in a couple days. have a safe journey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources