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?
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?
"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
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.
+1 best course of action, followed by read, read and read some more
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.
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
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.
