January 15, 2010 at 1:30 pm
Gail provides some very good points. She provided a link about Dirty Reads, you should read that article. Tuning queries, indexes, and database design should be the approach in this situation. The client knows that locking is stopping some of their processes. So the client asks for a quick fix to the problem. What the client really wants is the problem fixed without repercussion. It is best to explain to the client what should be done instead of paying for a band-aid now and then paying to have it fixed again and again and again.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 18, 2010 at 7:05 am
I would recommend what Gail has stated. Fix the long running queries. Once that is done you could also look at using Row level version. I assume you are running SQL 2005. Then you are sure to get the last committed data when ever you do a query.
-Roy
January 19, 2010 at 10:11 pm
Hello,
Thank-you for the replies and sorry for my late reply.
I finally got the answer from the client.
Just to let you know, I have told them the risks of killing a process to unlock the locks and it's totally not recommended for all the problems expected to be raise, they still want us to implement this function.
They said that they are aware of DB inconsistencies caused by killing a process but they still wanna do it.
At the moment, the running application has a function that transfers db from a server to another server. I'm guessing this is where the lock is occurring and is implemented by SSIS package.
So tuning query is not exactly what I can do as there are no queries.
I will definitely look into the package tho.
January 20, 2010 at 6:39 am
Be sure to get the client's decision in writing before proceeding. You sure don't want this coming back to bite you in the end if something goes horribly wrong - IMO.
-- You can't be late until you show up.
January 20, 2010 at 6:55 am
idiescreaming (1/19/2010)
They said that they are aware of DB inconsistencies caused by killing a process but they still wanna do it.
Killing a process will not cause database inconsistencies. SQL will fully roll back any transactions in process. If the client has properly implemented transaction handling (unlikely from the sound of thing), then anything in process will completely roll back
At the moment, the running application has a function that transfers db from a server to another server. I'm guessing this is where the lock is occurring and is implemented by SSIS package.
What's wrong with backup/restore? Or replication?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply