[Presenting] FWSSUG - my Slide Deck on EventLog_Tracking - using WMI to collect event logs (as well as other things)

Here's my latest presentation.  Pretty good reception overall.


Here's the basic premise: there's a lot that goes on our SQL boxes that we normally don't get involved in.  But SQL Server isn't an island - it's affected by the ebbs and flows of other processes.  I had a problem where I needed to collect the Windows Event Logs from a SQL server, and wound up going through a rabbit hole of interesting stuff you can do with WMI.  Here are the lessons I've learned.  All code already available (search for eventlog_tracking on this blog), and it's currently running on 60+ servers, collecting data every 15 minutes. It runs in under 2, and I think I could get it down under 1 if I had the time.

This is a predominantly slide presentation - the only demos I did were to run some of the command in the slide deck. 

[Presenting] 24 Hours of PASS - Growing our Community. Next Week!

[Tricks and Tips] a second way to indirectly view rows in a table

So in my last post I came up with a way to use DBCC IND and DBCC PAGE to view pages indirectly, so the stats don't get updated. 


Here's a simpler way, albeit with more impact.

Make an index, then look at the histogram.

For instance, I have an auditing table.  I want to see the last time rows were inserted into it.

In this case, we do two things:

CREATE INDEX NCIDX_TABLENAME_FIELDNAME ON TABLENAME (FIELDNAME)

This does NOT affect the "user%" fields in the DMV (SYS.DM_DB_INDEX_USAGE_STATS).
(as you can see, still unseen)


Next step: view the histogram.

DBCC SHOW_STATISTICS (tablename, NCIDX_TABLENAME_FIELDNAME) with HISTOGRAM



So what's that show us?  That from 4/27 to 5/22 there were 4 rows, of which 1 was at 4/27 8:40.  It also shows there was one on 5/22 at 8:24.  And our indexes are still clean - so when we look again at historical usage of the table, it comes back clean.

[Tricks and Tips] Using DBCC IND and DBCC PAGE to view a table without affecting DM_DB_INDEX_USAGE_STATS

First off: don't use this in production, or at least understand what you're/I'm doing.  This uses two undocumented DBCC commands.  There IS a replacement for DBCC IND in 2012+, but I needed this for older servers (and besdies, DBCC PAGE would still be unsupported).

Problem: I'm consolidating servers. As part of that, I need to see what tables the database has that are used. I use a script elsewhere on the blog for that, one that uses a DMV called SYS.DM_DB_INDEX_USAGE_STATS. It, unsurprisingly, tells you the last time an index was used - but can also be used to tell when a query last touched a table.  So if it's had inserts for 2 years but no selects, odds are you need to reconsider whether you need it.  (An aside - you might ought to consider rebooting your machines more often than once every 2 years)

However, I don't want to do a SELECT from the table, since that would affect the DMV.  Technically I have a way around that - I use my EPR (again, another post) to save out the stats twice a day.  However, when you go back and look at 3 months of history, there's always the thought "was that me, or some process I don't know about that only runs once a quarter?".  Hence, this.

What we do is use two massively unsupported DBCC commands to get the list of pages allocated to a table, then use DBCC PAGE to show the contents of them.  Once that's done, we hopefully pull the data through a pivot into a viewable table.  By default it assumes there's a clustered index, but that's pretty optional - I did it that way due to an implied speed boost.  (After all, it has to pull back ALL the pages associated with a table.)

Nifty bit: you don't need to use TRACEON (3604) with PAGE if you use WITH TABLERESULTS. Didn't know that!

My "project nickname" for this is Medusa's Mirror, since you're not looking at the table directly.  Technically it should be Perseus' Shield, but I didn't think of it when I created the Gist.  Besides - it's alliterative.

Did some lightweight testing on some tables, though I don't have offrow data.  Use at your own risk.

[Errors] Fixing "Buffer provided to read column value is too small" on a 2008 box.

Recently had a problem with my Eventlog Capture code.  An odd error mentioned in SQL 2005 - but this is on a 2008 box.


Error: 682, Severity: 22, State: 148. 2015-06-01 02:10:10.14 spid71 Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.

WHAT?  OMGCORRupti....oh, wait. DBCC comes back clean.  Go through code. Narrow it down to the insert.  Which actually inserts into a view.  And the definition of the view had changed in two ways. 

Why'd it kick in today and not before?  New month - so the monthly table was now getting inserts through the view, causing failures.  

Not sure which change caused the problem, but changing the fields in each table to match (I'd made "strings" and "Message" longer), and changing the view from:


select a,b,c,d,e from table201504
union all
select a,b,c,d,e from table201505
union all
select * from table201506

to

select a,b,c,d,e from table201504
union all
select a,b,c,d,e from table201505
union all
select a,b,c,d,e from table201506


fixed it. *Phew*