March 16, 2011 at 8:54 pm
We have a database that is in simple recovery mode. We only do full and differential backup for it.
But the transaction log keeps getting bigger. How can I fix this problem?
Should I set up a job to shrink the log every night?
What exactly the sql statement for this for SQL 2005?
Thanks
March 16, 2011 at 9:29 pm
use DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
Try to look if any maintenance jobs are going on and affecting the t log....
March 16, 2011 at 10:17 pm
The best solution for this is to set the transaction log to a suitable size and not shrink it.
If you are concerned about the size of it, then change the recovery model to "full" and implement transaction log backups.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2011 at 5:47 am
I don't like correcting people online, but don't follow Rohit's advice. Constantly shrinking your files leads to severe file fragmentation. You don't want to do this to a production server.
I would recommend that you first double check the options and determine if this database really is in Simple recovery. If it is, it sounds like the size and number of your transactions just requires more space. You might take a look at your code and processes to determine if you've got a problem there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 17, 2011 at 6:17 am
Recovery model is only one of the reasons why a log might grow. See http://www.sqlservercentral.com/articles/Transaction+Log/72488/
I'll echo Grant. Do not shrink the log regularly. Only after some abnormal operation has resulted in unusual growth.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply