This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Orion SQL Database Maintenance

edavis1 over 8 years ago

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%.

Top Replies

0 neomatrix1217 over 8 years ago

SolarWinds Knowledge Base :: Critical index fragmentation detected during database maintenance
Cancel
Vote Up +2 Vote Down

Cancel
0 edavis1 over 8 years ago in reply 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?
Cancel
Vote Up 0 Vote Down

Cancel
0 neomatrix1217 over 8 years ago in reply to edavis1

It will run when Solarwinds DB Maintenance runs which should be nightly.
Cancel
Vote Up 0 Vote Down

Cancel
0 pratikmehta003 over 8 years ago in reply to edavis1

HI,
Would like to know if u face any issues after u enable it. Do let us know...
Cancel
Vote Up 0 Vote Down

Cancel
0 edavis1 over 8 years ago in reply to neomatrix1217

It didn't defrag anything. The DB Maintenance just finished and the fragmentation is still the same.
Cancel
Vote Up 0 Vote Down

Cancel
0 neomatrix1217 over 8 years ago in reply to edavis1

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.
Cancel
Vote Up 0 Vote Down

Cancel
0 edavis1 over 8 years ago in reply to neomatrix1217

Ahhhh...so you can still manage DB maintenance for an SQL server using Solarwinds DN Maint tool?
Cancel
Vote Up 0 Vote Down

Cancel
0 edavis1 over 8 years ago in reply to neomatrix1217

Another thing I noticed. New tables pop up with IX_InterfaceID field name and completely fragmented. That just does not seem right.
Cancel
Vote Up 0 Vote Down

Cancel
0 edavis1 over 8 years ago in reply to edavis1

After running the DB maint tool the issue cleared up. Good call Neo. The oracle has chosen wisely.
Cancel
Vote Up +2 Vote Down

Cancel
0 neomatrix1217 over 8 years ago in reply to edavis1

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
Cancel
Vote Up 0 Vote Down

Cancel