Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

SQL growing problem

Hello all,

I have the problem that two tables are growing out of any proportion:

- APM_ProcessEvidence_Hourly (50.8G)

- APM_ProcessEvidence_Daily (20.8G)

and are now almost half of the size of my overall db of 150G.

Looking at the maintenance log I see the following error, which I think is in relationship to the unstopped growing of the two tables:

2011-07-13 02:16:20,490 [1] INFO SolarWinds.Data.DatabaseMaintenance.MaintenanceEngine - Summarizing detailed data from APM_ProcessEvidence
2011-07-13 04:14:14,291 [1] ERROR SolarWinds.Data.DatabaseMaintenance.StandardTableHandlerDAL - Failed to execute procedure: apm_dbm_APM_ProcessEvidence_DetailToHourly
System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_APM_ComponentStatus_Hourly'. Cannot insert duplicate key in object 'dbo.APM_ComponentStatus_Hourly'.
Warning: Null value is eliminated by an aggregate or other SET operation.
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-07-13 04:14:14,306 [1] INFO SolarWinds.Data.DatabaseMaintenance.MaintenanceEngine - Summarizing hourly data from APM_ProcessEvidence
2011-07-13 04:26:57,265 [1] ERROR SolarWinds.Data.DatabaseMaintenance.StandardTableHandlerDAL - Failed to execute procedure: apm_dbm_APM_ProcessEvidence_HourlyToDaily
System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_APM_ComponentStatus_Daily'. Cannot insert duplicate key in object 'dbo.APM_ComponentStatus_Daily'.
Warning: Null value is eliminated by an aggregate or other SET operation.
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)

Any ideas on how to fix it, it starts to get near to the disk limit. Not really wanting to just drop the tables.

best regards,

Andreas

Find more posts tagged with

sql_grow

sql_2008_table

Accepted answers

netlogix

I had a similar thing. APM history wasn't getting trimmed for a long time, then when I contacted support, they gave me a similar script and it filled my drive due to transaction log size too.

I ended up mounting an temporary iSCSI disk and moved the transaction log there (then back after I was done).

SQL needs a lot of space to get it the space to finish because SQL works in a way that the current transaction (the maintenance) has to be completely thought through and written out (in a transaction log) before it will actually write to the database (even when SQL is set to simple).

I suggest you find a way to get a large drive attached to that server for temporary use, but the other way to do it is to drop all the history.

All comments

FormerMember

Hi Andreas - I'd suggest logging a ticket with Support and flagging it as Urgent as the database needs some immediate attention. Also:

- Reference this thread to Support.
- Post back here with a case number.
- Post any solutions you get from Support

Thanks! :-)

FormerMember

Hello,,

opened the case with support and they gave me a case number:257821 and send me a script to execute which somehow aligned the tables and run successfully.

Script:

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
    declare @err int
declare @retainDate datetime
set @retainDate = dbo.apm_GetRetainDetailDate(GetUtcDate())

if exists (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#process_DetailToHourly%')
drop table #process_DetailToHourly

--select data which is not aggregated
SELECT
csd.ComponentID,
csd.[ID] AS ComponentStatusID,
csd.[TimeStamp],
ped.Name
INTO #process_DetailToHourly
FROM APM_ComponentStatus_Detail AS csd
INNER JOIN APM_Component c on csd.ComponentID = c.ID
LEFT JOIN APM_ProcessEvidence_Detail ped on csd.ID = ped.ComponentStatusID
WHERE (csd.Archive = 0) AND (csd.[TimeStamp] < @retainDate) AND dbo.apm_GetComponentEvidenceType(c.[ComponentType]) = 1

    --delete current data
DELETE t
FROM APM_ComponentStatus_Hourly t
JOIN (SELECT csd.[ApplicationID],
    csd.[ComponentID],
    dbo.DateAndHourOnly(csd.[TimeStamp]) as [TimeStamp]
   FROM APM_ComponentStatus_Detail AS csd
   WHERE (csd.Archive = 0)
   AND (csd.[ID] IN (SELECT DISTINCT [ComponentStatusID] FROM #process_DetailToHourly))
   GROUP BY ComponentID, dbo.DateAndHourOnly([TimeStamp]), [ApplicationID]
   ) t2
ON t.[ComponentID] = t2.[ComponentID] AND t.[Timestamp] = t2.[TimeStamp] AND t.[Archive] = 1

DROP TABLE #process_DetailToHourly

-- optional run of maintanace step
-- [dbo].[apm_dbm_APM_ProcessEvidence_DetailToHourly] '20091214'

Then they advised to run the database maintenance script, but this resulted in filling up my transaction log until the point that my transaction log file growed to 110GB filling up my disk, while the size of the larger table APM_ProcessEvidence_Hourly got only reduced from 50GB to 35GB. Obviously once I stopped the maintenance as the disk was full the rollback kicked in and my table is back to 50GB.

I am willing to drop the data in the table if necessary, but I need a solution, what are my options?

Best regards,

Andreas

FormerMember

This is not normal for a transaction log: I'd advise you ask support about SQL Performance Recommendations as it seems there may be an issue there.

netlogix

I had a similar thing. APM history wasn't getting trimmed for a long time, then when I contacted support, they gave me a similar script and it filled my drive due to transaction log size too.

I ended up mounting an temporary iSCSI disk and moved the transaction log there (then back after I was done).

I suggest you find a way to get a large drive attached to that server for temporary use, but the other way to do it is to drop all the history.