I previously posted about a SQL Server CheatSheet from DotNet4All that I thought was useful and comprehensive. If you already know what you need to do, but just forgot the syntax because you haven’t done it in eons, then  a well organized, concise, complete cheat sheet can save the day.

Here are a few other ones:

General SQL Server
DotNet4All

SQLAuthority
Scribd T-SQL

SQL Server Shortcuts
SQLArticles (really good, exhaustive list!)
SQLAuthority SSMS Shortcuts

Connection Strings
ConnectionStrings.com

Regex (especially when you’re working with SQLCLR/Regex)
.NET Regex Cheat Sheet
AddedBytes
(AddedBytes used to be known as ILoveJackDaniels, but that name has changed. Dave has a *lot* of useful cheat sheets in his site.)

SQL Injection
Ferruh Mavituna
ha.ckers

PowerShell
MSDN Blogs (PDF)
MSDN Blogs (MS Word)

SQL Server 2008
SQLAuthority

SQL Server DBA Interview Questions
SQLAuthority Interview Questions – Complete List

Problem:

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database <your db name> cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed."

Scenario:

Sometimes you need to have "exclusive" access to the database, let’s say,

• when you want to force a restore, or
• when you want to alter the database and an exclusive lock is required

To get this exclusive access, you may need to set the database to SINGLE_USER mode:

USE [master]

GO

ALTER DATABASE [DBName]

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

However, what if you accidentally lock yourself out of the database?

What if for some reason there is some other connection or user that is using the database, you will most probably get this error :

"Msg 5064, Level 16, State 1, Line 1

Changes to the state or options of database <your db name> cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed."

How do you get yourself back in?

Resolution:

1. Determine which connection is actually locking you out

-- look for the connection that uses your db and note the spid

EXEC sp_who

2. Kill the connection

KILL <spid>

3. Change the database back to MULTI_USER

ALTER DATABASE [DBName]

SET MULTI_USER;

GO

That should do it.