<div>
<div class="content wysiwyg-content">
What's the proper way to reduce the size of a SQL Server log file. &nbsp;I have a database that is about 500MB in size but the log file on disk has grown to over 20GB and still climbing. &nbsp;It was my understanding that backups should purge the logfile and allow the log file on disk to be shrunk. &nbsp;This, however, does not seem to be the case.<br />
<br />
1) The data in the database is not being grown by much at all. &nbsp;There are probably only 75k-100k records of all types and there are perhaps 25 to 50 records being added or modified each day. &nbsp;(Actually, that's probably a gross OVER estimate. &nbsp;I'd be surprised if it's more than 10/day on average.)<br />
<br />
2) The database is backed up 2X a day using a scheduled backup database in Enterprise Manager. The backup file is about 500MB on disk.<br />
<br />
3) The scheduled maintenance happens once daily in the middle of the night when nobody is working on the database. &nbsp;But this doesn't seem to do anything useful.<br />
<br />
4) If I try to run DBCC SHRINKDATABASE() on it, the process runs for 10 minutes or so and then says:<br />
<br />
Cannot shrink log file 2 (Some filename here) because all logical log files are in use<br />
<br />
Any tips here would be appreciated.
</div>
</div>


What's the proper way to reduce the size of a SQL Server log file.  I have a database that is about 500MB in size but the log file on disk has grown to over 20GB and still climbing.  It was my understanding that backups should purge the logfile and allow the log file on disk to be shrunk.  This, however, does not seem to be the case.

1) The data in the database is not being grown by much at all.  There are probably only 75k-100k records of all types and there are perhaps 25 to 50 records being added or modified each day.  (Actually, that's probably a gross OVER estimate.  I'd be surprised if it's more than 10/day on average.)

2) The database is backed up 2X a day using a scheduled backup database in Enterprise Manager. The backup file is about 500MB on disk.

3) The scheduled maintenance happens once daily in the middle of the night when nobody is working on the database.  But this doesn't seem to do anything useful.

4) If I try to run DBCC SHRINKDATABASE() on it, the process runs for 10 minutes or so and then says:

Cannot shrink log file 2 (Some filename here) because all logical log files are in use

Any tips here would be appreciated.

SQL Server 2000 Log File Grown to Excessive Size

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.