Reviewing SQL Server Error Log – A Better approach!

How many of you have a habit of reviewing Error Log(s) of the SQL Server(s) which you support on a daily basis(At least on regular basis, if not daily)? If you are not doing that, go ahead and start making it as a practice from “Right now”. You can thank me later for this suggestion 😀

If you’re already doing that,then you are awesome…! But, let me ask this. How many times you get annoyed reviewing your error log(s), especially if you are looking for a specific error. Well, you can apply filter or you can simply query your error log from T-SQL. But what if you are only looking for all the severe Errors, total error count, Errors by frequency all at one place? Yes, you can write your own query and use SSRS for generating a fancy report. But Wait……What if I say SSMS already has this cool report inbuilt for us?

In this blog post I will show you a better way to decipher your current Error Log for Errors by using that hidden report.
It’s called “Number Of Errors” Report and you can get to this report by right clicking on “Management” Node -> Reports -> Standard Reports->Number of Errors in your SSMS as shown below.a

How it looks? See below…

1
As you can see, at a glance I know I’ve got 6 Severe Errors(Sev 18 or above) and 5 Moderate errors in my current error log. See below screenshots for how neat the info is once I expand those nodes.

Severe Errors:
2

You can see I’ve got the same 824 Error repeated 6 times. ( Well, I tried attaching the same corrupted ldf file 6 times for this demo)

Moderate Errors:
3

All errors by Frequency:
4

Is in’t nice? Personally, I liked this report…and thought of sharing what I’ve discovered with you folks 😀
Please ignore if you are already aware of this nifty report, if not…well, you know it now!

SQL Server Discovery Report – Know What SQL components you’ve got Installed at a glance

Let’s assume you’ve got a new SQL Server under your support as a primary DBA and you’ve decided to investigate and document all the SQL Server components/features installed on this Server. What are your options? Will you go to Config manager and look at Services for Installed Services. Well, what about Client components? You can pull build levels/Version(SP/CU) easily for your database engine using T-SQL, what about other services? What about your client tool(s) version…Chances are they will be same as your DB Engine(but they could be different based on how and who patched the SQL Servers)? What if you’ve got multiple Instances of SQL Server with different versions…?

Well, we’ve something called “Installed SQL Server features Discovery Report” under Tools in your Installation Center which provides all this information in a single place 🙂 .  Go to the Installation Center from your start->All Programs->Microsoft SQL serverXXXX->Configuration tools->SQL Server Installation Center. Go to Tools and click on discovery report as shown below.

4

After few seconds, I got below report which gives me basic information on what all I’ve got Installed related to SQL Server on this Server with the build numbers, Edition, clustered/StandAlone etc all at single shot (You can see I’ve got 2 Named Instances, 2008 and 2012) which is pretty useful IMO.

5

Hope you guys find this nifty tool useful! Cheers…

SIMPLE Recovery Model Database waiting on LOG BACKUP!!!!

Recently I’ve encountered a strange issue with one of our databases. This is a SQL Server 2012 Instance(SP1). This is our staging environment and was hosting many databases(Few in SIMPLE and few in FULL Recovery Model).

Note: The Recovery Model for “MODEL” database on this Instance was “SIMPLE”. We kept this way, as we don’t care about Log Backups/Point In time recovery for our Stage Databases.

Issue: Developer complained about error he got with T-Log ran out of space. Well, initially i thought, some one did refreshed this database recently from Production and forgot to chage the database rec model from FULL to SIMPLE. I looked at rec model, It was set to SIMPLE and was never refreshed. Well, now i was almost sure, this is becuase of some active transaction sitting in the log and not letting SQL Server to truncate the log with CHECKPOINTs…

You know what, I was wrong again! When I queried sys.databases for Log_reuse_wait_Desc, I found very interesting and strange behaviour of SQL Server. It was waiting for Log backup to be taken on the database(But, this database is in SIMPLE Recovery Mode) See below for what Am talking about!

1

Cause: This is a BUG in SQL Server 2012 which got fixed in SP1 CU4.( http://support.microsoft.com/kb/2833645)

Bug Number:

1254695 2830400

(http://support.microsoft.com/kb/2830400/ )

FIX: Database does not follow simple recovery model behavior in SQL Server 2012 after you set the recovery model of the “model” database to “Simple”

Action:  Patch(SP1 CU4) the 2012 SQL Server ASAP. (Btw, CU5 for SP1 is out as well).

Quick Fix: I swapped database recovery model to FULL and took a Full backup and switched it back to SIMPLE recovery model and it was back to normal(Also, I changed the Recovery model for MODEL database to FULL on this Instance till we patched this Server).

Hope this helps if you encounter this strangeeeee Issue!

An Insight on Contained Databases – SQL Server 2012

It’s been a long time SQL Server 2012 got released and many organizations are already using 2012 for hosting their production databases. In this blog post let us see one of the striking feature of SQL 2012, “Contained Database” which microsoft advertised a lot during the initial phases of 2012/DENALI.

One of the major advantages of contained Database(s) is, getting rid of User-Login Mapping Issues/Orphaned Users Issues and all that mess. Think about copying a database/Refreshing Lower environments Scenario, where we have to deal with mapping users/fixing Orphaned Users blah blah…. With contained Databases, all we have to do is, copy the database and Voila done…. Basically, in a contained Database, the user credentails are created and stored within the database, hence no dependency on mapping SID’s and Server level Logins. let’s see it in action….

Before doing anything, the very first step you have to do is to enable “contained database authentication” at Instance level. You can do this using GUI(SSMS) or T-SQL as shown below.

Step 1: Enable contained database authentication at Instance Level

1

As you can see above, it is enabled on my Instance. If not, you can use sp_configure to turn it ON as shown below…

2

or using GUI(Go to SQL Instance Properties->Advanced)

3

Step 2: Create a contained Database

From GUI, Right click on databases and “NewDatabase…”and in options tab you have to make “Containment Type” as “Partial”, click Ok and boom….you have created a Contained Database 🙂  See below…I created a database called “Contained_Tst” for this demo purpose.

4

Step 3: Creating Users/Connecting to Contained Database

Once done creating Contained Database, you can create those special users(Without login dependencies) inside the DB. They can be either SQL Auth or Windows accounts. Navigate to Users under Security inside the database and select “new User…” as shown below.

5

Now, Key is to select the appropriate option(See below)

6

I’ve selected SQL User with password, created user called “Contained_User”

7

Now we are done with creating User! It’s that simple….Now let’s see how to connect to contained Database using that account.

Using SSMS:

If you connect normally, you will be getting the below shown error.

8

So, what are we missing here? The key is to select the actual contained database manually which the user has access to…Go to connection properties and type in the database name manually(Drop down will/might be empty) and click ok. It should work now without any issues.

9

Once connected, you can see only the contained_tst database in the object explorer as expected and suser_sname() giving some GUID instead of the User Name.(See below)

10

When you try to access a different database on the Instance, you will get the below security context error…

11

Connecting to contained Database from SQLCMD:

You will be getting the same error as we got in SSMS if you try to connect without providing the database name(See below)

12

As soon as I tried providing the Database with -d switch, it worked as expected(see below)

13

Can a contained Database have normal Users?

Yes, it can have a combination of both with out any issues. So, how do we identify which Database users are Database Scoped(Contained Users) and which users are Instance Scoped(Normal DB users as we used to have in the past)?

In SYS.DATABASE_PRINCIPALS, we have columns(authentication_type and authentication_type_desc) which shows the type of authentication for database user(s) as well….

For demo purposes, I’ve created 2 more normal users (tst1 and tst2) besides contained_user. Now let’s see the results of sys.database_principals, shown below

14

As you can see tst_1 and tst_2 are Instance scored Users.

Can we convert/Migrate Normal Users to Contained Users? The Answer is Yes, by using “sp_migrate_user_to_contained“!

In this example, let’s migrate Tst_1 which we created above as a normal user to contained user within contained_Tst database. See below screenshot

15

Voilaaa…..Done!

Caution: Make sure not to disable login while migrating unless you really want to. Because, this login might have a user mapping in some other databases which will break things severely.

Hope this gives a decent amount of exposure on contained databases and what can be achieved making use of them.

SQL Server 2012 SP1 got released….!

Today in the PASS summit, Microsoft announced SQL Server 2012 Service Pack1 which is made available to download for public officially. You can download this from here.

One cool thing to notice is that URL provides you with two flavors of downloads -SP1 Update and  SP1 Slipstream 🙂 Personally, I like this move by Microsoft to provide Slipstream Downloads. You know why, if you’ve had to prepare your own SlipStreams for your SQL Server Installations at work!

You can get the List of Bugs got fixed in SP1 here.