Top 10 Secrets of a SQL Server Expert

Here are the top 10 ways a SQL Server DBA can take control of his environment and reduce the overall potential for crises to occur.

10. Take Inventory
9. Standardize Configurations
8. Understand the I/O Subsystem
7. Create a Customized Maintenance Plan
6. Ensure the Security of Your System
5. Get on Good Terms with Your Developers
4. Develop a Comprehensive Disaster Recovery Strategy
3. Take and Test Regular Backups
2. Monitor and Maintain Performance
1. Know Where to Find Information

Read the article here

SQL Server Management Service (SSMS) of 2008 R2 is crashing when editing steps in SQL Server Agent Jobs

SQL Server Management Service (SSMS) of 2008 R2 is crashing when editing steps in SQL Server Agent Jobs
When editing/viewing steps in SQL Server Agent Jobs in SQL Server 2008 R2, SOMETIMES SSMS crashes with following error message.

------------------------------

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

------------------------------
ADDITIONAL INFORMATION:

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

------------------------------
BUTTONS:

OK
------------------------------


http://connect.microsoft.com/SQLServer/feedback/details/557402/ssms-can-no-longer-create-or-edit-job-steps



Temporary work round would be to close SSMS and start it again. However, this is bug exists with SQL Server 2008 R2 and you need to install CU3 for this. When the service pack for SQL Server 2008 R2 is released you can use that service pack to resolve the issue.

Code Names for SQL Server

You must have heard about codes names used for various products released by Microsoft. Do they have any logic behind naming them? Recently I had the opportunity of watching a PASS SQL Server session conducted by Stephen Forte and at the start of the session he discussed about Microsoft product code names. That was really nice and thought of sharing with others.

Different product teams have different conventions of naming them. Windows CE team naming their products after Whiskies. For example, Talisker, Macallan etc. Visual Studio team is naming their products after Islands or Cities Pacific North West. So what are used for SQL Server? Well, National parks are used for code names are SQL Server.

Shiloh – SQL Server 2000

Shiloh is National Military Park in US. Shiloh National Military Park preserves the American Civil War Shiloh and Corinth battlefields.

Yukon – SQL Server 2005

Though many are saying this is a mountain, there is a National park called Yukon in Alaska.

Katmai – SQL Server 2008

Katmai is again a National park. Katmai National Monument was created in 1918 to preserve the famed Valley of Ten Thousand Smokes, a spectacular forty square mile, 100 to 700 foot deep ash flow deposited by Novarupta Volcano. A National Park & Preserve since 1980, today Katmai is still famous for volcanoes, but also for brown bears, pristine waterways with abundant fish, remote wilderness, and a rugged coastline.

Kilimanjaro – SQL Server 2008 R2

Kilimanjaro, The name itself is a mystery wreathed in clouds. It might mean Mountain of Light, Mountain of Greatness or Mountain of Caravans.

Denali – SQL Server 2011

For the future version of SQL Server again the standard is maintained. Denali is situated in Alaska.

Finally , there is a another cool story about code name Longhorn. That is the code name for Vista. Can you guess what longhorn is? Well it is a BAR!!!. Yes it is a bar situated between Whistler and Blackcomb it is said to be easily the most popular bar in Whistler.one of the most favorable locations in all the drinking world. You can view some of the pictures from here.

Singing Pass in August as seen between Whistler and Blackcomb

Whistler is the code name for Windows 2003 and Blackcomb is code name for Windows 7. Since Vista release between Windows 2003 and Windows 7, Vista was named after bar which is between Whistler and Blackcomb.

SQL Server Agent job syspolicy_purge_history is failing in the cluster environment

When you install or upgrade SQL Server 2008 or R2 you will see a new SQL Server Agent job named syspolicy_purge_history. In fact this is the only job you will see after installing a brand new SQL Server 2008 instance.

More at http://www.sql-server-performance.com/faq/syspolicy_purge_history_failing_p1.aspx

Process & Processor

Performance Monitor a.k.a perfmon is used to monitor different counters for different purposes. However, some counters are bit confusing. Process and Processor two misleading counters so thought of putting this note.

Processor

When adding processor object, you have _Total object and 0, 1 .. which are the processor number. Following the graph for _Total (Red) , 0 (Blue), 1 (Pink) objects for % Processor Time counters. ( Colors given in the graph for relevant counter)

In the above graph, you will see _Total is NOT the total of processer 0 and 1. But it is the average of them.

For example, let us say you have four processors of % Processor time of 20, 30, 50, and 80 and _Total count will be 45.  i.e.  (( 20 + 30 + 50 + 80 ) /4)

Process

While processor is the counter for your processors and Process is counter for each process you are executing right at the moment.

for example,  if you wish to measure % processor for sqlserver, this is the measure you have to select.

in this also, you have the _Total counters. Unlike the Processor counter this counter is sum of all the processes INCLUDING idle processor.

From the above graph, you can see _Total counter value is through out 200.

So if you want to match Process and Process it will be following,

\Processor(0)\% Processor Time  +  \Processor(1)\% Processor Time  + … All the other processors

= \Processor(_Total)\% Processor Time  -  \Process(Idle)\% Processor Time

Is There a Shortage of SQL Server Experts?

Do you think we have enough SQL Server experts? Article from Brain indicate there are shortage in SQL Server experts.

“There are certainly many world-class SQL Server experts, and there have been for quite some time. I suppose it’s more of a matter if there are enough available to satisfy demand. One observation I’ll make is that many of “famous” SQL Server experts I know are consultants who presumably aren’t interested in working full-time for a single company in a DBA capacity. Note that I didn’t say the best SQL Server people are consultants; I used the word “famous.” I’ve long suspected that for every PASS pre-con speaker there are dozens of people who are just as talented on a technical level and don’t desire to be famous or simply haven’t had the break that propels them to attention on the community stage. So, I wonder—am I right about that? If I’m right, then I suspect that the lack of expert and very senior SQL Server technologists is largely perception rather than reality.”

Getting Job Category for the SQL Server Agent Jobs

Getting Job Category

There are Job categories associated with SQL Server Agent Jobs. As you know you can get the job information by querying sysjobs system table.

SELECT J.name as jobName,C.name Category FROM sysjobs J

INNER JOIN syscategories C ON J.category_id = C.category_id

WHERE C.category_class = 1

Now you can see this is not huge query. But the problem is Microsoft documentation, If you go the sysjobs documentation as shown in the following image, id does not say from which table you should get the category from. Since job category table does not have job prefix it is bit difficult to find this out.

But the documentation not that bad since you have http://msdn.microsoft.com/en-us/library/ms181367.aspx page which will tell you all the related tables for the Agent Jobs. However, it would have been much better if this information is mention at the sysjobs documentation itself.

Dilbert & SQL Function

I am not a fan of Dilbert. But this seems to be something related to SQL.  

SQL Agent job getting suspended

When you run some SQL Server agent jobs they will be suspended and you will not be permitted to re-run them.

http://www.sql-server-performance.com/faq/sql_agent_job_suspended_p1.aspx

Know your Data with Data Profiling

Data quality is become a major issue in database. In SSIS, there is a new control task called Data Profiler. See my article in sql-server-performance.com