[Replication] IDENTITY, failed inserts due to primary key, and no-longer-replicated tables

We recently had a problem where we had to break replication to several table, but keep data flowing to them from another data source.  So we built a process that inserted new records into the no-longer-replicated table

However, our inserts failed, telling us there was a problem with the Primary Key. "Violation of the primary key" because it didn't accept NULL.  Which was odd, because when you look at it the tables clearly have an identity column.  Eventually we (okay, it was JS) thought to check the identity column:

DBCC CHECKIDENT (blah, NORESEED)

Which returned NULL(!).  Simply doing

DBCC CHECKIDENT (blah, NORESEED)

Pushed the values back to a real number, at which point the inserts were able to occur.

Note that you cannot RESEED an identity back to NULL.  I have no doubt there's a way, because SQL does it for replication, but if you want to go back to replicating that table, you'll probably need to snapshot it.

sp_server_diagnostics - dynamic EVENTS parser

(deprecated - see newer post for a much faster parser with more info)

Here's part 2 - parsing the error logs out of sp_server_diagnostics.  Copy and paste the entire article; code overflows the column but is still copyable.

First, get the data. I use a global temp table for it, and I don't drop it until I'm done troubleshooting.
sp_server_diagnostics should run continuously and you should be able to pull it that way, but I can't remember from where.  Thus, I just run it for 5 seconds to populate the table.

Feel free to share, feel free to send me changes and enhancements.  FYI - if you have a lot of errors, the XML parsing is SLOW if you have a lot of errors.  This runs in under 20 seconds on a normal box, but on one of my trouble boxes it can take 2.5-5 minutes.  I have no doubt someone GOOD at XQuery can make this go considerably faster - and I'd love to know how.  If there are no errors in the results, nothing will be returned.

I hope this helps you troubleshoot SQL Server 2012.

--takes 5 seconds to run, only valid on 2012 servers
if object_id('tempdb..##SpServerDiagnosticsResult') is null 
BEGIN
CREATE TABLE ##SpServerDiagnosticsResult 
(
  rowId INT IDENTITY PRIMARY KEY,
  create_time DateTime,
  component_type varchar(128),
  component_name varchar(128),
  state int,
  state_desc varchar(20),
  data varchar(max)
)

INSERT INTO ##SpServerDiagnosticsResult
EXEC sys.sp_server_diagnostics
END

--SP_SERVER_DIAGNOSTICS Dynamic Event Parser V 1.11
--You may use this at will, you may share it provided this header remains.  
-- Written 2012 Michael Bourgon
--Commercial use prohibited without permission - if you charge money for using this, I want a cut.
-- If you're just running this as your job as a DBA, enjoy.
-- Due to the XML parsing this takes about 4 minutes to run, of which over half is the parsing to generate the selects
-- Please feel free to share, and feel free to send corrections or enhancements - thebakingdba.blogspot.com
-- Thanks to Marc_S on Stackoverflow for the help on parsing XML!
SET NOCOUNT ON 
DECLARE @events TABLE (id INT IDENTITY PRIMARY KEY, EventName VARCHAR(100))
DECLARE @sql NVARCHAR(max)
DECLARE @min int, @max INT, @eventtype VARCHAR(100)

--get a list of event types, then walk through each separately; columns won't match 
INSERT INTO @events (EventName)
select 
    DISTINCT EventName = Evt.value('(@name)[1]', 'varchar(100)')
FROM 
(
SELECT CAST(data AS XML) AS xml_data 
FROM ##SpServerDiagnosticsResult 
WHERE component_name = 'events'
)getlistofsubevents
CROSS APPLY xml_data.nodes('/events/session/RingBufferTarget/event') Tbl(Evt)

--Loop - for each event type, generate a SQL script for those columns
SELECT @min = MIN(id), @max = MAX(id) FROM @events
WHILE @min <= @max
BEGIN
SET @sql = NULL 
SELECT @eventtype = EventName FROM @events WHERE id = @min

--header for the query
SELECT @sql = N'select 
EventName = Evt.value(''(@name)[1]'', ''varchar(100)'')
,OriginalTime = Evt.value(''(@timestamp)[1]'', ''varchar(100)'')' + CHAR(10) + CHAR(9)

--meat of the query - get the data for each unique TYPE, if a normal value.
-- if the subdatatype is not a "normal" type, we assume we want a name/text/value
-- we use varchar(100) for that, rather than a separate CASE, for speed
-- SO, don't just add varchar(100) to this CASE without understanding why.
SELECT @sql = @sql + 
N' ,' + SubEventName + 
+ CASE SubDataType
when N'int' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')[1]'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
WHEN N'bigint' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value'+ ')[1]'', ''' + SubDataType + ''')'  + CHAR(10) + CHAR(9)
WHEN N'unicode_string' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')[1]'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
WHEN N'uniqueidentifier' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')[1]'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
WHEN N'nvarchar(100)' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')[1]'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
WHEN N'bit' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')[1]'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
ELSE N' = isnull(Evt.value(''(data[@name="' + SubEventName + '"]/type/@name)[1]'', ''varchar(100)''),'''') + '' : ''
    + isnull(Evt.value(''(data[@name="' + SubEventName + '"]/text)[1]'', ''varchar(100)''),'''') + '' : ''
    + isnull(Evt.value(''(data[@name="' + SubEventName + '"]/value)[1]'', ''varchar(100)''),'''')' + CHAR(10) + CHAR(9)
end
FROM 
--break out each event type for the larger query; could just use nvarchar/varchar, but returning the right data type is cleaner
-- (and we need to know when it's a non-standard type for the name/text/value)
(select distinct
--EventName = Evt.value('(../@name)[1]', 'nvarchar(100)'), --disabled since it's not actually used
SubEventName = Evt.value('(@name)[1]', 'nvarchar(100)'),
SubDataType = CASE Evt.value('(type/@name)[1]', 'nvarchar(100)') 
WHEN 'int32' THEN N'int'
WHEN 'uint16' THEN N'int'
WHEN 'boolean' THEN N'bit'
WHEN 'unicode_string' THEN N'nvarchar(100)'
WHEN 'uint32' THEN N'bigint'
WHEN 'uint64' THEN N'nvarchar(100)'
WHEN 'guid' THEN N'uniqueidentifier'
WHEN 'ansi_string' THEN N'nvarchar(100)'
ELSE N'varchar(100)' END --if unknown, then probably name/text/value. 
FROM 
(
SELECT CAST(data AS XML) AS xml_data FROM ##SpServerDiagnosticsResult WHERE component_name = 'events'
)event_xml_record
CROSS APPLY xml_data.nodes('/events/session/RingBufferTarget/event/data') Tbl(Evt)
WHERE Evt.value('(../@name)[1]', 'varchar(100)') = @eventtype
)full_data_info

--and the footer for our query; might be able to do a dual CROSS APPLY, but this is more readable
SELECT @sql = @sql + N'
from (
SELECT CAST(data AS XML) AS xml_data FROM ##SpServerDiagnosticsResult WHERE component_name = ''events''
)spserverdiageventparser
CROSS APPLY xml_data.nodes(''/events/session/RingBufferTarget/event'') Tbl(Evt)
WHERE Evt.value(''(@name)[1]'', ''varchar(100)'') = ''' + @eventtype + ''''

EXEC sp_executesql @sql
-- PRINT @sql

SET @min = @min + 1
END

sp_server_diagnostics - parsing (part 1 - RESOURCE)

SQL Server 2012 offers a new way to view problems on your server.  It's light-weight, it's already running, and it captures all sorts of information.

It's called sp_server_diagnostics.

There's just one problem - the results are in XML and are hard to read.  We can fix that.  We have the Xquery.

Here's part 1, the results from the memory and waits.
Part 2 is the tricky part - events.  Forthcoming.


First, let's catch the data.

--takes 5 seconds to run, only valid on 2012 servers
if object_id('tempdb..##SpServerDiagnosticsResult') is null 
BEGIN
CREATE TABLE ##SpServerDiagnosticsResult 
(
 rowId INT IDENTITY PRIMARY KEY,
 create_time DateTime,
 component_type varchar(128),
 component_name varchar(128),
 state int,
 state_desc varchar(20),
 data varchar(max)
)

INSERT INTO ##SpServerDiagnosticsResult
EXEC sys.sp_server_diagnostics
END

--now to parse

--idea pilfered from Neil Hambly, written by M Bourgon

--Memory Info

SELECT a.b.value('@description','varchar(100)') AS descript, a.b.value('@value','bigint') AS val FROM 

(

SELECT CAST(data AS XML) AS xml_data FROM ##SpServerDiagnosticsResult

)aaa

CROSS APPLY xml_data.nodes('/resource/memoryReport/*') a(b)

--top 10 wait types

SELECT a.b.value('@waitType','varchar(100)') AS WaitType,

  a.b.value('@waits','bigint') AS WaitType_Waits,

  a.b.value('@averageWaitTime','bigint') AS WaitType_Avg,

  a.b.value('@maxWaitTime','bigint') AS WaitType_Max

FROM 

(

SELECT CAST(data AS XML) AS xml_data FROM ##SpServerDiagnosticsResult

)aaa

CROSS APPLY xml_data.nodes('/queryProcessing/topWaits/nonPreemptive/byCount/*') a(b)

[Powershell] Building a dm_os_wait_stats repository

(update 2013/06/13: I've superseded this with a new framework that will run almost any piece of code, powershell or sql, saving the results to a table, and running with multiple threads. Please give it a look!
http://thebakingdba.blogspot.com/2013/04/servers-extensible-powershell.html )

Inspired by Paul Randall's talk at PluralSight on Waits & Queues (highly recommended), I've built a repository of dm_os_wait_stats by cobbling together some other people's code.

Lots of code here, but for you it's a matter of copy & pasting 4 files, a table, and a job.  Call this my 1.0 release. For 1.0, I'm only doing one server at a time; yes, I plan on multithreading it, but it does 80 servers in under 3 minutes.  And yes, if it can't reach a server it will throw a message (that I don't currently capture), but it does continue.

What we're doing:

• grab a list of servers that you already have stored in a database somewhere
• for each server
• run Paul Randall's code that aggregates the overall wait stats
• save results to a central server (probably where you have your server list)

Powershell code pilfered from Chad Miller, SQL code from Paul Randall.  

http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx

and

http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx

First, grab the scripts for invoke-sqlcmd2 (http://gallery.technet.microsoft.com/ScriptCenter/en-us/7985b7ef-ed89-4dfd-b02a-433cc4e30894) and write-datatable (http://gallery.technet.microsoft.com/ScriptCenter/en-us/2fdeaf8d-b164-411c-9483-99413d6053ae) and save to files named invoke-sqlcmd2.ps1 and write-datatable.ps1, respectively.  Everything goes in  c:\sql_scripts.

Next, the actual query from Paul Randall; save this as get_dm_os_wait_stats.ps1. This gets useful info from the DMV.

WITH Waits AS

    (SELECT

        wait_type,

        wait_time_ms / 1000.0 AS WaitS,

        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,

        signal_wait_time_ms / 1000.0 AS SignalS,

        waiting_tasks_count AS WaitCount,

        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,

        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum

    FROM sys.dm_os_wait_stats

    WHERE wait_type NOT IN (

        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',

        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',

        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',

        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',

        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',

        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',

        'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',

        'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',

        'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK'

--mdb 2012/09/12 adding 2012-specific waits to ignore

,'DIRTY_PAGE_POLL','HADR_FILESTREAM_IOMGR_IOCOMPLETION'

)

    )

SELECT

    @@servername as server_name, getdate() as insert_datetime, W1.wait_type AS WaitType, 

    CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,

    CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,

    CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,

    W1.WaitCount AS WaitCount,

    CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,

    CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,

    CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,

    CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S

FROM Waits AS W1

    INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum

GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage

HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold

Now, create the table for the results.  The identity column is at the end so that write-datatable doesn't balk.  Technically the ID is not needed, but I can more easily see how the process is doing.

CREATE TABLE [dbo].[dm_os_wait_stats_info](

      [server_name] [sysname] NOT NULL,

      [insert_datetime] [datetime] NOT NULL,

      [WaitType] [varchar](120) NOT NULL,

      [Wait_S] [decimal](14, 2) NULL,

      [Resource_S] [decimal](14, 2) NULL,

      [Signal_S] [decimal](14, 2) NULL,

      [WaitCount] [bigint] NULL,

      [Percentage] [decimal](4, 2) NULL,

      [AvgWait_S] [decimal](14, 4) NULL,

      [AvgRes_S] [decimal](14, 4) NULL,

      [AvgSig_S] [decimal](14, 4) NULL,   

--ID at the end, otherwise the write-datatable chokes

      [id] [bigint] IDENTITY(1,1) NOT NULL,

 CONSTRAINT [PK_dm_os_wait_stats_info] PRIMARY KEY CLUSTERED

(

      [server_name],

      [insert_datetime],

      [WaitType]

)

)

Next, save the following code to a file named get_dm_os_wait_stats.ps1.  I put it in c:\sql_scripts.  The first two lines "dot source" the scripts so that their functions can be called.  The third is the actual heavy lifter.

. c:\sql_scripts\invoke-sqlcmd2.ps1
. c:\sql_scripts\write-datatable.ps1
invoke-sqlcmd2 -serverinstance "serverwithserverlist" -query "SELECT server_names FROM yourdatabase.dbo.yourserverlist WHERE active = 1" | foreach-object {$dt = invoke-sqlcmd2 -erroraction silentlycontinue -serverinstance $_.server -inputfile c:\sql_scripts\dm_os_wait_stats_agg.sql -As 'Datatable'; write-datatable -serverinstance "serverwithserverlist" -DATABASE "targetdb" -tablename "dm_os_wait_stats_info" -DATA $dt}

Finally, create the job.  Only needs one job step, set as Operating system (CmdExec).  Schedule that however often you want - I'd say either hourly or daily.  You'll want another job to delete old records (I'll leave that as an exercise for the reader)

powershell "& c:\sql_scripts\get_dm_os_wait_stats.ps1"

And that's pretty much it!  

[SSIS] fun with SSIS 2012 - config UNC paths in jobs

Ran into a problem with Jobs invoking SSIS packages, where you overwrite the Configuration in the job (job step->general->configuration, value).
For some reason, this type of UNC path doesn't work:
\\yourservername\your\directory\here\
but, this does:
\\yourservername\your\directory\here\\

[statistics] Find last update time for all statistics for a table

(tbd 2015/09/09 - added truncate table to the loop.  Doh!)

Statistics matter. So you want to find when they were last updated.  You usually see this one:


SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('pad_tx')

Which works - for all statistics tied to an index.  But SQL Server has more than just those: there are also the _WA_Sys stats, which begin with that phrase and are created by the engine.  (IIRC, you can also see DTA stats, built by the Database Tuning Advisor) 

In theory you shouldn't have many of them, because they're only generated if you're querying a field that isn't part of an index.  Theoretically you should clean them out (that may be another post), since a stat could be in there twice - once for an index and once because you queried the table (creating the stat) before you created the index.

This should list all of them.  

DECLARE @sql NVARCHAR (1000), @MIN INT, @MAX INT, @statname NVARCHAR(128)

declare @listofstats TABLE (id int identity, NAME sysname)

INSERT INTO @listofstats (name) 

SELECT name FROM sys.stats WHERE object_id = object_id('yourtablenamehere')

if object_id('tempdb..#stats_info') is not null

    DROP TABLE #stats_info

CREATE TABLE #stats_info (updated DATETIME, [Table cardinality] BIGINT, [snapshot ctr] BIGINT, steps INT, density DECIMAL(19,16), [rows above] INT, [rows below] INT, [squared variance error] DECIMAL (19,16), [inserts since last update] MONEY, [deletes since last update] MONEY, [leading column type] VARCHAR(50))

if object_id('tempdb..#stats_info2') is not null

    DROP TABLE #stats_info2

CREATE TABLE #stats_info2 (stat_name VARCHAR(128), updated DATETIME, [Table cardinality] BIGINT, [snapshot ctr] BIGINT, steps INT, density DECIMAL(19,16), [rows above] INT, [rows below] INT, [squared variance error] DECIMAL (19,16), [inserts since last update] MONEY, [deletes since last update] MONEY, [leading column type] VARCHAR(50))

SELECT @MIN = MIN(ID), @max = MAX(id) FROM @LISTOFSTATS

DBCC TRACEON (2388)

WHILE @min <= @max

begin

TRUNCATE TABLE #stats_info
 SELECT @sql = NULL, @statname = null

SELECT @statname = NAME FROM @listofstats WHERE id = @min

SELECT @sql = N'DBCC SHOW_STATISTICS (''yourdatabasenamehere..yourtablenamehere'','''+ @statname +''')'

INSERT INTO #stats_info 

EXEC master..sp_executesql @sql

INSERT INTO #stats_info2 SELECT @statname, * FROM #stats_info

SET @min = @min + 1

end

DBCC TRACEOFF (2388)

SELECT #stats_info2.stat_name, #stats_info2.updated FROM #stats_info2 INNER JOIN (SELECT stat_name, MAX(updated) AS max_date FROM #stats_info2 GROUP BY stat_name)a 

ON #stats_info2.stat_name = a.stat_name AND #stats_info2.[updated] = a.max_date

ORDER BY #stats_info2.stat_name

[Wat] DECLAREing inside a loop? Yes, yes you can.

Came across this code today.  Sticking it in my personal 'Wat?' file (https://www.destroyallsoftware.com/talks/wat/)

Lump this in with the "hey, did you know you can DECLARE and set to a value without being inside a SP"?

This code works on 2008 and higher.


DECLARE @i INT = 1

WHILE @i < 5
BEGIN
DECLARE @a INT = 4
PRINT @i
SET @i = @i+1
END