Lock:Deadlock Chain
Exchange
1
16325
2006-04-21 09:20:18.560
Parallel query worker thread was involved in a deadlock
0
Is the process I am running deadlocking with Exchange Server?
The data above is from Profiler.
Thanks
Lystra
Hi list,
I'd be grateful for a little advice. Running into some problems and not sure where to start. I've looked through some books and articles and Online Help but need some insight into the issue from a person.
I'm running a SQL2K server with about 30 databases, none of them over 20M in size. These db's are driving websites on servers in the same server room and also accessed remotely using Enterprise Manager. For 95% of the time these databases are performing well. Then connections on webpages start timing out. This is a problem that 'fixes' itself very quickly but I need to identify what's causing the problems. I have sa access to the server and am trying to find a way, via perfomance monitoring or enterprise manager, to work out which database, if any, is responsible for this behaviour. There are no blocks visible in Current Activity on Enterprise Manager, I'm getting average LockRequest/sec of about 2000 bit with peaks up to 31000. Processor time is averaging out at about 10%, not too scary.
I can find lots of information on optimising individual databases and queries within them but no too much on identifying processes or queries that might be hitting a whole server installation in this fashion.
Any advice most welcome.
Jo
Hello,
I have tables/columns exist.How to encrypt database or mask columns if I have to send a copy of DB to microsoft to troubleshoot for a problem.
Thanks.
I need help. I have a SSIS package that connects to a ODBC database, it then does ETL from that database into a SQL 2005 database. I run the package using a Sql Agent job each night. However the package fails randomly with no apparent reason. I have setup logging and after viewing the logs I am no closer to solving the problem. Any Ideas how I can track this down? Thanks.
View 16 Replies View RelatedThis error is triggered by the following code. How do I figure out why?
It works fine on an old server running win2000 but blows up on win2003
Microsoft VBScript runtime error '800a01a8'
Object required: 'Param1.value'
/mmd/_ScriptLibrary/getcons.asp, line 19
set conn = session("conn")
' Response.write(pg);
set cmdStoredProc = Server.CreateObject("ADODB.Command")
set cmdStoredProc.ActiveConnection = conn
cmdStoredProc.CommandText = "sa.mmd.getConsultantList"
cmdStoredProc.CommandType = adCmdStoredProc
set Param1 = cmdStoredProc.CreateParameter("conam",adVarChar,adParamInput,3)
cmdStoredProc.Parameters.Append Param1
Param1.value = request.querystring("q")
Param1.size = Param1.value.length
set rs = cmdStoredProc.Execute
Hello guys, i am new to sql so forgive me if dont see something in the code that i should. Anyway the code below comes from the sql script that belongs to "BPBlog asp blogging software" ...its not mine.
Whenever i run this code in query analyzer from sql server 2000 i get an error that says :
Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '('.
*line 16 is the below line in the code.
i have been going through books online since last night to try to get an understanding of the syntax of all those keywords that sorround the error but still havent figured it out yet. any advise on the error would be sincerely appreciated.
Thanx,
USE [Blog]
GO
/****** Object: Table [dbo].[tblAuthor] Script Date: 11/07/2006 10:51:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblAuthor](
[fldAuthorID] [int] IDENTITY(1,1) NOT NULL,
[fldAuthorUsername] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fldAuthorRealName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fldAuthorEmail] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fldAuthorWebsite] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fldAuthorBlurb] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fldAuthorPassword] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Approved] [smallint] NULL DEFAULT ((0)),
[fldAdmin] [smallint] NULL DEFAULT ((0)),
CONSTRAINT [aaaaatblAuthor_PK] PRIMARY KEY NONCLUSTERED
(
[fldAuthorID] ASC
)
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Does anyone have any good places where I can get some practice scenarios for DBA activity? Also any transact sql puzzles to solve for practice purposes. I want to get as much "real world" activity under my belt as possible in a quick time-frame.
Thanks.
Hi, we just started receiving the following error messages about 3weeks ago. We don't get them all the time, or even on the same pagesnecessarily, but we get them from time to time and our sql serverconnection is slower in the last three weeks also. Our database isremotely hosted, and the programmers are also offsite, so we are tryingto deal with this issue with our database server host, with noknowledge of sql server. They say nothing has changed on the server,but we haven't changed any of our front end files either, so we don'tknow what the problem is. Here's 3 of the most common errors:1. Microsoft Cursor Engine error '80004005'Data provider or other service returned an E_FAIL status..../subview.asp, line 1322. Microsoft OLE DB Provider for SQL Server error '80004005'TDS buffer length too large.../subview.asp, line 283. Microsoft OLE DB Provider for SQL Server error '80004005'Unknown token received from SQL Server.../members.asp, line 182We have *no idea* what the above error messages refer to, or whetherthis is something wrong with the server or the ASP pages mentioned. Anyhelp anyone could provide so we can understand the types of messages weare getting would be nice.Regards,Sage
View 1 Replies View RelatedCan anyone help me troubleshoot this failed job. The error message in eventviewer is:SQL Server Scheduled Job 'Job1' (0xB1D83B7357CF2B4C808AD1CFA34CE8F9) -Status: Failed - Invoked on: 2005-05-12 09:31:38 - Message: The job failed.The Job was invoked by User sa. The last step to run was step 1 (Job1).The job step is:EXEC proc1I can execute the procedure in QA.Proc1 does the following:INSERT values into a table in the database where the job is running fromSELECT records from a seperate database on seperate serverI'm using four part naming in the select against a linked server. I suspectthat the issue is the security between the different servers/databases but Idon't know where to get more information about the failed job or what to trynext.thanks
View 1 Replies View RelatedHi,This is the first time I use SOAP. Dont really know the exact pictureyet. Hope you can shed some lights on me.May I know where should I start troubleshooting if I get the followinerror message when running SOAP on Windows 2003, SQL Server 2000 ?Error Code: 0 Error Source= Microsoft VBScript runtime error ErrorDescription: ActiveX component can't create object:'MSSOAP.SOAPClient' Error on Line 1. The step failed.Thanks.
View 2 Replies View RelatedWe have some transactional replication jobs from oracle to mssql2005. I found an error in the replication monitor as below,
============================================
Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax. (Source: MSSQLServer, Error number: 1018)
Get help: http://help/1018
Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x00000000000000000D4300000000, Command ID: 26290)
============================================
I found the corresponding table from the command id. Since some of the tables in our env. are created with triggers which cause the replication error occasionally! How can I trace the information of the transaction/record which made the replication pending? Also, there may be 'invalid' input in oracle and replicate to mssql2005 which cause error in trigger and made the replication panding. Any way to solve this kind of problem? Thanks in advance
SQL Server 7.0 with SP 1
Database maintenance job is failing
with error about being unable to delete old backup file.
I can manually delete the file with no problem.
This occurs randomly, maybe once a week.
Any ideas on what's wrong, and how to fix it?
Hi all,
This one is a real X FIle, just without Mulder, Scully or the Lone Gunmen!
I have a database, to which access must be restricted via a sole application. So, I have to use an application role. I go in the database and run these statements to add and activate the roles, respectively;
Exec sp_addapprole 'Sirius', 'password'
(The system confirms the role is created.)
Exec sp_setapprole 'Sirius', 'password' 'odbc'
(The system confirms the role is activated.)
Right, now I should not be able to connect using anything but this role, agreed? But here's where things go wrong. I can then successfully connect from another computer by using MS query from Excel, from a login that is not even a member of the Public Role!
I tried again, started and stopped the Server/DTS/Agent services and dropped the old role after each successful login before recreating it. I've checked my syntax exhaustively. I must be doing something wrong, or overlooking something, otherwise MS has a major security problem! (Just hope the Cancer Man doesn't find out!!)
Thanks in advance everyone,
Jaishel.
Has anyone seen a document on "How to
troubleshoot Client Connectivity for
SqlServer 2000" ? Thanks in advance.
I am having one DTS job failed but it has not written much in the history. i wont to know the exact cause of the failure.
please help..
thanks in advance
Hi all:
I'm new to replication but I have already set up replication and have seen it
working and failing and have gotten myself out of jams so far but there must
be an easy way to administer it when things don't replicate as expected. I'm
finding that I could easily kill half a day just trying to dig up
information leading to troubleshooting tips. Is there documentation just on
managing this feature. The regular MS Administrator's guide doesn't offer much.
Currently I have a problem that if replication fails on one command I get a
SQL Mail telling me of the problem but does replication continue to the next
command or does it just stop until the problem is fixed? I'm finding that I am
constantly checking the publisher and subscriber databases and verifying if
replication is indeed doing what the msjob_commands table reports. I set the
batch to commit after each transaction instead of every 100.
Help...
Hi all. I'm new to this forum and looking for some assistance.
I've run into a unique (for me) performance problem.
I have a select statement that performs fine ( < 1 second ) using one set of values in the criteria but very poorly ( > 3 minutes )using different values. In both circumstances the query returns zero rows. The query involves a parent-child join with the criteria spread across both tables.
The execution plan looks similar between the two; the difference being a few percentage points difference on some of the operations. The tuning advisor has no recommendation in case 1 but suggests a couple of additional indexes and 4 statistics in case 2.
My gut tells me that the solution is *not* applying the additional indexes/statistics but some other issue. Or it could be the sushi I just ate.
Anyway, I'm hoping someone can point me in the right direction as what to analyze to determine why simply changing a single supplied criteria value would have such a dramatic effect on performance.
I have a handful of jobs that I need to troubleshoot, but this one will hopefully give me enough insight to do the rest myself. I need to figure out why a Maintenance Plan that performs a Diff Backup on 4 databases, and then a transaction log back up on the 4 databases is failing. I just get the following error message, and I cannot tell what failed.
Executed as user: <SERVER_NAME>SYSTEM. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:20:05 AM
Progress: 2008-01-03 11:20:05.95
Source: {SOME_STUFF_WAS_HERE}
Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete
End Progress
Progress: 2008-01-03 11:20:06.35
Source: Back Up Database (Differential)
Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete
End Progress
Progress: 2008-01-03 11:20:06.35
Source: Back Up Database (Differential)
Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete
End Progress
Progress: 2008-01-03 11:20:06.37
Source: Back Up Database (Differential)
Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete
End Progress
Progress: 2008-01-03 11:20:06.37
Source: Back Up Database (Differential) ... The package execution fa... The step failed.
How can I find out how to fix this problem? And please let me know if I am doing something wrong. (For instance, perhaps those two tasks shouldn't be together.)
- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/
I don't know if the title for the subject is appropriate here, anywayhere goes:This process was set up by someone and I have inherited it. I have asql2000 database that has about 13 tables that get populated with datafrom 3 different databases. I have identified where each of this datacomes from, and the stored procedures that do the updates, inserts, anddeletes, and the jobs that run these stored procedure to do theupdates, except for one table. The updates for all the other tablesare done through scheduled jobs. For the one table I know where thedata comes from and the stored procedure that needs to run to do theupdate on the table, however I have not been able to identify theprocess that runs the stored procedure.I am hoping that someone can give me a clue as to how to find out wherea stored procedure is being used - or any other hint as to how I couldgo about finding out how this table gets updated.ThanksKR
View 2 Replies View RelatedHi all
My problem is, as I suppose, simple.
I am working on OS XP Professional SP 2, 512 MB RAM, 80 GB HDD. I also installed IIS 5.1
I installed SQL Server 2005 Express edition with Advanced Services including Reporting Services.
In some video I saw that I should configure Reporting Services on my own after instalation, so I did.
Then I started Reporting Services Configuration Manager, and made my own configuration. I called Virtual Directory MyReports.
When I started Internet Explorer with URL:
http:\localhostMyReports I received a message like "HTTP:1.1 500 Internal Server Error"
How to properly configure IIS and Reporting Services?
Reporting Services is working in Reporting Services Configuration Manager
Please help me out
I can't connect to any instance of MSDE on my laptop using my web app. I duplicate the whole process on another local machine and have no problems. I'm using DNN, but it's not a problem with a connection string.
Over the last week I've had to rebuild this laptop - reformating and all. I'm running win xp pro, vs'03. I've tried this on msde2000sp3a & msde2000rela. Everthing is fully patched, updated, and current.
I'm working on two pcs at the same time with identical environments, doing one step on one keyboard and then the other:
I take one set of fresh DNN files and install them on each pc.
create a new db, user with dbo rights.
create a new virtual directory in iis.
alter the dotnetnuke.sln, dotnetnuke.vbproj.webinfo to reflect the path to the local site.
alter the web.config file with the connection string.
browse to the website (this is how dnn launches, creating the db from scripts, etc.)
At this point my desktop pc works great and I've got a new instance of DNN.
My laptop does launch dnn, but it can't connect to the db and says there's a problem with the connection string - but the only difference in the strings is the instance name - which isn't spelled wrong!!!!
So I'm led to believe that there is something wrong with the setup on the laptop which was just rebuilt.
It's got .net 1.1, msde2000sp3a, winxppro sp1. I've uninstalled and reinstalled all the components I can think of.
What would you do??? I'm completely out of ideas on this.
Do I format the hd and start again? That would hurt, but I've tried everthing else I can think of.
Oh, here's the other rub. I can create a system dsn, test the connection - and it works! I know that point's toward dnn, but like I've said I believe I've ruled that out.
Thanks for any advice...
Hi --
I am a new user of SQL Server, Enterprise Manager, and Reporting Services (all three are installed on my comptuer) and have a problem. Please bear with me, I am very new at this and don't know all the correct terminology!
Someone in our IT group "discovered" that I had two "instances" of SQL on my machine. The way he came to this was that I was trying to use the "." to connect to my local machine in Query Analyzer and it wasn't pulling the databases I had saved to my computer, it was looking in another folder LOCALDB (a subset of my computer's name). He was unsure how to move all of my databases on my comptuer to that LOCALDB folder so he uninstalled the "LOCALDB" entry from the Add/Remove Programs screen to fix this. He said "No to All" when it prompted him to remove shared entries.
Consequently, when I load Enterprise Manager now, I get the "Snap-In failed to initialize" message. I also do not have any SQL Server options in my Start Menu Anymore. Query Analyzer still exists, though, I had saved a shortcut to my taskbar and I can still launch it from there. He is saying all I need is to re-install the "Client Tools" folder from the CD and that will fix the problem. I am skeptical, since it appears to be a larger issue (SQL isn't in my Start Menu anymore!).
I am sure that my SQL install is probably completely corrupt. I would like to return to full functionality of SQL Server, Enterprise Manager, and MRS as soon as possible. Can anyone begin to advise me on how to do so?
I really do appreciate your help in advance.
jzafereo
Hi
I have sucessfully set up transactional replication, allowing the subscriber to update the publisher. All works well for a while, but after a couple of weeks or so it fails, but always for a different reason !
My question is, is there anything that can be done to help replication stay healthy. I had thought of doing regular backups of the database and the transaction log, and then truncating the transaction log.
Any advice, or links to other troubleshooting resource much appreciated.
Hello!
We have setup up transactional replication with dedicated distributor in SQL Server 2005 environment. I have noticed that during particular time of the day latency is increasing dramatically. I have been checking Tracer Tokens and Total latency during that time is around 30-40 min (both publisher to distributor and distributor to subscriber is taking much longer that normal). Normally, it is less than 10 sec. I was wondering if there is a way to pinpoint exact cause of the latency. This is pull subscription.
I would appreciate if someone can share (or point to the right direction) best practice on transactional replication setup/maintenance. My understanding is that only committed transactions are replicated, correct? I checked database on publisher and didn't see any outstanding long running transaction.
Any help is greatly appreciated.
Thanks,
Igor
This issue just happen recently. The buffer cache ratio went from > 90%to 50% and has slowly been climbing back up over 8 hours or so. Itscurrently @ 76%. Is this something I should take action on immediately?It seems to be coming back to normal...
View 1 Replies View RelatedIs there some way of troubleshooting errors when using the reportviewer control (with no reportserver).? Specifically, I am dynamically loading supreports and their ascociated datasets. I get an "Error: Subreport can not be shown." in the report viewer display panel at run time. In the past when I have gotten them it was a matter of randomly trying things until I hit on the problem. I can trace it to the point where I load the dataset for the subreport and all seems correct. There are no report parameters to be passed so I know its not that. I've double checked the subreportname,datasource and dataset names and they are all okay.
Any help would be greatly appreciated.
The primary database i'm responsible for has started to grow super fast. Every couple of days is growing by 10% (which matches with the db settings). But, the recent growth doesn't match with the historical growth. It took a couple of months to grow from 7 to 8 GB, but it has grown to about 24 Gb in the last 2 months. Bottom line - trust my assertion that it's growing alarming fast.
I need help determine what objects are fueling the growth. If I know the objects, I can probably determine the cause. From a flip-side, it might be legit data stored very poorly. I'm open to any ideas...but I need to get ahead of this problem in the next week or so...or I'm going to run out of room on the hard drive and could start to affect my users.
Please send my any ideas you might have.
Thanks,
alex8675
One of our developers has a Microsoft Access 2000 database that runs queries that compare the Access db data to a SQL Server database. He uses pass through queries to get the data from SQL Server.
We're finding that the Access query runs quickly against our test server, even with copies of production data, but when we try the same query against our production server, the CPU on the local computer running Access is pegged and the query takes up to 10 minutes to run.
First I verified that the SQL Server structures between test and production were identical, including indexes. I checked index fragmentation, and productions indexes are less fragmented than tests. Again, test and production currently have the identical data.
I've run a profiler trace on our production SQL Server 2000 server, and I see the RPC for the query from Access running almost instantaneously.
Any ideas on what might be the cause of the difference in speed between test and production SQL Server servers, or any suggestions on other things I could look at/tools I could use to troubleshoot this issue further?
Hi Everyone:
I have a SSIS Package which in brief moves data from one SQL data store to another. On my local machine, the package executes fines, and does what it is supposed to do, by moving data from Point A to Point B. I have both Point A(DB) and Point B(DB) on my local machine. But when I deploy this package over to ASSEMBLY which is basically another environment, I get weird intermittent errors. What should I do? Can someone give me some tips on how to do good error logging in SSIS? I am currently writing to Windows Event Log, and I seem to look at some errors that are coming thru. What else can I do on SSIS Package side, to improve error reporting and handling, so troubleshooting is faster and more effective. I just need some stable advice on how to setup my package to do error logging where troubleshooting issues in different environments is more effective. Also I need to know, what could be causing these issues? Thanks.
MA
I am losing data from time to time and can not figure out where the rows are vanishing to. In essence, I have 5 files that I process. The processing occurs on a daily basis by one SQLAgent job that calls 5 individual packages, each doing a different piece of work.
I stage each file to a temp table, then perform some minor transformations, then union the rowsets from each file before landing to another temporary table. A subsequent package performs additional transformations that apply to the entire dataset, before inserting them to their final destination. Along the way, I reject some records based on the business rules applied. Each package in the entire Job is logging(OnError, TaskFailed, Pre and Post execute). Their are no errors being generated. No rows are being rejected to my reject tables either.
Without getting into the specific transforms, etc. being used in this complex process, has anyone seen similar unexplained behaviour? I've not been able to identify any pattern, except that it is usually only 1 or 2 of the record types (specific to a source file), that will ever not be loaded. No patterns around volumes for specific source files. There are some lookups and dedupes, however I've seen the records 'drop off' before reaching these transforms. I have noticed I had my final desination load not using fast load. However sometimes the records disappear before even getting to my final staging table which is inserting using fast load. I am going to turn on logging the pipelinerowssent event. Any other suggestions for troubleshooting/tracking down these disappearing records?
Thanks
We are hosting a 140 GB database on SQL Server Version 7 and Windows2000 Advanced Server on an 8-cpu box connected to a 15K rpm RAID 5SAN, with 4 GB of RAM (only 2 GB of which seem to be visible to theOS) and a 4 GB swap file. (The PeopleSoft CIS application will notpermit us to upgrade to SQL 2K.) We recently upgraded the server from4 to 8 cpus and the SAN disks from 10K to 15K drives. But we stillhave heavy SAN disk usage, sometimes at 100%, and read queues oftenaveraging 4 and peaking at 12.The CPUs are loaded at only 20-50%. (The politics are such that it iseasier to throw hardware at the problems.)We are looking into archiving, converting from RAID 5 to RAID 10, andat splitting the mdf file into several file groups in an attempt toget more disk heads into play. (We are also looking at rewriting theapplication to reduce the read volume and frequency.) Does anyone haveany other ideas?Incidentally, does swapfile get used when the physical memory equalsthe OS maximum? If the OS can only see 2 GB and we have 2 GB (actually4 GB) of memory, is the 4GB local swap file on the C drive unused?Thanks in advance for any assistance.
View 1 Replies View RelatedOur system is reasonably complex with a lot of non-trivial stored procedures. As the load on our DB increased we're now getting more and more deadlocks (10 per day or so from about a million stored proc executions).
We try to avoid transactions where we can, and we do attempt to optimse stored procs to steer clear of deadlock conditions, but with the sheer number of stored procedures we can't possibly avoid all deadlock conditions.
One solution I'm considering is to re-run stored procs that failed because of a deadlock. In the .net code we'll run the stored proc, check for a deadlock error and if one happened, wait 100ms and try again.
What do you guys think?