| Author |
Topic |
|
lebedev
Posting Yak Master
126 Posts |
 Posted - 2009-09-16 : 12:37:01
|
| We noticed that our transaction logs grow very quickly and sometimes we cannot shrink them even after performing log backups. I would like to understand when a transaction log be shrunk? Specifically, can the log be always shrunk after full or transaction log backup?Can transaction log be shrunk during actively running transactions?Do I need to run CHECKPOINT command before shrinking the log?Is it recommended to set transaction log to grow automatically?Thanks,Alec |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-09-16 : 23:31:44
|
| you need to understand the architecture of Transaction log . Booksonline has good informations. They have virtual active log portion and inactive virtual portion . Under Full Recovery after every log backup inactive portion is freed . Under simple recovery model, it happens after every checkpoint. Inorder to shrink the log file immediately, you need to break log chain by Truncate_only option or simple recovery model. You won't be able to recover database in point in time after you set this. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-09-17 : 04:01:01
|
| Generally transaction logs should not be shrunk at all. Only real exception to this is when an unusual (once-off) operation has grown the log far beyond the size it needs to be.Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
|
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2009-09-21 : 11:57:07
|
| Thank you for the great article. It helped a lot.The article says: "Large data modifications, such as data loads or index rebuilds should be taken into account when calculating a log file size." We analyzed our log files and found that the vast majority of operations logged are insertions, expunges and deletions of index records. I suspect that most of them are logged during index rebuilds, which we do every night. Should we not rebuild indexes so often and instead reorganize them? A more general question is why index operations are being logged? Are they really required for database recovery and consistency if you can always rebuild them after recovering the data?Thanks,Alec |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-09-21 : 13:03:32
|
| Sure index rebuilds have to be logged. Imagine if they weren't and the DB crashed half-way through a rebuild. The result would be a suspect database because there would be no way to tell what had been done, how many of the modified pages had been written back to disk and how many had been modified only in memory and hence lost in the crash. Not good. All data modifications in SQL Server are logged, they have to be. The transaction log is how SQL ensures consistency and durability, two of the ACID properties of a database. Database recovery is a secondary use for the transaction log.In general, I'm not in favour of rebuilding all indexes every night. It's fine on small DBs, but on larger ones the time taken becomes prohibitive. Look for a good custom index rebuild script. One I like is found somewhere on this site - http://sqlfool.comThat will rebuild indexes only if they need rebuilding. It'll save you time and log space.The other thing you can do is switch the DB into bulk-logged recovery for the duration of the rebuild then back to full after. Index rebuilds can be minimally logged, so it reduces log impact. Read over the limitations on point-in-time restores if you go that route--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2009-09-22 : 12:48:15
|
| These are all very helpful. Thanks!Even after reading all suggested articles we are still missing something. We are trying to understand why our transaction log file is 6GB in size and has only 2% of free space after a transaction log backup.Here is our current state:* DB is in Full Recovery model* Log backups occur every 5 minutes* Log file size is 6GB with 8% free space* The sizes of recent log backups are around 500KB* The application that uses the database is shut down, but other databases are in use* The database server machine is not heavily loaded: CPU < 20%, avg. disk queue ~ 1* It takes over 10 minutes to execute select count(*) on a table with 20,000 rows in the database with 6GB TX logAny ideas why we are in this state and what needs to be done to get out of it?Thanks,Alec |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2009-09-22 : 13:53:35
|
| The database is not involved in any replication or database mirroring.The result of the query in the problematic database is "ACTIVE_TRANSACTION". (The same query run against other databases that do not have this problem returns "NOTHING".)Alec |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2009-09-22 : 15:23:51
|
| We don't have any large transactions. Moreover, the application has been shut down for a couple of hours and the load on the database server was light.So I don't understand why a transaction would generate or hold on to 6GB of transaction logs?Thank you for your help!Alec |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-09-22 : 15:48:51
|
| Because the log can only be truncated (marked for reuse) up to the beginning of the oldest open transaction, in case that transaction rolls back.If the log_reuse_wait_desc is active transaction, then you have an old, open transaction. It may not be from the app, it could be from a DBA who ran BEGIN TRANSACTION did some stuff and forgot to commit.Run DBCC OPENTRAN to see the spid that's got that transaction open. From that you can use sysprocesses to see the login name and the host name (machine name). Then you can chose to kill that connection or take whatever other action seems appropriate once you know who's responsible.--Gail ShawSQL Server MVP |
|
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2009-09-22 : 16:01:27
|
| This is explain a lot. Apparently the long-running transaction has recently completed and we were able to free up the transaction log. We will have to wait until the next time the database gets in this state and figure out which transaction ran away.Is there a way to configure SQL Server to rollback any transaction that ran for longer than a certain period of time?Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
When can transaction log be shrunk?