Log File Full | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Log File Full

I need one more help, an urgent one, please.
I had scheduled dts to run nightly and append data to an existing destination table.
There are 6 databases and they are rather large (each contains over 35 tables).
This morning, job failed to run and the reason is because according to the error message, log files are full.
Can someone please suggest a remedy for this situation for me.
Maybe I need to reschedule dts and choose the ‘Drop destination table and recreate option to eliminate this problem? Thanks in advance
No, you have to take intermittent log backups on the destination database to reduce the log file size being used during DTS. Or if you have enough disk space where log files are stored, you may want to set the log files to auto-extend and then truncate (or backup) them in the morning.
The easy thing you can do is to run BACKUP LOG … WITH NO_TRUNCATE and take full backup then after.
And while running this DTS task you can set the DB recovery model to SIMPLE to save the Tlog space, if Tlog is important then keep in FULL RECOVERY model and take intermittent log backups. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Thanks to both of you for your response.
I have been trying to do this backup but kept getting the following error: Write on C:BackupNF failed. status = 112
Backup Database is terminating Abnormally. I was wondering if anyone of you knows what this means.
Make sure that you have the folder C:Backup exists and there is enough space available on C: drive. Also, send us the complete backup statement you are trying to run.
hi ykchakri! Space may be an issue here.
There is only 3.97 megs of space left. Below is the backup script. BACKUP LOG NorthFulton
WITH TRUNCATE_ONLY BACKUP DATABASE NorthFulton TO NF
WITH NOINIT
Yes the error 112 relates to the space issue on the disk and make sure you’ve plenty of space before performing such backup or DTS operations. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I think you can backup to a cdrom, can’t you?
If it is possible, how can I modify this code to make it possible? BACKUP LOG NorthFulton
WITH TRUNCATE_ONLY BACKUP DATABASE NorthFulton TO NF
WITH NOINIT Thanks satya!
Enter the complete path and file name or create device pointing out to CDROM drive and ensure CD is on the drive to perform backup. And only you can burn 750mb worth of database to CD. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
And only you can burn 750mb worth of database to CD. That was a good one, right there. I have tried just about everything.
All the various jobs we have scheduled have all stopped working because of log files being full.
I have tried using enterpise manage as well as scripts (such as the ones I posted) to backup our database and free up some space but I keep getting different types of errors.
Right now, instead of trying to back up to c drive has no space, I decided to backup to e: drive which has over 56G of space.
Now I am getting this error: "Cannot open backup device NF1. Device error or device off-line" I am getting a bit frustrated.
Can you please tell me what I am doing wrong?
Here again are the scripts I am using.
The first script creates a file and the second which is failing is supposed to backup data to that file. /* Create some logical backup devices */ USE master
EXEC sp_addumpdevice ‘disk’, ‘NF1’, ‘E:BackupNF1.bak’
EXEC sp_addumpdevice ‘disk’, ‘NFlog’, ‘E:BackupNFlog.bak’ ** Clear the log and then back up the database.
*/ BACKUP LOG NorthFulto
WITH TRUNCATE_ONLY BACKUP DATABASE NorthFulto TO NF1
WITH NOINIT Please tell me what I am doing wrong!!!!
Is there anyone who can look over the code I posted and tell me why I am having a hard time getting it to work, please?
Try backup to E: drive itself by executing :
BACKUP DATABASE dbname TO DISK=’E:pathnameackupname’ WITH INIT. And make sure SQL service account has necessary privileges to access the specified path. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
sorry Satya,
I am still at loss as to why this thing is not working. I changed it to what you suggested but still get same error.
I have ensured that service account has administrative privileges to access the path: BACKUP LOG NorthFulto
WITH TRUNCATE_ONLY BACKUP DATABASE NorthFulto TO DISK=’E:ackupNF1′
WITH INIT
Cannot open backup device ‘E:ackupNF1’. Device error or device off-line. See the SQL Server error log for more details.
What is the CD-burning software used and make sure the SQL service account has necessary privilege to use the application. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
All I can suggest is from a sql 7 BOL comment With disk and tape, specify the complete path and file name. For example, DISK = ‘C:Mssql7BackupMybackup.dat#%92 So in your case, add the file name, not just the path name.
And while I’m looking at it, I think you are backing up to CD….a further tidbit (this is sql 7, but I think its the same for 2000) "If you intend to transfer the resulting backup set to a CD-ROM and then restore from that CD-ROM, you must set BLOCKSIZE to 2048" Beyond that, I’m not sure.
Chris
I have already abandoned the idea of backing to cdrom.
So no, I am not backing up to cdrom.
The E drive referred to is another drive that has over 56gigs of memory.
Well, the comment is still to specify the file name along with the path, for disk and cd. So it cant hurt to try. Then, maybe take a look at the sysdevices to make sure its all setup correctly. Sp_helpdevice still works in sql 7 (dont know about 2000) but if not you can query the system table directly (shhhh, thats not a recommended practice) to see how its setup in there. Next, try testing writing something other than a backup to that drive from sql using xp_cmdshell. That is the error we were getting when the service account didnt have permission to write to that drive. If all that is okay, try writing a small database (make one up) and see if that works. I’ll see if anything else comes to mind and let you know.
As referred make sure SQL service account has necessary privileges on E: drive. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>