Create SQL Job to shrink database

If you are a newbie in SQL database, this post may be helpful for you to create SQL job in SQL Server 2000. SQL Jobs are similar to Windows Scheduled job. It also can be scheduled to run hourly, daily, weekly or monthly as per required, provided that SQL Server agent is on and no other external things stops the execution.

 

1. Open the SQL Server Enterprise Manager.
2. Go to Microsoft SQL Servers -> SQL Server Group (or other group) -> Open the node of required SQL Server running -> Open Management folder -> Open SQL Server Agent -> Jobs.
3. In the Right Pane, right click and select New Job.
4. In General tab, give an appropriate name (say "Shrink Database – testdb") for new job and select owner.
5. In Steps tab, click New to open a pop-up window.
6. Write an appropriate name for step (say "Step 1").
7. Type the required query to shrink database in Command multiline box.

             DBCC SHRINKDATABASE (N'testdb', 10)

             It shrinks database named "testdb". But, it keeps 10% free space in the database.

             It gives the freed space to operating system for its use.

 

8. On Advanced tab, you can choose what to do on successful completion of job and on failure. For now, you can leave it to default.
9. Click Ok to save and return to main pop up window of "Shrink Database – testdb".
10. On Schedule tab of the pop up, click New Schedule.
11. Give the name of new schedule (say "Shrink Database – testdb").
12. Select Recurring and click Change.
13. Select frequency of job and time for the job and click Ok to return.
14. Click Ok to return to main pop up window "Shrink Database – testdb".
15. If you have configured SQL main operator, you can send mail on different stages of job execution through Notification tab. I will discuss more on it in my forth coming posts. For now, you can leave it.
16. Click Ok to save the job and you are done.
17. Check if the job is executed on time or not and executed properly or not.
18. You can always run any job instantly by right clicking the job and selecting Start Job.

 

Shrinking database through SQL Jobs can definitely remove burden on database administrators to manually shrink database each time. I hope this post is helpful for new comers to create basic jobs in SQL Server.