question

csaha2 avatar image
csaha2 asked

Transactional log backup failed

Message >Executed as user: NT AUTHORITY\NETWORK SERVICE. ...00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:13:27 PM Progress: 2010-10-26 13:13:28.61 Source: {A8B5E784-394D-42D6-A950-A19F3622989E} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2010-10-26 13:13:29.03 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'E:\Backup\Lo".: 100% complete End Progress Error: 2010-10-26 13:28:24.19 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "BACKUP LOG [play] TO DISK = N'E:\Backup\LogFile\play\play_backup_201010261313.trn' WITH NOFORMAT, NOINIT, NAME = N'play_backup_20101026131329', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "Write on "E:\Backup\LogFile\play\play_backup_201010261313.trn" failed: 112(There is not enough spa... The package execution fa... The step failed This database has 340 MB and the log file is 13 G, the place where I am trying to backup the transactional log has only 4 gig left. Is space an issue?
sql-server-2005backuptransaction-loglog-backup
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Not only is space an issue, but a 340MB database having 13GB worth of log is another. Either this database is in full recovery model, and you are not taking log backups, or you just had a LOT of updates?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes the database is in full recovery mode and I have never taken log backup.
0 Likes 0 ·
ozamora avatar image
ozamora answered
"log file is 13 G, the place where I am trying to backup the transactional log has only 4 gig left. Is space an issue?" Yes
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
The error message "There is not enough space" would indicate that, yes, space is an issue. 13GB log on 340MB database? Is this some sort of high throughput transient data, and log backups are always this sort of size, or is it that you've only just realised that the transaction logs need to be backed up? One way of dealing with this is: - take a full backup of the database - put the database into SIMPLE recovery mode - shrink the transaction log file manually - put the database back into FULL recovery mode - take a full backup of the database, and start taking regular transaction log dumps.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 best course of action, followed by read, read and read some more
1 Like 1 ·
+1. Correct the issue ASAP
0 Likes 0 ·
Tim avatar image
Tim answered
Yes @Csaha2 As @ozamora copied and pasted from your question, the log you are trying to backup is several times the size of the available space you are trying to backup to. My advice is to take a full backup of the database, then truncate your tlog. You can then shrink the log and take appropriate measures to ensure you are managing your tlog growth properly. If you need help with that, let us know and we will post some links for you.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1. 14 GB log for a 340 MB DB.......
0 Likes 0 ·
Hi there, thank for all the advices, I am able to do the transactional log backup with all my databases but how do I shrink the log file? Do I need to truncate too? I ran the DBCC LOGINFO(Play) and other databases. In some the status is 2 and in some status is 0. What do I do next. I ran use[Play] go sp_helpfile go File name = IMSA2 and IMSA2_log( this is 13 g) can I shrink this using the following command DBCC SHRINKFILE (IMSA2_log, 500MB) WITH NO_INFOMSGS what is next. Thanks in advance for all your help
0 Likes 0 ·
BACKUP LOG [PLAY] WITH TRUNCATE_ONLY Then shrink the file DBCC SHRINKFILE(IMSA2_LOG, 500)
0 Likes 0 ·
Sorry I am going to ask you again, the above sql scripts you mentioned should be applied on the logfile that is with the database datafile and not the transactional log right? Can this script be applied to all databases whether the status is 0 or 2? Will this cause any issue related to data loss while truncating and shrinking the log file.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.