February 14, 2013 at 7:40 am
I cannot seem to remove snapshot isolation on one of the databases.
Whenver I run:
ALTER DATABASE TimsTest SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
It runs forever (over 16 hours so far).
So, I was thinking this was because of other connections right?
No, I restored a second copy of the same db with another name, set it to single user mode and verified in process manager.
Still running forever.
What could cause this?
Is there any other way to turn it off?
It's an emergency as we have utilized all available space, and need to keep the tempdb small.
February 14, 2013 at 7:45 am
What is the statement's wait type?
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
February 14, 2013 at 7:48 am
Had the same issue in a Dynamics AX setup which needs the snapshot DB level to be on
If we had an issue with the row version store we ran this first and killed anything which came back, as they where classed as stale connections hogging resource
SELECT
SPR.SPID,
SPR.HostName,
SPR.Last_Batch,
SDMTASDT.elapsed_time_seconds
FROM
SYS.sysprocesses SPR
INNER JOIN
sys.dm_tran_active_snapshot_database_transactions SDMTASDT
ON
SPR.spid = SDMTASDT.session_id
WHERE
SDMTASDT.elapsed_time_seconds > 600
AND
SPR.last_batch <= DATEADD(hour,-2,getdate())
ORDER BY
spr.last_batch
Then ran to see what was using the row version store, and killed the connections off which would release bits of free space to TempDB bringing it back into normal levels < 20GB out of a 500GB Tempdb.
SELECT
ISNULL(A.Session_ID,0) AS ParentTransactionSPID,
ISNULL(C.hostname,'Parent Terminated Row Version Still Exists') AS ParentTransactionHostName,
ISNULL(C.last_batch,'') AS ParentTransactionLastTimeActive,
ISNULL(B.Session_ID,0) AS ChildTransactionSPID,
ISNULL(D.hostname,'No Child Transaction') AS ChildTransactionHostName,
ISNULL(D.last_batch,'') AS ChildTransactionLastTimeActive
FROM
sys.dm_tran_active_snapshot_database_transactions A
LEFT OUTER JOIN
sys.dm_tran_active_snapshot_database_transactions B
ON
A.transaction_sequence_num = B.first_snapshot_sequence_num
LEFT OUTER JOIN
sys.sysprocesses C
ON
C.spid = A.session_id
LEFT OUTER JOIN
sys.sysprocesses D
ON
D.spid = B.session_id
As far as I am aware this is the only way to switch the option off and will complete once all transactions have committed, as to why its not working the singleuser db, unsure, but would say check sys.dm_exec_requests to see if it is a percentage_complete showing operation.
February 14, 2013 at 7:49 am
The wait type is DISABLE_VERSIONING
February 14, 2013 at 8:36 am
Interesting. So, my next question is...do all sessions from other databases need to be disconnected from the version store for versioning to be disabled? If so, this may be the cause as their is another database on the same server with versioning ON that is always connected.
Can someone verify?
February 14, 2013 at 8:39 am
tim.cloud (2/14/2013)
Interesting. So, my next question is...do all sessions from other databases need to be disconnected from the version store for versioning to be disabled? If so, this may be the cause as their is another database on the same server with versioning ON that is always connected.Can someone verify?
Possibly
When you set ALLOW_SNAPSHOT_ISOLATION to a new state (from ON to OFF, or from OFF to ON), ALTER DATABASE does not return control to the caller until all existing transactions in the database are committed. If the database is already in the state specified in the ALTER DATABASE statement, control is returned to the caller immediately. If the ALTER DATABASE statement does not return quickly, use sys.dm_tran_active_snapshot_database_transactions to determine whether there are long-running transactions. If the ALTER DATABASE statement is canceled, the database remains in the state it was in when ALTER DATABASE was started. The sys.databases catalog view indicates the state of snapshot-isolation transactions in the database. If snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF will pause six seconds and retry the operation.
February 14, 2013 at 8:48 am
I got the transaction to finish this time!!
I had to kill the processes from the other databases that were connected to the version store.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply