Best Practices For Handling Deadlocks In Sql 2005 And Beyond
Jan 14, 2008
Greetings
I have a scenario where a table's after insert & after update triggers write to a history table. On this particular table, the history rows are quite small, so the occassional deadlock condition is inevitable.
In SQL Server 2000, I taught the application guys to handle these kinds of exceptions themselves, sleep a second, and retry. In 2005 (and I assume 2008) we now have TRY CATCH in T/SQL.
What are the pro's and con's of catching deadlocks in your T/SQL code (triggers or sprocs) .vs. the "old way" of catching it at the application level? I'm sure this has been discussed before, but search returns a lot of unrelated postings...
I'm thinking it's better to catch this at the application level, under the assumption that it's better to make the client(s) do the work than the database, but I'm sure there are considerations I'm not taking into account here. What experiences do you folks have to share on this?
Thanks!!
View 8 Replies
ADVERTISEMENT
Jan 3, 2008
Hello,
In general, with the introduction of schemas in 2005, is it considered "bad practice" to tell people to create new tables in the "dbo" schema?
Our product documentation contains a "quick start" guide for users who just want to get the product up and runing. We suggest that the customer creates a database for our application. This database is configured with a user that is assigned to the 'db_owner' role (we want to keep things as simple as possible) that we use to connect to the database. This database will only be used by our application. In this situtation is it okay to use the "dbo" schema, or should we consider creating another schmea for all our tables?
Are the any "best practices" for using schemas in SQL 2005?
Thanks,
Brenden.
View 7 Replies
View Related
May 17, 2007
I am re-posting this from the security Forum where it remains un-answered.
OK, Here's the set up.
I have a Windows 2003 box, soon to have SSL installed
On it is IIS 6.0, SQL 2005 Standard Edition (5Cal user lic)
SOON I'll have a prod enviornemnt where a web app being served by IIS is accessing SQL. I can go into SQL and set up a user account, call it MyAppSQLAcess, and code that into the connectionn string and lock it down to the tables/db it has access to. Or I can do it w/windows authentication, or I can do it a number of other ways, the question is this:
What is the best way for an asp.net app being server by IIS 6.0 to access data from SQL 2005 server when they are all on the same BOX? WRT Speed and security?
Thanks
Dan
View 2 Replies
View Related
Oct 16, 2007
I have just completed a default install of MS SQL Server 2005 x64 edition on a 1 processor Quad core, 8GB, Windows Server 2003 Standard x64 R2 machine in an Active Directory Domain. It has 2 arrays configured with 2) 36Gb drives mirrored for the OS as C: and 3) 147Gb drives in a RAID5 array as E: drive.
I am simply the server guy who was asked to install SQL server for some projects in the future. The company will be bring in people to to the dev.
I wanted to setup the server as best as I can and so I've been searching around for "best practices" for fresh installs.
Anyone have any suggestions on things to configure/setup to improve the performance of the default install?
In addition, I want to redirect the databases to the E: drive and separate them from the OS drive (C:). Does anyone have any information on doing this? I have searched and found a few articles, but thought I would ask first.
It is probably noticeable that I have limited SQL configuration skills.
Thanks,
A simple IT guy trying to build a system right
View 4 Replies
View Related
Jun 13, 2007
Does anybody have a link to either of these two documents. My company is getting ready to go through an audit and we need some firepower and to know what is expected. Any help with obtaining microsoft SQL Server 2005 best practices documents is appreciated.
-Kyle
View 3 Replies
View Related
May 9, 2007
Does anyone have any advice or useful experience configuring memory management for SQL Server 2000 and 2005 instances residing on the same box? I'm looking for advice on whether I should just completely leave it up to SQL Server and Windows to allocate memory or should I attempt to put ceilings on certain instances.
Any help is appreciated!
View 6 Replies
View Related
Dec 23, 2007
Hello all,
I'm finding the documentation online to upgrading from 2000 to 2005 fairly poor.
What are the upgrading options and which one is the best option for a web server database.
Also what are best practices pre-upgrade, during upgrade and post-upgrade.
Cannot find much online.
Also since this question will be asked often might as well make it a sticky.
Thanks
View 3 Replies
View Related
Aug 14, 2007
We have a live OLTP database for which we create full backups every week and differential backups every day. Recently we added an OLAP database, which we need to update daily with changes from the live database.
This is the process we are planning to use.
1. Restore last full OLTP backup.
2. Apply the last differential OLTP backup.
At this point we should have a replica of the live OLTP database.
3. Update OLAP database based on the OLTP replica database.
4. Delete the OLTP replica database.
Two questions.
1. If different from the process above, how is this OLTP-to-OLAP transformation typically done in the industry?
2. What is the best way to implement this process with SQL Server 2005?
Thanks.
View 3 Replies
View Related
Oct 8, 2007
I would like to know best practices for setting up my environment. To date, I've had everything running on a single server. That would include the database engine, SSIS, SSAS and SSRS. The box configuration is dual hyperthreaded 3.6GHz Xenon with 4GB of RAM on Windows Server 2003. I just received a much larger server and want to configure it to maximize our environment. The new box contains four 2.6GHz Quad Core processors with 16GB of RAM. I would like to know if I should split the ETL and database engine from SSAS and SSRS, or should this box have enough horse power to house it all and use my other box as a dev environment. Also, we are planning to purchase Performance Point 2007 primarily for PAS and Scorecard Manager so please take that into consideration as well. Any comments are greatly appreciated.
Thanks.
View 1 Replies
View Related
Nov 10, 2005
Microsoft recommends using Windows authentication instead of SQL Server authentication in SQL Server 2005 for improved security. What are the Microsoft best practices for implementing this? Will be helpful if someone also provides some links that talks about this....
View 5 Replies
View Related
Dec 12, 2006
I'm thinking of storing a lot of images, Word documetns, PDF, and emails as media in SQL Server 2005. Is it equiped to handle a lot of media? Or is it still preferred to store media separately on a file server?
View 6 Replies
View Related
Apr 4, 2008
We have several jobs that if any step fails, we need to continue with the next step. Due to this we have the both 'On Success Action' and 'On Failure Action' set to 'Go to the next step'. Our only issue is that when all the steps are done we still require the job itself to fail.
So my question is this, is there anyway the status of a job can be queried to determine if any of it's steps failed?
View 6 Replies
View Related
May 22, 2008
Re: Best Practices (security): Should SQL Server (2005) *not* be installed on the same physical HD as the Windows OS (Server 2003 R2) ?
Hi,
We're setting up some new servers, and today I'm looking into best practices for the SQL Server Setup portion of it.
The servers have include 2 x 250G HD, and from what I've read, where IIS is concerned, it should not be installed on the drive that has the OS on it, for security reasons. I was wondering if the installation of SQL Server should be on the non-OS drive as well ?
Thanx,
Barry O'Neill
View 6 Replies
View Related
Dec 13, 2007
I want to refer in a table quotes to customers and itemes from another table.
here is my table list in database:
"Customers"
"PriceList"
I want to create another table which through it I can create qoutes for customers suing a collection of items in PriceList table.
for example I want to be able to create something like this:
Title:
my quote example
QuoteID:
46543
Items (retrieved from PriceList table):
370
474
456
belongsTo (a relation to CustomerID in Cstomers table):
6234876
Discount, Date, WrittenBy, etc.
how many tables do I have to use for it, and what queries I have to write, and do I have to create any special relationships?
regards
View 9 Replies
View Related
Mar 17, 2005
Our 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?
View 8 Replies
View Related
Aug 13, 2002
Hi,
we have a production inviremont that is running for about 10 months. Since a couple of weeks we are having problems with "Deadlocks".
This cant be due to an increase in data size on the tables that are having the issues because these are cleaned in the same transaction that populates them.
These tables are used to store temporary data that the production system needs to calculate the correct price for any given order. This transaction takes between 0.5 to 1 second to commit.
We are running on a dual processor machine with 1 Gb of RAM with SQL Server 7 - sp 3, Windows NT 4 sp 6, Microsoft Transaction Server.
In all our queries and stored procedures we use the optimizer hints (nolock) for select statements and (rowlock) for updates or deletes.
Any help and/or suggestions would be appriciated.
View 2 Replies
View Related
Dec 17, 1998
Is there any way to totally avoid deadlocks. In some critical applications
we have removed transactions entirely, counting on other means to maintain
database consistency. We still get deadlocks in this area. These are mainly
inserts, and the only thing I can think is that updates to the indexes are
causing multiple page locks which result in deadlocks. Is this true?
Will deadlocks be eliminated in 7.0 with row level locking for this situation?
Or will index page splits still cause a possibility of deadlock contention?
Thanks!
ben
View 2 Replies
View Related
Mar 5, 2001
Hi ,
I have a problem with a SP in 6.5. When i try to run a Stored Proc which is a simple select statement dumped into a temp table in a particular database, I lock other users who are tring to log into other databases some in tempdb database. When i try to kill the process the rollback takes almost 45 mins or so..till then no one can log on to the server.
The SP works fine when no one is logged into the Great Plains server. One more thing i observed is that, the SP when run results on a deadlock only when the owner is a user. If the owner is DBO it works fine.
Can anybody throw some light on this.
Thanks in Advance
Siv
View 1 Replies
View Related
Jul 10, 2002
I am getting the following dead lock error message writtent to the Error Log.
How do i interpret this...?
2002-07-10 11:49:52.88 spid3 Node:1
2002-07-10 11:49:52.88 spid3 KEY: 6:1531868524:1 (1e0040209980) CleanCnt:1 Mode: X Flags: 0x0
2002-07-10 11:49:52.88 spid3 Grant List::
2002-07-10 11:49:52.88 spid3 Owner:0x26429de0 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:62 ECID:0
2002-07-10 11:49:52.88 spid3 SPID: 62 ECID: 0 Statement Type: INSERT Line #: 67
2002-07-10 11:49:52.88 spid3 Input Buf: RPC Event: sp_Save;1
2002-07-10 11:49:52.88 spid3 Requested By:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-S-S SPID:58 ECID:0 Ec:(0x29f534f8) Value:0x2649f0c0 Cost:(0/0)
2002-07-10 11:49:52.88 spid3
2002-07-10 11:49:52.88 spid3 Node:2
2002-07-10 11:49:52.88 spid3 KEY: 6:1695345104:1 (ffffffffffff) CleanCnt:1 Mode: Range-S-U Flags: 0x0
2002-07-10 11:49:52.88 spid3 Grant List::
2002-07-10 11:49:52.88 spid3 Owner:0x26450f20 Mode: Range-S-U Flg:0x0 Ref:1 Life:02000000 SPID:58 ECID:0
2002-07-10 11:49:52.88 spid3 SPID: 58 ECID: 0 Statement Type: INSERT Line #: 250
2002-07-10 11:49:52.88 spid3 Input Buf: RPC Event: sp_IPAQManagerFetchFilterDetail;1
2002-07-10 11:49:52.88 spid3 Requested By:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-Insert-Null SPID:62 ECID:0 Ec:(0x3bb5f4f8) Value:0x2649e040 Cost:(0/2340)
2002-07-10 11:49:52.88 spid3 Victim Resource Owner:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-S-S SPID:58 ECID:0 Ec:(0x29f534f8) Value:0x2649f0c0 Cost:(0/0)
View 1 Replies
View Related
Oct 27, 2004
Hi,
I've got a deadlock problem. The log below has been generated. The problem is that during one day, I have more than 300 deadlocks like it. Before, the were not so many deadlocks.
During past year, the number of users has grow (from 100 before to 500 or 700 now)
*** Deadlock Detected ***
- Requested by: SPID 360 ECID 0 Mode "S"
- Held by: SPID 113 ECID 0 Mode "S"
Index: aaaaa_PK
Table: TABLE_1
Database: MYDB
== Lock: KEY: 22:325576198:1 (ff009ae5078d)
- Requested by: SPID 113 ECID 0 Mode "S"
- Held by: SPID 374 ECID 0 Mode "X"
Index: aaaaa_PK
Table: TABLE_1
Database: MYDB
== Lock: KEY: 22:325576198:1 (ff009ae5078d)
- Requested by: SPID 374 ECID 0 Mode "IX"
- Held by: SPID 360 ECID 0 Mode "S"
Table: TABLE_2
Database: MYDB
== Lock: PAG: 22:1:2428
== Deadlock Lock participant information:
Input Buf: S E L E C T the_rest_of_the_query
SPID: 360 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 1
Input Buf: s p _ e x e c u t e 8
Input Buf: s p _ c u r s o r 8À B 8 8f ç @ Table I
Input Buf: S E L E C T the_rest_of_the_query
SPID: 360 ECID: 0 Statement Type: SELECT Line #: 1
== Session participant information:
== Deadlock Detected at:
==> Process 360 chosen as deadlock victim
I have done :
- rebuild indexes on all tables (fillfactor 90)
- analysed memory activity
Could a lack of memory be at the origin of the problem ? Which counters in perfmon are significant for memory lack ?
Could the index fill factor could be at the origin of the problem ? At time, it is at 90 percent.
Config : Winnt4 Server, MS-SQL 7 SP4 , 2 GB of RAM , 2 x Xeon 700
Thanks for any help.
View 4 Replies
View Related
Feb 16, 2004
Hi folks,
I have an application built on top of a questionable DB design which requires overcomplicated selects. The application is experiencing deadlocks regularly, in some cases with only one concurrent user.
I set the trace flag 1204 but am not seeing anything in the Error.log and I initiated a trace in profiler which does not seem to show any deadlock.
Despite having recreated the problem which show my browser hanging indefinitely. When I run the following queries:
SELECT spid, waittime, lastwaittype, waitresource
FROM master..sysprocesses
WHERE waittime > 10000
AND spid > 50
SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)
I get:
55860978LCK_M_XPAG: 13:1:2573
54AWAITING COMMANDsleeping sa 11499
55UPDATE sleeping sa 21499
respectively. Any help would be welcome.
Thanks in advance,
Don
View 9 Replies
View Related
Sep 16, 2007
hi,
We have a SQL 2005 transaction database server that suddenly started to issue deadlock errors last week on most of the databases on that server and a lot of timeout errors. Before that, that database server performed very well and timeouts were minimal to zero. I am not sure what changed for it to have these performance problems.
The only major change we did was to convert several varchar columns to nvarchar in several tables (as part of internationalization initiatives). We did not modify the procs from varchar to nvarchar though but would be doing that phase by phase.
There is also one proc in which we used the snapshot isolation level of sql server 2005. These are only 2 major changes done within the past 2 weeks. Would these be the cause for these deadlocks and timeouts on our web-based application?
Any ideas?
Thx
Sri
View 6 Replies
View Related
Jul 23, 2005
Hi EverybodyI am new to sqlserver 2000.I know basics of locks.but i dont know how toresolve deadlock issues.I am cofusing by reading articles with 90%information and remaining 10% missing.Can any one help me which is the goodsite to learn and resolve deadlocks.Note: I create deadlock. when i try to trace deadlock using dbcc traceon(1205,3604,-1).In error log showing nothing about the deadlock.showing created traceon.........Any help would be appreciated.--Message posted via http://www.sqlmonster.com
View 13 Replies
View Related
Jan 13, 2006
We have a problem with a table giving us deadlock issues and we can'tfigure out why.It's a table we write to fairly often perhaps 50 times a minute. Andalso do a select of 200 rows at a time from 4 servers every 5 minutes or so.We are only keeping 48 hours worth of rows in the table which averagesat 30000 a day on a busy day.This table has 1 PK and 2 FKs plus one TEXT column which does notparticipate in the WHERE clause.We are using binded variables.We have applied the latest patch to SQL2003 server running onWindows2003. The patch is supposed to resolve deadlock issues.Anyone have any advice on how to alleviate this problem.Thanks
View 5 Replies
View Related
Jan 17, 2008
Morning All,
Am getting the following error from a number of users and am sort of wondering where to start in terms of diagnosing the problem. If anyone could give me any pointers on where to start in diagnosing the issue I would be grateful.
"System.Data.SqlClient.SqlException: Transaction (Process ID 282) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
View 1 Replies
View Related
Jul 25, 2002
I know blocks and Deadlocks are different but how related are they? Seems like when I get reports of deadlocks I always have blocks and the blocks grow as time passes.
View 2 Replies
View Related
Oct 10, 2000
My database has been in production for 10 months with no deadlock problems. Three weeks ago I had to restore the database and I am now experiencing several deadlocks. SQL server is suppose to handle this and kill a process to resolve the deadlock but it is not. The deadlock occures on a stored procedure which is a simple select on a table with 187 records. DBCC ran with no problems. Any ideas what might be causing this? Any why is SQL not handling? Your help is greatly appreciated!
View 1 Replies
View Related
Aug 12, 1998
I need some help in reducing deadlocks in 6.5 I have tested with `Insert Row Locking` turned off and it reduced the number of deadlocks. What i need to know is if removing the foreign key relationships on tables reduces/eliminates Deadlocks. If any of you have any info on this please let me know.
Thanks
Kalyan
View 1 Replies
View Related
Feb 21, 2005
i have an issue regarding the following error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID ##) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
this is caused on a couple of ASP pages, which are using:
Code:
recordset.open "sql statement", connection, adOpenKeyset, adLockOptimistic
etc etc
recordset.addnew
etc
recordset.update
This is happening fairly frequently, which can't be a good thing, so would it be worth changing the lock or cursor types to a different one, in particular, adLockPessismistic?
Any pointers or comments would be very welcome.
This is happening on a windows server os, sql server 2000 using classic asp (.asp) pages, connecting to sql server using ado within the asp pages.
View 1 Replies
View Related
Apr 21, 2006
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
View 4 Replies
View Related
Mar 19, 2004
I am conducting stress testing for my website and keep getting deadlocks with the following message when one process is adding about 100 records per second and another process is trying to access the data:
Transaction (Process ID 499) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
What do I need to do in my stored procedures to avoid this? I only have ONE stored prcoedure that locks a row while incrementing an ID value. I am not doing any other locks, so is this a SQL Server system lock?
Any advise would be much appreciated. Thanks!
View 2 Replies
View Related
Apr 10, 2008
I have a search function, which searches across many tables.
It's a pretty heavy SPROC, I'm wondering in general, what are the best way to reduce deadlocks ? Its used a fair bit, and altho I haven't noticed problems with it myself, there are definately a decent amount of deadlocks showing up in the logfiles.
I've always assumed this is something really difficult, and avoided it like the plague.
Any tips are much appreciated !
thanks,
mike123
View 9 Replies
View Related
May 29, 2008
I'm going thru my application log, and just seeing what errors are popping up. I have a relatively intense search feature, thats causing alot of deadlocks.
Exception type: SqlException
Exception message: Transaction (Process ID 105) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
In general, what's the best way to resolve this ?
Should I see if I can apply "WITH (NOLOCK)" to my data ?
Any suggestions are greatly appreciated !
thanks again!
mike123
View 4 Replies
View Related