Created
April 16, 2016 17:13
-
-
Save rajanand/feeb918f105b4113c703e2d70249879a to your computer and use it in GitHub Desktop.
Parallel Data Warehouse (PDW): To close all the session connected to the database and then drop the database in PDW (Microsoft APS).
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 1. Get all the session id for the database. | |
-- 2. Kill all the session id. | |
-- 3. Drop database | |
--================================================ | |
DECLARE @TOTAL_CONNECTIONS INT; | |
DECLARE @INDEX INT; | |
DECLARE @RESTORE_DB_NAME VARCHAR(100); | |
DECLARE @SQL_KILL_SESSION NVARCHAR(1000); | |
DECLARE @SQL_DROP_DB NVARCHAR(1000); | |
SET @RESTORE_DB_NAME = 'DB_TO_BE_DROPPED' --> database name | |
SET @SQL_DROP_DB = 'DROP DATABASE '+@RESTORE_DB_NAME | |
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.DatabaseConnection')) | |
DROP TABLE dbo.DatabaseConnection | |
CREATE TABLE dbo.DatabaseConnection WITH (DISTRIBUTION = REPLICATE ) AS | |
SELECT | |
SESSION_ID, | |
ROW_NUMBER = ROW_NUMBER() OVER(ORDER BY SESSION_ID) | |
FROM SYS.DM_PDW_LOCK_WAITS | |
WHERE OBJECT_TYPE='DATABASE' AND OBJECT_NAME=@RESTORE_DB_NAME | |
SET @INDEX = 1; | |
SET @TOTAL_CONNECTIONS = (SELECT COUNT(*) FROM dbo.DatabaseConnection); | |
WHILE @INDEX <= @TOTAL_CONNECTIONS | |
BEGIN | |
SET @SQL_KILL_SESSION = (SELECT 'KILL ''' + CAST(SESSION_ID AS NVARCHAR(10)) + '''' FROM dbo.DatabaseConnection WHERE ROW_NUMBER=@INDEX) | |
PRINT @SQL_KILL_SESSION | |
EXEC SP_EXECUTESQL @SQL_KILL_SESSION | |
SET @INDEX += 1 | |
END | |
IF EXISTS (SELECT * FROM sys.databases WHERE name = @RESTORE_DB_NAME ) | |
--EXEC SP_EXECUTESQL @SQL_DROP_DB --***DROP DATABASE*** | |
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.DatabaseConnection')) | |
DROP TABLE dbo.DatabaseConnection |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment