Epic Blog of Awesome

code.tech.sci.math.art.write

Recovering a SQL script

First, save your scripts.

My box crashed and I had an unsaved script in Query Analyzer I’d been working on for a couple hours. When I rebooted and brought Mgt Studio back up, there was no message about “recovered files.” I then looked in C:\Documents and Settings\YourUsername\My Documents\SQL Server Management Studio\Backup Files\ but alas, no files.

I found when searching this SQL statement and it actually worked. It searches the server’s execution plan cache and returned my code in full from the last time it was executed. Just thought I’d share in case you find yourself in the same position someday.

SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
where execsql.text like ‘%MyTable%’
ORDER BY execquery.last_execution_time DESC

(Note I remembered that my query hit the MyTable table in my DB and that seemed like a decent thing to search on, and it was)

Using Visual Studio database projects in real life – Gunnar Peipman’s ASP.NET blog

Visual Studio database projects are good to support software development. I have successfully used database projects for years and I think it’s time to share my experiences also to global developers audience. In this posting I will introduce you how to effectively use database projects so developers are working with up-to-date schema and test data all the time.

via Using Visual Studio database projects in real life – Gunnar Peipman's ASP.NET blog.

sql Records: Converting a delimited list to a table using T-SQL

There is a common problem of converting a delimited list of values to a table format to join to other tables within a database. For example, a session database might have a stored procedure that accepts two parameters: a a SessionID and b a comma-delimited list of attributes to retrieve. Below are 6 techniques to accomplish this within SQL Server and a 7th which essentially pushes the processing to the application tier.

via sql Records: Converting a delimited list to a table using T-SQL.

Enabling Email Alerts for SQL Server Deadlocks | Practical SQL Server

Of course, setting up alerts for deadlocks should be pretty easy, right? All you have to do is go into the SQL Server Agent, set up an alert for Error 1205, set up who to email/alert when it happens, and you’re done, right?Well, unfortunately, setting up alerts for deadlocks isn’t that simple. For starters, while Error 1205 is the most common error number associated with a deadlock, Errors 1211 and 3928 are also associated with deadlocks within SQL Server though Error 3928 is reserved for a particularly nasty/non-typical issue relating to deadlocks. So. There’s that issue for starters i.e., if you want to be thorough, you’ll have to set up alerts for all three error numbers.

via Enabling Email Alerts for SQL Server Deadlocks | Practical SQL Server.

Answer : limit sql agent job resource with resource governor

limit sql agent job resource with resource governor

Hi! I am trying to limit resource used by SQL agent job and its not working
for me. When the job kicks off, for some reason the process its not going to
the workload group that I have defined and it goes to the default workload
group.

Run the job. While the job is running, look at sys.dm_exec_sessions. You
will see the program_name column (which correlates to APP_NAME() in the
classifier function) has something like:

TSQL JobStep (Job 0XCDFA…)

So now your classifier function can say (don’t forget to replace the
0XCDFA… part with what you actually observe above):

DECLARE @j BINARY(16) = CONVERT(BINARY(16), 0XCDFA…);

IF APP_NAME() LIKE ‘%’ + CONVERT(VARCHAR(36), @j, 1) + ‘%’
SET @workload_group_name = ‘BackupAndDataCompression_Group’;

This will work as long as you don’t drop and re-create the job.

Complete classifier function example:

USE MASTER
GO
CREATE FUNCTION [dbo].[rgclassifier_HeadcountRefresh]() RETURNS
sysname
WITH SCHEMABINDING
AS
 BEGIN
 DECLARE @workload_group_name AS sysname
 DECLARE @j BINARY(16) = CONVERT(BINARY(16), 0xB2E92732843AB94EB01D3CF5CC5FC1F6);
IF APP_NAME() LIKE '%' + CONVERT(VARCHAR(36), @j, 1) + '%'
 SET @workload_group_name = 'BackgroundJobs';
RETURN @workload_group_name
 END;
GO

via Answer : limit sql agent job resource with resource governor.