6 Replies Latest reply on Mar 16, 2011 11:21 AM by netlogix

SQL Database is growing fast

exact Mar 15, 2011 10:27 AM

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

Re: SQL Database is growing fast

pacetti Mar 15, 2011 11:11 AM (in response to exact)

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,
Like (0)

Actions
- Re: SQL Database is growing fast
  
  exact Mar 15, 2011 11:19 AM (in response to pacetti)
  
  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
  
  Like (0)
  
  Actions
  - Re: SQL Database is growing fast
    
    bshopp Mar 15, 2011 1:11 PM (in response to exact)
    
    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
    
    Like (0)
    
    Actions
    - Re: SQL Database is growing fast
      
      netlogix Mar 15, 2011 3:09 PM (in response to bshopp)
      
      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"?
      
      Like (0)
      
      Actions
      - Re: SQL Database is growing fast
        
        exact Mar 16, 2011 5:00 AM (in response to netlogix)
        
        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
        
        Like (0)
        
        Actions
        
        Re: SQL Database is growing fast
        
        netlogix Mar 16, 2011 11:21 AM (in response to exact)
        
        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.
        
        Like (0)
        
        Actions

Go to original post