How to drop database when used by other users.

When ever we are droping or deleting the databse if it is used by other users. Sql server displays the following error message.

"cannot drop database "Database_Name" because it is currently in use"

To avoid this error message and close all the existing connections and drop immedeiately you need to use the following syntax.

Change the Database_Name with your Database name to drop.


IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Database_Name')
BEGIN
PRINT 'DROP DATABASE [Database_Name']'
ALTER DATABASE [Database_Name'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [Database_Name']
END
GO

All SQL Server Session information in a SQLServer

How to get all session id's information which are connected to the database



select * from sysprocesses

How to display System database names in Sql Server

SELECT name , database_id FROM sys.databases WHERE owner_sid =1

How to display User Created Database names in Sql Server

SELECT name , database_id FROM sys.databases WHERE owner_sid <> 1

How to restore master (system) database

• How to restore master database backup.
o Stop the Sql Server
o Start the sql server in Single user mode using the command sqlservr.exe – m from command prompt.
o Restore the master database

• How to restore master database when the master database is corrupted.
o Execute the following command by placing setup disk with the following options by navigating the folder where setup.exe is present.
start /wait \setup.exe /qn
INSTANCENAME= REINSTALL=SQL_Engine
REBUILDDATABASE=1 SAPWD=
o After executing the above command, stop the sql server.
o Start the SQL Server in single user mode using the command sqlservr.exe –m
o Restore the master database from the backup.