July 20, 2007 at 12:37 pm
Hello All
I am a Oracle DBA working recently on SQL Server; Sometime ago, since the upper limit on the log file size was not set & the backup failed for some reason, the log file grew and filled the disk. I therefore put the upper limit as 10GB; Recently for some unavaidable reasons, the transaction log backup failed (presently alert is not setup to notice the DBA of backup not running) and the log file grew in size again to 10GB limit set by me. Please note down the fact that there is lot of disk space available when this happened. Oracle has a different way of handling the scenario. It switches the log file contents to an archive file and essentially "nulls" the log file and starts all over.
Is there a way to do so in SQL Server? This is Win2K3 and Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
July 20, 2007 at 2:50 pm
Setting the Recovery Model to Simple will cause SQL Server to automatically truncate the logfile once everything has been written to the disk rather than waiting for the T-Log Backup. It can then reuse the freed up space. The down side is you will only be able to restore to the last DB Backup and not to a point in time as you can in Full Recovery Mode. Whether or not this is acceptable is up to you.
Alter Database YourDB set recovery simple
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply