Critical System Protection

We are running Symantec System Protection, the back end database is a SQL Server 2005 Standard Edition.  I am not sure the folks that are configuring this application are optimizing it to the best of its ability.  The CSPEvent table is growing by an average of 10 million records a day.  The table is on its own file group, and drive, with the non-clustered indices on their own file group, and drive.  This one table is nearly one terabyte with only 90 days of data being held in it.  Do others have this size CSPEvent table?

Hello, This is the SPC forum, I believe you meant to post this in the SCSP forum. I am going to move it over there for you.

see if this article helps

http://www.symantec.com/business/support/index?page=content&id=TECH114212

Thanks Pete.  SQL Agent is running.  The purge from the application has stopped working and I am purging it via a SQL Agent job running the scsp_purgeevents stored procedure. 

DECLARE @Daystokeep INT

SELECT @Daystokeep=DATEDIFF(dd,MIN(event_dt),GETUTCDATE())
FROM   P00CSP1.dbo.CSPEVENT

 BEGIN
  WHILE @Daystokeep >=90
   BEGIN
    EXEC P00CSP1.dbo.scsp_purgeevents @eventclass = 'realtime', @purgemode ='PURGE', @filtervalue=@Daystokeep, @purgelimit=0
    WAITFOR DELAY '00:00:30';
    SET @Daystokeep=@Daystokeep-1
   END
 END

 We are only keeping 90 days worth of data in the table and it is:

fileid Drive Logical Name   Allocated Space(MBs) Used Space(MBs) Available Space(MBs)
3        I:       scspevents   998707                        722113                    276594

I can no longer shrink the above file as I receive an error

--Trying to shrink by 512mb

 dbccshrinkfile(3, 998067) 

--Produces

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

I cannot reorganize the clustered index any longer either as it constantly is blocked:-(

Hi Komal, thank you for responding.  I am not sure what you mean as I am the DBA not the application support.  I am meeting with them today could you please expand on your reply so I can speak with them about it?

Thank you.

Hi Julie,

I would suggest you to have a look to this article I've recently created to reduce the number of events recorded:
https://www-secure.symantec.com/connect/articles/how-reduce-scsp-server-database-size-and-improve-performances

You can find below an additinal article about how to purge Events from the database:
http://www.symantec.com/docs/TECH116227