Find SQL Agent Job name based on Activity Monitor

Posted on by
Reply

This is an article that I had never planned until today, late in the evening  when I was browsing through the forums at SQLServerCentral. I happened to see a thread where the user who posted the question was facing trouble identifying the name of the SQL Agent Job. You can visit the thread by clicking here. (Not required if you have actually been redirected from that thread).

Problem

When I initially looked at the post, I thought this is not a big deal and suggested to use Job Activity Monitor to look at the Active Jobs. What happenned next was made me sit for couple of hours on a Friday night to solve the problem, that eventually made in me write this article. The user replied back saying, what if there are multiple Jobs concurrently running? How can I identify which individual record in the activity monitor corresponds to a particular Job, since Activity Monitor displays the Job’s hexadecimal code instead of displaying the Job name.

I started to query the sys.sysprocesses and the sysjobs tables from Master and MSDB databases. From the first glance at the Name column of the processes and the Job_Id column of Msdb.dbo.sysjobs. After close observations of a particular Job records, I could find that the hexadecimal values in the program_name column of the sys.sysprocesses and the Job_Id column in Msdb.dbo.sysjobs are nothing but a rearrangement of the first 20 characters, the last 12 characters are same (at least from my observations until now).

When I could figure out the order of the characters, all I had to do was reshuffle those and compare, the complete script of the stored procedure is available below.

Use the following script to create the stored Procedure that is required to fetch the SQL Job Name.

When you next time identify in Activity Monitor that a Job Step is executing for more than the desired amount of time or using more system resources, pick that spid and execute the command as shown below.

EXEC dbo.FETCH_ACTIVE_JOB_NAME @SPID = 60

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru to know when there is new content.

–Bru Medishetty

Enable AWE Option in SQL Server

Posted on by
1

In this article we will look at the steps to enable AWE Option in SQL Server. When enabled, the Advanced Windowing Extensions gives the ability to SQL Server to utilize Memory more than 4 GB. AWE enabling for SQL Server is valid in 32 Bit Systems only, on 64 bit systems AWE is not required to be enabled.

Steps to enable AWE

In the SSMS, right click on the SQL Instance in Object Explorer, and select properties. the following Properties Dialog Box is displayed.

In the left Pane of the Box, Select the Memory Page and Under Server Memory options check the box as shown in the picture below to use AWE to allocate memory. You can also specify a Minimum and Maximum Memory SQL Server can utilize on the machine. So that incase of Memory available to SQL Server it does not complete utilize and resulting in no or less memory to the Operating System and other application running on the Server.

 

Select OK in the Properties page. The change in the setting will not be effective upon restarting SQL Server.

Alternately, you can also enable using sp_configure. The following scripts provide the alternate steps to
perform to enable AWE option.

As always, I suggest referring other technical information before enabling AWE and other system settings that need to be enabled on the Server for this to be successful, such as enabling /3GB switch or /pae switch in Boot.ini etc.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru to know when there is new content.

–Bru Medishetty

Cursors in SQL Server

Posted on by
Reply

Cursors in SQL Server enable you to work with each of records in a record set, so that you can sequentially work with those records and carry out the required task.

The best example that I can recollect to quote here is when you have a table with 100 rows, with one of the columns as email address. You are required to send an email to all valid records in that column and each record is some way or the other has its own condition, then a Cursor would be a good way to implement this.

What cursor does is, it enables you to fetch the data into the memory and then for each of the row in the data set, you can look at the row, can implement any custom logic that is needed and move on to the next row until you reach the end of the records.

There are different types of cursors available and based on the one you choose, you can actually move back and forth with the records.

One other example I can give is, when you need to run a backup for all the databases in a SQL Instance, it is very easy to implement using cursors and has been used by me and many SQL DBA’s before the introduction of the DTS and SSIS technologies.

That is the main benefit of cursors, but the flip side to it is, they are avoided in situations where the dataset is large, due to performance issues with cursors. Since they have this unique ability to traverse through the dataset, they occupy a high amount of memory in the Server.

You should be able to find many examples on this topic and I shall try to publish an article with more details in one of my next blogs.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru to know when there is new content.

–Bru Medishetty