Sql-server – SQL Server : scheduled JOB issue

sql serversql-server-agent

I have one issue in my live server, I created a job to shrink the log file and I have scheduled it to run every day with sa user

When I run this job manually from Management Studio, it is run successfully and shrinks the log file of database.

I have also checked the history for this job and it is running every day as per the scheduled time, but the issue is that that when it is run as per schedule that time it is run successfully but doesn't shrink the log file, the log file size stays the same size.

Can anyone help me to find what is the issue and how I can solve it?

Best Answer

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.

Related Question