Hey Jessica, let's break down your questions step by step to get you sorted out with that log truncation and backup cleanup task.

First, let's get the paths you need with simple T-SQL queries:

• To get the default backup path for your SQL Server instance:

  SELECT 
      SERVERPROPERTY('InstanceDefaultBackupPath') AS DefaultBackupPath;
  

  This returns the folder where SQL Server will save backups by default if you don't specify a path.

• To find the physical paths of transaction log (.ldf) and data files (.mdf/.ndf) for every database:

  SELECT 
      db.name AS DatabaseName,
      mf.physical_name AS FilePath,
      mf.type_desc AS FileType
  FROM 
      sys.databases db
  JOIN 
      sys.master_files mf ON db.database_id = mf.database_id
  ORDER BY 
      db.name, mf.type_desc;
  

  Look for FileType = 'LOG' to identify transaction log files for each database.

Let's walk through this proc line by line so it makes sense:

• CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles] @path NVARCHAR(256), @extension NVARCHAR(10), @age_hrs INT

  • Defines the procedure with 3 parameters:
    • @path: The folder containing your backup files (e.g., 'C:\SQLBackups\')
    • @extension: The file type to target (e.g., 'trn' for transaction log backups, 'bak' for full backups)
    • @age_hrs: How many hours old a file needs to be before deletion (e.g., 168 = 7 days)

• SET NOCOUNT ON;

  • Suppresses the "X rows affected" message, which keeps output clean for scheduled jobs.

• Cutoff date calculation:

  • SET @DeleteDateTime = DateAdd(hh, - @age_hrs, GetDate())
    • Calculates the oldest date a file can be to stay—any file older than this gets deleted.
  • SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))
    • Converts the cutoff time into a string format that the system stored procedure xp_delete_file understands (e.g., 2024-05-20T14:30:00).

• EXECUTE master.dbo.xp_delete_file 0, @path, @extension, @DeleteDate, 1

  • Runs the system proc to delete files:
    • 0: Targets backup files (use 1 for maintenance plan files)
    • @path: Folder to scan
    • @extension: File type to delete
    • @DeleteDate: Cutoff date for deletion
    • 1: Includes subfolders under @path (set to 0 if you don't want this)

Here's how to turn this into a scheduled weekly job using SQL Server Agent:

1. Create the stored procedure (if you haven't already):

   • Open SQL Server Management Studio (SSMS), connect to your instance.
   • Expand the database where you want to store the proc (e.g., master or a dedicated utility database) and run the CREATE PROCEDURE script you provided.

2. Create a SQL Server Agent Job:

   • In SSMS, expand SQL Server Agent > Right-click Jobs > Select New Job.
   • Name the job (e.g., "Weekly Backup Log Cleanup").
   • Go to the Steps tab > Click New:
     • Step name: "Delete Old Transaction Log Backups"
     • Type: "Transact-SQL (T-SQL)"
     • Database: Select the database where you created the proc
     • Command: Call the proc with your parameters (adjust values to match your environment):

       EXEC usp_DeleteOldBackupFiles 
           @path = 'C:\SQLBackups\', 
           @extension = 'trn', 
           @age_hrs = 168; -- 7 days = 24*7 hours
       

       Add separate steps if you need to delete other backup types (like .bak files).
   • Go to the Schedules tab > Click New:
     • Schedule name: "Weekly Cleanup Schedule"
     • Frequency: Weekly
     • Set the day and time (e.g., Sunday 2 AM, when server load is low)
   • Save the job.

3. Test the job:

   • Right-click the job > Select Start Job at Step... to verify it runs without errors. Check the job history if you run into issues.

• Test first: Before scheduling, run the proc manually with a safe @age_hrs (e.g., 1008 for 42 days) to ensure you don't delete critical files.
• Log truncation vs. deleting backup files: A quick clarification—truncating the active transaction log (inside SQL Server) is different from deleting backup log files. For Full/Bulk-Logged recovery mode, take regular transaction log backups to keep the active log file from growing. The stored procedure deletes the backup files (.trn), not the active log itself.
• Permissions: Ensure the SQL Server Agent service account has read/write access to the @path folder you specify.

内容的提问来源于stack exchange，提问作者Jessica Nutt