SQL Server Maintenance Plans Jerome Espinoza Database Administrator - - PowerPoint PPT Presentation

▶
sql server maintenance plans jerome espinoza database
SMART_READER_LITE
LIVE PREVIEW

SQL Server Maintenance Plans Jerome Espinoza Database Administrator - - PowerPoint PPT Presentation

Dec 04, 2023 171 likes •752 views

RMUG 2014 Laramie County Community College SQL Server Maintenance Plans Jerome Espinoza Database Administrator Laramie County Community College 1 Housekeeping Laramie County Community College Please turn off cell phones If you must

slide-1
SLIDE 1

RMUG 2014 Laramie County Community College

SQL Server Maintenance Plans Jerome Espinoza Database Administrator Laramie County Community College

1

slide-2
SLIDE 2

RMUG 2014 Laramie County Community College

  • Please turn off cell phones
  • If you must leave the session early,

please do so as discreetly as possible

  • Please avoid side conversation during

the session Thank you for your cooperation! Housekeeping

2

slide-3
SLIDE 3

RMUG 2014 Laramie County Community College

Introduction

  • The purpose of this presentation is to

demonstrate how to implement many

  • f the ‘Best Practices’ for SQL Server

maintenance plans.

The benefit to doing this is: –Managing data and transaction log files –Eliminating index fragmentation –Ensuring accurate, up-to-date statistics –Detecting corrupted database pages –Establishing an effective backup strategy

Overview or Agenda

3

slide-4
SLIDE 4

RMUG 2014 Laramie County Community College

Introduction

  • Unfortunately, the material presented

here cannot possibly be presented in great detail because of the time allotted for one session.

  • This material will be presented

conceptually with as much detail as time permits. Overview or Agenda

4

slide-5
SLIDE 5

RMUG 2014 Laramie County Community College

Agenda

5

slide-6
SLIDE 6

RMUG 2014 Laramie County Community College

Create SQL Server Administration Objects

6

There are several custom database objects required by this maintenance plan that need to exist in each database. This section describes how to build those objects.

slide-7
SLIDE 7

RMUG 2014 Laramie County Community College

  • 1. Create SQL Server Administration Objects

7

  • A. Set each SQL Server instance to allow both SQL Server and

Windows Authentication

  • B. Create the following objects in each SQL Server instance:
  • i. A login with SA privileges (I use the same name and

password in each SQL Server instance)

  • ii. A database named DBAdmin owned by the user created

in step (i)

  • iii. A stored procedure named spDBFileSpace in the

DBAdmin database that will update a table with all of the database file sizes

  • iv. A stored procedure named spOSDiskSpace that will check

the free space to be > 10% of total disk space on each disk

  • v. A table named DB_File_Space, located in the DBAdmin

database, in which the stored procedure from step (iii) can record database file size data.

slide-8
SLIDE 8

RMUG 2014 Laramie County Community College

1.A Database Authentication

8

Open the server properties window and check the Security tab to be sure that both SQL Server and Windows Authentication are enabled for the server.

slide-9
SLIDE 9

RMUG 2014 Laramie County Community College

1.B.i Database Login.

9

slide-10
SLIDE 10

RMUG 2014 Laramie County Community College

1.B.ii DB Admin Database

10

slide-11
SLIDE 11

RMUG 2014 Laramie County Community College

1.B.iii spDBFile Space Stored Procedure

11

slide-12
SLIDE 12

RMUG 2014 Laramie County Community College

1.B.iv spOSDiskSpace Stored Procedure

12

slide-13
SLIDE 13

RMUG 2014 Laramie County Community College

1.B.v DB_File_Space Table

13

slide-14
SLIDE 14

RMUG 2014 Laramie County Community College

Maintenance Plans

14

The following pages describe how to setup SQL Server maintenance plan jobs using the SQL Server Management Studio.

slide-15
SLIDE 15

RMUG 2014 Laramie County Community College

SQL Server Maintenance Plans

15

slide-16
SLIDE 16

RMUG 2014 Laramie County Community College

SQL Server Maintenance Plans

16

slide-17
SLIDE 17

RMUG 2014 Laramie County Community College

SQL Server Maintenance Plans

17

The screen shown Above will appear. Drag a task from the toolbox into the MP and configure as shown on the following pages.

slide-18
SLIDE 18

RMUG 2014 Laramie County Community College

Database Backups

18

Database backups are the last line of defense against data loss

  • r corruption and in my

estimation the single most import duty performed by a DBA.

slide-19
SLIDE 19

RMUG 2014 Laramie County Community College

  • 2. Database Backups

19

slide-20
SLIDE 20

RMUG 2014 Laramie County Community College

  • 2. Database Backups

20

Your organization should decide what is best regarding backup storage.

slide-21
SLIDE 21

RMUG 2014 Laramie County Community College

  • 2. Database Backups

21

  • Back Up Database Task is used in this

maintenance plan

  • Backup frequency depends on the type of

recovery model/backup as per the table on the previous page.

  • Backups expire after 14 days
  • System Administrators are notified in case of job

failure

slide-22
SLIDE 22

RMUG 2014 Laramie County Community College

  • 2. Database Backups

22

slide-23
SLIDE 23

RMUG 2014 Laramie County Community College

  • 2. Database Backups

23

slide-24
SLIDE 24

RMUG 2014 Laramie County Community College

Update Statistics

24

  • Perform this function weekly to update

query optimization statistics on a table

  • r indexed view.
slide-25
SLIDE 25

RMUG 2014 Laramie County Community College

  • Updating statistics ensures that queries compile with up-to-date
  • statistics. However, updating statistics causes queries to recompile

when executed.

  • Do not update statistics too frequently because there is a

performance tradeoff between improving query plans and the time it takes to recompile queries.

  • You may want to consider updating your statistics weekly. This can

be done over the weekend, or the middle of the night, on all user databases.

  • Also, leave the Auto Update Statistics and Auto Create Statistics

properties set to true for your database.

  • The auto update will be invoked much less frequently if you manually

force an update every weekend. This is a good thing because you don’t want queries recompiling at random times throughout the business week if you can help prevent it.

  • 3. Update Statistics

25

slide-26
SLIDE 26

RMUG 2014 Laramie County Community College

  • Update Statistics Task is used in this

maintenance plan –Small databases use a full scan –Large databases scan a percentage of the entire database

  • This task is performed on all user databases
  • This task is run weekly off-hours
  • System Administrators are notified in case of

job failure

  • 3. Update Statistics

26

slide-27
SLIDE 27

RMUG 2014 Laramie County Community College

  • Perform this function weekly to check

the allocation and structural integrity

  • f user and system tables, and of

indexes in the database. Verify Integrity

27

slide-28
SLIDE 28

RMUG 2014 Laramie County Community College

  • Use the Check Database Integrity Task to check the

allocation and structural integrity of user and system tables, and indexes in the database

  • This task is run weekly off hours over the weekend
  • This is accomplished by running the DBCC CHECKDB

Transact-SQL statement

  • Running DBCC ensures that any integrity problems

within the database are reported, thereby allowing them to be addressed later by a system administrator

  • All User Databases can be checked in one maintenance

plan

  • System Administrators are notified in case of job

failure

  • 4. Verify Integrity

28

slide-29
SLIDE 29

RMUG 2014 Laramie County Community College

  • This feature will remove files related to

maintenance plans, including database backup files and reports created by maintenance plans. Maintenance File Cleanup

29

slide-30
SLIDE 30

RMUG 2014 Laramie County Community College

  • Two Maintenance Cleanup Tasks are used in

this maintenance plan –First task deletes old database backups > two weeks old –Second task deletes old transaction log backups > two weeks old

  • This task is run weekly (after the weekly full

backup jobs)

  • System Administrators are notified in case of

