Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    BC Canada
    Posts
    61

    Unanswered: Fragmented System Tables

    I have found that a bunch of our tables were highly fragemented. (in some cases as much as 80%)

    I used DBCC dBReindex to correct all the user tables, but how do I defrag the system table indexes?


    Thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    --all except clustered indexes on sysobjects and sysindexes
    dbcc dbrepair(@dbname, repairindex, @tabname, @indid)

    -- And an example
    exec sp_dboption 'YourDatabase','single user','true'
    dbcc dbrepair('YourDatabase', repairindex, 'syscolumns', 1)
    exec sp_dboption 'YourDatabase','single user','false'

    -- And better encapsulated in undocumented MS SP
    exec sp_dboption 'YourDatabase','single user','true'
    exec sp_fixindex 'YourDatabase', 'syscolumns', 1)
    exec sp_dboption 'YourDatabase','single user','false'

  3. #3
    Join Date
    Oct 2002
    Location
    BC Canada
    Posts
    61
    Originally posted by ispaleny
    --all except clustered indexes on sysobjects and sysindexes
    dbcc dbrepair(@dbname, repairindex, @tabname, @indid)

    -- And an example
    exec sp_dboption 'YourDatabase','single user','true'
    dbcc dbrepair('YourDatabase', repairindex, 'syscolumns', 1)
    exec sp_dboption 'YourDatabase','single user','false'

    -- And better encapsulated in undocumented MS SP
    exec sp_dboption 'YourDatabase','single user','true'
    exec sp_fixindex 'YourDatabase', 'syscolumns', 1)
    exec sp_dboption 'YourDatabase','single user','false'
    Thank you very much,

    2 additional related questions

    1) Can this be set to run weekly via a scheduled Job?
    2) Can this be done with users still connected

    (we have users connected 24/7 via internet)

    Thanks again.

  4. #4
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Q1) Can this be set to run weekly via a scheduled Job?
    A1) Yes. But you must add a script for disconnecting users.

    Q2) Can this be done with users still connected
    A2) No. The database must be in a single user mode.

    I must be a funny job to maintain a living database
    Let me know how you solved it.

    Good luck !

  5. #5
    Join Date
    Nov 2002
    Posts
    19
    If you have set up your web site to have one place where the connections are made AND if the database is not THAT huge, you could replicate it, run the corrections, point the connections to the "new" database, replace the old one and go on.

    That should be something that could certainly be automated. Might be more work than its worth tho....
    DerFarm
    It IS as bad as you think
    and they ARE out to get you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •