Daily Shrink Database Log Automatically Using Jobs In SQL Server

Vignesh Mani

9y124.1k6100Article

SQL ServerDaily Shrink Database LogJobs In SQL Server

6 Comments

Join the conversation! Your thoughts help the community grow.

Victor Sosa9y ago
Hi Vignesh. Shrink is not a good practice at all. That is something you should not automate at all. Shrink is only to be used in specific emergencies. Here's some good reference: https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
- Nigel Fernandes7y ago
  Yes but I guess it would be ok to do it on test environments ,every week
- Tim Schoeller7y ago
  To be fair Brent Ozar is against scheduling the shrink. He is referring primarily to the MDF database file. This article is shrinking the LDF log files. The main problem seems to be that you end up passing the storage allocation back and forth between SQL and the OS which costs resources. Check this article for more info: https://www.brentozar.com/blitz/transaction-log-larger-than-data-file/
Hariharan Krishnamoorthi9y ago
Useful Article Vignesh.. we expect more articles..
Gayathri Anbazhagan9y ago
Nice aricle...anna...good explanation.... thanks for sharing...
- Vignesh Mani9y ago
  Thank You so much Gayathri.

Introduction

This article explains how to shrink database log daily using jobs in SQL Server, step by step. We can shrink database log automatically using jobs in SQL Server that we do not need to run the SQL Script manually.

Steps for Shrinking Database

Step 1

Open SQL Server and go to SQL Server agent. SQL Server Agent should be started if stopped. Right-click SQL Server Agent and click Start.

After clicking on Start, it will ask for confirmation as “Are you sure want to start the SQLSERVERAGENT Service”. Click OK button.

Now, SQL Server Agent has started. Now, we can create jobs to shrink the database log. After starting SQL Server Agent, we can see the following screenshot.

Step 2

For shrinking database log, we need to write SQL query. Syntax and SQL query for shrinking the database log is given below.

Syntax

ALTER DATABASE Database_Name SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([DatabaseName_Log], 0, TRUNCATEONLY)
ALTER DATABASE Database_Name SET RECOVERY FULL WITH NO_WAIT
GO

Query

Here, we are using the database as “Sample”. The query for shrinking the database log is written below.

ALTER DATABASE Sample SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([Sample_log], 0, TRUNCATEONLY)
ALTER DATABASE Sample SET RECOVERY FULL WITH NO_WAIT
GO

Find database and log file

Sometimes, database name and log file may vary so we need to confirm the database and database log file names first.

Expand the database and go to our database. Now, right click your database and go to Properties. Then, click “Files” in Database Properties window and confirm database name and log file name in "Logical Name” Column.

SQL Server

Step 3

Right click on Jobs and click "New Job". The "New Job" window will open.

SQL Server

Enter the name, description, while owner and category details are entered by default.

Step 4

Go to "Steps" in "New Job" window and click “New” button at the bottom of the window.

SQL Server

After clicking the “New” button, “New Job Step” window will open. Enter step name, select type as “Transact-SQL script (T-SQL)”.

In command column, we need to pass SQL database log shrink script and click ok button. Go to step 2 of this article. We can see the database shrink query and it is detailed.

SQL Server

Step 5

Go to schedule tab “New Job” window and click “New” button. Now “New Job Schedule” window will be open. In this window type name and select the “Frequency” which days we need to run SQL script to run shrinks database log query.

We can set a specific time for running SQL script to shrink database log in “Daily frequency” then click ok button.

SQL Server

Step 6

Now we finish all settings and now click ok in “New job” window. Go to SQL Server Agent and expand the Jobs under SQL server agent, we can see our database shrink log jobs.

Step 7

We can check if our database has shrunk log file jobs and whether it's running successfully or not. Go to our job and right click to our job then click “Start job at step”. After executing we get a success message and if we have any mistakes in our script we will get an error message.

SQL Server

We can check daily if our Job is working successfully or not. Go to Jobs and right click jobs then click “View History”. Now, we can see all jobs' history.

Conclusion

This article explained how to shrink database log files automatically using SQL Jobs. I hope this is very helpful to students and freshers; and it gives some ideas to those who are new to learning SQL Server.