Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
matthewf_boi
Starting Member
2 Posts |
 Posted - 2010-02-01 : 16:39:06
|
| Hi, i have a sql2005 database in Full recovery model.(Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) )MDF is 12GB & LDF is currently 6.4GB.I do weekly fulls on Friday at 10pm.I also do daily Diffs at 8am, 12pm, 4pm.We also do hourly Transaction log backups during biz hours since this is an ERP system.The hourly transactional logs seem to vary predictably given how much work is being done on the database.But the diffs just seem to large in proportion to the size of the db, EVEN AFTER A FULL BACKUP WAS JUST DONE.For instance, i just ran a full back up which was 18.7GB. Then i IMMEDIATELY ran a diff and it's 6.4GB.Shouldn't a diff immediately following a full be almost nothing?Am i missing anything here?my backup syntax that i just ran was as simple as:--fullBACKUP DATABASE coll18_production TO DISK = 'X:\SQL Backups\Weekly\coll18_production\coll18_production_FULL__20100201_1425.bak'--diffBACKUP DATABASE coll18_production TO DISK = 'X:\SQL Backups\Daily\coll18_production\coll18_production_DIFF__20100201_1430.bak' WITH DIFFERENTIALThanksMatthew |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-01 : 16:49:17
|
| "For instance, i just ran a full back up which was 18.7GB. Then i IMMEDIATELY ran a diff and it's 6.4GB"Does it make any difference [to the size of DIFF] if you run a TLog backup immediately before the FULL backup?Maybe there are lot of open transactions that have to be "represented" in the DIFF backup (and the FULL backup for that matter)But personally I found the size of our DIFF backups surprising. Often a very large percentage of the total size of the FULL backup, even when the database has very little activity. |
 |
|
|
matthewf_boi
Starting Member
2 Posts |
Posted - 2010-02-02 : 13:25:37
|
| Thanks both for the feedback - Last night after hours i did a full, a backup log, a dbcc shrinkdatabase and then ran through a full, transaction BU and differential again.The full was 19.45GB and then the diff (14 minutes later) was 7.6GB.Interestingly, my 8am diff was only 346mb. So i'm also waiting to see what my noon diff looks like.Matt |
|
|
|
|
|
|
Differential Backups seem to large?