SQL Server Performance

Log File Full

Discussion in 'SQL Server DTS-Related Questions' started by simflex, Oct 29, 2003.

  1. simflex New Member

    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
  2. ykchakri New Member

    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.
  3. satya Moderator

    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
  4. simflex New Member

    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.
  5. ykchakri New Member

    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.
  6. simflex New Member

    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
  7. satya Moderator

    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
  8. simflex New Member

    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!
  9. satya Moderator

    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
  10. simflex New Member

    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!!!!
  11. simflex New Member

    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?
  12. satya Moderator

    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
  13. simflex New Member

    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.
  14. satya Moderator

    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
  15. ChrisFretwell New Member

    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
  16. simflex New Member

    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.
  17. ChrisFretwell New Member

    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.

  18. satya Moderator

    As referred make sure SQL service account has necessary privileges on E: drive.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page