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.
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.
In 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?
SELECT '5' AS 'value/@version', 'database' AS 'value/@type', 'master' AS 'value/name', LTRIM(RTRIM(( [Server Name] ))) AS 'value/server', 'True' AS 'value/integratedSecurity', 15 AS 'value/connectionTimeout', 4096 AS 'value/packetSize', 'False' AS 'value/encrypted', 'True' AS 'value/selected', LTRIM(RTRIM(( [Server Name] ))) AS 'value/cserver' FROM dbo.RedGateServerList FOR XML PATH(''), ELEMENTS
I need to add some header information to the beginning of the query:
<?xml version="1.0" encoding="utf-16" standalone="yes"?><!-- SQL Multi Script 1 SQL Multi Script Version:1.1.0.34--><multiScriptApplication version="2" type="multiScriptApplication"><databaseLists type="List_databaseList" version="1">
Everything I have tried ends up as a failure, usually compile issues. My goal here is to be able to automare a configuration file for multiscript so I can keep my server list up to date.
Overall goal: Write a Bulk Insert statement using the UNC path of a filetable directory.
Issue: When using the UNC path of the filetable directory in a Bulk Insert Statement, receiving "Operating system error code 50(The request is not supported.)" Looking for confirmation as to whether this is truly not supported.
Environment: SQL Server 2012 Standard. Windows Server 2008 R2 Standard
I have a group of about 5 servers (which will likely grow toabout 25 in the near future) with their names listed in a table in a database on one of the servers. I want to query all servers in that table using the following query to pull the storage drive, database name, created date, age and size of the databases for each server listed in the table:
SELECT left(mf.Physical_Name,2) AS Storage_Drive, DB_NAME(mf.database_id) AS DatabaseName, db.create_Date, DateDiff(day, db.create_date, getDate()) Age, sum((mf.size*8))/1024 SizeMB
[Code] ...
How would I best accomplish this if I want to implement it using a TSQL procedure?
We have a hierarchical table of some 2-3k of rows that grows slowly at only 3-5 rows a month, and is never likely to be above 5k, holding file/directory names with an IdParent int value pointing at that items immediate parent or NULL if root
iterative code required to achieve the result I am looking for is best handled in the Application layer, but in this case the design brief is that we must get our results back as a View. Speed of execution and code clarity are secondary 'icing on the cake' issues.
I have tried recursive CTE's and XML FOR solutions but without success, and while I feel the latter is probably the best all rounder the searches I have made and the examples I have read do not seem to solve this particular issue.
Even to find the starting point of the search tree (i.e. the .EXE and the .CSV items) was difficult because the final slash is optional in some of the rows (.e. row 4)
The end result of my view on the above data should return:
Id FilePath 5 C:ProgramFilesExcel.EXE 6 D:DataFilesMyDataList.CSV (note the adding of the missing delimiter between DataFiles and MyDataList)
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?
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. Â My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.Â
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
I was wondering if there is a way to 'Move File' with the File System Task inside of a For Each Loop container but to dynamically set the Destination path variable.
Currently, this is what I have: FileDestinationPath variable - set to C:TestFiles FileSourcePath variable - set to C:TestFiles FileNameAndLocation variable - set to blank
For Each Loop Container €“ Iterates through a folder C:TestFiles that has .txt files in it with dates in the file name. Ex: Test_09142006.txt. Sets the file path (fully qualified) to the Variable Mapping FileNameAndLocation.
Script Task (within For Each Loop, first step) €“ Sets the FileDestinationPath to the correct dated folder within C:TestFiles. For example, if the text files I want to move are for the 14th of September, it takes FileDestinationPath and appends the date folder to the end of it. The text files have a date in the file name (test_09142006.txt) and I am picking this apart (from FileNameAndLocation in the For Each Loop) to get the folder date. (dts.Variables(€œUser::FileDestinationPath€?).Value = dts.Variables(€œUser::FileDestinationPath€?).Value & €œ€? Month & €œ_€? & Day & €œ_€? & Year & €œ€?) which gives me €œC:TestFiles 9_14_2006€?.
File System Task (within For Each Loop, second step) €“ This is where the action is supposed to occur. I want it to take the FileDestinationPath and move the FileNameAndLocation file (from the For Loop) into this folder for each run.
Now as for my problem. I want this package to run everyday but it has to set the FileDestinationPath variable dynamically according to that day€™s date. Basically, how do I get this to work since I can€™t hard code the destination path variable from the start? I have the DestinationVariable on the File System Task set to the FileDestinationPath variable, after the script task builds it. However, using FileNameAndLocation as the SourceVariable on my File System Task tells me that the €œVariable €œFileNameAndLocation€? is used as a source or destination and is empty.€?
Let me know if I need to clarify further€¦...I may be missing something very simple. Any help would be greatly appreciated!
I am running my package in sql server 2012, in which i am giving network path for flat file destination. And its working fine. But if i give m local path, its giving me  error " cannot open data file" ...
I 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?
I have an issue with a DTS package. We create a zip file and then attach it to emails going out using DTS. The problem is that the attachment, when received, is named using the full path to the file, so it is quite long.
Has anyone seen this before? Is there a way out of this?
I am considering mapping a drive to the share holding the file to be named, but the fact is this will shorten the name but will still result in the path being included.
I am wondering if this is a bug, as I suspect this isn't the default behaviour.
I experienced a weird error while deploying my SSIS package. After running the manifest file, i noticed that one of the configuration file's path was not updated in the dtsx file. My solution has 8 packages and almost every package has 2 configuration files. Except 1 file every other config file's path is being updated. Has anybody experieced such a problem?
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.
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.
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.
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.
I'm in the final stage of my asp.net project and one of the last things I need to do is add the following file information to my SQL server 2000 database when a file is uploaded:
First of all I have a resource table to which I need to add: - filename - file_path - file_size (the resource_id has a auto increment value)
so that should hopefully be straight forward when the file is uploaded. The next step is to reference the new resource_id in my module_resource table. My module resource table consists of: - resource_id (foreign key) - module_id (foreign key)
So, adding the module_id is easy enough as I can just get the value using Request.QueryString["module_id"]. The bit that I am unsure about is how to insert the new resource_id from the resource table into the module_resource table on file upload. How is this done? Using one table would solve the issue but I want one resource to be available to all modules - many to many relationship.
I cannot get the log file path read from the configuration.
If the path in the Connection Manager is invalid, package throws an error "SSIS logging provider has failed to open the log" instead of reading it from the config.file. What am I doing wrong?
Here is the portion of the config file. Everthing else is read from the config file correctly.
I'm trying to reverse engineer an XML output based on a client's need of a very specific format. Besides the issue of getting the XML declaration written in and removing NULLs, I'm having an issue with a three times nested PATH query.
So far the document almost has the correct format, except for the first nested root being returned. Is there any way to prevent this?
*some data redacted
Query:
DECLARE @a XML SET @a = (SELECT FileCreationDate, ( SELECT PCE_TPD.PJN, ( SELECT top (2) TaskCode, TaskName, RSI, TAFFD, AFD, Notes, PID, CUSID, NeedToBeNA
[Code] ....
Current Output (first few lines):
<TDBUIData xmlns="http://www.xxxxx"> <TDBUIData12 xmlns=""> --NEED TO ELIMINATE THIS <FileCreationDate>2014-10-15T23:23:00</FileCreationDate> <TDBUIDL> <PJN>MRWSH010824</PJN>
We have a job running the SSIS Package. I am working with that SSIS Package. The SSIS package has a script task. In the script task they used the variable of source path. I want to change the value of that variable (Need to modify the source path).
I modified the variable value in that package and in the config file but getting error message
Exception has been thrown by the target of an invocation.
We installed the SQL Server 2012 GUI tools, including "Upgrade Advisor," on a new virtual machine. We can connect to the old SQL Server 2008 R2 clustered databases just fine over port 1433 using SQL Server Management Studio 2012, but Upgrade Advisor is a mixed bag. Here are the steps we are taking:
- Click on "Start," "All Programs," "Microsoft SQL Server 2012," "SQL Server 2012 Upgrade Advisor"
- Fill in the server name with a valid SQL Server Network Name
- Select "SQL Server" as the only component to be analyzed
- Fill in the instance name with a valid named instance
- Use Windows Authentication (the default)
- The "SQL Server Parameters" window displays valid database names, meaning that we have a successful connection to the desired SQL Server instance at this point
- Leave all of the databased selected (the default)
- Click the "Run" button
- The "Upgrade Advisor Progress" window spins for a while and eventually generates the following error:
TITLE: mscorlib ------------------------------ The network path was not found. ------------------------------ BUTTONS: OK ------------------------------
Why would Upgrade Advisor be able to display the database names after making a successful connection only to fail later saying that it cannot find the network path?
I'm getting a rule check fail "Long path names to files on SQL Server Installation media failed" when installing SQL 2012 Standard edition from a network share.
declare @test as varchar(32) declare @test2 as varchar(32)
set @test='today''s problem' set @test2='my <string> '
select @test as '@attribute' for xml path ('myrow') select @test2 as '@attribute' for xml path ('myrow')
I want for xml path to correctly encode the single apostrophe as &apos but the single apostrophe doesn't get encoded. In the second example the greater and less than does get encoded.
I want to compare the filepath column in table with physical drive files and get the details of files which in table and not in physical and viceversa...
How do I dynamically change the file path for the source file in DTS? Can i use global variable?. if so , How do i use it in the connection task. Appreciate your help.
