Cursor to Kill all system Processes

DECLARE @DatabaseName nvarchar(50)

DECLARE @SPId int

DECLARE @SQL nvarchar(100)

--SET @DatabaseName = N'AdventureWorks2008'

SET @DatabaseName = DB_NAME()

DECLARE my_cursor CURSOR FAST_FORWARD FOR

SELECT SPId FROM MASTER..SysProcesses

WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @SPId

WHILE @@FETCH_STATUS = 0

BEGIN

 SET @SQL = 'KILL ' + CAST(@SPId as nvarchar(10))

 print @SQL

 EXEC sp_executeSQL @SQL

 --KILL @SPId -- Causing Incorrect syntax near '@spid'.

 FETCH NEXT FROM my_cursor INTO @SPId

END

CLOSE my_cursor

DEALLOCATE my_cursor

System procedure "sp_who" Detail and Uses

Select sp_who: it returns all system connections with following information.

sp_who returns a result set with the following information.

Column	Data type	Description
spid	smallint	Session ID.
ecid	smallint	Execution context ID of a given thread associated with a specific session ID. ECID = {0, 1, 2, 3, ...n}, where 0 always represents the main or parent thread, and {1, 2, 3, ...n} represent the subthreads.
status	nchar(30)	Process status. The possible values are: dormant. SQL Server is resetting the session. running. The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. For more information, see Using Multiple Active Result Sets (MARS). background. The session is running a background task, such as deadlock detection. rollback. The session has a transaction rollback in process. pending. The session is waiting for a worker thread to become available. runnable. The session's task is in the runnable queue of a scheduler while waiting to get a time quantum. spinloop. The session's task is waiting for a spinlock to become free. suspended. The session is waiting for an event, such as I/O, to complete.
loginame	nchar(128)	Login name associated with the particular process.
hostname	nchar(128)	Host or computer name for each process.
blk	char(5)	Session ID for the blocking process, if one exists. Otherwise, this column is zero. When a transaction associated with a specified session ID is blocked by an orphaned distributed transaction, this column will return a '-2' for the blocking orphaned transaction.
dbname	nchar(128)	Database used by the process.
cmd	nchar(16)	Database Engine command (Transact-SQL statement, internal Database Engine process, and so on) executing for the process.
request_id	int	ID for requests running in a specific session.

Procedure that returns Insert statement for a table "sp_generate_inserts" Using Where condition

-- I have picked this from some other blog and edit it for Where clause

