Month: December 2011

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”!

Advertisements

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…!

Connecting to the Integration Services service on the computer “mirrorstand” failed with the following error: “The RPC server is unavailable. “.

Couple of days ago we’ve seen how to fix DCOM permissions issue while connecting to SSIS remotely. See here.

In this Blog Post let’s see one more annoying error(RPC Server unavailable) we see developers complaining about, while connecting to SSIS 2008 Server from there client machines.

Connecting to the Integration Services service on the computer “mirrorstand” failed with the following error: “The RPC server is unavailable.
“.This error occurs when the computer host name does not exist, or cannot be reached.

I’ve SSIS Service running on “mirrorstand”  machine and I’m trying to connect from another machine “node2”. Please note, I’ve not configured Firewall yet on my “Mirrorstand” machine(Windows Server 2008).

How to Fix this Error?
All we’ve to do is add couple of exceptions in our Firewall, basically to allow “MsDtsSrvr.exe” Program and SSIS Port(TCP 135). Let’s see how to do that in action.

Step 1: Logon to your SSIS Server and Open Firewall with Advanced Services and create a new Inbound Rule for TCP Port 135.

Step 2: Click on New Rule and select Port as shown below.

Step 3: Select TCP and enter 135 as port number as shown below.

Step 4: 

Step 5: 

Step 6: 

Now, The most important part comes here. We’ve to add MsDtsSrvr.exe as an exception. Create a new Rule and follow the same screenshots, but You’ve to choose Program instead of Port and browse to the executable.

Typically, you should be able to locate this executable in “C:\Program Files\Microsoft SQL Server\100\DTS\Binn” Folder as shown below.

And, my firewall Inbound Rule looks like below…

Once I added the Port and Program to my Inbound Rules on my SSIS Server, I’m able to successfully connect to SSIS(mirrorstand) from “Node2” machine as you can see below… 🙂

Hope this info helps when you get into this situation…Cheers!

 

Few Useful(often Ignored) DBCC Commands…

Thought of compiling all the useful DBCC Commands for DBA related to Memory Management within SQL Server. Well,  I’ve seen many DBA’s unaware of the below very useful commands staying under assumption, CHECKDB is the only DBCC Command we’ve in SQL Server.

Okay! Let’s see what do SQL Server offer to DBA’s to do  a diagnosis of SQL Server Memory.

DBCC DROPCLEANBUFFERS : This Command will remove all the Data Pages from the Buffer Cache. Basically, you can issue this if you want to clear your Buffer Cache.

DBCC FREEPROCCACHE: This command will remove all the execution plans from Procedure Cache.

DBCC FREESYSTEMCACHE(‘ALL’) : This command will remove all the Unused entries from all the available caches. It includes Plan cache as well!

DBCC FREESESSIONCACHE : This command will Flush the Distributed Query Connection(Linked Servers) Cache on SQL Instance.

DBCC FLUSHPROCINDB(DB_ID) : This is a pretty useful command which clears Query Plans related to a given Database.
Note: This is an Undocumented Command. 

Hope this is useful! Be very cautious and understand what you are doing before issuing these Commands blindly on your Production SQL Instances to resolve an issue, especially on an Instance which is shared by Multiple applications with many Databases.

How to Move a Mirrored Database MDF and LDF Files?

Couple of days ago, I had a conversation with one of my colleagues, on how to move database files from one location to another which is has DB Mirroring enabled. In this blog post, let’s see that in action..

Let’s consider the below Scenario:

1. You’ve a Large Database(let’s say 500+ GB DB) which is participating in DB Mirroring as a ‘Principal’ Database.

2. That’s the only database sitting on this Instance, a Dedicated Instance for your Application(In other words, detaching-attaching DB will cause almost the same impact as SQL Services Restart)

3. You want to Move your MDF and LDF files to another LUN (because of space constraints or you are doing SAN Expansion or something else)

4. You do not want to Break Mirroring completely, because you do not want to re-configure everything from scratch…which takes considerable amt. of time and not acceptable to your Business, running without DR for that long period of time.

5. What is the option we have in this case?

Solution: First thing to note, we can’t detach a Database while it is participating in Database Mirroring. You’ll get the below error if you try to do so..

“The operation cannot be performed on database “Mirror_tst” because it is involved in a database mirroring session.
ALTER DATABASE statement failed.”

so what to do now..? Remember, we’ve ALTER DATABASE…..MODIFY FILE….to do this trick.

Demo:

I’ve a database called ‘Mirror_tst’ which is being mirrored from PROD Instance to DR Instance as you can see below. Also, please note the current database file locations in the below screenshot(As of now, they are in default location)

Now, Let’s update the database file(s) location in system catalogs to “C:\Backs\” folder. For this I’ll issue the below commands.

ALTER DATABASE mirror_tst
MODIFY FILE(NAME= mirror_tst,FILENAME=’C:\Backs\mirror_tst.mdf’)
GO
ALTER DATABASE mirror_tst
MODIFY FILE(NAME= mirror_tst_log, FILENAME= ‘C:\Backs\Mirror_tst_log.ldf’)
GO

and i received below messages:

The file “mirror_tst” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “mirror_tst_log” has been modified in the system catalog. The new path will be used the next time the database is started.

So, the database file locations were modified in system catalog, all we have to do now is Stop SQL Service, move the physical files and start SQL Services. Let me do that!

I issued SHUTDOWN WITH NOWAIT on my PRINCIPAL Server as you can see below.( Be very cautious with this statement, understand what exactly you are doing before running this statement on your Prod Instance, believe me guys, Running this single statement unknowingly might cost you, loosing your Job!!..)

 

As you can see SQL is stopped and now, I moved the Physical files to ‘C:\Backs” folder as you can see below.

Now, I’m starting SQL Services and let’s see what happens to our database and more importantly to our Mirroring Status, keeping my fingers crossed 🙂

Once Restarted, the SQL Server was happy to bring up this Mirrored Database and our Mirroring Process is running happily without any issues as you can see below:) 🙂 Notice Physical files new location and the Mirroring Status now. Everything is pretty Normal:)

Hope this post helps,if you get into same scenario….Cheers!

Connecting to the Integration Services service on the computer “mirrorstand” failed with the following error: “Access is denied

In this Blog post let’s see how to fix DCOM Permissions issue for an user who receives below error when trying to connect to “SSIS” Remotely from a remote machine.

The Error Message you’ll receive when you are not granted required DCOM permissions is..

Connecting to the Integration Services service on the computer “mirrorstand” failed with the following error: “Access is denied.
“.

This error occurs when the computer has not been configured to allow remote connections through DCOM, or the user does not have permission to access the SQL Server Integration Services service through DCOM.

Note: The SSIS Server which I’m trying to connect is “MirrorStand”. The login which I’m trying to connect is ‘SREE\HR1″

So, how to fix this Error?

Solution:

Step 1: Log on to SSIS Server and Go to Component Services in Administrative Tools as shown below.

Step2: Navigate to DCOM Config ->MsDtsServer100 as shown below.

Step 3: Right Click on MsDtsServer100 and go to Properties and navigate to “Security” tab as shown below.

Step 4: Click “EDIT” beside all the three Options(Launch and Activation Permissions, Access Permissions and Configuration Permissions) shown in the above screenshot…

Once you click on ‘Edit’ button you will get the below screen where you’ve to add the required login as shown below.

Once adding in DCOM – component services is completed, then navigate to “Users and Groups” in computer Management and add the login in “Distributed Com Users” Windows Group as shown below.

Once you are done with adding the above shown DCOM permissions, you have to Restart SSIS Service. Then the login(user) should be able to connect to SSIS remotely.

As you can see below in the screenshot, I’m able to connect to SSIS Remotely (from a different machine, “Node1” in my case ) as ‘Hr1’ Login.

Tadaaaaaaaaaaaaaaa……..We made it 🙂

Hope this Helps!