April 10, 2009 at 6:55 am
Thats what I'm wondering about?? Why didn't the transaction log shrink? Why is it still asking for the old file?
April 10, 2009 at 9:00 am
Edd (4/10/2009)
Thats what I'm wondering about?? Why didn't the transaction log shrink? Why is it still asking for the old file?
The transaction log won't shrink (get smaller on disk) just by performing a backup of the log. Backing up the transaction log makes the space available in the file to be reused (truncates). Review the article in my signature for further information about managing transaction logs.
You need to review the maintenance plans and make sure there are no other plans trying to backup the transaction log. I would bet that you have another plan or sub-plan that has a task in it that is either trying to backup the log to that old file, or a step to remove old backups that is hardcoded to that filename.
Now that you have a regular maintenance plan in place, you can shrink the transaction log. This will be a one time operation. Based upon your usage I would shrink the file to no less than 500MB and continue to monitor. To shrink the file:
DBCC SHRINKFILE(logical_filename_of_log, 500);
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 10, 2009 at 9:36 am
It can do this without the missing log backup that its looking for? without losing anything?
April 10, 2009 at 9:46 am
Are you asking whether or not you can shrink the transaction log? Yes.
The missing backup file - I am not sure what is going on there. Can you post the full message and identify the maintenance plan and/or job that is generating the message?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 13, 2009 at 11:24 am
I have both recent database and transaction backups. The two remaining issues with the database now are;
1. The transaction log is not shrinking with a backup.
2. Errors during the transaction log backup.
I now understand that backing up the transaction log will not reduce its size until it is truncated and shrunk, rather it deletes data from the log by creating white space in the transaction file that is available for future writing. No problem there. I haven't done this yet since I'm not sure if I need to solve the tranaction log backup error first. Is my assuption correct?
The transaction log backup is performed via the same maintanance plan. The SQL log suggest the database backup is working correctly. It fails trying to find the path to an old tranaction (6 month old?) log backup. It is creating new tranaction log backup files. The file its looking for is not present or available. It was created by an old maintainance plan that is also gone.
The SQL log error message is,
BACKUP failed to complete the command BACKUP DATABASE [My Database] TO DISK = N'E:\SQL Backup\TransactionLogs\My Database_tlog_200803050000.TRN' WITH NOINIT , NOUNLOAD , NAME = N'InstantForum414 backup', NOSKIP , STATS = 10, NOFORMAT
The event viewer error message is;
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 4/13/2009
Time: 4:00:00 AM
User: N/A
Computer: STORAGE
Description:
SQL Server Scheduled Job 'My Database backup' (0xD619C3ACEA64DF4097B7F4C4580325A8) - Status: Failed - Invoked on: 2009-04-13 04:00:00 - Message: The job failed. The Job was invoked by Schedule 3 (Schedule 1). The last step to run was step 1 (Step 1).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Is the solution to this doing a database backup, switching to simple backup and back to a full backup? This is not a typical database in that losing even a weeks worth of data is not preferable but IS acceptable.
April 13, 2009 at 11:44 am
Yes, your assumption is correct - the transaction log file will not shrink just because you back it up. To make the file smaller, you have to explicitly shrink it using DBCC SHRINKFILE. This should not be something that is done on a regular basis and should only be done after an extra ordinary event.
Now, onto your error message. That error message has absolutely nothing to do with your transaction log backups. This error is caused by trying to backup a database to a specific file that does not exist.
The SQL log error message is,
BACKUP failed to complete the command BACKUP DATABASE [My Database] TO DISK = N'E:\SQL Backup\TransactionLogs\My Database_tlog_200803050000.TRN' WITH NOINIT , NOUNLOAD , NAME = N'InstantForum414 backup', NOSKIP , STATS = 10, NOFORMAT
In the above command, the system is trying to backup a database with the name [My Database] to the file 'E:\SQL Backup\TransactionLogs\My Database_tlog_200803050000.TRN'. Because the command has both NOINIT (don't initialize the file) and NOFORMAT (don't format the file) - the system is expecting that file to exist.
Now, the trick is going to be finding where that backup command is coming from. Review your maintenance plans and SQL Server Agent jobs. When you find the plan (or job) that is trying to do this, fix that job or remove it.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 13, 2009 at 11:51 am
Thank You Jeffrey,
I found the source of the error message in the jobs folder and disabled it.
Once I can complete both a database and transaction log backups without any errors I'll deal with shrinking the transaction log file.
Progress!!
April 13, 2009 at 12:00 pm
Great - it sounds like you now have things under control and should be able to shrink the transaction log.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply