SQL 2005 transaction log growth
-
We created a database as a backend for our Datawarehouse processing. The database is set to use Simple Recovery mode. I noticed that the trans log size (the physical disk size) is about 21GB and growing. I thought that in the Simple Recovery mode the trans should be -- and remain -- a lot smaller.
Could a single, long running, transaction cause this type of growth, even in Simple mode?
TIA
-
Absolutely! However, in a DW situation it isn't likely that it is a user transaction causing this, it is more likely that this is being caused by either the ETL process or index maintenance.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Try enabling "SELECT INTO/BULKCOPY" database option using sp_dboption procedure...
MohammedU
Microsoft SQL Server MVP -
What does dbcc opentran ("insertYourdbNameHere") show you?
John Zacharkan -
John,
DBCC OPENTRAN will show you open transactions the database...
Are you asking the original poster to run this command?
MohammedU
Microsoft SQL Server MVP -
Sorry yes
of course, from there Bill can track back which is the offending process that's taking so long. There can be a number of reasons but that should get him pointed in the right direction. Another thing to look for is if the database is being replicated until repl_done is been made the log will continue to grow. Bill if you are replicating content run sp_repltrans as well.
Zach
John Zacharkan -
Yes, dbcc opentran did the trick . It showed that one long, big, transaction, was running. The developer was not committing properly. When I pointed this out to him, he corrected it and we haven't had a problem since.
TIA, to all!
Bill
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply