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
872 Views

Similar Articles

[PageSpeed] 58

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:

Access 2007 Compact problems
I've just installed SP1 for Access 2007. Before the Service Pack, Compact and Repair would create a new compacted file called Database.mdb but wouldn't rename it to the name of the original file (which is what happened in Access 2003). Now it doesn't create a copy at all! Note that this only happens if you are Compacting a file on a network drive. It works fine on a local drive (but since all my work is on a network drive, it's a hassle to say the least) I suppose this is a fix for the intermittent problem whereby Compact would sometimes delete the file altogether (thankfully...

Problem with the pst file
Hi there, I'm facing a problem in opening a pst file(zile size 2.1GB). I have scanned the file using scanpst.exe several times but the problem still persists. I have also used the tool which minimises the size of the file..but no luck. I really don't know why this problem occurs..is it due to the file size..or one of the mails has got corrupt. Also if by change if iam able to retrieve the file can i export the same to Outlook Express so that the problem doens't arise again..? Thanks in advance. Regards. Avinash Outlook 97-2002 pst-files can only be up to 2GB. You can r...

Configure Exchange 5.5 for backup to another domain
Hello, Is it possible to configure Exchange 5.5 to be a back mail server for another domain as well as it's host domain. Example... mx record has 10 -> firstdomain.com 20 -> backupdomain.com If firstdomain.com's exchange server goes down is there a way to configure backupdomain.com's exchange server to accept mail for firstdomain.com and send it to the server when it comes back online? But it cannot stop accepting mail for backupdomain.com. Thanks. Chris DeFreitas Yes, it is posible. On Exchange server for backupdomain, in Exch Admin prog, IMC props, Routing ta...

Is it possible
I would like to know is it possible to make million dollar in the internet, if you have any suggestions, write on my forumhttp://isitpossible.truemillion.comMark...

Outlook Client Problem
i had installed outlook client to a user that was local administrator. when the user is no longer a local administrator the "outlook client add in" is disabled. does the user have to be a local administrator? ...

Problem with '04 upgrade
I put 2004 money on my computer and it locked me out. I can't even open it. It says I dont have the appropriate permission to access the item. Can anyone help? >-----Original Message----- >I put 2004 money on my computer and it locked me out. I >can't even open it. It says I dont have the appropriate >permission to access the item. >Can anyone help? >. >I have had noting but problems with 2004 and returning to 2002 deluxe. MS has to make improvements before I will purchase anymore software as far as money is concerned. ...

Personal Folders in OWA
Hello, I have just enabled OWA on our Exchange 2003 SP2 through ISA Server 2004. Is there any way to access personal folders in OWA? Thanks in advance. If by personal folders you mean .pst files, the answer is NO! Sorry. C C wrote: > Hello, > > I have just enabled OWA on our Exchange 2003 SP2 > through ISA Server 2004. Is there any way to > access personal folders in OWA? > > Thanks in advance. > > "C C" <someone@atsbcglobal.net> wrote: >I have just enabled OWA on our Exchange 2003 SP2 >through ISA Server 2004. Is there any way...

Problems with backupexec 10d second storage group info. store restoration.
We have three mail box mailbox groups, we backup the information stores using backup exec 10d I have confirmed that mailbox permissions are the same for all three. But the problems is that when I try to restore using the recovery storage group I am successful using backup exec for the first storage group but it does not work for the second or third. I have dismounted the first storage group and deleted the files and have added the second storage group to the recovery storage group but I get the below error. The error from backup exec is: Final error: 0xe000ff00 - Unable to restore some Mi...

Downloading Statements Directly Not Possible
I have Money 2002 installed on an Win XP Pro SP1 machine. Something happened and I probably did it, that causes the statement downloads not to go directly to the account. Rather they want to be saved in a file and then opened into Money. I then have to delete the download. I have tried several things in order to fix this: 1. I verified the file associations of OFC, OFX were correct. They were. 2. Lowered all my security settings in IE. Even put banks into Trusted site zone. No go. 3. Made new accounts. Didn't help. 4. My IE cache is large (500MB) and regularly cleaned out. 5. I made s...

CRM 3.0 Client for Outlook Install Problem
I've had this running OK in Outlook 2007 (upgraded from 2003). Then had to uninstall it, uninstall Office 2007 and manually remove Office 2003 to start over installing 2007 from scratch. Now the client install chokes because it can't recognize Outlook 2007 (which runs fine by itself). From the log: 14:54:46| Error| CRegKey:Open failed.(error = 5) (util.cpp:GetRegKeyValue:236). 14:54:46| Error| Check OutlookInstallationValidator : Failure: Setup failed to determine whether a supported version of Microsoft Outlook is installed. Does this install work when it's not upgrading ...

URGENT! Adress book problem...
I have created a User with a mail box in Exchange 2003. When I configure Outlook I receive a message stating that the said User does not exist in the address book however this User does exist in the Active Directory. Could you please explain how I may overcome this problem in Outlook. Thanks. Hi Daniel, does you problem still exist? cheers, Andy "Daniel Castillo" <Daniel Castillo@discussions.microsoft.com> schrieb im Newsbeitrag news:BE4F21E4-D9D3-40EC-BF62-5ABB63DAA5C1@microsoft.com... >I have created a User with a mail box in Exchange 2003. When I configure > ...

Problem saving spreadsheet as xml
I have MS Office 2003 and an Excel spreadsheet that I want to save as an "xml", for use on my website but I want to password it so that anyone viewing it online can't change anything. When I select "Save" and then "Tools" (as I have been shown to do and it worked for them!) and then "General Options", I get a dialogue box headed "Save Options" but both "Password to View" and "Password to Modify" are greyed out and I can't do anything with them. I selected, instead, "Read-only recommended", thinking t...

Overseas connection problem
Hi all, I'm using Exchange 5.5 and now got a problem for overseas connection. All the users within local area works well and Taiwan office just upgraded the internet connection to FBBI. After that all the users can't download the large attachment that larger than 200kb. I did charged the MTU setting and it's still not work. Any idea of it? Many thanks, Nelson from Hong Kong Is this isolated to large attachments within messages or also large files across the lan (i.e. file sharing)? If this is all network traffic and downloads that are limited, you should contact your provi...

Publisher Install Problem
Hello, Whenever I try and install publisher98 as a custom install it freezes when checking for ample disk space. When I select typical install for instance it installs fine. Any suggestions Well you have solved your own problems - terrific. -- "If you don't know where you are going, any road will take you there!" I wanted to do a custom to select all the options including clipart etc. I would've thought you picked up on that. I installed as typical as a temporay measure anonymous@discussions.microsoft.com wrote: > I wanted to do a custom to select all the optio...

Sales Tax Problems With RMS 2.0
Alright i just recieved a client call that he is having a problem with sales tax on customers, here are the symptoms. 1. under the customer options --> Billing information, there is a box called (customer tax) and it is grayed out. 2. all customers have been defaulted to tax exempt what am i missing here??? Anyone??? "ILMARcorp" wrote: > Alright i just recieved a client call that he is having a problem with sales > tax on customers, here are the symptoms. > > 1. under the customer options --> Billing information, there is a box called > (customer tax) ...

Radar Charting problems, Can I change...
Hi people I have 2 queries relating to Radar charts in Excel (Office XP/2002). Can I change the angle/start position of the first plotted point? I always starts at 12 o'clock but I want it to start at 9 o'clock. I ca re-order the data to achieve this effect but the data, to the viewer is not in a logical sequence if I do that. Then also, can I change the direction of the plotting from clockwise t anti-clockwise? Thanks in advance. Stell -- Message posted from http://www.ExcelForum.com Stella - You can't do these things (unless someone smarter than me knows how), but you c...

is this possible? reload a form that is based on a query with new query that is defined in the form.
I have a basic form. The form is based on a query, so when I open the form, a pop comes up where I enter in what I want to query. So instead of 30,0000 records, I get 1-10 records. on the form, I have a text box. I want to be able to enter in what I want to query next and afterupdate have the recordset/form reload with that query instead of my original qeury. I figure this has to be done with VB. I am just starting to learn some VB but I am lost. Any info would be great, thank you. It does. You need to apply the new query or table name, or the sql string to the forms recordsource, then...

Backup #5
I have read tons of information about bakcing up and restoring Exchange server. But none of them makes much sense to me. Is there some easy way to do this? Is there a way to just backup some files. I would appreciate if somebody could send me a link to some easy to follow steps. Thanks Soni What specifically do you not understand about backing up and restoring Exchange? What doesn't make sense? Which documents have you read? Have you read the Disaster Recovery Whitepaper? Which version of Exchange are you using? What backup software? We need a bit more information here so that...

Problems when importing graphic
Everytime I import a simple circular graphic from Illustrator it shows with a box around it. The box is not transparent and therefore I cannot place the graphic over anything else. Any advise? FYI-I've imported as an eps, jpeg and tif file--same results everytime. Thanks. Transparency is not supported in JPG or TIF and you will need a Postscript printer to print EPS. By nature all rasterised graphics are square by nature. Depending on which version of Publisher you are using, you could place the graphic in a AutoShape to achieve the result you require. -- David, Hemera Photo ...

Possible to detect changes to an XmlDocument object?
If I instantiate an XmlDocument object, the load a file through the Load method, is there a way, later on in execution, to easily determine if the XmlDocument object has changed from the original version loaded? I'd like to be able to check a condition to see if the document in memory has been editted (i.e., nodes added, attributes changed, etc.) so that my user can decide whether or not to commit changes to the disk file before moving to another file. Will I have to resort to re-loading the file, and comparing OuterXml properties? Gabe Gabe Covert wrote: > I'd like t...

Problem with OWA and Exchange 5.5
Hi everyone, I am having this problem with MS Exchange 5.5 and Outlook Web Access. When I log in remotely I get to see my emails and as in the office I can reply, delete, move emails as necessary. The problem is that when I get back in the office all new emails are marked as unread. If I have replied or forwarded email they are not marked appropriately either. Is there anything I can do to rectify this problem? Any patches, changes to the registry? Regards, Anguel ...

Windows XP problem
My machine runs on Widnows XP SP2 and IE8.0. Two days before my father browsed something in the Inbox of Faccbook and then clicked "Home", It seemed not to respond, then he "refesh" the page. It did not respond. He click "refresh" again and it stoped to respond too. So he tried to close the Browser and it returned the error message "IE has encountered probelm and it must be terminated. So he pressed "Confirm". But it did not respnd. He wait for a while, the computer shut down and restart automatically. When it entered to the Windows...

Authentication problems
Hello, we recently migrated from CRM 3.0 to CRM 4.0. We can login and work with CRM. Everything works as expected. But users sometimes get the message "401. Unauthorized....". It's a simple white screen with the error in plain black text. So no custom IE error message. Restarting the browser (and reopening the crm website) solves the problem in most cases. - First I thought that is was a session that gets expired but certain users claim they also get the error after only a few minutes of work. - Sometimes restarting the browser does not work and you have to wait a few minut...

Is it possible to compare a string and a field name
I have the following tables: Membership, Subscriptions, OfficeUseOnly. The Office Table contains a Year, Year To and From [financial year] and the other fields are the 5 membership types (regular, student etc) and these 5 fields contain the different amount each type of member has to pay. I want to create a form whereby on entering the financial year and the membership type the correct amount is automatically inserted into the 'Amount' field: FinancialYear - in current form = OfficeUseOnly SubsFinYear and then if MemberType = 'Student' find Student field ...

Exchange Groupshield Detected Items Problem
I've called McAfee on this problem and they said it was an exchange problem. In Groupshield I see items in the detected items list with a detected date of today, but when I look at the information icon it indicates it is a message from say 2 months ago. I've used the exchange utilities to perform an offline defrag and repair. Any help would be appreciated. Robin On Fri, 27 May 2005 09:12:03 -0700, "Robin" <Robin@discussions.microsoft.com> wrote: >I've called McAfee on this problem and they said it was an exchange problem. > >In Groupshield I see it...