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!

Windows Networking Tools for SQL DBA’s – PATHPING/TRACERT/PING

Hey Folks! Hope you all had fabulous holiday season 🙂

In this Post let’s see Three fabulous tools which Windows OS offers for testing your Network between your Servers. A typical example for DBA’s would be testing Network Latency between your Prod and DR Server while initial testing of your Mirroring configuration or even looking for latency/Response times when you are copying a huge Backup File from one Server to another Server etc.

Tool 1: PING

This is one of the most used command by any IT professional, irrespective of what he/she does. PING is a very Basic test to verify IP connectivity from one machine to another.

Eg: PING MachineName ; PING IPAddress; PING -T MachineName/IPAddress(T for Continuous ping).
For More Options and Info please visit http://technet.microsoft.com/en-us/library/bb490968.aspx

Tool2: TRACERT

This is another useful tool which traces the entire route(Includes all the Routers/NIC’s involved in your Path) from Source Machine to Destination machine.  Pretty useful tool.

Eg: TRACERT Machinename
For info on how to interpret those results, please visit http://technet.microsoft.com/en-us/library/cc940128.aspx

Tool3: PATHPING ( Awesome Tool to have in your ToolKit)

This is one of my all time Favorite tool which basically mixes both PING and TRACERT Results and will provide you with a very detailed/useful information.

Eg: PATHPING MachineName. In this Example I’m doing a Pathping from my Domain Controller to one of the server in my domain. See below on how it looks for the output.

Typically, you would see many Network Interfaces in between(Of Course right…!). Try “PATHPING GOOGLE.COM” from your machine and you can see what I mean. Or Just try PATHPING on one of your Servers in your company, you’ll see what I’m trying to say.

Tip: I typically make use of “>”  appended to my command, so that I can save the Output as a report in a text file and I can review later any time or you can simply forward that text file to your Server/Network Team to look into. Please see below for what I’m trying to explain(Basically, it’s something like “SPOOLING”  in Oracle if you are familiar with ORACLE SQLPLUS).

Now it created a Text file called “Output.txt” under my C Drive and the contents are as shown below..

Trust me Guys, PATHPING is one of the most Useful tools we have for free, which many of DBA’s are not aware of, or I should say most ignored Tool by many DBA’s!

Technet has fabulous info on how to interpret results and options available etc here: http://technet.microsoft.com/en-us/library/cc958876.aspx

Hope this Info helps!…

Work Ethics/Morals

Work ethic is a set of values based on hard work and diligence. It is also a belief in the moral benefit of work and its ability to enhance character. An example would be the protestant work ethic. A work ethic may include being reliable, having initiative, or pursuing new skills. – Extracted from Wikipedia!

Why are Work Ethics/Morals so important no matter how good you are at/with your technology? Well, the “ethics” which you carry will define your real “you”. It’s not always Just about how good you are Technically, sometimes, I would say many times, all it matters is how/what’s your behavior with your peers.  IMHO, being professional, no matter what our jobs is…is bare minimum expectation any fellow worker would expect from you. We should learn how to be “Professional” before becoming IT Professionals! Being polite is no bad, even if you are a Pro in your technology. In fact, the more polite we are, more the people like you and it adds you lot of applauds and cheers to your Bucket!…

Let me ask this, What would be your opinion on a person who always say” Yes, I know it, i know how to do it”  just as a default Statement/Answer, but if asked to do it, he/she has no idea how/what was the question about? I really don’t understand why people fear to admit that they don’t know things. Being a SQL DBA, I admit I’m not an Expert/ GURU in each and every aspect of SQL Server. In fact No one can be a true Guru in each and every aspect of SQL Server. Well, SQL Server is not Notepad Application which you’ll come to know all the options available in couple of minutes/hours!!  Well, this is about First set of people.

Now, let me tell you about second set of people..sighsss…………!

They claim that they know everything, well, but If they are asked for same to show they’ll no where close to solution. But still, they tend not to agree that they “don’t know”. Well,…Being a good professional ;), assume that I’ve started to show how things work, they get a phone call(to his/her personal Mobile) and they left my cubicle. What exactly they are achieving here? Well, nothing! One thing is clear is that they know nothing and they are trying to escape from things and more importantly they created an impression to me that, they are “Worthless“. What if the same person sleeps in a conference room while you are trying to explain something new?? It happened to me recently and believe me, I was like “Reallyyyyyyyyyyyy”

What would I feel about them??(Just my Personal Opinion,YMMV)

In my humble Opinion, they are not worthy enough to receive my help/attention! Yes, that’s what comes to my mind as a first thought. Why should i really care, if he/she him/herslef has no zeal towards what they are doing for survival???…

1. They know nothing except faking things all around the place. I will never ever offer any help, because as per His/her statement ” They already know it ;)” anyways even if I try to help them, they will try to escape. – They think that they are in Safe Zone by escaping from situation, but in reality I got them!! It doesn’t take more than few minutes to get to know about his/her skills in what ever work they are doing!…

2. I’ll never offer help for carrying such ruthless attitude!

3. They are not fit for this Job. Yes, I mean it!!

4.They are  Useless and my Company is paying him/her money for no worth.

5. I would never give +ve feedback on him/her to my Management.(Of course, they are of no worth to my organization)

Bottom Line : First Learn how to be professional, Then you can learn about your Technology. Don’t shy away, If you don’t know something, say it loud, ” I Don’t know that, can you please shed some light on this? ” There’s nothing wrong in saying ” Yes, I don’t know”!

Could not load package “\File System\Test” because of error 0x80070002.

Well, today I had a very funny/annoying/frustrating Issue where I was getting the below Error message when I tried to Run a Package from SSMS connecting to SSIS.

TITLE: SSIS Execution Properties
——————————
Could not load package “\File System\Test” because of error 0x80070002.
Description: Could not find file ‘C:\Program Files\Microsoft SQL Server\110\DTS\Binn\..\Packages\Test.dtsx’.
Source: mscorlib

Basically, I got that SSIS Package from a developer to Deploy on the Server and we do File System Deployments here in my Company. Before deploying the package onto Server and scheduling via Agent, I was asked to manually Run the Package and test it. Well, I was  successfully able to run that Package Just by Double Clicking and invoking DTExecUtil.exe. Everything is fine upto this Point. When I tried to Import the Package onto SSIS Server and run that package I was getting this Annoying and frustrating Errors…

Let me recreate the issue and show you what I’m trying to explain…

Step 1: I’ve a Package called “Test” in my Default Package Store as you can see below.

Step 2: I select Run package from SSMS as you can see below.

Step 3: Now, I click on Execute Button. and I get the Error Messages which I’m talking about!

Step 4: Now, Let me double click on the SSIS Package from the NTFS Folder path. I’ll get the same Execute Package Utility where I have an option to execute the package, but this time I’m able to Run the Package without any issues. So what’s really happening here???

Initially I thought it might be because of Permission Issues but that’s not the case.  I changed the Package Store Location and Restarted SSIS Service, but it didn’t helped me either..What’s happening really?? I was really frustrated at this point and Of Course  Hot Coffee made my mind to work Sharp 😀

If you observe the below Screenshot very carefully, there is a space between the file name and .dtsx!!!!

As you can see It’s “Test .dtsx” not “Test.dtsx“…ha ha! Yup…This was the Issue. Once I removed that Extra space between File name and Extension SSMS and SSIS both were happy as shown below!!

So the Problem was that single stupid Extra Space and I was like “Reallllyyyyyyyyyyyy??” It caused me 20 Minutes of my time….Sounds really Stupid right….?? But trust me folks, it was one crazy and Funny Error.

Bottom Line : SSIS doesn’t like Spaces 😉

Hope this helps when you get into any similar Issue 🙂 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…!