Tag Archives: t-sql

Databases, MS SQL Server 2016, Tech Notes

Check Transactional Replication Status

2019-07-18 Jon Culp Leave a comment

select [distributionHistory].comments [Comments]
     , isnull([Last Error].error_text,'') as [Last Error]
     , convert(datetimeoffset, [distributionHistory].[time]) [Last Synchronized Date]
     , [distributionAgent].publication [Publication Name]
     , [distributionAgent].publisher_db as [Publisher Database]
     , (case
            when [distributionHistory].runstatus = '1' then 'Start'
            when [distributionHistory].runstatus = '2' then 'Succeed'
            when [distributionHistory].runstatus = '3' then 'InProgress'
            when [distributionHistory].runstatus = '4' then 'Idle'
            when [distributionHistory].runstatus = '5' then 'Retry'
            when [distributionHistory].runstatus = '6' then 'Fail'
            else cast([distributionHistory].runstatus as varchar)
        end
       ) [Run Status]
     , sub.name [Subscriber]
     , [distributionAgent].subscriber_db [Subscriber Database]
     , (case
            when [distributionAgent].subscription_type = '0' then 'Push'
            when [distributionAgent].subscription_type = '1' then 'Pull'
            when [distributionAgent].subscription_type = '2' then 'Anonymous'
            else cast([distributionAgent].subscription_type as varchar)
        end
       ) [Subscription Type]
     , und.UndelivCmdsInDistDB [Undelivered Commands]
from
    [distribution].[dbo].[MSdistribution_agents] as [distributionAgent] with (nolock)
    left join [distribution].[dbo].[MSdistribution_history] as [distributionHistory] with (nolock)
        on [distributionHistory].agent_id = [distributionAgent].id
    join
    (
        select s.agent_id
             , MaxAgentValue.[time]
             , sum(case when xact_seqno > MaxAgentValue.maxseq then 1 else 0 end) as UndelivCmdsInDistDB
        from
            [distribution].[dbo].[MSrepl_commands] t with (nolock)
            join [distribution].[dbo].MSsubscriptions as s with (nolock)
                on (
                       t.article_id = s.article_id
                       and t.publisher_database_id = s.publisher_database_id
                   )
            join
            (
                select hist.agent_id
                     , max(hist.[time]) as [time]
                     , h.maxseq
                from
                   [distribution].[dbo].MSdistribution_history hist with (nolock)
                    join
                    (
                        select agent_id
                             , isnull(max(xact_seqno), 0x0) as maxseq
                        from [distribution].[dbo].MSdistribution_history with (nolock)
                        group by agent_id
                    ) as h
                        on (
                               hist.agent_id = h.agent_id
                               and h.maxseq = hist.xact_seqno
                           )
                group by
                    hist.agent_id
                  , h.maxseq
            ) as MaxAgentValue
                on MaxAgentValue.agent_id = s.agent_id
        group by
            s.agent_id
          , MaxAgentValue.[time]
    ) und
        on [distributionAgent].id = und.agent_id
           and und.[time] = [distributionHistory].[time]
    join [master].[sys].[servers] as sub with (nolock)
        on [distributionAgent].subscriber_id = sub.server_id
	outer apply (
           select top 1
                  error_text
           from [distribution].[dbo].MSrepl_errors with (nolock)
           where id = [distributionHistory].error_id
           order by time desc
       ) as  [Last Error];

select [distributionHistory].comments [Comments]

, isnull([Last Error].error_text,'') as [Last Error]

, convert(datetimeoffset, [distributionHistory].[time]) [Last Synchronized Date]

, [distributionAgent].publication [Publication Name]

, [distributionAgent].publisher_db as [Publisher Database]

, (case

when [distributionHistory].runstatus = '1' then 'Start'

when [distributionHistory].runstatus = '2' then 'Succeed'

when [distributionHistory].runstatus = '3' then 'InProgress'

when [distributionHistory].runstatus = '4' then 'Idle'

when [distributionHistory].runstatus = '5' then 'Retry'

when [distributionHistory].runstatus = '6' then 'Fail'

else cast([distributionHistory].runstatus as varchar)

end

) [Run Status]

, sub.name [Subscriber]

, [distributionAgent].subscriber_db [Subscriber Database]

, (case

when [distributionAgent].subscription_type = '0' then 'Push'

when [distributionAgent].subscription_type = '1' then 'Pull'

when [distributionAgent].subscription_type = '2' then 'Anonymous'

else cast([distributionAgent].subscription_type as varchar)

end

) [Subscription Type]

, und.UndelivCmdsInDistDB [Undelivered Commands]

from

[distribution].[dbo].[MSdistribution_agents] as [distributionAgent] with (nolock)

left join [distribution].[dbo].[MSdistribution_history] as [distributionHistory] with (nolock)

on [distributionHistory].agent_id = [distributionAgent].id

join

(

select s.agent_id

, MaxAgentValue.[time]

, sum(case when xact_seqno > MaxAgentValue.maxseq then 1 else 0 end) as UndelivCmdsInDistDB

from

[distribution].[dbo].[MSrepl_commands] t with (nolock)

join [distribution].[dbo].MSsubscriptions as s with (nolock)

on (

t.article_id = s.article_id

and t.publisher_database_id = s.publisher_database_id

)

join

(

select hist.agent_id

, max(hist.[time]) as [time]

, h.maxseq

from

[distribution].[dbo].MSdistribution_history hist with (nolock)

join

(

select agent_id

, isnull(max(xact_seqno), 0x0) as maxseq

from [distribution].[dbo].MSdistribution_history with (nolock)

group by agent_id

) as h

on (

hist.agent_id = h.agent_id

and h.maxseq = hist.xact_seqno

)

group by

hist.agent_id

, h.maxseq

) as MaxAgentValue

on MaxAgentValue.agent_id = s.agent_id

group by

s.agent_id

, MaxAgentValue.[time]

) und

on [distributionAgent].id = und.agent_id

and und.[time] = [distributionHistory].[time]

join [master].[sys].[servers] as sub with (nolock)

on [distributionAgent].subscriber_id = sub.server_id

outer apply (

select top 1

error_text

from [distribution].[dbo].MSrepl_errors with (nolock)

where id = [distributionHistory].error_id

order by time desc

) as [Last Error];

Databases, MS SQL Server 2008 R2, MS SQL Server 2016, Tech Notes

T-SQL Rename Logical and Physical Database Files

2018-08-10 Jon Culp Leave a comment

I used to get really nervous about renaming database files, which I think is an appropriate response. Changing the attributes of the database’s physical objects does sound a bit scary on the surface, but once you’ve done it a few times you’ll realize it’s easy to do, and easy to fix if you make a mistake. This simple script template shows the basic steps of the process.

-- USE [master];
GO
--Change logical file names.
ALTER DATABASE [My_Database] 
  MODIFY FILE(NAME = [old_name_data], NEWNAME = [new_name_data]);
ALTER DATABASE [My_Database] 
  MODIFY FILE(NAME = [old_name_log], NEWNAME = [new_name_log]);
GO
--Disconnect all existing sessions.
ALTER DATABASE [My_Database] SET SINGLE_-- USER WITH ROLLBACK IMMEDIATE;
GO
--Change database to OFFLINE mode.
ALTER DATABASE [My_Database] SET OFFLINE;
GO

/******************************************
STOP! NOW RENAME THE PHYSICAL FILES ON DISK
******************************************/

--Point databases at new phyisical files.
ALTER DATABASE [My_Database] MODIFY FILE(NAME = 'new_name_data', 
  FILENAME = 'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\new_name_data.mdf');
ALTER DATABASE [My_Database] MODIFY FILE(NAME = 'new_name_log', 
  FILENAME = 'L:\MSSQL13.MSSQLSERVER\MSSQL\DATA\new_name_log.ldf');
GO
--Change database to ONLINE mode.
ALTER DATABASE [My_Database] SET ONLINE;
GO
--Reopen connections to all users
ALTER DATABASE [My_Database] SET MULTI_-- USER;
GO

-- USE [master];

--Change logical file names.

ALTER DATABASE [My_Database]

MODIFY FILE(NAME = [old_name_data], NEWNAME = [new_name_data]);

ALTER DATABASE [My_Database]

MODIFY FILE(NAME = [old_name_log], NEWNAME = [new_name_log]);

--Disconnect all existing sessions.

ALTER DATABASE [My_Database] SET SINGLE_-- USER WITH ROLLBACK IMMEDIATE;

--Change database to OFFLINE mode.

ALTER DATABASE [My_Database] SET OFFLINE;

/******************************************

STOP! NOW RENAME THE PHYSICAL FILES ON DISK

******************************************/

--Point databases at new phyisical files.

ALTER DATABASE [My_Database] MODIFY FILE(NAME = 'new_name_data',

FILENAME = 'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\new_name_data.mdf');

ALTER DATABASE [My_Database] MODIFY FILE(NAME = 'new_name_log',

FILENAME = 'L:\MSSQL13.MSSQLSERVER\MSSQL\DATA\new_name_log.ldf');

--Change database to ONLINE mode.

ALTER DATABASE [My_Database] SET ONLINE;

--Reopen connections to all users

ALTER DATABASE [My_Database] SET MULTI_-- USER;

MS SQL Server 2008 R2, MS SQL Server 2016, Tech Notes

Backup Transaction Logs to Nul for All Online Databases

2017-03-20 Jon Culp Leave a comment

First of all, never use this in a production environment! This script is to backup your transaction logs to a “nul device” for all online databases which are not using simple recovery model. In windows it is indeed spelled “nul” with one “L”. The only reason you would want to do this is if you have a non production environment using full recovery model and this server architecturally mirrors your production environment. For example, we have a “staging” server that is used for testing our code changes before they go into production. We require the staging environment to be as close to production as possible and have scheduled scripts that sync them weekly. In this scenario, we have many databases in the staging server that are using full recovery model but we do not want to backup the t-logs, we would rather just throw them away.

SET NOCOUNT ON

DECLARE @db AS sysname
DECLARE @SQL AS VARCHAR(250)
DECLARE @logged_DBs TABLE (DataBase_Name sysname)
INSERT INTO @logged_DBs ( DataBase_Name )
SELECT name FROM sys.databases WHERE recovery_model_desc != 'SIMPLE' and state_desc = 'ONLINE'

WHILE EXISTS (SELECT * FROM @logged_DBs)
BEGIN
	SET @db = (SELECT TOP 1 database_name FROM @logged_DBs)
	SET @SQL = 'BACKUP LOG '+@db+' TO DISK=''NUL'''
	EXEC (@SQL)
	DELETE FROM @logged_DBs where DataBase_Name = @db
END

SET NOCOUNT ON

DECLARE @db AS sysname

DECLARE @SQL AS VARCHAR(250)

DECLARE @logged_DBs TABLE (DataBase_Name sysname)

INSERT INTO @logged_DBs ( DataBase_Name )

SELECT name FROM sys.databases WHERE recovery_model_desc != 'SIMPLE' and state_desc = 'ONLINE'

WHILE EXISTS (SELECT * FROM @logged_DBs)

BEGIN

SET @db = (SELECT TOP 1 database_name FROM @logged_DBs)

SET @SQL = 'BACKUP LOG '+@db+' TO DISK=''NUL'''

EXEC (@SQL)

DELETE FROM @logged_DBs where DataBase_Name = @db

END

To learn more about NUL devices, here’s a link to the wikipedia page: https://en.wikipedia.org/wiki/Null_device

MS SQL Server 2008 R2

Collapse and Concatenate Rows

2016-07-13 Jon Culp Leave a comment

I had a need to concatenate and comma separate some multi-row data into an array of values with each having an unknown number of elements, in other words, take a many to one parent-child relationship and collapse the many child rows into the one parent record and separate the child record values with a comma. In the past, my default method to solve this problem was to build a temporary table and then use a loop to iterate through a data set and append the elements by updating rows in the temporary table, or use a common table expression with anchor and recursive members. Recently I stumbled upon the “stuff” and “for xml” functions. I had seen these functions before but never took the time to understand their potential use. These function can be used to solve the problem mentioned.

T-SQL For XML (Path Mode)

Function Description: A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in sub queries. The top-level FOR XML clause can be used only in the SELECT statement. In sub queries, FOR XML can be used in the INSERT, UPDATE, and DELETE statements. It can also be used in assignment statements.

In a FOR XML clause, you specify one of these modes: RAW, AUTO, EXPLICIT, PATH. We will only use PATH for this exercise.

The PATH mode together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a simpler manner.

The EXPLICIT mode allows more control over the shape of the XML. You can mix attributes and elements at will in deciding the shape of the XML. It requires a specific format for the resulting rowset that is generated because of query execution. This rowset format is then mapped into XML shape. The power of EXPLICIT mode is to mix attributes and elements at will, create wrappers and nested complex properties, create space-separated values (for example, OrderID attribute may have a list of order ID values), and mixed contents.

I will not list the syntax for this function because it can get pretty complex very quickly for all of the options. Instead, you can see in the example I just use it to concatenate the rows into a comma separated array. Any other XML is basically ignored by passing in the argument “Path (”)”.

(More information on For XML)

T-SQL Stuff Function

Function Description: The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Function Syntax: STUFF ( character_expression , start , length ,character_expression )

(More Information on the Stuff function)

Example Problem

List database users and include all roles of which a user is a member, separated by comma.

Solution using Stuff and For XML

“For XML” is used to collapse and concatenate the row data into a single array.
“Stuff” is used to remove the leading comma.
Note that we are using a correlated subquery when we reference “dp.principal_id” in order to limit our roles and role members to our database principals in the main outer query.

SELECT
dp.principal_id
,type_desc
,create_date
,modify_date 
,STUFF(( SELECT  ',' + CONVERT(VARCHAR(500),isnull(-- USER_NAME(mem.role_principal_id),''))
        FROM    sys.database_role_members mem
        WHERE   mem.member_principal_id = dp.principal_id
      FOR
        XML PATH('')
      ), 1, 1, '') AS Roles
from sys.database_principals  dp
where type != 'R'
and name not in ('dbo','guest','INFORMATION_SCHEMA','sys','MS_DataCollectorInternalUser')

SELECT

dp.principal_id

,type_desc

,create_date

,modify_date

,STUFF(( SELECT ',' + CONVERT(VARCHAR(500),isnull(-- USER_NAME(mem.role_principal_id),''))

FROM sys.database_role_members mem

WHERE mem.member_principal_id = dp.principal_id

FOR

XML PATH('')

), 1, 1, '') AS Roles

from sys.database_principals dp

where type != 'R'

and name not in ('dbo','guest','INFORMATION_SCHEMA','sys','MS_DataCollectorInternalUser')

Output:

principal_id	type_desc	create_date	modify_date	Roles
5	SQL_– USER	11/13/2002	6/19/2010	db_owner
7	SQL_– USER	11/13/2002	6/19/2010	db_owner,db_datareader,db_datawriter
8	WINDOWS_– USER	3/26/2009	3/26/2009	db_datareader
10	WINDOWS_– USER	3/16/2008	3/16/2008	db_datareader
11	SQL_– USER	7/11/2008	6/19/2010	WebViews
13	SQL_– USER	7/25/2014	7/25/2014	role_proxyusers
14	WINDOWS_– USER	3/12/2009	3/12/2009	db_datareader
16	SQL_– USER	7/25/2014	7/25/2014	role_proxyusers
18	SQL_– USER	8/26/2005	6/19/2010	db_owner
19	WINDOWS_– USER	6/2/2009	6/2/2009	MSReplPAL_9_1,db_datareader
20	WINDOWS_GROUP	10/3/2005	10/3/2005	db_datareader
21	SQL_– USER	7/14/2009	5/2/2016	role_proxyusers,db_datareader,db_denydatawriter

MS SQL Server 2008 R2, Tech Notes, Uncategorized

Disable then Enable SQL Agent Jobs

2016-01-22 Jon Culp Leave a comment

This script has three actions:
1) Put Current Job Information Into Temp Table
2) DISABLE All SQL Server Agent Jobs
3) ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling
Note: If you need to disconnect from the server after step 1 then step 3 will not be able to read from the temp table. Instead, you would have to feed a list of job IDs to the query. I have created a place for this just in case this is what you have to do.

Test Results:
Put Current Job Information Into Temp Table: 47 row(s) affected
DISABLE All SQL Server Agent Jobs: 33 row(s) affected
ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling: 33 row(s) affected

SET NOCOUNT ON

-- USE MSDB;

--Put Current Job Information Into Temp Table
SELECT * 
INTO #SYSJOBS	/* DROP TABLE #SYSJOBS */
FROM MSDB.dbo.sysjobs 
PRINT 'Put Current Job Information Into Temp Table: ' + CONVERT(varchar,@@rowcount)  +' row(s) affected'

SELECT * FROM #SYSJOBS
;

--DISABLE All SQL Server Agent Jobs
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1
PRINT 'DISABLE All SQL Server Agent Jobs: ' + CONVERT(varchar,@@rowcount)  +' row(s) affected'

SELECT * FROM MSDB.dbo.sysjobs
;

--ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling
UPDATE A
SET Enabled = 1
FROM MSDB.dbo.sysjobs A
WHERE Enabled = 0
AND (
	EXISTS (SELECT 1 FROM #SYSJOBS B WHERE B.enabled = 1 AND A.job_id = B.job_id) /*READ FROM TEMP TABLE*/
	OR CONVERT(varchar(MAX),job_id) IN ('') /*MANUALLY LIST JOB IDS*/
	)
PRINT 'ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling: ' + CONVERT(varchar,@@rowcount)  +' row(s) affected'

SELECT * FROM MSDB.dbo.sysjobs
;

SET NOCOUNT OFF

SET NOCOUNT ON

-- USE MSDB;

--Put Current Job Information Into Temp Table

SELECT *

INTO #SYSJOBS /* DROP TABLE #SYSJOBS */

FROM MSDB.dbo.sysjobs

PRINT 'Put Current Job Information Into Temp Table: ' + CONVERT(varchar,@@rowcount) +' row(s) affected'

SELECT * FROM #SYSJOBS

;

--DISABLE All SQL Server Agent Jobs

UPDATE MSDB.dbo.sysjobs

SET Enabled = 0

WHERE Enabled = 1

PRINT 'DISABLE All SQL Server Agent Jobs: ' + CONVERT(varchar,@@rowcount) +' row(s) affected'

SELECT * FROM MSDB.dbo.sysjobs

;

--ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling

UPDATE A

SET Enabled = 1

FROM MSDB.dbo.sysjobs A

WHERE Enabled = 0

AND (

EXISTS (SELECT 1 FROM #SYSJOBS B WHERE B.enabled = 1 AND A.job_id = B.job_id) /*READ FROM TEMP TABLE*/

OR CONVERT(varchar(MAX),job_id) IN ('') /*MANUALLY LIST JOB IDS*/

)

PRINT 'ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling: ' + CONVERT(varchar,@@rowcount) +' row(s) affected'

SELECT * FROM MSDB.dbo.sysjobs

;

SET NOCOUNT OFF

MS SQL Server 2008 R2, Tech Notes

Get and Kill Executing Processes of Job

2016-01-19 Jon Culp Leave a comment

This query will identify the current SQL processes or executions which belong to a specific job.

SELECT *
FROM master.dbo.sysprocesses p
JOIN msdb.dbo.sysjobs J
	ON UPPER(SUBSTRING(replace(p.program_name, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34))
	= UPPER(CONVERT(VARCHAR(34),convert(varbinary(16), J.Job_Id),1))
WHERE J.name = 'Some Job'

SELECT *

FROM master.dbo.sysprocesses p

JOIN msdb.dbo.sysjobs J

ON UPPER(SUBSTRING(replace(p.program_name, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34))

= UPPER(CONVERT(VARCHAR(34),convert(varbinary(16), J.Job_Id),1))

WHERE J.name = 'Some Job'

We had a job process that was getting blocked for long periods, and in turn was causing a lot of blocking. The best solution here is to fix the root blocker, however, as a short term fix we want to allow the job to run but then kill it a specific time if it is still running. To do this, we create another job that looks for all of the SPIDS associated to executions which belong to the job getting blocked, and execute a kill commend for each SPID.

SET NOCOUNT ON

--DECLARE VARIABLES
DECLARE @Job_ID UNIQUEIDENTIFIER = 'YOUR JOB ID'
DECLARE @tbl_SPIDS TABLE (SPID SMALLINT)
DECLARE @SPID SMALLINT
DECLARE @SQL VARCHAR(MAX)

--GET ALL SPIDS FOR ACTIVE JOB PROCESSES
INSERT INTO @tbl_SPIDS ( SPID )

SELECT p.spid
FROM   master.dbo.sysprocesses p
WHERE UPPER(SUBSTRING(replace(p.program_name, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34))
    = UPPER(CONVERT(VARCHAR(34),convert(varbinary(16), @Job_Id),1))

--KILL ACTIVE JOB PROCESSES
WHILE EXISTS (SELECT TOP 1 1 FROM @tbl_SPIDS)
BEGIN
	SET @SPID = (SELECT TOP 1 SPID FROM @tbl_SPIDS)
	SET @SQL = 'KILL '+CONVERT(VARCHAR(10),@SPID)
	EXEC (@SQL)
	DELETE FROM @tbl_SPIDS WHERE SPID = @SPID
END	

SET NOCOUNT OFF

SET NOCOUNT ON

--DECLARE VARIABLES

DECLARE @Job_ID UNIQUEIDENTIFIER = 'YOUR JOB ID'

DECLARE @tbl_SPIDS TABLE (SPID SMALLINT)

DECLARE @SPID SMALLINT

DECLARE @SQL VARCHAR(MAX)

--GET ALL SPIDS FOR ACTIVE JOB PROCESSES

INSERT INTO @tbl_SPIDS ( SPID )

SELECT p.spid

FROM master.dbo.sysprocesses p

WHERE UPPER(SUBSTRING(replace(p.program_name, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34))

= UPPER(CONVERT(VARCHAR(34),convert(varbinary(16), @Job_Id),1))

--KILL ACTIVE JOB PROCESSES

WHILE EXISTS (SELECT TOP 1 1 FROM @tbl_SPIDS)

BEGIN

SET @SPID = (SELECT TOP 1 SPID FROM @tbl_SPIDS)

SET @SQL = 'KILL '+CONVERT(VARCHAR(10),@SPID)

EXEC (@SQL)

DELETE FROM @tbl_SPIDS WHERE SPID = @SPID

END

SET NOCOUNT OFF

MS SQL Server 2008 R2, Tech Notes

Show SP_Who and Rollback Status for All SPIDS Rolling Back

2015-12-16 Jon Culp Leave a comment

We had a session we had to kill and roll back and wanted some more information about the status of the roll back. If you only have one SPID to check then you can run the kill commend with an option:

--KILL STATUS
KILL (SPID) WITH STATUSONLY

/*
OUTPUT:
SPID 127: transaction rollback in progress. Estimated rollback completion: 78%. Estimated time remaining: 5819 seconds.
*/

--KILL STATUS

KILL (SPID) WITH STATUSONLY

OUTPUT:

SPID 127: transaction rollback in progress. Estimated rollback completion: 78%. Estimated time remaining: 5819 seconds.

I wanted to expand this by dynamically looking for all sessions in rollback status and then provide some information about the session as well as the kill status. This way, I do not need to first manually review sessions and look for records in rollback status, then check each kill status.

/*********************************************************
SHOW SP_WHO AND ROLLBACK STATUS FOR ALL SPIDS Rolling Back
*********************************************************/

SET NOCOUNT ON

--CREATE VARIABLE TABLE TO HOLD RESULTS OF SP_WHO
DECLARE @SP_WHO TABLE (SPID smallint null, ecid smallint null, status nchar(30) null, loginame nchar(128) null, hostname nchar(128) null, blk CHAR(5) null, dbname NCHAR(128) null, cmd nchar(16) null, request_id int null)
INSERT INTO @SP_WHO --PREPARE TO RECEIVE DATA FROM SP_WHO
EXEC sp_who --EXECUTE SP_WHO WHICH WILL OUTPUT SESSION INFO

--ONLY KEEP SESSIONS IN ROLLBACK STATUS
DELETE FROM @SP_WHO WHERE STATUS != 'ROLLBACK'

--CHECK IF THERE ARE ANY ROLLBACK SESSIONS
IF 0 = (SELECT COUNT(*) FROM @SP_WHO)
	PRINT 'No active rollback sessions'
ELSE
BEGIN
	DECLARE @SPID INT
	DECLARE @PRINT NVARCHAR(MAX)
	DECLARE @SQL_KILLSTATUS NVARCHAR(MAX)

	WHILE 0 < (SELECT COUNT(*) FROM @SP_WHO) --LAZY LOOP
	BEGIN
		SET @SPID = (SELECT TOP 1 SPID FROM @SP_WHO)
		SET @PRINT = (SELECT TOP 1 'SPID: '+ISNULL(CONVERT(NVARCHAR(MAX),spid),'null')+', LOGINAME: '+LTRIM(RTRIM(ISNULL(loginame,'null')))+', HOSTNAME: '+LTRIM(RTRIM(ISNULL(hostname,'null')))+', DBNAME: '+LTRIM(RTRIM(ISNULL(dbname,'null'))) FROM @SP_WHO WHERE SPID = @SPID)
		PRINT @PRINT
		--BUILD KILLSTATUS COMMAND
		SET @SQL_KILLSTATUS = 'KILL '+CONVERT(VARCHAR(MAX),@SPID)+' WITH STATUSONLY'
		EXEC (@SQL_KILLSTATUS)
		
		--ITERATE LAZY LOOP
		DELETE FROM @SP_WHO WHERE SPID = @SPID
	END 
END

SET NOCOUNT OFF

/*
OUTPUT:
SPID: 127, LOGINAME: STARWARS\svc_etldi_prd, HOSTNAME: DTETLDI2, DBNAME: RD_SalesMart

SPID 127: transaction rollback in progress. Estimated rollback completion: 79%. Estimated time remaining: 5487 seconds.
*/

/*********************************************************

SHOW SP_WHO AND ROLLBACK STATUS FOR ALL SPIDS Rolling Back

*********************************************************/

SET NOCOUNT ON

--CREATE VARIABLE TABLE TO HOLD RESULTS OF SP_WHO

DECLARE @SP_WHO TABLE (SPID smallint null, ecid smallint null, status nchar(30) null, loginame nchar(128) null, hostname nchar(128) null, blk CHAR(5) null, dbname NCHAR(128) null, cmd nchar(16) null, request_id int null)

INSERT INTO @SP_WHO --PREPARE TO RECEIVE DATA FROM SP_WHO

EXEC sp_who --EXECUTE SP_WHO WHICH WILL OUTPUT SESSION INFO

--ONLY KEEP SESSIONS IN ROLLBACK STATUS

DELETE FROM @SP_WHO WHERE STATUS != 'ROLLBACK'

--CHECK IF THERE ARE ANY ROLLBACK SESSIONS

IF 0 = (SELECT COUNT(*) FROM @SP_WHO)

PRINT 'No active rollback sessions'

ELSE

BEGIN

DECLARE @SPID INT

DECLARE @PRINT NVARCHAR(MAX)

DECLARE @SQL_KILLSTATUS NVARCHAR(MAX)

WHILE 0 < (SELECT COUNT(*) FROM @SP_WHO) --LAZY LOOP

BEGIN

SET @SPID = (SELECT TOP 1 SPID FROM @SP_WHO)

SET @PRINT = (SELECT TOP 1 'SPID: '+ISNULL(CONVERT(NVARCHAR(MAX),spid),'null')+', LOGINAME: '+LTRIM(RTRIM(ISNULL(loginame,'null')))+', HOSTNAME: '+LTRIM(RTRIM(ISNULL(hostname,'null')))+', DBNAME: '+LTRIM(RTRIM(ISNULL(dbname,'null'))) FROM @SP_WHO WHERE SPID = @SPID)

PRINT @PRINT

--BUILD KILLSTATUS COMMAND

SET @SQL_KILLSTATUS = 'KILL '+CONVERT(VARCHAR(MAX),@SPID)+' WITH STATUSONLY'

EXEC (@SQL_KILLSTATUS)

--ITERATE LAZY LOOP

DELETE FROM @SP_WHO WHERE SPID = @SPID

END

SET NOCOUNT OFF

OUTPUT:

SPID: 127, LOGINAME: STARWARS\svc_etldi_prd, HOSTNAME: DTETLDI2, DBNAME: RD_SalesMart

SPID 127: transaction rollback in progress. Estimated rollback completion: 79%. Estimated time remaining: 5487 seconds.

In my test there was only one SPID in rollback status but it will work if there are more than one.

MS SQL Server 2008 R2, Tech Notes

List SQL Servers On Network

2015-12-15 Jon Culp Leave a comment

A list of servers can be obtained using windows command prompt (cmd) or powershell (ps). CMD and PS both provide Server Name and Instance Name, however, powershell provides two additional fields: IsClustered, and SQL Server Version. One challenge I have noticed is that running these commands from my local machine only returns a short list with names that look like computer names and not server names. I had to run the commands from a production server, a QA server, and a development server to obtain the server lists from those environments. There must be some network separation between them, which I am still investigating.

CMD: OSQL -L or SQLCMD -L

PS: [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

MS SQL Server 2008 R2, Tech Notes

Check if SQL Server is Clustered

2015-12-14 Jon Culp Leave a comment

--Returns property information about the server instance.
SELECT SERVERPROPERTY('ServerName') AS 'ServerName' , SERVERPROPERTY('IsClustered') AS 'IsClustered'

--Returns a list of failover clustered instance nodes on which an instance of SQL Server can run. This information is useful in failover clustering environments.
SELECT SERVERPROPERTY('ServerName') AS 'ServerName',
*
FROM sys.fn_virtualservernodes()

--Returns property information about the server instance.

SELECT SERVERPROPERTY('ServerName') AS 'ServerName' , SERVERPROPERTY('IsClustered') AS 'IsClustered'

--Returns a list of failover clustered instance nodes on which an instance of SQL Server can run. This information is useful in failover clustering environments.

SELECT SERVERPROPERTY('ServerName') AS 'ServerName',

FROM sys.fn_virtualservernodes()

For more information on SERVERPROPERTY(): https://msdn.microsoft.com/en-us/library/ms174396.aspx

MS SQL Server 2008 R2, Tech Notes

Random n% Records – Fastest Method

2015-12-03 Jon Culp Leave a comment

select /*top 1000000*/  --optional
* 
from [TABLE] with (nolock)
where (ABS(CAST((BINARY_CHECKSUM ({LIST PRIMARY KEY COLUMNS}, NEWID())) as int)) % 100) < 10

select /*top 1000000*/ --optional

from [TABLE] with (nolock)

where (ABS(CAST((BINARY_CHECKSUM ({LIST PRIMARY KEY COLUMNS}, NEWID())) as int)) % 100) < 10

Method Source: https://msdn.microsoft.com/en-us/library/cc441928.aspx

Jon Culp [.com]

Tag Archives: t-sql

Check Transactional Replication Status

T-SQL Rename Logical and Physical Database Files

Backup Transaction Logs to Nul for All Online Databases

Disable then Enable SQL Agent Jobs

Get and Kill Executing Processes of Job

Show SP_Who and Rollback Status for All SPIDS Rolling Back

Check if SQL Server is Clustered

Random n% Records – Fastest Method

An Information Archive for a Frivolous Human