Reindexing the system tables
-
Hi forum,
My question is tricky:
How can I reindindex my system tables (sysobjects, sysindexes, syscolumns...)
I know that officially I cannot DBXX DBREINDEX or INDEXDEFRAG, nor delete and recreate the indexes on those tables.
But we need to.
We are running SAP on SQL2K Ent. Ed. and as you may know SAP is coming with over 27.000 tables.
Now my sysobjects tables hae over 275.000 Rows, the sysindexes over 60.000 and the syscolumns over 7.000.000 rows.
And of cours thos tables are terribly fragmented wich affects the performance.
I just simply cannot accept the answer from the BOL
"DBCC DBREINDEX is not supported for use on system tables."
What should I do?
Bye
Gabor -
Hi nyulg,
quote:
We are running SAP on SQL2K Ent. Ed. and as you may know SAP is coming with over 27.000 tables.so it is true, that you can run an SAP/R3 system on SQL Server ?
Woh, that's pretty challenging, I think.
I thought they only use something like Adabas ?!?, SAPDB and Oracle.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
For shure!
I'm running several SAP systems for over 2 years now!
My database size is over 150 GB and I have 1300 user connected to. The average transaction response time is still below 0.5 sec.
It's running fine, I do not have problems with excepting the system table maintenances.
Bye
Gabor -
Hey gurus,
No hints, no ideas?
I would really need a solution
Bye
Gabor -
This should do for you.
Microsoft Knowledge Base Article - 106122
sp_fixindex.
Let us know.
Thanks Tom.
-
Hi Tom,
quote:
This should do for you.Microsoft Knowledge Base Article - 106122
sp_fixindex.
can you provide the full link?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The SP somehow do not want to fire.
I am in the test database.
I have the foolowing error message : "The database name provided 'test' must be the current database when executing this stored procedure."
It works for everything exceptind
I've checked the contect of that SP and I have seen that after some test it is calling dbcc dbrepair.
I've tried it it it was working for everything excepting sysobjects and sysindexes. Here is the error message:
"Server: Msg 2598, Level 16, State 1, Line 1
Clustered indexes on sysobjects and sysindexes cannot be re-created.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
"
Any idea how to go forward on those 2 tables
Thanks
Gabor
Bye
Gabor -
By the way Tom,
The officiel answer from MS is that there is no official answer to my question.
There is a workaround which is copy the database with DTS which will recreate the system tables.
That one is of course inacceptable for us because our database has over 150 GB and I do not have too many spare disk space
Frank,
The link is: http://support.microsoft.com/default.aspx?scid=kb;en-us;106122
Bye
Gabor -
Hi Gabor,
quote:
The link is: http://support.microsoft.com/default.aspx?scid=kb;en-us;106122thanks!
Btw, I've searched other sites without any results
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I read somewhere (but I cannot find it at the moment) that if you change the sort collation, stop and restart the SQL Service, then the indexes on the system tables are recreated. I believe that this is the only way to reindex the system tables.
Someone might be able to confirm whether this is true. You might want to do it twice - once to change it and again to put it back. However, I do not know whether you can recycle the service to do this.
Jeremy
-
Jeremy,
Unfortunatly it did not help.
I've created a test database with 10.000 tables each with 1 clustered and 1 nonclustered index.
After my creation the system tables (sysobjects, sysindexes, syscolumns) became wery fragmented :
DBCC SHOWCONTIG scanning 'sysindexes' table...
Table: 'sysindexes' (2); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 699
- Extents Scanned..............................: 92
- Extent Switches..............................: 697
- Avg. Pages per Extent........................: 7.6
- Scan Density [Best Count:Actual Count].......: 12.61% [88:698]
- Logical Scan Fragmentation ..................: 51.50%
- Extent Scan Fragmentation ...................: 58.70%
- Avg. Bytes Free per Page.....................: 2765.9
- Avg. Page Density (full).....................: 65.83%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After I've altered the database collation and then back. It took a while.
I've checked the fragmentation again but no change
At the end of the day I only have a problem with sysobjects and sysindexes. for everything else the dbrepair works.
Gabor
Bye
Gabor -
Hi Gabor,
just a quick thought, just hit me.
Why don't you call SAP support, to see what they say?
They ust have some experience with this issue
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Already done.
This is a Microsoft "feature" they told to me.
And really it is not at all an SQL issue.
Try to have a database with several hundreds or thousends of tables and you will get the same problem.
We have called MS as well as we are Premier Support Partners.
What they told as was as good as nothing:
"You have to copy your database into a new database. It will recreate your system tables"
A bullshit! This is not really a customer friendly answer!
Making a copy of a database having several hundreds of GB (assuming you have as much of spare space), after test is everything is OK.
And all this during a maintenance window of one night!!!
Until now the sp_fixindex was the best idea. It is working on every table excepting sysindexes and sysobjects.
Really, what I think is that MS is not prepared to hande that kind of applications.
And I'm really sorry to say that because I like SQLServer very much. I'm working on for years as I've done for DB2, Oracle, Sybase and I think on a Windows platform this is the best choice.
But they have to do something to improve that kind of problems!
Bye
Gabor -
quote:
Until now the sp_fixindex was the best idea. It is working on every table excepting sysindexes and sysobjects.And I'm really sorry to say that because I like SQLServer very much. I'm working on for years as I've done for DB2, Oracle, Sybase and I think on a Windows platform this is the best choice.
But they have to do something to improve that kind of problems!
tja, it was worth a try.
Not that I fully agree with you, but that leads to my first reply
Our SAP runs on Oracle on a mainframe, and although we outsourced this to somewhere else it runs pretty fast and stable.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Na ja.
As you may know within SAP the real bottleneck is not the database server but the application servers.
But our overall SAP response times are quite nice: below 0,5 sec per transaction!
And all this for 1300 users.
(by the way we are running SAP for our customers on WINTEL using Oracle and even on a mainframe based DB2.)
By the way this is not the SAP whare I want to bulk copy. 🙂
Bye
Gabor
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply