Transcript Unix System Admin

3. SQL Administration tasks
• Objectives
– Keep the databases long time running
• Contents
–
–
–
–
–
Update distribution statistics
Rebuild indexes
Maintain full-text indexes
Alerts
Maintenance Plan
• Practicals
– Rebuild and update database indexes
– Making Alerts
– Making Maintenance Plan
• Summary
Updating Distribution Statistics
• verify that the autocreate and
autoupdate statistics options are
enabled (the default) for a
database,
• right-click the database in SQL
Server Enterprise Manager, click
Properties, and then click the
Options tab. Verify that both of the
check boxes are selected.
• SQL query analyzer To manually
update statistics on a table:
$
$
$
$
man
man
man
man
man
–k tcp
passwd
5 passwd
Update statistics northwind.dbo.customers
• SQL query analyzer To manually
force create statistics on a table:
Create statistics stats1 on northwind.dbo.customers (customerid) with fullscan,
norecompute
Maintaining Indexes
• After a while, the database’s become fragmentated, this is
normally defragmented automatically.
• Indexes control the order and placement of data stored in
datapages of a table.
• Databases can become slow and start consume memory
and cpu cycles.
• This makes the need for rebuilding indexes in databases
regulary.
• Database Maintenance Plan Wizard in SQL Server
Enterprise Manager.
• You can rebuild indexes using the Transact-SQL
Drop index dbo.customers.northwind_customers_id
Create index northwind_customers_id
On northwind.dbo.customers (customerid)
Maintaining Full-Text Indexes
• Full-text indexes are indexes of all character data in one or
more tables in a database.
• use the Full-Text Indexing Wizard in SQL Server Enterprise
Manager to
– enable full-text indexing for a database, one or more tables, and specified
columns within the tables.
• use the enterprice manager -> Database -> Full text catalog
(right click) to
–
–
–
–
New
Rebuild
Re populate
Remove
Double click or right click on any full text catalogue to change its
properties/scheduling and so on
• The indexes are stored in the file system
• Full text indexes must be populated with data from
databases/tables
Configuring event Alerts
• Let the system speak to you!
• SQL Server Event Alerts
– Error number. A unique number for each error.
– Message string. Diagnostic information regarding the cause of the error, including the
object name
– Severity. Low numbers indicate information messages and high numbers indicate serious
errors.
– State code. Used by Microsoft support engineers to find the source code location for the
error.
– Procedure name. The stored procedure name if the error occurred in a stored procedure.
– Line number. The line number of a statement in a stored procedure that caused the error.
– Performance Conditions. SQL Server 2000 provides objects and counters that are used
by Windows 2000 System Monitor
• Configuring Alerts
– Using the Create Alert Wizard
– Using SQL Server Enterprise Manager Directly
Management -> SQL Server Agent -> Alert (right click to make new or click on event)
• Responses
–
–
–
–
one or more operators can be notified using e-mail, pager, or NET SEND.
A custom notification message can be added
A specified
job can also be executed
New Alert from enterprice manager
Creating a Database Maintenance Plan
• Using the Database Maintenance Plan Wizard
The Database Maintenance Plan Wizard allows you to configure the following
tasks to execute automatically according to specified schedules.
– Rebuilding indexes using a specified fill factor
– Shrinking a database to a specified size
– Updating distribution statistics
– Performing DBCC consistency checks
– Backing up database and transaction log files
– Setting up log shipping
• To start the Database Maintenance Plan Wizard, from the Tools
menu:
1. click Database Maintenance Planner,
or in the console tree, right-click Database Maintenance Plans, in
the Management container
2. then click New Maintenance Plan to display the Welcome To
The Database Maintenance Plan Wizard page.
Viewing and Modifying Database Maintenance Plans
• After the database maintenance
plan has been created, you can
view and modify it in one of two
ways.
• Management container in the
instance
– expand the SQL Server Agent
container, and then click the Jobs
container.
• Using the Sqlmaint Utility
– command-prompt utility can also be
used to create and execute a database
maintenance plan.
– Microsoft recomen using the wizard