July 24, 2009 at 11:37 am
I ran a database backup from a maintenance, it reported that it failed but I'm wondering if it really did:
Executing the query "BACKUP DATABASE [FIN_PROD] TO DISK = N'D:\\SQLBACKUPS\\FIN_PROD\\FIN_PROD_backup_200907240923.bak' WITH NOFORMAT, NOINIT, NAME = N'FIN_PROD_backup_20090724092310', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The transaction log for database 'FIN_PROD' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
BACKUP DATABASE is terminating abnormally.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 1026032 pages for database 'FIN_PROD', file 'FIN_PROD' on file 1.
100 percent processed.
Processed 1 pages for database 'FIN_PROD', file 'FIN_PROD_log' on file 1.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
It was done thru a maintenance plan, which checks integrity, backs up the user databases, backs up the transaction logs then rebuilds indexes. The log portion happened successfully, but the log file is now maxed (it went from ~900mbs to 4gbs). As I research, it appears that it's best to run an index defrag script rather than the index rebuild step. It also seems that I should do the transaction log backup first, then the full database backup.
My questions are:
1. Should I run the 'DBCC SHRINKFILE' command against the log file and/or 'BACKUP LOG name with TRUNCATE_ONLY' to get the log file down?
2. Did the database get backed up successfully?
The database is in FULL recovery mode.
TIA
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
July 24, 2009 at 12:15 pm
dh (7/24/2009)
1. Should I run the 'DBCC SHRINKFILE' command against the log file and/or 'BACKUP LOG name with TRUNCATE_ONLY' to get the log file down?
Maybe and no. If you run a log backup with truncate, you are breaking the recovery chain and you will not be able to take log backups until another full backup runs.
If you're certain that the log space is not required, then after a log backup you can run a once-off shrink file to bring the log down to a reasonable size
To reduce the logging impact of the index rebuilds, set the DB into bulk-logged recovery before the index rebuild and back to full after. Index rebuilds are bulk operations in recovery models other than full
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
July 24, 2009 at 12:39 pm
Thanks so much Gail. Do you think my database was backed up properly?
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
July 24, 2009 at 12:43 pm
No idea. Can you restore the backup file? That's the best way to see if the backup completed.
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
July 25, 2009 at 10:22 am
July 25, 2009 at 11:30 am
What is the frequency of the index rebuild operation? Generally speaking, if the fragmentaion is >30%, you should go for rebuild or else reorganization would suffice. You should do the rebuild activity manually.. Also rebuilding is a fully logged operation and might be a cause for your log file getting full.
2. Did the database get backed up successfully?
check with restore verifyonly to see if your backupset is valid or not.
July 25, 2009 at 1:45 pm
dh (7/24/2009)It was done thru a maintenance plan, which checks integrity, backs up the user databases, backs up the transaction logs then rebuilds indexes. The log portion happened successfully, but the log file is now maxed (it went from ~900mbs to 4gbs). As I research, it appears that it's best to run an index defrag script rather than the index rebuild step. It also seems that I should do the transaction log backup first, then the full database backup.
This suggests that you do not have a separate plan (or sub-plan) that backs up the transaction log on a regular basis (e.g. every hour). If that is true - then you really need to set that up.
My questions are:
1. Should I run the 'DBCC SHRINKFILE' command against the log file and/or 'BACKUP LOG name with TRUNCATE_ONLY' to get the log file down?
Only run SHRINKFILE if you have scheduled transaction log backups running. Monitor the sizes of the transaction log backups to get an idea of how large the transaction log should be. Shrink the file one time to a size that is a bit larger than the largest transaction log backup.
Review the article that Gail wrote - it should clear up any doubts about the process you should be following.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply