May 14, 2003 at 12:01 pm
Is there another way to create a script to perform a Rebuild Index on System Tables without using sp_fixindex?
May 14, 2003 at 12:28 pm
To my knowledge the answer is no.
May 15, 2003 at 4:41 pm
Are you wanting to rebuild the indexes on the system tables due to corruption or are you trying to defrag the indexes?
I routinely use DBCC INDEXDEFRAG on every table in all my databases, including the system databases.
David R Buckingham, MCDBA,MCSA,MCP
May 16, 2003 at 5:31 am
How do you use DBCC INDEXDEFRAG when it is not supported for use on system tables? If I could, I would use DBCC DBREINDEX but it also is not supported for use on system tables. Do you have a copy of the script showing how you used DBCC INDEXDEFRAG on your system tables?
I am just trying to Rebuild the indexes on ALL tables in a database; nothing is broke. Just trying to set up maintenance using scripts that will be ran from a batch job. I DO NOT want to use SQL Server Agent for this. Any help will be grateful!
May 19, 2003 at 8:57 pm
DBCC INDEXDEFRAG runs in all of my databases, but as you pointed out it is not supported for use on system tables. It does not cause my Agent job to fail even though it is issued on the system tables.
In fact, until you pointed it out I was unaware that it was having no effect on my system tables. Now I'm searching for the same answer you are.
David R Buckingham, MCDBA,MCSA,MCP
May 20, 2003 at 4:14 am
Ok, after a bit of research the only way to fix up the system tables is using sp_fixindex (or at least the code in the Proc). But you have to first set the DB into single user mode. Once done then you can affect the system indexes except for Sysobjects and Sysindexes (there is a referencing issue that makes it impossible to rebuild the clustered indexes without losing them so they are disabled against).
However the only tables I found may be of any use to correct are the following.
syscolumns
syscomments
sysdepends
and maybe sysusers if you have a lot of accounts in the DB.
Beyond that the rest either don't report any index or they are so small they fit on a single page and their clustered index insures order.
Now it should also be noted that you cannot set master in single user mode while the server is running. You will have to stop and start the server in single user mode to accomplish that in master. Here were the results of what I found in master
syscolumns
systypes
syscomments
syspermissions
sysusers
sysdepends
sysdatabases
sysxlogins
sysdevices
sysmessages
sysconfigures
sysservers
syslanguages (but would not change beyond 50%)
syscharsets (but would not change beyond 75%)
sysaltfiles (but would not change beyond 50%)
sysfilegroups
(note: not all even required doing but unless otherwise noted they were 100% when done, some of these are also in the user DBs and can be done but they were at 100% when I checked. Also, this was done on a SQL 2000 server, no 7 available for testing at this time).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply