3 Replies Latest reply on Jan 6, 2012 9:56 AM by mezdem

    db maintenance - SP run manually

    mezdem

      Hi All, The db maintenance routine fails every night after varying times due to using all available space on the db cluster node (shared) tlog.


      So I have started to work through the db maintenance stored procs but basically i need to run them all individually to ensure that we dont run out of tlog space on that volume.


      I have run these already - all fine.


      exec


      dbo.dbm_SyslogMessages_DeleteStale


      '2011-12-04 11:47:01.403'


      exec


      dbo.dbm_TrapMessages_DeleteStale


      '2011-12-04 11:47:01.403'


      exec


      dbo.dbm_Events_DeleteStale


      '2011-10-04 11:47:01.403'


      Now I need to run the rollup tables procs, is there a master SP that i am missing? As this is a one off run to clear out potentially 10 months + of data, I would like to be able to set a manual @date value for each set of SPs to use, but the rollup ones use a passthrough value which I need help with the running order and script...


      Main ones are:


       


      -- dbo.dbm_ResponseTime_DetailToHourly


      -- dbo.dbm_ResponseTime_HourlyToDaily


      -- dbo.dbm_ResponseTime_DeleteStale


      -- dbo.dbm_ResponseTime_DeleteOrphans


      and


      -- dbo.dbm_CPULoad_DetailToHourly


      -- dbo.dbm_CPULoad_HourlyToDaily


      -- dbo.dbm_CPULoad_DeleteStale


      -- dbo.dbm_CPULoad_DeleteOrphans


       


      -- dbo.dbm_CustomPollerStatistics_DetailToHourly


      -- dbo.dbm_CustomPollerStatistics_HourlyToDaily


      -- dbo.dbm_CustomPollerStatistics_DeleteStale


      -- dbo.dbm_CustomPollerStatistics_DeleteOrphans


      but I will need to run nearly of the sets of procs in turn. it currently gets around the NPM_Wireless table section before it fails.


       


      Thanks


      M

        • Re: db maintenance - SP run manually
          Tomas Mrkvicka

          Hi,

          these stored procedures usually requires @id parameter which identifies entity (node, interface etc.). You must call given procedure for every entity (you can do it in database cursor). Here is brief description:


          For ResponseTime procedures you must call procedure for each NodeID from table Nodes (if stored procedure requires @id parameter)

          EXEC [dbo].[dbm_ResponseTime_DeleteOrphans] @id = 10
          EXEC [dbo].[dbm_ResponseTime_DeleteStale] @dateDetailed = N'2012-01-01 00:00:00',@dateHourly = N'2012-01-01 00:00:00', @dateDaily = N'2012-01-01 00:00:00'
          EXEC [dbo].[dbm_ResponseTime_DetailToHourly] @id = 10, @date = N'2012-01-01 00:00:00'
          EXEC [dbo].[dbm_ResponseTime_HourlyToDaily] @id = 10, @date = N'2012-01-01 00:00:00'

          For CPULoad procedures you must call procedure for each NodeID from table Nodes (if stored procedure requires @id parameter)

          EXEC [dbo].[dbm_CPULoad_DeleteOrphans] @id = 10
          EXEC [dbo].[dbm_CPULoad_DeleteStale] @dateDetailed = N'2012-01-01 00:00:00',@dateHourly = N'2012-01-01 00:00:00', @dateDaily = N'2012-01-01 00:00:00'
          EXEC [dbo].[dbm_CPULoad_DetailToHourly] @id = 10, @date = N'2012-01-01 00:00:00'
          EXEC [dbo].[dbm_CPULoad_HourlyToDaily] @id = 10, @date = N'2012-01-01 00:00:00'

          For CustomPollerStatistics procedures you must call procedure for each CustomPollerAssignmentID from table CustomPollerAssignment (if stored procedure requires @id parameter)

          EXEC [dbo].[dbm_CustomPollerStatistics_DeleteOrphans]
          EXEC [dbo].[dbm_CustomPollerStatistics_DeleteStale] @dateDetailed = N'2012-01-01 00:00:00',@dateHourly = N'2012-01-01 00:00:00', @dateDaily = N'2012-01-01 00:00:00'
          EXEC [dbo].[dbm_CustomPollerStatistics_DetailToHourly] @id = 'A408BEC1-3E09-4638-B548-C26DD0725F4A', @date = N'2012-01-01 00:00:00'
          EXEC [dbo].[dbm_CustomPollerStatistics_HourlyToDaily] @id = 'A408BEC1-3E09-4638-B548-C26DD0725F4A', @date = N'2012-01-01 00:00:00'

            • Re: db maintenance - SP run manually
              Tomas Mrkvicka

              Here is pattern for cursor going through all nodes:

               

              DECLARE mainCursor CURSOR LOCAL FAST_FORWARD FOR
              SELECT NodeID FROM Nodes WITH(NOLOCK)

              OPEN mainCursor

              DECLARE @actualNode AS INT

              FETCH NEXT FROM mainCursor INTO @actualNode

              WHILE @@FETCH_STATUS = 0
              BEGIN
                  -- here you can execute procedure for given node stoed in @actualNode variable

                  FETCH NEXT FROM mainCursor INTO @actualNode   
              END

              CLOSE mainCursor
              DEALLOCATE mainCursor

                • Re: db maintenance - SP run manually
                  mezdem

                  thanks tomas, thats not going to give me what i need, SW can you please help with the actual code you use during the main window?

                  Ideally I would want to use the actual values set for each type of statistic - so getdate()-30 for detailed, getdate()-60 for hourly, getdate()-90 for daily etc as set in the 'Polling Settings' table.

                  Then how to forward that value chosen into the next SP so that I dont delete detailed before they are converted into hourly, and hourly records before they have been converted into daily sums etc

                   

                  Cheers