job failure

  • 5. Maintenance File Cleanup

30

slide-31
SLIDE 31

RMUG 2014 Laramie County Community College

  • This feature will remove log files created by

PerfMon and SQL Agent jobs that are not removed by the Maintenance File Cleanup task.

Perfmon/SQL Agent Job Log Cleanup

31

slide-32
SLIDE 32

RMUG 2014 Laramie County Community College

  • This is a custom maintenance plan designed to cleanup
  • ld PerfMon files created by the Alert and Notification

System* and SQL Agent log files not removed by the Maintenance File Cleanup task.

  • Execute SQL Server Agent Job Tasks are used in this plan
  • This maintenance plan has two subplans

–Subplan 1: PerfMon log files will be deleted after 14 days –Subplan 2: SQL Agent job log files will be deleted after 90 days

  • This maintenance plan is performed weekly
  • DBA is notified in case of job failure
  • 6. PerfMon/SQL Agent Job Log Cleanup

32

slide-33
SLIDE 33

RMUG 2014 Laramie County Community College

  • 6. PerfMon/SQL Agent Job Log Cleanup

33

slide-34
SLIDE 34

RMUG 2014 Laramie County Community College

  • 6. PerfMon/SQL Agent Job Log Cleanup

34

Create a SQL Agent job named Delete_Old_PerfMon_Logs with the properties set as follows:

  • The Type should be set to

“Operating System (CmdExec)”

  • Be sure to run as

“AdminProxy”. Will require AdminProxy creation.

  • The command should run

the batch file created on the previous page.

slide-35
SLIDE 35

RMUG 2014 Laramie County Community College

  • 6. PerfMon/SQL Agent Job Log Cleanup

35

slide-36
SLIDE 36

RMUG 2014 Laramie County Community College

  • 6. PerfMon/SQL Agent Job Log Cleanup

36

slide-37
SLIDE 37

RMUG 2014 Laramie County Community College

  • 6. PerfMon/SQL Agent Job Log Cleanup

37

Create a SQL Agent job named Delete_Old_SQLAgent_Logs with the properties set as follows:

  • The Type should be set to

“Operating system (CmdExec)”

  • Be sure to run as

“AdminProxy”.

  • The command should run

the batch file created on the previous page.

slide-38
SLIDE 38

RMUG 2014 Laramie County Community College

  • 6. PerfMon/SQL Agent Job Log Cleanup

38

slide-39
SLIDE 39

RMUG 2014 Laramie County Community College

  • This task will delete historical data

related to backup and restore activities, SQL Server Agent jobs, and database maintenance plans found in system tables. Database History Cleanup

39

slide-40
SLIDE 40

RMUG 2014 Laramie County Community College

  • The History Cleanup task deletes entries in the

following history tables in the SQL Server msdb database.

  • backupfile
  • backupfilegroup
  • backupmediafamily
  • backupmediaset
  • backupset
  • restorefile
  • restorefilegroup
  • restorehistory
  • By using the History Cleanup task, a package can delete

historical data related to backup and restore activities, SQL Server Agent jobs, and database maintenance plans.

  • 7. Database History Cleanup

40

slide-41
SLIDE 41

RMUG 2014 Laramie County Community College

  • The History Cleanup Task should be

configured as follows:

  • It should be run once a week
  • Delete SQL Agent job history
  • lder than 3 months
  • System administrators should be

notified in case of job failure

  • 7. Database History Cleanup

41

slide-42
SLIDE 42

RMUG 2014 Laramie County Community College

  • This task will run a custom stored

procedure to check the free space on system disks. Check Disk Free Space

42

slide-43
SLIDE 43

RMUG 2014 Laramie County Community College

  • This is a custom task in the maintenance plan that will

check each operating system disk to be sure it is under 90% full. If that test fails, the Alerts and Notification system will be used to email the SysAdmins and alert them that there is less than 10% free space on the disk.

  • One sure way to grind a database server to a halt is to

run low on free disk space. –If Windows needs to start paging excessively, while at the same time the database is caching table data, bad results can happen. –If a disk containing transaction log files becomes 100% full, the database will freeze until it can write the pending log entry to disk.

  • 8. Check Disk Free Space

43

slide-44
SLIDE 44

RMUG 2014 Laramie County Community College

  • This maintenance plan uses the Execute T-SQL

Statement Task

  • It should be run daily after the backup and cleanup

jobs complete

  • There will be one subplan for each physical drive on

the system to check it’s free space –Each subplan will call the spOSDiskSpace stored procedure –Each step will send a failed notice if the disk > 90% full

  • System administrators are notified in case of job failure
  • 8. Check Disk Free Space

44

slide-45
SLIDE 45

RMUG 2014 Laramie County Community College

  • 8. Check Disk Free Space

45

slide-46
SLIDE 46

RMUG 2014 Laramie County Community College

  • 8. Check Disk Free Space

46

slide-47
SLIDE 47

RMUG 2014 Laramie County Community College

  • This task will run a stored procedure to

log the space utilization of all database files in a custom table for reporting purposes. Record Database File Space Utilization

47

slide-48
SLIDE 48

RMUG 2014 Laramie County Community College

  • 9. Record Database File Space Utilization

48

slide-49
SLIDE 49

RMUG 2014 Laramie County Community College

  • 9. Record Database File Space Utilization

49

This report is an example where a programmer enabled a feature that caused the database to grow. This report helped to determine the problem at a glance.

slide-50
SLIDE 50

RMUG 2014 Laramie County Community College

  • This is done to determine when indexes

require reorganizing or rebuilding due to excessive fragmentation. Fragmentation can be costly in SQL Query performance. Checking for Index Fragmentation

50

slide-51
SLIDE 51

RMUG 2014 Laramie County Community College

  • Checking for Index Fragmentation is not done as part
  • f the maintenance plan.
  • This is done periodically by the DBA manually.
  • Fragmentation between 10% and 30% consider re-
  • rganizing an index (not rebuilding it)
  • Fragmentation over 30% is the cutoff for re-indexing.
  • Re-Indexing large tables will cause excessive logging on

full recovery model databases. –Full recovery model databases should be set to simple

  • r bulk-logged before re-indexing.

–If log shipping is in place it should be stopped before re-indexing (this of course will require a resynchronization of the databases after re-indexing has completed).

  • 10. Checking for Index Fragmentation

51

slide-52
SLIDE 52

RMUG 2014 Laramie County Community College

  • 10. Checking for Index Fragmentation

52

slide-53
SLIDE 53

RMUG 2014 Laramie County Community College

  • This is a task built into SQL Server that I

recommend to never use. Shrinking the Database

53

slide-54
SLIDE 54

RMUG 2014 Laramie County Community College

There are two options for shrinking database files:

1.

Shrinking data files – under no circumstances do this. This option should never be used as part of routine scheduled maintenance.

2.

Shrinking transaction log files – from time to time this may be needed. However, if the rest of this maintenance plan is put into action , it should never be necessary. Regular log backups should keep the transaction log file sizes in check.

  • 11. Shrinking the Database

54

slide-55
SLIDE 55

RMUG 2014 Laramie County Community College

There are three important phases in the life of a database:

  • 1. Design
  • 2. Implementation
  • 3. Maintenance

The first step is performed by the software vendor. The second step, while critical, is done once and takes the shortest amount of time. The third step goes on ad

  • infinitum. You will spend most of the life of the database

maintaining it, keeping it well tuned, and keeping the data secure.

Summary

55

slide-56
SLIDE 56

RMUG 2014 Laramie County Community College

Summary

56

slide-57
SLIDE 57

RMUG 2014 Laramie County Community College

Any Questions? Thank You! Jerome Espinoza

Database Administrator

Laramie County Community College Email: jespinoz@lccc.wy.edu Phone: 307-772-7320 Questions

57