SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Backup size smaller
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sqlraider
Yak Posting Veteran

USA
65 Posts

Posted - 11/16/2011 :  15:34:19  Show Profile  Reply with Quote
I have a DW database in Simple recovery mode that I take Full backups nightly after the nightly insert/updates are made(NO Deletes).

Last nights backup size is considerbly smaller than the night before. What would cause this?

Last nights size 1,234,322 kb the log said 154213 pages.

Night before size 1,486,228 kb the log said 185692 pages.

2 weeks ago siz 1,397,140 kb the log said 174506 pages.

The backup size increases about 10,000 kb per night.

I am the only one that has access to this database & it is going to become a production DW once it is completely built.

Any ideas?

Thanks,
Rookie DBA

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 11/16/2011 :  16:22:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
Do you have a maintenance plan in place that rebuilds or reorganizes the indexes? If so, that'll likely be the culprit. You've probably got less fragmentation than before.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 11/16/2011 :  16:23:46  Show Profile  Reply with Quote
Did the SQL Server service get restarted? I believe SQL Server creates a checkpoint for simple-mode logs when the service is restarted so anything in the log that has been written to the mdf is flushed out. I could be making that up though, trust but verify. Or maybe don't trust. Regardless I doubt it's anything to be worried about.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 11/16/2011 :  16:51:25  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
quote:
Originally posted by influent

Did the SQL Server service get restarted? I believe SQL Server creates a checkpoint for simple-mode logs when the service is restarted so anything in the log that has been written to the mdf is flushed out.


Yes, it does that, but it also does a checkpoint at the start of a full backup.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Sqlraider
Yak Posting Veteran

USA
65 Posts

Posted - 11/16/2011 :  16:54:45  Show Profile  Reply with Quote
No the SQL Server service was not restarted.

I don't have the database fully built yet so I don't have rebuilds or reorganizes on indexes setup yet (fragmentation on indexes is under 20%).

I did however deleted an EMPTY table using the SQL Server Mangement Studio, do to a complete re-design of said table & re-created it using TSQL script. The table I deleted had no records in it but was created prior to setting the database to Simple recovery & running full nightly backups. Could this be the culprit?

Thanks.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 11/16/2011 :  16:56:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
Nightly insert/update have fewer rows? Any way to tell?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Sqlraider
Yak Posting Veteran

USA
65 Posts

Posted - 11/16/2011 :  17:08:22  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Nightly insert/update have fewer rows? Any way to tell?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



Not sure I understand. I insert/update using Merge & the ouput from these Merge statements are inserted into what I call audit tables(these tables are in the same database & backup too). This way I can see if a record was inserted or updated & if updated what the record looked like before & after the update.

From what I can tell no data is missing & the updates last night are in the database. Everything looks fine except the backup is smaller.

Thanks.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 11/16/2011 :  17:59:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
My point is how much data changed in the database. If you inserted less rows or updated rows that made them narrower, then that would account for the backup size difference.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Sqlraider
Yak Posting Veteran

USA
65 Posts

Posted - 11/17/2011 :  10:26:09  Show Profile  Reply with Quote
Here are the numbers on the table that has the most updates/inserts, the other tables have under 100 updates/inserts rows per night.

Updates about 1,000 rows each night, this was consistent with each night in question.

Inserts 20,307 the night the backup was 1,486,228 kb and 10,572 the night the backup was 1,234,322 kb, so about 50% less.

But (I'm new to this) I don't understand why this decrease in inserts would make the backup size so much smaller. I think it should have had a very small increase in size.

Going back to the empty table I deleted, it did have a Clustered Index on it. I never inserted any rows into it. Could this be the reason in the descreased backup size?

I really appreciate all your help,
Thanks.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 11/17/2011 :  13:29:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
I seriously doubt the empty table is related to this. Sounds like it's due to your reduced inserts. What's the maximum possible row size for that table? (Add up the data types, maximums)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/17/2011 :  14:22:23  Show Profile  Reply with Quote
Is the LOG backing up changed PAGEs? If so perhaps the inserts were closely-clustered when the backup file was small, and dispersed when it was large. Or maybe there was a reindex in between which reduced fragmentation and meant that the next set of inserts were more tightly clustered
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 11/17/2011 :  15:04:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
We covered the fragmentation part already. That was what I first suspected, but OP indicated no rebuild or reorg in place yet.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Sqlraider
Yak Posting Veteran

USA
65 Posts

Posted - 11/17/2011 :  16:03:57  Show Profile  Reply with Quote
FYI - Tara I am using a copy of your backup usp to create my backups. I have an SQL Server Agent job that executes the usp with the @bkpType = 'Full'. Again Thanks your helpful blog.

The maximum row size for that table is 153.

Don't know if this could be the cause but I was testing a Merge statement with a rollback that had 200,000 plus transactions to rollback. But that was after the large backup & before the update/inserts & then the small backup.


Begin Tran
  Merge
    Update
  else
    Insert
Rollback Transaction


Thanks.



Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 11/17/2011 :  16:13:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by Sqlraider

FYI - Tara I am using a copy of your backup usp to create my backups. I have an SQL Server Agent job that executes the usp with the @bkpType = 'Full'. Again Thanks your helpful blog.





Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 11/17/2011 :  16:14:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
How about indexes on that table? How many? How wide are they? What's the fillfactor set to for these?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Sqlraider
Yak Posting Veteran

USA
65 Posts

Posted - 11/17/2011 :  16:27:11  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

How about indexes on that table? How many? How wide are they? What's the fillfactor set to for these?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



One primary key clustered index, comprised of 6 columns, 49 bytes total. As for the fillfactor, I didn't specify one so the default?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000