none
T-SQL in Maintenance Plan

    Question

  • When I run this code in the query tool, it works.  But I put the exact code in a T-SQL Task of a Maintenance Plan and I get the following error:

    Status: Warning: One or more tasks failed..
    Details:
    Execute T-SQL Statement Task (ALX-ENGCSRV32)
    Execute TSQL on Local server connection
    Execution time out: 30000
    Task start: 2010-09-24T12:24:41.
    Task end: 2010-09-24T12:24:41.
    Failed:(-1073548784) Executing the query "DBCC SHRINKFILE (N'MES_HDL_Batch_log' , 0, TRUNCATEONLY) WITH NO_INFOMSGS;
    " failed with the following error: "Could not locate file 'MES_HDL_Batch_log' for database 'master' in sys.database_files. The file either does not exist, or was dropped. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Command:USE MES_HDL_Batch;
    GO
    DBCC SHRINKFILE (N''MES_HDL_Batch_log'' , 0, TRUNCATEONLY) WITH NO_INFOMSGS;
    GO

    I understand the problem but don't know how to fix it - not looking at right database in order to find the log file.

    FYI: I'm in a manufacturing environment and working with a older boxed product where transaction log just grows and grows.  We don't ever roll transactions back or anything so that's why this is acceptable for us.

    • Edited by Kirkee Friday, September 24, 2010 5:26 PM typo
    Friday, September 24, 2010 5:24 PM

All replies

  • If you don't need the Transction Log for Recovery it's a good idea to use Recovery Model "simple". Then the Log grows only to the needed size.

    To fix your problem: did you checked the permissions? The SQL Server Agent-Account should have the permission for shrinkfile.

     

    Friday, September 24, 2010 5:45 PM
  • In the job step select the correct database instead of "master". The job is failing because it is unable to find that file in the master database


    Pradeep Adiga
    My blog: http://www.sqldbadiaries.com

    Recent posts on my blog
    Friday, September 24, 2010 7:52 PM
  • I already have the recovery model set to simple but the log file still grows continuously.

    So I wanted to do the shrinkfile on the log daily.

    Monday, September 27, 2010 9:03 PM
  • In the SQL, the first line is...

                       Use MES_HDL_Batch

    which is the name of the database whose log file I'm trying to shrink.

    I don't see any way to set the database in the maintenance plan interface.

    Monday, September 27, 2010 9:05 PM
  • I already have the recovery model set to simple but the log file still grows continuously.

    So I wanted to do the shrinkfile on the log daily.


    That is unusual in the SIMPLE mode. What is @@version?

    Related link: http://sqlusa.com/bestpractices2005/shrinklog/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Saturday, October 02, 2010 2:12 PM
    Moderator
  • I already have the recovery model set to simple but the log file still grows continuously.

    So I wanted to do the shrinkfile on the log daily.


    If the log file keeps growing despite you are in simple mode, this indicates one of the following this:

    1) There is an open transaction in the database, for instance someone forgot to run commit transaction in a query window. Use DBCC OPENTRAN to find out.

    2) You are using replication, or did use at some point in time, and because there are unreplicated transaction, the log cannot be truncated.

    3) You are shrinking the log, but you run large transactions in the database, so the log expands again. If this is the case, stop shrinking the log, and let the log keep its size.

    4) You have in fact not change the recovery model of the database, use sp_helpdb to find out.

    Whatever the root problem is, shrinking is not the solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Saturday, October 02, 2010 2:55 PM