question

Leo avatar image
Leo asked

Need to Shrink DB LOG

Hi, I have recently changed one of my Production DB Recovery Model from Simple to Full. I have done the Backup when I switched the Recovery Model. Now, I am having problem with Disk space and I have no time to buy another SAS disk. I need to clear the space to prevent the DB error before tomorrow. I just want to double check with you guys before I run the following query - alter database set recovery simple go checkpoint go alter database set recovery full go backup database to disk = 'c:\mydb.bak' with init go dbcc shrinkfile (N'mydb_log' , 1) go Currently *DB Size - 350GB / Log Size - 300GB* Then I will do regular Log backup to keep the DB in good shape. Is that correct procedure? Thanks.
sql-server-2008backuptransaction-log
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
its time to review the code you are executing and see if you can make it more efficient in terms of log file usage. Can you post the code? If you can I would suggest putting it in a new question rather than here.
1 Like 1 ·
Leo avatar image Leo commented ·
Log grow again ...gone back to 300GB even I done the regular log backup after I done the above script.
0 Likes 0 ·
Leo avatar image Leo commented ·
File Growth is set to 15%. Autogrowth is enabled. Set to unrestricted file growth to true. Shall I change that to Restricted File Growth in (MB) ?? Leave File Growth in percent as it is (15%)??
0 Likes 0 ·
Leo avatar image Leo commented ·
@John - It was too many query has been run this morning so that I can't monitor which one is make the Log File Bigger. Anyway can we track on why that happened or which query has the problem?? btw shall I run the above query again? What do you think?
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
It sounds like you switched to full recovery but did not then set up log backups. You must backup the logs when in full recovery. How often really depends on your system and what level of recoverability you're trying to achieve with the business. I've seen backups as frequent as every 5 minutes or as little as once an hour. Usually it's somewhere in between.
12 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
A log backup? A full backup doesn't affect the log at all. They are seperate critters.
3 Likes 3 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
If that's the case, then it might be worth changing the schedule of the log backup to do more frequent log dumps during the run period of the data load. If it's a regular data load... It'll slow things down a tad, but if your priority is keeping the TLog file size down that might be a trade-off worth making.
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
I agree with @Thomas, more frequent log backups, but I'd also check the data load process and see if you can't reduce the amount of log it needs, load in batches, that sort of thing.
1 Like 1 ·
Leo avatar image Leo commented ·
Grant - We do backup for every 12 hours and I did run the full backup only after I switched to Full Recovery.
0 Likes 0 ·
Tim avatar image Tim commented ·
@Leo, full backups do not truncate the transaction log as Grant has stated. You have to backup the transaction log or change the recovery model to truncate the log. Either issue a command similar to what @Cyborg has listed. If space is an issue where you don't have room to store a tlog backup of that size you can change your recovery model back to simple (log is then truncated), then back to full. You can then shrink the tlog and start making regular tlog backups.
0 Likes 0 ·
Leo avatar image Leo commented ·
@TRAD, yeap...schedule for Log backup every 8 hours now and full backup for every 24 hours. Just checked the Log and it looks pretty good. Not growing much and now 2GB. Thanks.
0 Likes 0 ·
Leo avatar image Leo commented ·
Problem again, this morning Disk is full up by log again even I have backup the log. Size is go back to 300GB... do you know what happen? Log backup run last night and it was fine until this morning about 7am.
0 Likes 0 ·
Leo avatar image Leo commented ·
File Growth is set to 15%. Autogrowth is enabled. Set to unrestricted file growth to true. Shall I change that to Restricted File Growth in (MB) ?? Leave File Growth in percent as it is (15%)??
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
A couple of things, hourly log backups might not be enough, first. Second, if the log is getting that big, then something is occuring to make it grow. You need to know what that something is. Do you have a massive data load that runs before 7AM? Are you running reindexing or something along those lines then? Yes, you need to get the log size right & get the backups going correctly, but you also need to address this issue that's filling up the log.
0 Likes 0 ·
Leo avatar image Leo commented ·
@John -yes, it did run massive data load at 9am this morning.
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
And, to provide a bit of symmetry, I agree with Grant.
0 Likes 0 ·
Leo avatar image Leo commented ·
Thanks. @Grant & @Thomas... so inactive transactions are still in TLog at the moment and I am not sure when check point will due. So shall I switch to simple recovery and run the check point manually and shrink the long and reset back to Full Recover and setup the regular backup for every 30mins or something??
0 Likes 0 ·
Tim avatar image
Tim answered
If your database is already in full recovery mode, you can backup the transaction log then shrink the logfile, then start doing regular tlog backups. A transaction log backup truncates the log.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
I support above answers. In SQL Server 2008 running log backup truncates the Log size but on the later versions you may need to run truncate and then shrinking it manually by following Command

BACKUP LOG  WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE('LogFile', 100)
But i have a suggestion on your code, size your log adequately to restrict frequent auto growth of your log file as frequent auto growth results in performance overhead.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image Leo commented ·
I don't think BACKUP LOG WITH TRUNCATE_ONLY is still support in sql 2008, is that correct?
0 Likes 0 ·
Cyborg avatar image Cyborg commented ·
Yes that feature is removed from SQL Server 2008
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.