Solved

Is it the Disk Cache?

Posted on 2002-04-13
22
668 Views
Last Modified: 2012-05-04
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.
0
Comment
Question by:Chofo
  • 12
  • 5
  • 3
  • +2
22 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 6941460
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
 

Author Comment

by:Chofo
ID: 6942028
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6942093
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
 

Author Comment

by:Chofo
ID: 6942547
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6942647
>> 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
 

Author Comment

by:Chofo
ID: 6942904
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
 
LVL 35

Expert Comment

by:David Todd
ID: 6943989
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
 

Author Comment

by:Chofo
ID: 6944732
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
 
LVL 35

Expert Comment

by:David Todd
ID: 6949730
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
 

Author Comment

by:Chofo
ID: 6950485
David: I still loop through all the tables, what exactly does sp_MSForEachTable and how Do I use it?

Chofo
0
 
LVL 35

Expert Comment

by:David Todd
ID: 6952057
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Chofo
ID: 6953431
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
 

Author Comment

by:Chofo
ID: 6959619
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6959656
Can you post the full CREATE TABLE statement(s), including the index. trigger and constraint creation.
CHeers
0
 

Author Comment

by:Chofo
ID: 6959792
I'm e-mailing it to you.

Chofo
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6960014
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
 

Author Comment

by:Chofo
ID: 6960279
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
 

Expert Comment

by:gchavezr
ID: 6961644
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
 

Author Comment

by:Chofo
ID: 6962790
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
 
LVL 1

Expert Comment

by:solart
ID: 7138702
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
 

Author Comment

by:Chofo
ID: 7143576
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
 

Author Comment

by:Chofo
ID: 7163333
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now