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

(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

MSSQL-Antivirus-Exclude1

MSSQL-Antivirus-Exclude2

MSSQL-Antivirus-Exclude3

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

SQL-Reset-SA-PW-1

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

SQL-Reset-SA-PW-2

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

SQL-Reset-SA-PW-3

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

SQL-Reset-SA-PW-4

  • 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”

SQL-Reset-SA-PW-5

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

SQL-Reset-SA-PW-6

 

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 Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">