HK SQL Server Doctor: April 2015

2015-04-17

Quick Solve Unclosed Transaction

The quickest way to find a process that transaction is unclosed and what statements it's running is:

1. DBCC OPENTRAN

Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the CURRENT Database of this DBCC OPENTRAN running. You can obtain the Process ID.

2. DBCC INPUTBUFFER(@spid)

After you obtained the Process ID from DBCC OPENTRAN, you can run DBCC INPUTBUFFER to get the last statement sent from the client that the process run by passing in the process ID into it.

Example: We will create a bad stored procedure and run it for demonstration purpose.

/* A Bad Stored Proc. */

USE [TestDB]

CREATE PROC [dbo].[UnclosedTran]

@p1 varchar(50)

BEGIN

BEGIN TRAN

UPDATE Test1 SET col1 = @p1

END

/* Run that SP */

USE TestDB

EXECUTE UnclosedTran 'ABC'

/* Find the SPID of oldest process with transaction unclosed */

/* use sp_MSForEachDB to loop through all databases */

EXEC sp_MSForEachDB 'DBCC OPENTRAN(?)'

/* Get the SPID value from the DBCC OPENTRAN output, then see its input buffer */

DBCC INPUTBUFFER(61) /* replace your spid value */

After you take notes of the “EventInfo” (the bad stored procedure / sql script), then you may decide to KILL the process. E.g.

KILL 61 /* replace your spid value */

2015-04-15

How to Add New Table(s) to Transactional Replication WITHOUT Re-Generate the Whole Snapshot

You have transactional replication configured in your production environment. You need to add a new article (table) to the publication. You wish to initialize only the new article added to the publication, in order to avoid taking a total snapshot of all existing articles in the publication. Below steps teach you how to do:

1. Set Publication Properties

USE <DB>

DECLARE @publication sysname = '<publication>'

-- check immediate_sync

EXEC sp_helppublication @publication

-- If immediate_sync = 1, fix it (independent_agent must be 1) by disable immediate_sync

EXEC sp_changepublication

@publication = @publication,

@property = 'allow_anonymous', @value = 'False'

EXEC sp_changepublication

@publication = @publication,

@property = 'immediate_sync', @value = 'False'

--check

EXEC sp_helppublication @publication

2. Add New Table(s) to the Publication's Articles list

Right-click the Publication -> "Properties" -> "Articles" -> Uncheck "Show only checked articles in the list" -> Check the "New Table" in the list -> Press "OK", e.g.

3. Generate mini-Snapshot for the New Table(s)

Right-Click that "Publication" -> "View Snapshot Status" -> Press "Start".

It should only generate 1 article (if only one new table was added) as shown below:

2015-04-10

Analyzing Deadlock using built-in "system _health" Extended Event

Background:

By default, SQL Server automatically starts an Extended Events Session called “system_health” when it start-up. This session collects information includes any deadlocks that are detected. (ref. http://blogs.msdn.com/b/psssql/archive/2008/07/15/supporting-sql-server-2008-the-system-health-session.aspx

https://msdn.microsoft.com/en-us/library/ff877955.aspx)

Without any extra monitoring and loading, this default enabled “system_health” X-Events Session gives us a way to check the deadlock information, including the locked objects (table/index) and SQL statements/stored procedure calls involved in the deadlock.

This “system_health” events session logs detected events into memory, ring buffer size 4MB, when the buffer fills up it will overwrite the oldest entries.

How to do:

Run the following SELECT statement in production server:
(ref. http://blogs.msdn.com/b/sqlserverfaq/archive/2013/04/27/an-in-depth-look-at-sql-server-memory-part-2.aspx)Use Master

SELECT

xed.value('@timestamp', 'datetime') as Creation_Date,

xed.query('.') AS Extend_Event

FROM

(

SELECT CAST([target_data] AS XML) AS Target_Data

FROM sys.dm_xe_session_targets AS xt

INNER JOIN sys.dm_xe_sessions AS xs

ON xs.address = xt.event_session_address

WHERE xs.name = N'system_health'

AND xt.target_name = N'ring_buffer'

) AS XML_Data

CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)

ORDER BY Creation_Date DESC

The result is ordered by the time of the deadlock (latest on top) like following:
Click on the xml inside “Extend_Event” in the result, it will show you the deadlock xml report (the xml tags < and > were transformed to < and >, you can copy the XML into a notepad, and replace them for better view). E.g.

<victim-list>

<process-list>

</frame>

</frame>

</executionStack>

EXEC uspDeadlockTest_2 2002 </inputbuf>

</process>

</frame>

</frame>

</executionStack>

EXEC uspDeadlockTest_1 101 </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</ridlock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</ridlock>

</resource-list>

</deadlock>

</deadlock-list>

</value>

</data>

</event>

As you can see above, the affected sql statements or stored procedure calls are in the <inputbuf> element. The deadlock victim (process being killed and rolled-back by SQL Server engine) is identified by the process ID in <victimProcess>. The objects (table/index) involved are represented by Resource-IDs in different formats, including TAB (table), PAGE, KEY (index), and RID (row).
(ref.: https://support.microsoft.com/en-us/kb/224453 – section “Waitresource”)

In order to resolve the waitresource ID into the table/index name, you can run the follow SQL statements.
(these SQL can also be run in a Non-production sql server with a production database backup image restored on it, better the latest backup, as the object or data-page may not exists if the backup was too old).

The 1^st number in all different waitresource ID formats is the db_id, you can check the database name by this SQL:

SELECT DB_NAME(db_id) /* this DB_NAME statement can only be run on production server */

Then you can run the follow SQL for different waitresource formats, by setting the current database as the affected database:

USE <DBName>

For Object ID (OBJECT:db_id:object_id) / Table ID (TAB:db_id:object_id) :

SELECT OBJECT_NAME(object_id);

For HOBT ID (KEY:db_id:hobt_id) :

SELECT o.name AS TableName, i.name AS IndexName, SCHEMA_NAME(o.schema_id) AS SchemaName FROM sys.partitions p JOIN sys.objects o ON p.OBJECT_ID = o.OBJECT_ID JOIN sys.indexes i ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id WHERE p.hobt_id = hobt_id

For Page ID (PAGE:db_id:file_id:page_id) / Row ID (RID:db_id:file_id:page_id:slot) :

DBCC TRACEON(3604)

DBCC PAGE('DBName', file_id, page_id)

The object id will be shown in the result, Metadata: Object_Id = ???

The index id will be shown in the result, Metadata: IndexId = ???

Then you can get the table name and index name by OBJECT_NAME function and sys.indexes DMV.

SELECT OBJECT_NAME(Object_Id)

SELECT name FROM sys.indexes WHERE object_id = object_Id AND index_id = IndexId

2015-04-01

Email Alert of Long Uncommitted Transactions

Long running transaction in database causes performance impact to your system. Process blocking and deadlock will be arisen by lock waits on the resources being acquired by the long running transaction. Transaction log space cannot be reclaimed as there is an active transaction, the transaction log eventually out of space and the database becomes inaccessible (read-only). Also, if you have turned on READ_COMMITTED_SNAPSHOT for a database, SQL Server saves the before image of the data before starting a transaction in tempdb. This is called version store. If a transaction is running for a long time, the version store does not get cleared and it continues to grow filling up tempdb data file.
In order to monitor any long running (uncommitted) transactions in your database, you can create an email alert to send notification to your DBA team using Database Mail. Thanks for the script provided by Paul, I wrote the below script to send such email alert. You can create a SQL Agent Job, paste this script as the job step, and schedule the job to run periodically. (This script uses another stored procedure ConvertTableToHtml to render the result as HTML table, you should create this stored procedure before creating your job).

SET NOCOUNT ON;

-- **** Threshold in seconds ****
DECLARE @timeoutSec int = 15

DECLARE @recipients varchar(max), @subject nvarchar(255), @body nvarchar(max)

-- **** EMAIL RECIPIENTS, semicolon-delimited ; ****
SET @recipients = 'peter.lee@guosen.com.hk'

SET @subject = 'Long Uncommitted Transaction'
SET @body = 'Uncomitted transactions running longer than specified threshold ' + CAST(@timeoutSec AS varchar(10)) + ' seconds.'

DECLARE @htmlTable varchar(max)
CREATE TABLE #t (
[session_id] int,
[Login Name] nvarchar(128),
[Database] nvarchar(128),
[Begin Time] datetime,
[Duration (min.)] varchar(50),
[Last T-SQL Text] nvarchar(max)
)

INSERT #t SELECT
[s_tst].[session_id],
[s_es].[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
[s_tdt].[database_transaction_begin_time] AS [Begin Time],
CAST(CAST(DATEDIFF(second, [s_tdt].[database_transaction_begin_time], GETDATE()) / 60.0 AS decimal(9, 1)) AS varchar(50)) AS [Duration (min.)],
[s_est].text AS [Last T-SQL Text]
FROM
sys.dm_tran_database_transactions [s_tdt]
JOIN
sys.dm_tran_session_transactions [s_tst]
ON
[s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN
sys.[dm_exec_sessions] [s_es]
ON
[s_es].[session_id] = [s_tst].[session_id]
JOIN
sys.dm_exec_connections [s_ec]
ON
[s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
sys.dm_exec_requests [s_er]
ON
[s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
WHERE DATEDIFF(second, [s_tdt].[database_transaction_begin_time], GETDATE()) > @timeoutSec
AND s_tdt.database_id <> DB_ID('msdb') -- EXCLUDE msdb system database
ORDER BY
[Begin Time] ASC;

DECLARE @rowcount bigint
SELECT @rowcount = COUNT(*) FROM #t

IF @rowcount > 0
BEGIN
--Sending Mail
EXEC master.dbo.ConvertTableToHtml 'SELECT * FROM #t', @htmlTable OUTPUT
SET @body = @body + '<br/>' + @htmlTable
EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@body = @body,
@body_format = 'HTML',
@subject = @subject,
@importance = 'High';
END

DROP TABLE #t

Sample result:

Be aware that this script excluded the msdb database, as SQL Server uses this system database to run some background tasks.

網頁

2015-04-17

Quick Solve Unclosed Transaction

2015-04-15

How to Add New Table(s) to Transactional Replication WITHOUT Re-Generate the Whole Snapshot

2015-04-10

Analyzing Deadlock using built-in "system _health" Extended Event

2015-04-01

Email Alert of Long Uncommitted Transactions