May 28, 2003 at 8:56 am
My db size is less than 1GB and the nightly backups show this true size but the noon differential backup always show a size of 9GB. DBCC Opentran shows no open transactions. Does anyone know what is going on?
May 28, 2003 at 9:53 am
What have you done from nightly backup to noon backup? Have you reorganize the database?
May 28, 2003 at 10:53 am
Is there a lot of data modification going on in the morning?
-SQLBill
May 28, 2003 at 10:55 am
By data modification, I mean ANY type of change. For example, let's say you have a column that has DATE LAST VIEWED. This column could be updated many times in one day. Doesn't change the size of the database since the information is still just a date, but each change/modification will increase the size of the differential backup.
-SQLBill
May 28, 2003 at 12:51 pm
Remember that the diff backups, holds all the changes since the last full backup. Everytime you execute a full backup, SQL set a flag in every page in the data file of the databases. And then, each time a modification too any page is made, SQL changes that mark, so the next time SQL executes a diff backup, it will contain all the pages that where modified.
To reduce the size of the diff backup, maybe you should execute a full backup in the middle.
May 29, 2003 at 2:08 am
Thanks for your replies. This large diff backup only started in the last 2 days. I do a tran log backup at 10am, 2pm,4pm and 6pm. The 10am trna log backup has also become large at aroung 5GB while the other tran log backups are just about 1-3,000KB.
The size of the ldf file on disk is 25MB and the mdf file is 998MB.
I hope these additional info will help. By the way, I am running SQL server 2000 with SP2 and hotfix for the worm.
Thanks.
May 29, 2003 at 2:26 am
Hi
9Gb? cripes. The key issues with differentials is that its based on whole extents, rather than individual page changes, even if a single page in the 64k extent has been altered/updated/inserted the extent header is marked and it will be part of the backup, allowing full recovery from "the last FULL backup". The size is weird though for the size of the db, check your jobs carefully for starters to better gauge what is happening. I remember doing some testing a long time back and even a simple 20row change was seeing a diff file size of 300k vs full of around 100k... play around with a small db and take a look at the figures.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
May 29, 2003 at 4:46 pm
When do you do the complete database backup?
You should do it everyday. If you don't do this backup for a long time then the diff backup would be very large.
Robert
June 2, 2003 at 10:23 am
This is kind of implied by what the others have said, so I apologize if it was obvious.
I was not apparent in your description when the full backups are performed. If they are daily and the 10am transaction backup is that big, it sounds like there must be a morning processing that changes a lot of data. What new jobs have been introduced to run in the morning?
If you are not doing a full backup daily, do one now and see if the next differential is still huge.
Guarddata-
June 3, 2003 at 4:52 am
Strange... but if the Differential backup is so large does it take a long time to do (longer then a full) and likewise with the restore?? If so why not just take a full at that time. Of course you still need to find out the root cause of the size issue. 🙂
Nigel Moore
======================
June 3, 2003 at 8:50 am
Thanks to all. I found out that strangely enough the differential backup and the trans log backup at 10am were being done with the the noinit option. Changing this to with init has resolved the problem. I don't know how it got changed from the with init which we have been using all along.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply