November 5, 2008 at 11:34 pm
Hi All,
I want to thank you all for this wonderful forum and I really learned several things from the problem/solutions posted here.
We are having a problem with our application database and the problem seems to be a little wired to me. We are using a database for our Telecommunication application. It has 40 tables. We have upgraded the database from SQL Server 2000 to SQL server 2005.We have just detached it from SQL Server 2000 and attached it to SQL Server 2005.
The problem we are facing now is whatever new records we saved to a Particular table , it getting saved for 1/2 days then getting deleted out of nowhere. Our application has is not deleting it and it does happening only to that particular table. Other tables are fine.
Can someone help to sort out why this is happening and how can I enable log to track events happening to that perticular table.
Thanks in advance. Any kind of information will be appreciated by me and let me know if you need anything to get this issue.
November 6, 2008 at 10:49 am
First I would look at any SQL Server Agent jobs and make sure that they are not doing the deletes.
Then you have a couple of options for tracking the deletes. A trigger on the affected table that logs the deletes and who is doing them or setting up a server-side trace with filters for activity on that specific table or database.
You should also check security. Who has delete rights on the table? Do they really need delete rights? This is also why I, and many other DBA's on this site, only allow access via stored procedures. Then no one has the ability to delete data outside the application.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 6, 2008 at 10:55 am
You can off course create a trigger for delete on that table to perform a rollback for delete statements and raise en error stating all connection info.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 6, 2008 at 11:07 am
follow the Jack instructions.
November 6, 2008 at 11:42 am
Dinendra Nath Dolai (11/5/2008)
The problem we are facing now is whatever new records we saved to a Particular table , it getting saved for 1/2 days then getting deleted out of nowhere. Our application has is not deleting it and it does happening only to that particular table. Other tables are fine.
SQL does not and will not randomly delete data on its own. If data is been deleted then some process is deleting it.
Is there perhaps a cascading delete to this table?
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
November 6, 2008 at 1:06 pm
Thanks for the instant replies. I am absolutely agree with GilaMonster. Some application is doing the deletion and what I have to do is to get some information which application is doing that.
However I will also follow Jack's and ALZDBA's suggestions to prevent the deletion of data and also to trace the source of deletion.
Thanks again and I will update you as per.
November 6, 2008 at 1:20 pm
starting a SQLTrace will also help to determine the source of your deletes.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 7, 2008 at 3:07 am
Hi,
just so i know - do you guys think it possible that an open transaction getting rolled back on the table might be a reason for this kind of delete to occur?
lets say someone opening a transaction but not comitting it, and then in the evening he/she shuts down his client - closing the connection and forcing the rollback?
karl
Best regards
karl
November 8, 2008 at 3:23 am
Karl Klingler (11/7/2008)
Hi,just so i know - do you guys think it possible that an open transaction getting rolled back on the table might be a reason for this kind of delete to occur?
No. If there's a transaction left open then the rows inserted would remain locked and anyone else trying to read them would be blocked (unless running read uncommitted isolation level). Also you'd notice growth of the transaction log because the log backups wouldn't be able to truncate the log due to the open transaction.
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply