Kevin3NF

In my last post I hoped to convince you to pay attention to all of the various “Login Failed for user…” messages that you see in your SQL Server ERRORLOGS.   ALL of them.

Yes, some you can ignore based on the environment or the person.   Jim the web guy on a Dev box is just not that much of a security threat (unless you let him touch Prod, but that’s a different post).

Some of you have one or two servers, and reviewing ERRORLOGs is no big deal to do manually.  More of you have tens and tens of them.   Some of you have thousands (I’m looking at you in Managed Hosting environments such as Verizon, Rackspace, etc. where customers pay you to do this).

By now, hopefully you are aware that you can issue queries against all servers or groups of servers at once.   If not, a very quick how-to in SSMS:

1. View-registered servers (Ctrl-ALT-G)
2. Expand Database Engine
3. Right-Click Local Server Groups
4. Set up groups as you see fit (mine are Prod and non-prod)
5. Register servers in the appropriate group

To query a group, right-click the group (or Local Server Groups for all of them), select New Query and off you go.  The results will append the server name as the first column.

Note that my servers are listed in order, but the results are not.   Results come in the order SQL Server was able to connect and receive the results back:

Side note…running Select @@version is a great way to ensure all of the SQL Servers are up and running, especially after a Patch weekend, or even just first thing in the morning.

Now for the stuff you actually wanted to read/learn…how to read the ERRORLOGS all at once:

We are going to dump the results of sys.xp_readerrorlog into a temp table, and then query just like any other table:

Create Table #Errorlog
(Logdate datetime,
 ProcessInfo varchar(50),
 LogText varchar(5000))

–Dump all the things into the table
insert into #Errorlog
EXEC sys.xp_readerrorlog
 0 — Current ERRORLOG
,1 — SQL ERRORLOG (not Agent)

–Query just like you would anything else:
Select *
from #Errorlog
Where 1=1
and (LogText like ‘%Error%’
or LogText like ‘%Fail%’)
And Logdate > getdate() -3

–Clean up your mess, you weren’t raised in a barn!
Drop Table #Errorlog

If I’m doing this on a Monday, I set the date to look back to over the weekend…otherwise 1 or 2 days.  But whatever works best for you.  There is nothing magic in here.  For more details on xp_readerrorlog, click the link.  No point in me re-writing a perfectly good explanation.

Hopefully this will help you pay more attention to what’s going on in your ERRORLOGs, whether for Login Failed, or even just to find all backups for a certain DB.  Just change the Where clause to fit your needs.

If I saved you any time at all, I’ve done my job.  Let me know if you actually use this!

Fries are awesome, but I’m trying to ShrinkFile my gut…

Kevin3NF

Sometimes its good to sit back, listen, nod and hear what is being said before speaking.   Actually, that is almost always the best idea.

Refreshing the Dev environment

I started a new contract recently.  This is a global company, but I am on a relatively small product team within it.  10 developers, 3 admins (SQL, O/S, Storage), 2 Business Analysts.

The company has a formal Change Management process, which is great.  However, this team manages and operates outside of that structure…not so good.   Devs have sysadmin to production. For now.

I and the other DBAs are working on all of the things you would expect to be missing from this sort of environment…consistent backup strategy, documentation, proper security, etc.

As with most places our Developers have a Dev copy of the database and are told not to touch the Pre-Prod version.  But, since we are test loading data there, Dev is way out of date.  Also, there are objects in Dev not moved to Pre-Prod yet.  So, they regularly point their queries, procedures and even the website to the PP database.  And then forget what they are connected to after lunch.

This makes for interesting hallway conversations!

The solution of course is to refresh the Dev db from Pre-prod, without losing any Dev code or users.

DBAs that have been around for awhile know where I am going with this…

Basic steps:

1. Backup the Production (or Pre-prod in my case) database.   Or pull from your overnight backup.   You DO have an overnight backup, right?   RIGHT?!?!?   😉

2. Restore that backup to the Dev server as MyDB_Refresh:

— Get the logical and physical filenames

Change the database names if you need to do the same on MyDB_Refresh.  You can use the same offline/online code when you are doing an emergency restore in prod and the app servers won’t let go of their connections.

Compare a select of tables between Prod and Dev to make sure the data is refreshed, and double check using schema compare that Dev is different than Prod (likely, since that is what Devs do…).

This is a basic process, best done in a maintenance window, early in the morning or late at night.  And you can automate most of this.  I have not tried to automate schema compares other than using SQLPackage.exe in the SSDTools to “upgrade” a target data-tier application, or create a Drift report.   But that is way beyond the scope of this post.  Maybe another time 🙂

Please feel free to post up your processes, or enhancements to this one that some new DBA or Windows admin may be able to use 3 years from now when he finds this post on Google.

Have a fantastic day!

Kevin3NF

Experienced DBAs and Developers…might as well pass on by this one 🙂

Today, I want to turn SQL into English for (mostly) Developers and (some) DBAs that are asked to develop or tune queries.

No matter where I go, I always run into a Dev that loves to use NOT IN.   I get it.  Its simple, it makes sense, and it works.  You will get back the correct rows.

BUT!!!   Lets talk through a simple example as to why a JOIN is going to be far more efficient and make your DBAs and managers very happy….

Scenario:

You are a book lover, and want something new to read.  So you go to the local Narnes and Boble.   When you get there, you find something that sounds familiar, but you can’t remember if it is in your collection at home or not.

The Setup:

I created a table (AllBooks) with a thousand rows and another table (MyBooks) with 49.  Both have a BookID (pretend its the ISBN number so the analogy works).

My two choices for determining what books I do not own…look for books NOT IN my current collection, or JOIN the two lists (yeah…the analogy breaks down a bit here, but stick with me).

The Queries:

Basic stuff, right?   Both will return 951 records (books) that I do not own.  And, very quickly…because the tables are tiny.   Sub-1 second is fast.

The issue here is HOW the rows are compared.

English version now, techy stuff later:

In the first query, this is equivalent to you standing at the bookstore and calling home to have someone check to see if the book in your hand is already in your collection.  EVERY time.  One by one.

In the second, you got really smart and brought a list with you, which you are comparing to the books on the shelf at the store.   You’ve got both “lists” in one place, so it is far more efficient.

Techy stuff/Execution Plans:

Disclaimer…I’m not trying to teach you to read execution plans or even know the deeper meaning of the various operators…just going to show the plans behind the queries above.  If you DO want to know more than you can ever use, go download Grant Fritchey‘s e-book.   And Follow/Stalk Grant on Twitter.  He loves that.

The first query uses Nested Loop operators to work through the two lists:
“For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows”

The second uses a Hash:
“Use each row from the top input (MyBooks) to build a hash table, and each row from the bottom input (AllBooks) to probe into the hash table outputting all matching rows”

These were run at the same time.  85% of the time spent on the NOT IN, compared to 15% on the JOIN.   This was for a very small sample of data…1000 rows and 49 rows.   Take this up tens of thousands, or millions and you’ll start to see significant delays in getting results using NOT IN.

Short story long…if you are writing or tuning queries, this is an easy win.

Go get some fries.  And some books….49 is a pitifully small number for someone of your intelligence and ability 🙂

Kevin3NF