Tuesday, February 26, 2013

SQL – Locked in Single-User Mode

If you ever want to see what sessions are active in SQL, or if you ever perform a SharePoint Backup through Central Administration, and SQL Server goes into single-user mode and does not remove that state – the following SQL query will return all active user sessions, by ID.

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where d.name = 'db_name'
GO
Once you get this ID, you can just manually kill the session, to the ID, and then disable single-user mode in the respective tables, etc.