Is it the Disk Cache?
Posted on 2002-04-13
I’m running MS SQL 7 on Windows NT 4 SP 6 on a Dual Pentium III system on motherboard GA-6VXDC7 with a Quantum Fireball lct 20 disk
The last few months I’ve been having trouble with my main database, which is about 400 MB. It runs normally for some days, then suddenly it corrupts one of its indexes (I do a simple query like SELECT * FROM MyTable WHERE PrimaryKey=nn to get an “out of time” error). I run DBCC CHECKDB (MyDatabase,REPAIR_ALLOW_DATA_LOSS) and up is again for the next few days…
It has had the same problem about 10 times now, and every time is a different table the one I can’t seek using an index (always a large table). I’ve had good luck for the time being, and no data has been lost, but I’m not sure what will happen the next time…
I’m going to try these two things:
* I’m going to make sure there’s no Disk Cache messing around. Here I need your help, because I’m not sure what to check (I already gave you all my hardware info)
* I’m going to make a new empty database and start from scratch to add tables, relationships and other database objects, and see that way if it was data corruption on the database. (And I still don’t know how to copy database diagrams, or get the Transact SQL instructions to re-create store procedures in the proper order so I don’t get the warning some sp wasn’t created when it should)
Can you help me make sure I won’t have any more trouble with my database? (any of these:)
- Explain to me exactly how to make sure there no Disk Cache bothering.
- Explain to me how to re-create a whole database step by step (taking into account there are foreign key restrictions, and all kind of objects: tables, views, sp, diagrams)
- Suggest another solution.