Wednesday, September 26, 2012

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.

Tuesday, September 25, 2012

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.

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)

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.

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.

CREATETABLE [dbo].[dm_os_wait_stats_info](

[server_name] [sysname] NOTNULL,

[insert_datetime] [datetime] NOTNULL,

[WaitType] [varchar](120)NOTNULL,

[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)NOTNULL,

CONSTRAINT
[PK_dm_os_wait_stats_info] PRIMARYKEYCLUSTERED

(

[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.

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)

Wednesday, September 5, 2012

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\\

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.