Sql-server – Scheduled Job Succeeds, Does Nothing; Manual Job Succeeds, Performs Requirements

The answer, it turned out, is something no one in forum land would have been able to guess at, no matter how good with SQL Server they were. It was application-level security that no one here was aware of.

The CRM database which was the source of the data for insert, contains a table of system users managed on the front end of the application. The vendor who developed the software set it up, but as they left no documentation, no one here was aware of the security layer on the back-end. It was confusing, largely, because the security only affects certain views - so all those other jobs that were working were calling for information from tables and views that were not influenced by the security.

When testing logged in as me, the procedure worked because I am set up in the CRM table as an administrator, but the credentials used to run SQL Server agent don't even exist in the CRM user table.

Had nothing to do with SQL Server security, but everything to do with permissions (didn't see that coming)...

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.