January 12, 2015 at 4:00 am
Friends,
I am encountering a weird issue in SQL Server 2008 R2. A database sized 500 GB has the following backup schedule.
Sunday full backup.
Rest of the other days except Sunday differential backup.
My full back up size is around 500 GB while the differential backup sizes are around 400 GB.
I checked the maintenance plan and confirmed that I have done the right selections.
I am absolutely confused with this?
January 12, 2015 at 4:15 am
Do you do the index rebuild/reorganize after the full backup? If you do, then this can explain it. Also do you get the 400GB backup on Monday or does the differential backup at Monday is much smaller and it gets to its size at Saturday?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 12, 2015 at 4:17 am
Are you initializing each backup file or are you stacking them together into a single file?
"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
January 12, 2015 at 4:27 am
Adi Cohn-120898 (1/12/2015)
Do you do the index rebuild/reorganize after the full backup? If you do, then this can explain it. Also do you get the 400GB backup on Monday or does the differential backup at Monday is much smaller and it gets to its size at Saturday?Adi
No index rebuilding isnt scheduled at all. (I am not sure if application itself is doing something like this).
On your second point regarding differential backup sizes from Monday to saturday, frankly I havent observed it much but more or less, the size is similar throughout.
Grant Fritchey (1/12/2015)
Are you initializing each backup file or are you stacking them together into a single file?
Single file backup
January 12, 2015 at 4:34 am
Then the implication is that you have some process or other that's hitting 400 of the 500gb worth of pages on your database. Is there a nightly load or some other process that you're doing that would explain it?
"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
January 12, 2015 at 4:37 am
Grant Fritchey (1/12/2015)
Then the implication is that you have some process or other that's hitting 400 of the 500gb worth of pages on your database. Is there a nightly load or some other process that you're doing that would explain it?
I am sure its a heavy duty database which is operational most of the time.
How do I find this out?
Just to give you an example, I had a full backup which completed by 1:00 AM and ran a differential backup in sometime which completed by 7:00 AM.
Within this time frame, I am not sure as what all ran on the application size but the size of diff backup was almost 80-90% of the entire full backup.
January 12, 2015 at 4:41 am
Benki Chendu (1/12/2015)
Just to give you an example, I had a full backup which completed by 1:00 AM and ran a differential backup in sometime which completed by 7:00 AM.Within this time frame, I am not sure as what all ran on the application size but the size of diff backup was almost 80-90% of the entire full backup.
Then some process(es) ran which modified 80-90% of the database in that period. A differential backup contains all data pages modified since the full backup.
Are you sure there's no index rebuilds running? That's the usual culprit for large diff backups.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2015 at 4:45 am
I'm not aware of a process that will tell you what changed a specific page on the database. I think you're just going to have to go with standard activity monitoring using trace or extended events to capture the queries run against the system.
From the sounds of things, you've got something going on there. That's extremely abnormal for a database to have 80% of the pages modified within 6 hours or so just on standard OLTP. I still think you must have an index maintenance script or load process running. Or, your application is somehow modifying all the database in short order. The last just doesn't make sense.
"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
January 12, 2015 at 4:49 am
GilaMonster (1/12/2015)
Benki Chendu (1/12/2015)
Just to give you an example, I had a full backup which completed by 1:00 AM and ran a differential backup in sometime which completed by 7:00 AM.Within this time frame, I am not sure as what all ran on the application size but the size of diff backup was almost 80-90% of the entire full backup.
Then some process(es) ran which modified 80-90% of the database in that period. A differential backup contains all data pages modified since the full backup.
Are you sure there's no index rebuilds running? That's the usual culprit for large diff backups.
I am not running any index rebuilding through a job atleast.
Could the application folks be doing something?
January 12, 2015 at 4:58 am
Yes. They could be. As I said, putting activity monitoring through trace or extended events will let you know what they're doing.
"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
January 12, 2015 at 5:02 am
When you rebuild a table, SQL Server modifies the value of column modify_date in sys.objects so it will have the value of the time that the table was rebuild. Assuming that you don't modify the tables on a daily basis can you run this query and check if there are any tables that were modified during Sunday? If you'll see many of them, it means that there is a very good chance that index maintenance is being done on the database.
select * from sys.tables order by modify_date desc
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 12, 2015 at 5:06 am
Grant Fritchey (1/12/2015)
Yes. They could be. As I said, putting activity monitoring through trace or extended events will let you know what they're doing.
ok...Would I be able to track these changes in the error log? may be through a trace file?
January 12, 2015 at 6:12 am
Benki Chendu (1/12/2015)
Grant Fritchey (1/12/2015)
Yes. They could be. As I said, putting activity monitoring through trace or extended events will let you know what they're doing.ok...Would I be able to track these changes in the error log? may be through a trace file?
Error log, no. Trace file, absolutely. Just make sure you're cautious about the events and columns you select for tracking. RPC completed and SQL Batch completed are generally OK. If you try to capture statement completion though, you might run into trouble.
"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
January 12, 2015 at 6:16 am
And don't use the Profiler GUI.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2015 at 7:30 am
Adi Cohn-120898 (1/12/2015)
When you rebuild a table, SQL Server modifies the value of column modify_date in sys.objects so it will have the value of the time that the table was rebuild. Assuming that you don't modify the tables on a daily basis can you run this query and check if there are any tables that were modified during Sunday? If you'll see many of them, it means that there is a very good chance that index maintenance is being done on the database.
select * from sys.tables order by modify_date desc
Adi
I see some 12 tables being modified between the full backup and the differential backup taken in the next 5-6 hours but none of them seem to be consuming any space on the disk.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply