Possible problem with differential backups

Hi all,
I have a 170GB OLTP database that only processes INSERTs and SELECTs,
no UPDATEs or DELETEs. I add about 1.8GB of data every day, as
verified with my transaction log backups taken every 15 minutes. A
full database backup is created every Friday at 10PM and then
differential backups are created Sa, Su, Mo, Tu, We & Th at 10PM. The
problem I have is that the differential backup file from Saturday
night (the 1st one that is created) is always about 38GB. The
subsequent differential backups add about 1.8GB of data until I return
to Friday night and the process begins anew.

At first I thought that I had some job that was running between Friday
and Saturday, but I cannot find anything. Reviewing a SQL Profiler
trace for that 24 hour period actually showed that the weekend is the
slowest period of data ingestion. To test this I performed a full
database backup at 8AM on Friday morning and then waited 10 minutes to
perform a differential backup. The differential backup file was 8GB.

I opened a case with Microsoft and my tech has stuck with the solution
that this is normal and that I really must have 38GB worth of extent
changes on Saturday and that I must have had 8GB worth of extent
changes during my 10 minute test period. This just doesn't seem
correct to me.

He told me that the differential backup process only keeps track of
extent changes since the last full backup. I agree with this. However,
the example he used does not seem correct.

Given a database with ten extents, all marked as "0" at the time of
full backup/baseline:
0000000000

Let's say that three of those extents are changed to "1":
0001100100

the first differential backup will record those three changes.

Now if those two of the "1"s change back to "0"s:
0000000100

the next differential backup will be smaller that the previous one
because only one extent has been changed from the baseline. I disagree
with him. I believe that there have been five extent changes in total
and just because it happened to change back to the original data it
doesn't matter. I have not found any documentation supporting or
disproving this theory.

Can anyone provide some additional insight?

Thank you.
0
JoshBond
9/8/2010 5:30:35 PM
sqlserver.server 1327 articles. 0 followers. Follow

7 Replies
1213 Views

Similar Articles

[PageSpeed] 46

JoshBond (joshsackett@gmail.com) writes:
> The problem I have is that the differential backup file from Saturday 
> night (the 1st one that is created) is always about 38GB. The subsequent
> differential backups add about 1.8GB of data until I return to Friday
> night and the process begins anew. 

The typical thing would be that you have a reindex job running in that
time frame. However:

> To test this I performed a full database backup at 8AM on Friday morning
> and then waited 10 minutes to perform a differential backup. The
> differential backup file was 8GB. 

I assume that you are not running reindexing jobs that time of day.

Anyway, just to make sure that I get this correctly.

1) You take a full backup.
2) You take a diff backup. This backup is 38 GB in size.
3) You take a new diff backup one day later. This backup is 39.8 GB.
4) And next is 41.6 GB and so on.

Is that right?


> Given a database with ten extents, all marked as "0" at the time of
> full backup/baseline:
> 0000000000
> 
> Let's say that three of those extents are changed to "1":
> 0001100100
> 
> the first differential backup will record those three changes.
> 
> Now if those two of the "1"s change back to "0"s:
> 0000000100

No, that does not make sense. How would SQL Server knows that the
data has reverted back to the original state? 

I don't use differential backups much, but I don't see why the first
diff backup is that big. That's a little funny.

What recovery mode is the database in? Full or Bulk_logged? Are there
any special features in SQL Server you use like replication,full-text
etc?


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
9/9/2010 8:21:12 PM
By the way, would it be possible for you to post the exact BACKUP command
that you use?
-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
9/9/2010 8:22:05 PM
> 1) You take a full backup.
> 2) You take a diff backup. This backup is 38 GB in size.
> 3) You take a new diff backup one day later. This backup is 39.8 GB.
> 4) And next is 41.6 GB and so on.
> Is that right?

That is correct.

I am not running any reindex jobs during that time period. In fact, no
jobs run between the FULL backup and the first DIFF.

The statements I use are:

BACKUP DATABASE [MyDatabase]
   TO DISK = 'F:\Server1\MyDatabase_FULL_YYYYMMDD.BAK'

-- Time elapses.
BACKUP DATABASE  [MyDatabase]
   TO DISK = 'F:\Server1\MyDatabase_DIFF_YYYYMMDD.DIF'
   WITH DIFFERENTIAL

Also, I typically use RedGate's SQL Backup software. But I have ruled
that out as the problem because the overly large DIFF backup happens
whether I use native SQL backups or RedGate's software.
0
JoshBond
9/10/2010 4:10:17 PM
One possibility is you have something which updates lots of rows, and then 
keeps updating those same rows over again (maybe an UPDATE with an incorrect 
WHERE clause).  That would explain why the diff grows very fast after the 
full backup, but then since it is updating the same rows all the time, the 
diff doesn't grow much, but gets large again very soon after the next full 
backup.

One thing you could try (since it seems to only take about 10 minutes to see 
at least some of this) is to run a SQL trace for a while.  Maybe even only 
just a few minutes if you find it affects performance too much.  In that 
trace you want to include events SQL:StmtCompleted and SP:StmtCompleted. 
Include the column IntegerData.  What is put in IntegerData depends on the 
event, but for those two events, IntegerData is the number of rows affected 
by that statement.  By looking at that column and the Writes column, you may 
be able to see if you have a SQL UPDATE that is affecting more rows than it 
should.

Tom

"JoshBond" <joshsackett@gmail.com> wrote in message 
news:8c683c50-fee9-47fb-8ad2-55324f077e73@m35g2000prn.googlegroups.com...
>> 1) You take a full backup.
>> 2) You take a diff backup. This backup is 38 GB in size.
>> 3) You take a new diff backup one day later. This backup is 39.8 GB.
>> 4) And next is 41.6 GB and so on.
>> Is that right?
>
> That is correct.
>
> I am not running any reindex jobs during that time period. In fact, no
> jobs run between the FULL backup and the first DIFF.
>
> The statements I use are:
>
> BACKUP DATABASE [MyDatabase]
>   TO DISK = 'F:\Server1\MyDatabase_FULL_YYYYMMDD.BAK'
>
> -- Time elapses.
> BACKUP DATABASE  [MyDatabase]
>   TO DISK = 'F:\Server1\MyDatabase_DIFF_YYYYMMDD.DIF'
>   WITH DIFFERENTIAL
>
> Also, I typically use RedGate's SQL Backup software. But I have ruled
> that out as the problem because the overly large DIFF backup happens
> whether I use native SQL backups or RedGate's software. 

0
Tom
9/10/2010 6:03:36 PM
Are you sure you are not backing up the first DIFF into the same file as 
your full backup?

"JoshBond" <joshsackett@gmail.com> wrote in message 
news:8c683c50-fee9-47fb-8ad2-55324f077e73@m35g2000prn.googlegroups.com...
>> 1) You take a full backup.
>> 2) You take a diff backup. This backup is 38 GB in size.
>> 3) You take a new diff backup one day later. This backup is 39.8 GB.
>> 4) And next is 41.6 GB and so on.
>> Is that right?
>
> That is correct.
>
> I am not running any reindex jobs during that time period. In fact, no
> jobs run between the FULL backup and the first DIFF.
>
> The statements I use are:
>
> BACKUP DATABASE [MyDatabase]
>   TO DISK = 'F:\Server1\MyDatabase_FULL_YYYYMMDD.BAK'
>
> -- Time elapses.
> BACKUP DATABASE  [MyDatabase]
>   TO DISK = 'F:\Server1\MyDatabase_DIFF_YYYYMMDD.DIF'
>   WITH DIFFERENTIAL
>
> Also, I typically use RedGate's SQL Backup software. But I have ruled
> that out as the problem because the overly large DIFF backup happens
> whether I use native SQL backups or RedGate's software. 

0
Jeffrey
9/10/2010 8:00:53 PM
Tom Cooper (tomcooper@comcast.net) writes:
> One possibility is you have something which updates lots of rows, and
> then keeps updating those same rows over again (maybe an UPDATE with an
> incorrect WHERE clause).  That would explain why the diff grows very
> fast after the full backup, but then since it is updating the same rows
> all the time, the diff doesn't grow much, but gets large again very soon
> after the next full backup. 

Interesting idea. We'll see what Josh says.

> One thing you could try (since it seems to only take about 10 minutes to
> see at least some of this) is to run a SQL trace for a while.  Maybe
> even only just a few minutes if you find it affects performance too
> much.  In that trace you want to include events SQL:StmtCompleted and
> SP:StmtCompleted. Include the column IntegerData.  What is put in
> IntegerData depends on the event, but for those two events, IntegerData
> is the number of rows affected by that statement.  

There is also the RowCounts column.

Another approach would be to use DBCC PAGE to look at the page with the
DIFF map, and see to which table(s) the updates exents belong to. A bit
tedious though, and not to be done without one Kalen Delaney's books beside
you.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
9/10/2010 8:32:42 PM
On Sep 10, 1:00=A0pm, "Jeffrey Williams" <jeff.williams3...@verizon.net>
wrote:
> Are you sure you are not backing up the first DIFF into the same file as
> your full backup?
>

Good idea. However, my first backup is 170GB and the first diff is
38GB. Different files.
0
JoshBond
9/10/2010 10:31:46 PM
Reply:

Similar Artilces:

Problem with CSocket
Hi all. I wrote an MFC application to make an HTTP file transfer between a client and a server. The application receives as input the list of servers to call (in this case, servers are remote devices with an embedded web server), and one by one it sends the file and closes the connection. The sequence of the operations made is this: 1) Dial-up call to device X 2) Open socket on port 80 3) HTTP POST of the file to the device 4) Wait for the response, and analysis to understand if the transfer has gone well or not 5) Open a new socket (after the response, the old socket is closed) 6) HTTP ...

Headers and footers problems
My footer is huge! How do I size it down? Make sure that Vertical Alignment is set to "Top" (although this usually affects the header rather than the footer). -- Stefan Blom Microsoft Word MVP "Quarrithbrakka" <Quarrithbrakka@discussions.microsoft.com> wrote in message news:5EFE175F-1305-4101-A498-CC8A9A6A829D@microsoft.com... > My footer is huge! How do I size it down? And if that's not the issue, display nonprinting characters and make sure the footer doesn't contain extra, empty paragraphs. -- Suzanne S. Barnhill Micros...

CRM Server Installation Problem!!
I am having a problem installing MSCRM 1.2. I am trying to use the demo version of CRM, the Adventure Works Cycle option. My configuration consists of a Domain Controller running Win 2000 Server SP4 . I am trying to install MSCRM on a member of the domain running Windows 2000 Server SP4. There is a DNS Server on the DC which is integrated with AD, and a SQL Server 2000 running on the Win 2K machine. The install errors out when it is configuring ASP.NET with the message: Setup was unable to install Microsoft CRM Server. Setup was unable to provision your organization. Setup was unable to crea...

Problem: Signature appears in calendar events
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Email Client: pop I just installed Entourage 2008, and I'm having an issue with my email signature appearing in the notes section for my calendar events. It's an annoying problem, because any notes I make for the calendar event appear below the signature. I can't find a way to turn this off, and if I try to delete the signature it just reappears. <br><br>The signature that's appearing is for a POP email account (not my default one). I should also note that when I installed Entourage 2008 I added an Exchange ...

Mail problem #3
I have a problem getting my email. When I click on outlook express it goes to OLE, but I can not retrieve my mail. The icon in the upper right hand corner of the screen continuously spins and then after a minute or so Outlook Express times out and asks me if I want to try again. I can not get back to getting mail like I used to do. I would appreciate your help and advice on how to get this problem solved. Thanks for your help. Try posting this in an Outlook Express news group - this is not one of them. Outlook is a part of Microsoft Office and is what this group supports. Outlook Expres...

Merge Cell Problem
I have created a new worksheet which is very basic with no formulas. I want to merge some cells however the Merge' button on the tool bar is grey'd out. It does not function. I have never come across this before and do not know how to fix. Does anyone have any suggestions please. Any reply appreciated. Les Jetrascal Sounds like your worksheet is protected. Check tools>Protection, if there is an option to "Unprotect" you have a protected worksheet. You need to have an unprotected sheet to make this kind of change. Another option rather than merging ...

EXCEL EXPORT PROBLEMS
I am setting up a catalogue for someone who has given me their price/product info. setup in EXCEL tables. They want to be able to make the necessary updates to their EXCEL tables and then send to me for updating future catalogues. I have tried to import into both Corel Draw and Word with no success, either the info. is non-editable once imported or it is converted into a mess. Can EXCEL files be imported/exported at all into any other layout programs while still maintaining the columns/tables set up in the original? I'd appreciate your help, thanks! ...

Problems with a workbook that thinks it's shared
I have a spreadsheet that is used as a template to generate other spreadsheets that are then sent out to other users to fill out and then send back. However some users have reported errors occuring within the spreadsheets that get generated, albeit intermittently. What's weird is that the generated spreadsheet shows all the symptoms of being shared (the code falls over when it tries to unprotect a sheet for example), but going into the 'share workbooks' option shows it as unshared. However sharing it and then unsharing it seems to fix the problem. There is this workaround o...

Problem with CString::Delete
CString str = "12345"; // this works fine str.Delete(2, 3); // this does not result in an access violation but also does not work str.Delete(2, 4); // both of these result in an access violation str.Delete(2, INT_MAX - 1); str.Delete(2, str.GetLength()); Per MSDN, if nCount is longer than the string, the remainder of the string will be removed. While this may have been the intent, it obviously does not work. Is this a known issue? I can use Left() just fine, but I was more curious as to what was up with Delete(). The nBytesToCopy variable in Delete() is negative right befor...

Excel Startup Problem
Every time I starts excel 2000, I will get this message : Compile error in Hidden Module: AutoExecNew When ever I want to quit excel, I'll get another message: Compile error in Hidden Module: DistMon I am using Office 2k and Windows XP. Please help. thanx -- :) Hi Rya05, > Every time I starts excel 2000, I will get this message : > Compile error in Hidden Module: AutoExecNew > > When ever I want to quit excel, I'll get another message: > Compile error in Hidden Module: DistMon This is probably caused by an add-in file called pdfwrite.xla. Search your HD for th...

Exchange/Outlook archiving problem with space restrictions
I've recently read the following kb article that states archiving is broken when you've reached your space limit on an Exchange server. We are running Exchange Server 2003 SP2 and using Outlook 2003 clients. Does MS have any plans on fixing this issue? We have a lot of users hitting their limits and then not being able to archive items. http://support.microsoft.com/default.aspx?scid=kb;en-us;297226 ------------------------------ Jeff Wommer Sr. Systems Technologist Northern Michigan University On Wed, 02 Nov 2005 07:36:47 -0800, Jeff Wommer <jwommer@nmu.edu> wrote: >I&#...

Hotmail Problem Please Help
I am running Exchange 2003 Standard on a Windows 2003 Server. I also have ISA 2000 on the same box. Everything is working fine. The problem I am having is getting mail out to a hotmail address. When I send then mail, I see it sits in the Queue for less then a minute then disappears. It never makes it to the Hotmail address nor do I get any return errors back in my client indicating the mail never made it to its destination. On Sun, 13 Nov 2005 15:51:04 -0800, "Kevin Mc" <Kevin Mc@discussions.microsoft.com> wrote: >I am running Exchange 2003 Standard on a Windows 200...

Problem with HtmlView
Hi. I got some problems with HtmlView. I need your help. I got a form (MFC). It content a Html page. So, I just want to cache it into local (like Save As...). I used URLDownloadToFile(NULL, szURL, szFileLoacl, 0, NULL) function. But, if szURL like http://.../img.jpg, it worked, and if szURL like file:\\\.../img.jpg, it didn't work.What happen? And one more thing, when I load images into local successful. How to let Html file know the source image in local which I just load? I hope see your ideas soon. Bye everybody. Happy day! "Phuc Dam" <tidolili@yahoo...

Problem with chart creation
Sheet1 was populated with column arrays in Range("A8:J50"). I wanted to creat a chart for column array as specified in cells A1, A2, A3 as rowbegin, rowend,and columnofinterest respectively. Soon after the chart was created all the values in Cells(*, *) were lost, and the macro ceased to function properly. Can you help me? Sub Creat_a_chart() Dim rowbegin, rowend, columnofinterest As Double Cells(1, 1) = 8 ' as given example Cells(2, 1) = 20 ' as given example Cells(3, 1) = 3 ' as given example rowbe...

Decomissiong E2k SMTP Problem
I installed a new E2k3 server sp2 on a Server 2003 machine. I had a server 2000 E2k server handling email. If I stop the SMTP service on the old server, mail does not get through. Any idea's? TIA, Eric Yes, the connectors are probably homed to the Ex2k server. http://support.microsoft.com/default.aspx?scid=kb;en-us;307917 This is a good guide to removing the old 2k server - point 9 briefly discusses moving the connectors. Also, you will need to check your firewall allows mail to be sent out from and in to the new Ex server. "Eric K" <EricK@discussions.microsoft.co...

Exchange 5.5 problem with NDR
Hello, I've got a problem with my Exchange 5.5 Server. I've applied the hotfix n� 837794 with allow to control the generation of the NDR. http://support.microsoft.com/default.aspx?scid=kb;EN-US;837794 I put in the registry, the DWORD value SuppressNDROptions = 1. No more NDR !!! But it's not the behavior I've expected. In fact, all I wanted was to block and delete the messages in the sending queue with "<>" as originator and "host unreachable" status when trying to send notification... Any idea ? Thanks Franck. Filtering for Blank Senders i...

Excel Book1 problem
How i can fix my excel problem.? When ever i run excel.exe i got book2,where it suppose to be book1 Have you mistakenly saved a workbook as Book2.xls and put it in your startup folder? Regards, Peo Sjoblom "unix" wrote: > How i can fix my excel problem.? > When ever i run excel.exe i got book2,where it suppose to be book1 > > No ...

pulic folder restore problem
Hello all, we are currently migrating from Exchange 5.5 to Exchange 2003. Things mostly went fine with one exception: Right after we deleted the last Ex5.5 server from systemmanager, but before the first (successful) brick level backup of the new server, a user (USER_X) managed to cause maximal harm in the worst moment by deleting a complete folder from public folders. (The following actions all take place on the old Exchange5.5 server) We were able to restore a version of the PUB.EDB of the Ex5.5 server from the time when it still held a replica of the folder in question. And we were able t...

Problem viewing Excel 2003 Pivot Chart fields in Excel 2007
When I’m running Excel 2007 and I’m viewing a chart in a file created in Excel 2003 using the ‘Pivot Chart and Pivot Chart Report’ functionality, I’m unable to see the Pivot Chart (drop down) fields anywhere on the Pivot Chart. This happens even when the file created in Excel 2003 is ‘converted’ to 2007 when it is opened. -- Ronny B Hi, When the pivot chart is selected try choose PivotChart Tools, Analyze, and turn on the Pivot Chart Filter. -- Thanks, Shane Devenshire "ronny B" wrote: > When I’m running Excel 2007 and I’m viewing a chart in a file created in > ...

Common Dialog Problem
Awesome! Application.GetOpenFilename seems to have taken care of the problems and I won't even have to change too much of my code. Thank you, Jim!!! Nny :) PS I was only using the Common Dialog control because I didn't know of Application.GetOpenFilename <smile> ...

SQL Select Problem
Hi all I have a little problem with a select. I would need to have the most simple solution :-) I have the following table: [FK_Candidate], [Key], [Value] 1 'Profession' 'Informatiker' 2 'Profession' 'Informatiker' 1 'Source' 'Quelle1' 2 'Source' 'Quelle2' I need to get all candidates with Profession = Informatiker AND Source =...

DDE problem between Word and Excel
For years I have been using Word programs (Office 2000 series) to access Excel name and address data files to produce letters, mailing labels, etc. Now, when executing a Word merge program, I am all of a sudden receiving the message, "Waiting for Microsoft Excel to Accept DDE Commands." The computer then hangs up, as if in a loop. This is true for all my Word merge programs. Nor can I create a new merge document in Word, because the program hangs up when attempting to access the Excel name and address file. What has happened? Thanks in advance. Gordon Biggar Houston, Texas P....

Excel formula problem SUMPRODUCT
Hi Guys, I have a problem with a formula adding up rows against 2 parameters. I am trying to add up rows on a report that 1) show that the actio against it is "open" and its target completion date is overdue. I have tried the following formula but it will not work. =SUMPRODUCT(--(H5:H47="OPEN")*(G5:G47="<TODAY())) This formula works if I substitute the date part with another tex parameter but will have nothing to do with dates even if I put in specific date that I know matches one from the column, I still get 0 Any ideas. Thanks in advance And -- Message ...

Office Ultimate 2007 installation problem.
I bought Office Ultimate 2007 on the Microsoft site and downloaded it. (It was with student discount. I am not sure whether I am eligible to tech support). My computer has Windows XP. Has 2GB of RAM memory and 2.8 GHz speed. After downloading application from Microsoft site I attempted to install it. Installation got stuck on the "Error 1309 - Error reading from file e:\sku011.cab. Verify that the file exists and that you can access it. Setup failed. Rolling back changes" E:\ is on my computer DVD drive and I was installing it from a file so it should not look ...

Outlook problem #37
The sent time on incoming messages, from different time zones, is changed to our local time when it gets to Outlook. We are using Exchage 2003 for our email server. Does anybody could please help us with this? Thanks. ...