How to reduce a large db log file size

Question

Post reply

How to reduce a large db log file size

setlan1983

SSCommitted

Points: 1636
More actions
December 7, 2009 at 10:37 pm

#385905

As mentioned above, how can I reduce the size of the db log file?
Currently I have a db log file size which is about 12GB. Then when I run a stored procedure using SQL Query Analyzer, it shows error message "The log file for database 'dbname' is full. Back up the transaction log for the database to free up some log space."
Current db using FULL recovery model. May I know what is the proper way to reduce the log file size?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I believe DBCC SHRINKFILE is available in SQL 2000.. Look at that..

CEWII

setlan1983 SSCommitted Points: 1636 More actions · Answer 2

Elliott W (12/7/2009)
I believe DBCC SHRINKFILE is available in SQL 2000.. Look at that..
CEWII

Tried, but the size not decreasing... still 12GB :pinch:

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Is the database configured for full or simple recovery. I'm guessing full..

You could do:

DUMP TRANSACTION databasename WITH NO_LOG

GO

DUMP TRANSACTION databasename WITH TRUNCATE_ONLY

GO

That should clear up the log..

CEWII

setlan1983 SSCommitted Points: 1636 More actions · Answer 4

Elliott W (12/7/2009)
Is the database configured for full or simple recovery. I'm guessing full..
You could do:
DUMP TRANSACTION databasename WITH NO_LOG
GO
DUMP TRANSACTION databasename WITH TRUNCATE_ONLY
GO
That should clear up the log..
CEWII

The database configured for FULL recovery.

After the DUMP TRANSACTION, can I recover the previous log?

What's is the different WITH NO_LOG & WITH TRUNCATE_ONLY?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

No, it would be lost.

They are the NO_LOG more or less marks all the log entries and backed up and the truncate clears them.

CEWII

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

setlan1983 (12/7/2009)
May I know what is the proper way to reduce the log file size?

The proper way is to schedule regular log backups so that the space in the log is reused and you have point-in-time recovery.

Before you truncate the log, please read through this - Managing Transaction Logs[/url]

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

setlan1983 (12/7/2009)
What's is the different WITH NO_LOG & WITH TRUNCATE_ONLY?

Nothing. From Books Online

NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

setlan1983 SSCommitted Points: 1636 More actions · Answer 8

GilaMonster (12/8/2009)
setlan1983 (12/7/2009)
May I know what is the proper way to reduce the log file size?
The proper way is to schedule regular log backups so that the space in the log is reused and you have point-in-time recovery.
Before you truncate the log, please read through this - Managing Transaction Logs[/url]

Space in log is reused means the log size will no longer grow?

Example current log size is 10GB, if perform backup log regular, means the size will keep in 10GB?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

setlan1983 (12/8/2009)
Space in log is reused means the log size will no longer grow?
Example current log size is 10GB, if perform backup log regular, means the size will keep in 10GB?

Maybe. Depends on the rate of transactions and the frequency of log backups. If you're running log backups every hour and the DB does 40GB of transactions in an hour, then the log will grow to at least 40GB. If the DB only does 200MB of transactions in an hour then the log shouldn't grow

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 10

Excellent point, shrinking can be useful if you had a large transaction or other issue but if the file will normally grow to that large size over a day then you might as well leave it..

CEWII