CheatSheet of useful Transact-SQL statements for Microsoft SQL Server, tutorials, error codes etc.

FailOverCluster Event ID Error ESSENT 455, 489, 490

(https://support.microsoft.com/en-us/kb/2811566)

Occurs when moving Cluster-Group. SQL Service accounts doesn’t have sufficient permissions to write log files into Sum folder.
Set permission R/W for Database Engine account and Agent account on Sum folder located at:
C:WindowsSystem32LogFilesSum
It’s part of Software Usage Metrics feature.

 

Recommended settings for Antivirus program to exclude specific folders, process and file types

ALTER DATABASE failed because a lock could not be placed on database ‘myDB’. Try again later.

Usually occurs when running DB is set into Offline mode, then will be not accessible. Because of locked transactions inside DB can’t be set into offline mode, it can broke data consistency. To rollback locked transactions which blocks DB run:

ALTER DATABASE myDB SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO

If you want to set DB into Online mode run:

ALTER DATABASE myDB SET ONLINE
GO

If some process still blocks rollback, set DB into single-user mode (be aware of multiple open query windows in SSMS of myDB, every window means 1 connection to DB):

ALTER DATABASE myDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

After successful operation which we needs to do in DB switch back to multi-user mode:

ALTER DATABASE TestDb SET MULTI_USER

An error occurred in the Microsoft .NET Framework while trying to load assembly id 12345

Check if DB has trustworthy enabled:

SELECT is_trustworthy_on FROM sys.databases WHERE name = 'myDB’;
ALTER DATABASE Kredit SET TRUSTWORTHY ON;

Change DB owner of files to SA:

USE myDB
GO
EXEC sp_changedbowner ‘sa’

Show informations about CLR Assemblies:

SELECT * from sys.dm_clr_properties
SELECT * from sys.dm_clr_appdomains
SELECT * from sys.dm_clr_tasks
SELECT * from sys.dm_clr_loaded_assemblies

List enabled TRUSTWORTHY properties across all databases with SA dbowner:

SELECT SUSER_SNAME(owner_sid) AS DBOWNER, d.name AS DATABASENAME
FROM sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
INNER JOIN sys.server_principals p ON
p.principal_id = m.member_principal_id
inner join sys.databases d on suser_sname(d.owner_sid) = p.name
WHERE is_trustworthy_on = 1 AND d.name NOT IN ('MSDB') and r.type = 'R' and r.name = N'sysadmin'

List installed external CLR DLL procedures:

SELECT * 
FROM master.sys.extended_procedures;
GO

Show advanced configuration of SQL Server

USE master;
GO
EXEC sp_configure 'show advanced option', '1';
GO
RECONFIGURE;
GO

EXEC sp_configure;

Enable DAC

EXEC sp_configure 'remote admin connections', 1;
GO
RECONFIGURE
GO

Open TCP port 1434 for DAC. Command for connect to DAC in AD is:

sqlcmd -A -S mssql.contoso.com

Orphaned users caused by migration of DB

Get list of orphaned users:

USE myDB
GO
EXEC sp_change_users_login 'Report'

Fix orphaned users and link to newly created user logins:

USE myDB
GO
EXEC sp_change_users_login 'Auto_Fix', 'myUSER'

List all users in MSSQL (including detailed informations)

select * from master..syslogins

If disk is full because of transaction log

Set DB to simple mode and shrink log file to 20MB (you’ll lost everything inside transaction log):

ALTER DATABASE myDB SET RECOVERY SIMPLE;
USE myDB;
GO
CHECKPOINT;
GO
CHECKPOINT;
GO
DBCC SHRINKFILE(myDB_log, 20);
GO

Now enable again recovery to FULL:

ALTER DATABASE myDB SET RECOVERY FULL
GO

Reset SA password in single-user mode if you lost password (for Fail-Over cluster)

• In SSMS (SQL Server Management Studio) click View > Registered Servers – Inside Local Server Groups must be our SQL server. If there’s no SQL server, right click on Local Server Groups > Tasks > Register Local Servers

• Run from start menu RUN > cluadmin.msc, then turn off Core resource – in this case Take Offline mssql01

• Manually turn off second (passive) node – in this case mssql01b > More actions > Stop Cluster Service

• On Active node (mssql01a) turn off manually SQL services = SQLSERVERAGENT, MSSQLSERVER

• Run CMD as administrator
• Start command: net start MSSQLSERVER /m
  • If it can’t connect to DB, check if you stopped SQLServerAgent service because Agent will eat 1 permitted connection to DB which is maximum allowed.
  • Wen still can’t connect run: MSSQLSERVER /m”Ssms” or with SQLCMD: MSSQLSERVER /m”SQLCMD”

• Start SQL MGMT as administrator, then connect to SQL server with Windows Authentication
• In Security create new Login with these permissions: public, sysadmin

 

Exclusive access could not be obtained because the database is in use

Restore of database ‘myDB’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.SmoExtended)

If you want to restore DB, then you need to get exclusive access because all other connections needs to be dropped. Example (warning – in this example I use REPLACE):

USE [master]
ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [myDB] FROM  DISK = N'S:BackupMSSQL01-DBmyDBFULLMSSQL01-DB_myDB_FULL.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
RESTORE DATABASE [myDB] FROM  DISK = N'S:BackupMSSQL01-DBmyDBDIFFMSSQL01-DB_myDB_DIFF.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
GO

Error 1205 : Transaction 123 was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

It might happen that your transaction will be deadlocked because of bad T-Sql query or something other which eat your exclusive access till you haven’t finished your statement (because DB was set into Single user mode):

USE [myDB]
SET DEADLOCK PRIORITY HIGH;
ALTER DATABASE myDB set multi_user;
GO
SET DEADLOCK PRIORITY NORMAL

Instead of priority High (5) you can select from -10 to 10.