Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    4

    Unanswered: SQL Server 2005 Backup Failed

    Hi All,

    I'm new to the forum and new to SQL Server. We lost our DBA and it's now fallen to me to pick up the slack. Though training is in my future there are some tasks that I need to complete before then.

    We have a few SQL servers working with several other products but the one that I'm working on now is part of our DAM (Digital Asset Management) solution, Artesia.

    Over this last weekend our transaction log inexplicably quadrupled in size (approx 18GB's - 68GB's) and has filled the partition it sits on.

    I'm trying to perform a backup of the db and log to an external drive I have attached to the box so that I can then perform a "shrink" on the db and try to get this log slimmed down so we can get back up and running.

    I'm attempting to perform the backup by right-clicking on the db instance and selecting the backup...pointing to the ext drive that has 800+GB of free space. However I immediately get an error message stating that the backup failed. Additional information states that "an exception occurred while executing a transact-SQL statement or batch. The transaction log for database 'Artesia_Prod' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

    I've tried using "Full" backup and "Transactional Log" but get the same failure.

    Bearing in mind that my short term goal here is to have a backup of the db and log should the shrink command cause problems...does anyone have a suggestion for getting around this problem? Or another way I can get this transaction log slimmed down so that the drive it sits on is not full? Note that the db itself did not seem to grow...only the log.

    Any help would be GREATLY appreciated and though I'm a fairly seasoned IT guy I'm pretty new to SQL. Screenshot of error attached.

    Thanks
    Attached Files Attached Files
    Last edited by pmjewett; 08-11-09 at 10:57. Reason: added attachment

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, the transaction log is how SQL Server maintains database integrity in case of an unexpected shutdown. The classic example is when you transfer money from a savings account to a checking account, you want both parts (removing money from savings and adding money to checking) to go through as a unit, or not at all. Otherwise, you end up with an awful lot of angry customers.

    The problem you are facing is that the transaction log is full, and you can no longer make any changes to the database until some space is created. It is in fact so full, that the system can not even put in a record to mark the beginning of a backup. In order to remedy this, you will need to increase the size of the log file. This is easy if you have only hit the maximum size of the file. If this is the case, increase the maximum size of the file by a couple hundred MB, and you should be fine to do the backup.

    If you have hit the limit of the disk itself, then you will likely have to move something off of that disk, in order to make room. Alternatively, you can try to create a new logfile on a separate disk to take some of the overflow.

    Normally, transaction logs are kept in check by regular transaction log backups. If these are already in place, then this might be a one-off kind of thing. Maybe an infinite loop in the code somewhere, who knows. If these are not in place, you will want to set them up as soon as possible.

  3. #3
    Join Date
    Aug 2009
    Posts
    4
    Quote Originally Posted by MCrowley
    Well, the transaction log is how SQL Server maintains database integrity in case of an unexpected shutdown. The classic example is when you transfer money from a savings account to a checking account, you want both parts (removing money from savings and adding money to checking) to go through as a unit, or not at all. Otherwise, you end up with an awful lot of angry customers.

    The problem you are facing is that the transaction log is full, and you can no longer make any changes to the database until some space is created. It is in fact so full, that the system can not even put in a record to mark the beginning of a backup. In order to remedy this, you will need to increase the size of the log file. This is easy if you have only hit the maximum size of the file. If this is the case, increase the maximum size of the file by a couple hundred MB, and you should be fine to do the backup.

    If you have hit the limit of the disk itself, then you will likely have to move something off of that disk, in order to make room. Alternatively, you can try to create a new logfile on a separate disk to take some of the overflow.

    Normally, transaction logs are kept in check by regular transaction log backups. If these are already in place, then this might be a one-off kind of thing. Maybe an infinite loop in the code somewhere, who knows. If these are not in place, you will want to set them up as soon as possible.
    I've hit the limit of the partition itself. The other partition (C only has 7GB remaining and the larger 96GB partition (E that is home to both the db and log is completely full. I just ran a "Simple" backup as that seems to be the only one I can run successfully...however it didn't reduce the size of the log assuming it should have. The only other volume available to this box (apart from our SAN and I'm not sure how much space I can get there) is the ext disk I'm backing up to and it has 800+ GB's available.

    As our DBA was let go and I can't get any historical info on how backups were maintained I can't say if the transaction logs were ever backed up. Would a "Simple" & "Full" backup qualify as one that should keep the log in check? As I said before it appears to be my only option as any other backup attempted but "Simple" fails with the error message I attached.

  4. #4
    Join Date
    Aug 2009
    Posts
    4
    Trying to run the following but getting syntax errors. Can you help?

    This is what I entered and I got the below syntax errors. Can you help edit this?

    BACKUP LOG Artesia_Prod TO <E:> WITH TRUNCATE_ONLY DBCC SHRINKFILE('Artesia_Prod_log.ldf',<2048>


    The errors:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '<'.
    Msg 319, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '<'.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not sure what a "simple" backup is. I assume you mean a Full backup. It's what I get for not using the GUI, anymore. Either way, a full backup would not reduce the log size at all. Only a log backup would help there.

    Run this, and see what you get for log backups:
    Code:
    select max(backup_start_date) as "backup_start_date", 
    	case type
    		when 'I' then 'Differential'
    		when 'D' then 'Full'
    		when 'L' then 'Log'
    		else 'Unknown' end as "Type", 
    	database_name
    from msdb.dbo.backupset
    group by type, database_name
    To fix the problem, you may be able to shortchange another database on the server, to get the troubled database freed up. Are there any databases on the server that are in SIMPLE recovery mode? If so, you can try to shrink their logs to free up space for the troubled database's log to grow a little.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Before you go too far with TRUNCATE_ONLY, remember that this will remove the log, and you will not be able to restore the database to a point in time covered by the current log. Are you certain you want to try to discard the log?

    Gads, I sound like a pop up box, now.

  7. #7
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Here's an easy read for SQL basics intro. It may help lay some groundwork for your new responsibilities - besides having some nice scripts for dealing with log file shrinking.

    I had a log file suddenly double and fill a disk last week after adding a few columns to a huge table. Luckly; I keep the log files on a separate volume together with some DB Backups. So; I had something to delete (a day old backup file) - I'm now moving everything to a new (far bigger) server on Friday.

    For performance and integrity reasons; you should strongly consider getting a physically separate disk volume for the main log files. That way; a volume failure won't kill your DB and your recovery files simultaneously.

    Note: Mirrored disks is a good RAID choice for logs and is probably the simplest to implement.
    Last edited by vich; 08-13-09 at 02:09.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •