Is it the Disk Cache?

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.
ChofoAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
I think you should read the fixlist for the SQL Server 7 service packs, and apply the service packs (ie SP2).

For the Disk Cache, you should read you Disk and Controller printed configuration, there it is indicated if you have some kind of Disk Caching.

To recreate the db structure, you could use either:
* a backup/restore procedure
* scripting the database creation

CHeers
0
 
ChofoAuthor Commented:
Angellll:A backup/restore procedure won't recreate the structure, it'll just copy it, and it's a whloe difference.
I already told you I knew how to script the database, so you're not being helpfull about this.
Besides, I gave you the disk and motherboard model, as I have already tried to 'read my Disk and Controller printed configuration' and found nothing.   That's why I'm asking.

Regarding Service Packs, I did forget to mention that I keep them updated.   Sorry about that, I forgot...

Unfortunately, your suggestions haven't been of much help.   Hope you can think of something else, or be more specific.

Chofo
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Sorry if I seemed unprecise:
The "Quantum Fireball lct 20 disk" is IDE, so it doesn't have any Disk Caching (anyway that would be mentionned explicitely in documentations:
http://help.nec-computers.com/uk/pri/item_spec_hdd_lct20.asp

It's great that you keep your service packs upgraded, so that eliminates already most of the common problems.

You say you have a 400MB database, which isn't very large, so normally corruption shouldn't happen very often. How many records do your large tables have? Do you have TEXT or IMAGE data types? Could you send me the database (zipped, of course without sensitive data :-)

Note that it is current that data consistency checks (DBCC) are run eventually daily to prevent data corruption...

To recreate a database, you CAN use a restore (restore over existing database). Regarding the diagrams, that would be the sole possibilty, if at all, see here:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=5453
In the Diagrams feature of SQL Server Enter- prise Manager, can I export a diagram to a document or file that I can distribute to others?
No, but Microsoft is planning this functionality for a future release.


Regarding the scripting, you need to manually sort the stored procedures if they are dependant of each other.
To automate, you would need to get the scripts depending on the sysdependencies tables... If you want, I could try to build such a script.

Cheers




0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ChofoAuthor Commented:
AngelIII: Thank you for your fast answer, it’s wonderful to get a helping hand when in need…

Let’s see: about the hard, I still didn’t find anything, cache just isn’t a subject (I could infer from that Cache is NOT present, but I was hoping to read it in black and white)   Same answer for Mother board’s built in IDE controller.

SP3 is installed, no news there.

No, I can’t send you the whole database, I have a very poor Internet connection.  I could try sending an empty structure…
Most tables have about 100 000 records, just 3 of them have 500 000, and there are many table with less than 50 records.   All the tables that have failed at the moment have no TEXT fields, nor IMAGE data types.   It’s really annoying, because this shouldn’t be hanging… Can you think of a reason?

I don’t think I could manage to run DBCC every day…

I’ve restored many times a back up over an existing database, what I meant is that internally SQL doesn’t actually re-create the database, it takes a shortcut.   I wanted to force it to add each record, one by one, regenerating each index step by step, so I can be sure corruption isn’t present in the database (at least not the same corruption!)
If you manage to create that script, it’ll help me a lot.   I’m not used to create transact SQL scripts, and it takes me ages to know how to reference some system objects.

It’s a pity about those diagrams, I guess I will have to remake them all one by one.

Thank you very much for your help.

Chofo
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>> I don’t think I could manage to run DBCC every day…
You can automate that in a job, you might look into the Database maintenance Plans that can take off your head that "implementation".

Now, for the script, I have bad news... Not that i coulnd't write it, but this afternoon I was told to "visit" for the rest of the week for business purposes :-(

Maybe it would a nice thing for you do to this:
here is the idea:
# build a "temporary" table (procid , done). Note that it cannot be a # or ## temp table, as cursor (see below) wouldn't work. Of course, with some while loop, cursor can be avoided...
# insert the id of all your stored procedures
# then, run a cursor on that table left joined to the sysdependencies, checking for other stored procs which are NOT done. If there are none, use exec sp_helptext 'procname' to dump the "create procedure" script.
# update the "temp" table for those procedures that dumped.
# loop until you "did" all the procs...

Just a hint for the "restore": you could dump (bcp) all your tables, this would reload all the records.
The indexes however should be rebuild after a restore or full load (check out DBCC commands to rebuild all the indexes of a table...)
Add the usage of the exec sp_MSForEachTable to generate a script to do that for all your tables :-)


CHeers
0
 
ChofoAuthor Commented:
Every time I have to run DBCC CHECKDB (MyDatabase,REPAIR_ALLOW_DATA_LOSS)  I have to kill all the running processes, change database to unique user, run DBCC, change again to multiple users.   That isn’t easy neither proper to automate.
I DID program an every day reindexing, but that didn’t help (Database still kept hanging)

Regarding the script, I catch the idea.   Problem is I’m not used to work with metadata, so it makes me read a lot before writing the first line of code.   Anyway, I’ll give it a try.

I would have liked a different answer, something I hadn’t seen…
Ok, let’s wait a day or two for a different opinion, if it doesn’t come up, points are yours.

Chofo
0
 
David ToddSenior DBACommented:
Hi,

