Managing a Large Log File

Good Afternoon,
We have a particular database whose log file has grown particularly large on
one of our SQL servers (SQL 2000). The database is around 30 GB and the log
file is over 50 GB. I would like to accomplish 2 things and am looking for
any recommendations. First, I would like to get the log file down to a
reasonable size and secondly I would like to keep the log file from growing
this enormous again in the future. A co-worker dealt with a situation like
this in the past by taking the following steps:
-Detach the SQL database
-Rename, move or delete the existing log file
-Re-attach the database. At this point you will be notified that the log
file cannot be found and will be prompted to generate a new one.
-Set the recovery mode to Simple (as opposed to full) to prevent the log
file from growing this large again
Is this a reliable way of correcting this issue or is there a "best
practice" for performing something like this? Could any potential problems
arise from following the steps above? Is there a danger in setting the
recovery mode to Simple as opposed to Full? Any information anyone has
regarding how I can shrink the log file and prevent it from growing in the
future (but still be able to restore in the event of an emergency) would be
greatly appreciated.
Thanks!
-Shane Diaz
> -Detach the SQL database
> -Rename, move or delete the existing log file
> -Re-attach the database. At this point you will be notified that the log
> file cannot be found and will be prompted to generate a new one.
> Is this a reliable way of correcting this issue
I would never do that. I don't trust SQL Server creating a log file. I recommend that you:
Backup the log to the filename 'nul' (I assume you don't want the log backups).
DBCC SHRINKFILE the log file
Investigate the virtual log file layout (DBCC LOGINFO)
Repeat above steps two or three times until the log is at desired size.
Now set the recovery model to simple. Again, assuming you don't want to do log backups.
See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some details about log shrinking.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"shanediaz" <shanediaz@.discussions.microsoft.com> wrote in message
news:83B39C51-D0E5-48CF-B9A6-DD4E9256A877@.microsoft.com...
> Good Afternoon,
> We have a particular database whose log file has grown particularly large on
> one of our SQL servers (SQL 2000). The database is around 30 GB and the log
> file is over 50 GB. I would like to accomplish 2 things and am looking for
> any recommendations. First, I would like to get the log file down to a
> reasonable size and secondly I would like to keep the log file from growing
> this enormous again in the future. A co-worker dealt with a situation like
> this in the past by taking the following steps:
> -Detach the SQL database
> -Rename, move or delete the existing log file
> -Re-attach the database. At this point you will be notified that the log
> file cannot be found and will be prompted to generate a new one.
> -Set the recovery mode to Simple (as opposed to full) to prevent the log
> file from growing this large again
> Is this a reliable way of correcting this issue or is there a "best
> practice" for performing something like this? Could any potential problems
> arise from following the steps above? Is there a danger in setting the
> recovery mode to Simple as opposed to Full? Any information anyone has
> regarding how I can shrink the log file and prevent it from growing in the
> future (but still be able to restore in the event of an emergency) would be
> greatly appreciated.
> Thanks!
> -Shane Diaz