Index Space Usage

Hello there.

I need some help regarding the size of the SolarWinds database. Our database is currently about 50GB with data taking up about 10GB and Index Space Usage taking up the remainder.I would very much like to reduce the overall size of the database so that it is more manageable. I have tried shrinking the database/ file, but this doesn’t work. Any help on this matter would be greatly appreciated.Thank you (Orion version 8.5.1 and SQL server 2005).

Find more posts tagged with

database_size

orion_8.5.1

too_much

8.5.1

db_compact

Database

truncate

Accepted answers

thewardo

Excellent!!! Thank you, all is ok now! I truncated TrapVarbinds, Traps and Syslog tables then compacted the DB using Solarwinds Database Manager. The compact program showed an error on the FlowStats table so I let the compact program finish and then checked the table stats. It has a 40GB index! Ran the truncate command on the FlowStats DB, then compacted DB. The database is now only 1.5GB!

All comments

kevin2me

Hello,

1 Take a Backup of
your Orion Database

2 Make sure Your
Orion DB Maintenance is running successfully every night.

C:\Program
Files\Solarwinds\Orion\swdebugMaintenance.log

3 Run Compact
Database in Orion database Manager (Stop Orion Services)

4 Shrink MDF and
LDF files using SQL Server Management Studio. (Stop Orion Services)

If you Still Have
Large Indexes Try The Following.

Perform the
following in SQL Server Management Studio:

It will show you highly
fragmented indexes.

SELECT DATABASE_NAME = db_name(a.database_id) ,a.object_id
AS TABLE_ID ,OBJECT_NAME(a.object_id) AS TABLE_NAME ,a.index_id AS INDEX_ID
,b.name AS INDEX_NAME ,a.avg_fragmentation_in_percent AS
FRAGMENTATION_PERCENTAGE ,a.index_type_desc AS INDEX_TYPE FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) a, sys.indexes
b WHERE a.avg_fragmentation_in_percent > 30 and a.index_id > 0 and
a.index_id=b.index_id and a.object_id=b.object_id;

Or Run DBCC
SHOWCONTIG in SQL Server Management Studio on the NetPerfMon databases to check
the state of the indexes.

Use NetPerfMon ;

DBCC SHOWCONTIG

To rebuild the
table indexes, stop all Orion Services,

then run the
following SQL Statement. Statement below will re-index individual Tables in
SQL.

Use NetPerfMon ;

DBCC DBREINDEX (Table Name)

E.G.

Use NetPerfMon ;

DBCCDBREINDEX (NetFlowSummary2)

Also Look At DBCC INDEXDEFRAG

Depending on the amount of fragmentation, can be considerably faster than

running DBCC DBREINDEX because a relatively unfragmented index can be

defragmented much faster than a new index can be built.

Another advantage is that with DBCC INDEXDEFRAG, the index is always

available, unlike DBREINDEX. A large amount of fragmentation can cause

DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX, which

may or may not outweigh the benefit of the command's online capabilities.

www.microsoft.com/.../ss2kidbp.mspx

thewardo

Thanks for the quick reply! I have tired DBCC DBREINDEX (tableName) and DBCC INDEXDEFRAG, but neither has worked. When running the commands the database grows to fill the drive (only 10GB free) and then stops without reporting errors. I have then tried the compact database option (solarwinds) and also shrink database in SQL, but all this does is get the 10GB back. The index size hasnt changed and is still 40GB. Sorry I am not that experienced with SQL! Can I not just cut my loses and create a new database and copy all my configuration and node details to that? Then scrap the old bloated database?

kevin2me

Why Not Truncate the large Tables. Backup DB first.

Usually Syslog Traps and Trapvarbinds are the large Tables with Orion.

Check which Tables are large in your DB though and let me know what size they are.

Goto Start Menu, All Programs,

Solarwinds Orion, Database Utilities,

Open Database Manager.

Add SQL Server if necessary.

Select Orion Database (NetPerfMon by Default)

Right Click Traps Table and Query Now

Replace Select Query With the Following:

"Truncate Table TrapVarbinds"

Change to Read-Write

and press refresh Button to execute the SQL Statement.

Do the same for Traps Table

Truncate Table Traps

And also if you wish Syslog Table
Truncate Table Syslog

All 3 Tables will then be Empty, no records.

kevin2me

Also you could set your DB Recovery to Simple Mode.

There is a Database option that allows you to change the logging to Simple Mode (and not Full mode), this will greatly help in reducing the size growth of the Transaction log.

The SQL Server database transaction log file is maintained for recovery purposes, but it grows rapidly if you are not using the Simple Recovery model.

To change your database recovery model to Simple:
Run SQL Server Enterprise Manager. Expand the server group, expand your database server, and then expand Databases. Right-click the NetPerfMon database. Click the Options tab. In the Model list, click Simple.
For more information, see A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server in the Microsoft Knowledgebase. or click this link

=> support.microsoft.com/.../
Hope the info helps, let us know if you have further info.

thewardo