In this blog, we are going to cover Schedule Tasks Using SQL Server Agent In Database systems that need regular maintenance, which includes tasks like making backups and updating statistics. Maintenance may also include regularly scheduled jobs that execute against a database.
Topics We’ll Cover:
- Introduction
- Create A SQL Server maintenance plan
- What Are Extended events
- Create An Extended Events Session
Introduction
- Database systems need regular maintenance, which includes tasks like making backups and updating statistics. Maintenance may include incorporating routinely scheduled jobs that execute against a database. Some common examples of these jobs would be to extract, transform, and load data from a transaction processing system into a data warehouse.
- In SQL Server and Azure SQL oversaw cases, the SQL Server Agent administration permits you to schedule responsibilities to play out these support undertakings (just as giving other administration capacities). For Azure SQL Database and Azure Database for MariaDB/MySQL/Postgres, there are different choices accessible for scheduling maintenance operations, including Azure automation.
- One of the ways in which you can benefit from Azure is utilizing the underlying resource monitoring that the platform provides. You can likewise exploit the alternatives that the Azure platform offers for dealing with and responding to events. It’s also important to understand SQL Server’s event handling system, called extended events, and to be familiar with how you can leverage it to perform extensive monitoring of your systems.
Create A SQL Server Maintenance Plan
Typical activities that you can schedule for regular SQL Server maintenance include:
- Statistics Updates
- Database consistency checks
- Index maintenance
- Database and Transaction Log Backups
You should be aware of the importance of backups, as well as index and statistics maintenance, for all your databases. Database consistency checks, also known as CHECKDB (for the command DBCC CHECKDB) are of equal importance, as it is the only way to check an entire database for corruption.
As well as permitting you to execute database maintenance, Maintenance plans give choices to permit you to prune information from the msdb database, which goes about as the information store for the SQL Server Agent.
Maintenance designs likewise permit you to indicate that more established database reinforcements ought to be taken out from the disk. Eliminating old reinforcement records helps your SQL Server by lessening the size of your reinforcement volume and deals with the size of the msdb database.
Creating A Maintenance Plan:
You can create a maintenance plan using SQL Server Management Studio. For example, you might create a maintenance plan to back up system databases and another maintenance plan to back up user databases. You could also have another maintenance plan for special handling of the backup of one very large user database. The image below and the following examples show the creation a maintenance plan using the maintenance plan wizard.
Task Status Notification
One important part of automation is providing notifications in the event of job failure or if certain system errors are encountered.SQL Server Agent gives this usefulness through a group of objects. Alerting is most usually done by means of email utilizing the Database Mail functionalities of SQL Server.
- Notifications—notify an operator of the completion, success, or failure of a job.
- Operators—alias for people or group who receives notifications.
- Alerts—are assigned to an operator, for either a notification or a defined error condition.
1.) Notifications
Notification is part of each SQL Server Agent job. You have the option of sending a notification on Job completion, failure, or success. Most DBAs notify on failure only, to avoid an influx of notifications for successful jobs.
2.) Operators
Operators act as an alias for a user or group of users that have been configured to receive notifications of job completion, or to be informed of alerts that have been sent to the error log. An operator is defined as an operator’s name and contact information.
3.) Alerts
SQL Server Agent alerts allow you to be proactive with monitoring of your SQL Server. The agent reads the SQL Server error log and when it finds an error number for which an alert has been defined, it notifies an operator.
Extended Events
Extended events are a lightweight and very extensive diagnostic system that is built into SQL Server and Azure SQL Database and managed instances. Extended events allow you to collect additional information about the internal operations of your databases. Some examples of issues you might troubleshoot with Extended Events include:
- Logging missing column statistics
- Observing memory pressure in database
- Long-running physical I/O Operations
- Identifying long-running queries
- Troubleshooting deadlocking and blocking issues.
Create An Extended Events Session
1.) Using the New Session dialog from SQL Server Management Studio. You can get to this screen by expanding the Management node in SSMS, expanding the Extended Events node, right-clicking on Sessions, and selecting New Session.
The image above shows the first creation screen in the new Extended Events session. You must first name the session. SQL Server provides numerous templates which are grouped into the following categories:
- Locks and Blocks
- Profiler Equivalents
- Query Execution
- System Monitoring
Related/References
- Exam DP-300: Microsoft Azure Database Administrator Associate
- Microsoft Certified Azure Database Administrator Associate(Hands-On Labs)
- Azure SQL Deployment Options | SQL Managed Instance | SQL Database| SQL On VM
- Migrate SQL Server To Azure SQL Database
- Implement A High Availability And Disaster Recovery Environment
- Use External Table On Azure SQL Managed Instance To Read Data From Azure SQL Database
- Optimize Query Performance In SQL Server
- Create alerts for Azure SQL Managed Instance
Next Task For You
We will cover all the exam objectives related to how to perform migrations, Hands-On Labs, and practice tests in our Azure Database Administrator training program. If you want to begin your journey towards becoming a Microsoft Certified: Azure Database Administrator Associate by checking our FREE CLASS.
Leave a Reply