GO
/****** Object:  StoredProcedure [dbo].[sp_generate_inserts]    Script Date: 12/13/2011 17:22:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


sp_generate_inserts @table_name='table',@WhereClause = 'where id = 6'

create PROCEDURE [dbo].[sp_generate_inserts]
(
@table_name varchar(776),   -- The table/view for which the INSERT statements will be generated using the existing data
@target_table varchar(776) = NULL, -- Use this parameter to specify a different table name into which the data will be inserted
@include_column_list bit = 1, -- Use this parameter to include/ommit column list in the generated INSERT statement
@from varchar(800) = NULL, -- Use this parameter to filter the rows based on a filter condition (using WHERE)
@include_timestamp bit = 0, -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement
@debug_mode bit = 0, -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
@owner varchar(64) = NULL, -- Use this parameter if you are not the owner of the table
@ommit_images bit = 0, -- Use this parameter to generate INSERT statements by omitting the 'image' columns
@ommit_identity bit = 0, -- Use this parameter to ommit the identity columns
@top int = NULL, -- Use this parameter to generate INSERT statements only for the TOP n rows
@cols_to_include varchar(8000) = NULL, -- List of columns to be included in the INSERT statement
@cols_to_exclude varchar(8000) = NULL, -- List of columns to be excluded from the INSERT statement
@disable_constraints bit = 0, -- When 1, disables foreign key constraints and enables them after the INSERT statements
@ommit_computed_cols bit = 0, -- When 1, computed columns will not be included in the INSERT statement
@setid_on bit =0,
@WhereClause varchar(1000) = NULL
)
AS
BEGIN


SET NOCOUNT ON
--ERRROR handling routines for the specified parameters

IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))
BEGIN
RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)
RETURN -1
END
IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0))
BEGIN
RAISERROR('Invalid use of @cols_to_include property',16,1)
PRINT 'Specify column names surrounded by single quotes and separated by commas'
PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"'
RETURN -1
END

IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0))
BEGIN
RAISERROR('Invalid use of @cols_to_exclude property',16,1)
PRINT 'Specify column names surrounded by single quotes and separated by commas'
PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"'
RETURN -1
END
IF (PARSENAME(@table_name,3)) IS NOT NULL
BEGIN
RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)
RETURN -1
END

IF @owner IS NULL
BEGIN
IF ((OBJECT_ID(@table_name,'U') IS NULL) AND (OBJECT_ID(@table_name,'V') IS NULL))
BEGIN
RAISERROR('User table or view not found.',16,1)
PRINT 'You may see this error, if you are not the owner of this table or view. In that case use @owner parameter to specify the owner name.'
PRINT 'Make sure you have SELECT permission on that table or view.'
RETURN -1
END
END
ELSE
BEGIN
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = @owner)
BEGIN
RAISERROR('User table or view not found.',16,1)
PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.'
PRINT 'Make sure you have SELECT permission on that table or view.'
RETURN -1
END
END

--Variable declarations
DECLARE @Column_ID int,
@Column_List varchar(8000),
@Column_Name varchar(128),
-- @Start_Insert1 varchar(786),
@Start_Insert varchar(786),
@Data_Type varchar(128),
@Actual_Values varchar(8000), --This is the string that will be finally executed to generate INSERT statements
@IDN varchar(128) --Will contain the IDENTITY column's name in the table
--            @setid_off varchar(200)
--Variable Initialization
SET @IDN = ''
SET @Column_ID = 0
SET @Column_Name = ''
SET @Column_List = ''
SET @Actual_Values = ''
--set @setid_off='SET IDENTITY_INSERT OFF'


--IF  @owner IS NULL AND  @setid_on = 1
-- BEGIN
--        SET @Start_Insert1= 'SET IDENTITY_INSERT ON'
-- SET @Start_Insert = @Start_Insert1 +' '+ 'INSERT INTO ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'
--
-- END





 IF @owner IS NULL
BEGIN
       

SET @Start_Insert = 'INSERT INTO ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'
END
ELSE
BEGIN
SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'
END


--To get the first column's ID

SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name AND
(@owner IS NULL OR TABLE_SCHEMA = @owner)
--Loop through all the columns of the table, to get the column names and their data types
WHILE @Column_ID IS NOT NULL
BEGIN
SELECT @Column_Name = QUOTENAME(COLUMN_NAME),
@Data_Type = DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE ORDINAL_POSITION = @Column_ID AND
TABLE_NAME = @table_name AND
(@owner IS NULL OR TABLE_SCHEMA = @owner)
IF @cols_to_include IS NOT NULL --Selecting only user specified columns
BEGIN
IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0
BEGIN
GOTO SKIP_LOOP
END
END

IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns
BEGIN
IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0
BEGIN
GOTO SKIP_LOOP
END
END

--Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1
BEGIN
IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column
               
SET @IDN = @Column_Name

ELSE
GOTO SKIP_LOOP
END

--Making sure whether to output computed columns or not
IF @ommit_computed_cols = 1
BEGIN
IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1
BEGIN
GOTO SKIP_LOOP
END
END


--Tables with columns of IMAGE data type are not supported for obvious reasons
IF(@Data_Type in ('image'))
BEGIN
IF (@ommit_images = 0)
BEGIN
RAISERROR('Tables with image columns are not supported.',16,1)
PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.'
PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.'
RETURN -1 --Failure. Reason: There is a column with image data type
END
ELSE
BEGIN
GOTO SKIP_LOOP
END
END

--Determining the data type of the column and depending on the data type, the VALUES part of
--the INSERT statement is generated. Care is taken to handle columns with NULL values. Also
--making sure, not to lose any data from flot, real, money, smallmomey, datetime columns
SET @Actual_Values = @Actual_Values  +
CASE
WHEN @Data_Type IN ('char','varchar','nchar','nvarchar')
THEN
'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @Data_Type IN ('datetime','smalldatetime')
THEN
'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',109))+'''''''',''NULL'')'
WHEN @Data_Type IN ('uniqueidentifier')
THEN
'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @Data_Type IN ('text','ntext')
THEN
'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @Data_Type IN ('binary','varbinary')
THEN
'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
WHEN @Data_Type IN ('timestamp','rowversion')
THEN
CASE
WHEN @include_timestamp = 0
THEN
'''DEFAULT'''
ELSE
'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
END
WHEN @Data_Type IN ('float','real','money','smallmoney')
THEN
'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ',2)' + ')),''NULL'')'
ELSE
'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ')' + ')),''NULL'')'
END   + '+' +  ''',''' + ' + '

--Generating the column list for the INSERT statement
SET @Column_List = @Column_List +  @Column_Name + ','

SKIP_LOOP: --The label used in GOTO

SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name AND
ORDINAL_POSITION > @Column_ID AND
(@owner IS NULL OR TABLE_SCHEMA = @owner)


--Loop ends here!
END

--To get rid of the extra characters that got concatenated during the last run through the loop

SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)
SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)

IF LTRIM(@Column_List) = ''
BEGIN
RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)
RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter
END

--Forming the final string that will be executed, to output the INSERT statements

-- IF (@setid_on= 1 AND @include_column_list <> 0)
-- BEGIN
-- SET @Actual_Values =
-- 'SELECT ' +
-- CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
-- '''' + RTRIM(@Start_Insert) +
-- ' ''+' + '''(' + RTRIM(@Column_List) +  '''+' + ''')''' +
-- ' +''VALUES(''+ ' +  @Actual_Values  + '+'')''' + ' ' +
-- COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')
-- END



 IF (@include_column_list <> 0)
BEGIN
SET @Actual_Values =
'SELECT ' +
CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
'''' + RTRIM(@Start_Insert) +
' ''+' + '''(' + RTRIM(@Column_List) +  '''+' + ''')''' +
' +''VALUES(''+ ' +  @Actual_Values  + '+'')''' + ' '  +
COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')
+ ' ' + @WhereClause
END
ELSE IF (@include_column_list = 0)
BEGIN
SET @Actual_Values =
'SELECT ' +
CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
'''' + RTRIM(@Start_Insert) +
' '' +''VALUES(''+ ' +  @Actual_Values + '+'')''' + ' ' +
COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')
+ ' ' + @WhereClause
END
--IF @setid_on = 1
--BEGIN
--set @setid_off= 'Set IDENTITY_INSERT OFF'
--set @Actual_Values=@Actual_Values + @setid_off
--END
--Determining whether to ouput any debug information


IF @debug_mode =1
BEGIN
PRINT '/*****START OF DEBUG INFORMATION*****'
PRINT 'Beginning of the INSERT statement:'
PRINT @Start_Insert
PRINT ''
PRINT 'The column list:'
PRINT @Column_List
PRINT ''
PRINT 'The SELECT statement executed to generate the INSERTs'
PRINT @Actual_Values
PRINT ''
PRINT '*****END OF DEBUG INFORMATION*****/'
PRINT ''
END

PRINT '--INSERTs generated by ''sp_generate_inserts''
PRINT '--Build number: 22'
PRINT '--Problems/Suggestions?

PRINT '''
PRINT 'SET NOCOUNT ON'
PRINT ''


--Determining whether to print IDENTITY_INSERT or not
IF (@IDN <> '')
BEGIN
PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' ON'
PRINT 'GO'
PRINT ''
END


IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)
BEGIN
IF @owner IS NULL
BEGIN
SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
END
ELSE
BEGIN
SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
END

PRINT 'GO'
END

PRINT ''
PRINT 'PRINT ''Inserting values into ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' + ''''

Declare @vIncludeIdent Table(val varchar(max))

IF @setid_on = 1
Begin
Insert into @vIncludeIdent values ('SET Identity_Insert ON')
Insert into @vIncludeIdent  exec(@Actual_Values)
Insert into @vIncludeIdent values ('SET Identity_Insert OFF')
End
Else
Begin
Insert into @vIncludeIdent  exec(@Actual_Values)
End

--exec(@Actual_Values)
Select * from @vIncludeIdent
-- You'll get your INSERT statements, when the next line executes!


--IF @setid_on = 1
--BEGIN
--Set @setid_off ='Set IDENTITY_INSERT OFF'
--set @setid_off= @Actual_Values + @setid_off
--END
PRINT 'PRINT ''Done'''
PRINT ''


IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)
BEGIN
IF @owner IS NULL
BEGIN
SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL'  AS '--Code to enable the previously disabled constraints'
END
ELSE
BEGIN
SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'
END

