Daily Shrink Database Log Automatically Using Jobs In SQL Server
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.
Step 3
Right click on Jobs and click "New Job". The "New Job" window will open.
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.
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.
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.
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.
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.