Post reply

HELP, Sql Issues

  • January 3, 2006 at 9:13 am

    #90276

     

    SQL Server Issues

     

     

    Environment – Windows Server 2003, SQL Server 2000 Service Pack 4 running on a Veritas Cluster

     

    Issue #1:  Transaction log backups – Retention is set to 2 days but expired backups are not deleted.  Both maintenance plan log files and job log files do not report any error details.  Other backup jobs, user and system database backups, run correctly.

     

    From maintenance plan log - SQLMAINT.EXE Process Exit Code: 1 (Failed)

     

    From job log -       (null)

    SQLMAINT.EXE Process Exit Code: 1 (Failed)                                                                                                                                                                                                                     

    (null)

    Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]

     

    Issue #2:  No job history – All SQL Server jobs do not have any job history information.  When trying to view history, the following message is displayed:  “There is no history information for this job”.  The sysjobhistory table in the msdb database is empty.  The SQL Error log does not report any errors.  The SQLAgent.out log reports the following error several times during scheduled jobs:

     

    2005-12-29 23:00:04 - ! [298] SQLServer Error: 18452, Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. [SQLSTATE 28000]

    2005-12-29 23:00:04 - ! [382] Logon to server 'SERVER_NAME' failed (ConnAttemptCachableOp)

  • January 3, 2006 at 9:17 am

    #612422

    Hey Mark,

    Check the owner that the maintenance jobs are running under.

    This message usually indicates that the owner of the job is either Invalid, pasword has expired, or user is "Locked out"

  • January 4, 2006 at 7:55 am

    #612612

    Also check out the account under which SQL Server Agent is running - it may not have proper access to MSSQL, the MSDB database or the backup destination folder (to delete the files).

  • January 4, 2006 at 8:08 am

    #612621

    The thing that is boggling my mind is all my instances are running under the same universal account.  All of my other backups are deleting properly its only the transaction logs that are not being deleted.  Any ideas?

  • January 4, 2006 at 8:19 am

    #612630

    How are your logs being backed up? Do you append them to the current file or do you create a new backup file for each log backup? If you append them, then it will never delete. It can't remove PART of a file, it's all or nothing and the file gets a new date/time everytime it is appended to.

    -SQLBill

  • January 4, 2006 at 8:42 am

    #612638

    We have a script that runs the backups.  This script creates new backup each time its ran.  I can see all the backups that were created so its not being appended. 

  • January 4, 2006 at 6:57 pm

    #612760

    And all your SQL instances backup to the same set of folders?  Have you checked the folder permissions for the SQL Server account & SQL Server Agent account?

  • January 4, 2006 at 10:06 pm

    #612770

    That is my normal process.  Everything is in good shape as far as folder permissions.

  • January 5, 2006 at 8:42 am

    #612907

    Are the servers in diffent Domains?

    Are you going across a firewall?

    Can you ping the other server?

  • January 5, 2006 at 9:43 am

    #612937

    I found the problem.  Basically I had the transaction logs backing up every database including the databases that comes with SQL "Northwind" which was causing the job to fail and it would never get to the point where it would delete the prior jobs.  When I selected the databases I wanted to the jobs to run on the script ran correctly.  Thanks for everyones help.

  • January 5, 2006 at 6:52 pm

    #613087

    Problems like that can be frustrating

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply