Spid of -3 holding permanent lock on database and tempdb does not have any tables after restart

Question

Spid of -3 holding permanent lock on database and tempdb does not have any tables after restart

aurato

SSCrazy

Points: 2575
More actions
July 21, 2014 at 3:23 pm

#148495

I restarted my server's SQL Service when I could not kill a connection for nearly an hour. This caused many issues.
1) The database that the transaction was running against was stuck in recovery. The recovery was going to take an estimated 7 hours. The database was critical to several company programs, so I ran dump tran DBName with no_log, mainly because even without a transaction log backup chain, the DB is able to be rebuilt from data in other databases and it needed to come back up right then.
2) After the restart, tempdb and the model database had no tables. This caused an issue in our Great Plains 9 system where it was dependent upon two tempdb tables DEX_LOCK and DEX_SESSIONS. I created these tables with an online script from the GP website and that began to function again.
After any other given restart, when tempdb is recreated, it has no tables. Ditto with model.
3) All spids trying to access a table that the initial unkillable transaction was accessing are blocked by a spid of -3. This spid can't be killed and when I try to run this script I found online SELECT DISTINCT 'KILL ''' + CONVERT(VARCHAR(50),request_owner_guid) + ''';'
FROM sys.dm_tran_locks
WHERE request_session_id = -3
I get a request_owner_guid of '00000000-0000-0000-0000-000000000000' and trying to kill it does no good.
I am really lost and would appreciate anyone more knowledgeable helping me out here. Currently, the company is running without much issue, but I need to get this -3 out of the way and figure out what might be wrong with my system databases. Please help!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Spid -3 is a deferred transaction, Paul Randall blogs about it here[/url].

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

aurato (7/21/2014)
I restarted my server's SQL Service when I could not kill a connection for nearly an hour. This caused many issues.
...
2) After the restart, tempdb and the model database had no tables. This caused an issue in our Great Plains 9 system where it was dependent upon two tempdb tables DEX_LOCK and DEX_SESSIONS. I created these tables with an online script from the GP website and that began to function again.
After any other given restart, when tempdb is recreated, it has no tables. Ditto with model.
...

The [tempdb] is re-created whenever the SQL Service is started.So it cannot hold permanent tables inside. Perhaps the tables you mentioned were created by a stored procedure that runs automatically when the service is started. See this link for more information: http://msdn.microsoft.com/en-us/library/ms181720.aspx

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

aurato SSCrazy Points: 2575 More actions · Answer 3

This is what I ended up discovering. In addition to replication, an older version of the master database had the procedure that builds these tables set to run on startup. I don't know why a SQL Server restart would have undone that, but it did. Thanks for your help.

aurato SSCrazy Points: 2575 More actions · Answer 4

The deferred transaction eventually resolved itself, also. Thanks ChrisM.