13 Replies Latest reply on Jan 1, 2016 5:51 AM by pratikmehta003

Orion SQL Database Maintenance

edavis@mdsol.com Dec 29, 2015 8:54 PM

Has anyone written or seen an SQL query that can automatically defragment all the tables? I found that a ton of my Orion DB have over 90% fragmentation and more than half of the tables are over 75%.

Re: Orion SQL Database Maintenance

neomatrix1217 Dec 29, 2015 10:23 PM (in response to edavis@mdsol.com)

SolarWinds Knowledge Base :: Critical index fragmentation detected during database maintenance
1 of 1 people found this helpful
Like (1)

Actions
- Re: Orion SQL Database Maintenance
  
  edavis@mdsol.com Dec 29, 2015 10:32 PM (in response to neomatrix1217)
  
  Thanks Neo. You are the ONE. The oracle has seen it. One more quick question. When enabled, how often does the system run defrag on itself?
  
  Like (0)
  
  Actions
  - Re: Orion SQL Database Maintenance
    
    neomatrix1217 Dec 29, 2015 11:25 PM (in response to edavis@mdsol.com)
    
    It will run when Solarwinds DB Maintenance runs which should be nightly.
    
    Like (0)
    
    Actions
    - Re: Orion SQL Database Maintenance
      
      edavis@mdsol.com Dec 30, 2015 7:57 AM (in response to neomatrix1217)
      
      It didn't defrag anything. The DB Maintenance just finished and the fragmentation is still the same.
      
      Like (0)
      
      Actions
      - Re: Orion SQL Database Maintenance
        
        neomatrix1217 Dec 30, 2015 11:08 AM (in response to edavis@mdsol.com)
        
        Sorry was out visiting the Oracle in that ghetto housing place she lives in. Can run DB Maintenance and watch it let me know if you see it saying Defragmenting in the status bar.
        
        Like (0)
        
        Actions
        
        Re: Orion SQL Database Maintenance
        
        edavis@mdsol.com Dec 31, 2015 2:46 PM (in response to neomatrix1217)
        
        Ahhhh...so you can still manage DB maintenance for an SQL server using Solarwinds DN Maint tool?
        
        Like (0)
        
        Actions
        
        Re: Orion SQL Database Maintenance
        
        edavis@mdsol.com Dec 31, 2015 3:01 PM (in response to neomatrix1217)
        
        Another thing I noticed. New tables pop up with IX_InterfaceID field name and completely fragmented. That just does not seem right.
        
        Like (0)
        
        Actions
        
        Re: Orion SQL Database Maintenance
        
        edavis@mdsol.com Dec 31, 2015 3:04 PM (in response to edavis@mdsol.com)
        
        After running the DB maint tool the issue cleared up. Good call Neo. The oracle has chosen wisely.
        
        Like (2)
        
        Actions
        
        Re: Orion SQL Database Maintenance
        
        neomatrix1217 Dec 31, 2015 3:06 PM (in response to edavis@mdsol.com)
        
        Here is a more in depth defrag you can run from SQL Studio
        
        DECLARE @CoreTable VARCHAR(255)
        DECLARE CoreCursor
        CURSOR FOR
        SELECT table_name
        FROM information_schema.tables
        WHERE table_type = 'base table'
        OPEN CoreCursor
        FETCH NEXT
        FROM CoreCursor INTO @CoreTable
        WHILE @@FETCH_STATUS = 0
        BEGIN
        PRINT 'Reindexing Table: ' + @CoreTable
        DBCC DBREINDEX(@CoreTable, '', 80)
        FETCH NEXT
        FROM CoreCursor INTO @CoreTable
        END
        CLOSE CoreCursor
        DEALLOCATE CoreCursor
        
        Like (0)
        
        Actions
  - Re: Orion SQL Database Maintenance
    
    pratikmehta003 Dec 30, 2015 4:04 AM (in response to edavis@mdsol.com)
    
    HI,
    
    Would like to know if u face any issues after u enable it. Do let us know...
    
    Like (0)
    
    Actions
    - Re: Orion SQL Database Maintenance
      
      edavis@mdsol.com Dec 31, 2015 4:39 PM (in response to pratikmehta003)
      
      No issues. This solved the fragmentation issue.
      
      Like (1)
      
      Actions
      - Re: Orion SQL Database Maintenance
        
        pratikmehta003 Jan 1, 2016 5:51 AM (in response to edavis@mdsol.com)
        
        Thanks for informing...
        
        Like (0)
        
        Actions
Re: Orion SQL Database Maintenance

Malik Haider Jan 1, 2016 3:35 AM (in response to edavis@mdsol.com)

For more details please find the details below
Quick Orion Database Health Check
1 of 1 people found this helpful
Like (1)

Actions

Go to original post