Systems Engineering and RDBMS

Archive for August 1st, 2008

Kill all sessions for a given database

Posted by decipherinfosys on August 1, 2008

Sometimes, when doing maintenance work or upgrading from one version of the database to another one, a DBA/Database Developer needs to make sure that there are no other sessions connected to the user database. Of course, prior to do anything like this one would notify everyone about the maintenance window and when services will be restored etc.. Re-starting services was not an option since this was being done on only one of the databases on the instance and not all the databases.

Prior to SQL Server 2005, the way to do that was to do something like this in a script:

a) Take the database name as an input parameter, collect all the spids for that database from sysprocesses.

b) Loop through and kill those sessions, log the record and if needed – send an e-mail alert to the concerned account if that information is available.

This used to be an issue obviously since if there is a service that the developer forgot to switch off prior to this work, it could keep on trying to make a connection and post the kill command, can again make a connection to the database and be active.

In SQL Server 2005, this is pretty simply achieved by just setting the database in a single user mode. I typically always do this using the ALTER DATABASE COMMAND since it is a simple script and one can run it, do their work and then turn it back to multi user again. Here are the commands to do it:

Suppose we want to set the single user option for the database called PLANGOALS on the instance, the command would be:

USE MASTER
GO
IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = ‘PLANGOALS’ AND IS_AUTO_UPDATE_STATS_ASYNC_ON = 1)
BEGIN
ALTER DATABASE PLANGOALS SET AUTO_UPDATE_STATISTICS_ASYNC OFF
END
GO
ALTER DATABASE PLANGOALS SET SINGLE_USER WITH ROLLBACK IMMEDIATE

What we are doing above is that we are first checking whether the asynch option is set to ON for the database and are first setting it to off to make sure that the background thread that is used to update the statistics does not take a connection to the database – this is important. After that, we set the database in the single user mode with the termination option of rollback immediate which will rollback any other open transactions from other sessions and terminate those sessions.

And if you are a GUI person, this option is also available via SSMS. In the object explorer, right click on the database in question and select properties. Go to Options and then in the Restrict Access option select Single. If there are open connections to this database, an Open Connections message will appear and you can just click on Yes to close other connections and move on with setting it to a single user mode.

And once you are done with your work, you can set the option back to MULTI_USER using the ALTER DATABASE command or from the GUI (if the GUI is going to be the only session through which you would be connecting):

ALTER DATABASE PLANGOALS SET MULTI_USER

Posted in SQL Server | Leave a Comment »

Simple Script to look for All Objects with a given String

Posted by decipherinfosys on August 1, 2008

Here is a simple SQL Server utility stored procedure to look up all the objects (the un-encrypted ones) in the schema for a given string. It takes in two parameters –

a) The string that you are searching for, and

b) A bit option to show the code also for objects that match the search like stored procedures, functions, views etc.

This procedure does have a limitation of not accounting for the wrap around of the word in the body of those objects i.e. if I am to search for say the Address table and if a procedure is using it in the procedure body however, the text field in the syscomments system table has multiple lines for that procedure because the procedure body exceeded nvarchar(4000) size limit and the Address word is split between more than one line, this code will not report it. There is a very simple resolution to it and one essentially just needs to write a function to allow for such search scenarios – we will post the code for that situation in an upcoming post.

Here is the utility procedure code:

IF EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = ‘USP_FIND_OBJECTS’
AND ROUTINE_SCHEMA = ‘dbo’
AND ROUTINE_TYPE = ‘PROCEDURE’
)
BEGIN
PRINT ‘Procedure dbo.USP_FIND_OBJECTS already exists…Dropping it and recreating’
DROP PROC dbo.USP_FIND_OBJECTS
END
ELSE
BEGIN
PRINT ‘Creating procedure dbo.USP_FIND_OBJECTS’
END
GO
CREATE PROCEDURE USP_FIND_OBJECTS
(
@str_name nvarchar(50),
@Show_Code bit = 0
)
AS
BEGIN
/*SET NOCOUNT FIRST*/
SET NOCOUNT ON

/*CREATE A TABLE TABLE TO HOLD THE OUTPUT*/
DECLARE @TEMP TABLE
(
OBJ_NAME NVARCHAR(50),
OBJ_TYPE NVARCHAR(30),
CREATE_DATE_TIME DATETIME,
OBJ_CODE NVARCHAR(4000)
)

/*

DO WE WANT TO SHOW THE CODE?
–0 is NO and 1 is YES

*/
IF (@Show_Code = 0)
BEGIN
INSERT INTO @TEMP
SELECT DISTINCT
CONVERT(nvarchar(50),SO.name),
SO.type,
crdate,
Null
FROM sysobjects SO
JOIN syscomments SC ON SC.id = SO.id
WHERE SC.text LIKE ‘%’ + @str_name + ‘%’
UNION
SELECT DISTINCT
convert(nvarchar(50),SO.name),
SO.type,
crdate,
Null
FROM sysobjects SO
WHERE SO.name LIKE ‘%’ + @str_name + ‘%’
UNION
SELECT DISTINCT
convert(nvarchar(50),SO.name),
SO.type,
crdate,
Null
FROM sysobjects SO
JOIN syscolumns SC ON SC.id = SO.ID
WHERE SC.name like ‘%’ + @str_name + ‘%’
END
ELSE
BEGIN
INSERT INTO @TEMP
SELECT
convert(nvarchar(50),SO.name),
SO.type,
crdate,
text
FROM sysobjects SO
JOIN syscomments SC ON SC.id = SO.id
WHERE SC.text LIKE ‘%’ + @str_name + ‘%’
UNION
SELECT
convert(nvarchar(50),SO.name),
SO.type,
crdate,
Null
FROM sysobjects SO
WHERE SO.name LIKE ‘%’ + @str_name + ‘%’
UNION
SELECT
convert(varchar(55),SO.name),
SO.type,
crdate,
Null
FROM sysobjects SO
JOIN syscolumns SC ON SC.id = SO.ID
WHERE SC.name LIKE ‘%’ + @str_name + ‘%’
END

IF (@Show_Code = 0)
BEGIN
SELECT
Obj_Name
,CASE (Obj_Type)
WHEN ‘P’ THEN ‘Procedure’
WHEN ‘TR’ THEN ‘Trigger’
WHEN ‘X’ THEN ‘Xtended Proc’
WHEN ‘U’ THEN ‘Table’
WHEN ‘C’ THEN ‘Check Constraint’
WHEN ‘D’ THEN ‘Default’
WHEN ‘F’ THEN ‘Foreign Key’
WHEN ‘K’ THEN ‘Primary Key’
WHEN ‘V’ THEN ‘View’
WHEN ‘TF’ THEN ‘SQL Table Valued Function’
WHEN ‘IF’ THEN ‘SQL Inline TVF’
WHEN ‘TA’ THEN ‘Assembly DML Trigger’
WHEN ‘FN’ THEN ‘SQL Scalar Function’
ELSE Obj_Type
END
,Create_Date_Time
FROM @TEMP
ORDER BY Obj_Type, Obj_Name
END
ELSE
BEGIN
SELECT
Obj_Name
,CASE (Obj_Type)
WHEN ‘P’ THEN ‘Procedure’
WHEN ‘TR’ THEN ‘Trigger’
WHEN ‘X’ THEN ‘Xtended Proc’
WHEN ‘U’ THEN ‘Table’
WHEN ‘C’ THEN ‘Check Constraint’
WHEN ‘D’ THEN ‘Default’
WHEN ‘F’ THEN ‘Foreign Key’
WHEN ‘K’ THEN ‘Primary Key’
WHEN ‘V’ THEN ‘View’
WHEN ‘TF’ THEN ‘SQL Table Valued Function’
WHEN ‘IF’ THEN ‘SQL Inline TVF’
WHEN ‘TA’ THEN ‘Assembly DML Trigger’
WHEN ‘FN’ THEN ‘SQL Scalar Function’
ELSE Obj_Type
END
,Create_Date_Time
,Obj_Code
FROM @TEMP
ORDER BY Obj_Type, Obj_Name
END

END
GO

And here is a sample execution:

EXEC USP_FIND_OBJECTS
@str_name = ‘Address’
,@show_code = 1

This would be useful to look up all the objects in the schema which match a particular string.

Posted in SQL Server | Leave a Comment »