Just a silly idea but are the databases on NTFS partitions rather than FAT? and when were they last defragmented?

I was surprised when doing some work on a new SBS 2000 server how much fragmentation has on the data drive ...

Regards
David
0
 
ChofoAuthor Commented:
David: all right, that's new!
Yes, partition is NTFS and no, I haven't defragmented it for more than a year now...
As I have no defragmenter, I will backup database to another drive, format the partition and restore the backup.
I'll need 10 or more days to tell if it worked...

Chofo
0
 
David ToddSenior DBACommented:
Hi,

And don't forget to run a dbcc dbreindex on all tables after doing the restore ... and an update statistics.

I used to use a cursor to loop through all tables in my database until someone pointed out there is a sp_MSForEachTable that isn't documented ...

Regards
  David
0
 
ChofoAuthor Commented:
David: I still loop through all the tables, what exactly does sp_MSForEachTable and how Do I use it?

Chofo
0
 
David ToddSenior DBACommented:
Hi,

I forget who produced the code sample and I didn't test it.

I can't get it to work for a DBCC.

I got the following to work ...
sp_MSForEachTable 'select top 5 * from ?'

after looking at the stored procedure using
sp_helptext sp_MSForEachTable

But this does work ...

declare @TableName     sysname

declare c_Tables cursor for
select Table_Name
from information_schema.tables
where Table_Type = 'Base Table'

open c_Tables
fetch next from c_Tables into @TableName
while @@fetch_status = 0 begin
     print @TableName
     dbcc dbreindex( @TableName, '', 90 )

     --declare @SQLStr     nvarchar( 2000 )
     --set @SQLStr = N' Update Statistics ' + @TableName
     --execute( @SQLStr )
     execute( N' Update Statistics ' + @TableName )

     fetch next from c_Tables into @TableName

end
close c_Tables
deallocate c_Tables

And since sp_MSForEachTable uses a cursor internally, the only difference is the code you have to write ...

Regards
  David
0
 
ChofoAuthor Commented:
David: thank you for your code, but I already had it.   What I didn't know was how to simplify it using sp_MSForEachTable, but since it's the same...

Thanks anyway,

Chofo
0
 
ChofoAuthor Commented:
David: it wasn't fragmentation...
I formated the disk, restored the database, and it worked for 5 days;  and then it broke again.
It must be something else.
It's always a problem with indexes of large tables.
Hope someone helps me hit the nail!

Chofo
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Can you post the full CREATE TABLE statement(s), including the index. trigger and constraint creation.
CHeers
0
 
ChofoAuthor Commented:
I'm e-mailing it to you.

Chofo
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Thanks, i got the file.
Now, I couldn't see anything that MUST be the problem, but I would like to suggest the following:
* create a second filegroup, and place all the tables that contain fields of type [text] to that filegroup.
* avoid to use [text] as much as possible, you certainly know that for nvarchar the max size is 4000 (SQL7+).
* For large and table with higher load I would reduce the FillFactor for indexes (primary key uses an index implicitely), and also place the indexes on another disk (if possible), using the filegroup feature.



Other notes:
* i have seen nvarchar(1) NOT NULL?!
* using filegroups gives you more flexibility when implementing backup and restore, can help to improve performance by making I/O faster. Finally, a broken file will not break the whole database, if only the index filegroup break, instead of restoring, just recreate (you should have the scripts ready ...


Continuing looking...
0
 
ChofoAuthor Commented:
Angel: you are far ahead from me!
I don't have a clue on how to create a different file group for indexes (I read about it a long time ago, but never implemented it, I will have to study!)
With text fields, I'll try to change them all to nvarchar.   Nobody takes the time to write more than 4000 characters...
I'll keep you informed, thanks again,

Chofo
0
 
gchavezrCommented:
Hi Chofo:

Please see this URL

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=19989

it has an article called:

Checking a Disk Controller's Write Cache

Hope that does it usefull for you.

Regards
0
 
ChofoAuthor Commented:
gchavezr: Thank you very much for your link, I really enjoyed performing the test.  Although the Cache issue had been cleared by AngelIII a few messages above.   But you're message was reassuring, thanks again!
0
 
solartCommented:
A shot in the dark.

Use Enterprise Manager, select the database, right click the database and select properties from the drop down.

This will pop-up a window, select the "options" tab.

Under the "settings" section, check to see if "auto-shrink" is checked or unchecked.  

If its CHECKED, then uncheck it.

Run your DBCC DBCHECK to verify all is well.

Now run for a few days and see what happens.

Hope this is it?

solart
0
 
ChofoAuthor Commented:
Solart: sorry, auto-shrink is already unchecked (I think it has never been checked), so it must be another thing.

AngelIII: I did the changes you suggested, but I'm still having problems every now and then, It's not very frequent (every 2 weeks) but  it still happens...
I never split the database, I have only one filegroup, I was hoping to save me the mess;   guess I'll have to dirt my hands eventually!
Chofo
0
 
ChofoAuthor Commented:
Ok, I've given up...
It still hungs every once in a while.  I should try AngelIII last suggestion, but it's not quite the right moment.  So, I'm giving him the answer, I think he got closer to my problem.
Sorry it took me this much time to anser...
0
All Courses

From novice to tech pro — start learning today.