locked
SQL Job "Check Usage History" RRS feed

  • Question

  • Does anyone out there have a SQL Server-based SoftGrid data store which has its transaction logs backed up every hour?  We just implemented ours in a SQL Server 2005 64-bit clustered environment.  Here is my conversation with the DBA:

    DBA:  You have a monthly SQL job ("Check Usage History") that runs at 2:00AM, it truncates transaction log, and as a result trn. log backups sequence gets broken, and all consequent trn. backups after 2:00AM will fail.  We can stop trn. log backups for your database, but then you will loose the ability of point in time recovery, and if something happens to the database you can only rely on last nights full backup, and would be able to the status of last night.

    ME:  Does it seem like the transaction log truncation is a bug with the SQL Job, or is it an expected result due to conflicts between the job and the transaction log backups?"

    DBA:  No that is not a bug, at the end of procedure which is called by the job named "Check Usage History", it truncates inactive part of transaction log without backing it up, this action breaks the sequence of the rest of our hourly transaction backups. Causing failed trn. backups every hour till the next full backup. It will be resolved the next day, but sends us 20 alerts in a row each month (on the first day of the month) due to failed trn. backup.
     
    I suggest to share the issue with vendor, and if they insist on leaving the current SQL job the way it is, then we will have to ignore 20 alerts in a row once a month, or we can disable hourly trn. backup only for SoftGrid_PROD database, which is the last thing I want to do, I would like to have the maximum recoverability for all databases.

    Any thoughts on this?

    Thanks,
    Arthur.
    Friday, November 2, 2007 5:30 PM

Answers

  • This job runs only of the first day of the month.  It removes entries from two tables (one with application use records, one with records from the server logs) based on a configuration setting for how many months of data to retain (default=6 months).

     

    You can safely ignore these alerts.  It sounds like your DBA is probably smart enough to edit the stored procedure behind this job to make it work as (s)he would like.  Of course Microsoft wouldn't support such a change, but it is just some logging records here, not anything critical.  Keeping a good backup on the rest of the database (at least via a daily snapshot) is all you really need IMHO.

     

    Wednesday, November 14, 2007 4:09 AM
    Moderator

All replies

  • This job runs only of the first day of the month.  It removes entries from two tables (one with application use records, one with records from the server logs) based on a configuration setting for how many months of data to retain (default=6 months).

     

    You can safely ignore these alerts.  It sounds like your DBA is probably smart enough to edit the stored procedure behind this job to make it work as (s)he would like.  Of course Microsoft wouldn't support such a change, but it is just some logging records here, not anything critical.  Keeping a good backup on the rest of the database (at least via a daily snapshot) is all you really need IMHO.

     

    Wednesday, November 14, 2007 4:09 AM
    Moderator
  • I commented out the SHRINK_FILES: and TRUNCATE_LOG: sections of the  sp_SFTcleanupusage stored procedure and have had no more issues.
    Tuesday, November 24, 2009 8:57 PM
  • I wonder if modification to temporarily turn off the backup in the script would be warranted instead?

    In fact, I would think that hourly backups of this database may be a tad excessive!  Should there be a failure, you only loose any changes made in the management console plus metering and database log records that are more real time.  This is not critical data and you could probably live with less backup.  You might want to talk to your dba about just running the backup once a day and avoid this window.

    I'm not a DBA so maybe others can chip in here too.
    Tuesday, November 24, 2009 10:36 PM
    Moderator