cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Server backup - Commit Transaction Logs

Tater_Race
Level 2
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.
8 REPLIES 8

Ben_L_
Level 6
Employee
This technote should take care of the issue.  http://support.veritas.com/docs/253528

Tater_Race
Level 2
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
Level 6

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
Level 2

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_
Level 6
Employee
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
Level 6
Certified
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
Level 6

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
Level 2
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.