Increase Job History

The SQL Server Agent by default only keeps 1000 rows of history for all jobs. There is also a limitation for each job to keep only 100 rows each. These rows do not only include the a row for the entire job, but the rows for each step in the job also. These rows can quickly get filled leaving you in the dark when you need to troubleshoot what had happened in a job. To change these values, you need to have sysadmin access to the SQL Server Agent. Within the object explorer, right click on the SQL Server Agent node, and choose properties. A new pop-up window will appear displaying the SQL Server Agent properties. Select the History node and the history settings are displayed: Here you have the option to not limit the agent job history, by unchecking the corresponding box. You can also choose the maximum # of rows to store for all jobs, and an option to limit each job. The last option is to Remove history that’s older than a specified time period. This would be recommended if you do not limit the history log size, since you will not be pruning any records otherwise. Continue reading ...


This command shows the last statement executed for a particular SPID. This statement is mostly used for troubleshooting purposes to determine the exact command a particular SPID is running. You must be a member of the sys admin fixed server role, or have VIEW SERVER STATE permission (if SQL 2005+). As for a real world use, I will mostly use this command after using sp_who2 to find out which SPID is taking a lot of resources. To execute, simply replace the SPID 55 below with the one you want to spy on. [cc lang=”sql”] DBCC INPUTBUFFER(55) [/cc] And here are the results: The results you are looking for are displayed in the third column above. This shows the last statement that has been or is currently being executed by the SPID. Another alternative to the DBCC INPUTBUFFER is to use the dm_exec_requests DMV. This query will show the currently executing statement for a SPID: [cc lang=”sql”] SELECT SQLStatement = SUBSTRING ( qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE er.statement_end_offset END – er.statement_start_offset)/2 ) FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt WHERE er.session_id = 54 [/cc] This DMV will only work on SQL 2005+ and you also need the VIEW SERVER STATE permission as a minimum. The one advantage DBCC INPUTBUFFER has over this, is the DMV will only show the currently executing request and not the previously executed request so you need to be a little quicker to catch it. The above DMV […]

Creating Unique Random Numbers

This is typically a hot topic and I’m going to try and tackle it with my rudimentary math. Ultimately given time and the frequency of generation, there is no such thing as a completely unique random number. There will always be some chance that a random number can be regenerated even though the chances do go way down when given a larger set of bytes. Using Numbers Let’s first look at probably the most random way to generate a random number. Using a function introduced in SQL 2008 called CRYPT_GEN_RANDOM(). This function takes a parameter of byte length, and returns a random hex based number within that range of bytes. We can easily convert this varbinary data to an integer based value. This function is based upon a low level windows API and is cryptographically secure. [cc lang=”sql”] SELECT CAST(CRYPT_GEN_RANDOM(8) AS bigint) [/cc] Using the method above returning an 8 byte random number and casting it as a bigint, your chances of repeating a duplicate are (roughly) between 1 and 15 billion. To break that out in terms of time, if you generated a random number every second you would without a doubt hit a duplicate random number between 31 and 47 years. However there’s no guarantee that it might not happen way before or way after however the chances go down the farther you move from the mean. Even though this may sound like a long time, the randomness that a bigint provides may not be enough for your […] Continue reading ...

Overcome Divide by Zero Using NULLIF

Anytime we are dividing we need to think of the divide by zero scenario. Even if you think you will never encounter this with your result set, it’s advisable to guard against it because when divide by zero is encountered, an error is thrown. The best method I’ve found to overcome this is by using the NULLIF function. This function takes two parameters and if they are equal, a NULL value is returned. Lets take a look at an example that throws a divide by zero error. [cc lang=”sql”] DECLARE @iter float; DECLARE @num float SET @num = 10; SET @iter = 5; WHILE @iter > -5 BEGIN SELECT @num / @iter SET @iter = @iter – 1 END [/cc] Running the following query, we see that once the variable @iter becomes zero, we receive an error. So the most elegant way to overcome this is to use NULLIF function and compare @iter to zero. When it does equal zero, it will instead change it to a null. And when dividing anything by NULL will equal a NULL. [cc lang=”sql”] DECLARE @iter float; DECLARE @num float; SET @num = 10; SET @iter = 5; WHILE @iter > -5 BEGIN SELECT @num / NULLIF(@iter,0); SET @iter = @iter – 1; END [/cc] This executes without error, however we still receive a null as a result. If you need otherwise, then you may want to wrap the equation in an ISNULL, to return a different value. [cc lang=”sql”] DECLARE @iter float; DECLARE […]

A Query Method Faster Than BETWEEN

This is an invaluable trick that can be sometimes be used instead of the BETWEEN operator. A common place this can be used is in an historical table that contains EffectiveFrom & EffectiveTo dates. In these kinds of tables, many historical versions of a record can be stored. Each record in this scenario will contain a Start & End Date which signifies the time span when a record is active. Because two records cannot be active at the same time, we can be sure that the Start & End dates will never overlap. While this describes one particular scenario for this example, there are many other situations this method can be used. Let’s look at the typical query we would use to find the record which is active for an employee at a specific point in time. [cc lang=”sql”] SELECT * FROM [HumanResources].[EmployeeDepartmentHistory] WHERE EmployeeID = 274 AND ‘2001-04-28’ BETWEEN StartDate AND EndDate [/cc] This query will return to us the single record that is active as of ‘2001-04-28’. However if you think about the constraints our table contains, we actually only need to query one column (the StartDate) in order to isolate this record. This is because the StartDate will not overlap any other StartDate & EndDate for this employee. Here’s an example: [cc lang=”sql”] SELECT TOP 1 * FROM [HumanResources].[EmployeeDepartmentHistory] WHERE EmployeeID = 274 AND StartDate >= ‘2001-04-28’ ORDER BY StartDate [/cc] Because only one record can be active for an employee at a single point in time, […] Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!