April 8, 2009 at 9:03 am
I'm running a SQL 2005 Enterprise server in a 2 node cluster. One particular DB is ~8GB in size, but when I the full backup job runs (9PM nightly, before any other maintenance, ETL, whatever tasks) the backup file is ~15GB in size. None of the other DB's on the server have this issue.
The 8GB size is all of the data files and the transaction log.
Has anyone encountered this before? If so, what was the cause of the problem? DB access has dropped radically due to the program using it being obsoleted. Now access is mostly limited to maintenance, accounting, and auditing.
Thanks in advance,
Greg
Greg Roberts
April 8, 2009 at 9:07 am
Are you appending the backup to a previously existing file? What do you see when you run RESTORE FILELISTONLY from disk = 'YourPath\YourFile'?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 8, 2009 at 9:23 am
No, the job overwrites the existing backup file. The file is backed up to tape by our netadmin @ ~Midnight.
Here are the job steps.
Step 1: Backup the DB
BACKUP DATABASE [CORP]
TO [Ditto - Corp]
WITH
NOFORMAT,
INIT,
NAME = N'CORP-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD
Step 2: Backup the Transaction Log The transaction log backup is appended to the Full backup.
BACKUP LOG [CORP]
TO [Ditto - Corp]
WITH
NOFORMAT,
NOINIT,
NAME = N'CORP-Transaction Log Backup',
SKIP,
NOREWIND,
NOUNLOAD
Step 3: Shrink the DB
File Sizes
DB Data: Physical MB: 7176 Backup MB: 14915
Log: Physical MB: 468 Backup MB: 463
Greg Roberts
April 8, 2009 at 9:27 am
Sounds like you did a backup to the same file without issuing INIT to reset it. So, it appended the backup to the file.
If you RESTORE HEADERONLY you should see everything that's in the backup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 8, 2009 at 9:28 am
Greg Roberts (4/8/2009)
No, the job overwrites the existing backup file. The file is backed up to tape by our netadmin @ ~Midnight.Here are the job steps.
Step 1: Backup the DB
BACKUP DATABASE [CORP]
TO [Ditto - Corp]
WITH
NOFORMAT,
INIT,
NAME = N'CORP-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD
Step 2: Backup the Transaction Log The transaction log backup is appended to the Full backup.
BACKUP LOG [CORP]
TO [Ditto - Corp]
WITH
NOFORMAT,
NOINIT,
NAME = N'CORP-Transaction Log Backup',
SKIP,
NOREWIND,
NOUNLOAD
Step 3: Shrink the DB
File Sizes
DB Data: Physical MB: 7176 Backup MB: 14915
Log: Physical MB: 468 Backup MB: 463
Sorry, I was typing when you posted this. Try running RESTORE HEADERONLY anyway. See what's in it. I wouldn't be at all surprised for there to be two copies.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 8, 2009 at 9:51 am
Thanks for the correction Grant, it is headeronly.
Greg - the log backup is set to NOINIT so, that is probably where the problem lies.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 8, 2009 at 11:19 am
The log portion is only 463 MB, which is a little less than the physical size of the log. The total physical size has never exceeded 9GB.
Viewing the media contents in SSMS I only see the 2 backup sets I expect. The way to big full backup, and the tiny log backup. The HEADERONLY option should not show anything different. But I'll check.
CONFRIMED: The HEADERONLY option displays the same results as SSMS. Only 2 sets exist in the backup file, which is as intended.
Greg Roberts
April 8, 2009 at 8:45 pm
This may be way out of left field, but are you backing up to a different disk? If so, is the allocation unit size the same or similar on the disks?
April 9, 2009 at 12:18 am
I would be curious as to why you are trying to append a log backup as a step 2 in a job after a full backup?
Also if this was the method you should in addition check the name = '' and compare it to the headeronly output and make sure the media names are the same.
Thanks,
April 9, 2009 at 10:33 pm
Just a thought, but does the database have a full text catalog that you're not counting in the size of the datbase but is in the backup?
April 28, 2009 at 11:15 am
On the nose! I completely forgot the Full Text Index. THANK YOU!!!!!!!
Every once in a while I have a serious brain fade and need the good old smack upside the head to reboot. 😀
RE the log backup after a full backup, it was the only way I could get he log file to shrink. I know it should be shrinkable after the full backup, but for whatever reason my server just doesn't want to play nice. And if i let it, the log file will consume ALL of the available disk space.
Thanks again gang. We can consider this thread closed.
Greg Roberts
April 28, 2009 at 11:22 am
Greg Roberts (4/28/2009)
Every once in a while I have a serious brain fade and need the good old smack upside the head to reboot. 😀
We've all been there and done that. 😀
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply