October 4, 2004 at 6:53 am
Hello Everyone!
We have been having severe issues (many EXCEPTION_ACCESS_VIOLATONs, etc.) dynamically creating some large indices on some rather large, wide tables (~50 million rows, >100 columns) in our DB that was upgraded from 7.0 to 2000. Searching Microsoft's site for the past few months turned up the MSDN article Q295114. Apparently the keys column in the sysindexes system table in 7.0 was limited to 816 bytes. The upgrade (to 2000) was supposed to upgrade this column to allow 1088 bytes. Checking the system tables in some of the suspect DBs shows that these columns were not updated. They are still varbinary 816.
Anyway to get these system tables updated after we have already followed the upgrade path from 7.0 to 2000?
Thaks ahead of time for any assist!
Allen
October 7, 2004 at 11:09 am
Dumb question....but if you go in the EM and look at the Properties sheet -> Options tab, what does it say for the "Compatibility Level"?
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
October 7, 2004 at 12:25 pm
The compatibility level is reported in Enterprise Manager as "Database compatibility level 80." What happened was, back in January, the previous DBA did not install SQL Server 2000 over top of the current 7.0 installation. The DBs were either backed up or the datafiles were moved off the server and the server was wiped clean. A new OS was installed (Win Server 2003) and a fresh, full copy of SQL Server 2000 installed. I presume the DBs were either restored in SQL Server 2000, or the data files were moved back onto the server and the DBs were reattached from within SQL Server 2000.
By doing this, it appears that the system tables in three of our DBs were not properly updated, namely the keys columns in the dbo.sysindexes tables were not increased from varbinary (816) to varbinary (1088). That's the only one I have been able to find so far (I'm sure there are more), but it appears to have the most to do with our observable issue: the creation and consistency of large indices on large tables. We keep getting all kinds of EXCEPTION_ACCESS_VIOLATIONs and they bring the service down, killing whatever else may have been running in the instance.
Right now, what I am doing, and I think this is the "long way" around, is this: create a new, empty DB. Use the DTS task "Copy SQL Server Objects" to move all of the objects from the old DB (created in 7.0) to the new (created in SQL Server 2000). Because this task will only (to my knowledge, but someone may know better) create objects in the PRIMARY file group, I have to drop all nonclustered indices and nonclustered primary keys and recreate them on an INDEX file group, on separate RAID 10 spindles (from the RAID 5 spindles where the PRIMARY file group datafiles are located).
I was just wondering if there was a tool/hack to "upgrade" a 7.0 DB's sytem tables once it had been attached to SQL Server 2000, but not properly "upgraded" by the SQL Server 2000 installation routine. We had tried re-installing SQL Server 2000 overtop of the existing 2000 installation, but this did not fix the issue; sys tables reamined in 7.0 format. The data files were also moved to a test server with 7.0 installed, but they were no longer recogized as valid DB datafiles by 7.0. Catch-22!!
Thanks!
Sincerely,
Allen
October 7, 2004 at 12:55 pm
Here's a thought.
Do a backup of the DB as it currently is. Then do a restore as "New_DB" on the same server. Take a look at the tables and see if they are right.
It might be a case of just simply do a Drop Database and Restore. Depending on authentication mode you might have to do sp_sidmap, but that isn't a big deal.
Just throwing out an idea.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
October 7, 2004 at 1:02 pm
Sounds like that ought to work. I will give that a shot.
Thanks, Jim!
Sincerely,
Allen
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply