September 28, 2016 at 10:22 am
I have SQL2008 database with some 100s of tables. Every 2 hours transaction log backup to tape is triggered. Just to save network bandwidth, i would like to backup the Trx logs only if there are changes in any of the tables. If no change from previous TLog backup, then no need to backup.
Can someone please help me with a script or guide me with a solution.
September 28, 2016 at 11:39 am
You would have to poll all the tables in the database, one-by-one, to determine if there are changes. That's a poor approach. You could read the log using DBCC LOG (or fn_dblog), but that's an undocumented procedure (both are), so I wouldn't recommend it for this. You could look at DBCC SQLPERF to see the space used, but that could work out to be the same even though data has changed. There aren't any easy ways I know to do this that will work well & consistently.
Log backups are pain-free operations. Just run the backup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 28, 2016 at 12:30 pm
The irony is it might be more overhead to check for the updates than to just do the log transfers.
In theory you might (should?) be able to use sys.dm_db_index_operational_stats to determine if any user tables were modified. You'd have to capture the values in that view after each backup, then compare them prior to the next backup. I am not saying this will work, just that it might.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 28, 2016 at 1:17 pm
If the delta for number of bytes written has changed, then you can assume that logs need to be shipped.
Also, the 2nd query will look at last update date for each table.
-- return the size in bytes and total bytes written for each database:
select db_name( database_id ) as database_name
, name as file_name
, type_desc
, size_on_disk_bytes
, num_of_bytes_written
from sys.database_files df
cross apply sys.dm_io_virtual_file_stats ( db_id(), file_id ) vfs;
-- return the last update date for each database and table:
select db_name( database_id ) as database_name
, object_name( object_id, database_id ) as object_name
, max(coalesce(last_user_update, last_system_update)) as last_updated
from sys.dm_db_index_usage_stats
group by database_id, object_id
order by database_id, object_id;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 28, 2016 at 1:20 pm
-- return the last update date for each database:
select db_name( database_id ) as database_name
, max(coalesce(last_user_update, last_system_update)) as last_updated
from sys.dm_db_index_usage_stats
group by database_id;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 28, 2016 at 1:53 pm
You'd still need to keep at least the history from the previous check, because an index/table could be dropped, which would remove it from the system views.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 28, 2016 at 2:16 pm
ScottPletcher (9/28/2016)
You'd still need to keep at least the history from the previous check, because an index/table could be dropped, which would remove it from the system views.
Aggregating object level statistics is problematic, but I'm thinking that comparing the delta of total bytes written for the database will work.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 28, 2016 at 2:54 pm
Eric M Russell (9/28/2016)
ScottPletcher (9/28/2016)
You'd still need to keep at least the history from the previous check, because an index/table could be dropped, which would remove it from the system views.Aggregating object level statistics is problematic, but I'm thinking that comparing the delta of total bytes written for the database will work.
True. Then again, the whole thing is rather problematic. I mean, I have code to check the differential bit maps that SQL itself uses for diff backups, but I only use it to rule out a diff backup if too many changes have been made, not to control whether a backup is done at all or not.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 28, 2016 at 3:40 pm
ScottPletcher (9/28/2016)
Eric M Russell (9/28/2016)
ScottPletcher (9/28/2016)
You'd still need to keep at least the history from the previous check, because an index/table could be dropped, which would remove it from the system views.Aggregating object level statistics is problematic, but I'm thinking that comparing the delta of total bytes written for the database will work.
True. Then again, the whole thing is rather problematic. I mean, I have code to check the differential bit maps that SQL itself uses for diff backups, but I only use it to rule out a diff backup if too many changes have been made, not to control whether a backup is done at all or not.
Nice touch.
I agree though. This is a bit of a sketchy solution to something that generally isn't a problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 3, 2016 at 7:20 pm
Chandu-212374 (9/28/2016)
I have SQL2008 database with some 100s of tables. Every 2 hours transaction log backup to tape is triggered. Just to save network bandwidth, i would like to backup the Trx logs only if there are changes in any of the tables. If no change from previous TLog backup, then no need to backup.Can someone please help me with a script or guide me with a solution.
Check the log_reuse_wait column of sys.databases.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 4, 2016 at 3:05 pm
I'd go with Grant's original advice: just do the backup. If there's no activity in the database since the last log backup, the amount written to your backup volume won't be much more than a placeholder. I've had a log backup running every ten minutes for eight hours on a database with no activity, and the log backup is only 243K since it was initialized at 7am.
Redgate has some free books here, I'd recommend Tony Davis and Gail Shaw's book on transaction log management and Shawn McGehee's on backup and restore.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 4, 2016 at 3:17 pm
To be fair, the number of databases would have an impact here. I've got servers with hundreds (and hundreds) of dbs. Even small files add up to a decent amount of space for that many dbs.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 4, 2016 at 3:24 pm
Very true, Scott. But the original poster was talking about hundreds of tables, not hundreds of databases. There could be a terminology mismatch here between what he said and what he meant, but as he hasn't been back since his original post, we don't know.
I hope we didn't scare him off! 😀
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply