Forum Discussion

Tater_Race's avatar
Tater_Race
Level 2
16 years ago

SQL Server backup - Commit Transaction Logs

Through reading different posts I have found out that to get SQL Server to commit transaction logs using Backup Exec you have to do a full back up the database and then a log backup of the database. Well I did that and the transaction logs will still not truncate.

Can anyone offer me any other advice as to why the logs will not truncate.

My database size is 100GB and the log files are 500GB. I am running out of space on my backup drive due to the fact that BUEX, I assume, is backing up the logs and the database everytime.


Any advice would be apprechiated.

Thank you in advance.
10.x and Earlier
Backup and Recovery
Backup Exec
Database
Tip-How to
  • Ben_L_'s avatar
    Ben_L_
    Level 6
    16 years ago
    This technote should take care of the issue.  http://support.veritas.com/docs/253528
  • Tater_Race's avatar
    Tater_Race
    Level 2
    16 years ago
    Ben,

    Thank you for the reply.

    I have done a Full backup and a log backup. And the log file is still the same size.
    So to look at the log space size and allocation I used the DBCC SQLPERF(LOGSPACE); function which returned values of:

    databaseName    531103.6            1.064952             0

    Which tells me that the log file has been truncated.

    So I tried the shrink operation noted in the technote http://seer.entsupport.symantec.com/docs/253528.htm and the log file was still the same size.

    The technote above had a link to another technote so I went there and it described a manual shrink process called SHRINKFILE, which I tried with the same results.

    So I looked up DBCC SHRINKFILE (Transact-SQL) on MSDN and found that I should now do the following put the db into simple recovery mode, shrink the file then put it back into FULL recovery mode
    _________________________________________________
    GO
    -- Truncate the log by changing the database recovery model to SIMPLE.
    ALTER DATABASE DatabaseName
    SET RECOVERY SIMPLE;
    GO
    -- Shrink the truncated log file to 100 MB.
    DBCC SHRINKFILE (DatabaseName_log, 100);
    GO
    -- Reset the database recovery model.
    ALTER DATABASE DatabaseName
    SET RECOVERY FULL;
    GO
    _________________________________________________


    So I tried this on our developement server and it worked fine.

    What I would like to know is do you suggest this. And if so should I create a job to do this each week after a full backup and a log backup.

    My plan for backup is this. Full backup on Sunday night, log backup M-F and then a ShrinkFile on Saturday after the Log backup.

    Thanks again.

  • Ken_Putnam's avatar
    Ken_Putnam
    Level 6
    16 years ago

    Why worrry about shrinking the log file?  as long as it is flushed, why not just let MSSQL manage the space in the log file on it's own?
  • Tater_Race's avatar
    Tater_Race
    Level 2
    16 years ago

    That will be fine after I get the size down. But right now it is 1/2 a Terabyte and it is taking up too much space on the server. So I want to shrink it then I can back up the logs every day which will trunacate it and it should never get that big again.
     

  • Ben_L_'s avatar
    Ben_L_
    Level 6
    16 years ago
    The log backup does NOT shrink the file size. 

    All you should have to do is run the shrink to lower the file sizes, I have never had to run through all the steps that were posted above to correct the issue.  In SQL 2005/2008 you should be able to just right click the database and select Tasks - Shrink - Files. Select the logs from the File Type drop down and click OK.

    I would not suggest changing the recovery model from full to simple and back, this is going to purge everything in the logs.
  • Hywel_Mallett's avatar
    Hywel_Mallett
    Level 6
    16 years ago
    My experience is that if your SQL log files get very large, then you can do the following:
    Do a full database backup
    Do a log backup
    Shrink the database and its files
    Keep doing regular full and log backups to allow space in the log file to be reused.

    If you need to make very large database changes, then you can change it to bulk-logged mode temporarily, then change it back to full recovery model when you've done the changes.
  • Ken_Putnam's avatar
    Ken_Putnam
    Level 6
    16 years ago

    Sorry -

    didn't understand just how BIG the logs were in this case

    basically I was trying to say the same thing that Hywell said earlier today
  • Tater_Race's avatar
    Tater_Race
    Level 2
    16 years ago
    Thanks guys for the help.

    I did a full backup and then a log backup. That same day I tried to shrink the log files and they would not shrink. Needless to say I was concerned. But I came in the next morning and tried the shrink again and sure enough they shrank. I guess patience is a virtue.

    I really appreciate all of your help.