SQL Server's Fascinating Stories

There are many times, I felt the need to capture those processes running and consuming CPU on an SQL Server. It comes to a boiling point when that dearth is elevated to make me feel doing a labor while the CPU utilization has peaked due to SQL Server. As a result, my first question to any SQL Server expert was the same, as to why there is no direct way to capture the detail. As a result, my learning lead me to WMI, a treasure for an admin in automation mind. (Trivia: Windows Task Manager works on the same WMI principle used in this code). This is a POLL based CPU Usage reporting, as there is no direct triggering mechanism provided by Windows by any other means to use them appropriately.  Below query can be put into an SQL Agent job, with a Powershell step, which can be called every 1m (Job completion takes ~53sec per my test). This job mails you the most CPU consuming processes on a server whenever CPU usage breaches 90%(Can be changed per your need). If you need precise Utilizati

Revisiting the blogger to finally start again blogging about the SQL Server after a long long time.  This post is on analyzing and digging into what is causing a job significant time to complete. This will be a basic level of approach and I wish, I could have done better. But, its was enough in my case to get to a conclusion, with the knowledge I equip.   Issue : A job that's scheduled to run every three hours, was running for more than 48hours. Average job run time, 1.5hrs.  Analysis : First and foremost thing to do, is to find out on what session id is the job running on the instance. You may use the below query to fetch the session id of the job from the job name. declare @Var1 varchar(36) select @Var1=substring(REPLACE(CAST(job_id AS VARCHAR(36)),'-',''),17,16) from sysjobs where name =' JOB_NAME ' select * from sys.sysprocesses where program_name like '%'+@Var1+'%' You may get a single row or multiple rows with

Following query can be used to search through all the job steps for any Keyword. USE [msdb] GO SELECT j.job_id, j.name, js.step_id, js.command, j.enabled FROM dbo.sysjobs j JOIN dbo.sysjobsteps js ON js.job_id = j.job_id WHERE js.command LIKE '%Keyword_Search%' GO

The below script can be used to search a keyword in any of the existing Stored Procedures or Functions on any database. SELECT SPECIFIC_NAME, ROUTINE_NAME, ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES where ROUTINE_DEFINITION like '%KEYWORD_SEARCH%'

You might see the following error while a package was newly imported and is set to be called from an SQL Agent through a job. The job would fail reporting the below error. Source:        Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error This is mainly because, the package protection level, which is by default set to  "EncryptWithSensitiveKey" Resolution: Set the package protection level to "DontSaveSensitive". But, if in case you know the password for the key, you could use it. How to change the SSIS Package Protection: In Business Intelligence Development Studio, open the Integration Services project that contains the package. Open the package in the SSIS designer. If the Properties window d

Please check the link for a list of Error States explained in detail for the Logon Errors 18456 on SQL Server. http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx These are for now, and will add to this if there are any more useful found.