none
SQL Server job failed. The Job was invoked by Schedule 11 (NAV Backup 1). The last step to run was step 1 (Subplan_1)

    Question

  • Date 07-01-2014 10:00:00 PM
    Log Job History (NAV Backup 1.Subplan_1)

    Step ID 1
    Server NAVSRV
    Job Name NAV Backup 1.Subplan_1
    Step Name Subplan_1
    Duration 00:02:26
    Sql Severity 0
    Sql Message ID 0
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0


    1122


    • Edited by G-Unic Saturday, January 11, 2014 8:59 AM
    Thursday, January 09, 2014 10:08 AM

Answers

  • Discussed offline with OP,problem was that OP was taking backup on Network drive and SQL server service account did not had permissions .Asked him to provide read/write/modify permission on that folder to SQL server service account

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, January 15, 2014 6:29 AM

All replies

  • " failed with the following error: "Could not locate file 'PHARMANEXT_log' for database 'PHARMANEXT' in sys.database_files. The file either does not exist, or was dropped.   DBCC execution completed.

    Cannot shrink file '2' in database 'ReportServerTempDB' to 128 pages as it only contains 96 pages.  DBCC execution completed. 


    1122

    Hello,

    Your log has 2 messages

    1. Says log file for database 'PHARMANET' does not exists.Can you check whether such log file exists .Look for drive on which data files for this database resided.

    2. You are trying to shrink log for Report server tempdb database which i dont think is required.You tried to shrink log file to specified value but i cannot be done as size is much less than that.

    I do not recommend running shrink db as a part of maintenance plan for log files.It causes performance issues and there is no need to do for some temp db


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, January 09, 2014 10:44 AM
  • I navigated to C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA  and noticed that PHARMANEXT_log  is modified to PHARMANEXT_log_95ABA2F6-DC67-4CFC etc. something like this. I am not good at SQL server but think that it is not normal

    1122

    Is it by any chance your application creates new database every month or quater  or by any specified time.Is this first time you faced this issue ( backup failed) . Log file with name 'PHARMANEXT_log_95ABA2F6-DC67-4CFC' does not seems to me created by user.

    If you have created backup using maintenance plan select all database option while selecting database .


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Thursday, January 09, 2014 11:06 AM
    Thursday, January 09, 2014 11:06 AM
  • 22:01:46.08     Source: delete change log & shrink DB      Executing query "  DBCC SHRINKDATABASE (PHARMANEXT)".: 100% complete  End Progress  Progress: 2014-01-07 22:02:08.93     Source: index reorganise      Executing query "USE PHARMANEXT;  SET NOCOUNT ON;    DECLARE @objec...".: 100% complete  End Progress  Error: 2014-01-07 22:02:24.37     Code: 0xC002F210     Source: remove sql log Execute SQL Task     Description: Executing the query "DECLARE @name VARCHAR(50)  DECLARE @fileName VARCH..." failed with the following error: "Could not locate file 'PHARMANEXT_log' for database 'PHARMANEXT' in sys.database_files.

    1122

    Hello,

    Sorry i missed few points again.Your job name is 'Delete change log & shrink DB'.

    Please dont shrink database using DBCC SHRINKDATABASE command it will cause heavy fragmentation.Remove it from job plan

    Problem here is your log file changed ,you need to change this location in you Plan.This will solve the issue.But you still did not answer my question how and who changed it.See SQL server errorlog file.

    There is job step remove' SQL log execute task' I see this as a culprit

    Regarding how to make sure all databases are included in backup maintenance plan.See below ,click radio box  All databases


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, January 09, 2014 11:25 AM
  • About your question on How and Who changed it, i told that nobody changed it. I found in this way.


    1122

    I also Saw the plan and the path of Database and database log was ok , i mean PHARMANEXT_LOG_95ABA2F6-DC67-4CFC etc... was exactly like in the C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

    Maybe is something wrong with my backup script, but i dont think so because before 23 of december everithing was ok. After this date it failed.

    Anyway check this script of "Remove SQL log" , the one that you think is a culprit:

    DECLARE @name VARCHAR(50)
    DECLARE @fileName VARCHAR(256)

    DECLARE db_cursor CURSOR FOR 
    SELECT name 
    FROM master.dbo.sysdatabases 
    WHERE name NOT IN ('master','model','msdb','tempdb') 

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @name  

    WHILE @@FETCH_STATUS = 0  
    BEGIN  
    SET @filename = @name + '_log' 
    Exec ('Use [master] alter database [' + @name + '] set recovery simple')
    Exec ('Use [master] alter database [' + @name + '] set recovery FULL')
            Exec ('Use [' + @name + '] DBCC SHRINKFILE ("' + @filename + '",1)')

           FETCH NEXT FROM db_cursor INTO @name  
    END  

    CLOSE db_cursor  
    DEALLOCATE db_cursor

    go

    Hello,

    You have created a cursor which will take all user databases and change recovery model to Simple and then to full what this will cause it will break the log chain and you might loose your RPO and RTO.This operation truncates the log .Please stop this job immediately.It is worst you can do to your Database recovery and availability.After this you are trying to shrink .

    As pointed out it is seriously not advised to shrink data file.And if you have proper trn log backup it manages the growth of log file

    Hope this helps

    Keep looking for any such activity which might change anything in your database


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, January 09, 2014 3:59 PM


  • DELETE from [PHARMANEXT].[dbo].[Pharma Next shpk$Change Log Entry]
    where [Date and Time] < DATEADD(day,- 365, (SELECT GETDATE()))

    GO

    DBCC SHRINKDATABASE (PHARMANEXT)

    1122


    Your are welcome.Now again why shrink database command buddy.I suggested you not to run this this will cause massive fragmentation this is worst you can do to your database.

    Also you are trying to delete all records which are 1 year old are you aware that delete operation is fully logged and will generate huge trn logs .How much is data for 1 year i guess its large.Generally on busy OLTP system deleting 1lakh to 3 lakh records will not cause lock escalation and will also not bloat your log file.Runing huge delete operation can cause blocking If you really want to run it ,then run during downtime. 


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, January 10, 2014 9:41 AM
  • Shanky pls can you give me your e-mail address

    1122

    Friday, January 10, 2014 10:54 AM
  • Shanky pls can you give me your e-mail address

    1122

    shanky21smat@hotmail.com

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, January 10, 2014 12:25 PM
  • I sent you an e-mail Shanky . Pls answer me.

    1122

    Monday, January 13, 2014 8:32 AM
  • I sent you an e-mail Shanky . Pls answer me.

    1122

    Apologies for delay.I will look at it today and will reply you

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Tuesday, January 14, 2014 5:57 AM
  • Thanks bro :)

    1122

    Tuesday, January 14, 2014 1:41 PM
  • Discussed offline with OP,problem was that OP was taking backup on Network drive and SQL server service account did not had permissions .Asked him to provide read/write/modify permission on that folder to SQL server service account

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, January 15, 2014 6:29 AM