When was SQL Server Restarted??

As a DBA seldom we see cases where a User reports “Hey Mr DBA, my application went down for a minute or even few minutes, Now everything appears to be Okay. Can you pls take a look what happened?” Well, our very First place should be SQL Server Error Logs( My Personal favorite to start troubleshooting any issue) and let’s assume in this case that our Instance has been restarted and we see it in our Error Logs. So this is the traditional way to find out regarding SQL Server restarts – The SQL Server Error Logs.

Let’s jump into another scenario where you want to know when was the SQL Server restarted. It would be little annoying to cycle through all the error logs in SSMS if you are recycling your Logs on a daily basis and maintaining multiple Error Logs(PS we can have upto 99 Archives of Error Logs listed in our SSMS). So what would be our easiest bet to figure out when was the Instance restarted??

Method 1:

TempDB Creation date:

Since we all know Tempdb will be created each time you restart SQL Server, Tempdb creation date will give us the answer.

Alternate Methods:

1st Session(Session_id = 1) creation time and by starting SQL Server 2008, we can query DMV sys.dm_os_sys_info.

FYI Please see the below Screenshot

Note: Notice a slight difference in Milli seconds( could be seconds) in tempdb creation time and the time when SQL created it’s first session. But that’s Okay! Choose your Best method. I use tempdb creation date(since I’m used to it since SQL 2005). May be I’ve to start using DMV on new boxes!!

Hope this helps!


Installing SQL Server from Command line(CMD)

How often do you Install SQL Server in your Environment?? Monthly Once? In that case, I prefer just double clicking setup.exp in my installation media and follow the instructions from GUI. How about Weekly Once? Well, i would go with GUI again…considering the time i’ve for completing the task..What if you got a new SQL Farm where you are assigned with a task to Install SQL Server on all your Non Prod environments in a single day or two..Assuming you’ve DEV,SIT and UAT as your Non-Production environments and you’ve to install 8-10 Instances on a single day, i strongly prefer installing SQL Server from command line. It might be little annoying initially, but believe me you’ll be saving 65-70% of your precious time just by providing few parameters to your setup.exe from cmd line. Basically you’ll be skipping all the screens in GUI where you are supposed to provide all the required information through out the installation process.  Let me show you what exactly I’m saying….

I’ve inserted my Installation Media(actually mounted my ISO using Deamon Tools) in my DVD drive and navigated to setup.exe from cmd(Run as Administrator). You can enter SETUP.EXE /? if you want to know all the available Options and Switches which you can make use of in this Installation as shown below.

Before proceeding any further, this is my game plan for Installation:

SQL Server 2008 DB Engine, No SSRS, no SSAS, no SSIS, no Tools(SSMS/BIDS). FYI…My Laptop already has 2 SQL 2008R2 Instances and 1 DENALI Instance….So I’m limiting my installtion with just SQL Server 2008 DB Engine.

InstanceName: SQL2008

SQLSVCACCOUNT (Service Account for DB engine): NT Authority\System

AGTSVCACCOUNT (Service account for SQL Agent): NT Authority\System

SQLSYSADMINACCOUNTS: Sysadmins on SQL Server. I’ll add myself here.

Features: SQL(This implies Just DB Engine, If you want to Install SSIS, SSRS, SSAS as well on the same server – which you should never do; you can enter IS, RS and AS as well)

SECURITYMODE: Windows Authentication/SQL Authentication – I’ll choose SQL in this demo.


So, combining all together my syntax would become

All i did was just navigating to G:\(MY DVD Drive) and typed(ctrl+c and Ctrl+v 😉 from my scripts toolbox) the below and just hit ENTER on my Keyboard!!


I’m welcomed with the installation progress(you can see screenshot below)

After 4 minutes I was returned to the main command prompt without any issues…and you can also see my Config manager with our new Instance(SQL2008) being successfully installed in the below Screenshots

Perfecttttttttt……..:) We avoided all the PAUSE and halts which we usually face while Installing from GUI. At this point, even if you want to change any service accounts or even Data/Log file locations, It’s Just matter of few minutes…. from our config manager!! So..all you need to do is Just save the above Syntax somewhere in your documentation and you can Just Copy Paste by changing the required parameters such as Instance name, Service accounts…etc as needed. Having a standard Drive(s) naming conventions and standard Drive Letters across your enterprise on your SQL Servers makes this process much simple as you can see!

Hope this is useful and you’ll start exploring this if you haven’t yet!!


Setting up Email Alerts for PBM(Policy-Based Management) Policies

PBM(Policy-Based Management) – One of the most fancy terms which we(the fantastic SQL Server DBA’s) are listening starting SQL Server 2008. In this Blog post I’m not going to explain in detail what PBM is how exactly it works, instead I’m going to show how to create a Policy for checking Auto-Close, Auto-Shrink,Compatibility Level and Recovery Model of all the databases on a regular basis and how to setup email alerts if any of our databases are violating our policy.

For detailed explanation on PBM PS http://msdn.microsoft.com/en-us/library/bb510667.aspx

As i mentioned in my previous post, setting up DB Mail profile is mandatory for sending email alerts from SQL Server. Okay, enough blabbering..let’s get started!

As the first step, under “Management” Node in your OE(Obj Explorer) expand “Policy Management” and RC on “Policies” and choose create “new policy” as shown below.

Note: You can create conditions first and choose them accordingly for any future policies as well. In this post, I’m going to create everything from Create new policy Option it self, which avoids confusion for people who are new to PBM.

Name your Policy and select “create new condition” to create one as shown below.(you can pick from here if you’ve already created one in the past as mentioned in the above note)

Once, you click on “create new condition”, you’ll be welcomed with a new screen as shown below. Give any appropriate name for your condition and choose one facet from the drop down as shown below. I this case I’ve to choose Database Options Facet, because i’m creating a policy to evaluate DB Options.

select all your required options and create an expression using And/OR, Drop down Menus as shown below and Click OK. You can add your own description if you need any under “Description” tab.

Once done, now you have choose your targets(I selected against every Database) and evaluation mode(I’ve selected on schedule and created one to run/evaluate on daily basis) as shown below.

Once done..you can add your own description(optional) and click Ok. At this point we are all set with setting up a policy which runs on a daily basis to evaluate all our conditions.  Let’s try to evaluate manually and see how it looks.

Go to your policy node and RC and select Evaluate(Enable the policy if it’s disabled). You’ll be welcomed with the below screen which is very much self explanatory…

You can click on View to see more details on the violation and also you can click on Apply to fix them all from right here with a single button click.

Configuring Email Alert:

Soo…we haven’t configured email alerts yet upto this point. To achieve this , the simple trick is to know the exact Error Number(s) which PBM generates in our Error logs and to setup alerts/actions for those Error Numbers in our SQL Server Agent. As per BOL the below are the error numbers which we’ve to look for when setting up alerts for PBM

Execution mode Message number
On change: prevent

(if automatic)

On change: prevent

(if On demand)

On schedule 34052
On change 34053

To double check, Let’s see our SQL Error Log and Event Viewer what has been logged…

Okay…..a message has been logged with eventID 34052. So…now let’s configure Alert to notify operator from SQL Agent for this Error Number.

FYI..I already created an Operator as you can see below

Okayyy…it’s time to create Alert. Follow the below Screen shots in the same order

Choose Error Number as shown below

DO NOT JUST CLICK OK now….Instead go to RESPONSE tab and choose an operator whom you want to notify for this Alert based on this Error Number as shown below.


Again, don’t just hit OKinstead go to Options and enter a customized message which you feel appropriate for you as shown below

Now click OK:) and let’s try to evaluate our policy manually as we did earlier(Go to your policy node under Management in your Object Explorer and RC and select Evaluate) for testing our policy initially and let us see what we’ve in our Inbox.

Perfectttttttttttt………!!! See how simple it is…to make yourself a hero with all these cool automation stuff 🙂

Hope this Helps!!