Managing Monitoring and Troubleshooting SQL Server 2000 Databases

written by: Andrew McLaren; article published: year 2006, month 10;



In: Categories » Computers and technology » Databases » Managing Monitoring and Troubleshooting SQL Server 2000 Databases

Troubleshooting SQL Databases is an operation that supposes multiple actions. They are described below.

Optimizing Database Performance

Database performance is an important part of administering a SQL Server 2000 environment. There are many ways that performance can be increased including indexing, locking and recompiling.

Indexing Establishing indexes can dramatically increase query performance, but can also adversely affect data modification performance. Any changes to an indexed table have to be reflected in the indexes to that table, and all changes are logged. These factors combine to produce a slowdown in data modification tasks.

Locking To ensure that data changed by one user does not unexpectedly affect another user’s operations, SQL Server 2000 uses locks. Locking holds data that is in use by another process. The extent of the hold depends on the options that have been configured on the lock. Locks can decrease system performance if over-implemented. There are four transaction isolation levels that are enforced through locking:

  • 1. Read Uncommitted - Transactions are isolated enough to prevent reading physically corrupt data
  • 2. Read Committed - SQL Server default level
  • 3. Repeatable Read - Transactions acquire read and write locks on the rows that they utilize
  • 4. Serializable - Transactions are completely isolated from each other

Recompiling When a SQL statement is compiled, it is also optimized for the database object that it is working on. Optimization of a SQL statement is based on the information available at the time. Since data or structures may change over time, it may be necessary to recompile SQL statements to maintain performance.

Statistics Statistics contain information on how key values are distributed in a table. Statistics are useful in increasing query response time as they give the query engine a better idea on where to begin a search for a particular value in a table. As data changes in a table, statistics should be updated to reflect the changes. Tables can be configured to automatically create and automatically update statistics.

Optimizing Data Storage

Optimizing Filegroups There are situations when certain systems can improve performance by controlling the placement of data and indexes onto specific disk drives. can aid this process by specifying the drives on which files will reside. The system administrator can create filegroups for each disk drive and assign specific tables or indexes from a table to specific filegroups.

Managing Database Fragmentation There are two types of fragmentation in a SQL database, internal and external fragmentation. Internal fragmentation occurs when indexes are inefficiently using space. External fragmentation occurs when the physical order of pages does not match the logical order. The best way to rid your database of fragmentation is to use the DBCC INDEXDEFRAG command. This command will defragment the index while still allowing access to the table the index refers to.

Disaster Recovery

Backup Operations There are three different kinds of backups that can be performed on a SQL 2000 Server database:

  • Full backup: copies all database files, including transactions logs, data files, and indexes.
  • Differential backup: copies only the files that have changed since the last full backup was made.
  • Log backup: copies all log records that have been written to the transaction log since the last full or log backup was made.

Recovery Methods Setting the recovery method determines how quickly you can restore transaction log backups in the case of hardware failure. Recovery method also dictates the size of the resulting transaction log backup and the degree to which the database is secure from losing committed transactions upon restoration.

SIMPLE Model The simple recovery model requires the lowest amount of system resources. The system often truncates the transaction log which means that only full and differential backups are allowed.

BULK_LOGGED Model The recovery model allows you to completely restore your database in case of hardware failure. Operations are fully, but only minimally, logged. This leads to a middle ground compromise between disk space utilization, speed, and safety.

FULL Recovery Model A transaction log that operates using the full recovery model is has the least risk of losing transactions. All events are fully logged. This logging is useful for restoration but it can lead to massive transaction log sizes and disk space complications.

Log Shipping

Log shipping is a feature that backs up transaction logs and transmits those backups to a fallback server that is to be used in case of failure of the primary server. Log shipping automates these backups at a user-specified interval and automatically restores them on the backup server. To implement log shipping the logon IDs for both the original server and the fallback server must be synchronized to enable users to log into the server in case of original server failure. The SIMPLE recovery mode cannot be used in log shipping as it does not allow transaction log backups.

Integrity checks

Database Consistency Checker (DBCC) The DBCC is a collection of utilities that are used to check the integrity of a database. These utilities can be used to detect and repair problems in a database. Commands that are included in the Database Consistency Checker are preceded with DBCC. Consistency checking commands in the DBCC include the following:

  • CHECKALLOC Checks the allocation and use of all pages in the specified database
  • CHECKCATALOG Checks consistency between system tables and specified database
  • CHECKDB Checks disk space allocation and integrity of all objects in the database
  • CHECKFILEGROUP Checks the disk space allocation and integrity of all tables in the specified filegroup
  • CHECKIDENT Checks the current identity value and corrects it if needed
  • CHECKTABLE Checks the integrity of the data and indexes for a given page
  • DBREINDEX Rebuilds more indexes for a table in the specified database
  • INPUTBUFFER Shows the last statement sent to the server from the client
  • SHOWCONTIG Shows fragmentation information for the data and indexes for the specified table
  • SHOWSTATISTICS Shows the statistics for the current table
  • SHRINKDATABASE Shrinks the size of the data files in a database by a specified percentage (if possible)
  • SHRINKFILE Shrinks the size of a data or log file to a specified size (if possible)
  • TRACEON Enables a specified trace flag
  • TRACEOFF Disables a trace flag
  • TRACESTATUS Displays the status of trace flags
  • UPDATEUSAGE Reports and corrects inaccuracies in the sysindexes table
  • USEROPTIONS Returns the user options for the current user connection

