Kill multiple sessions – SQL SERVER

How to kill multiple sessions in SQL Server?
Killing one session is simple enough, you just issue kill command with SPID, KILL < SPID > , one thing to note in SQL Server SPID up to 50 are reserved for system databases(master, msdb etc.), so be careful when issuing the KILL command and make sure you are killing the right SPID, killing the SPID without knowing what the statement is doing can and will have adverse affect.

Ok, with that warning stated, back to the topic. How to kill multiple sessions? You have to write out KILL for each SPID you want to kill, this is where TSQL comes in handy. I don’t want to kill everything so I have included clauses for a particular database, program_name and lastwaittype. SPID <> @@SPID will filter out my current SPID, @@SPID returns current session id.


DECLARE @cmd varchar(255)

SELECT @cmd = COALESCE(@cmd,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID('< DB_NAME >') AND SPId <> @@SPId
and hostname = '< server_name >'
and program_name = 'IBM Cognos 8'
AND lastwaittype = 'ASYNC_NETWORK_IO'

SELECT @cmd

Add leading characters to field – SQL Server

You have custid column with values like (1, 2, 3500, 7800) etc. Now the boss says to make custid uniform length of 5 digits.

What to do?

Use RIGHT function to pad the field with leading 0’s. By using the RIGHT function we only add 0’s to values less than 5 digits in length.

–First test it out with select statement.

SELECT RIGHT('00000' + ISNULL(custid, ''), 5) from table

–Update the table

update table
set custid = RIGHT('00000' + ISNULL(custid, ''), 5)
where 1=1

Put Delay Between Query Execution – SQL Server

Sometimes a delay is needed between executing queries. TSQL let you do that by using WAITFOR statement.

This will pause for 1 sec before running the second query.

SELECT CONVERT(varchar(50), getdate(), 20)
WAITFOR DELAY '00:00:01'
SELECT CONVERT(varchar(50), getdate(), 20)

WAITFOR

WAITFOR has other options that can be used like pausing till a particular time by using TIME option.

See Microsoft page here.

INFORMATION_SCHEMA – SQL Server

There are occasion when you need to find metadata about database, tables, views etc. INFORMATION_SCHEMA is very useful for this type of information. You can get information about tables, column, column types etc. This is view is database specific, so the view will provide information about current database.

Example: SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Microsoft INFORMATION_SCHEMA PAGE

Update Statistics – SQL Server

To initiate stat update on whole database.

EXEC sp_updatestats;

Update Single Table.

UPDATE STATISTICS tableName ;

This will update stats on the particular table with sample size determined by query optimizer. You can specify WITH FULLSCAN option to scan the entire table, however this will take much longer than above.

Update Single Table WITH FULLSCAN.

UPDATE STATISTICS tableName WITH FULLSCAN;

Disable SQL Server Agent Job – SQL Server

Recently we had an issue with our tape backup system, we needed to quickly disable backup jobs to tape and enable backup jobs to drive. There were multiple jobs and using SSMS gui would have taken too much effort and time. To disable or enable multiple jobs update the sysjobs table in msdb.

Get list of jobs.

SELECT * FROM msdb.dbo.sysjobs

Update sysjobs table to disable job.

UPDATE msdb.dbo.sysjobs
SET enabled = 0
WHERE 1=1
AND job_id = '87D56BD2-EC90-4A6C-A00C-30A8F81133F5'

Get count of multiple tables using T-SQL – SQL Server

I was asked by my coworker get row count of tables with a particular column. So I came up with this little script to get it quickly using T-SQL.

DECLARE @tableName VARCHAR(255)
DECLARE @count smallint
DECLARE @stmt NVARCHAR(4000);

##Create insert statement into temp.

SET @stmt = 'INSERT INTO #tmpTable (tableNames, recCount)
SELECT ''** tableName **'', count(*) from ** tableName ** WHERE 1=1 and
= ''value''';

##Create temp table

CREATE table #tmpTable(
tableNames VARCHAR(255),
recCount smallint
)

##Declare CURSOR for select statement from sys.columns

DECLARE db_cursor CURSOR FOR
select distinct OBJECT_NAME(object_id) from sys.columns
where 1=1
and name = '
'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tableName

##Use while loop to insert record count in temp table.

WHILE @@FETCH_STATUS = 0

BEGIN

SET @stmt = REPLACE(@stmt, '** tableName **', @tableName);

EXEC sp_executesql @stmt;

SET @stmt = 'INSERT INTO #tmpTable (tableNames, recCount)
SELECT ''** tableName **'', count(*) from ** tableName ** WHERE 1=1 and
= ''value''';

FETCH NEXT FROM db_cursor INTO @tableName

END

Close db_cursor
DEALLOCATE db_cursor

##Get all the tables with count

select * from #tmpTable

#Drop temp table.

drop table #tmpTable