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.
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.
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.
A log backup? A full backup doesn't affect the log at all. They are seperate critters.
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.
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.
@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.
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.
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)
