Usually I can see that users are trying to put the database to offline either there planned activity or for some other reasons, but they are unable to put the database offline,so what is the wrong?

1.When ever if you are putting database to offline ensure that no users connected to that database & try to see did any other SQL server agent,windows schedular are not running with refernce to this database(The reason I mentioned here

just to ensure thats it).

so what if users are connected ->Can I go ahead and kill… 🙂
A)Please donot do this untill unless if the users says or confirms that you can kill them, because unknowingly if you kill you mighe be need to wait for the transactions to get it rollback completely so Imagine if somethings leads
the corruption(but any way I dont want to talk here out of information).
B)Better ask the user to comeout from his session.
C)Incase if you the batch Id that usually connects every time(Ofcourse you can kill ->I will show how it is) if you kill then it will reconnect again.. so ask the Application team to stop.

2.Then how can I check that who is connected to database.
Yes you can use sp_who2 ->But please donot use this one because we have good DMVs to get the information over here-

so try using the below scripts-

!)This shows the number of connections per each DB:
SELECT
    DB_NAME(dbid) as DBName,
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE
    dbid > 0
GROUP BY
    dbid, loginame

Incase if you are using the 2005+ onwards use the below DMVs to get the informations-
sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_requests

Other way you can see
=================
A)You can use the GUI to use for ex ->in sql server 2008 ->right click on the SQL instance from SSMS(SQL server Mangement studio)->Activity Monitor-> in right side you can see Overview page
 go to Processes tab
select which one you want( I mean spid) ->right click if you want to Kill you can & if you want to know what he is doing click on Details.

Incase of SQL server 2005->you can use Activity monitor & you can filter it out how you want..the best thing is that when you are taking the database offline you want to kill all Active connects for that database
you will get the  “DROP CONNECTION” check box ->Just check the box click on OK –>your work finished 🙂

B)The other simple way is as mentioned below-
Open SSMS->new query window-> run the below T-SQL
USE master
GO
ALTER DATABASE <your dbanme here>
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

It makes the Database offline..

Still you can get the scripts to kill one short time but usually I follow the above one-

Learn
========
Dont relay on sp_who2 ->Try to use DMVs.
Think different way how you can put..

Happy learning 🙂

Advertisements