db maintenance - SP run manually

Question

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

mezdem · Answer

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

tomas.mrkvicka · Answer

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

@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

tomas.mrkvicka · Answer

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'