September 10, 2015 at 1:02 pm
Hi, how can i reduce the size of the transactional logs in SQL database?
thanks
September 10, 2015 at 4:12 pm
There are a lot of factors involved in how to reduce the size of log files and whether you even should.
Ideally, I would say make a priority of reading this: http://www.sqlservercentral.com/articles/books/94938/.
That will go over all the factors more effectively than I could in a forum post 🙂
Having said that, here's a (very!) brief tour of the considerations.
Basically, if a log has grown to a particular size as a result of the normal workload and backup strategy (for databases in the full recovery model, how frequently you back up the log affects the size the log will grow to for a particular workload), then unless workload is reduced or transaction log backups are taken more frequently, that is the size it needs to be.
If some unusual activity, say a very long-running rogue transaction, a disconnected mirror, etc., has pushed the log to an unusual size, then you might want to consider looking at shrinking the log to a more typical size.
Even in that case of unusual activity, though, if the drive housing the log file has plenty of space, then having a decent amount of headroom in the log file isn't a bad thing (quite the contrary, as that means unusual bursts of activity won't necessarily trigger costly autogrowth events).
That's just touching on some of the pieces involved, and is by no means a comprehensive look. I'd absolutely recommend taking a look at that book, or at least providing some more details about your situation so we can provide more pointed advice.
Cheers!
September 10, 2015 at 7:43 pm
The Gail Shaw book Jacob recommended is great.
An appropriate follow-up question is, "Why do you ask?"
-- Itzik Ben-Gan 2001
September 11, 2015 at 1:25 am
you can use following command to reduce log size of database .
use <database_name>
dbcc shrinkfile(2,10)
September 11, 2015 at 2:57 am
haridatpurohit (9/11/2015)
you can use following command to reduce log size of database .use <database_name>
dbcc shrinkfile(2,10)
You can, but that's a really bad thing to do to a busy database. SQL's going to have to spend extra time after that re-growing the log. Please read the book referenced above.
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