Microsoft SQL Server agent: User.s Guide - IBM

1 IBM Tivoli Composite Application Manager for MicrosoftApplications: Microsoft SQL Server AgentVersion s GuideSC23-8880-06 IBM Tivoli Composite Application Manager for MicrosoftApplications: Microsoft SQL Server AgentVersion s GuideSC23-8880-06 NoteBefore using this information and the product it supports, read the information in Notices on page 405 This edition applies to version of IBM Tivoli Composite Application Manager for Microsoft Applications: Microsoft SQL Server agent (product number 5724-U17) and to all subsequent releases and modifications untilotherwise indicated in new editions. Copyright IBM Corporation 2005, Government Users Restricted Rights Use, duplication or disclosure restricted by GSA ADP Schedule Contractwith IBM The Configure Database Agents window232.

2 The Database Server Properties window24 Copyright IBM Corp. 2005, 2012iiiivMicrosoft SQL Server agent : User s GuideTables1. When data is collected and cache duration forattribute Key aspects of data gathering for Data that is accessed by attribute groups thatgather data from operating system orMicrosoft SQL Server Additional Names and descriptions of configurationsettings for each Interfaces for starting and stopping theMicrosoft SQL Server agent locally Cross-linked workspaces within the SQLS erver Capacity planning for historical data loggedby the Microsoft SQL Server User ID and password combinations24010. Index and table Index and table Index and table Top Processes by CPU Server Transactions Details Wait Time and Requests of Locks report26016.

3 Space Utilization by Database report26117. Free Space Availability Database Status Availability CPU Utilization Processes Details Server Lock Details Servers with Most Failed Jobs report26723. Information to gather before contacting IBMS oftware Trace log files for troubleshooting agents27225. Problems and solutions for installation andconfiguration on UNIX and Windows systems 27826. Problems and solutions for installation andconfiguration of the Microsoft SQL General problems and solutions SQL Server problems and General agent problems and solutions28730. Remote deployment problems and solutions 29631. Workspace problems and Specific situation problems and solutions30133.

4 Problems with configuring situations that yousolve in the Situation Problems with configuration of situations thatyou solve in the Workspace Problems with configuration of situations thatyou solve in the Manage Tivoli EnterpriseMonitoring Services Take Action commands problems Tivoli Common Reporting for Microsoft SQLS erver agent problems and Time periods and suffixes for summary tablesand Additional columns to report Overview of attribute groups to event classesand Copyright IBM Corp. 2005, 2012vviMicrosoft SQL Server agent : User s 1. Overview of the Tivoli in this of the IBM Tivoli Management interface SQL Server agent data groups that gather data from MicrosoftSQL Server SELECT statements and groups that gather data from operatingsystem or MS SQL Server 2.

5 agent installation language or Linux as a non-administrator installation and configuration .. 18 Granting environment and stopping the Microsoft SQL up the Microsoft SQL Server agent in acluster 3. Workspaces reference .. 41 Predefined Availability Database Database Details Services Data Mining Services Memory Statistics workspace 44 analysis Services Storage Engine QueryProcessing Services Rows Processing workspace .. 44 Availability Database Information workspace .. 45 Availability Groups Information workspace.. 45 Availability Groups Information In Replicas Information workspace .. 46 Availability Replicas and Database Replicas Status Information Mirror Detail Detail Database Mirror Detail workspace.

6 47 Enterprise Database Summary workspace .. 47 Enterprise Errorlog Alerts Log Shipping Database Processes Summary workspace .. 47 Enterprise Server Locking Servers Overview Server Statistics Server Summary Alerts Detail Detail Detail Summary Resource Type Summary workspace .. 49 Log and Space Information Shipping Database Details workspace .. 49 Log Shipping Error Details Shipping Job Details SQL Server Historical SummarizedAvailability SQL Server Historical SummarizedAvailability Daily SQL Server Historical SummarizedAvailability Hourly SQL Server Historical SummarizedAvailability Weekly SQL Server Historical Summarized SQL Server Historical Summarized CapacityDaily SQL Server Historical Summarized CapacityHourly SQL Server Historical Summarized CapacityWeekly SQL Server Historical SummarizedPerformance SQL Server Historical SummarizedPerformance Daily SQL Server Historical SummarizedPerformance Hourly SQL Server Historical SummarizedPerformance Weekly SQL

7 Server Holding Lock SQL Text workspace .. 54 Processes Batch Statistics Configuration Detail Copyright IBM Corp. 2005, 2012viiServices Detail Locking Statistics Summary Server CPU Bottleneck Server Disk Space and Logon Statistics .. 56 SQL Server Integration Service Details workspace 56 SQL Server Logical Disk Bottleneck workspace 56 SQL Server Memory Bottleneck workspace .. 57 SQL Server Physical Disk Bottleneck workspace 57 SQL Server Service Broker Server Transactions Summary workspace .. 57 SQL Text for Process ID Detail Information 4. Attributes in each attribute SQL Additional Database Details attributes 60MS SQL Additional Availability Database SQL analysis Services Data Mining attributes 63MS SQL analysis Services MDX SQL analysis Services Memory SQL analysis Services Rows SQL analysis Services Storage Engine SQL Availability Database Details attributes 85MS SQL Availability Database Details In SQL Availability Database Statistics attributes 91MS SQL Availability Database SQL Availability Groups Details attributes.

8 96MS SQL Availability Groups Details In SQL Availability Group Listener SQL Availability Groups Summary attributes 103MS SQL Availability Replicas Details attributes 105MS SQL Availability Replicas Details In SQL Availability Replicas Statistics attributes 110MS SQL Availability Replicas Status attributes 112MS SQL Availability Replicas Status SQL Batch Stats SQL Blocked Queries Details attributes .. 119MS SQL Configuration SQL Database Detail SQL Database Mirroring SQL Database Summary SQL Device Detail SQL Enterprise Database SQL Enterprise Log Shipping DB SQL Error Event Details SQL Filegroup Detail SQL FileTable Detail SQL Individual Queries Details attributes148MS SQL Integration Service Details attributes151MS SQL Job Detail SQL Job Summary SQL Lock Conflict Detail SQL Lock Detail SQL Lock Resource Type SQL Lock Summary SQL Log Shipping DB Details attributes.

9 165MS SQL Log Shipping Errors SQL Log Shipping Jobs Detail attributes .. 166MS SQL Memory Manager SQL Problem Detail SQL Problem Summary SQL Process Detail SQL Process Summary SQL Remote Servers SQL Resource Pool Stats SQL Running Queries Details attributes .. 181MS SQL Service Broker Activation attributes .. 185MS SQL Service Broker Statistics attributes .. 186MS SQL Service Broker Transport attributes .. 188MS SQL Services Detail SQL Server Detail SQL Server Properties SQL Server Summary SQL Server Transactions Summary attributes 203MS SQL Statistics Detail SQL Statistics Summary SQL Table Detail SQL Table Summary SQL Text SQL Workload Group Stats attributes.

10 213 VMS SQL Remote Servers SQL Server Enterprise View attributes .. 217 Chapter 5. Situations SQL Server agent : User s 6. Take Action Take Action Check-up and Tune-up action .. 240 Dump Database Transaction Log Fragmented Indexes Query Optimizer Statistics action .. 248 Start SQL Server SQL Server Space Usage Information action .. 250 Chapter 7. Policies 8. Tivoli Common Reportingfor the monitoring report Cognos report data models and 9. product information for IBM troubleshooting of log file of trace trace log detailed tracing in the collector trace log 274 Option one: modify the settings two: modify environment variables.