Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tripodal
Constraint Violating Yak Guru
259 Posts |
 Posted - 2010-01-13 : 11:56:26
|
| I have a database with a single table, yesterday two values in this table were replaced with incorrect values."CFC0F166289D964B8D1D1D50626E4179" was changed to"AFC0F166289D964B8D1D1D50626E4179"and "hTbmHu8zi3"was changed to "hTbJHu8zi3"My developers are adamant this is hardware failure and I need to replace the sql server. This is rather convenient for them as they dont take any ownership of the cost or effort in doing it.I need to determine, if this is indeed a hardware problem, or user error. My inexperience tells me I need to use some kind of trace, and what I would like to know is what updates / inserts are made by what sql login.Also any advice / guidance on weather this seems like a hardware failure or other bug, is greatly appreciated.Tripodal |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-13 : 11:59:35
|
| where are these values stored? do you have any kind of audit info on your tables?SQL Server MVP |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-13 : 12:01:44
|
 hardware failure?I don't think so but who knows...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-13 : 12:08:27
|
If you are using Transaction Logging on your database (i.e. if the database Recovery Model is FULL and not SIMPLE) then you can restore the database to a point-in-time.I'm sure you won't want to restore the database itself, but you can restore the backups to a new, temporary, database.You could restore it to some time well before the incident, check the value of the columns in those two rows, and then continue restoring forwards in reasonable "time chunks" until you find when it changed.I'm with you that the developers are off their trolley and passing the buck. There is a slim chance that they are right, so we'll give them the benefit of the doubt. However, you might want to tell them that the cost to you of checking their theory is $X, and if it turns out they are wrong you'll be expecting them to foot the bill (or buy you lunch, whatever; Oh - plus a Pint for me, thanks  )It will be quite tedious stepping through the backups, so you will probably have to do it in stages. First stage restore and step through in quite large steps to establish roughly when the change occurred. Then for Stage 2 start the restore again, from the start of that time-section, and restore that period in slightly less granular steps, and if necessary repeat again.If you think the problem will happen again then you can start a "recording session" of all SQL activity, and then use that to determine when a particular change occurs. That's going to be like looking for a needle in a haystack though.You could also create a trigger on the table that created a "log record" (in another SQL table) whenever that table/column changed - plus the time, connection ID, etc. That might be a more focused way of finding the next occurrence. (Because the Trigger stores its logging data in a SQL Server database table, you will be able to just query that data the same way as any other)Good luck! |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2010-01-13 : 12:16:09
|
| I am GUI nerd, So my apologies for the poor question. :)I am not entirely sure of your definition of "audit info"But, I do not see any information that can be used as a verification of correct / incorrect data, or who may have modified it last. In the table itself. The correct information is stored in an alternate place in a flat txt file. This is how I can verify values have changed.An example row535,"_test","1234C4734E32F94CAAE88DF6AD338091","_test""ATempDB","hlu123449","ATempDBUser","DOLBY","ATempDB""hlu123449","ATempDBUser","DOLBY","ATempDB","hlu123449""ATempDBUser","DOLBY","ATempDB","hlu123449","ATempDBUser""DOLBY",False,False,False,False,False,FalseHopefully this is more helpful with the structure.CREATE TABLE [dbo].[master] ( [masterID] [int] IDENTITY (1, 1) NOT NULL , [SiteTitle] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [GUID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [URL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CartDSN] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CartPass] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CartUN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CartSERVER] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CalDSN] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CalPass] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CalUN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CalSERVER] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [LinksDSN] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [LinksPass] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [LinksUN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [LinksSERVER] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [NewsDSN] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [NewsPass] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [NewsUN] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [NewsSERVER] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Cart] [bit] NOT NULL , [Calendar] [bit] NOT NULL , [Links] [bit] NOT NULL , [bPages] [bit] NOT NULL , [bGallery] [bit] NOT NULL , [bNews] [bit] NOT NULL ) ON [PRIMARY] |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-13 : 12:23:10
|
| Could you re-edit your post to split up the example row please? (Its stretching the browser by being all-on-one-line, the Forum sotfware here is a bit old, and not smart enough to sort that out)This is in the SQL 2000 forum, do you have SQL 2000, or later?I ask because in later versions there is something called TORN PAGE DETECTION which (I think) will alert you if data (within SQL Servers data files) is changed outside SQL Server's control. So if something (e.g. hardware fault) messed with the data direct that would be detected.That would leave us with the only possible change being via SQL commands, and that would be either a user, or a user presenting some "real" data that got messed up by a hardware fault along the way.(I presume you say my post above? I mention it in case it crossed with yours such that you missed it) |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2010-01-13 : 12:26:01
|
Your welcome to my freezer, there is plenty to drink there. :) Have one for me as well.This is very helpful. I am fortunate in that the transaction logs for this DB are literally 72kb for days on end. So tracking down the moment is not hard at all.Setting up a trigger seems like a good use for triggers, since this table is modified perhaps 2-3 times a week. The exception is that it is read from perhaps 100,000 times a day? Maybe more. Will the trigger affect read performance?quote:
Originally posted by Kristen
If you are using Transaction Logging on your database (i.e. if the database Recovery Model is FULL and not SIMPLE) then you can restore the database to a point-in-time.I'm sure you won't want to restore the database itself, but you can restore the backups to a new, temporary, database.You could restore it to some time well before the incident, check the value of the columns in those two rows, and then continue restoring forwards in reasonable "time chunks" until you find when it changed.I'm with you that the developers are off their trolley and passing the buck. There is a slim chance that they are right, so we'll give them the benefit of the doubt. However, you might want to tell them that the cost to you of checking their theory is $X, and if it turns out they are wrong you'll be expecting them to foot the bill (or buy you lunch, whatever; Oh - plus a Pint for me, thanks )It will be quite tedious stepping through the backups, so you will probably have to do it in stages. First stage restore and step through in quite large steps to establish roughly when the change occurred. Then for Stage 2 start the restore again, from the start of that time-section, and restore that period in slightly less granular steps, and if necessary repeat again.If you think the problem will happen again then you can start a "recording session" of all SQL activity, and then use that to determine when a particular change occurs. That's going to be like looking for a needle in a haystack though.You could also create a trigger on the table that created a "log record" (in another SQL table) whenever that table/column changed - plus the time, connection ID, etc. That might be a more focused way of finding the next occurrence. (Because the Trigger stores its logging data in a SQL Server database table, you will be able to just query that data the same way as any other)Good luck!
|
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2010-01-13 : 12:27:13
|
Damn I am forgetting all my eqiquette, it is SQL2000 Sp4, and I split the lines for the sample row.quote:
Originally posted by Kristen
Could you re-edit your post to split up the example row please? (Its stretching the browser by being all-on-one-line, the Forum sotfware here is a bit old, and not smart enough to sort that out)This is in the SQL 2000 forum, do you have SQL 2000, or later?I ask because in later versions there is something called TORN PAGE DETECTION which (I think) will alert you if data (within SQL Servers data files) is changed outside SQL Server's control. So if something (e.g. hardware fault) messed with the data direct that would be detected.That would leave us with the only possible change being via SQL commands, and that would be either a user, or a user presenting some "real" data that got messed up by a hardware fault along the way.(I presume you say my post above? I mention it in case it crossed with yours such that you missed it)
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-13 : 12:28:11
|
quote:
Originally posted by tripodal
I am GUI nerd, So my apologies for the poor question. :)I am not entirely sure of your definition of "audit info"
by audit info i meant columns like last modified,modified by etc which give us an idea of who ( or which process) caused data update and when.i cant any such columns above though.SQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-13 : 13:09:12
|
| "Will the trigger affect read performance?"Novisakh16: You've quoted the long line now, darn-it! Go back any chop it up please!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-13 : 13:12:17
|
quote:
Originally posted by Kristen
"Will the trigger affect read performance?"Novisakh16: You've quoted the long line now, darn-it! Go back any chop it up please!!
ok now?SQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-13 : 13:40:51
|
| Thanks, all sorted. |
|
|
|
|
|
|
|
|
Sql Data Changed?