November 22, 2013 at 12:13 am
Hi ,
I've got a peculiar problem with my VCENTER's SQL 2008 database.
My Database is running on SIMPLE recovery model. The problem is my transaction log keeps on increasing all the time until we stop the database and truncate/shrink it. That way i can run it for another one more day.
I cannot shrink/truncate it when it is running because it shows ACTIVE TRANSACTION.
I cannot restart it everyday just to stop the transaction log from growing. What do i do?
Please advice.
Thanks,
Jai
November 22, 2013 at 12:41 am
Something's leaving a transaction open. Identify who that is (from DBCC OpenTran and sys.dm_exec_sessions) and either commit the transaction or kill the session and let it rollback. Then do some investigations as to why transactions are getting left open every day.
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
November 22, 2013 at 12:59 am
dbcc opentrans tells me No active open transactions.
whereas the following query returns two rows
login name session_count
Adminstrator 3
sa 16
SELECT login_name ,COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name;
oops and btw...sorry this is SQL 2005 not 2008
November 22, 2013 at 1:09 am
Opentran is database-specific, run it in the database that has the growing log. If your log reuse wait is active transaction, then you have an open transaction.
The number of sessions per login is pretty irrelevant, it's not who's using the system you're interested in, it's who has an open transaction they have not committed. Check DBCC OpenTran, find the spid that it returns, check that spid in sys.dm_exec_sessions and sys.dm_exec_connections to identify who is running it and from where, then decide whether you can get the person to commit the tran (if it's ad-hoc queries) or roll it back after checking what it's doing.
Once you've identified which session has the open transaction, you need to figure out why there is an active transaction every day. Is someone running this manually? Is there a bug in the app? You're going to need to do some investigations.
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
November 22, 2013 at 1:12 am
I notice one idle session that have open tranasaction.
The program name is Service Runner and i suspect its probably a VMWare system process with status as sleeping and lock_timeout = -1
Not sure what that means, though. Can i just go ahead and kill it. How do i do that?
November 22, 2013 at 1:14 am
I suggest you go and make sure you know exactly what it is before you kill it, what it was running and from where. You shouldn't be guessing as to what's happening, check and be sure.
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
November 22, 2013 at 1:35 am
yup, a lock has been placed on the database by a process with the following atrributes:
owner id = 0
owner type = shared_transaction_workspace
objectid=0
request mode = S
November 22, 2013 at 1:46 am
That's normal, every single session will have a shared lock on the database it uses and it will hold that lock until the session ends. This is to ensure that a database cannot be dropped while a session is connected to it.
That lock is not the cause of your problems. Your problem is an open transaction that is preventing the log from being reused. Don't look at the locks tables, they're not going to tell you what's happening. Use DBCC OPENTRAN to identify which session(s) have an open (active) transaction and investigate those sessions, see where they are coming from, which machine, which user, which application
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
November 22, 2013 at 1:53 am
unfortunately all i get to see from DBCC OPENTRAN is:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
November 22, 2013 at 1:55 am
What database context are you running it in?
What database shows Active Transaction as the log_reuse_wait in sys.databases?
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
November 22, 2013 at 2:47 am
i've had to restart it again, because its run off space again. i will have an update soon..
November 22, 2013 at 3:43 am
USE Database_WithBigLog
DBCC OPENTRAN
November 26, 2013 at 7:42 pm
Thanks, That returned me the following results. From the start time this seems like this process's been running for over 3 days.
Oldest active transaction:
SPID (server process ID): 58
UID (user ID) : -1
Name : user_transaction
LSN : (473273:176905:59)
Start time : Nov 24 2013 11:31:44:010PM
SID : 0xb6e2b7cea93af54eb49780a9ff6d2b0b
November 26, 2013 at 7:53 pm
Adding on to my earlier message:
Noticed the following process triggered via SQL agent running since then; seems to be a DELETE command:-
purge_stat1_proc
November 26, 2013 at 9:58 pm
mJai (11/26/2013)
Thanks, That returned me the following results. From the start time this seems like this process's been running for over 3 days.Oldest active transaction:
SPID (server process ID): 58
UID (user ID) : -1
Name : user_transaction
LSN : (473273:176905:59)
Start time : Nov 24 2013 11:31:44:010PM
SID : 0xb6e2b7cea93af54eb49780a9ff6d2b0b
Yup, that looks like a suspect.
Query sys.dm_exec_sessions and sys.dm_exec_connections and see where session_id 58 is coming from, which login, what machine, what application. USe the most_recent_sql_handle column of sys.dm_exec_connections along with the sys.dm_exec_sql_text function to see the last thing it ran. That combined should give you a good idea of where this open transaction is coming from.
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 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply