cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

SQL Database is growing fast

Jump to solution

Hi,

we run NPM, APM,NTA and  NCM on a SQL 2008 x64 Database. This Database is growing very fast.

I did a compact database 10 days ago and we had a size of 7GB after that. Now ten days later the Database is back to 57GB and the server is very slow because swapping.

I run compact database at the moment and I just see a "not responding" and hope it will run anyway.

Is there a way to schedule a compact database on a daily base?

 

here some details:

Database Engine      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Standard Edition (64-bit) on Windows NT 6.1 (Build 7600: ) (Hypervisor)

Network Elements      2417
Nodes     403
Interfaces     1606
Volumes     408
Alerts      140
Events     233263
Pollers     4277
Polling Engines     1
Retain Detail Stats      7 Days
Retain Hourly Stats     30 Days
Retain Daily Stats     365 Days
Retain Events     30 Days

thanks

Eike

0 Kudos
1 Solution
Level 14

exact,

The section "Creating a Database Maintenance Plan" in the SolarWinds Orion NPM Administrator Guide provides a procedure for creating a scheduled backup of your database that includes a db compact and shrink.

HTH,

View solution in original post

0 Kudos
6 Replies
Level 14

exact,

The section "Creating a Database Maintenance Plan" in the SolarWinds Orion NPM Administrator Guide provides a procedure for creating a scheduled backup of your database that includes a db compact and shrink.

HTH,

View solution in original post

0 Kudos

Hi Pacetti,

 

sorry you right was a while ago as I created that.

But..  it ran for 6 days and from this point it failed. Becaue that I started a manual compact database.

Compacting still hangs at "NetFlowSummary2 table" for about an hour now (like three times before)

Eike

0 Kudos
Product Manager
Product Manager

Are you storing alot of Syslog and Traps?  That will cause your DB to grow fast.  Also how many flows per second are you sending?

This doc might be a good read as well here

0 Kudos

I had an issue where my sw database maintenance (Database-Maint.exe) was failing because my temp DB couldn't grow big enough to handle the shrink process.

Have you looked in "C:\Program Files (x86)\SolarWinds\Orion\swdebugMaintenance.log"?

0 Kudos

Hi,

thanks for all the replies.

The Database Maintance jobs last night failes again, after I deleted the old ones and recreated the backup and compacting job.

the swdebugMaintenance.log shows that following error the only one rest looks fine.

System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_NPM_NV_WL_CONTROLLERS_HOURLY_NPM_NODES". The conflict occurred in database "SolarWindsOrion", table "dbo.NPM_NV_WL_CONTROLLERS_HOURLY", column 'NPMNodeID'.
The DELETE statement conflicted with the REFERENCE constraint "FK_NPM_NODES_DvcID_ID". The conflict occurred in database "SolarWindsOrion", table "dbo.NPM_NODES", column 'DvcID'.
The DELETE statement conflicted with the REFERENCE constraint "FK_NPM_DEVICES_TemplateID_ID". The conflict occurred in database "SolarWindsOrion", table "dbo.NPM_DEVICES", column 'TemplateID'.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at SolarWinds.Data.DatabaseMaintenance.StandardTableHandlerDAL.InternalExecuteStoredProc(String procedureName, SqlParameter[] parameters)
2011-03-16 03:33:55,679 [1] INFO  SolarWinds.Data.DatabaseMaintenance.MaintenanceEngine - Summarizing detailed data from NPM_Nodes
2011-03-16 03:33:55,679 [1] INFO  SolarWinds.Data.DatabaseMaintenance.MaintenanceEngine - Summarizing hourly data from NPM_Nodes
2011-03-16 03:33:55,679 [1] INFO  SolarWinds.Data.DatabaseMaintenance.MaintenanceEngine - Removing older data from NPM_Nodes
2011-03-16 03:33:55,679 [1] INFO  SolarWinds.Data.DatabaseMaintenance.MaintenanceEngine - Removing summarized older data from NPM_Nodes
2011-03-16 03:33:55,679 [1] INFO  SolarWinds.Data.DatabaseMaintenance.MaintenanceEngine - Finializing maintenance for  NPM_Nodes

If I start the job manually it just fails at the same second (No logfile in the given folder).

If I do a manual compact I get several errors like

---------------------------
DatabaseManager
---------------------------
Error Rebuilding Indexes On Table NetFlowDetail_489_1444740
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find a table or object with the name "[dbo].[NetFlowDetail_489_1444740]". Check the system catalog.
---------------------------
OK  
---------------------------

 

But after confirming with OK it comes to an end.

I will try a manual backup in a few minutes. and the go through the Doc from bshopp which looks very interesting (thanks for that by the way)

 

Eike

0 Kudos

if that message keeps showing up every day in that log, open a support ticket and give that to them.  When my tempdb ran out of space, it made constraint conflicts too.  Support gave me a script to clean it up (it was in a different table than your's though).

I now have an APM monitor that checks that log for "] Error " in that file and alert if it is there.

0 Kudos