Database Maintenance Plan Wizard The Database Maintenance Plan Wizard is a utility that aids in creating a maintenance plan for a SQL Server 2000 database. You can use the Database Maintenance Plan Wizard to create and schedule a backup scheme that will backup on a designated schedule or will backup based on a defined level of database activity. As part of the backup process, the Database Maintenance Plan Wizard can configure log shipping. The Database Maintenance Plan Wizard can also be used to automatically run maintenance scripts on a predefined schedule.

Troubleshooting Transactions

SQL Profiler The SQL Profiler is a program that captures events from a server. The captured events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem. The SQL Profiler is used for activities such as:

  • Stepping through problem queries to determine the causes of problems
  • Finding and diagnosing slow running queries
  • Capturing the series of SQL statements that lead to a problem
  • Monitoring the performance of SQL Server to tune workloads

SQL Server Enterprise Manager SQL Server Enterprise Manager is the primary administrative tool for SQL Server and provides an MMCcompliant user interface that allows users to:

  • Define groups of servers running SQL
  • Register individual servers in a group
  • Configure all SQL Server options for each registered user
  • Create and administer all SQL server databases, objects, logins, users, and permissions in each registered server
  • Define and execute all SQL Server administrative tasks on each registered server
  • Design and test SQL statements, batches, and scripts interactively
  • Invoke various wizards defined for SQL server

legal disclaimer

1) Our website is not responsible for the information contained by this article as well for any and all copyright infringements by authors and writers. E-articles is a free information resource. If you suspect this article for any copyright infringements, please read the Terms of service and contact us to investigate the problem.
2) The E-articles directory team is not responsible for inaccuracies, falsehoods, or any other types of misinformation this tutorial may contain and will not be liable for any loss or damage suffered by a user through the user's reliance on the information gained here. Please read the Terms of service

Useful tools and features

Translate this article to...    Send this article to you or to a friend

Link to this article from your page   
If you like this article (tutorial), please link to it from your web page using the information above. Linking to this page, this is the only way to help us improve our service, the same time providing your visitors with a way to improve their online experience.

related articles

1. Managing and Monitoring SQL Server 2000 Security
Authentication There are two methods of authentication offered SQL Server 2000, Windows authentication and SQL Server authentication. Windows authentication allows the SQL server authentication to be integrated directly with security on a Windows 2000/NT system. Authentication will depend on the security mode that SQL Server 2000 runs under, Windows Authentication mode, or Mixed Mode. If you are running SQL Server 2000, you will need to configure the SQL server to run in mixed mode to allow the non-Win...

2. Managing Monitoring and Troubleshooting SQL Server 2000
SQL Server Agent Jobs SQL Server Agent is a job scheduling agent that aids in the management of a SQL server. SQL Server Agent can be started when the operating system starts and is configured through the SQL Service Manager or Enterprise Manager. The SQL Server Agent is responsible for the execution of jobs. Jobs are made up of multiple steps that have instructions on what to do depending on the success or failure of each step in the job. Jobs are assigned to an operator, a user who is responsible for tha...

3. Tips for Designing a Better SQL Database
Keeping the following subjects in mind helps ensure that your database design and updates go smoothly. Don’t design a database that copes with the norm. Tempting as it is to design a database that covers most situations, doing so is dangerous. The unexpected happens just when you least expect it, so make sure you design your database to cover all situations that could arise, or at least ensure that it can cope with the unusual situations. Even if a client tells you not to worry about the possibility of two people attem...

4. Why and When to Use a Database
When there are a huge number of alternative ways to store data, why should you trouble yourself creating a database? What advantages does a database hold? The main advantage is fast and efficient data retrieval. A database helps you to organize your data in alogical manner. Database management systems are fine-tuned to rapidly retrieve the data you want in the way you want it. Databases also enable you to break data into specific parts. Retrieving data from a database is called querying. You’ll often see the term SQL query, which brief...

5. Creating SQL Server 2000 Databases
Attaching and Detaching Databases Detaching/attaching is used in moving a database between computers or physical disks. When a database is detached, the links from the server to the data files and transaction logs are removed. When you attach a database, you re-establish those links. When you detach and re-attach, you eliminate the need to restore the database backup manually. When a database is reattached, all files that have changed location must be specified. Filegroups Filegroups ar...

6. Developing Notification Services Applications in SQL server 2005
The process for developing Notification Services applications begins with defining the rules that govern how the application works. Next, you must compile the application. Then you need to construct an interface that allows the user to add subscriptions to the application. Finally, you’ll need to add any custom components that may be needed by the application. Let’s look at each of these steps in more detail. Defining the Application The Notification Services developer uses a combination of XML...

7. SQL Server Service Broker Activation
SQL Server Service Broker activation is another unique feature of the SQL Server Service Broker subsystem. Activation enables you to create a stored procedure that is associated with a given input queue. The purpose of the stored procedure is to automatically process messages from that queue. As each new message comes in, the associated stored procedure is automatically executed to handle the incoming messages. If the stored procedure encounters an error, it can throw an exception and be automatically recycled. ...

8. SQL Server 2005 Administrative Tools
This article gives you an overview of the SQL Server 2005 administrative tools, including: Profiler enhancements Database Tuning Advisor Assisted editors Profiler Enhancements With SQL Server 2005, Profiler gets an overhaul as well. The new Profiler now supports the ability to trace both SSIS and Analysis Services commands. SQL Server 2000 was limited to tracing relational database calls only. By having these capabilities, you can use these ...