Wednesday, October 31, 2018

Who dropped that table?

Who dropped that table?  Who altered that view?  Who added that column.... and so forth.  You'd like to think you'll always know what's going on in your databases, but unless NOBODY touches it but you, that's just not possible.  I can tell you war stories that will floor you!  Some other time with a glass of red...  Until then, this is a quick query to tell you who dropped or altered any object within any database on your server.   

       -- declarations
              @current VARCHAR(255),
              @start VARCHAR(255),
              @index INT;

       -- find your trace path
       SELECT @current = path FROM sys.traces WHERE is_default = 1;

       SET @current = REVERSE(@current)
       SELECT @index = PATINDEX('%\%', @current)
       SET @current = REVERSE(@current)
       SET @start = LEFT(@current, LEN(@current) - @index) + '\log.trc';

       -- query on the eventclasses for delete and alter
       SELECT CASE EventClass WHEN 164 THEN 'Object:Altered'
              WHEN 47 THEN 'Object:Deleted' END [Action],
       FROM::fn_trace_gettable(@start, DEFAULT)
       WHERE EventClass IN (164,47)
       AND EventSubclass = 0
       AND DatabaseID <> 2

 Your end result will vary.. but this is my output from one of my instances:

Now you go talk to Joe Shmo or Ms. Lenovo and figure out why they're editing/dropping objects in your database.  😏

See these for details on fn_trace_gettable and the object events I used in my query:

Tuesday, October 30, 2018

How long will the CheckDB run?

For the most part, how long it will run depends on how large the database is, and how many objects are being checked.  More objects = more time.  There are also certain arguments that you can use to make it run faster and slower, like TABLOCK and PHYSICAL_ONLY respectively, but that's for another discussion.  In this post I just want to show you how to determine how long a checkdb is going to run before it is complete.

I ran the integrity check for the first time on a 1.7TB database over the weekend, and it ran for 23:12:44.  Pretty much a full day.  I knew it would be timely, but I wasn't expecting 24hrs.  I ran this statement about 16 hours into it, to find the estimated completion time:

          DATEADD(ms,estimated_completion_time,GETDATE()) [EstimatedEndTime],
          estimated_completion_time/(1000*60) [EstimatedCompletionTimeInMinutes],
          session_id = 65 --< this is the spid for your actively running checkdb

And this was my output:


Not as clear as I'd like, but it told me I was about 69% complete and gave me the estimated end time -- both in minutes, as well as the actual estimated time it will complete.  In this case, it estimated 7:46AM, but it actually completed at about 7:02AM.  Because it was so long running, this information was very helpful.  I was able to reach out and let a few people know when it would be complete, rather than just saying 'I don't know'.

NOTE:  This doesn't just apply to the CHECKDB.  As per this reference,  the sys.dm_exec_requests DMV returns details about any request actively running on the server.  Can be quite helpful!

Thursday, October 25, 2018

How to query for locking and blocking details?

I've got a new customer who is having a lot of blocking problems on his database, but he didn't have any real details about where the blocking was occurring.  This query is something that I use to return useful aggregate details on where the blocking has occurred since the last service restart.

       db_name(database_id) [Database],
       object_name(object_id) [Table],
       row_lock_count + page_lock_count [#Locks],
       row_lock_wait_count + page_lock_wait_count [#Blocks],
       row_lock_wait_in_ms + page_lock_wait_in_ms [BlockWaitTime],
       index_id [IndexID]
       row_lock_wait_count + page_lock_wait_count > 0
       AND object_name(object_id) IS NOT NULL
       BlockWaitTime DESC

Your output will be something like this:

You can see it gives you a quick visual on where the blocking is happening.  Pretty helpful, but don't take it verbatim.  The lock and block counts are accurate, but there are some known inaccuracies with the block wait time (see below link).  In my opinion, the query is still accurate enough to give you an idea of where your blocking is occurring, and let you know where to look further.  Hope you find it useful!

Wednesday, October 24, 2018

How to find out who installed SQL Server?

Odd question, but it came up for me today.  Today a customer told me that I installed both SQL Server 2016 and 2017 on their instance, and they wanted me to remove v2017.  um... No.  Why would I do that?  So I took a look, and there were two full installations side by side -- but it definitely was not done by me.  I cleaned it up, and also performed the steps below, so that I could let them know this was not done by me.

First, go to this location to find your setup bootstrap logs:

At that location, you should search for this file:   sql_engine_core_inst_Cpu64.log

Open up that file and search for ' logonuser '... and there you go!  Of course, this is my installation log and not my customer's, but you get the point.  The LogonUser value corresponds to whomever was logged on while the installation is performed.

Happy SQL Servering!   ☺☺☺

Thursday, October 18, 2018

When was the Windows server last booted?

Many times I must look into sys.databases to determine the last SQL Server service restart.  The fastest way to do that is just look at your tempdb create date:

SELECT * FROM sys.databases


SELECT create_date FROM sys.databases WHERE [name] = 'tempdb'

But, today I need to know when the Windows server was rebooted.  Very easy, and I wanted to share it with you.  Open up a cmd prompt and type 'net statistics server'.  

Voila!  See the 'Statistics since 10/18/2018 1:03:54 PM'  -- that is your last server boot time.

Happy SQL Servering!