December 7, 2009 at 10:37 pm
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?
December 7, 2009 at 10:57 pm
I believe DBCC SHRINKFILE is available in SQL 2000.. Look at that..
CEWII
December 7, 2009 at 11:03 pm
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:
December 7, 2009 at 11:09 pm
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
December 7, 2009 at 11:27 pm
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?
December 8, 2009 at 12:01 am
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
December 8, 2009 at 1:11 am
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
December 8, 2009 at 1:16 am
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
December 8, 2009 at 3:00 am
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?
December 8, 2009 at 3:05 am
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
December 8, 2009 at 6:52 am
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply