December 2, 2013 at 10:09 am
I have a peculiar situation. There are several user databases on the instance, all other db's are backing up fine. One however (full recovery model), a full or differential backup total size is nearing 40GB. The mdf for the database is around 10GB. The ldf is ~30GB, but we also do two-hourly transaction log backups. There is little room in either file to be shrunk.
This is causing issues with backups completing successfully, as we invariably run out of room before our maintenance tasks run to delete old backup files.
I have checked that full text indexing is not configured on the database, and it doesn't use filestreaming.
I'm really confused by this behaviour.
Can anyone please help?
December 2, 2013 at 10:48 am
When you run a full backup, it also backs up the transaction log. If your transaction log has 30gb of transactions that have not been backed up (guessing based on your statement that the files are full), then that will be part of the backup of the database. I'd suggest getting the log backups run, and much more frequently than once an hour (again, inferring from what you said). 10gb of data plust 30gb of log equal 40gb of 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
December 2, 2013 at 11:09 am
Have you checked to see how much space is being used inside your tlog file? If it is all 30GB then you have your answer, but the next question is why is your tlog full when you are running tlog backups? You might want to see if you have any long running transactions that are still open.
December 2, 2013 at 7:59 pm
Full backups and differential backups don't backup data that is in the transaction log. They contains pages that exist in the various .mdf and .ndf files (the data files) of the database.
So a full backup of 10GB database should take up to 10GB (give or take the space actually used by the database). A database with a lot of free space won't need anything like 10GB of disk space for a backup.
A differential backup only backs up pages changed since the last full backup. How large this backup would be really depends on the number of pages that have been changed since the last full backup. e.g. if you continually change the same pages, the differential backup would be the same size each time. If you change a lot of different pages, then the differential backup will grow.
What I would check is whether you are appending backups in the .bak file. SQL Server is quite happy to store multiple backups in a single file. You simply need to choose which one you want when you need to restore from the backup.
December 2, 2013 at 9:18 pm
happycat59 (12/2/2013)
Full backups and differential backups don't backup data that is in the transaction log. They contains pages that exist in the various .mdf and .ndf files (the data files) of the database.
That is not correct both do contain some tlog records too. Here is a link:
http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx#id0980008
but here is the quote:
Another misconception about full backups is that they only contain data. Both full backups and differential backups also contain some transaction log records so that the restored component (database, file, or filegroup) can be made transactionally consistent.
December 2, 2013 at 11:46 pm
you can make use of compressed backup feature which will reduce your backup size substantially.
also run select log_reuse_wait_desc from sys.databases
and review log_reuse_wait_desc
December 2, 2013 at 11:49 pm
happycat59 (12/2/2013)
Full backups and differential backups don't backup data that is in the transaction log. They contains pages that exist in the various .mdf and .ndf files (the data files) of the database.
They do back up the transaction log. They don't truncate the log, but they have to back up enough of the log that the DB can be restored in a consistent state. If there's a very long running transaction at the point that the backup runs, or replication that's far behind, the amount of log backed up can be large
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2013 at 1:58 am
Keith Tate (12/2/2013)
Have you checked to see how much space is being used inside your tlog file? If it is all 30GB then you have your answer, but the next question is why is your tlog full when you are running tlog backups? You might want to see if you have any long running transactions that are still open.
Hadn't thought of that. Makes sense, as even though the log is full (7% free space of the 30GB), the transaction log backup files are literally tens of MB.
Just another note to add; these data bases were recently converted from full to simple by another support company after an upgrade, and I changed them back to full when I realized they were being stupid. (I'll not go into that conversation). So could this have caused any problems?
December 3, 2013 at 2:00 am
No, that wouldn't have caused a problem.
What is log_reuse_wait_desc for that database (from sys.databases)? Are you sure you're not making multiple backups to the same file (appended)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2013 at 2:03 am
GilaMonster (12/3/2013)
Are you sure you're not making multiple backups to the same file (appended)?
Absolutely sure, all other db's are backed up on the same maintenance plan, and all other backup files are small in comparison.
December 3, 2013 at 2:13 am
Nope, no long running transactions, no blocking transactions....
Any other ideas?
December 3, 2013 at 2:14 am
GilaMonster (12/3/2013)
What is log_reuse_wait_desc for that database (from sys.databases)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2013 at 2:17 am
GilaMonster (12/3/2013)
GilaMonster (12/3/2013)
What is log_reuse_wait_desc for that database (from sys.databases)?
NOTHING
CHECKPOINT
LOG_BACKUP
NOTHING
LOG_BACKUP
NOTHING
NOTHING
NOTHING
LOG_BACKUP
LOG_BACKUP
NOTHING
CHECKPOINT
NOTHING
NOTHING
NOTHING
NOTHING
CHECKPOINT
REPLICATION
LOG_BACKUP
LOG_BACKUP
LOG_BACKUP
LOG_BACKUP
NOTHING
LOG_BACKUP
NOTHING
NOTHING
LOG_BACKUP
LOG_BACKUP
NOTHING
NOTHING
NOTHING
NOTHING
NOTHING
NOTHING
December 3, 2013 at 2:20 am
Not for every database on the server, that's a pretty meaningless list without the DB names. What is the log_reuse_wait_desc for that database? For the specific database you're having problems with?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2013 at 2:23 am
If I run
use %databasename%
then the select command, will this give me the correct result?
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply