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 |
|
vbwizard
Starting Member
12 Posts |
 Posted - 2008-01-18 : 12:17:00
|
| Hi all,I found problem with my database and was wondering if anyone here could shed some light on the issue.I have two tables, Absences and AbsenceDates. The first one records the absence of an employee and the second one records a record for each day of the occurance. I do a full select on the second table and I see primary keys that do NOT exist in the select of the second table. so I dug further and here is what I found.Select * from Absences (rowcount in Query Analyser is: 20883)Select * from Absences Order By AbsenceID Desc (rowcount is 443)The second select contains the data that I am missing in the first select. So, I called a friend and they said to run DBCC CHECKDB and I did. The data came back as follows... DBCC results for 'Absences'.There are 21337 rows in 243 pages for object 'Absences'.CHECKDB found 0 allocation errors and 0 consistency errors in database 'EmployeeAbsenteeism'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.now if you add up the rows that the other two selects return it comes to 21326, not 21337. I am assuming that the value that DBCC gets is from sysobjects and that some sort of update would need to be run for it be accurate. This I don't care about too much, what I really need is for my main select statement to return ALL of the data, not just what it feels like returning.My experience is with programming mainly (6 years in .net) and not DBA, so any help would be greatly appreciated.Cheers,Brent**** UPDATE *****I tried running the following sqlselect * Into Absences1 From Absencesthe results were: (21337 row(s) affected)now, I did a select * from Absences1returned 20883 rows.. :(****** UPDATE ******* I ended up fixing the following way.Select * Into AbsencesFix WHERE PrimaryKey < 110577GOINSERT INTO AbsencesFix ({allfields}) (Select {allfields} FROM Absences WHERE PrimaryKey > 110577)Gosp_rename 'Absences', 'Absences_oldCorrupt'GOsp_rename 'AbsencesFix', 'Absences'GoThis little script did the trick, I can now select all the data in the table.Hope the fix that was given to me by another DBA friend of mine can help someone out who has a similar problem. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2008-01-18 : 13:39:45
|
| did checkdb return ANY errors?if all of this is true, and not a typo or mistake, then i'll bulk copy the data out, drop the table, recreate and bulk insert data back in.i'd also make sure my backups were handy...by the way, what version and SP level SQL Server? |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-20 : 00:31:52
|
| There maybe a row with bad data, check then in AbsenceID buckets. |
|
|
|
|
|
|
|
|
The msytery of the missing data...