System Reporter Crashing

[stoocake]

Morning all, I don't think I've actually posted on here before, but it's a great forum and helped me out in the past.

I have an issue with System Reporter on our F400 whereby it crashes out regularly.

The trimdb logs show the error, but I can't seem to do anything about it. It's been playing up since Friday last week.

2014-08-06 08:00:03: Started trimming tables
2014-08-06 08:00:03: Trimming daily tables
2014-08-06 08:00:03: Trimming hourly tables
2014-08-06 08:00:04: Deleting statldrg samples for uniq_id 1 older than 2014-07-30 06:47:26
2014-08-06 08:29:34: Mssql DB error executing DELETE FROM statldrg_hourly_2 with (rowlock) WHERE TSECS < 1406699246 AND sys_uid = 1: 37000 9002 {[Microsoft][SQL Server Native Client 10.0][SQL Server]The transaction log for database '3parsysrep' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases}

I set up a scheduled task to restart the service each time it crashes so it's never stopped for more than a few minutes at a time.

In terms of the back end, it appears to be writing 100mb of data every few seconds to the transaction logs (it's a SQL DB) and the log file has hit 30gb. The database itself is only 20gb.

I've played around with the settings in the sampling policy, putting them both up (so the trimbdb job has less work to do) and down (to try and purge more data at once from the DB) but neither make any difference.

Any help would be appreciated.

[stoocake]

Looks like we may have fixed it. Expanded the 30gb limit on the transaction log to 45gb seems to have sorted it.

[Schmoog]

Increasing the size limit for the transaction logs is only a band aid though. If it got up to 30 gb it'll hit 45 with relative ease as well.

I would suggest the following:

A) perform a regular full backup with transaction log truncation using a backup software's sql integration (backup exec, veeam, appassure, commvault, hp data protector, take your pick, they all do it)

Or

B) set your sql database to "simple" recovery model rather than "full" recovery model, and then compact the database. This will negate the use of the transaction logs, save you a bunch of disk space, and prevent you from having that particular issue in the future.

If you want detailed steps for option b, let me know what version of sql you are using for your system reporter and I can send them over

[Richard Siemers]

Excellent reply Schmoog. Are you DBA in addition to a storage admin?