Sql-server – SQL Job Archival Process Clarifications

Using Kenneth's code, and a few other bits and pieces that I pulled together, I came up with the following code which I think should do the job. If anybody sees any potential problem with this I'd be interested in knowing!

-- Check if this server is the preferred replica for backups.
    If sys.fn_hadr_backup_is_preferred_replica( @DatabaseName ) <> 1
    BEGIN

        -- Check whether the script is running from SQL Agent.  If it is not then raise an error so that the user knows that the backup hasn't completed.
        -- (If the job is run from the agent then there should be an equivalent job running on the preferred replica, so no need to alert).
        declare @AgentAccount varchar(100)
        set @AgentAccount = (select cast(value_data as varchar(100)) as [AccountName]
                                FROM sys.dm_server_registry
                                WHERE registry_key LIKE '%SQLSERVERAGENT%' --For named instance change to SQLAgent$<instanceName>
                                AND value_name = 'ObjectName'
                            )


        if @AgentAccount = SYSTEM_USER
        begin

            declare @jobSource tinyint
            SELECT  @jobSource = ja.run_requested_source
                    --WHEN 1 THEN 'SOURCE_SCHEDULER'
                    --WHEN 2 THEN 'SOURCE_ALERTER'
                    --WHEN 3 THEN 'SOURCE_BOOT'
                    --WHEN 4 THEN 'SOURCE_USER'  -- This one is manual
                    --WHEN 6 THEN 'SOURCE_ON_IDLE_SCHEDULE'
                    --ELSE 'UNKNOWN' END
            FROM msdb.dbo.sysjobactivity ja
            WHERE ja.session_id = @@SPID
            AND start_execution_date is not null
            AND stop_execution_date is null;

            if @jobSource = 4   -- Job was run manually.
            begin
                RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
                return 1
            end
            else
            begin
                print 'No backup produced as this server is not the preferred replica for backups.'
                return 0
            end
        end
        else
        begin
            RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
            return 1
        end
    END

The answer is you cannot shrink log file if there is no free space available.

Shrinking can occur only while the database is online and, also, while at least one virtual log file is free. In some cases, shrinking the log may not be possible until after the next log truncation.

Can you run DBCC LOGINFO(DB_name) and see what is last value in status column. If value is 2 you cannot shrink if value is 0 you can because 0 means VLF is truncated and no active transaction is using it. You can read how shrinking of Transaction Log works

A log file can only be shrunk to a virtual log file boundary, shrinking a log file to a size smaller than the size of a virtual log file might not be possible, even if it is not being used. The size of the virtual log file is chosen dynamically by the Database Engine when log files are created or extended. It wont give you failure message but wont even shrink. I also think there is no free space in log file which can be freed and you are just forcing it to free it. What is the query you are using to shrink database log file can you please show it.

You must read This BOL article on shrinking a log file. What is output of below query

Use Db_name
GO
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

Next question would be why are you shrinking Log file every day is it really causing issue. Shrinking of log file is also a evil operation because when log file grows process running has to wait for it to grow and since instant file initialization is not there for user database log files every time log file grows it has to Zero out space and write the the information there. This could be a time consuming on busy system where disk I/O is heavily utilized so moral is don't shrink log files. by shrinking you are just allowing it to grow again and it will grow again because SQL Server has to write information in log files related to queries running on system

You can pre-size it and provide necessary space to log files so as to avoid autogrowth events.

Make sure you keep proper value for Autogrowth setting don't keep it in percentage as it will cause unnecessary growth. More about autogrowth can be read from this link. See by how much SQL Server is growing and you would allocate that much space for a month to database log file so as to avoid and autogrowth events.