What's up!

Pyaarey Allah!

Wednesday, March 5, 2014

SQL Server - Kill process attached to database

0 comments

If you are trying to run a command


And you can't get access to the database because database might be in use by the single user and is in the single user mode.

There is a work around.

Run This command


SP_Who will show you processes attached to your database. See DBNAME Column and find your database in it. Then note down SPID. For example 72 is the SPID of the process you want to kill.
Then run this command to kill the process.

Kill command will kill the process. And then you will be able to run Set multi user mode command again and it should work.

Saturday, February 8, 2014

Shrink database and check its progress

0 comments

Claiming space from a SQL Server database is not like deleting all rows from a table or truncate it. Please follow the instructions if you want to reclaim your disk space.

  1. Run Sql Command:
    DBCC SHRINKDATABASE (MyDBName, 10 )
    Here MyDBName is a valid database name and 10 is the percentage of reserved free space.
  2. Run following command to check percentage done:
    select percent_complete, total_elapsed_time, estimated_completion_time, * from sys.dm_exec_requests where command = 'DbccFilesCompact'