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

    SQL Database is growing fast

    exact

      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

          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,

            • Re: SQL Database is growing fast
              exact

              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

                • Re: SQL Database is growing fast
                  bshopp

                  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

                    • Re: SQL Database is growing fast
                      netlogix

                      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"?

                        • Re: SQL Database is growing fast
                          exact

                          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

                            • Re: SQL Database is growing fast
                              netlogix

                              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.