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.

Drop DB2 Database – DB2

To drop db2 databae issue following command.

Close db connections.
db2 quiesce db immediate force connections
Close application connection by force.
db2 force application all
Drop DB.
db2 drop db < name_db >

If the drop statement doesn't work, restart DB2 instance and then issue drop command.
db2stop
db2start

Restore DB2 Database – DB2

To restore DB2 database issue the following command after launching the DB2 Command Window.

db2 restore db < name_db > from < location_of_backup > taken at < time_backup_taken >

Restore as different DB2 database.

db2 restore db < name_db > from < location_of_backup > taken at < time_backup_taken > into < name_of_DB >

Restore DB replace existing

db2 restore db < name_db > from < location_of_backup > taken at < time_backup_taken > into < name_of_DB > replace existing

May need to issue following command if rollforward is not done.

db2 rollforward db < name_db > to end of logs
db2 rollforward db < name_db > complete

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;