Microsoft SQL Server Tips

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

FailOverCluster Event ID Error ESSENT 455, 489, 490


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:
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:

If you want to set DB into Online mode run:

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):

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

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

Check if DB has trustworthy enabled:

Change DB owner of files to SA:

Show informations about CLR Assemblies:

List enabled TRUSTWORTHY properties across all databases with SA dbowner:

List installed external CLR DLL procedures:

Show advanced configuration of SQL Server

Enable DAC

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

Orphaned users caused by migration of DB

Get list of orphaned users:

Fix orphaned users and link to newly created user logins:

List all users in MSSQL (including detailed informations)

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):

Now enable again recovery to FULL:

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 RUNcluadmin.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):

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):

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.