0

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?

Attie Wagner
  • 1,312
  • 14
  • 28
Jessica Nutt
  • 67
  • 1
  • 12
  • That is not what they're asking. You have to do some research first; [SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008](https://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/) is somewhere to *start*. – Andrew Morton Aug 19 '19 at 07:52
  • 1
    So i have requested my work confirm what they mean by a weekly "log truncation job" and they have said they mean clear down old backup log files. Is this the same as truncating the log files? – Jessica Nutt Aug 19 '19 at 08:04
  • I wonder if they consider the transaction log file to be a backup log file or if it is me who has not understood. Are there actually files containing records of backups? – Andrew Morton Aug 19 '19 at 08:08
  • I found a script on the site "https://www.mssqltips.com/sqlservertip/2960/sql-server-backup-paths-and-file-management/" which i have tried but i keep get the location \\CFS-HV-xxxx\{6AD0A88F-15F8-42B5-9539-E2E25FA5FDD9}1 which looks like a temp location – Jessica Nutt Aug 19 '19 at 08:17
  • Be careful not to try anything on the actual server, only try things out on a test database. There are SQL Server "error logs" which keep a record of messages. You can see them in SQL Server Management Studio (SSMS) under the "Management" -> "SQL Server Logs" section, and you can find where those files are stored by executing `sp_readerrorlog` and looking for the line which starts with "Logging SQL Server messages in file ...". But those files are normally automatically "rotated" to keep their total size limited. – Andrew Morton Aug 19 '19 at 08:22
  • I have also checked and spoken with my company and the backups do not appear to be done via SQL Server but by a seperate outside company so i have requested more information on how they are backing up the database. Thank you for your help though – Jessica Nutt Aug 19 '19 at 08:31
  • I can't imagine they want you to simply delete the database log file. I suggest you read up on [what a database log file is](https://learn.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server), and what the different modes are for such a log file. Pay special attention to [recovery models](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server) and what the implication is of each model. With that knowledge, you should come back to what is asked of you, and rethink your strategy. – TT. Aug 19 '19 at 09:04
  • To me, it sounds like they want you to purge the information about how/when a backup was made. This could typically be part of a maintenance plan, which does contain an option to clean backup history information. However, seeing you stated you use some third party tool to create backups, it might not do you much good. Still, it could be something for you to look at. – SchmitzIT Aug 19 '19 at 09:05
  • 2
    Also, please be very careful with regards to truncation of transaction logs. This can break your backup chain in case you are using transaction log backups in order to be able to restore to a point-in-time. You should typically monitor how big your transaction logs grow, and then leave it at a specific size (I usually go for typical size in peak hours +25%). This is because having the transaction log need to grow on the fly has a performance impact. Better to simply leave it at a different size, and SQL Server will clear it whenever backups are made (simplified explanation). – SchmitzIT Aug 19 '19 at 09:08
  • I ahve checked the databases and the recovery model being used on all servers is FULL. I found a script (https://dba.stackexchange.com/questions/91199/t-sql-query-to-find-backup-folder-for-specific-maintenance-plan) which from what i can see is showing FULL backups is being run hourly/daily. – Jessica Nutt Aug 19 '19 at 09:13
  • 2
    @JessicaNutt - In that case, do not mess with the transaction logs. They are *required* in order to provide a continuous list of transactions in chronological order. This ensures that if/when you have an emergency, you can restore to a point-in-time. Any deviation from your regular backup chain could jeopardize this. (Slightly exaggerated). – SchmitzIT Aug 19 '19 at 09:37

1 Answers1

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...

Attie Wagner
  • 1,312
  • 14
  • 28