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 recom
mend that you:
Backup the log to the filename 'nul' (I assume you don't want the log backup
s).
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 l
og backups.
See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some details a
bout 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 l
og
> file is over 50 GB. I would like to accomplish 2 things and am looking fo
r
> 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 growin
g
> this enormous again in the future. A co-worker dealt with a situation lik
e
> 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 problem
s
> 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 b
e
> greatly appreciated.
> Thanks!
> -Shane Diaz