PRINT 'GO'
END

PRINT ''
IF (@IDN <> '')
BEGIN
PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' OFF'
PRINT 'GO'
END

PRINT 'SET NOCOUNT OFF'


SET NOCOUNT OFF



RETURN 0
END

Enabling & Using Activity Monitor in SQL SERVER 2005/2008

Today i am desperate need of using Activity Monitor in SQL SERVER 2008 to find out slow running Query and Lock and Wait times to find blocking queries. When I open my sql server managment studio i did not find activity monitor and after some search in tools and menus i am unable to find any table that enables activity monitor because it is quite different from 2005. 

Now i have found a very good link that describe me how to enable and what type of information i can get through it. I am sharing this on my blog here. New Features in SSMS 2008

Activity Monitor

In SQL Server 2005, it was easy to find and start the Activity Monitor. You just opened up the Management object in SSMS and double-clicked on Activity Monitor, and you could instantly view process information. In SQL Server 2008, they have made the  Activity Monitor a little harder to find, but once you have learned where it is, you will probably be very impressed with all the new features that have been added.

To start Activity Monitor in SQL Server 2008 SSMS, right-click on the SQL Server name you want to monitor, and then click on Activity Monitor. The following screen appears:

Figure 1: The Activity Monitor has had a radical facelift, and feature boost.

Immediately, you can see that Activity Monitor looks entirely different than in SQL Server 2005. The first things that jump out at you are the four graphs. These display % Processor time (of the SQL Server process “sqlserv ” spread over all of the available CPUs, not for the entire server), Waiting tasks, Database I/O, and Batch Requests/sec. In the past when you needed this information, you had to use System Monitor or some other tool. Now, if your server is behaving strangely and you need a quick overview of what is happening, you can get it directly from Activity Monitor.

Below the graphs you will find four additional windows of information, the first of which (Processes) is shown in Figure 2:

Figure 2: This looks a little more familiar. Here, we see all of the active SPIDS.

When you open the Processes window, you see the SPIDs that you are so familiar with from the SQL Server 2005 SSMS Activity Monitor. Now, not only can you see the SPIDS, and sort them, but you can also filter on them using the drop-down boxes at the top of each column. And if you right-click on any of the SPIDs, you can choose to automatically launch Profiler, which will begin a trace on the SPID in question. This makes it very easy to begin a Profiler analysis of any SPID that you want to investigate.

Next, we move on to the Resource Waits window, shown in Figure 3:

Figure 3: We can see the current wait states of active threads.

The ‘Resource Waits’ screen provides a snapshot of key resource waits occurring on the server, thereby helping you to identify potential trouble with your SQL Server. Resource waits measure the amount of time a worker thread has to wait until it can gain access to the resources on the server that it needs, such as memory or CPU. A high resource wait time might indicate a resource bottleneck. As with Processes, you can sort and filter on any column.

The third window is Data File I/O, as shown in Figure 4:

Figure 4: Use the Data File I/O screen to identify databases with heavy I/O activity.

If you suspect that a particular database is being heavily hit with disk I/O, you can quickly find out by using the Data File I/O screen. You can sort and filter on any column with this or the other related screens.

The final screen is "Recent Expensive Queries":

Figure 5: Want to know what your most expensive queries are? Find out here.

If you are having performance problems due to resource-intensive queries, then the Recent Expensive Queries window will show you the most recent expensive queries (those currently in cache), allowing you to sort or filter them by any column, making it easy to identify problem queries. If you right-click any of the queries, you have the option of displaying the entire query (not just the small part of the query you see in the window) and you also have the option of displaying a graphical execution plan of the query.

Another feature that you might miss, if you are not careful, it the use of Tool Tips throughout all the screens of the Activity Monitor. If you move the cursor on top of almost any text on the Activity Monitor screen, a Tool Tip will appear, providing you with useful information on what you are seeing. Most of the data displayed in the Activity Monitor are from DMVs. Many of the Tool Tips even tell you the name of the DMV used to return the data you are viewing.

When you first lay hands on a copy of SQL Server 2008, you should start by trying out the new Activity Monitor. I guarantee it will make it much easier for you to quickly get a high-level perspective on what might be ailing your SQL Server. Once you know the big picture, then you can use other tools, such as DMVs, Profiler, or System Monitor, to drill down for more details.