July 19, 2004 at 10:51 am
The problem is as follows:
I have a database table that gets updated. The steps are as follows:
1 - the indexes get dropped
2 - the data gets bulk inserted
3 - the indexes get re-created.
More often than not, when the indexes are re-created, the server re-boots (that's the Windows server, not just SQL server).
The table isn't huge - it's ~ 1.5 million lines.
There are three indexes and they're created using a stored procedure. The stored procedure is as follows:
SET NOCOUNT ON
CREATE CLUSTERED INDEX accountProducts_accountName_and_supplierId_and_productCodeIndex ON accountProducts (accountName, supplierId, productCode, descriptionChunksAccountCode)
CREATE INDEX accountProducts_accountProductIdIndex ON accountProducts (accountProductId)
CREATE INDEX accountProducts_accountName_and_sortPriorityIndex ON accountProducts (accountName, sortPriority)
SET NOCOUNT OFF
I've determined that it's not one particular index that will crash the system - sometimes the clustered index works and it falls over on the next index, or the third index, or it might survive all three. It really is not predictable whether it will fail, which index it will fail on, or how far through creating a particular index it will fail.
SQL Server leaves no log files to indicate a problem. Windows is set to do a full memory dump on system failure; it doesn't manage to do this.
I'm running SQL Server 2000 sp3a running on Windows 2000 sp3 (+ all available security patches). The server is a RAID 5+1 server, running dual 2.8 GHz Xeon processors and 0.5 TeraBytes of disk (~300 GB free at the moment). The disk is regularly defragmented. The database is set with auto-shrink off (it was on, but we wondered whether it was a growth problem). The database is also now set to grow by up to 50% if required.
I have backed up the database and put it on an "identical" server - same results.
I have also run all the diagnostic DBCC routines I can find. All report the database to be in perfect health.
Any suggestions?
Griff
July 19, 2004 at 1:00 pm
Do you have any SQLDump created in Log folder? You may contact Microsoft SQL Server support to setup SQL Server dump and memory dump from Windows system.
July 20, 2004 at 4:14 am
Just a thought. It might be writing to the log but the server reboot will mean the update to the log file may not have been written to disk. Perhaps change the log file to a shared folder on another PC (just temporarily).
Because the problem transferred to a different windows server it sounds like the problem is probably a corrupt database. Do a dbcc checkdb on the database and tempdb.
Regards
Peter
July 20, 2004 at 6:00 am
Allen & Peter
There is no SQLDump file written anywhere. As mentioned, the OS doesn't even get the chance to do a memory.dmp.
I have run almost all of the available DBCC routines against the specific database and the tempdb [including checkdb, checktable(table), checkFileGroup, checkIdent(table), checkCatalog, showContig and checkAlloc], all of which report that the two databases are in perfect health. If I remember correctly, the TEMPDB is re-built everytime that the server re-boots.
I'll play around with the log file settings and see if I can trap anything.
July 20, 2004 at 7:48 am
Try to drop the three indices one at a time when rebuilding. Monitor the behavior of the system. You should be able to identify which index (or indexing) is causing the problem.
Justin
July 20, 2004 at 8:35 am
Justin
We have one stored procedure that has 3 lines of code in it to drop the three indexes.
We then import the data and then run a third stored procedure that has three lines of code in it to re-build the three indexes.
I've got it to re-build the indexes one at a time.
It turns out that sometimes it crashes on the first index, sometimes it survives that and crashes on the third second index, sometime it crashes on the third index and sometimes it happily generates all three indexes.
When it does crash, it could crash at any time during the re-build process. To my mind, it appears to be completely un-predictable.
Griff
July 20, 2004 at 9:01 am
I don't know how much data you are bulk loading each time, but rebuilding the indexes is probably causing the database to grow. Check the database growth percentage is large enough for what you need.
Peter
July 20, 2004 at 11:34 am
Peter
We've had this database and the TEMPDB both set to increase by 50% (both data and log files) and auto-shrink has been disabled. Unfortunately, to no avail.
Current statistics for database is as follows:
Data file size = 4084 MB (1447 MB free)
Log file size = 133 MB (118 MB free)
For the specific table that the data is imported into:
Rows = 14274273
Size = 5032992 KB
Index Size = 624432 KB
Griff
July 21, 2004 at 3:09 pm
Are the indexes on the same file group as the data?
Are the indexes on a separate filegoup on a different drive?
July 21, 2004 at 10:38 pm
We use a single file group on a single drive (well, a RAID 5 + 1 raid set)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply