Basic SQL commands

On this page some simple but useful SQL commands are shown.

Check all sysadmins on a SQL node:

Sometimes after a restore a login is not working, to fix use the following command:

Force take database offline:

ALTER DATABASE failed because a lock could not be placed on database error. If you get this error run the upper line and kill the SPID that is connected to the database.

Check open connections to databases

Check if full search is installed on SQL server

Delen via: Facebooktwitterpinterestlinkedinmail

Check Microsoft SQL Availailbity Group database failover status

With the following script databases in an Microsoft Availability Group cluster can be checked if they are failover ready. Run this script at all the SQL nodes that are past of the Availability Group. When the statys is displayed ‘1’ the database is ready for an failover.


Delen via: Facebooktwitterpinterestlinkedinmail

Memory usage per Microsoft SQL database

The following script can be used to determine the memory usage per database:



Delen via: Facebooktwitterpinterestlinkedinmail

Script to backup all database Microsoft SQL node

The following script can be used to backup all databases on an Microsoft SQL node:


Delen via: Facebooktwitterpinterestlinkedinmail