How do I defrag a System table in MSSQL 2000?

[shanec20]

How do I defrag a System table in MSSQL 2000? Using DBCC REINDEX does not work.

 

[Glyndwr]

if you have a clustered index then use

DBCC INDEXDEFRAG

stating the name of the clustered index

 

[JayKusch]

You cannot run the DBCC REINDEX command against system tables.

I would suggest you run DBCC CHECKCATALOG. This will check for the consistencies in and between the system tables in the specified database.

Maybe from the info returned you can get a better idea of what needs to occur w/ the system table you are questioning.



Thanks

J. Kusch

 

[RobColborne]

Hi

A SQL 2000 server is having problems with slow down where the memory used increases with it eventually locking up.

The Server has 1Gb of memory and is hosts just one user database. A cap has been placed on the memory in Enterprise Manager and the database has been re-indexed and indexed defragged.

I can't believe that it is a memory resource issue as 1gb should be plenty.

There is only one drive on the server with the MDF and LDF files stored togehter. I would like to see this changed to at least having a another drive for the transaction logs.

My Questions are:
1) How would the drive configuration be causing these issues with the server and having an affect on the memory as I need to explain this.

2) Is there anything else I could do to prevent the slow down

Many Thanks
Rob

 

[JayKusch]

I beleive you are on the write track initially. Split the data and TLog files to seperate discs. The issue on having them both on the same drive is that they are comepeting. The TLog is being written to with every SQL Server operation so it is quite busy being written to during normal operations. Thus, this takes away from the resources needed to write to your data file (MDF). In a perfect world, you would even split your TempDB to another disc and other DB objects like indexes and such to their seperate discs/partitions.

In seeing your memory continually growing until exhaustion, I would take a look at what jobs, queries, and applications are hitting your DB during this event and determine where the issue is. Use Performance Monitor to pull some meterics as well as leveraging SQL Server Profiler. Also keep tabs on applications besides SQL Server that may be running on the same server. I would take a look at Task Manager for these items.

Hope this get you started in the right direction to resolve this issue.

Thanks

J. Kusch