| Author |
Topic |
|
Sqlraider
Yak Posting Veteran
65 Posts |
 Posted - 2011-11-16 : 15:34:19
|
| 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
38200 Posts |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2011-11-16 : 16:23:46
|
| 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. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-11-16 : 16:51:25
|
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 ShawSQL Server MVP |
|
|
|
Sqlraider
Yak Posting Veteran
65 Posts |
Posted - 2011-11-16 : 16:54:45
|
| 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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sqlraider
Yak Posting Veteran
65 Posts |
Posted - 2011-11-16 : 17:08:22
|
quote:
Originally posted by tkizer
Nightly insert/update have fewer rows? Any way to tell?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sqlraider
Yak Posting Veteran
65 Posts |
Posted - 2011-11-17 : 10:26:09
|
| 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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-17 : 14:22:23
|
| 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 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sqlraider
Yak Posting Veteran
65 Posts |
Posted - 2011-11-17 : 16:03:57
|
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 InsertRollback Transaction Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sqlraider
Yak Posting Veteran
65 Posts |
|
|
|
Backup size smaller
That was what I first suspected, but OP indicated no rebuild or reorg in place yet.Tara Kizer
Tara Kizer