Configuration – MSSQLTREK

Physical Server or a Virtual Machine?

Lot of times, when stepping up for supporting a new SQL Server Instance, we as a DBA need to know whether it’s running on a Physical Machine or a VM. In this post, I will provide you guys with a simple T-SQL Script which you can run from your client machine(SSMS) connecting to the respective SQL Server, which lets you know if it’s a Physical machine or a VM.

Note: This Script works only on 2008R2 SP1 and above.

SELECT SERVERPROPERTY('computernamephysicalnetbios') AS ServerName
,dosi.virtual_machine_type_desc
,Server_type = CASE 
WHEN dosi.virtual_machine_type = 1
THEN 'Virtual' 
ELSE 'Physical'
END
FROM sys.dm_os_sys_info dosi

/* If you have a CMS configured, run the below Script from your CMS against multiple servers*/
SELECT dosi.virtual_machine_type_desc
,Server_type = CASE 
WHEN dosi.virtual_machine_type = 1
THEN 'Virtual' 
ELSE 'Physical'
END
FROM sys.dm_os_sys_info dosi

Additional Comments:
“virtual_machine_type_desc” Output value description
NONE – SQL Server is not running inside a virtual machine.
HYPERVISOR – SQL Server is running inside a hypervisor, which implies a hardware-assisted virtualization. If the instance is running on the host OS, the description will still return HYPERVISOR.

SQL Won’t come up after Installing Service pack!!

Hello folks! In this blog post, I would like to share a strange experience which I’ve never experienced till today while Installing Service packs for SQL Server.

Well, I’m in the process of Installing new SQL Server(2012) on Windows Server 2012. Installed RTM…Everything went well. Now I kicked off applying Service pack 1 for SQL Server 2012. Erkk…..SQL services won’t come up and the event viewer says “Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.” and the one which is shown below!!!…

“Script level upgrade for database ‘master’ failed because upgrade step ‘u_tables.sql’ encountered error 25641, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.”

Whatttt….?? Really? Nothing useful found in the SQL Error logs as well 😦 My immediate thought was to run Repair and I kicked off the repair wizard. It took its own sweet time. 25 mins passed and I got this stupid message:(

Agian, Nothing useful found in the SQL Error logs 😦 End result is it couldn’t fix it 😦

This is not good…!

So what’s the work around/Fix for this issue which worked for me in this case?

I changed the Service account for SQL Server to Local System and Boom…it worked 🙂 SQL Server DB Engine Service started and the build number has been updated as well! Once am done with Installing Service pack and the CU, I changed it back to the appropriate domain account(as it was earlier).

So, this sounds to me like some missing permissions issue but am not 100% sure what exactly it was looking for. Please let me know if you’ve encountered this issue in the past and what was the resolution.

Importance of reviewing your SQL Server Error Logs.

Few weeks ago I posted a poll here asking for how often do you review your Error logs. As promised, here comes the follow up post:)

Unfortunately, 45.45% out of 11 people said “Never and I’ll open Error log only If I want to investigate something” . IMHO, this is not a good practice(Especially if you want to be an outstanding DBA). Please don’t get me wrong…Am not saying that you are not an awesome and outstanding DBA! ( Well, People call you a DBA because you are already outstanding 🙂 )

Okay…….let me say this guys, Start reviewing your Error logs daily as the first thing when you get into your cubicle/desk/office. Trust me, there will be lot of surprises(at least a few) based on number of Servers(Instances I should say) you manage. SQL Server Error log is really one of the best buddies a SQL DBA has got for free besides default trace 🙂 . I simply love these two things in SQL Server which provide so much useful information in case of investigating something(At no additional cost/overhead).

A simple real life use case: SQL error log records all the failed login attempts by default, if you keep an eye(or even Just glance over it) you should know if there are any unwanted/unsafe login attempts(Which are failed) hitting your SQL Instance. For example…I see quite a few times, incoming connections from a particular host attempting to connect as “sa”. Error Log records IP address of the machine and the time stamp of course. I can just do a NSLOOKUP and check with respective owner of the machine on what’s happening. This is just one useful scenario, there are tons of use cases which you can come up with!

Believe me guys, there will be lot of surprises for you and lot of things you’ll discover about your own SQL Server Instance which you don’t know, just by reviewing your error logs on daily basis for a week or two!

Most of the times, I hear people saying ” I am responsible for managing multiple Instances, I really don’t have time to get into each of my SQL Server and review error log. It will eat up my entire day“. Well, you really don’t have to do this. All you have to do is just setup a job and automate which basically reads all the error logs from all of your servers and generates a fancy report and sends you an email.(All you need is to just create a simple SSIS package and SSRS for fancy reports) or may be Powershell could help you.

If you just have handful of SQL Instances to manage, see this blog post.

Basically, This will allow us to be “Proactive” and not being “reactive”. Preventing something bad happening to your database as opposed to fixing will really make you stand tall in front of your management and your peers. Am not saying, by reviewing Error logs daily you can literally prevent any issue from being happening. But you will be definitely able to reduce number of “OOPS…” moments!
IMHO, Even though part of DBA job is to troubleshoot/fix database related issues, our prime time goal as a DBA should be trying to prevent those issues from occurring at the first place!
Let me know if you need any help automating this process. I will be glad to point you in the right direction.

Cheers! Happy friday 🙂

sp_configure and sys.Configurations

This would be a very small blogpost, Well, actually an informational Post, no Issues and no troubleshooting shown in this post!

We all, being a DBA will be running “sp_configure” every now and then on our SQL Instances to see what are the configured settings on the Instances. okay, okay….If you don’t have this Practice, make this a habit from right now. Document your Instance settings in your Run Book and compare with them each time you run this and you can analyze what/why/when/who changed accordingly.

Okay, By default if we run “sp_configure” on our Instance, we’ll be getting only 17 Rows(as of SQL Server 2012 RC0).

So are those the only configurable Settings we’ve in SQL Server? Nope!

Actually, you can get all the configurable options from querying sys.configurations, where it returns 69 Rows as shown below.

Is_Dynamic = 1 implies, no need of running RECONFIGURE Statement after changing the setting.

Is_Advanced = 1 implies, we should run RECONFIGURE Statement after changing a setting.

Now, for sp_configure to return all the 69 Options(rows) all we’ve to do is enable “show advanced options” and run “RECONFIGURE” as shown below.

Once, done issuing “SP_Configure” will return all the options available as you can see below.

Now, you can see 69 rows being returned. Cheers!

SQL Server Error Logs??

Couple of days ago, I had conversation with a Jr.DBA regarding SQL Server Error Logs! She was confused with “Error Logs” under SQL Server Agent Node and “SQL Server Logs” under Management Node. In Fact, she was assuming that both are same which is not the case!!!

Also, I heard many times from many confused DBA’s that SQL Server Error logs can’t be accessed when the SQL Services are Offline, which is absolute Bogus!! SQL Server Error log is nothing but a plain Text file located in our “LOG” folder by default which can be opened using Notepad when you are not able to connect to SQL and see via SSMS.

In this blog post, let me make it clear what/Where are SQL Server Logs and what/Where are SQL Agent logs are. Please see below Screenshot.

As you can see we’ve “SQL Server Logs” – Under Management Node and “Error Logs”- Under SQL Server Agent.

Note: All these Error Logs(SQL Server Logs and SQL Agent Logs and any Dumps being created) will be located in “C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\Log” location. – In my case, I’ve SQL Server 2008R2 and a named Instance ‘PROD’, hence folder “MSSQL10_50.PROD”. “LOG” folder is not for storing your Transaction Logs!!!

If you are confused even now, You can use T-SQL to read your Error Log.

How to read SQL Server Error Log from T-SQL??

Method 1: EXEC XP_READERRORLOG

Method 2: EXEC SP_READERRORLOG – This is UnDocumented way! But this gives lot more flexibility to select the number and filter the contents etc…For example I want to read the contents from my 5th Error Log which has the word “Master”. For this, all i want to enter in my Query Pane is Exec Sp_ReadErrorlog 5, 1, ‘master‘. As you can see below I got only 4 records returned where ever word “master” is present.

Hope this helps…!