SELECT OBJECT_NAMEobject_id FROM sys.sql_modules WHERE OBJECTPROPERTYobject_id, ‘IsProcedure’ = 1 AND definition LIKE ‘%Foo%’
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)
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.
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.
How I can move table to another filegroup?
This blog post will have running account of the all the blog post I will be doing in this month related to SQL Server Wait Types and Wait Queues.
Let us see how to find and delete duplicate rows from a table which does not have a Primary Key.
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.
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
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
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
Updatable Subscriptions for Transactional Replication