T-SQL Tuesday #45 Follow the Yellow Brick Road

TSQL2sDay150x150

We have the distinct pleasure once again to have a party full of SQL enthusiasts.  The best part is that few if any of us are even in the same room or building for this party.

The party is known as TSQL Tuesday and this month is hosted by Mickey Stuewe (Blog | Twitter).  This month Mickey has come up with the notion that for some reason or another you may want to have something audited, tracked, traced or otherwise followed.  To quote Mickey from the invitation (which you can read here): “So, it is time to follow Dorothy and Toto down the yellow brick road and to share your experience with auditing data.”

Mickey did provide some viable options for auditing.  And I didn’t see any restrictions on what the topic of auditing couldn’t be.  So I have a slightly different spin on the topic than what appeared to be the direction that Mickey was headed with her examples and description in the invite.

That said, I will be discussing a topic along the vein of “finding out who did something stupid last week.”  And the TSQL Tuesday topic once again works out well this month for me because I have a back log of blog topics for this subject that I have been meaning to write.  So let’s get at least one of them knocked out of the way with this entry into the blog party of the month.

OzcurtBehold the Great and Powerful Audit

Have you ever come into work and been blasted first thing with “everything is slow!”

Then you start to dig and see your email reports and you begin to wonder why you have 50% of your clustered indexes jacked up to 99% fragmentation?  You know that sort of thing never happens suddenly in your environment because you have your finger on the pulse of all things DB related – or so you thought.

You check your index defrag log and see that it ran perfectly fine last night and you even see the report on fragmentation from after the defrag process was completed.  You begin to really scratch your head.  You know that you have no process that does any bulk loads or ETL batches after the index defrag process.  Nothing is really making any sense.  Then a lightbulb goes off and you check your report on database sizes.  You see that the database is now several hundred GB smaller and there is no free space left in the data files.  Free space that you had left in the data file intentionally.

300px-Steam_eruptionNow you are steamed and about to blow your top like this geyser.  Who has done something so stupid as to mess around with your data files?  We Senior DBA’s are somewhat territorial with the databases we support and we don’t like people making changes they haven’t discussed with us.  So it is perfectly understandable if you are a bit steamed by an event such as this.

The question now is: What are you going to do about it?  How are you going to find who jacked up your data files?

The answer is in the default trace.

[codesyntax lang="tsql"]

[/codesyntax]

The optimal solution may not be to have to rely on the default trace to capture an event that occurred in the past, but rather to proactively monitor it through other means (perhaps an extended event for example).  But in the event you need to capture the information because you were caught off-guard, you may be able to trap the information from the default trace.

When I run the above query, I get the following results (yes I intentionally shrunk my msdb database explicitly for this example).

results

Now when you have a sysadmin or a junior dba or that manager with too many permissions doing things they shouldn’t be doing, you have some evidence to show them.  You also can go to the person that “did something really stupid” and offer them a chance to learn why it might have been a bad thing – but do it nicely.  You don’t want to be the one that looks like a jerk.

2 Comments - Leave a comment
  1. […] T-SQL Tuesday #45 Follow the Yellow Brick Road […]

  2. […] T-SQL Tuesday #45 Follow the Yellow Brick Road […]

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">






Calendar
August 2013
M T W T F S S
« Jun   Sep »
 1234
567891011
12131415161718
19202122232425
262728293031  
Content
SQLHelp

SQLHelp

  • @billinkc: @Clem1029 #sqlhelp in my quick testing, before installing SSDT-BI, I had the AS folder in 12, not in 11. Installed and it showed up.
  • @billinkc: @Clem1029 #sqlhelp Does C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\ProjectTemplates\Analysis Services exist or 11.0
  • @tjaybelt: #sqlhelp any problems with installing SQL Server 2008R2 Web edition on Windows Server 2012?
  • @AskSQL: Anyone using Win2012R2 getting the SCSI VPD error when validating for WSFC? http://t.co/D8rLebe8dF Says I "don't have inquiry data" #SQLHELP
  • @Clem1029: Is there a registry key or file that will indicate that SSDT BI is installed on a machine? #sqlhelp
  • @sqlstudent144: Can DTSRun be installed separately from a SQL Server instance? & on a win 2008 srvr? #sqlhelp
  • @toosuto: What's the impact of restarting the DTC? #sqlhelp
  • @SirSQL: @_dave705 sys.xp_sqlagent_enum_jobs to check running status #sqlhelp or enum method dir sqlserver:\sql\localhost\default\jobserver\jobs | gm
  • @SirSQL: @_dave705 you would have to call an sp_start_job and then poll the status of the job until it's complete and handle that in PS #sqlhelp
  • @_dave705: @SqlBrit Since the sql agent job is asynchronous it does not return any status even if the job fails unless you have idea #sqlhelp

Welcome , today is Tuesday, September 30, 2014