We have (running SQL 2012 Std) and have enabled trace flag 1204 and 1222 for capturing deadlock through extended events. I have enabled deadlock notification through email .But it only send deadlock event occurred notification from the sql server error log . I was wondering if its possible to email the deadlock details they get generated in extended events via DB mail.
View 1 RepliesI created a Blocking Event Notification following this article ->[URL=
How do I throttle the alerts so that I only receive one alert per hour instead of 1000?
I am trying to send out notifications when jobs complete (fail or succeed). I have database mail working fine on my DEV server, but I am having issues with it on my PROD server. I am currently having people look into if McAfee may be blocking it.
I am able to send out a test email from SSMS>Management>Database Mail, but when I set a Notification for a job, the job will complete and in the history, it will say "NOTE: Failed to notify 'User' via email."
I have created an Operator and set up Profiles and Accounts, just as I did on my DEV server.
I am learning Extended event to capture deadlock which already happened, for this in my SQL SERVER 2012.. I am simulating a deadlock . With [URL]... where given a query to find the deadlock details using extended event here is the code..Retrieve from Extended Events in 2012.
SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM ( SELECT XEvent.query('.') AS XEvent
FROM ( SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s
[code]...
This code creates a deadlock but when i run the above Extended events query to get the details of deadlock, it doesnot display any results.
Is there a way to send out an email woth deadlock information (victim query, winner query, process id's and resources on which the deadlock occurred) as soon as a deadlock occurs in a database or at instance level?I currently has trace flag 1222 turned on. And also created an alert that send me an email whenever a deadlock occurs. but it just says that a deadlock occurred and I log into sql server error log and review the information.
View 5 Replies View RelatedHi.
I'm developing an app that uses Service Broker queues to allow a customer to create "events" that fire using a timer or a query notification. When these events fire, a message is sent to a Service Broker queue for processing. Because there is much managed code involved in processing these messages, I decided to use the External Activator application and an Event Notification to process these messages. My question is "what is the difference between using the External Activator application to launch another application (which simply RECEIVEs a message from the target queue and processes it) and creating a windows service that simply monitors the target queue (with a WAITFOR = -1 clause) and processes it?"
I guess I'm not sure how using the QUEUE_ACTIVATION Event Notification is really helping me.
Thanks,
Chris
hi. can't seem to find the event notifications in the service broker folder of Management Studio.. I can get the information from sys.event_notifications, but it seems odd that event notifications are not in the UI. Hopefully I'm looking in the wrong place?
View 1 Replies View RelatedI'm looking for a sanity check. Service broker and Event Notifications are new to me, so it is possible I'm either abusing or incorrectly using the technology. I'm experimenting with using Event Notifications and Server Broker to log error messages. The process works pretty well as long as I don't do something stupid such as stressing the process by calling raiserror in a loop of a 100000 - twice. It processed for a while, but eventual either SQL Server terminates (sev 25) or I had to force the SQL service to stop by killing it's process via task manager. I could not connect to SQL Server or stop the service normally.
I have several general questions. I think they are good questions. Any thoughts or suggestions would be appreciated.
1) I'm looking into generating deadlock and blocking email alerts. It appears that Event Notification is the way to go since there are very few events and I can respond to the event with an email. Is there a better method to automate blocking and deadlock email alerts that include event details?
2) If I wanted to retain some history of errors for the developers, should Event Notifications be avoided if a high number of errors can be generated by a badly formed T-SQL? Avoid TRC_ERRORS_AND_WARNINGS and USER_ERROR_MESSAGE? (I could tell the developers not to be stupid, but why would that stop them if it does not stop me?)
3) Is there a way to efficiently filter a Event Notification from entering the queue before the Receive statement is called? I get some events that I throw away after receiving them. For example, perhaps I want all events from all non-system databases without having to add a notification for each single non-system database. Or I want all errors with severity 11-17 only?
4) Is there a trick to filter out events from the procedure activated by the event. I tried using raiserror to debug a procedure without thinking. The result was that the queue never was empty because the processing produced more events to process. As a result, I don't use raiserror and use a try-catch to avoid raising errors in the procedure activated.
5) I can receive one message at a time using local variables or receive a batch of messages using a local table. Is a small batch the best way even if there is memory pressure?
6) In the activated procedure I continue processing in a loop until there are no more messages. This seems to be the most efficient. Is this always the case? Should I exit the procedure after a set number (large) of messages have been received? The procedure would activate again to continue processing?
7) Is there any point to using the MAX_QUEUE_READERS setting when processing event notifications? Should it be 1?
8) I currently get the next conversation group id and process its messages within a transaction. Is this a bad idea with event notifications? Should I just call Receive and get the next batch? I don't really care if I lose some messages if things are going badly. Should I avoid wrapping the receive in a transaction?
9) I could run a trace that starts with SQL Server; however, I think my only choice is to log to a file. Is there a way to trace to a table using SQLTrace without running profiler? I would like to automate the process and have the data in a table so that it can be easily queried and parsed for each database/team.
10) Is there a way to fix my process and handle 100000 messages a minute? Is there a way to skip messages when it gets to busy? Can the query generating the messages get throttled - perhaps along with the query designer - before the server gets into trouble? Query the memory used by a queue and drop/create the queue with a delay if there is an issue?
I'm using a single CPU test machine with only 1 GB and 2 GB pagefile and a single disk. OS: Windows 2003 R2 SP2. SQL: 9.0.3054 and on 9.0.3228 (update package 6 just came out today). I could add memory, but I think that would just permit me to queue more message before running into trouble.
I'll add code shortly - length limit.
Some packages I develop have a lot of steps, and some people like myself can go crazy with email notifications, depending on the graph, I sometimes add notifications (success/fail) at the component level, so I can know what happened to that SSIS package from my blackberry and immediately log into the VPN and fix the issue.
The only problem that I have with that is that sometimes, like right now, I see myself adding 6 "send mail tasks" to SSIS packages, one pass/fail for each component.
Basically, I was wondering if anyone had any ideas or suggestions, ideally I wish I could create one single send mail task that could be reused by other components on the package and just update the message/subject through expression and variables.
Hi!
Just set up my first Service Broker test. Wanted to send an Event Notification from one instance to another. Finally, the message arrives but profiler shows that the message is still being continously resent (and rejected as being duplicate). What am I doing wrong?
With best regards,
Artus
--
/* Local Event Source.sql */
USE master
GO
CREATE DATABASE NotificationDB
GO
ALTER DATABASE NotificationDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 5554
)
FOR SERVICE_BROKER
(
AUTHENTICATION = WINDOWS,
ENCRYPTION = DISABLED
)
GO
USE NotificationDB
GO
CREATE EVENT NOTIFICATION TestEN
ON DATABASE
FOR CREATE_TABLE
TO SERVICE 'ExpressService', 'AFEDD339-AD3D-4865-AF3C-299B0A0784C6'
GO
CREATE ROUTE ExpressRoute
WITH SERVICE_NAME = 'ExpressService' ,
BROKER_INSTANCE = 'AFEDD339-AD3D-4865-AF3C-299B0A0784C6',
ADDRESS = 'TCP://localhost:5555'
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO
USE master
GO
SELECT service_broker_guid FROM sys.databases WHERE database_id = DB_ID()
GO
/* Remote Service.sql */
USE master
GO
CREATE DATABASE Test
GO
ALTER DATABASE Test SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 5555
)
FOR SERVICE_BROKER
(
AUTHENTICATION = WINDOWS,
ENCRYPTION = DISABLED
)
GO
USE Test
GO
CREATE QUEUE ExpressQueue
WITH STATUS = ON
GO
CREATE SERVICE ExpressService
ON QUEUE ExpressQueue
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO
GRANT SEND ON SERVICE::ExpressService
TO [public]
GO
CREATE ROUTE ExpressServiceRoute
WITH SERVICE_NAME = 'ExpressService',
BROKER_INSTANCE = 'AFEDD339-AD3D-4865-AF3C-299B0A0784C6',
ADDRESS = 'LOCAL'
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO
SELECT service_broker_guid FROM sys.databases WHERE database_id = DB_ID()
GO
SELECT * FROM sys.endpoints
GO
I have an activated SP that is issuing PRINT statements.
These are raised as ErrorLog and EventLog events when using SQLProfiler
However if you attach an event notification for ERRORLOG and EVENTLOG, nothing appears in the queue. Any other ERRORLOG or EVENTLOG event caught by profiler is caught by the event notification as expected.
In the notifications tab of the job I have e-mail operator selected. Click on the box with the elipses to enter the email addresses. It will let me enter 2 email addresses in the e-mail name field. It looks like the field has a limited length...is there a way to manually enter several addresses?
View 1 Replies View RelatedI need to develop a process by which customers are notified that their 1) order was received and 2) order was shipped.
We are running Sql Server 2008 and all customers and data are located here on various servers. It is in a Windows 7 environment.
Has ever utilized Sql Server Database Mail for this purpose? Is it feasible?
HI,
Does any one know how I could send all the alerts, email notifications from the SQL Server to my Sprint PCS Phone. Is there any software for that. I know that it can be done by MSN mobile. Just checking whether it can be directly sent to my phone rather than going to hotmail which is then forwared to my cell phone. Let me know.
Thanks,
Cinday
I have a stored procedure which will send an email notification everyday the count of names and count of logins at night. And this sends like a link so the users can directly click on the link(s) and see who all logged in. This works perfectly fine.
Now my requirement is that if there is no count for either name or logins it should send just as text and not as link.
Select count (name) from dbo.test=0 just plain 'text total count of names 0'
Select count (logins) from dbo.test1=0 just plain text 'total count of logins 0'
I am trying to convert the @runtime to hh/mm/ss format as it's currently printing milliseconds.
Declare @runtime varchar(100)
Select @runtime = sum(run_duration)
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
where j.name = 'Accounts_From_App03'
and run_date = CONVERT(varchar(8), getdate(), 112)
Declare @Body varchar(100)
Set @Body = 'The Job completed successfully. Duration:' + @runtime
exec master.dbo.sp_SQLNotify 'App02@email.com', 'team@email.com', 'Scheduled Job Success' ,@Body
We know we can use the event lock_deadlock and xml_deadlock_report to capture the deadlock info, however I also want to capture the execution plans for all of the SPIDs in the deadlock graph, how to output the execution plans to the extended events trace results either ? such as if there is an action for execution plan or workaround for it ?If there is no built in action for execution plan , may I know if we can add the customized info to the extended events results file also ? Such as when the deadlock related event happens , then we can run a query to get some info ,then added the info along with other info such as sql_text, dbname etc to the events trace results file either ? The reason is if we also know the execution plans when the deadlock happens, it is useful to turning the query based on the execution plans to reduce deadlock happening .
View 5 Replies View RelatedI'm using SQL with an Access form for the front end. Is there anyway to have an email sent on the trigger of an event within the form? I'm not sure where to even begin.
View 4 Replies View RelatedI am trying to set up the email notification system in SQL server 2000, but im having some problems. Mainly, im having problems with setting up an email account in the first place (there is a dedicated mail box, but the one im working with is a different box altogether).
If anyone could help fathom this out from start to finish i would be really grateful.
I have included the Send email task and sending email from SSIS package on Error event.
But for an error I am receiving multiple atleast 10-15 email generated. i got only 2 emails with revelant error message and other email gives message like "
Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown."
I have included ''@[System::ErrorDescription]' as an email attachment.
Is there any way to get only specific relevant error email.
Please suggest
All the recommendations I see from Microsoft docs is to limit the use of Query Notifications (QNs) to notifying connected clients when changes to mostly-static reference or configuration data occur, and to keep the number of overall query forms in play and connected clients to a minimum. Any way regarding a more integral use of QNs and Service Broker from a web app to notify n-web servers (farm) of an update to data that could be updated concurrently and quite frequently, or with a system where the technique is used extensively with lots of different query forms?
View 0 Replies View RelatedHi,
I need a help from you all on Sending Email on some specific event from SQL SERVER 2005. Any Idea how Do I proceed?
Any help will be appreciated.....
Thanks,
Rahul Jha
Hi All,
I need to send out email when error occurs in the package. Is it a good practice to put the send email task in the event handler? Then MaximumErrorCount is set to 1. But for some reason, some time I saw more than one email are sent out. Please advise. Thanks
I've setup a deadlock monitor using extended events like this.
CREATE EVENT SESSION [deadlock] ON SERVER
ADD EVENT sqlserver.lock_deadlock(
ACTION(package0.process_id,sqlserver.client_app_name,sqlserver.client_hostname,
sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle)
[Code] ....
Deadlock happened couple of days ago. I'm trying to determine the cause of deadlocks. What script should I use to pull that information to see what objects/processes caused deadlock?
I have enabled 1222 and 1204 trace and restarted sql server. then i have replicated deadlock in my local.
This is the error came in sql
Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Then i opened ERRORLOG file , there is no information about deadlocks .
Where can found deadlock information and file path. is there any extra options required to enable trace?
DBCC TRACESTATUS(1222,-1)
TraceFlagStatusGlobalSession
1222110
How to find location trace log?
I am getting deadlock in my production, i was taken deadlock information from trace file , i found deadlock graph but i am unable to find exact scenario . I am attaching deadlock trace file.
View 5 Replies View RelatedI have a weekly Maintenance Plan Reindex job that has failed because of a deadlock. My question seems simple enough and I'm ashamed to say I ought to know this answer, but here goes: Does the rest of a given job continue after such failures (this one was maybe 3/4 through the log) occur?
View 6 Replies View RelatedIn order to troubleshoot some deadlocking that I am seeing on SQL Server, I am trying to capture the Deadlock XML by enabling the Events Extraction Settings option 'Save Deadlock XML events separately' and specifying a Deadlock XML results file.
Meanwhile, I am also tracing the Deadlock graph, Lock:Deadlock, and Lock:Deadlock Chain events. Yet the xdl file remains empty even though I am getting hits on the events themselves in the SQL Profiler trace.
Also, I have the following trace flag settings enabled.
TraceFlagStatusGlobalSession
1204110
1222110
Why the xdl file remains empty even though (I think) it should contain some XML for deadlocks that are actually happening?
I am getting a number of deadlocks when inserting and deleting items from the same table.
The delete statement has a U lock and awaiting an IX lock on an index that covers the column in the where clause.
The insert statement has a IX lock and awaiting a U lock on the same index.
The delete statement is deleting about 5000 rows, where as the insert statement is inserting a single row.
Both these statements are found in stored procedures being called from LINQ to SQL.
I am wondering if there is a way I can prevent the delete statement taking the U lock out?My thinking being if the delete didn't take out the U lock then it would not deadlock with the insert. Are there any hints I could use to avoid the particular lock above?
I have seen various examples of multiple updates causing a deadlock, which can be fixed by adding multiple indexes. However, as I am inserting and deleting rows I imagine that all the indexes will need to be updated by both operations.
I have inherited the architecture and don't have the time to redesign everything at present. My backup plan is to deprioritize the delete and build in a retry mechanism.
However, it would be really good if I could find a more elegant way to handle deleting and inserting rows at the same time.
Deadlock trace information below...
11/02/2015 22:21:26,spid21s,Unknown,waiter id=process1cc9c68558 mode=IX requestType=wait
11/02/2015 22:21:26,spid21s,Unknown,waiter-list
11/02/2015 22:21:26,spid21s,Unknown,owner id=process203f31b498 mode=U
11/02/2015 22:21:26,spid21s,Unknown,owner-list
11/02/2015 22:21:26,spid21s,Unknown,pagelock fileid=1 pageid=721673 dbid=6 subresource=FULL objectname=PerforceReports_Staging.dbo.DebugReport id=lock1663f5d900 mode=U
[Code] ....
We have around 5 SP’s which are inserting data into Table A,and these will run in parallel.From the temp tables in the SP,data will be loaded to Table A. We are getting deadlock here.No Begin and End Transaction used in the stored procedure.
What could be done to avoid deadlock.
I have a deadlock trace running on a production server that is filling up with the following:
Lock: Deadlock Chain
Parallel query worker thread was involved in a deadlock SPID 8
105 - Resource Type Exchange
Spid 8 is a SQL process LOCK MONITOR
There are no graphs as seen with typical deadlock, so it is not two queries that are deadlocking, and I've never seen a deadlock for "LOCK MONITOR"
I set up a trigger to capture logins at the server level. This is part of some metrics I am putting together to properly set up load testing.
I am not surprised by the number of logins from the application logins, but what does surprise me is the number of logins from the various users that can query the production data.
As an example, my account has 1200+ logins for today only.
My first thought was that a process was set up to use my login by mistake. This also applies to some of the other users.But, there are no processes that are using any login except for the SQL service account or the applications logins.
What constitutes a login event?
[URL]
Hello, I receive this error "The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications." I attach the database in Management Studio to query and enable the broker using the scrip below but to no avail. ALTER DATABASE DataName SET ENABLE_BROKER ‘''<<------successfulandSELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name' ‘'''<<-------value is 1 Global.asax ... Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs) System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("dataConnectionString1").ConnectionString) End Sub...Web.config ... <connectionStrings> <add name="dataConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|jbp_data.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> <add name="ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>... Hope you could help. cheers,imperialx
View 1 Replies View Related