June 30, 2015 at 7:43 am
Here are my results on tempdb
DbName | Recovery Model | PageVerify Option| Log_resuse_wait_desription | Log used in KB | log size in KB | Log used in %
tempdb|SIMPLE|CHECKSUM|ACTIVE_TRANSACTION|176944|291896|61
I ran sp_who2 active to see what is running on tempdb . I do not see any active transaction running on tempdb. How can I clear the active _transaction ?
June 30, 2015 at 7:44 am
Can you run DBCC OPENTRAN? Does it report any open transactions?
MCITP SQL 2005, MCSA SQL 2012
June 30, 2015 at 7:48 am
RTaylor2208 (6/30/2015)
Can you run DBCC OPENTRAN? Does it report any open transactions?
dbcc opentran('tempdb')
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
June 30, 2015 at 7:51 am
The transaction may not originate in tempdb though. You will need to repeat this in your user databases also.
MCITP SQL 2005, MCSA SQL 2012
June 30, 2015 at 7:55 am
Why is this a concern?
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
June 30, 2015 at 7:56 am
RTaylor2208 (6/30/2015)
The transaction may not originate in tempdb though. You will need to repeat this in your user databases also.
I ran the command in all the hundred databases and I don't see any active transactions, strange.
June 30, 2015 at 7:59 am
Never mind it is in CHECKPOINT state now. It was a concern because the queries were running very slow.
June 30, 2015 at 11:10 am
Log reuse reason, or a large tran log, or a 60% full tran log will not cause queries to run slow.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply