HK SQL Server Doctor

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-03-27

Transaction Log Disk Full due to Transactional Replication Not CatchUp

Problem Description:
When there are some bulk data operation (e.g. bulk data insert/update, create index/reindex, program bug leads to huge amount of updated rows) on a database publishing transactional replication, log reader agent may not catch up the bulk data operation. Transaction log of the affected DB cannot be truncated even log backup taken, until the log reader agent catch up the updated data. (Ref. http://support.microsoft.com/kb/317375 - section: Unreplicated transactions).

Emergency Resolution:
1. Assign extra transaction log space from another disk (if available, to buy time);
2. Stop the Log Reader Agent on the DB in Management Studio, by Right-Click the affected Publications, View Log Reader Agent Status, Click Stop;
3. Clear the pending replication commands in transaction log:
EXEC DB..sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1;
4. Drop all publications in DB:
EXEC DB..sp_droppublication 'all';
5. Disable Replication Publisher role of DB:
EXEC sp_replicationdboption 'DB', 'publish', 'false';
6. Check DB transaction log truncation NOT blocked by 'REPLICATION' again:
SELECT name AS DB, log_reuse_wait_desc FROM master.sys.databases;
7. Backup Transaction Log again to free space;
8. DBCC SHRINKFILE to shrink transaction log file (if required).

2015-03-25

You reduced a column size, but the table gets bigger, why?

Choosing correct data types could decrease the row size, and also improve performance. Sometime you may give the column too much space during table creation, and after the database already production running for a while, you would like to reduce the defined size for a column that well fit your system requirement. For example, you defined a column as fixed length nchar(50), but later you identify that using variable length nvarchar(10) is good enough.

For example, you created the table like this:

CREATE TABLE TestTbl (
id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
col nchar(50) NULL
)
GO

The initial size, surely zero.

In order to simulate the production usage of this table, let's populate it with 100000 rows.

DECLARE @i int = 1
WHILE @i <= 100000
BEGIN
INSERT TestTbl (col) VALUES ('XXX')
SET @i += 1
END
GO

The table is populated with data.

As you identify that this column can use smaller defined size, you alter the column definition as below:

ALTER TABLE TestTbl ALTER COLUMN col nvarchar(10)

Now the column defined size is reduced, from fixed length nchar(50) to variable length nvarchar(10). But when you check the table size, surprisingly, it becomes bigger than before.

Why? Unfortunately one thing is not commonly known – alteration of the table never decreases the row size. When you drop the column, SQL Server removes column from the metadata but does not reclaim/rebuild the row. When you change column type from int to tinyint, for example, actual storage size remains intact – SQL Server just checks new value on insert/update stages. When you increase the size of the field (for example change int to bigint), SQL Server creates another bigint column and keep old int column space intact.
So how to fix it? Well, you need to rebuild clustered index. SQL Server will reclaim all space and rebuild the rows when you do that. By the way, clustered index rebuild is time consuming operation which locks the table. You can only do that in your system maintenance window.

Let's try to rebuild the clustered index:

ALTER INDEX [PK__TestTbl__3213E83F6E978ECC] ON TestTbl REBUILD

Now the table size is reduced finally!

The truth is, you should always carefully choose the type and length of every table column during the design phase.

2015-03-23

SQL Server 32-bit or 64-bit version?

You can use two different commands to check your SQL Server is 32-bit or 64-bit.

@@VERSION
It returns system and build information for the current installation of SQL Server.

SELECT @@VERSION

Sample result:
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

In the first line of the result, X86 = 32-bit, and x64 = 64-bit. Same is true for operating system.

SERVERPROPERTY('Edition')
The SERVERPROPERTY system function returns property information about the server instance. The 'Edition' property is the product edition of the instance of SQL Server. 64-bit versions of the Database Engine append (64-bit) to the version.

SELECT SERVERPROPERTY('Edition')

Sample result:
Express Edition (64-bit)

Hope this helps.

2015-03-18

Repairing a Corrupted Database

One of the most common reasons behind database corruption is collision with any third-party software. Virus attack or bug infection can also corrupt the files. A hardware fault in your system or a crash in your hard disk drive (HDD) may cause the database files to become corrupt. Or, if the database files are being stored in a compressed volume or folder, this may cause corruption in the database files too. So it should be avoided to store SQL Server database files in compressed volumes or folders.

Consider this scenario:
You have been working in a SQL Server database from last few days. One day you find that the database status is tagged as suspect, which means the database file is corrupted. Or, you are having problem while connecting to the database. So how to fix it?
According to the Microsoft KB "How to troubleshoot database consistency errors reported by DBCC CHECKB", the best solution to fix database consistency errors is to restore from a known good backup. However, if you cannot restore from a backup, then you can try DBCC CHECKDB to repair the error. Below are the steps:

1. DBCC CHECKDB(DBName) WITH NO_INFOMSGS
The DBName is a name of your corrupted database. If this is completed without any errors then the database does not need to be repaired.

2. ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
The database must be set in single user mode before repairing it.

3. DBCC CHECKDB(DBName, REPAIR_REBUILD)
There are number of repair model, you should first try REPAIR_REBUILD, which is no data loss. If OK, go to step 5.e (multi-user mode) If not, go to next step.

4. DBCC CHECKDB(DBName, REPAIR_ALLOW_DATA_LOSS)
This may cause data loss. If ok go to step 5.e (multi-user mode) If not, go to next step.

5.
a. ALTER DATABASE DBName SET EMERGENCY
b. ALTER DATABASE DBName SET SINGLE_USER
c. DBCC CHECKDB (DBName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS
d. ALTER DATABASE DBName SET ONLINE
e. ALTER DATABASE DBName SET MULTI_USER

However, if you tried all the steps above but still unable to repair the database corruption, you can try a more powerful third-party SQL database recovery software - Stellar Phoenix SQL Database Repair

網頁

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

2015-03-27

Transaction Log Disk Full due to Transactional Replication Not CatchUp

2015-03-25

You reduced a column size, but the table gets bigger, why?

2015-03-23

SQL Server 32-bit or 64-bit version?

2015-03-18

Repairing a Corrupted Database