How to set up a weekly job to clear backup logs

Question

My work has asked to set up a weekly "log truncation job" on our SQL servers.

I think, I am correct in assuming (however I may be wrong) that they want me to set up a job to clear down the backup log files.

However I am new to SQL and so far I have not had to do anything like this before so I am not sure how to do this.

Currently I have no information on where the backups/logs are stored for the SQL servers.

Is there a script I can run which will tell me where the backup logs are?

Also does anyone know how to set up a job to clear the SQL server backup logs or know of a website which explains how to do this?

I found the below but I am not sure I fully understand what this means.

CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles] @path NVARCHAR(256),
@extension NVARCHAR(10),
@age_hrs INT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @DeleteDate NVARCHAR(50)
DECLARE @DeleteDateTime DATETIME

SET @DeleteDateTime = DateAdd(hh, - @age_hrs, GetDate())

    SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

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

Could anyone possible provide a little help on this?

Answer 1

The following script will show you the size of the DB's and provide you with their locations:

SELECT
    D.name                                          [Database Name]
,   F.Name                                          [File Type]
,   isnull(D.collation_name,'Unknown')              [Collation]
,   D.recovery_model_desc                           [Recovery Model]
,   F.physical_name                                 [Physical File]
,   F.state_desc                                    [Online Status]
,   F.size*8/1024/1024                              [File Size in GB]
,   F.size*8/1024                                   [File Size in MB]
--, F.size*8                                        [File Size in B]
--, *
FROM 
    sys.master_files F
    INNER JOIN sys.databases D ON D.database_id = F.database_id
ORDER BY
    D.name

In terms of truncating them - I checked and found the following: How do you clear the SQL Server transaction log?.

From the above link, I recommend you setup a SQL Job that does this...