|
Home > Archive > microsoft.public.sqlserver.server > December 2002 > Log file bloat
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| I have a database with a log file that is getting out of hand - about 66
GB for the log file versus 12 GB for the data file.
The database is built in batches, about 3 batches added each week and
about 3 old batches deleted each week.
About once every two weeks, I defrag the indexes, checkpoint after the
defrag, then
dbcc shrinkdatabase. But the log file keeps growing. The defragging
seems to add the most.
What could be going on? And how do I shrink the log file?
Thanks.
| |
| Mike John 2002-12-19, 4:24 pm |
| Are you backing up the log? Doing so will allow log space to be reused. DBCC
SHRINKFILE will help you shrink it.
Mike John
"W" <W@cluelessgeorge.gov> wrote in message
news:3E023B1E.7090506@cluelessgeorge.gov...
> I have a database with a log file that is getting out of hand - about 66
> GB for the log file versus 12 GB for the data file.
>
> The database is built in batches, about 3 batches added each week and
> about 3 old batches deleted each week.
>
> About once every two weeks, I defrag the indexes, checkpoint after the
> defrag, then
> dbcc shrinkdatabase. But the log file keeps growing. The defragging
> seems to add the most.
>
> What could be going on? And how do I shrink the log file?
>
> Thanks.
>
| |
| Keith Kratochvil 2002-12-19, 4:24 pm |
| If you do not use (or rely) on the log between database backups you can set
the recovery model to simple (within SQL Server 2000) or you can set the log
to truncate on checkpoint (SQL 7). If this is a production system, I would
recommend that you ignore that last comment and start taking backups of your
transaction log (as well as your database). If you don't care and just want
to recover disk space, you can follow my first instructions. Here is some
more information about shrinking the transaction log:
Q256650 INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/?kbid=256650
Q272318 INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?kbid=272318
--
Keith, SQL Server MVP
"W" <W@cluelessgeorge.gov> wrote in message
news:3E023B1E.7090506@cluelessgeorge.gov...
> I have a database with a log file that is getting out of hand - about 66
> GB for the log file versus 12 GB for the data file.
>
> The database is built in batches, about 3 batches added each week and
> about 3 old batches deleted each week.
>
> About once every two weeks, I defrag the indexes, checkpoint after the
> defrag, then
> dbcc shrinkdatabase. But the log file keeps growing. The defragging
> seems to add the most.
>
> What could be going on? And how do I shrink the log file?
>
> Thanks.
>
| |
|
| Thanks. The SQL Server 2000 did the job.
Keith Kratochvil wrote:
>If you do not use (or rely) on the log between database backups you can set
>the recovery model to simple (within SQL Server 2000) or you can set the log
>to truncate on checkpoint (SQL 7). If this is a production system, I would
>recommend that you ignore that last comment and start taking backups of your
>transaction log (as well as your database). If you don't care and just want
>to recover disk space, you can follow my first instructions. Here is some
>more information about shrinking the transaction log:
>
>Q256650 INF: How to Shrink the SQL Server 7.0 Transaction Log
>http://support.microsoft.com/?kbid=256650
>
>Q272318 INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
>http://support.microsoft.com/?kbid=272318
>
|
|
|
|
|