Get documents about "ADM520_620_Col32_FV - BASIS
Document Sample
FS310 Inkasso/Exkasso
ADM520
Diese Seite wird von Andrea für euch noch erstell!
ADM520
<leave this slide blank>
Database Administration MS SQL Server
THE BEST-RUN BUSINESSES RUN SAP
SAP AG© SAP AG 2003
2003
SAP Web AS 6.20
2003/Q2
50062304
Copyright
Copyright 2003 SAP AG. All rights reserved.
No part of this publication may be reproduced or transmitted in
any form or for any purpose without the express permission of
SAP AG. The information contained herein may be changed
without prior notice.
All rights reserved.
SAP AG 2003
Trademarks:
Some software products marketed by SAP AG and its distributors contain proprietary software components of
other software vendors.
Microsoft®, WINDOWS®, NT®, EXCEL®, Word®, PowerPoint® and SQL Server® are registered trademarks
of Microsoft Corporation.
IBM®, DB2®, DB2 Universal Database, OS/2®, Parallel Sysplex®, MVS/ESA, AIX®, S/390®, AS/400®,
OS/390®, OS/400®, iSeries, pSeries, xSeries, zSeries, z/OS, AFP, Intelligent Miner, WebSphere®, Netfinity®,
Tivoli®, Informix and Informix® Dynamic ServerTM are trademarks of IBM Corporation in USA and/or other
countries.
ORACLE® is a registered trademark of ORACLE Corporation.
UNIX®, X/Open®, OSF/1®, and Motif® are registered trademarks of the Open Group.
Citrix®, the Citrix logo, ICA®, Program Neighborhood®, MetaFrame®, WinFrame®, VideoFrame®, MultiWin®
and other Citrix product names referenced herein are trademarks of Citrix Systems, Inc.
HTML, DHTML, XML, XHTML are trademarks or registered trademarks of W3C®, World Wide Web
Consortium, Massachusetts Institute of Technology.
JAVA® is a registered trademark of Sun Microsystems, Inc.
JAVASCRIPT® is a registered trademark of Sun Microsystems, Inc., used under license for technology invented
and implemented by Netscape.
MarketSet and Enterprise Buyer are jointly owned trademarks of SAP AG and Commerce One.
SAP, SAP Logo, R/2, R/3, mySAP, mySAP.com, and other SAP products and services mentioned herein as well as
their respective logos are trademarks or registered trademarks of SAP AG in Germany and in several other
countries all over the world. All other product and service names mentioned are the trademarks of their respective
companies.
Target Audience
This course is intended for the following audiences:
Database administrators
SAP system administrators
Project team members
Duration: 3 days
SAP AG 2002
User notes
These training materials are not a teach-yourself program. They complement the explanations
provided by your course instructor. Space is provided on each page for you to note down additional
information.
There may not be sufficient time during the course to complete all the exercises. The exercises provide
additional examples that are covered during the course. You can also work through these examples in
your own time to increase your understanding of the topics.
Course Prerequisites
Required Knowledge:
SAP50 (mySAP.com Technical Fundamentals) or
SAPTEC (mySAP Technology Solution Fundamentals)
Working knowledge of the MS SQL Server 2000 database
and the operating system Windows NT or Windows 2000
Recommended Knowledge:
For the administration of SAP systems the courses BCtcc
(Technical Core Competence) or ADM100 (mySAP
Technology Administration) are necessary
SAP AG 2002
SQL Server Architecture
1 Introduction
2 SQL Server Architecture
3 How the SAP System uses SQL Server
4 Performance Monitoring and Tuning
5 Database Backup
6 Database Restore
7 Regular Maintenance and Error Handling
SAP AG 2002
© SAP AG ADM520 1-1
SQL Server Architecture
Contents
Architecture
Database Server and Instances
Authentication
Databases and files
Database objects
Logging
Locking
Objectives
At the end of this unit, you will be able to:
Describe the main features of the SQL Server architecture
SAP AG 2002
© SAP AG ADM520 1-2
Database Server
Database server, a process
running the database system
SQL Server
Windows server
Database 1 Database 2
SAP AG 2002
The term "database server" is often used to describe the physical computer on which the database
runs.
For the purpose of this explanation of SQL Server architecture, the term "database server" will refer
to the operating system process on Windows that represents the active, programmed side of the
database system. All management of the database system is performed through and by the server,
and all communication with the database systems goes through the server.
When the database server is running, the database system is up and you can connect to the databases.
When the database server is not running, the databases are down.
© SAP AG ADM520 1-3
Services
SQL Server Enterprise Manager - [Console P
Console Window Help
Action View Tools
Console Root
Microsoft SQL Servers
SQL Server Group
sapprod [Windows NT] Services viewed in the Enterprise
}
Distributed Transaction Co
Manager
Destination
SQL Server Agent
R3DUMPO
Services viewed in Windows
Distributed Transaction .. Coordinates Transac… Manual Local System
MSSQLSERVER Started Automatic PRDadm
MSSQLServerAdHelper Manual Local System
SQLSERVERAGENT Started Automatic PRDadm
SAP AG 2002
Under Windows, a program can be started as a service. A service runs in the background and has no
graphical user interface. To automatically or manually start and stop services at system startup, use
the Control Panel Services.
Microsoft SQL Server has the following services:
MSSQLServer: Database server
SQLServerAgent: Automatic task execution and SQL Server alert handling
Distributed Transaction Coordinator: distributes transactions over multiple SQL Servers
MSSQLServerAdHelper: Adds and removes objects used to register instances of SQL Server and
ensures that the Windows account under which an SQL Server service is running has permissions
to update all of the Active Directory objects for the instance, as well as any replication
publications and databases for that instance.
To start and stop these services, use the SQL Service Manager or the Enterprise Manager. The
service MSSQLServer can also be started remotely from another computer. Starting SQL Server is
the same as starting the service MSSQLServer.
The SQLServerAgent service must be started automatically together with MSSQLServer.
© SAP AG ADM520 1-4
Trace Flags
Trace Flags can be set using
Command Prompt
Startup parameters in the Enterprise Manager
SAP AG 2002
SQL Server can be started and stopped manually using the command prompt by typing:
net start mssqlserver or sqlservr, or net start SQLServerAgent or by running SQLSERVR.EXE.
By specifying the –T, SQL Server should be started with a specified trace flag in effect. Trace flags
are used to start servers with nonstandard behavior. This way a better understanding of SQL Server
or even a change in the way SQL Server will react on specific conditions is provided.
SQL Server can also be started with trace flags using the Startup dialog box in the Enterprise
Manager.
© SAP AG ADM520 1-5
Instances of SQL Server
SQL Server default instance MSSQLSERVR
sapprod SQLSERVERAGENT
master msdb tempdb model
SQL Server named instance MSSQL$sapprod\SQLServer
sapprod\SQLServer SQLAgent$sapprod\SQLServer
master msdb tempdb model
Windows server
SAP AG 2002
SQL Server 2000 supports multiple instances of the SQL Server database engine running
concurrently on the same computer. Each instance of the SQL Server database engine has its own set
of system and user databases that are not shared between instances.
There are two types of instances of SQL Server:
Default Instances: The default instance is identified by the name of the computer on which the
instance is running. When a client program such as the Query Analyzer specifies only the
computer name in its request to connect to SQL Server, a connection to the default instance of the
database engine on that computer is established. There can only be one default instance on any
computer, the default instance can be of any version of SQL Server.
Named Instances: All instances of the database engine other than the default instance are identified
by an instance name specified during installation of the instance. Clients must provide both the
computer name and the instance name of any named instance to which they are attempting to
connect. The computer name and instance name are specified in the format
computer_name\instance_name.
There can be multiple named instances running on a computer. Only database engines from version
SQL Server 2000 can operate as a named instance.
© SAP AG ADM520 1-6
Client/Server
Workstation A Workstation B
Client X Client Y
ODBC / OLE DB / DB Library ODBC / OLE DB / DB Library
Named pipes TCP/IP sockets
TCP/IP IPX/SPX TCP/IP
Named pipes TCP/IP sockets
ODS (Open Data Services)
SQL Server
Windows server
SAP AG 2002
A database server is an application that runs as a system process on a computer. Client tools (for
example, Query Analyzer) communicate with the database server through network protocols such as
TCP/IP or NW Link IPX/SPX Compatible Transport, the native protocol of Novell NetWare
networks.
The network protocol used by the database server determines the method used for inter-process
communication, for example, named pipes or TCP/IP sockets. These methods are installed as net
libraries (DLLs) using the program SQL Setup. Only one net library is active on a client. To
configure the library, use the program Client Network Utility.
Several application programming interfaces (APIs) are available. For example, OLE DB and DBLib.
Other client applications can use the ODBC interface.
© SAP AG ADM520 1-7
Processes and Threads
.. n ...
Thread 1 Thread 2 Fiber 1 2 Thread N Process
Windows server
SAP AG 2002
A process is a program that is currently being executed. Under Windows, to keep processes separate
from each other and prevent interference, each process has its own address space. The process is also
the owner of all the program resources, such as file handles and access tokens.
Windows 2000 allows a process to manage a 4GB range of linear addresses. An application process
is able to control the lower portion of this space (either 2GB or 3GB). The upper portion is managed
by system code for that particular process.
The basic unit of activity in Windows is the thread. Each process has at least one thread, and may
have multiple threads. Windows assigns time slices on physical processors to threads. All threads
can run concurrently. Since there is no address space switch involved, switching between threads
within the same process is much faster than a conventional process switch in operating systems that
run without the thread.
SQL Server has an internal layer that implements an environment similar to an operating system for
scheduling and synchronizing concurrent tasks without having to call the Windows kernel. This
internal layer can schedule fibers as effectively as it works with threads. A fiber is a unit of
execution. Fibers run in the context of the threads that schedule them. Each thread can schedule
multiple fibers.
The server configuration lightweight pooling parameter controls whether SQL Server uses threads or
fibers. The default is 0, in which case SQL Server schedules a thread per concurrent user command.
If lightweight pooling is set to 1, then SQL Server uses fibers instead of threads. For more
information on the lightweight pooling parameter, please refer to the section SQL Server
Configuration in the Unit 6: Regular Maintenance and Error Handling.
© SAP AG ADM520 1-8
Processes and Threads (2)
Workstation A Workstation B
Client Client
process X process Y
OLE DB OLE DB
Named pipes TCP/IP sockets
TCP/IP IPX/SPX TCP/IP
Named pipes TCP/IP sockets
ODS (Open Data Services)
SQLSERVR
Thread 1 Thread 2 Thread 3 ... Thread N
process
Windows server
SAP AG 2002
SQL Server runs in an operating system process called SQLSERVR. This operating system process
contains threads for the operating system and threads for clients logged on to the server.
Every connection between a client and SQL Server uses one of these threads. SQL Server maintains
a pool of either threads or fibers for user connections. The maximum size of this pool is controlled
by the max worker threads server configuration parameter. One thread can serve several connections.
For more information, please refer to the section SQL Server Configuration in the Unit 6: Regular
Maintenance and Error Handling.
© SAP AG ADM520 1-9
Special Threads
Lazy Writer
Lock Manager
Log Writer
Checkpoint Manager
Background Task
SAP AG 2002
The Lazy Writer thread scans the data cache to write dirty pages to disk. The Lazy Writer thread
sleeps for an interval of time. When it is restarted, it checks the size of the free buffer list. If the free
buffer list is below a certain point, the Lazy Writer thread scans the buffer cache to write dirty pages
to disk. Most of the work writing dirty pages is done by the user threads and the Lazy Writer thread
typically finds little to do.
The Lock Manager dynamically adjusts the resources it uses for larger databases, eliminating the
need to adjust the locks server configuration parameter manually. Details on the locking mechanism
are explained in later slides.
The Log Writer records are written asynchronously by a log writer thread, except when:
A commit forces all pending records for a transaction to disk
A checkpoint forces all pending records for all transactions to disk
The Checkpoint thread scans the buffer cache for dirty pages and writes to disk any buffer page that
are marked as dirty. Checkpoints typically find few dirty pages to write to disk because most dirty
pages get written to disk by the worker threads or Lazy Writer thread in the period between two
checkpoints.
The Background task checks every 30 minutes if the database or the transaction log files can be
shrunk, in case the database option autoshrink is set. It also starts every 5 seconds and checks on 20
data pages if they contain ghost records. These are records which are deleted logically but not
physically.
© SAP AG ADM520 1-10
Security Modes
SQL Server supports security modes for authentication
SQL Server security
Trusted security
Mixed security
SAP AG 2002
SQL Server must verify that the login id supplied on each connection request is authorized to access
the database server. This process is called authentication.authentication.
SQL Server supports three security modes for authentication:
SQL Server security: a connection to SQL Server is established through a SQL Server login and
password, e.g. using login id sa
Trusted security: a connection to SQL Server is established using the Windows user account.
When a user connects to SQL Server through a Windows user account, SQL Server verifies that
the account name and password were validated when the user logged on to the operating system.
The SQL Server client then requests a trusted connection to SQL Server. The properties of a
trusted connection include the Windows group and user accounts of the client that opened the
connection.
A member of the SQL Server sysadmin fixed server role, for example login id sa, must first
specify to SQL Server all the Windows accounts or groups that can connect to SQL Server.
Mixed security: Mixed security supports both, SQL Server security and trusted security.
© SAP AG ADM520 1-11
Databases and Files
Databases
master msdb tempdb model
Files
.ldf .ldf .ldf .ldf
.mdf .mdf .mdf .mdf
SAP AG 2002
The system databases are:
master holds all the system level information for a SQL Server system. It stores all login accounts
and all system configuration parameters.
model is used as the template for all databases created on SQL Server.
msdb is used by SQL Server Agent for scheduling alerts and jobs.
tempdb holds all temporary tables and temporary stored procedures for all users connected to SQL
Server. It also fills any other temporary storage needs such as work tables generated by SQL
Server. Every time SQL Server is started, tempdb is re-initialized. The initialization is recorded as
“clearing tempdb” in the error log.
Northwind and pubs are sample databases that are provided as learning tools.
SQL Server maps a database over at least two operating system files. Data and log information are
never contained in the same file, and individual files are used only by one database.
The primary file (.mdf) contains the startup information and system tables for the database and is
used to store data. Every database has one primary file. Secondary files (.ndf) hold data that does not
fit in the primary data file. Databases do not require any secondary data files if the primary file is
large enough to hold all the data in the database. Transaction log files (.ldf) contain the log
information used to recover the database. There must be at least one log file for each database.
© SAP AG ADM520 1-12
Files and Filegroups
Objects Filegroups
System tables and RAID 5
other user tables PRIMARY
RAID 1
Transaction log Log files
RAID systems
SAP AG 2002
Data files are assigned to one filegroup. A single table can use space in several different files within
the same filegroup.
The PRIMARY filegroup is created when SQL Server is installed.
© SAP AG ADM520 1-13
Automatic Growth
Database files
Volume File
+ : ...
(Autogrow ONLY for out-of-space failure)
Log files
+ ....
: Free files
SAP AG 2002
Database files only grow automatically in a filegroup if the option autogrow is set and no further
space is available on any of the database files in the filegroup.
Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written
to the filegroup, SQL Server writes an amount proportional to the free space in the file to each file
within the filegroup, rather than writing all the data to the first file until full and then writing to the
next file. For example, if File1 has 100 MB free and File2 has 200 MB free, one extent is allocated
from File1, two extents from File2, and so on. This way both files become full at about the same
time and simple striping is achieved.
Transaction log files, however, cannot be part of a filegroup; they are separate from one another. As
the transaction log grows, the first log file fills, then the second, and so on, using a fill-and-go
strategy rather than a proportional fill strategy. Therefore, when a log file is added, it may not be
used by the transaction log until the other files have been filled first.
© SAP AG ADM520 1-14
Space Allocation
Page File
Tables X Page 8
Extents
Row 1 Row 1 0 1 2 3 4 5 6 7 1
Row 2
Row 2 8 9 10 11 12 13 14 15 2
Row 3
Page 17 16 17 18 19 20 21 22 23 3
Row 3 8 Pages = 1 Extent
SAP AG 2002
The page is the unit of data storage in SQL Server. Pages are 8 KB in size. SQL Server allocates
pages to objects and reuses space freed up by deleted rows. These operations are internal to the
system and use data structures not visible to users.
Extents are the basic unit in which space is allocated to tables and indexes. An extent consists of
8 contiguous pages, or 64KB. A new table or index allocates first pages from mixed extents. That
means extents can contain pages from different objects. Extents are called uniform, when a table or
index allocates all eight pages.
Log files do not contain pages, they contain a series of log records, allocated on virtual log files.
© SAP AG ADM520 1-15
Space Allocation (2)
First extent on each file
8 contiguous pages = 1 extent (64 KB)
File Boot
header PFS GAM SGAM BCM DCM page ...
0 1 2 3 6 7 8
File header Contains information about the attributes of the file
PFS Page allocation / free space availability (in %)
Page Free Space
(S)GAM Usage of extents (free, mixed, full)
(Secondary) Global Allocation Map
BCM Changes of extents when in Bulked_logged recovery mode
Bulk Changed Map
DCM Changes of extents since last database backup
Differential Changed Map
Boot page Contains information about the attributes of the database
exists only in the primary data file
SAP AG 2002
File Header is a special page that contains information about the file.
Page Free Space (PFS) pages record whether an individual page has been allocated, and the amount of space free on each page. Each PFS
page covers 8000 pages. For each page, the PFS has a bitmap recording whether the page is empty, 1-50% full, 51-80% full, 81-95% full,
or 96-100% full. Once an extent has been allocated to an object, SQL Server uses the PFS pages to record which pages in the extent are
allocated or free, and how much free space is available for use.
Global Allocation Map (GAM) pages record which extents have been allocated. Each GAM covers 64000 extents, or nearly 4 GB of data.
The GAM has one bit for each extent. If the bit is 1, the extent is free; if the bit is 0, the extent is allocated.
Shared Global Allocation Map (SGAM) pages record which extents are currently used as mixed extents and have at least one unused
page. Each SGAM covers 64000 extents, or nearly 4 GB of data. The SGAM has one bit for each extent it covers. If the bit is 1, the
extent is being used as a mixed extent and has free pages; if the bit is 0, the extent is not being used as a mixed extent, or it is a mixed
extent whose pages are all in use.
Index Allocation Map (IAM) pages map the extents in a database file used by a heap or index. Each heap or index has one or more IAM
pages recording all the extents allocated to the object. A heap or index has at least one IAM for each file on which it has extents. A heap
or index may have more than one IAM for a file if the range of the extents for the heap or index on that file exceeds the range that an
IAM can record.
Bulk Changed Map (BCM) pages record the extents which have been changed by bulk operations such as CREATE INDEX since the last
transaction log backup. If the bit is 1, the extent has been changed by a bulk operation, if the bit is 0, the extent has not been changed.
BCM pages are only relevant when the recovery model of the database is set to bulk_logged.
Differential Changed Map (DCM) pages record which extent has changed since the last execution of BACKUP DATABASE. Each DCM
covers 64000 extents, or nearly 4 GB of data. If the bit for one extent is 1, the extent has been changed since the last BACKUP
DATABASE, if the bit is 0, the extent has not been changed. See also Unit 4: Database Backup.
The Boot page is the ninth page in the database file, that is, the first page of the second extent. It is stored in the primary database file and
in the first transaction log file. The boot page contains attributes of the database. It records attributes which are needed for an automatic
recovery. See also Unit 5: Database Restore.
© SAP AG ADM520 1-16
Free Space
PRD
Database
Owner: prdadm
Date created: 02/22/2002 05:55:54
Size: 65.000,00 MB
Space available: 15.664,99 MB
Database options: normal
Number of users: 2
Maintenance
Last database backup: 10/22/2002 06:00:54
Last differential backup: None
Last transaction log backup: 10/22/2002 10:00:54
Maintenance plans: None
Space Allocated
Data:
PRDdata1: 20000 MB 15655,59 MB 4344,31 MB
PRDData2: 20000 MB 15888,19 MB 4111,81 MB
PRDdata3: 20000 MB 15823,69 MB 4176,31 MB
Transaction Log space: 4999,99 MB 73,53 MB 4926,46
Total Used Free
SAP AG 2002
The log and data usage of a database can be displayed using the taskpad view in the Microsoft
Management Console of the Enterprise Manager. The space used for each data file and the
transaction log is shown.
In the first section the size of the database is shown as the size of the data and the transaction log.
The space available shows the space available for logging information and for data.
The information returned by transact-SQL command DBCC SQLPERF(LOGSPACE) can be used to
monitor the amount of log space used and indicates when to back up the transaction log.
System stored procedure sp_spaceused computes the amount of disk space used for data and indexes.
When updateusage is specified, SQL Server scans the data pages in the database and makes any
necessary corrections to the sysindexes table regarding the storage space used by each table. There
are some situations, for example, after an index is dropped, when the sysindexes information for the
table may not be current. This process can take some time to run on large tables or databases. The
command DBCC UPDATEUSAGE can be run separately.
© SAP AG ADM520 1-17
Database Objects
Logical Database Objects
Name xyz abc
§
Tables Views Constraints Stored Indexes
Procedures
Name xyz abc
System
Representation
System Tables
Physical
Memory
Pages Pages Pages
SAP AG 2002
An SQL Server database consists of tables that contain data and other objects such as views, indexes,
and stored procedures defined to support the activities performed on the data.
These database objects are stored on pages in physical memory, along with other data objects
defined in the system tables.
© SAP AG ADM520 1-18
Non-clustered Index (on a table with no clustered
index)
Level n Page pointer
Root Index
Atlanta
Pages
Level 1 New York indid >= 2
Intermediate nodes
Atlanta New York
Berlin Paris
Heidelberg Walldorf
L.A. Washington
Level 0 London
Leaf nodes nonclustered
Manchester rid
key values
rid
Data
Heidelberg, London, Berlin, New York, Pages
Germany United King. Germany USA indid = 0
L.A., Manchester, Washington,
Rid (row identifier):
USA United King. USA
- File ID,
Atlanta, Paris, Walldorf,
- Page number,
USA France Germany
- Row number
SAP AG 2002
Indexes are used to speed up searching for records in tables. Indexes can be created for a frequently
used search field or a combination of search fields.
SQL Server has two types of indexes:
Non-clustered index
Clustered index
A non-clustered index is a B tree, which is searched from the highest level to the data pages. A
search through the index requires the following page accesses:
One page for each index level, and one access on each data page(s).
This graphic displays data and index pages in a table. The search fields are named for City and
Country and contain a non-clustered index in the field City. With a non-clustered index, index and
data pages are divided, so that you can create as many non-clustered indexes for a table as needed.
The location of a data row is contained in each leaf level of the non-clustered index and is identified
by a record identifier (RID) comprised of the file number, page number, and slot number of the row.
© SAP AG ADM520 1-19
Clustered Index
France,
Paris Index
Germany, Pages
Level 1 Walldorf.
(indid=1)
USA,
Atlanta
USA, key
Washington Page pointer
Level 0 Data
(indid=0) France, Germany, USA, USA, Pages
Paris, Walldorf, Atlanta, Washington,
Europe Europe North America North America
Germany, United King., USA,
Berlin, London, L.A.,
Europe Europe North America
Germany United King., USA,
clustered
Heidelberg, Manchester, New York,
key
Europe Europe North America
SAP AG 2002
A clustered index dictates the physical storage order of the data in the table. That means a table can
contain only one clustered index. All inserts made fit in the ordering sequence of the clustered index
key.
A clustered index is organized as a B tree. Each page in a clustered index holds a page header
followed by index rows. Each clustered index row contains a key value and a pointer to either a page
or a data row. Each page in a clustered index is called an index node. The top node of the B tree is
called the root node. The bottom nodes in the clustered index are called the leaf nodes. In a clustered
index, the data pages make up the leaf nodes. Index levels between the root and the leaves are known
as intermediate levels.
For a clustered index, root points to the top of the clustered index. SQL Server navigates down the
clustered index to find the row corresponding to a clustered index key. To find a range of keys, SQL
Server navigates through the clustered index to find the starting key value in the range, and then
scans through the data pages. To find the first page in the chain of data pages, SQL Server follows
the leftmost pointers from the leaf node of the clustered index.
© SAP AG ADM520 1-20
Non-clustered Index with a Clustered Index
Non-clustered Index
Index
Index Pages
USA, Atlanta USA, Pages
Atlanta New York
Atlanta New York New York
Germany, France,
Berlin Paris
Berlin ... Paris
Germany, Germany,
Heidelberg Walldorf
Heidelberg Walldorf
USA, USA,
L.A. Washington
L.A. Washington
United King.,
London
London
United King., clustered key clustered key
Manchester
Manchester values values
Clustered Index search via clustered index key (country, city)
root Index
Pages
Level 1 (indid 1)
Level 0 (indid 0)
leaf nodes Data
Pages
SAP AG 2002
If a table has a clustered index and a clustering key, the leaf nodes of all non-clustered indexes use
the clustering key as the row locator rather than the physical record identifier (RID). If a table does
not have a clustered index, non-clustered indexes continue to use the RID to point to the data pages.
In both cases, the row locator is stable. When a leaf node of a clustered index is split (data page), the
non-clustered indexes do not need to be updated because the row locators are still valid. If a table
does not have a clustered index, page splits do not occur.
© SAP AG ADM520 1-21
Logging
SQL Server Query Analyzer
detail stats.
BEGIN TRAN
SQL command UPDATE
Cache
Files
Data Log
SAP AG 2002
A database transaction is a series of SQL commands that are completed, or not executed at all. A
transaction is started with the command BEGIN TRAN. All commands within one transaction are
handled as one atomic command by SQL Server .
Each SQL Server database has a transaction log that records data modifications made in the
database. The log records the start and end of every transaction and associates each modification
with a transaction. SQL Server stores the information in the log to either redo (roll forward) or undo
(roll back) the data modifications that make up a transaction. Each record in the log is identified by a
unique log sequence number (LSN).
© SAP AG ADM520 1-22
Commit
SQL Server Query Analyzer
detail stats.
SQL command COMMIT TRAN
Cache
.....
Files
Data Log
SAP AG 2002
A transaction is ended with the command COMMIT TRAN or ROLLBACK TRAN. SQL Server
then writes all logging information from this transaction to the log file and sends a message to the
application program confirming the COMMIT TRAN. This ensures that all confirmed changes from
SQL Server are logged onto a physical disk. Records to be changed are first stored in a cache.
Sometimes the records to be changed are only up to date in the cache.
© SAP AG ADM520 1-23
Checkpoints
SQL Server Query Analyzer
detail stats.
SQL command CHECKPOINT
Cache
.....
Files
Data Log
SAP AG 2002
During a checkpoint, all dirty data and log pages in the cache are written to the files.
There are 2 kinds of checkpoints:
Manual checkpoint (transact-SQL command Checkpoint)
Automatic checkpoint
SQL Server configuration parameter recovery interval controls when SQL Server issues a checkpoint
in each database. Checkpoints are done on a per database basis. The recovery interval sets the
maximum number of minutes per database that SQL Server needs to recover databases. The default
is 0, indicating automatic configuration by SQL Server. This means a recovery time of less than one
minute and a checkpoint approximately every one minute for each database.
When a database is set to use the simple recovery model, logging information from the log is erased
at each automatic checkpoint. This option is useful for databases that require many changes, and
have minimum security requirements, for example database msdb and tempdb. If this mode is set,
the restore cannot apply the transaction logs written later than the checkpoint. Perform a full backup
immediately after disabling this option.
© SAP AG ADM520 1-24
Checkpoints: Logical Sequence Number
SQL Server Query Analyzer
detail stats.
Boot page SQL Server Query Analyzer
detail stats.
ISQL/w
Begin Transaction
Update MONI set KEY = ‘4711’ where KEY= ‘0000’;
Begin Transaction
Commit
Update MARA set MATNR = ‘4711’ where MATNR=
MinLSN ‘0000’
Insert into KUNR values (‘9087’, ‘Microsoft’,
303 ‘Redmond’)
Select * from KUNR where KNR = ‘5679’
Transaction log
LSN LSN LSN LSN LSN LSN LSN
301 302 303 304 305 306 307
Begin Tran1 Update Begin Tran2 Update Commit Checkpoint Begin Tran3
MONI MARA Tran1
Tran1 Tran2
SAP AG 2002
During a checkpoint, the log sequence number (LSN) of the first log record at which a system-wide
recovery must start is written to the database boot page. This LSN is called the Minimum Recovery
LSN (MinLSN) and is the lowest of the three following values:
The LSN of the checkpoint
The LSN of the oldest recorded dirty data page
The LSN of the start of the oldest active transaction
The portion of the log file from the MinLSN to the end of the log is called the active portion of the
log. This is the portion of the log required for a full recovery of the database. No part of the active
log can ever be truncated. All log truncation must be done from the parts of the log before the
MinLSN.
The automatic recovery process is explained in detail in Unit 5: Database Restore.
© SAP AG ADM520 1-25
Lock Modes
detail stats. detail stats. detail stats. detail stats. detail stats.
Select Select Update Update Select
Shared Exclusive ....
SAP AG 2002
An RDBMS processes a large number of transactions simultaneously. A lock synchronizes
simultaneous accesses to an object. When a transaction accesses an object, the object is temporarily
locked to prevent other transactions from accessing it simultaneously.
The type of lock determines which operations from other transactions can be executed on the locked
object. Types of locks are:
Shared (S)
Used for operations that do not change or update data (read-only operations), such as a SELECT
statement.
Exclusive (X)
Used for data-modification operations, such as UPDATE, INSERT, or DELETE. This type of lock
ensures that multiple updates cannot be made to the same resource at the same time.
Update (U)
Used on resources that can be updated. This type of lock prevents a common form of deadlock that
occurs when multiple sessions are reading, locking, and potentially updating resources later.
Intent (I)
Used to establish a lock hierarchy.
Schema (Sch)
Used when an operation dependent on the schema of a table is being executed.
The two types of schema lock are: Schema stability (Sch-S), Schema modification (Sch-M)
© SAP AG ADM520 1-26
Lock Resources
Database (single user)
Table
Page
Index rows Key range
Extent
Row
Data rows
.... 8 pages
SAP AG 2002
SQL Server has multi-granular locking that allows different types of resources to be locked by a
transaction. These resources are locked at a level appropriate to the task by using a dynamic locking
strategy to determine the most cost-effective locks.
SQL Server can lock the following resources, which are listed in order of increasing granularity:
RID: Row identifier, used to individually lock a single row within a table
KEY: Row lock within an index, used to protect key ranges in serializable transactions
PAG: 8KB data or index page
EXT: Extent, contiguous group of eight data or index pages
TAB: Entire table, including all data and indexes
DB: Database
The finer the lock granularity, the more locks are needed. For example, when accessing a table with
100,000 pages, you can use 100,000 page locks or only one table lock. More locks require more
administration time.
If the lock granularity is coarse, other transactions must wait longer until the lock is released.
To display locks currently in use, use the stored procedure sp_lock or use the Current Activity View
in the Enterprise Manager.
© SAP AG ADM520 1-27
Lock Escalation
Minimize cost of locking
Transaction 1
Table 3 Intent exclusive (IX) 5 Exclusive (X)
2 Intent exclusive (IX) Coarse-grain locks
Page
Row 1 Exclusive (X)
4
Row Lock escalation
( large number of rows Fine-grain locks
or pages -> table lock )
SAP AG 2002
SQL Server may dynamically escalate or deescalate the granularity or type of locks. Lock escalation
is the process of converting many fine-grain locks into fewer coarse-grain locks, thus reducing
system overhead.
In this example, a transaction requests rows from a table for update purpose. SQL Server
automatically acquires locks on those rows affected (1) and places higher level intent locks on the
pages (2) or index, that contain those rows. The table which contains the rows also receives an intent
exclusive lock (3). When the number of locks held by the transaction exceeds a threshold, SQL
Server attempts to change the intent lock on the page to a stronger lock, e.g. an intent exclusive
would change to an exclusive lock (4). After acquiring the stronger lock, all row level locks held by
the transaction on the page are released, reducing lock overhead (5).
SQL Server may choose to use both row and page locking for the same query, for example, by
placing page locks on the index (if enough contiguous keys in a non-clustered index node are
selected to satisfy the query) and row locks on the data. This reduces the likelihood of lock
escalation.
SQL Server rarely needs to escalate locks; the query optimizer usually chooses the correct lock
granularity at the time the execution plan is compiled.
Lock escalation thresholds are determined dynamically by SQL Server and do not require
configuration.
© SAP AG ADM520 1-28
Transaction Isolation Level (1)
Dirty T1 update X rollback
changing command
read
X=5 X=7 X=7 X=5
reading command
T2 select X X=7 X=7 data read
Non- T1 select X,Y X=7,Y=2 select X,Y X=4,Y unknown
repeatable
read X=7 X=7 X=4 X=4
Y=2 Y=2
T2 update X, delete Y
Phantom T1 select <10 Y=7 select <10 X=5,Y=8, Z=3
X=5 X=5
Y=7 Y=7 Y=8 Y=8
Z=12 Z=12 Z=3 Z=3
T2 insert X, update Y, Z
SAP AG 2002
Several users running concurrent transactions can cause inconsistencies in the data read by other
users. The following situations may occur:
Dirty read: Transaction T1 updates data X. Another transaction T2 then selects X before T1
performs a COMMIT. T1 then performs a ROLLBACK. So T2 has read a value for X that never
existed in the database as consistent (committed) data.
Non-repeatable read: Transaction T1 selects data X, Y. Another transaction T2 then updates X
and deletes Y and commits. T1 then selects X, Y again. It reads a modified value for X and
discovers that Y does not exist.
Phantom data: Transaction T1 selects all the data that satisfies the condition < 10. Only X is
returned. Transaction T2 then creates data Z and updates Y so as to satisfy the condition < 10. T2
commits. T1 then again selects all the data < 10. Now X, Y, and Z are returned. So new data
appeared (phantom data).
© SAP AG ADM520 1-29
Transaction Isolation Level (2)
Read select Read select commit
uncommitted commit committed
lock lock
Repeatable select commit Data without lock
read
Another process
holds a shared
lock on this data
lock lock hold lock Another process
holds an exclusive
Serializable select commit lock on this data
Data is read
Select waits until
lock is released
key range lock
lock hold lock
SAP AG 2002
The isolation level determines to what degree one transaction is isolated from other transactions. A
lower isolation level increases concurrency but at the expense of data correctness. A higher isolation
level ensures that data is correct, but can negatively affect concurrency. The isolation level set by an
application determines the locking behavior used by SQL Server.
To define the isolation level for a connection to the database, use the transact-SQL command SET
TRANSACTION ISOLATION LEVEL.
SQL-92 defines four isolation levels, all of which are supported by SQL Server:
Read uncommitted accepts all dirty reads, non-repeatable reads and phantoms. No shared locks
are issued and no exclusive locks are honored.
Read committed avoids dirty reads. To achieve this shared locks are held while data is being
read. But the data can be changed before the end of the transaction, resulting in non-repeatable
reads or phantom data. This option is the SQL Server default.
Repeatable read avoids both dirty and non-repeatable reads. It sets locks on all data used in a
query and prevents other users from updating the data. Phantom data can occur.
Serializable avoids all dirty reads, non-repeatable reads and phantoms. It sets a range lock on the
data range selected. Other users cannot update or insert rows into that data range until the
transaction is complete.
© SAP AG ADM520 1-30
Summary of this Unit
Now you are able to:
Describe the main features of the SQL Server
architecture
Explain the different authentication modes
Understand the different index types
Name the system databases and their main functions
SAP AG 2002
© SAP AG ADM520 1-31
Further Documentation
Inside Microsoft Sql Server 2000
Microsoft Press, ISBN: 0-7356-0998-5
SAP AG 2002
© SAP AG ADM520 1-32
How the SAP System uses SQL Server
1 Introduction
2 SQL Server Architecture
3 How the SAP System uses SQL Server
4 Performance Monitoring and Tuning
5 Database Backup
6 Database Restore
7 Regular Maintenance and Error Handling
SAP AG 2002
© SAP AG ADM520 2-1
How the SAP System uses SQL Server
Contents
Client/Server architecture
Database access and security
Databases used in the SAP System
Objectives
At the end of this unit, you will be able to:
Describe the architectural specifics under SQL Server and the SAP
System
Name the databases used in the SAP environment and explain their
functionality
SAP AG 2002
© SAP AG ADM520 2-2
Architecture of the SAP System (1)
Presentation
Server
SAP GUI
SAP GUI SAP GUI
SAP GUI
SAP Instance
SAP SAP
Application Instance SAP SAP SAP
Server Work- Dispatcher Work-
SAP
Prozess SAP Prozess
Work- Work-
Prozess Prozess
Database
Server SQL Server
Databases
SAP AG 2002
The SAP System has a 3-tier architecture that includes the following:
Presentation server
Application server
Database server
The presentation server consists of a graphical user interface on the front end. Each SAP System user
runs an SAP GUI on a dedicated front-end computer.
The application server is used primarily for data processing. An SAP instance that runs on an
application server combines special SAP services such as dispatching requests. The dispatcher, a
special process on each SAP instance, communicates with the front end SAP GUIs and distributes
work requests to the work processes. The application logic runs in the work processes, which are
connected to the database server.
The database server is the computer where the database management system (DBMS) is located. The
DBMS (SQL Server) controls the databases and the database objects that perform all the database
activities.
Note that an SAP GUI on a presentation server does not connect directly with the database; only the
work processes do this.
© SAP AG ADM520 2-3
Architecture of the SAP System (2)
Presentation
Server
SAP GUI
SAP GUI SAP GUI
SAP GUI
SAP SAP Instance
Application SAP Instance
Server SAP
SAP Dispatcher SAP
Work- Work-
Prozess Prozess
Database
Server
SQL Server
Databases
SAP AG 2002
© SAP AG ADM520 2-4
R/3 Database Interface
SAP SAP Instance
Application
Server SAP Work Process
Database Access Agent
R/3 DB IF
DBSL IF
Database
Server SQL Server
Databases
SAP AG 2002
SQL Server accesses objects stored in databases on the database server and accepts all requests from
the SAP System through the database interface from the database access agent.
The database access agent in the work processes handles database requests, and consists of several
subcomponents. One of these subcomponent is the database vendor-independent R/3 database
interface (R/3 DB IF), which handles accesses to table and dictionary buffers.
The database access agent also provides the Database SQL Library Interface (DBSL IF), which is
database vendor-specific. All components on the SAP System side of this interface are independent
of the database used. On the database side of the interface, only system components provided by the
database vendor are used.
With SQL Server, components on the database side of the DBSL IF are implemented using a
Microsoft product. The main task of the DBSL IF is the mapping of ABAP Open SQL statements to
the database vendor-specific SQL language.
© SAP AG ADM520 2-5
Database Connections
SAP
Application
SAP Work- SAP Work-
Server
Prozess Prozess
OLEDB
TCP/IP sockets
TCP/IP
Database
Server TCP/IP sockets
ODS (Open data services)
SQL Server
SAP AG 2002
Each SAP work process is connected to the database server through several connections that are used
for executing database commands. The DBSL IF is implemented using the Microsoft OLE DB.
If the SAP work processes have to connect to a computer other than the database server, the
communication protocol will be TCP/IP, otherwise Named Pipes is chosen.
Open Data Services (ODS) is the component that manages all packets coming from the server net
libraries, for example TCP/IP sockets. The database server processes all requests passed to it by ODS
and returns the results to the client.
© SAP AG ADM520 2-6
Client Network Utility
SAP
Central
SAP SAP SAP SAP
Server
Work Work Work Work
SAP
Process Process
... Process Process
...
Application
Server TCP/IP sockets Named Pipes
TCP/IP Named
Pipes
TCP/IP sockets
Named Pipes
Database
Server
SQL Server
SQL Server
SAP AG 2002
In the SAP environment the network protocols should be activated as follows:
SAP Application Server connecting over the network: On every SAP Application Server the
default network protocol must be TCP/IP. In the client network utility, set TCP/IP at the top of the
list.
Cluster server with one node running SAP connecting to an SQL Server running on another node:
TCP/IP should be activated on the node where the SAP system runs.
Central system connecting within the same machine: If the central database server is also running
the SAP system the protocol must be set to Named Pipes. Named Pipes is more efficient than
TCP/IP on the local system, because the data transfer occurs in main memory only.
You can activate a protocol using the SQL Server Client Network Utility for each client, and the
Server Network Utility for the server. Only one protocol can be active on any one SAP Application
Server. To start the SQL Server Client Network Utility choose Programs/Microsoft SQL Server, and
then click Client Network Utility.
© SAP AG ADM520 2-7
Database Connections to SAP Work Processes
SAP SAP
SAP
0 Consistent transactions SAP Work Work
Work process
1 Stored proc. Application Process Process
Create/Select single Server
2..N Dirty read selects
01234567 01234567
READ UNCOMMITTED Database
READ COMMITTED Server
SQL Server
SAP AG 2002
Several connections are used for each SAP work process, with the following isolation levels:
Connection 0: Committed read connection, used for consistent transactions involving inserts,
deletes, updates, and select for update or database cursor usage.
Connection 1: Uncommitted read connection, used for all DDL transactions, creating stored
procedures, and to execute single selects and dirty read cursors.
Connection 2...N: Uncommitted read connection, used for dirty read selects.
A maximum number of 40 connections are established for each work process, other than connections
0 and connection 1. These connections are opened as needed but only closed when shutting down the
instance or restarting the work process. If the select is nested in too many surrounding selects (more
than 40), cursors are used in connection 1.
Each connection to the database uses approximately 50KB of memory.
© SAP AG ADM520 2-8
SAP Process Monitor
sapprd 5465 12 sleepi. EARLY.. SAPLTHB 0 48.890 8.856 2.048 AWAITING COM. 0
56 sleepi. EARLY.. SAPLTHB 0 801.570 1.234 456 AWAITING COM. 0
78 sleepi. EARLY.. SAPLTHB 0 90.346 456 90 AWAITING COM. 0
42 sleepi. EARLY.. SAPLTHB 0 9.457 150 12 AWAITING COM. 0
1809 89 sleepi. 0 501.290 1.390 7.903 AWAITING COM. 0
67 sleepi. 0 78.401 901 1.509 AWAITING COM. 0
59 sleepi. 0 809 855 56 AWAITING COM. 0
43 sleepi. 0 55.331 401 0 AWAITING COM. 0
7901 90 sleepi. KERBE.. 0 620.601 9.023 9.450 AWAITING COM. 0
87 runn. KERBE.. 0 88.345 5.998 8.345 SELECT 0
14 sleepi. KERBE.. 0 32.901 7.901 6.012 AWAITING COM. 0
63 sleepi. KERBE.. 0 45.234 967 690 AWAITING COM. 0
39 sleepi. KERBE.. 0 12.902 235 401 AWAITING COM. 0
9011 41 sleepi. 0 734.993 9.345 0 AWAITING COM. 0
SAP AG 2002
To call the SAP Process Monitor, choose Tools →Administration →Monitor →Performance
→Database→(ST04) Activity. Choose button Detail analysis menu then button SQL processes. By
default, the output is sorted by the CPU time and only connections used by the SAP System are
shown. To display the SQL processes sorted by the host process id (pid), choose button Group
by/Raw Display or F8.
The Appl. Server column displays the host name of the database client. For all connections
established by the SAP System, this is the name of the SAP application server to which the SAP user
is connected.
The host pid shows the process id of the SAP work process. Each SAP work process opens a number
of database connections, each of which is identified by its SQL Server process id (spid) and labeled
by the Application program name R3<T><nn>(<mm>)<type>:
<T>: Work process identifier (D: Dialog, B: Background, S: Spool, U: Update, E: Enqueue, 2:
Update2, ‘ ‘:external tool such as saplicense or tp)
<nn>: Work process number
<mm>: Number of connection
<type>: Connection context controlled by the DBIF
• comm rd: Committed read
• sp create, single select: DDL, creation of stored procedures and single selects
• unc rd: Dirty read selects
Note: The Application program name is not shown in the default layout. Choose another layout to
hide or display other columns.
To display the command that is currently being executed for a connection, double-click a row.
Choose Goto → All DB in the SAP menu to display connections to all databases run on the database
server.
© SAP AG ADM520 2-9
SQL Server Process Monitor
Process Info 97 items
Proce. User Database Status Open Tran Command Application
1 system no database cont background 0 LAZY WRITER
3 system master sleeping 0 SIGNAL HANDLER
10 sapprod\SAPServicePRD PRD sleeping 0 AWAITING COMMAND R3D00(1)sp create, single select OLEDB
11 sapprod\ SAPServicePRD PRD sleeping 0 AWAITING COMMAND R3D01(1)sp create, single select OLEDB
12 sapprod\ SAPServicePRD PRD sleeping 0 AWAITING COMMAND R3D00(0)comm rd OLEDB
13 sapprod\ SAPServicePRD PRD sleeping 0 AWAITING COMMAND R3D00(2)unc rd OLEDB
15 sapprod\ SAPServicePRD PRD sleeping 0 AWAITING COMMAND R3D01(2)unc rd OLEDB
16 sapprod\ SAPServicePRD PRD sleeping 0 AWAITING COMMAND R3D02(2)unc rd OLEDB
4 system no database cont background 0 LOCK MONITOR
20 sapprod\SAPServicePRD PRD sleeping 0 AWAITING COMMAND R3B11(1)sp create, single select OLEDB
21 sapprod\ SAPServicePRD PRD sleeping 0 AWAITING COMMAND R3D02(1)sp create, single select OLEDB
22 sapprod\ SAPServicePRD PRD sleeping 0 AWAITING COMMAND R3S12(0)comm rd OLEDB
23 sapprod\ SAPServicePRD PRD sleeping 0 AWAITING COMMAND R3S12(2)unc rd OLEDB
2 system no database cont background 0 LOG WRITER
5 system master background 0 TASK MANAGER
SAP AG 2002
You can also display the SQL processes by choosing Enterprise Manager →Management →Current
Activity →Process Info. The Process Info view displays all SQL processes sorted by their login id. A
colored globe indicates an active process. All inactive processes are marked by gray globes.
The information displayed is read from the table sysprocesses, which contains information about the
client and system processes running on SQL Server. Table sysprocesses is logically found in
database master. It is built dynamically each time it is read, and therefore does not exist physically.
© SAP AG ADM520 2-10
Data Select from ABAP: Table Buffers
select * from T100
where ...
SAP Instance endselect.
SAP Work Process
SAP Database Access Agent
Application Shared
Server buffers
DBSL IF
SAP AG 2002
This section explains how the SAP system accesses the database when executing ABAP programs.
In ABAP, you access the database by using Open SQL commands. The ABAP program and its Open
SQL commands are independent of the database system.
An Open SQL command is converted into a standard form and is passed to the Database Access
Agent. The Database Access Agent checks whether the accessed table is buffered in an SAP table
buffer. If the table is buffered, the data is retrieved from the SAP buffers and results are supplied
without accessing the database.
© SAP AG ADM520 2-11
Data Select from ABAP: PREPARE
Permanent Direct SQL Statements
Stored Procedures
select * from
select * from TBTCO TBATG into table
SAP Instance where ... I_TBATG for all
endselect. entries … where ...
endselect.
SAP Work Process SAP Work Process
Database Access Agent Database Access Agent
SAP DBSL IF DBSL IF
Application
Server SELECT OBJECT, TABNAME,
INDNAME, TGORDER, FCT,
EXECMODE, SEVERITY,
GDATE, GUSER FROM TBATG
create WHERE FCT IN (@P1, @P2,
Y2R6GH78J676ABC0000TBTCO @P3, @P4, ...)
SAP AG 2002
If the requested data is not found in the SAP buffers, the DBSL IF translates the Open SQL
command to one or more stored procedures or to a direct SQL statement:
Stored procedures are reusable collections of SQL commands, compiled in a single execution
plan. Permanent stored procedures are stored in the SAP database and are not deleted after
restarting SQL Server or the SAP instance. The DBSL IF creates a unique stored procedure
name for each Open SQL command.
Direct statements come from dynamic Open SQL statements, for example FOR ALL ENTRIES.
Direct SQL statements are created directly on the SAP database without creating a stored
procedure in prior.
When an Open SQL statement is send to the database access agent, the operation is called the
PREPARE operation. The following steps are performed:
The statement is analyzed by the database access agent and its origin is classified.
The open SQL statement is converted into a native SQL statement.
For permanent stored procedures a unique stored procedure name is created by the DBSL agent.
A set of parameters is generated from the WHERE condition for the statement text. A stored
procedure text is generated consisting of the stored procedure name, the parameters and the text.
The stored procedure is created on the database.
For direct SQL statements the native SQL statement is executed on the database and cached in
the procedure cache.
© SAP AG ADM520 2-12
Data Select from ABAP: OPEN and FETCH
Permanent Direct SQL Statements
Stored Procedures
select * from
select * from TBTCO TBATG into table
SAP Instance where ... I_TBATG for all
endselect. entries … where ...
endselect.
SAP Work Process SAP Work Process
Database Access Agent Database Access Agent
SAP DBSL IF DBSL IF
Application
Server Sp_executesql(SELECT
OBJECT, TABNAME,
INDNAME, TGORDER, FCT,
EXECMODE, SEVERITY,
execute GDATE, GUSER FROM TBATG
Y2R6GH78J676ABC0000TBTCO WHERE FCT IN (@P1, @P2,
@P3, @P4, ...))
SAP AG 2002
When a permanent stored procedure is executed, the following operations are performed:
The DBSL agent passes the command to execute a stored procedure including the parameters to
SQL Server.
The SQL statements in the stored procedure are compiled and optimized and an execution plan
is created. If the stored procedure already exists and an execution plan is still in procedure cache,
the execution plan is reused.
SQL Server executes the stored procedure.
Direct SQL statements are executed using system stored procedure sp_execute in the OLE DB
interface. This stored procedure executes a transact-SQL statement that can be reused many times, or
that has been built dynamically. The transact-SQL statement can contain embedded parameters.
The execution of the stored procedure or the direct SQL statement starts with the OPEN operation.
The group of records returned when a stored procedure or a direct SQL statement is executed is
called a result set. While the stored procedure or direct SQL statement is executed, the result set is
transferred from SQL Server to the requesting SAP work process. This is called the FETCH
operation.
© SAP AG ADM520 2-13
Stored Procedure Name Cache
SAP Instance
SAP Work Process Stored
procedure
Database Access Agent name
cache
DBSL IF In
YR... yes name no
cache
?
SAP exec in cache
Y2R6...
Application In
no yes
sysobjects
Server
?
exec YR... create Y2R6…
exec Y2R6...
exec Y2R6...
SAP AG 2002
Before creating a permanent stored procedure, the DBSL IF checks if the unique name already exists
in a stored procedure name cache. If a stored procedure name is not found in the stored procedure
name cache, the DBSL IF checks whether it exists in the sysobjects table. If it is not found in the
sysobjects table it is created on the database before it is executed. If the stored procedure is found in
the name cache, it is executed directly.
The names of permanent stored procedures are stored in a cache defined by the SAP instance profile
parameter dbs/mss/pn_cache_size. This value gives the number of permanent stored procedure
names in the name cache. The default value is 10000 for kernel releases 6.20; for later kernel
releases the default value is 20000.
Stored procedure names are stored physically in the sysobjects table, stored procedure texts are
stored physically in the syscomments table in the SAP database.
© SAP AG ADM520 2-14
Stored Procedure Names
Y2R10000000BB1H1911DD03L
Y2A000001D9C7VK3725RSCROSS10i0o5ns
Y2E000011F2CBC50301REPOLOAD
Y2R65000168C73K0943ALPERFDBi11o18ns
Y2C00000453CBC21832DBSYNSEQ
Y2A0000032FCAUK4623SAPLSEUwCOMPONENTut01i6o1ns
<SID> Y2R10000000C5GG0516TBTCP
Y2K00002B2DCBC21130DOKCLUi5o9ns
Y2R20000001C5GG1657TST01
. . .
SAP AG 2002
Naming conventions for permanent stored procedures:
Y2<ModuleType><StatementId><Timestamp><ModuleName><Suffix>
<ModuleType> identifies the type of module the statement comes from, for example, A for
ABAP report, C for a C-program, R for a table name
<StatementId> is usually the same as the statement id in the SAP System; statement Ids uniquely
identify an SQL-DML statement in the SAP environment
<Timestamp> usually specifies the generation time of an object, for example, the generation
time of an ABAP report
<ModuleName> is derived from the module name given in the statement id, for example the
table name or report name
<Suffix> is optional and indicates for example single selects (ut01 means up to one row)
© SAP AG ADM520 2-15
Windows Authentication
Presentation SAP Application Database
server server server
SAP GUI <SID>
sapusr
SAP user: ..... SAP System MSSQL
PW: ..... server
pubs
<DOMAIN>\ §
SAP_<SID>_
GlobalAdmin
Windows user SAP user Login Id Server role
Example: Joe SAPUSR <DOMAIN>\ System
SAP_<SID>_ Administrator,
GlobalAdmin default DB <SID>
Authori- Logon at Access to Logon at Access to
zation: workstation SAP objects SQL Server DB objects
SAP AG 2002
From Release 4.5A, the SAP System uses trusted connections when running with SQL Server
exclusively. With this method, the SQL Server login id sapr3 is not used for SAP work process
connections. The Windows user running the SAP service (SAPService<SID>) connects to the
database server.
Access to SQL Server is controlled by the Windows NT account or group, which is checked when
logging on to the operating system on the application server. When SAP work processes connect to
SQL Server, they request a Windows trusted connection to SQL Server. Windows does not open a
trusted connection unless the SAP application server has successfully logged on using a valid
Windows account. In this case SQL Server does not have to check the account. SQL Server gets the
user account information from the trusted connection properties and matches them against the
Windows accounts defined as valid SQL Server logins. If SQL Server finds a match, it accepts the
connection.
Refer to the SAP Security Guide Volume II Microsoft SQL Server under Windows NT or Windows
2000 for detailed information.
© SAP AG ADM520 2-16
Databases in an SAP SQL Server System
Databases
master msdb tempdb <SID>
Files
.ldf .ldf .ldf .ldf
.mdf .mdf .mdf .mdf
SAP AG 2002
During SQL Server installation, databases master, tempdb, and msdb are created.
master: Records all of the system level information for a SQL Server system, that is, all login
accounts, system configuration settings, existence of all other databases and the location of the
primary files.
msdb: Used by SQL Server Agent for scheduling alerts and jobs, e.g. regular backups.
tempdb: Contains all temporary tables and is re-created every time SQL Server is started.
Temporary tables are automatically dropped at disconnect, and no connections are active when
the system is shut down.
In addition to the standard databases created in every SQL Server installation, a database is created
for the SAP System. This database receives a 3-letter identification (for example, PRD, TST).
In this course, the SAP database is referred to as <SID>.
© SAP AG ADM520 2-17
SAP Database Files
SAP Database <SID>
<SID>DATA1
<SID>DATA2
<SID>DATA3 <SID>LOG1
<SID>DATAn <SID>LOGm
Files
...
Filegroup PRIMARY
File System <drive>:\<SID>DATA1\<SID>DATA1.mdf <drive>:\<SID>LOG1\<SID>LOG1.ldf
<drive>:\<SID>DATA2\<SID>DATA2.ndf ... <drive>:\<SID>LOGm\<SID>LOGm.ldf
<drive>:\<SID>DATA3\<SID>DATA3.ndf
... <drive>:\<SID>DATAn\<SID>DATAn.ndf
SAP AG 2002
Each database has two logical parts: data (data files) and transaction log (log files). When SQL
Server and the SAP System are installed, the data files of the <SID> database are created in the
directories <drive>:\<SID>DATA1\<SID>DATA1.mdf and
<drive>:\<SID>DATAn\<SID>DATAn.ndf, where n is the number of the file. The data files may
reside on different physical drives. SAP recommends storing the data files using RAID5. The
standard installation creates 3 data files. This makes it easier to expand the database. See Chapter 6
‘Regular Maintenance and Error Handling'.
The transaction log file is created in the directories <drive>:\<SID>LOG1\<SID>LOG1.ldf and
<drive>:\<SID>LOGm\<SID>LOGm.ldf, where m is the number of the file. The log files must be
mirrored. Hardware mirroring using RAID1 is strongly recommended. The standard installation
creates one log file.
After a standard installation, all SAP data files reside in the special filegroup PRIMARY.
Stored procedure sp_helpfile returns the physical names and attributes of files associated with the
current database. Use this stored procedure to determine the names of files attached to one database.
© SAP AG ADM520 2-18
Summary
Now you are able to:
Explain the architecture of SQL Server and the SAP System
Name the different databases used under SAP, and explain
their function
Describe the authentication modes used under SAP
Explain how ABAP SQL statements are executed on the SAP
database
SAP AG 2002
© SAP AG ADM520 2-19
Further Documentation
Books Online
SAP Installation Guide
SAP Database Administration with
Microsoft SQL Server 2000
SAP Press, ISBN: 1-59229-005-1
SAP Security Guide Volume II
Microsoft SQL Server under Windows
NT or Windows 2000
SAP AG 2002
© SAP AG ADM520 2-20
Unit Actions
? Exercises
Solutions
SAP AG 2002
© SAP AG ADM520 2-21
How the SAP System uses SQL Server Exercises
Unit: How the SAP System uses SQL Server
Topic: Database Access and Security
At the conclusion of this exercise, you will be able to:
• Change the password of Windows user <Domain
name>\SAPService<SID>
• Understand the way the SAP System opens connections to the database and
executes SQL statements on the database
For security reasons the customer wants to change the password of the
user who runs the SAP System.
The customer observes a high activity in one SAP work process and
wants to find out, what this SAP work process executes on the database.
1-1 Changing the password of Windows user <Domain name>\SAPService<SID>
1-1-1 Change the password of Windows user <Domain
name>\SAPService<SID>. Stop the SAP System using the SAP Microsoft
Management Console and stop Windows service SAP<SID>_<instance
number>. Restart the service and the SAP System.
What do you observe?
1-1-2 Change the password of Windows user <Domain
name>\SAPService<SID> back to the old password and restart the service
and the SAP System. What happens?
1-1-3 How do you correctly change the password of Windows user <Domain
name>\SAPService<SID>? What steps do you have to perform?
1-2 Uncommitted read connections to the database
1-2-1 Kill all uncommitted read connections from SAP work process 0, which
have been opened to SQL Server.
How do you determine the SQL Server Process IDs (spid)?
Which statement or tool do you use to kill SQL Server connections?
1-2-2 Run program ZADM520NEST using transaction SE38. Observe the SQL
processes and their activities using transaction ST05.
Find out which SAP work process runs program ZADM520NEST.
1-2-3 Did the SAP work process open new connections to the database?
What is the maximum number of uncommitted read connections?
© SAP AG ADM520 2-22
How the SAP System uses SQL Server Solutions
Unit: How the SAP System uses SQL Server
Topic: Database Access and Security
1-1 Changing the password of Windows user <Domain name>\SAPService<SID>
1-1-1 Stop the SAP System.
To change the password of <Domain name>\SAPService<SID>, use the
Windows User Manager under Start → Programs → Administrative Tools
→ Computer Management. Under folder Local Users and Groups select
folder Users and mark user SAPService<SID>, click the right-mouse button
and choose Set password.
Stop the SAP System using the SAP System Management Console and stop
service SAP<SID>_<instance number> using the Computer Management
tool. When restarting the service error message
Could not start the SAP<SID>_<instance number> service on Local
Computer. Error 1069: The service did not start due to a Logon Failure.
occurs. The service cannot be restarted because the authorization is not
correct. Windows authorization is checked for service
SAP<SID>_<instance number>, but not when the work process is started.
The login attempt of the work process connections to SQL Server fails.
1-1-2 Change the password back using the Windows User Manager.
Start the service SAP<SID>_<instance number> again.
Start the SAP System.
1-1-3 Stop the SAP System and service SAP<SID>_<instance number>. Change
the password of user <Domain name>\SAPService<SID>. Specify the new
password in the service used for the SAP System. Select service
SAP<SID>_<instance number> in the Computer Management tool, click
the right mouse button and select Properties. In the Log On tab change the
password. Start the service and the SAP System again.
1-2 Uncommitted read connections to the database
1-2-1 To display the connections each SAP work process has opened to the
database, call monitor Database Performance Analysis: SQL Server
Database Overview (Transaction ST04). Choose Detail Analysis Menu →
SQL processes.
Choose layout /4ADM520 to sort the connections by their host process IDs
(Host PID). Check only processes opened by the SAP System, i.e. processes
where the login ID <Domain name>\SAPService<SID> is displayed in
column SQL Server Login.
The column Application program shows names starting with: R3D00(x),
where D is the work process type (dialog), 00 the number of the SAP work
© SAP AG ADM520 2-23
process and x is the number of the connection. The uncommitted read
connections are identified by unc rd OLEDB in the program name. The spid
is displayed in column SPID.
Alternatively, use the SAP Process Overview (transaction SM50) to display
the Host PID belonging to SAP work process 0.
To kill a SQL Server connection use transact-SQL statement KILL or
choose the Enterprise Manager, mark the connection in the SQL Server
Process Monitor, click the right-mouse button and choose Kill Process.
1-1-2 In a new window, call transaction ST05 and start a SQL trace.
Call transaction SE38, and execute program ZADM520NEST.
Call the SAP Process Monitor, use transaction code ST04, and choose Detail
Analysis Menu → SQL processes. To group the output, select Group
by/Raw display.
To find out which SAP work process runs ZADM520NEST, look for column
Report.
1-1-3 To find out the connection numbers used by the work process:
Display the trace by choosing button Display trace in transaction ST05.
Choose Trace List → Display Extended Trace List, and look for program
ZADM520NEST in the column Program. In the comment in column
Statement the stored procedure name and the connection number are
displayed.
The connection number is also displayed in the Database Process Monitor in
the column Application program as R3D00 (X).
ZADM520NEST executes nested selects on database table DD32S. For each
nested select, a new uncommitted read connection to the database is opened.
A maximum of 40 dirty read connections will be opened.
© SAP AG ADM520 2-24
Performance Monitoring and Tuning
1 Introduction
2 SQL Server Architecture
3 How the SAP System uses SQL Server
4 Performance Monitoring and Tuning
5 Database Backup
6 Database Restore
7 Regular Maintenance and Error Handling
SAP AG 2002
© SAP AG ADM520 3-1
Performance Monitoring and Tuning
Contents
Database configuration
Cache size tuning / CPU utilization / Parameter tuning / Disk
layout
Application problems
Lockwaits / Expensive statements
Objectives
At the end of this unit, you will be able to:
Monitor the performance of SQL Server
Identify the source of database performance problems
Find solutions to the performance problems detected
SAP AG 2002
© SAP AG ADM520 3-2
Sources of Database Performance Problems
Performance problems
due to:
1 2
Poor configuration Inefficient applications
Poor database Poor hardware
configuration configuration
SAP AG 2002
Poor database performance normally results from problems with database configuration, application
coding problems, or hardware capacity. This section explains how to identify and correct these
problems.
The first step is normally to tune the database and hardware configuration. In the Database Monitor,
you:
Check the general database configuration
Use the database tools to verify the efficiency of the application coding
Note: Tuning inefficient application coding may allow you to avoid buying new hardware.
Changing the database configuration may affect the amount of hardware required. This unit therefore
also explains how to check if your current hardware configuration allows the necessary database
configuration changes.
© SAP AG ADM520 3-3
Database Configuration
Poor configuration
Poor database Poor hardware
configuration configuration
Data cache Disk Main
memory Disks
layout
Parameter CPU
settings
SAP AG 2002
This section discusses the following areas of database configuration:
Data cache
SQL Server configuration parameters
Disk layout
To detect data cache problems, the cache hit ratio is the main indicator.
An improper separation of several logical database portions and other frequently accessed files may
lead to disk hot spots, which results in high disk response times.
© SAP AG ADM520 3-4
Cache Usage
RDBMS Memory
Area Data cache
SELECT *
FROM MARA
WHERE ... SQL connections Procedure cache
SAP Work parsed SQL
Process create proc statements and
execution plan
Database
Tables Indexes Procedure Texts
MARA_1
MARA Y2R0000064B69RC5522MARA
MARA_0
SAP AG 2002
An Open SQL statement coming from an ABAP program is executed as a stored procedure or as a
direct statement in SQL Server.
For each table, there is a set of common statements such as INSERT, UPDATE and DELETE
operations. A permanent stored procedure exists for each of these statements. These stored
procedures can be reused.
To shorten access times, the execution plan of a stored procedure is stored in the procedure cache.
SQL Server automatically allocates the necessary space from the available memory, and dynamically
adjusts the size of the procedure cache.
For a detailed description of this stored procedure mechanism, see Unit 2: How the SAP System uses
SQL Server.
© SAP AG ADM520 3-5
Statement Execution (1)
RDBMS Memory
Area Data cache
SELECT *
FROM MARA
WHERE ... SQL connections Procedure cache
SAP Work parsed SQL
Process load proc statements and
execution plan
Database
Tables Indexes Procedure Texts
MARA_1
MARA Y2R0000064B69RC5522MARA
MARA_0
SAP AG 2002
If the stored procedure plan does not exist in the procedure cache, the procedure text in the database
is used to generate the execution plan.
© SAP AG ADM520 3-6
Statement Execution (2)
RDBMS Memory
Area Data cache
2
SELECT * 2
FROM MARA
WHERE ... SQL connections Procedure cache
SAP Work
Process exec proc
1
Database
Tables Indexes Procedure Texts
MARA_1
MARA Y2R0000064B69RC5522MARA
MARA_0
SAP AG 2002
The execution plan determines which indexes are to be used for table access (step 1).
Once the execution plan of the stored procedure is in the procedure cache, the procedure can be
executed.
The required parts of the used indexes and tables are transported from the disk into the data cache for
further processing (step 2). It is helpful if the pages required are already stored in the data cache from
previous use.
The Query Processor selects the data required, and returns it through the database interface to the
SAP work process.
© SAP AG ADM520 3-7
Cache Usage Analysis
1 > 6h
3
4
5 2 > 98
1 > 500
SAP AG 2002
To display the most important performance parameters of the database, call Transaction ST04, or
choose Tools → Administration → Monitor → Performance → Database → Activity. An analysis is
only meaningful if the database has been running for several hours with a typical workload. To
ensure a significant database workload, we recommend a minimum of 500 CPU busy seconds. Note:
The default values displayed in section Server Engine are relative values. To display the absolute
values, press button Absolute values. Check the values in (1).
The cache hit ratio (2), which is the main performance indicator for the data cache, shows the
average percentage of requested data pages found in the cache. This is the average value since
startup. The value should always be above 98 (even during heavy workload). If it is significantly
below 98, the data cache could be too small. To check the history of these values, use Transaction
ST04 and choose Detail analysis menu →Performance database. A snapshot is collected every 2
hours.
The current size of the data cache (3) is displayed along with its target server memory, which is the
total amount of dynamic memory the server can consume since server start (4). Memory setting (5)
shows the memory allocation strategy used, and shows the following:
FIXED: SQL Server has a constant amount of memory allocated, which is set by SQL Server
configuration parameters min server memory (MB) = max server memory (MB).
RANGE: SQL Server dynamically allocates memory between min server memory (MB) < > max
server memory (MB).
AUTO: SQL Server dynamically allocates memory between 4 MB and 2 PB, which is set by min
server memory (MB) = 0 and max server memory (MB) = 2147483647.
FIXED-AWE: SQL Server has a constant amount of memory allocated, which is set by min server
memory (MB) = max server memory (MB). In addition the address windowing extension
functionality of Windows 2000 is enabled.
A detailed description of the memory parameters is found in the following slides.
© SAP AG ADM520 3-8
Memory Settings
Windows Startup Parameters
/3GB
/pae
SQL Server Memory Parameters
max server memory
min server memory
set working set size
awe enabled
Size of physical and virtual memory
SAP AG 2002
Managing SQL Server memory involves consideration of the Windows startup parameters
/3GB
/pae
and the SQL Server configuration parameters
max server memory
min server memory
set working set size
awe enabled
How memory should be configured for SQL Server further depends on:
the coexistence of other SAP components on one physical machine (i.e. either a Central Instance
or an Update Instance on the same machine as SQL Server runs)
the amount of available RAM and virtual memory
Recommended memory settings are detailed in SAP note 327494.
© SAP AG ADM520 3-9
Windows Startup Parameters
Memory Address Space Memory Address Space
boot.ini
0 /pae 0
to allow 1GB system
address space
1 GB 1 GB
and
2 GB 2 GB
2-3 GB
user process space
3 GB to allow one process the 3 GB
use of more than 4GB
Physical memory
boot.ini
4 GB 4 GB
/3gb
… …
64 GB 16 GB
SAP AG 2002
Standard Windows 2000 Server provides support for up to 4 processors and 4 GB of RAM.
Windows 2000 Advanced Server supports up to 8 processors in an Symmetric Multi Processing
(SMP) system and, with the Address Windowing Extensions (AWE), can support up to 8 GB of
memory. Windows 2000 Datacenter supports up to 32-processor SMP systems and a maximum
physical memory of 64 GB.
AWE lets applications such as SQL Server Enterprise Edition use addresses above 4 GB to cache
data in memory. AWE maps up to 64 GB of physical memory into a 32-bit (4 GB) virtual address
space in 4 KB pages. AWE extends the Windows page tables from 20 bits to 24 bits. The extra four
bits enable the use of 36-bit physical addresses. To enable the Physical Address Extension (PAE)
add /pae in the boot.ini file.
The /3GB startup parameter is a switch informing the operating system to use only 1 GB of memory
for system addresses which then permits an increase in the amount of user process space from the
default of 2 GB to 3 GB. This startup parameter is only available with the Advanced Server or
Datacenter Windows 2000 products. This feature is enabled by adding /3GB in the boot.ini file.
When the /3GB switch is used in conjunction with the /PAE switch, the operating system does not
use any memory in excess of 16 GB.
© SAP AG ADM520 3-10
SQL Server Memory Parameters
Memory Address Space Memory Address Space
max server memory (MB) awe enabled = 1
min server memory (MB)
Used to access up to 8 GB of
SQL Server used to dynamically adjust SQL Server memory
memory memory that SQL Server needs memory on Windows 2000 Advanced
Server and
64 GB on Windows 2000 Data
Center
SQL Server Query Analyzer
detail stats.
SQL Server Query Analyzer
detail stats.
sp_configure
sp_configure
‘max server memory
‘awe enabled’, ‘1'
(MB)’, ‘2048'
reconfigure with
reconfigure with
override
override
SAP AG 2002
SQL Server is able to dynamically adjust the amount of memory that it needs for optimal performance. With
the use of the max server memory and min server memory parameters the default behavior of automatic
memory adjustment can be overridden. This can help in situations where the SAP System runs as a combined
instance on the same machine.
Parameter awe enabled allows SQL Server to be able to use the AWE (Address Windowing Extension) feature
of Windows 2000. By default, SQL Server can use a maximum of 3 GB of RAM. With Windows 2000,
applications can use the AWE API to address more RAM. In Windows 2000 Advanced Server up to 8 GB of
RAM can be used, in Windows 2000 Datacenter Server up to 64 GB of RAM can be used. Using AWE in SQL
Server implies the following:
SQL Server no longer dynamically manages the size of the address space.
All memory is acquired at startup of SQL Server until shut down.
Memory pages using AWE will not be paged out.
When parameter awe enabled is set, SQL Server memory must be fix. Additionally set working set size must
be disabled, or network problems may occur when under stress. Windows must be enabled to run AWE by
the boot.ini startup parameter /pae.
In order to enable AWE use by SQL Server, SQL Server must be run by an account with the permission "Lock
Page in Memory". Additionally, there must be at least 3GB of free memory available on the computer.
When awe enabled is set a message is recorded in the SQL Server errorlog "Address Windowing Extension
enabled".
Setting set working set size to 1 means that Windows will not swap out SQL Server pages. If the values of max
and min server memory are different, then the set working set size value should be set to 0. Even though set
working set size can be set = 1 when memory has been fixed it is still recommended to set it equal to zero.
SQL Server parameters are set using stored procedure sp_configure. See Books Online for more details.
© SAP AG ADM520 3-11
CPU Utilization Analysis
1
2
3
SAP AG 2002
Transaction ST04 also shows the CPU usage of SQL Server. The header section of the ST04 main
screen displays the number of CPUs installed in the computer where SQL Server is running, as well
as the number of CPUs that can be used by SQL Server (1). Available CPUs are determined using a
bit mask given in the SQL Server parameter affinity mask.
In the section Server Engine, the percentage of CPU seconds used by SQL Server is displayed in the
field CPU busy (2). The field CPU idle (3) displays the percentage of seconds where the CPUs that
were available for SQL Server were not used. The value IO busy, which is part of CPU busy, shows
the percentage of seconds that was used for IO operations issued by SQL Server.
To display absolute values, press button Absolute values.
Note: Each CPU counts separately, that is, if 3 CPUs are used in 2 seconds, CPU busy = 6. If 1 CPU
was used and 2 were unused in 2 seconds, CPU idle = 4.
Usually, the numbers displayed have accumulated since SQL Server startup. These numbers are not
very informative as the large amount of idle time at night covers up potential bottlenecks during peak
times. Therefore, you should use the Reset and Since Reset functions to get the times during a
representative time frame. The amount of elapsed time since the last Reset is also displayed. Initially
and after a Refresh, this shows how much time has elapsed since SQL Server started.
The value for CPU busy (2) should always be approximately < 70%, that is, CPU idle > CPU busy /
2.
To check the history of the CPU utilization, use Transaction ST04 and choose Detail analysis menu
→ Performance database.
© SAP AG ADM520 3-12
Cache and CPU Tuning
?
Poor SQL Yes Tune poor
statements? statements
No
Increase
SQL Server
memory Yes Yes 2 * CPU idle Increase
Cache hit ratio
and set > 98%? + > CPU busy? CPUs for
memory mode SQL Server
No No
No All CPUs available No
OS paging?
for SQL Server?
Increase Yes Yes Add CPU(s)
server main to server
memory
SAP AG 2002
Before tuning memory or CPU for SQL Server, you should make sure that poor SQL statements are
already tuned since they can significantly affect memory or CPU utilization. (How to detect and tune
poor SQL statements is discussed later in this unit).
Before increasing SQL Server memory, you must check if there is sufficient main memory available.
Operating system paging is a sign of insufficient main memory; especially on the database server.
Call Transaction ST06 and choose Detail analysis menu →Previous hours →Memory. The amount
of page in per hour should not exceed 20% of the available physical memory. For optimal
performance, the value should be 0.
The SQL Server memory parameters should be set as described in SAP note 327494.
Before increasing the number of CPUs on the server, check the usage of the CPUs for other
applications that could be moved to other servers (for example, SAP work processes). Also check the
CPU utilization history of the Windows processes. Use transaction ST06, choose Detail analysis
menu, then Top CPU processes. Alternatively, use the Windows Task-Manager.
© SAP AG ADM520 3-13
Other SQL Server Parameter Settings
Parameter name Value
affinity mask 0
allow updates 0
awe enabled 0
c2 audit mode 0
cost threshold for parallelism 5
default language 0
fill factor (%) 0
index create memory (KB) 0
lightweight pooling 0
locks 0
max degree of parallelism 1
max server memory (MB) 2147483647
max worker thread 255
media retention 28
min memory per query (KB) 1024
min server memory (MB) 0
network packet size (B) 8192
open objects 0
priority boost 1
recovery interval (min) 0
set working set size 0
show advanced options 1
user connections 0
user options 0
SAP AG 2002
SQL Server configuration parameters can be checked using transaction ST04 → Detail Analysis
Menu → SQL Server Parameters.
A value 0 for SQL Server configuration parameters locks, open objects, index create memory (KB),
and user connections indicates that memory is dynamically allocated for these objects as required.
The amount of memory available for the data cache decreases if the number of these objects
increases.
SQL Server can execute queries using more than one CPU. This is called parallel execution. SQL
Server executes queries using only one CPU until the estimated execution time is below the value (in
seconds) of Cost threshold for parallelism. A value of 0 for cost threshold for parallelism means that
no queries are executed in parallel. This parameter is ignored if only 1 CPU is available to SQL
Server or if max degree of parallelism is set to 1. The value max degree of parallelism shows the
number of CPUs SQL Server can use for parallel statement execution (0 = all available CPUs, 1 = no
parallel executions).
See Books Online for a detailed description on all SQL Server configuration parameters and SAP
note 327494 for current recommendations.
© SAP AG ADM520 3-14
Optimal Disk Layout (Data Separation)
paging file(s) SQL Server <SID>
tempdb Transaction log
<SID>
data files
1-4 GB min. 300 MB min. 1 GB min. 20 GB
Suggested disk requirements
SAP AG 2002
Disk I/O is the most time-consuming operation for the database system. Therefore, you can
significantly improve performance by:
Using fast disks and I/O controllers
Physically separating files with high I/O activity so that they can operate concurrently
Maximum throughput can be achieved by placing the following four types of files on separate
physical disks (or disk systems/controllers):
paging file(s)
SQL Server tempdb file(s)
<SID> transaction log file(s)
<SID> data files
To check the hardware resources refer to the SAP Installation Guide found under
http://services.sap.com/instguides. Above the minimum requirements for a small SAP system
installation (application server and database server) are given. Depending on the amount of data
involved, the requirements will change.
© SAP AG ADM520 3-15
RAID Technology
RAID 0
RAID 1
RAID 5
RAID 0+1
(= RAID 10)
SAP AG 2002
RAID technology provides two basic functions:
Higher I/O performance by striping data over multiple physical disks, and therefore achieving an
even I/O distribution over all disks
Fault tolerance by mirroring disks or adding parity information
RAID levels 2, 3, and 4 are not as efficient and are not commonly used. For more information about
these levels, refer to SQL Server Books Online.
RAID 0 is called disk striping. All read/write operations are split into slices (usually 16-128 KB) that
are spread across all disks in the RAID array. There is no redundancy added. Read and write
performance is improved by equally distributing workload on all participating disks. This level
provides the highest performance of all RAID levels.
In RAID 1, every physical disk is mirrored. All write operations are written to the original disk and
to the disk mirror. Write performance may be a little lower as both disks’ writes must be
synchronized. With mirroring, one disk may fail without data loss.
In RAID 0+1 (also called RAID 10), every disk in the disk stripe set is mirrored. It provides nearly
the same performance as RAID 0 but adds redundancy at the cost of doubling the number of disks.
RAID 5 combines disk striping with parity. One physical disk is added to hold the parity
information. The parity information is also striped along all disks. Every write operation needs 2
physical reads and 2 physical writes (read data + parity and write new data + new parity). It offers
less fault tolerance, as only one disk in the whole array may fail without data loss. The total disk
storage is not available to store data.. There needs to be enough free space available for parity
information to equal the size of the smallest disk in the array.
© SAP AG ADM520 3-16
Optimal Disk Layout (Using RAID Technology)
<SID>
Transaction log
RA
ID
5
paging file(s) SQL Server
tempdb RAID 0+1
RAID 0 RAID 0 Transaction log
<SID>
data files
<SID>
(RAID 0+1 for (RAID 0+1 for
high availability) high availability)
(or RAID 0+1)
1-4 GB min. 300 MB min. 1 GB min. 20 GB
Suggested disk requirements
SAP AG 2002
The paging files and the SQL Server tempdb files are temporary data files that do not require
redundancy to ensure data security. For high availability, RAID 0+1 should be used. Since these files
are write-intensive, they should not be placed on RAID 5 systems. If they are small enough to fit on
one disk, RAID technology is not required.
Transaction log files must be placed on hardware mirrored disks (RAID 1) to ensure data security. If
more than one disk is used for transaction log files, additional striping should be used (RAID 0+1).
Data files must be placed in a RAID 5 system to ensure data security (RAID 0+1 would be faster but
more expensive).
© SAP AG ADM520 3-17
Analyzing Disk I/O Problems: SQL Server I/O
Statistics
1 > 10
2
1
SAP AG 2002
SQL Server collects an I/O statistic for the data and log files for all the databases in the SQL Server.
You can use these I/O statistics to compare the activity for each data file, and to determine whether
activity is evenly distributed over the data files.
The I/O statistics are included in the SAP SQL Server database monitor ST04 → Detail Analysis
menu → IO per File. You can choose between tempdb and the SAP database.
The column 'ms/IO' displays the average wait time for one operation in ms and is calculated from the
output of SQL Server system function fn_virtualfilestats. For acceptable performance 'ms/IO' should
be below 10ms for all data files (1). For the transaction log files the average wait time is much
smaller than for data files (2).
fn_virtualfilestats counts the statistics since SQL Server start.
For more information, see SQL Server Books Online and SAP note 521750.
© SAP AG ADM520 3-18
Analyzing Disk I/O Problems: Performance Monitor
100% Disk time
Disk queue
length up to
45 >> 4
(2 disks)
SAP AG 2002
Disk I/O performance can be measured using the Performance Monitor (from Start, choose Settings
→ Control Panel Administrative Tools →Performance Monitor).
Note: Windows does not show the physical disks in the RAID system. Therefore, you first need to
divide the performance counters by the number of physical disks. Then, for RAID 1 (and RAID
0+1), multiply the number of I/O write requests by 2. For RAID 5, multiply the writes by 4 to get the
number of physical disk I/O requests.
When ”% Disk Time = 100” you must check I/O performance. The disk queue length (read + write
queue) per physical disk should not significantly exceed 2, otherwise there is an I/O bottleneck.
To get disk performance data, enable the disk performance object using command diskperf -y (a
Windows reboot is required afterwards).
For long term monitoring, use the Performance Monitor to create a log file with snapshot data from
the specified update intervals.
For more information about using the Performance Monitor, see SAP note 110529 and Windows
Online Help.
© SAP AG ADM520 3-19
I/O System Tuning
?
Check all logical
Slow RAID No Disks with the
identified? Performance
Monitor
Yes
SQL Server I/O stats
show avg. wait time for
one disk > 10 ms
Yes
Avg. disk queue
length > 2 * phys.
Disks in RAID
Yes
Change hardware
configuration
SAP AG 2002
To find the peak throughput for a whole RAID system or the whole I/O bus, measure disk I/O
performance on all physical disks.
If the SQL Server I/O statistics show an average wait time of more than 10 ms during peak
workload, check the I/O system using the Windows performance monitor. The average disk queue
length per physical disk should not significantly exceed 2.
If you detect an I/O bottleneck, ask your hardware partner to check your hardware configuration.
© SAP AG ADM520 3-20
Database Configuration: Summary
Poor configuration
Poor database Hardware
configuration configuration
Data cache Disk Main
memory Disks
layout
Parameter CPU
settings
Cache Operating
High I/O Disk
hit ratio system
times response
> 98% paging CPU times
SQL Server Select 1 row
CPU utilization Page in utilization
via prim. key Wait queue
< 100 MB / h 2 * idle > busy and low
2 * idle > busy < 10 ms
(total) transfer rate
(SQL Server)
SAP AG 2002
© SAP AG ADM520 3-21
Application Problems
Performance problems
due to:
Poor configuration
Poor database
configuration
Poor hardware
configuration
Inefficient applications
Too many Poorly
Lockwaits unnecessary qualified
statements statements
SAP AG 2002
Application problems can be classified into three groups:
Lockwaits
- These are caused by long running transactions that are holding locks, which are blocking other
applications that want to get the same locks.
Too many unnecessary statements
- Poor coding causes many small statements to be read in a loop or results in data being read twice.
Poorly qualified statements, where...
- Insufficient selection criteria is given
- The database optimizer has no efficient access path to the requested data (for example, in the
case of missing indexes)
- The database optimizer does not find the best access path (due to an inappropriate execution
plan)
© SAP AG ADM520 3-22
Exclusive Lockwaits
Frontend
users
SAP Application Server
If many users are waiting
SAP Work processes for a specific lock, they may
block all other users.
Database
data
row
SAP AG 2002
In this example, a large number of SAP user requests are channeled into a smaller number of SAP
work processes on the application server.
A user holding a lock occupies an SAP work process. Other users trying to apply the same lock will
have to wait and at the same time occupy their own SAP work process.
As the number of lockwaits increases, fewer and fewer SAP user requests can be processed by the
available SAP work processes.
In the worst case (lockwaits = number of SAP work processes), a small number of users can cause
the entire SAP System to freeze.
© SAP AG ADM520 3-23
Lockwait Situations
SAP Update Requests WAITING ...
Work- MARA MARA Lock
Process 4
SAP Update Requests WAITING! Acquires
Working...
Work- MARA MARA Lock MARA Lock
Process 3
SAP Update Requests WAITING! Acquires
Work- Working... Commit
MARA MARA Lock MARA Lock
Process 2
SAP Update Acquires A long period
Work- Commit
MARA MARA Lock of processing
Process 3
Time
Locked WP 1 WP 2 WP 3
MARA by:
SAP AG 2002
When updates are programmed, the time that the program holds a lock should be kept as short as
possible.
This diagram illustrates how the wait time to acquire a lock increases when several work processes
are waiting to acquire a lock on the same object, and a long time is needed to process the update.
In order to update table MARA, work process 1 acquires a lock. Then, work process 2 requests a
lock on the same object, but has to wait for work process 1 to release its lock. Work process 1 takes a
long time processing before it performs a commit and releases the lock.
While work process 2 is waiting to acquire the lock, a lock request from work process 3 is pending.
Work process 3 has to wait until work process 2 performs a commit. Work process 3 has to wait
even longer than work process 2 because it has to wait for work process 1 and 2 to complete their
commit.
© SAP AG ADM520 3-24
Monitoring Lockwaits (1)
ANJA SAPPROD
EARLYW SAPPROD
SAPPROD
SAPPROD
EARLYW SAPPROD
SAPPROD
SAPPROD
SAPPROD
ANJA
SAP AG 2002
Exclusive lockwait situations due to database locks are shown in the SAP Database Monitor (Call
Transaction ST04 and choose Detail analysis menu →Exclusive Lockwaits).
The Host PID is the process ID of an SAP work process. The owner of the lock is shown in a blue
line and holds the status granted shown by a green signal in the first column. All blocked processes
are shown below and have the status waiting indicated by the red signal.
© SAP AG ADM520 3-25
Monitoring Lockwaits (2)
SQL Server Enterprise Manager - [Console Root\Microsoft SQL Servers\SQL Server Group\207.105.67.113 [Windows NT]\Management\Cu... _ x
Console Window Help _ x
Action View Tools
51 Items
Console Root
Microsoft SQL Servers
SQL Server Group spid 1 spid 10 spid 11 spid 179 spid 13 spid 14 spid 15
[blocked by 177]
SAPPROD [Windows NT]
Databases
Data Transformation Services spid 16 spid 17 spid 18 spid 19 spid 20 spid 21 spid 22
Management
SQL Server Agent
Backup spid 23 spid 24 spid 25 spid 26 spid 27 spid 109 spid 29
Current Activity - 14.12.2002 11:35:10 [blocked by 179]
Process Info
Locks / Process ID
Locks / Object spid 50 spid 51 spid 52 spid 53 spid 54 spid 55 spid 56
Database Maintenance Plans [blocked by 109] [blocked by 109]
SQL Server Logs
Replication
Security spid 57 spid 58 spid 59 spid 60 spid 61 spid 62 spid 63
Support Services
spid 64 spid 65 spid 66 spid 67 spid 68 spid 69 spid 70
spid 177 spid 178
[blocking]
SAP AG 2002
You can also view the current lockwait situation using the Enterprise Manager.
On the database server, choose Management →Current Activity →Locks/Process ID. All the SQL
process IDs are displayed.
Blocking is written under the icons representing the SQL process IDs that have a blocking lock
holder.
Blocked By <n> is written under the icons that have a lock waiter that is waiting for a lock held by a
SQL process ID. Blocked SQL processes are additionally marked with a red square in their icon.
The head of a locking chain is marked with a red exclamation mark (spid 177 in the above example).
© SAP AG ADM520 3-26
Too Many Statements
RDBMS Memory
Area Data Cache
WHILE ...
SELECT *
FROM MARA
WHERE ...
ENDWHILE.
SQL Connections Procedure Cache
SAP Work
Process
Database
Tables Indexes Procedure Plans
MARA_1
Y2R0000064B69RC5522MARA
MARA
SQL Trace
MARA_0
SAP AG 2002
In this example, the same SELECT statement may be executed several times. If the conditions in the
WHERE clause do not change, the same data is read several times from the database.
To find this kind of duplication, you can run an SQL Trace (Transaction ST05). This logs the
communication between the SAP work processes and the database for a particular group of SAP
users.
© SAP AG ADM520 3-27
SQL Trace
OPEN SELECT WHERE „SPRSL“ = @P000 AND „ARBGB“ = @P001 /* R3:
FETCH
OPEN SELECT WHERE „SPRSL“ = @P000 AND „ARBGB“ = @P001 /* R3:
FETCH
OPEN SELECT WHERE „SPRSL“ = @P000 AND „ARBGB“ = @P001 /* R3:
FETCH
SAP AG 2002
To start, stop, and display the SQL Trace, call Transaction ST05 or choose Tools →Administration
→Monitor →Traces →Performance Trace.
To see the time stamps and program names, select button Extended List. All statements that were
sent from the SAP work processes to the database are displayed. Identical statements are probably
caused by one statement being executed repeatedly. To directly access the corresponding ABAP
program line, choose button Display Call Position in ABAP Program.
The column Records shows the number of read or manipulated rows. The column Return code (RC)
shows the result value of the database operation. The duration (execution time) of the statement is
shown in column Duration. The time is specified as: seconds.milliseconds.microseconds
The column Statement shows the statement executed on the database which is either the contents of
the stored procedure or a direct statement. In case the content of a stored procedure is shown, the
name of the stored procedure is appended to the statement.
© SAP AG ADM520 3-28
Poorly Qualified Statements (1)
139.271 MARA PREPARE SELECT WHERE „MANDT“ = @P000 AND „BRGEW“ = @P001 /* R3:
147.068 MARA OPEN
2.809 MARA FETCH 0
145 MARA FETCH 1
290 MARA FETCH 1
1.794 MARA FETCH 0
High execution time + returning few records
SAP AG 2002
The runtime (duration) of the statement is highlighted in red if it exceeds a given threshold value of
100.000 microseconds. If a statement returns few rows and has a long execution time, the statement
must be improved.
An inefficient statement can have several consequences:
The database is kept busy processing many data blocks
CPU load is increased on the database server
An SAP work process is blocked by the report and there are high wait times for other processes
Several pages are displaced from the database buffer
The cache hit rate for other SQL statements suffers
Performance can be harmed system-wide by expensive statements
© SAP AG ADM520 3-29
Poorly Qualified Statements (2)
RDBMS Memory
Area Data cache
SELECT *
FROM MARA
WHERE MANDT = 001
AND BISMT IN (...)
SQL connections Optimizer
SAP Work
Process
Explain: Database
Execution path Tables Indexes Statistics
MARA_1
Used index MARA
Table definition MARA_0
...
SAP AG 2002
The Database Optimizer is a cost-based optimizer that minimizes the number of pages to be read. It
estimates the costs for each index used and compares these to the cost of a table scan for each table
to be accessed.
For this cost estimation, the Optimizer uses statistical information about the data distribution, which
is stored for each index in a statistics page.
There are some common reasons for long execution times:
No index exists and therefore the whole table is read sequentially in a full table scan
The index used is not very selective, so a large range has to be read in an index range scan
The Optimizer re-uses the execution plan which keeps an unsuitable strategy
The statement is badly formulated and selects much more data than is actually necessary
© SAP AG ADM520 3-30
Getting More Information
Jump to ABAP source:
• Show ABAP statement
Information from SAP Data Dictionary:
• Table fields Explanation of Query:
• Indexes and index fields • Which index is used
• Access method used
• JOIN sequence
SAP AG 2002
To determine why a statement runs slowly, you can request more details from the SQL Trace. Place
the cursor on the desired line and choose:
Button Display Details to show:
- The complete statement
- The name of the stored procedure used if a permanent stored procedure was created
- If a database cursor was used for the execution
- The parameter values and types used
Button DDIC Information to show:
- The table structure according to the SAP Data Dictionary (field definitions)
- The indexes defined on the table(s) used, as written in the SAP Data Dictionary
Button Explain to show:
- The Optimizer decisions (for the time of the explanation). That is, the indexes used and the
JOIN sequence
Button Display Call Position in ABAP Program to show:
- The related ABAP statement (if executed from ABAP)
© SAP AG ADM520 3-31
Understanding Explain SQL
Filter conditions applied afterwards Table and index name
Explain tree nodes Strategy chosen by the Optimizer Column values used for Seek
SAP AG 2002
The output of the optimizer explanation consists of the complete statement followed by the explain
tree. In the explain tree, the root node shows the statement type (for example, SELECT). All other
nodes are shown as PLAN_ROW. Except the root node, each node represents a working step
necessary for the statement execution. The execution works from the bottom to the top, to produce
the results for the statement. Everything belonging to the same node has the same indentation level.
Every PLAN_ROW node shows the corresponding step below the estimated execution costs for
processing. It also shows the estimated number of rows returned and the estimated cost for I/O and
CPU resources (these numbers have a virtual unit and are only used for comparing different
execution plans).
Above the PLAN_ROW identifier, you can see the strategy used for this step (for example, Filter
and Clustered Index Seek) followed by the parameters for the strategy (for example, filter values,
index names, index fields, operators, and values).
In this example, two steps are required to process the SELECT statement (2 * PLAN_ROW):
First, a Clustered Index Seek on index MARA~0 of table MARA is used at the leaf level to search
the index field MANDT for the value ”000”. The output is sorted by the clustered index key. The
estimated number of rows returned from this step is 211.
The results of this step are then fed into the next step, which filters the rows with the condition
BISMT = ”2” OR BISMT = ”1”. The estimated number of rows returned by this step is 1. This is
also the result of the whole statement (parent node is the SELECT).
© SAP AG ADM520 3-32
Getting Table and Index Information
1
2
1
2
6 5 1 1
4 3
7
SAP AG 2002
To display detailed information on a table and its indexes, use Transaction DB02 and choose Detail
analysis, then enter the table name.
Total rows (1) displays the current number of rows in the table.
Row modification counter (2) shows the number of changed rows since the last update statistics. An
update statistics operation resets this counter to 0.
If Auto stats (3) is set and the database option Auto update statistics is also set, SQL Server
automatically performs an update statistics when a certain percentage of the table rows have been
changed (according to the row modification counter).
Index depth (4) shows the longest path from the root page to a leaf page of the index. A clustered
index always has one level less than the same index created as a nonclustered index because the data
pages are stored as the lowest level of the index. The index height gives the number of pages to be
read for an index seek operation in the index. If a clustered index exists, the accesses through the
nonclustered indexes are followed by a clustered index access, because the cluster key is stored in
the nonclustered indexes instead of the row ID.
Last update statistics (5) displays the timestamp of the most recent update statistics operation.
Index columns (6) displays the list of the index fields.
If the database option Auto create statistics is set, column statistics (7) are created automatically by
SQL Server. These automatically created histograms have a name starting with _WA_Sys_.
© SAP AG ADM520 3-33
Index Statistics
Total number of rows in table
Date of last update for
these statistics
Density of given
columns combination
Histogram of the first index field
SAP AG 2002
To display the statistical information stored in the SAP System for each index, call Transaction
DB02 and choose Button Detailed analysis, then enter the table name. In the screen displayed, select
the requested index and choose button Show statistics.
Only a contiguous list of columns of each index is usable for a selection where the first column is
included. The index shown here can only be used if JOBNAME or JOBNAME and JOBCOUNT are
in the WHERE clause. For JOBCOUNT alone it is not useful.
The All density value is calculated by dividing 1 by the number of different values or value
combinations for the field or fields. The index is best if the combined useable columns have a very
low density and only a few duplicates exist for a given combination.
The section below shows a list of samples taken for the values of the first index column (histogram).
The total number of samples taken is displayed in column Steps in the top line. The histogram
provides statistical information about the frequency of any single value in the table. Statistics are
comprised in the following information:
RANGE_HI_KEY: Upper bound value of a histogram range (step).
RANGE_ROWS: Number of rows from the sample that fall within the range, excluding the upper
bound.
EQ_ROWS: Number of rows from the sample that are equal in value to the upper bound of the
histogram step.
DISTINCT_RANGE_ROWS: Number of distinct values within a range, excluding the upper
bound.
AVG_RANGE_ROWS: Average number of duplicate values within a histogram step, excluding
the upper bound (RANGE_ROWS / DISTINCT_RANGE_ROWS for
DISTINCT_RANGE_ROWS > 0).
The value RANGE_ROWS is equal to AVG_RANGE_ROWS multiplied by
DISTINCT_RANGE_ROWS.
© SAP AG ADM520 3-34
Finding Expensive Statements
SAP Instance Database system
Stored procedure
name cache
Y2R10000000BB1H1911DD03L
Y2A000001D9C7VK3725RSCROSS1
Y2E000011F2CBC50301REPOLOAD SQL connections
Y2R65000168C73K0943ALPERFDB
Y2C00000453CBC21832DBSYNSEQ
Y2A0000032FCAUK4623SAPLSEUw
SAP Work Y2R10000000C5GG0516TBTCP
Process Y2K00002B2DCBC21130DOKCLUi5 exec proc
Stats info
Read
Switch
stats
On / Off
tats
Sp s
SAP AG 2002
The stored procedure name cache of each SAP instance provides some additional space for storing
statistical information about the execution of stored procedures, such as the:
Duration of the slowest and average execution (in ms)
Number of executions (since the statistics were turned on)
Number of rows returned during the fastest, slowest, and average running execution
Parameter values for the execution that returned the maximum number of rows
Name of the stored procedure
To view the stored procedure statistics, call Transaction ST04 and choose Detail analysis menu
→SAP stats on SPs. By default, the stored procedure statistics is switched on (SAP instance profile
parameter dbs/mss/stats_on = 1). The statistics can be displayed for a single application server or for
all the servers. You can check the contents of a specific stored procedure or display a list of the
selected stored procedures.
© SAP AG ADM520 3-35
Stored Procedure Name Cache Statistics
SAP AG 2002
Two times are measured for every stored procedure execution; the duration without fetches and the
duration with fetches, which may be significantly higher if a larger number of rows are returned.
The number of pages read or written is not displayed as this information is not known at the SAP
System level.
To check which procedures are responsible for the most database load, sort by column ∑ ms(+fetch).
The top procedures are either slow or executed very often.
To find the slow statements that are targets for optimization, sort by column ∑ Max ms(+fetch).
Statements used for changes, such as UPDATE, DELETE, INSERT, and SELECT FOR UPDATE,
may have a high maximum time due to lockwait situations. Also, the execution times may vary
because of different parameter values. Therefore, you should sort by column Avg. ms (+fetch). The
top statements are the statements that are slow on every execution.
You can select a line and choose Button SQL statement to get the procedure text (the statements
executed within the stored procedure) and the parameter list of the call that returned the largest
number of rows (if available). You can then use the function Explain SQL to display a new explain
plan with actual parameters. You can also check the Explain SP of the precompiled procedure in the
SQL Server procedure cache to see if the stored execution plan is identical.
© SAP AG ADM520 3-36
How to Tune an Expensive SQL Statement
Poor
statement
SQL Where
DDIC Explain used list
info
Is there a Yes Good
Yes Inefficient
suitable Optimizer
No index? decision? coding?
Create or change No
secondary index Statistics Yes
in DDIC + DB page
Yes Index Yes Re-code
Update Autoupdate No statistics
statistics stats on? up to
date?
No Re-code by
specifying an
Switch on
optimizer hint
auto updstats
SAP AG 2002
In this example, we assume that no suitable index exists and that the fields in the WHERE clause are
MANDT and BISMT:
SELECT * FROM MARA WHERE MANDT = xxx AND BISMT = yyy
If you were to tune this expensive statement, you might consider creating a secondary index.
However, creating a secondary index does not solve all problems, in fact it may have its own
consequences. For example, if you increase the number of indexes on a table, the duration of
INSERTS, DELETES and UPDATES also increases.
Not every field has to be contained in the index. If you were to use the Explain SQL function for this
example, you could see that the clustered index on MANDT and MATNR was used, but it was not
very efficient. This means that the MANDT field was not helpful and should not have been selected
for the index. Since there was only one row returned, the BISMT field is probably the selective one.
Now you must determine how to create a useful index.
© SAP AG ADM520 3-37
How to Create a Useful Index
A good index
Is used in many statements
Is used often
Contains only selective fields
Is small and possibly covered
SAP AG 2002
A good index should be used in many statements.
If an index is used often, the database request time and the database load are reduced.
A selective field is a field that has:
Many different values in the real data in the table
A small number of data rows with identical values, compared to the total number of rows within
the table
If an index is small, many index rows fit on one index page. This is especially useful for index row
scans that may be triggered, for example, by comparison operators.
A covered index contains all selected fields plus all the fields in the WHERE clause. It prevents
accesses to the data pages.
© SAP AG ADM520 3-38
Determine Selective Field Combinations
184239
184239
100%
100% selectivity is fine
SAP AG 2002
To obtain the current selectivity of each combination of columns for a table, call Transaction DB02
and choose Detail analysis →Table name →Selectivity. You can select any columns that can be
indexed, other than image or text fields.
From the screen displayed you can check the:
Number of distinct entries for your selection
Total number of rows in the table
Selectivity (in percent)
If you enter a combination of fields, you cannot tell if only one of the given fields is selective or if
the combination gives a high selectivity. Therefore, you should always check the sub selections.
A more accurate (but more costly) way to get the selectivity of field combinations is to use the
density function. The result is weighted according to the number of occurrences for each distinct
value.
© SAP AG ADM520 3-39
How to Tune an Expensive SQL Statement (2)
Poor
statement
SQL Where
DDIC Explain used list
info
Is there a Yes Good Yes Inefficient
suitable Optimizer
No coding?
index? decision?
Create or change No
secondary index Statistics Yes
in DDIC + DB page
Index Re-code
Yes No Yes
Update Autoupdate statistics
statistics stats on? up to
date?
No Re-code by
specifying an
Switch on
optimizer hint
auto updstats
SAP AG 2002
In this example, we assume that a suitable index exists and that the optimizer has chosen this index,
as shown by SQL Explain.
This means you must check the coding to find the poor statement.
It is possible to jump to the ABAP coding directly from the Stored Procedure Name Cache.
© SAP AG ADM520 3-40
Example of an Expensive SELECT (in ABAP)
Poor
SELECT * FROM zlips WHERE werks = int_lips-werks
AND lgort = int_lips-lgort.
CHECK zlips-matnr IN sl_matnr.
...
ENDSELECT.
Good
SELECT * FROM zlips WHERE werks = int_lips-werks
AND lgort = int_lips-lgort
AND matnr IN sl_matnr.
...
ENDSELECT.
SAP AG 2002
In the first example, many data rows will be read unnecessarily from the database and filtered
subsequently on the SAP System level.
The second example already gives all filter conditions to the database and therefore returns only the
rows really needed to the SAP System. Finding the data on the database can also be faster because an
additional field can be used in an index seek.
© SAP AG ADM520 3-41
How to tune an expensive SQL statement (3)
Poor
Statement
SQL Where
DDIC Explain used list
Info
Is there a Yes Good Yes Inefficient
suitable Optimizer
No coding?
index? decision?
Create or change No
secondary index Statistics Yes
in DDIC + DB page
Index Re-code
Update Yes Autoupdate No statistics Yes
statistics stats on? up to
date?
No
Re-code by
Switch on
specifying an
auto updstats
SAP AG 2002
optimizer hint
In this example, the optimizer chooses an index which was appropriate when the statement was
executed with different parameters. In this case the optimizer has to be notified to recompile the
statement or to choose a specific index. This can be done by giving optimizer hints.
© SAP AG ADM520 3-42
Recompilation and Optimizer Hints
SELECT *
FROM ZMARA RDBMS Memory
area
WHERE MANDT = 001
SQL connections
Optimizer
SAP Work
Process
SELECT *
FROM ZMARA
WHERE MANDT = 002 Procedure cache
execution plan:
SAP Work
clustered index seek
Process
Execution plan is reused.
High execution time because
of inappropriate strategy
SAP AG 2002
The SQL Server determines the execution plan with the first calling of a stored procedure. All further
calls use the same execution plan until the stored procedure is compiled again (for example by the
automatic update statistics). When first executing a stored procedure, the query optimizer decides to
read the data following a certain strategy. When executing the stored procedure a second time, the
strategy might not be appropriate anymore because the stored procedure uses different values now. A
recompilation of the stored procedure forces the optimizer to create a new execution plan using a
different strategy.
Since R/3 4.6A, optimizer hints in the ABAP for SQL Server (see SAP Notes 129385 and 133381)
have been introduced. Appending the following hint to a SELECT in the ABAP has the effect that
the SQL Server creates a new execution plan every time the SELECT is executed:
%_hints mssqlnt '&REPARSE&'
This makes sense when the selectivity of an individual or a few SELECTs is extremely different.
© SAP AG ADM520 3-43
Expensive SQL Statements: Summary
An expensive SQL statement needs many reads and can
cause system-wide performance problems
Analyzing an expensive SQL statement
Statistical records → high database request time
SQL Trace → statements with long response times
Stored procedure name cache statistics → high execution times
Process overview → report and table name
Where used list → table
Tuning an expensive SQL statement
Create/change an index
Check if Auto update statistics is on
Understand the DB Optimizer
Rewrite poor coding
Use optimizer hints
SAP AG 2002
An expensive SQL statement needs many reads and can cause system-wide performance problems.
When you analyze an expensive SQL statement, you must check the:
Statistical records for the high database request time
SQL Trace for the statements with long response times
Stored procedure name cache statistics for high execution times
Process overview for the report and table name
Where used list for the table
To tune an expensive SQL statement, SAP recommends that you:
Create or change an index
Use Automatic update statistics (DB option + index option) and Auto create statistics (DB option)
Understand the DB Optimizer
Rewrite poor coding
Use optimizer hints
© SAP AG ADM520 3-44
Application Problems: Summary
Performance problems
due to:
Poor configuration
Poor database
configuration
Poor hardware
configuration
Inefficient applications
Too many Poorly
Lockwaits unnecessary qualified
statements statements
Exclusive Procedure stats, Procedure stats,
lockwaits SQL Trace SQL Trace
SAP AG 2002
Application problems typically involve either lock contention or inefficient SQL coding.
SQL problems are normally caused by statements that are being executed more frequently than
necessary or individual statements that are very expensive.
A statement can be very expensive because of poor WHERE clauses, missing indexes, or poor
optimizer decisions due to outdated statistics.
If you determine that both the database configuration and the application have been adequately tuned
and you still have performance problems, SAP recommends that you consult your hardware partner
about additional hardware. This course does not cover a hardware analysis.
© SAP AG ADM520 3-45
Summary of this Unit
Now you are able to:
Analyze the performance of your SQL Server database
system running with the SAP System
Identify the source of database performance problems
Improve database performance
SAP AG 2002
© SAP AG ADM520 3-46
Further Documentation
Books Online
SAP Installation Guide
SAP Database Administration with
Microsoft SQL Server 2000
SAP Press, ISBN: 1-59229-005-1
SAP Notes
Windows Online Help
SAP AG 2002
© SAP AG ADM520 3-47
Unit Actions
? Exercises
Solutions
SAP AG 2002
© SAP AG ADM520 3-48
Performance Monitoring and Tuning Exercises
Unit: Performance Monitoring and Tuning
Topic: Application Problems
At the conclusion of this exercise, you will be able to:
• Work with the SQL trace tool (transaction ST05)
• Detect an expensive SQL statement, analyze its cause and find
an appropriate solution
The customer detects a slow running program and decides to trace the
program using the SQL trace tool (transaction ST05). He further detects a
slow running statement, which he analyzes and tunes.
1-1 SQL Trace
1-1-1 Report ZADM520MARA1 selects a record from table ZADM520MARA,
which is a copy of master material table MARA. Call ST05 to turn on the
trace, execute report ZADM520MARA1 (in transaction SE38). In ST05,
turn off the trace, and display the trace results.
Look at the access times to table ZADM520MARA. Check for the slow
statement for the OPEN operation. Which index was used for the slow
statement?
1-1-2 Apply the appropriate change to speed up the execution. Repeat the steps
above and check the results.
© SAP AG ADM520 3-49
Performance Monitoring and Tuning Solutions
Unit: Performance Monitoring and Tuning
Topic: Application Problems
1-1 SQL Trace
1-1-1 Display the trace by choosing button Display Trace in transaction ST05.
Position your cursor on the slow statement, and choose Explain.
The clustered index ZADM520MARA~0 is used. SEEK was only
performed for field MANDT. For BISMT, the program read the entire table.
Performance could be improved by creating a secondary index on column
BISMT.
Use transaction SE11. Display table ZADM520MARA. Choose Button
Indexes .., and enter an ID for the new index.
Trace the program again and check if the new index is used.
© SAP AG ADM520 3-50
Database Backup
1 Introduction
2 SQL Server Architecture
3 How the SAP System uses SQL Server
4 Performance Monitoring and Tuning
5 Database Backup
6 Database Restore
7 Regular Maintenance and Error Handling
SAP AG 2002
© SAP AG ADM520 4-1
Database Backup
Contents
Backup Types
Performing Backups
Verifying Backups
Backup Strategies
Objectives
At the end of this unit, you will be able to:
Describe and perform the different backup types which are
supported by SQL Server using the SAP Planning Calendar and
SQL Server tools
Explain the technical implementations of the described backup
types and name the involved system tables
Verify the backups
Define a backup strategy that is suitable for your company
SAP AG 2002
© SAP AG ADM520 4-2
Importance of Database Backups
User errors Logical errors
(Such as a deleted table) (Such as a corruption)
External factors Physical errors
(Such as fire or water
(Such as a media failure)
damage)
Data
loss
To prevent data loss, a valid backup is necessary
SAP AG 2002
External factors, physical errors, and logical errors can all lead to data loss and system downtime.
An effective backup strategy and recovery plan is essential in minimizing system downtime and data
loss.
To ensure the availability of your SAP System, the backup strategy developed by your database
administrator must be carefully tested.
© SAP AG ADM520 4-3
Backup Types
Database Backup <SID> master msdb
Transaction Log Backup <SID>
<SID>
Differential Backup
Complete System Backup
SAP AG 2002
Before a database backup and restore strategy can be defined, the different backup types of SQL
Server are explained and described in detail.
SQL Server provides different backup types, which are supported by SAP:
Database backup
Transaction log backup
Differential backup
In addition to the above backup types SQL Server provides filegroup and file backups, which are not
used in the SAP environment.
In addition to backups performed by SQL Server, the Windows operating system provides a backup
tool to save files in the file system on a backup medium.
© SAP AG ADM520 4-4
Database Backup
Data
Andre Vasser Hil Frenzen
X0 X1 X2 X3 X4 X5 X6 X7
Kojak MagnumDerrick Marple
X8 X9 X10 X11 X12 X13 X14 X15
Landis
X16
Wolf
X17
Wang
X18
Kerber Kania
X19 X20
Thomas
X21 X22
Merdes
X23
1 Copy all used data pages to the backup media
Mozart
X24 X25
Bach
X26 X27
Transaction Log
Strauss Wagner Beeth.
X28 X29 X30 X31
begin update begin insert commit chkp insert rollback
LSN0 LSN1 LSN2 LSN3 LSN4 LSN5 LSN6 LSN7
delete
LSN8 LSN9 LSN10 LSN11 LSN12 LSN13 LSN14 LSN15
2 Copy all used log pages to the backup media
LSN16 LSN17 LSN18 LSN19 LSN20 LSN21 LSN22 LSN23
LSN24 LSN25 LSN26 LSN27 LSN28 LSN29 LSN30 LSN31
Set the timestamp of the backup to the time
3
when the backup has finished
SAP AG 2002
In a database backup, all user-defined objects, system tables and data are copied to a backup
medium. The database backup is done while the database is online and thus has a slight performance
impact.
Therefore the database should be backed up at times when the workload is minimal.
In addition to backing up the data pages, the transaction log is stored. Restoring the database from
this backup means that the status of the time when the backup of the database ended is reached. This
backup includes transactions that were performed during the backup. However, some operations are
not allowed during a database backup:
Creating or deleting database files
Shrinking either the database (automatically or manually) or the database files
The transaction log is not truncated when you perform a full database backup.
© SAP AG ADM520 4-5
Transaction Log Backup
Transaction Log
begin1 update begin2 insert commit2 chkp insert begin3
LSN0 LSN1 LSN2 LSN3 LSN4 LSN5 LSN6 LSN7
delete dump commit1 chkp insert insert delete delete
LSN8 LSN9 LSN10 LSN11 LSN12 LSN13 LSN14 LSN15
insert 1 Copy all used log pages to the backup media
LSN16 LSN17 LSN18 LSN19 LSN20 LSN21 LSN22 LSN23
LSN24 LSN25 LSN26 LSN27 LSN28 LSN29 LSN30 LSN31
Transaction Log
begin1 update begin2 insert commit2 chkp insert begin3
LSN0 LSN1 LSN2 LSN3 LSN4 LSN5 LSN6 LSN7
delete dump commit1 chkp insert insert delete delete
LSN8 LSN9 LSN10 LSN11 LSN12 LSN13 LSN14 LSN15
insert 2 Truncate the inactive portion of the transaction log
LSN16 LSN17 LSN18 LSN19 LSN20 LSN21 LSN22 LSN23
LSN24 LSN25 LSN26 LSN27 LSN28 LSN29 LSN30 LSN31
SAP AG 2002
When the transaction log is backed up, all used pages from the transaction log are backed up (1).
Next the transaction log is truncated, that is, the inactive part is deleted (2).
By creating transaction log backups, a database can be restored to any point in time contained within
the sequence of transaction logs, right up to the point of failure. When creating a transaction log
backup, the starting point of the backup is where the previous transaction log backup ended (if a
transaction log backup has been created). In this example with the log sequence number 0, because
no transaction log backup has been created since the full database backup. A subsequent transaction
log backup would start with log sequence number 17.
Although part of the transaction log is backed up with a database backup this is not taken into
account for the transaction log backup.
The transaction log cannot be backed up:
If the recovery model is set to simple. Read more about the recovery models in the following
slides.
Remember that a transaction log backup can only be created after a preceding full database or a
differential backup.
© SAP AG ADM520 4-6
Truncating the Transaction Log
Transaction Log 1
Determine the
Virtual Log 1 Virtual Log 2 Virtual Log 3 Virtual Log 4 Virtual Log 5
MinLSN before the
Transaction Log is
truncated
unused
Start of logical log MinLSN End of logical log
Transaction Log 2
Truncate the log
Virtual Log 1 Virtual Log 2 Virtual Log 3 Virtual Log 4 Virtual Log 5
before the start of
the Virtual Log
containing the
unused
MinLSN
Start of logical log End of logical log
SAP AG 2002
If log records were never deleted from the transaction log, the log would keep growing until it filled
all the available space on the disks holding the log files. Therefore old log records that are no longer
needed for recovering or restoring a database must be deleted to make space for new log records. The
process of deleting these log records is called truncating the log.
The active part of the transaction log can never be truncated. The active part of the log is needed to
recover the database at any time. It must always be present in the database. In case the server fails
the database must be recovered when the server is restarted. The record at the start of the active
portion of the log is identified by the minimum recovery log sequence number (MinLSN). See also
Unit 1: SQL Server Architecture.
Transaction logs are divided internally into sections called virtual log files. Virtual log files are the
unit of truncation. When a transaction log is truncated, all log records before the start of the virtual
log file containing the MinLSN are deleted.
Truncating does not mean physically deleting. It simply marks the virtual log file as inactive.
When the unused space at the end of the last virtual log is used up, the logging of the transactions
starts again at the beginning of the first virtual log.
© SAP AG ADM520 4-7
Recovery Model
Full Transaction Log
301 302 303 304 305 306 307 308
Insert Begin Tran1 Begin Tran2 Create index Update Commit Delete A001 Checkpoint
MARD … Page x A002 set ... Where ...
Create index Insert
ZMARA_1 ... MARA ….
Tran3 Tran1 Tran2 Tran1 Tran2
Bulked_logged Transaction Log
Extent x
301 302 303 304 305 306 307 308
Insert Begin Tran1 Begin Tran2 Update Commit Delete A001 Checkpoint
BCM MARD … A002 set ... Where ...
Create index Insert
ZMARA_1 ... MARA ….
Tran3 Tran2 Tran1 Tran2
Simple
Transaction Log
301 302 303 304 305 306 307 308
Insert Begin Tran1 Begin Tran2 Update Commit Delete A001 Checkpoint
MARD … A002 set ... Where ...
Create index Insert
ZMARA_1 ... MARA ….
Tran3 Tran2 Tran1 Tran2
SAP AG 2002
In SQL Server you can use predefined recovery model to support planning your backup and recovery
strategy. Recovery Models offer different levels of system performance and data security
requirements.
SQL Server provides the following three recovery models:
Full: Full Recovery provides the ability to recover the database to the point of failure or to a
specific point in time. All operations, including bulk operations such as CREATE INDEX, and
bulk loading data, are fully logged in the transaction log. Every page of the newly created index
tree gets written into the transaction log. When creating a clustered index the data pages will also
be written into the log. The transaction log is truncated only by a transaction log backup.
Bulk logged: The Bulk Logged Recovery model provides protection against media failure
combined with the best performance and minimal log space usage. Extents changed by bulk load
operations such as CREATE INDEX are marked in the Bulk Changed Map (BCM), but not
logged. All other database operations are fully logged. When the transaction log is backed up, both
the transaction log data and the marked extents are backed up. Log backups generated can only be
completely restored again.
Simple: Simple Recovery requires the least administration. In this model, data is recoverable only
to the most recent full database or differential backup. Transaction log backups are not used, and
minimal transaction log space is used. Bulk inserts and the creation of new indexes are not logged.
A log truncation occurs every time a checkpoint is processed.
You can set the recovery models for each database using the SQL Server Enterprise Manager or the
ALTER DATABASE statement. See Books Online for more details.
Check to see which model is active, using the DATABASEPROPERTYEX function, the SAP
Database Allocation Monitor (transaction code DB02) or the SQL Server Enterprise Manager. In a
productive SAP System, it is recommended that the Full Recovery Model is set for the SAP
database. Only in exceptional cases, the recovery model full can be changed to bulk logged.
© SAP AG ADM520 4-8
Differential Database Backup
DCM
DCM 1 Read the DCM pages to determine
DCM which extents have been changed
since the last full database backup
Data
Andre Vasser Schu
X0 X1 X2 X3 X4 X5 X6
Kojak Rex Derrick Marple 2 Copy all modified extents since the last
X7 X8 X9 X10 X11 X12 X13
full database backup to the backup media
Dilg Wolf Wang Kerber Kania Thom
X14 X15 X16
Transaction Log
X17 X18 X19 X20
Transaction Log
begin update begin insert commit chkp insert rollback
LSN0 LSN1 LSN2 LSN3 LSN4 LSN5 LSN6 LSN7
delete
Copy all used log pages
LSN8 LSN9 LSN10 LSN11 LSN12 LSN13 LSN14 LSN15
3 to the backup media
LSN16 LSN17 LSN18 LSN19 LSN20 LSN21 LSN22 LSN23
LSN24 LSN25 LSN26 LSN27 LSN28 LSN29 LSN30 LSN31
Set the timestamp of the backup to the time
4 when the backup has finished
SAP AG 2002
A database backup can be supplemented by differential database backups that record only the
changes made to the database after the last full database backup. Each subsequent differential
database backup records all the changes made to a database after the last full database backup. If a
database backup is created, followed by multiple differential database backups, only the database
backup and the last differential database backup created must be restored.
Differential backups are used primarily in heavily used systems where a failed database must be
brought up as quickly as possible. Differential backups are smaller than full database backups, so a
restore from a differential backup takes less time, because only the extents that contain modifications
are restored. These extents are recorded in the Differential Changed Map (DCM), which has been
introduced in Unit 1: SQL Server Architecture.
During a differential backup, SQL Server backs up the transaction log to produce a consistent
database when the database is restored.
© SAP AG ADM520 4-9
File System Backup
Directory Files
X:\<SID>DATA1 Primary data file
X:\<SID>DATA2 Secondary data file
X:\<SID>DATA3 Secondary data file
Y:\<SID>LOG1 Transaction log file
Z:\Tempdb data and log files of the tempdb
C:\Program Files\Microsoft SQL Server\8.0\
COM application programming interfaces
Copy all SAP and SQL Server
Tools tools such as Books Online 1 files to the backup media
C:\Program Files\Microsoft SQL Server\MSSQL\
Backup Default Backup directory
Binn MS SQL Server executables
Data System and sample database files Create a document containing
Install Installation scripts and logs
2 the file structure
Jobs Temporary job output files
Log Errorlogs and Joblogs File System
Repldata Working directory for replication tasks Structure
Trace Trace files created by the SQLProfiler
Upgrade Files used for upgrade
D:\usr\sap\<SID> SAP executables
trans Transport directory
D:\WINNT Windows System directory
SAP AG 2002
Perform a complete system backup of the SAP file tree, the operating system files, as well as the
database data files, the transaction log files, the SQL Server executables and system database files
onto the backup media.
To ensure that the correct actions are performed in case of a system crash, you should create a
document containing the file structure of the whole system. This document must be available and
understood by the person who performs the database restore.
© SAP AG ADM520 4-10
How to Perform a Backup
unplanned regular
Tue
Mon Tue Wed Thu Fri
Tue Sat Sun
Mon Tue Wed Thu Fri Sat Sun
Mon Tue Wed Thu Fri Sat Sun
Query Analyzer
Mon Tue Wed Thu Fri Sat Sun
Enterprise Manager
SAP Planning Calendar
DB13 or DB13C
Monthly
Backup
Cycle
SAP AG 2002
There are three ways to perform database backups :
Using the SAP Planning Calendar (transaction code DB13) or the central SAP Planning Calendar
(transaction code DB13C)
On the database level, using the Enterprise Manager tool, select: Tools → Backup Database.
On the database level, using the transact-SQL statement BACKUP.
Other backup tools can also be used, but are not covered in this course.
Course ADM100 provides a description of how to use the SAP Planning Calendar.
Do NOT use Windows Backup to perform backups of the SAP database. Windows Backup should
be used to perform a complete offline system backup.
© SAP AG ADM520 4-11
Backup Devices
TAPE
Single backup on tape
R3DUMP0
Parallel backup on R3DUMP0
TAPE
more than one tape
R3DUMP1
R3DUMP2
Database X Backup on local
DISK
or network disk device
Windows
Backup
Backup with third party
PIPE
software using named pipes
Third party
software
SAP AG 2002
You can use several different backup devices for a SQL Server backup:
One or more tape backup devices (for example, devices R3DUMP0, R3DUMP1, …; created
during the installation of the SAP System)
Disk device (local or network)
Named pipe device, used by third party software
For small SAP databases (up to around 60 GB), backups should be performed on a single tape
device, for example a DLT tape.
Larger SAP databases can be backed up using one device and several tapes, written sequentially. If
more than one tape device is available (directly connected to the computer), tapes can be written in
parallel onto these devices. A parallel backup is recommended if the time frame is small.
Backup to tape, whether to a single tape, or to multiple tapes using the sequential or parallel backup
procedure, is supported by the SAP Planning Calendar (Transaction DB13).
To manage the backup of the SAP database into a disk backup device, use SQL Server Backup.
Afterwards, use Windows Backup to back up the disk dump files onto a backup media. For this type
of database backup, use Windows Backup to restore the database backup file on disk, then use the
database restore facility with the backup file. The advantage of this method is that the backup to disk
is quite fast and another computer may then be used to perform the Windows Backup.
Third party software can also be used to perform backups. Data is transferred using named pipes.
Third party software can also be used to backup to tape devices located on a different computer than
the one where SQL Server is installed.
© SAP AG ADM520 4-12
SAP Planning Calendar - Concept
Mon 12 Tue 13 Wed 14 Thu 15 Fri 16 Sat 17 Sun 18
success success success success 0:00
Mon 19 Tue 20 Wed 21 Thu 22 Fri 23 Sat 24 Sun 25
success success
success success success 2:00
error
Mon 26 Tue 27 Wed 28 Thu 29 Fri 30 Sat 31 Sun 01
0:00 DB 0:00 DB 0:00 DB 0:00 DB 0:00 DB
9:00 Log 9:00 Log 9:00 Log 9:00 Log 9:00 Log
Mon 02 Tue 03 Wed 04 Thu 05 Fri 06 Sat 07 Sun 08
0:00 DB 0:00 DB 0:00 DB 0:00 DB 0:00 DB
9:00 Log 9:00 Log 9:00 Log 9:00 Log 9:00 Log
SAP Layer
DB Interface
Database
Layer
Job Scheduling Engine, Logs (msdb
SQLServerAgent Tables, SAP Tables)
SAP AG 2002
When creating a backup job using the SAP Planning Calendar (transaction code DB13 or DB13C)
the database interface creates a SQL command. This command defines and schedules a job using the
SQL Servers Job Scheduling Engine. These jobs call stored procedures sap_backup_databases,
sap_backup_diff_databases and sap_backup_log to back up the databases and the transaction log.
A job created by the SAP Planning Calendar can be defined to run immediately or on a recurring
schedule, for example, once a week. Once you have created a job, you can view the job definition by
double-clicking the job name or by choosing button Parameters. If the requirements of a task
change, you can modify the task by double-clicking its name and choosing Edit. If the task is no
longer needed, simply delete it by choosing button Delete.
Each scheduled job to be executed by SQL Server Agent is stored in table sysjobs on database msdb.
Their names start with SAP CCMS and can be viewed using the SQL Enterprise Manager.
© SAP AG ADM520 4-13
Scheduling Backups
CCMS Planning Calendar
Planning Goto Listing Help System
MON TUE
- New Plan for Tue 17.12.
WED THU FRI SAT SUN
BACKUP Start time 18:00:00 Period: 1
Action
Full database backup
MON TUE WED THU FRI SAT SUN
Differential database backup
Transaction log backup
Data files Check database consistency
MON TUE WED THU FRI SAT SUN
BACKUP Database(s) selection Backup device selection
MON TUE WED THU FRI SAT SUN
Database to be dumped
Select Backup devices
Transaction
log master
msdb R3DUMP0 Tape
PRD R3DUMP1 Tape
R3DUMP2 Tape
SAP AG 2002
The SAP Planning Calendar supports the following backup types out of the SAP System:
Full database backup
Differential database backup
Transaction log backup
These actions can be scheduled to run on their own; once or repeatedly. Alternatively, they can be
scheduled to run in combination with other tasks by selecting a predefined Planning pattern. A
Planning pattern specifies a combination of tasks that are executed at predefined intervals and can
be scheduled together. The Calendar offers the Planning pattern: 5 workdays, DB backup + hourly
log backup (8am - 6pm) + Monthly Check DB
To schedule the pattern, you simply need to choose it and specify a time. The SAP System
automatically assigns values to the parameters required for the execution.
To schedule a single task double-click the day on which you want a backup scheduled. In the
selection screens, mark:
Backup type required
Database(s) you want to back up
One (or more) backup device
For detailed information on how to use the SAP Planning Calendar, see the SAP Online Help.
© SAP AG ADM520 4-14
Media Names
Device names
Device names
Database
Database Type
Type Number
Number Tapecount
Tapecount
R = SAP DB
R = SAP DB D = database
D = database 01 -- 31 =
01 31 = S = sequential
S = sequential
day of
day of
the
the
M = msdb
M = msdb + = differential
+ = differential P = parallel
P = parallel
month
month
S = master
S = master L = trans. log
L = trans. log
II = combination SAP DB included
= combination SAP DB included
C = combination SAP DB not included
C = combination SAP DB not included
SAP AG 2002
The media on which the backup is performed is identified by a label. This label identifies the
contents by indicating the type and day of the backup and is given automatically when scheduling
backups using the SAP Planning Calendar. The media name is put together as follows:
The first character shows the type of data on the media:
R SAP database
S master Database
M msdb database
I More than one database is backed up on the tape and the SAP database is included
C More than one database is backed up on the tape and the SAP database is not included
The second character shows the type of backup:
D Full database backup,
L Transaction log backup
+ Differential database backup
The next two characters (numbers) show the day of the month.
The last character on the label indicates whether it is a parallel or a sequential backup
© SAP AG ADM520 4-15
Media Names (2)
Stick name label
on tape cartridge
ID15S
ID15S = One tape of a sequential SAP, master, and msdb (I) database backup
(D) on day 15 of the month
RD05P = One tape of a parallel (P) SAP (R) database backup (D) on
day 5 of the month
SAP AG 2002
Name all tapes used for backups. Type in a tape name when creating an SQL Server backup task and
stick a label with the same name on the tape medium.
The first tape label in this example has the name ID15S. Following the naming convention outlined,
previously, this label denotes an SAP database, master, and msdb full database backup, performed on
the 15th day of the month.
The second example is RD05P. This is an SAP database backup on the tape. The backup was
performed on day 05 of the month. The character P indicates a parallel backup was performed.
© SAP AG ADM520 4-16
Database and Log Backup Options
For the last Backup only
For the first Backup only
Backup Backup
Database Transaction
Log
<SID>
Log
master
msdb
SAP AG 2002
For a full database backup, use the following options:
Unload tape Rewinds the tape after the backup, set this option for a database backup
Initialize tape Existing backups are overwritten, if the expiration period is over, and the
tape name corresponds to the day the backup is performed.
Verify backup Checks backup is to see if all data on tape is readable
Expiration period Set to 27 days
For transaction log backups, set the following options as indicated:
Unload tape Set for the last transaction-log backup of the day stored on this tape
Initialize tape Set for the first transaction-log backup on the tape when the tape is reused
Verify backup Set for all backups
Expiration period Set to 27 days
When using the option Format tape, the media name and all data on the tape are overwritten, no
matter what the media retention or expiration period. Use Format tape only when new tapes are
used.
The option Initialize tape allows you to overwrite backups on a tape if the expiration period is past.
The media name is not overwritten and can be used for identifying the tape.
The option Verify executes transact-SQL command RESTORE VERIFYONLY on the chosen
backup medium after having performed the backup. We recommend that you set this option for each
backup performed. It checks that the backup set is complete and that all volumes are readable.
However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained
in the backup volumes. If the backup is valid, SQL Server returns the message: ”The backup set is
valid.” This message will be displayed in the pop-up window after double-clicking on the task. If it is
not valid, you should repeat the backup on a new medium.
© SAP AG ADM520 4-17
Database Backup with Enterprise Manager
SQL Server Backup - PRD x
General Options SQL Server Backup - PRD x
Database: PRD General Options
Name: PRD full database backup
Backup
Description: PRD Backup Database
Verifying the backup will read the entire backup and check for
Backup
media integrity. Checking the identity and expiration of the
Database - Complete media prevents accidental overwrites.
Database - Differential
Verify backup upon completion
Transaction log
Eject tape after backup
File and filegroup:
Remove inactive entries from transaction log
Destination
Check media set name and backup set expiration
Check media set name and backup set expiration
Backup To: Tape Disk
Add... Media set name: RD25S
\\ \Tape0
Remove... Backup set will expire: 27
After: days
Contents... On: Fri 01/24/2003
Overwrite Media Set Labels:
Initializing tape or disk media set erases the previous contents
Append to media
of the media and labels the media set with a name and
Overwrite existing media description.
Schedule Initialize and label media
Schedule: Media set name: RD25S
Media set description
OK Cancel Help
OK Cancel Help
SAP AG 2002
To back up the database using the Enterprise Manager, choose Tools → Backup Database. Select the
database to back up, and select Database - Complete. Select the destination and specify the option to
overwrite the existing media.
In the Option folder, different options can be set. When the backup is completed, you should verify
the media integrity of the backup. An expiration period must be set to prevent the media to be
overwritten within 27 days. A media name must be specified. Use the SAP naming conventions
explained earlier in this chapter.
You can execute the backup immediately, or you can schedule it for execution by choosing Schedule
and specifying the appropriate information in the Schedule Backup dialog box that is displayed.
The database can be backed up using the Transact SQL command BACKUP DATABASE which is
described in detail in the SQL Server Books Online.
© SAP AG ADM520 4-18
Log Backup with Enterprise Manager
SQL Server Backup - PRD x
General Options SQL Server Backup - PRD x
Database: PRD General Options
Name: PRD transaction log backup
Backup
Description: PRD Transaction Log
Verifying the backup will read the entire backup and check for
Backup
media integrity. Checking the identity and expiration of the
Database - Complete media prevents accidental overwrites.
Database - Differential
Verify backup upon completion
Transaction log
Eject tape after backup
File and filegroup:
Remove inactive entries from transaction log
Destination
Check media set name and backup set expiration
Backup To: Tape Disk
Add... Media set name: RL25S
\\ \Tape0
Remove... Backup set will expire: 27
After: days
Contents... On: Fri 01/24/2003
Overwrite Media Set Labels:
Initializing tape or disk media set erases the previous contents
Append to media
of the media and labels the media set with a name and
Overwrite existing media description.
Schedule Initialize and label media
Schedule: Media set name: RL25S
Media set description
OK Cancel Help
OK Cancel Help
SAP AG 2002
To back up the transaction log using the Enterprise Manager, choose Tools → Backup Database.
Select the database you want to back up, then select Transaction log. Select the destination. Specify
whether you want to overwrite an existing medium or whether you want to append the backup to the
backup sets already on the medium.
In the Option folder different options can be set. When the backup is completed, you should verify
the media integrity of the backup. An expiration period must be set to prevent the media from being
overwritten within 27 days. A media name must be specified. The inactive portion of the transaction
log should be removed. Use the SAP naming conventions explained earlier in this chapter.
You can execute the backup immediately, or you can schedule it for execution by choosing Schedule
and specifying the appropriate information in the Schedule Backup dialog box that is displayed.
The database can be backed up using the Transact SQL command BACKUP LOG which is
described in detail in the SQL Server Books Online.
© SAP AG ADM520 4-19
Tape Options
SAP AG 2002
When the SQLServerAgent service is started, it queries the system tables in the msdb database to
determine what jobs to start. If a backup job is scheduled, SQL Server Agent will pass the backup
command to SQL Server. If the tape device is not ready at the specified time, SQL Server will
normally cancel the job and write an error to the error log. The following timeout values are
available to overcome such a problem:
Wait indefinitely: Wait indefinitely for SQL Server to respond.
Try once then quit: Try once and then time out when waiting for SQL Server to respond.
Try for minute(s): Specify the time (in minutes) to try before timing out when waiting for SQL
Server to respond.
We recommend trying for 10 minutes and then send a timeout message.
© SAP AG ADM520 4-20
File System Backup using the Windows Backup Tool
Distributed Transaction .. Coordinates Transac… Manual Local System
MSSQLSERVER Automatic PRDadm
MSSQLServerAdHelper Manual Local System
SQLSERVERAGENT Automatic PRDadm
SAPPRD_00 Automatic SAPServicePRD
SAPOSCOL Automatic SAPServicePRD
SAP AG 2002
The entire system, including all Windows, SAP and SQL Server files must be backed up regularly
using the Windows Backup utility. The backup of these files is necessary for a restore operation
when the disk on which SQL Server and SAP executables are located crashes. In addition, it serves
as an additional backup that may play a vital role in dealing with emergency situations where other
routine backups have been damaged. Choose Start → Programs → Accessories → System Tools →
Backup to open the Windows Backup tool. For detailed information see the Windows Online Help.
Windows, SAP and SQL Server files need to be backed up:
After installing a Windows or SQL Server Service pack
Before special actions such as an SAP Upgrade
Windows Backup Tool only backs up closed files. Therefore the services MSSQLServer,
SQLSERVERAGENT, SAP<SID>_<InstanceNo> and the SAPOSCol must be stopped.
To ensure that the whole system is backed up, you should install an additional Windows system
(Help Windows), which is only used when backing up the system. For information on how to
proceed, refer to the Windows documentation.You must ensure that you do not back up the local
registry. If an additional Windows is not available, you must also back up the local registry.
Never use Windows backup for regular database backups that are part of a predefined backup and
restore strategy. When you restore such a Windows backup, the database and transaction log files are
restored to the state they were at the time you ran the Windows Backup. No subsequent differential
or log backups can be applied. Therefore you can not do a point-in-time database recovery. This
means that the changes you made to the database since the last Windows Backup are lost and cannot
be redone. Instead, always use SQL Server-based backups for regular database and log backups.
© SAP AG ADM520 4-21
Backup Requirements and Costs
Backup time Long
?
Short
Restore time Long
?
Short
High availability No
?
Yes
Training Short
?
Long
Acquisition costs Low
?
High
Administrative workload Low
?
High
SAP AG 2002
Requirements. Depending on your situation, you may need one, or a combination of several,
advanced backup scenarios. When you define your backup scenario, you must consider:
How long it takes to perform a backup
The maximum time required for a complete database restore
To what extent your backup strategy provides additional security in case of a hardware failure
Costs. Your backup scenario will also depend on:
How much training the administrator requires
The amount of database administration required for implementation and production operation
The acquisition costs
Now that you are familiar with the different backup types supported by SAP and Microsoft tools, we
can introduce various advanced backup scenarios. The following slides introduce various advanced
backup scenarios supported by SAP tools, ranked according to the above criteria using a ”smiley
matrix.” This ranking is intended as a rough guideline only.
© SAP AG ADM520 4-22
Single DB and Transaction Log Backups
Database Backup Transaction Log Backup
msdb
master <SID>
<SID> <SID>
<SID>
DB13: DB13: Backup
Full daily Database Backup Transaction Log Backup time
of master, msdb and <SID> of <SID> to one tape Restore
to one tape each hour time
High
availability
Training
Acquisition
ID01S RL01S costs
Administrative
workload
SAP AG 2002
This scenario is the reference point for ranking the scenarios.
A full database backup of the SAP database should be performed daily on a permanent storage
media, for example, a data tape. This backup type records the complete state of the data in the
database at the time the backup operation completes. The master database and the msdb database
must also be backed up daily.
The transaction log of the SAP database must also be backed up in order to restore the database to a
specific point in time. The transaction log contains information on database changes, which is used
for roll forward and roll back operations. SAP recommends scheduling log backups every 30-60
minutes. The number of transaction log backups depends on the size of the transaction log (normally
1.5 GB), and the transaction rate of your system.
Transaction log backups are much smaller in size than database backups, as they only contain the
changes since the last transaction log backup. Therefore, a transaction log backup has a low impact
on system performance, if performed during normal operation.
A transaction log must be backed up before the transaction log file is full. If the transaction log is
full, no further database transactions can be performed. See Unit 6: Regular Maintenance and Error
Handling on a solution on how to handle a full transaction log. After the transaction log backup, all
log information which is not active is truncated out of the transaction log file.
The databases and transaction log backups must not be written to the same backup device.
SAP recommends this strategy because it meets the needs of most customers.
© SAP AG ADM520 4-23
Parallel Tape Support
Database Backup Transaction Log Backup
msdb
<SID>
master <SID>
<SID> <SID>
DB13: DB13:
Full daily Database Backup Transaction Log Backup Backup
of master, msdb and <SID> of <SID> to more than time
to more than one tape one tape each hour Restore
time
High
availability
Training
ID01P
RL01S
Acquisition
costs
ID01P RL01S
ID01P Administrative
workload
SAP AG 2002
To reduce the backup time, as well as the time required for restoring the database and the transaction
log, transaction DB13 supports the parallel use of multiple devices. This strategy can also be used if
all databases or the volume of all transactions logs to be backed up will not fit on one single tape and
an automatic or manual device changing is not possible. A disadvantage may be that if one device
cannot be read, the whole backup is not restorable. Therefore we recommend that you always verify
the structure of the backup on the device as explained in previous slides.
© SAP AG ADM520 4-24
Two-Step Disk Backup
Fast 1st step DB13:
Full daily Database Backup
msdb of master, msdb and <SID> DISK_DEV1
master to DISK_DEV1
<SID>
DISK_DEV2
“Slow” 2nd step
<SID> Windows Backup Backup
<SID> time
<SID>
Restore
time
Fast 1st step DB13:
High
Transaction Log Backup of availability
“Slow” 2nd step
<SID> to DISK_DEV2 each
Windows Backup
hour Training
Acquisition
costs
RL01S ID01S Administrative
workload
SAP AG 2002
A two-step disk backup is performed as follows: First, a backup is made to disk. Typically, this is
faster than a backup to tape. Second, the files are backed up from disk to tape. The advantages of this
method are that it:
Reduces backup time
Reduces restore time (if the required files are still available on disk)
Backups can be saved to remote disks, through shared folders
First, additional backup devices on disk must be created for the database backups and the transaction
log backups. Enough disk space to hold these backups must be available. Note that the size of a
database backup corresponds roughly to the used size of the database because only used pages are
copied to the backup media.
Second, the files are copied to tape using Windows Backup or another third-party tool.
If the backups on disk are still available when a restore is necessary, the restore time is reduced. If
the backups are no longer available, the restore will need to be performed from tape.
The advantage of this strategy is that backups that are performed to a remote disk are physically
separated from the server. In case of fire or flood, the backup on the remote disk may still be
available.
© SAP AG ADM520 4-25
Supplementary Differential Backups
Sun Mon ... Fri
msdb
<SID>
master <SID>
<SID>
<SID> <SID>
DB13: DB13: DB13:
Full weekly Database Differential daily Transaction Log Backup Backup
Backup of master, msdb Database Backup of of <SID> to one tape time
and <SID> to one tape <SID> to one tape each hour Restore
time
High
availability
Training
Acquisition
ID01S R+02S RL02S costs
Administrative
workload
SAP AG 2002
Using a full database, differential database, and transaction log backups together can reduce the time
needed to restore a database back to any point in time after the database backup was created.
Additionally, creating both differential database and transaction log backups can increase the
robustness of backup procedures in the event that either a transaction log backup or differential
database backup becomes unavailable, for example, due to media failure. Differential backups
reduces the runtime of the backup itself because during the backup procedure the DCM page is read
to determine which extents have changed.
Typically combined database, differential database, and transaction log backups involve creating
database backups at longer intervals, differential database backups at medium intervals, and
transaction log backups at shorter intervals. For example, create database backups weekly,
differential database backups daily, and transaction log backups hourly.
© SAP AG ADM520 4-26
Hot-Standby Server
Production server Standby server
Full Database Backup
of master, msdb and <SID>
DISK_DEV1
msdb to DISK_DEV1 once msdb
master DB13: master
Full Database Backup of
<SID> <SID>, master and msdb
<SID>
to tape
DB13:
Transaction Log Backup of DISK_DEV2
<SID> to DISK_DEV2 each Backup
<SID> hour <SID> time
<SID> <SID> Restore
<SID> <SID>
time
Daily Windows Backup of Daily Windows Backup of High
DISK_DEV1 DISK_DEV2
to tape to tape
availability
Training
Acquisition
costs
ID02S RL02S Admin.
workload
SAP AG 2002
A standby server is a second server that can be brought online in the event that the production server
fails. The standby server contains a copy of the databases on the production server, including the
system databases. This copy is maintained by initially backing up the databases on the production
server and restoring them once on the standby server. Periodically, transaction log backups from the
databases on the production server are applied to the standby server to ensure that the standby server
is synchronized with the production server. If the production server fails, the standby database can be
opened, and can take on the role of the production database.
The transaction log backups from the production server are applied on the standby database. This
allows a delayed restore on the standby server in case of a possible user error. On the standby server,
Windows backups to tape are performed.
NOTE: Certain structural changes on the production database are not automatically replicated on the
standby database. In this case, the recovery is stopped, and the database administrator must resolve
the situation manually. The creation and deletion of datafiles on the primary server are not
automatically replicated on the standby server.
This scenario is highly dependent on the implementation, and you must therefore plan the
environment in detail. See Books Online for a detailed description.
© SAP AG ADM520 4-27
Monitoring Backups
Tue
Mon Tue Wed Thu Fri
Tue Sat Sun
Mon Tue Wed Thu Fri Sat Sun
! i
? Mon Tue Wed Thu Fri Sat Sun
Mon Tue Wed Thu Fri Sat Sun
Event Viewer SAP Planning Calendar
(DB13 or DB13C)
Enterprise Manager
Monthly Database backups
Backup
Cycle
CCMS Database Backup
History (DB12)
Explorer
SAP AG 2002
You must check regularly whether the backup tasks were completed successfully. Always check
that:
The most recent backup has run successfully
All the backups in the backup cycle are being executed according to the schedule. Gaps in a
backup sequence can have serious consequences in a subsequent attempt to restore the database.
The backup and restore process works successfully and enables you to restore your database to a
correct and consistent state.
There are different ways of monitoring single backups of databases and transaction logs:
Use the SAP Planning Calendar (transaction DB13 or DB13C)
If the SAP System is unavailable, use the Enterprise Manager and the Event Viewer to check
backup task execution.
To display the SQL Server error log, use the Explorer or the Enterprise Manager. Using the
Explorer, select the drive and the MSSQL\LOG\ path. The current error log is in file
ERRORLOG. All recent error logs can be found in files ERRORLOG.1 to ERRORLOG.6. By
default SQL Server maintains up to seven error logs. The number of error logs can be configured
in the Enterprise Manager.
To display the SQL Server Agent error log, use the Explorer or the Enterprise Manager. Using
the Explorer, select the drive and the MSSQL\LOG\ path. The extension of each archived error
log indicates the age of the error log. For example, an extension of .1 indicates the newest
archived error log.
The backup cycle must be checked using the SAP Planning Calendar (transaction DB13) or the
CCMS Database Backup History (transaction DB12).
© SAP AG ADM520 4-28
Monitoring using the SAP Planning Calendar
Mon 12 Tue 13 Wed 14 Thu 15 Fri 16 Sat 17 Sun 18
success success success success 0:00
Mon 19 Tue 20 Wed 21 Thu 22 Fri 23 SQL
Sat 24 task information
Sun 25
************** Task information ******************
success success success success 2:00 Jobname: SAP CCMS Log Backup of PRD
error Taskname: step1
Type: TSQl
DB-Name: PRD
Mon 26 Tue 27 Wed 28 Thu 29 Fri 30 Lastrun: 20021118 20000
Nextrun: 20021125 20000
0:00 DB 0:00 DB 0:00 DB 0:00 DB 0:00 DB Created: Nov 14 20025:18PM
9:00 Log 9:00 Log 9:00 Log 9:00 Log 9:00 Log Command: sap_backup_log @r3db="EW1",@
@bdev="R3DUMP0",@expdays=28
Description: SAP CCMS JOB
Mon 02 Tue 03 Wed 04 Thu 05 Fri 06
*************Task history information **********
0:00 DB 0:00 DB 0:00 DB 0:00 DB 0:00 DB For run: 20021118 020000
9:00 Log 9:00 Log 9:00 Log 9:00 Log 9:00 Log Status: 1 (success)
Message: 4029
Severity: 10
Duration: 0 hours, 56 minutes, 29 seconds
Last message: DBCC execution completed. If DBCC printed
SAP Layer error messages, contact your system admin
DB Interface
Database Layer
Job Scheduling Engine
msdb tables
SQL ServerAgent
SAP AG 2002
You can check whether your most recent database and transaction log backups are usable in the SAP
Planning Calendar. It is best to check the backup immediately after the backup has finished, before
you remove the tape.
You should check whether the transaction log backups are completed successfully on a daily basis.
Omitting such a check could have serious consequences. In the event of a hardware crash and a
subsequent restore operation, this may result in the loss of several hours of data.
The status of a finished task is marked red if the task failed, or green if the task was completed
successfully. To display more information on a completed task, select the task and double-click.
Each task not run successfully should be checked immediately.
The primary attributes of a backup job scheduled using the SAP Planning Calendar are:
Jobname: The name of the job. Backups scheduled using the SAP Planning Calendar always
begin with SAP CCMS [Log | Database ] of <database list> [timestamp]. The timestamp
specifies the scheduling time and is always unique.
Job Step: A job step is an action that the job takes on a database or a server. The job step is
defined by the transact-SQL statement (TSQL) sap_backup_databases,
sap_backup_diff_databases or sap_backup_log.
The task history section returns information on the job and the job step execution. This information
is read from msdb table sysjobsteps, which contains the information for each step in a job to be
executed. Table sysjobhistory contains information about the execution of scheduled jobs.
If the option verify has been chosen, the runtime of the scheduled is longer even though the backup
itself is already finished. The verification of the backup is done after the backup is completed and
belongs to the job, but not to the backup.
© SAP AG ADM520 4-29
Monitoring using the Database Backup History
SAP AG 2002
Checking individual backups is not sufficient to ensure that your backup strategy is being
implemented successfully. You should also periodically check all of your database backups together
to make sure that your strategy is being adhered to and there are no gaps in the sequence.
In the SAP System, the Database Backup History (transaction DB12) offers an overview of all
database backups and all transaction log backups scheduled in the SAP Planning Calendar.
Choose Backup History and then All Backups. A result list appears showing technical details about
all backups that have been executed for the last 30 days (default). Select a backup and choose
History for additional details.
If a backup failed diagnose exactly what happened. To do so, it is necessary to also look at the SQL
Server error logs.
The Backup Device List displays all the defined backup devices.
© SAP AG ADM520 4-30
Verification Using SQL Enterprise Manager
Name Enabled Runnable Scheduled Status Last Run Status Next Run Date
SAP CCMS Blocking Lockstats Yes Yes Yes not running succeeded (10/30/20 10/30/2002 10:05:00 AM
X SAP CCMS Full DB Backup of PRD, master Yes Yes Yes not running failed 11/03/1998 10:00:00 PM
SAP CCMS Full DB Backup of PRD, master Yes Yes Yes not running succeeded (10/28/20 11/04/2002 10:00:00 PM
SAP CCMS Full DB Backup of PRD, master Yes Yes Yes not running succeeded (10/29/20 11/05/2002 10:00:00 PM
SAP CCMS Full DB Backup of PRD, master Yes Yes Yes not running succeeded (10/30/20 11/06/2002 10:00:00 PM
SAP CCMS Full DB Backup of PRD, master Yes Yes Yes not running succeeded (10/31/20 11/07/2002 10:00:00 PM
X SAP CCMS Log Backup of PRD Yes Yes Yes not running failed 11/03/2002 10:00:00 AM
SAP CCMS Log Backup of PRD Yes Yes Yes not running succeeded (10/27/20 11/03/2002 11:00:00 AM
SAP CCMS Log Backup of PRD Yes Yes Yes not running succeeded (10/27/20 11/03/2002 12:00:00 AM
SAP CCMS Log Backup of PRD Yes Yes Yes not running succeeded (10/27/20 11/03/2002 01:00:00 PM
X SAP CCMS Log Backup of PRD Yes Yes Yes running failed 11/03/2002 02:00:00 PM
SAP CCMS Log Backup of PRD Yes Yes Yes not running Unknown 11/03/2002 03:00:00 PM
SAP CCMS Log Backup of PRD Yes Yes Yes not running succeeded (10/27/20 11/03/2002 04:00:00 PM
SAP AG 2002
To display the job history in the Enterprise Manager, select the folder Management, SQL Server
Agent, item Jobs.
All the jobs scheduled from the SAP System begin with 'SAP‘. For example, 'SAP CCMS DB
backup of <Databases>'. Jobs whose status is ‘failed' should be analyzed in detail. Double-click the
job to retrieve more information. Do NOT use the Enterprise Manager to change the attributes of
jobs coming from the SAP System. They should always be modified using the SAP Planning
Calendar.
SQL Server keeps the history data of backups and jobs in system tables in database msdb. Some
system tables were mentioned in previous slides. Tables that contain information about backups
include:
backupfile: contains one row for each database and log backup made
backupset: contains one row for each backup set
© SAP AG ADM520 4-31
Backup Errors - Additional Information
SAP AG 2002
If a backup error occurs, you can find additional information in the Event Viewer and in the database
error log. Further notification in case of a failure of the scheduled job can be defined using the
Enterprise Manager.
Select Start → Settings → Control Panel → Administrative Tools → Event Viewer to open the Event
Viewer.
© SAP AG ADM520 4-32
Summary of this Unit
Now you are able to:
Describe and perform the different backup types which
are supported by SQL Server
Explain the technical implementations of the described
backup types and name the involved system tables
Define a backup strategy that is suitable for your
company and implement the scenario
Verify the backups
SAP AG 2002
© SAP AG ADM520 4-33
Further Documentation
ADM100 mySAP Technology Administration
SQL Server Books Online
SAP Online Help
Windows Online Help
SAP AG 2002
© SAP AG ADM520 4-34
Unit Actions
? Exercises
Solutions
SAP AG 2002
© SAP AG ADM520 4-35
Database Backup Exercises
Unit: Database Backup
Topic: Performing Backups
At the conclusion of this exercise, you will be able to:
• Perform and verify database and transaction log backups using the
SAP Planning Calendar, the SQL Server Enterprise Manager and
transact-SQL commands
The customer chooses a suitable backup strategy that meets the
requirements for his company and implements the backup strategy using
the SAP Planning Calendar and SQL Server tools.
1-1 Back up the database using the SAP Calendar. Perform a full database backup using
the SAP Planning Calendar, as described in Unit 4: Database Backup.
1-1-1 Call the SAP Planning Calendar. Schedule a full database backup of all
three databases recurring weekly at 10.00 p.m. to backup device
R3DUMP4.
Which options do you have to set?
1-1-2 Schedule a Transaction Log Backup for the next day at 12.00 p.m. to
backup device R3DUMP5.
Which options do you have to set?
1-1-3 Check the parameters of your scheduled tasks.
1-2 Back up the database using the SQL Enterprise Manager. Perform an immediate
full database backup of databases test and master and backup the transaction log of
database test using the SQL Enterprise Manager.
1-2-1 Open the SQL Enterprise Manager. On your local SQL Server perform a
scheduled immediate full database backup of databases test and master to
backup devices R3DUMP1 and R3DUMP2.
What are the naming conventions for the media set name, assuming that test
is the SAP database?
What other options do you set?
1-2-2 Perform a scheduled immediate backup of the transaction log of database
test to R3DUMP3.
What options will you set?
What are the naming conventions for the volume labels assuming that test is
the SAP database?
1-2-3 Check the backups.
© SAP AG ADM520 4-36
1-3 Back up the database using the Query Analyzer. Make an immediate database
backup of msdb using Query Analyzer commands. If necessary, check Books
Online on the correct syntax on the BACKUP command.
1-3-1 Open a Query Analyzer window. Enter the correct transact-SQL command
to back up database msdb to backup devices R3DUMP1 and R3DUMP2 and
execute it. Append the backup to the existing backups of databases test and
master on the two backup devices.
1-3-2 Back up the transaction log of msdb to R3DUMP5 using the correct
transact-SQL command.
Are you able to perform a backup?
If not, what error message is displayed?
© SAP AG ADM520 4-37
Database Backup Solutions
Unit: Database Backup
Topic: Performing Backups
1-1 Back up the database using the SAP Calendar. Perform a full database backup using
the SAP Planning Calendar, as described in Unit 4: Database Backup.
1-1-1 Call the SAP Planning Calendar under Tools → CCMS → DB
Administration → Planning Calendar → DB13 – Local (transaction code
DB13). Double-click today’s date. In the dialog box, enter Database Backup
and the correct time (10:00 p.m.). In the next dialog box, databases master,
msdb and DEV must be selected. In the Backup Device Selection dialog box
select backup device R3DUMP4.
Set the expiration period to 27 days.
You need to set the following options: initialize, unload tape, and verify.
1-1-2 Call the SAP Planning Calendar under Tools → CCMS → DB
Administration → Planning Calendar → DB13 – Local (transaction code
DB13). Double-click tomorrow’s date. In the dialog box, enter Transaction
Log Backup and the correct time (12:00 p.m.). In the Backup Device
Selection dialog box, select backup device R3DUMP4.
Set the expiration period to 27 days.
Also set nounload, initialize device, and verify since it is the first backup
you are performing on R3DUMP5.
1-1-3 To check the parameters, select the scheduled task and choose Parameters.
1-2 Back up the database using the SQL Enterprise Manager. Perform an immediate
full database backup of databases test and master and backup the transaction log of
database test using the SQL Enterprise Manager.
1-2-1 Open the SQL Enterprise Manager. Choose Tools → Backup Database.
Select the database (test) from the database list, type a name and a
description, and select Database - complete.
In the destination list, select both backup devices R3DUMP1 and
R3DUMP2. If only one backup device is displayed in the list, add a second
device by choosing button Add.
Choose radio button Overwrite existing media.
Choose Schedule. In the Edit Schedule window select One Time to run the
backup immediately. Choose OK when you have specified all the
information for the backup.
© SAP AG ADM520 4-38
Under tab Options, set flag Initialize and label media and enter the media
set name (ID<dd>P, where <dd> is the day of the month.) and set the
backup to expire after 27 days.
Do the same for database master, but make sure that you set the Append to
media option. This ensures that the backup will be appended to the devices.
In the Option folder, you can check the media set name and expiration
period.
1-2-2 Open the SQL Enterprise Manager. Choose Tools → Backup Database.
Select the database (test) and Transaction Log. Select backup device
R3DUMP3.
Schedule the backup for immediate execution and specify a name for the
task.
Under tab Options, select Initialize and label media and Expires after 27
days. Remove inactive entries from the transaction log.
The volume name for the test transaction log backup is RL<dd>S, where
<dd> stands for the day of the month.
1-2-3 Check the run status, content and volume label of the scheduled backups by
choosing Management → SQL Server Agent → Jobs.
If a backup was completed successfully, the SQL Server error log will show
a message similar to this:
Database backed up: Database: test, creation date(time):
2003/02/18(18:28:24), pages dumped: 125, first LSN: 7:164:1, last LSN:
7:168:1, number of dump devices: 2, device information: (FILE=1,
TYPE=DISK, MEDIANAME='ID18P': {'R3DUMP1', 'R3DUMP2'}).
1-3 Back up the database using the Query Analyzer. Make an immediate database
backup of msdb using Query Analyzer commands. If necessary, check Books
Online on the correct syntax on the BACKUP command.
1-3-1 In the Enterprise Manager, choose Tools → SQL Server Query Analyzer.
Alternatively you can directly open a Query Analyzer window. Issue the
following command:
BACKUP DATABASE msdb to R3DUMP1, R3DUMP2 WITH
NOINIT, MEDIANAME = ‘ID<dd>P’
where MEDIANAME is the name used in exercise 1-2-1.
If the backup is successful, the following message will be returned:
Processed 1456 pages for database 'msdb', file 'MSDBData' on file 3.
Processed 1 pages for database 'msdb', file 'MSDBLog' on file 3. BACKUP
DATABASE successfully processed 1457 pages in 8.370 seconds (1.425
MB/sec).
© SAP AG ADM520 4-39
1-3-2 Issue the following command:
BACKUP LOG msdb TO R3DUMP5
It returns with message:
Server: Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is
SIMPLE. Use BACKUP DATABASE or change the recovery model using
ALTER DATABASE.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
© SAP AG ADM520 4-40
Database Restore
1 Introduction
2 SQL Server Architecture
3 How the SAP System uses SQL Server
4 Performance Monitoring and Tuning
5 Database Backup
6 Database Restore
7 Regular Maintenance and Error Handling
SAP AG 2002
© SAP AG ADM520 5-1
Database Restore
Contents
Restore strategy and scenarios
Restore methods
Checking restore
Objectives
At the end of this unit, you will be able to:
Perform a database restore using SQL Server tools
Describe the different restore methods
Check the restore
SAP AG 2002
© SAP AG ADM520 5-2
Goals of a Database Restore Strategy
Physical errors
(Such as a media failure)
External factors
(Such as fire or water
Database restore without loss of data
damage)
User errors
(Such as a deleted table) Restoring the database to a specific point in time
Logical errors
(Such as a corruption) Database restore in a specific time window
(for various scenarios)
Procedure and
escalation plan
Document your procedures and escalation plans
SAP AG 2002
If data is lost due to external factors, such as fire or water damage to your hardware, or physical
errors, such as a hardware failure, you must restore the database up to the point in time when the
database crashed. If a full restore is possible and the backup strategy is set up properly, only data
from uncommitted transactions before the error will be lost.
If data is lost due to logical errors, such as an unintentionally deleted table, you must restore the
database up to a point in time shortly before the error occurred.
To ensure that the correct actions are performed for each of the different scenarios, create a
document containing organizational descriptions of procedures and an escalation plan. This
document must be available and understood by the person who performs the database restore.
© SAP AG ADM520 5-3
Principles of a Database Restore
Availability
ONLINE
Target
Replace hardware
Actual and set up system
Apply Apply
Problem Restore Automatic
differential transaction
analysis database recovery
backups logs
OFFLINE
Time
SAP AG 2002
An appropriate backup and restore strategy ensures minimum down time when the SAP System
becomes unavailable due to an error. SAP recommends that you plan these strategies in advance and
test them in your test system.
When you plan your backup strategy, consider how long you can afford to shut down the SAP
System in different scenarios. To calculate the total time required for a full database restore, include
the following times required for:
Analyzing the error
Replacing the required hardware, and setting up the operating system and file systems
Restoring the database from the database backup
If differential backups were made, apply the last differential database backup
Performing a forward recovery from the backed up transaction logs
Performing an automatic recovery at server startup
The maximum downtime of a productive SAP System is the time from when the error is noted to the
time when the administrator finishes checking the restored system.
The database is restored as follows:
The content of the database backup is copied from the backup medium to the database (Restore).
If differential backups were made, the last differential database backup is copied.
All transaction log files are imported from the backup medium to the database.
The automatic recovery rolls back uncommitted transactions and rolls forward committed
transactions.
© SAP AG ADM520 5-4
Automatic Recovery (1)
Data Cache
Begin Tran1
x1
SQL Server
User 1 x2
Step 1
Begin Tran2
<SID>DATA1 <SID>LOG1
User 2
Commit Tran1
x1
dirty
SQL Server
Begin Trans1 x2
dirty
User 1 Begin Trans2
Trans1: update Step 2
Trans2: insert
Commit Trans1 Action written to
transaction log
User 2
SQL Server goes down due to power outage before checkpoint occurs Step 3
SAP AG 2002
SQL Server stops processing for example, if an operator restarts the server while users are connected
and working in databases or in case of a sudden power outage. An automatic recovery is then carried
out when SQL Server is restarted. This ensures that all transactions committed before system failure
are physically entered in the database because the corresponding modified data pages might not have
been flushed to the data files yet. All other partially completed and thus uncommitted transactions
are rolled back. This principle is explained in the example shown.
Two transactions, TRAN1 and TRAN2, are started by two different users. The transactions are
recorded in the transaction log as BEGIN TRAN1 and BEGIN TRAN2. Both transactions change
pages in the data cache, and these pages are marked dirty.
In the second step, user 1 ends the transaction with a COMMIT TRAN1. SQL Server writes all log
information on Transaction TRAN1 in the transaction log. COMMIT confirms that all the affected
log records in the cache are written to the transaction log.
A checkpoint causes SQL Server to write the data pages marked dirty to the database. In this
example however, SQL Server and the Windows Server go down before the checkpoint, due a power
outage.
© SAP AG ADM520 5-5
Automatic Recovery (2)
Automatic recovery
Begin Trans1
Begin Trans2 Written TRAN1
Roll back Trans1: update
to database
<SID>DATA1 Step 4
Trans2: insert
Commit Trans1
Date Source Message
03/01/21 08:54:07.43 spid1 Recovering of database PRD (7) is 0% complete (approximately 21 more seconds)
03/01/21 08:54:07.44 spid1 Recovering of database PRD (7) is 100% complete (approximately 0 more seconds)
03/01/21 08:54:07.45 spid1 Recovery is checkpointing database PRD (7).
03/01/21 08:54:07.46 spid1 Recovering database ‘PRD’
03/01/21 08:54:07.47 spid1 Starting up database tempdb
03/01/21 08:54:07.48 spid1 Analysis of database tempdb is 100% complete.
03/01/21 08:54:07.43 spid1 Recovery completed
SAP AG 2002
In the fourth step, the Windows Server and SQL Server are started again. Whenever SQL Server is
started, the automatic recovery is processed in each SQL Server database:
The LSN of the last checkpoint is read from the database boot block along with the Minimum
Recovery LSN.
The transaction log is scanned from the Minimum Recovery LSN to the end of the log. All
committed dirty pages are rolled forward by redoing the logical operation recorded in the log
record.
SQL Server then scans backward through the log file rolling back all uncompleted transactions by
applying the opposite of the logical operation recorded in the log records.
This type of recovery is also done by a RESTORE statement. After restoring the copy of the
database or log, the RESTORE statement also has to ensure that all committed log records are rolled
forward and all uncompleted transactions are rolled back.
Check the result of an automatic recovery in the SQL Server error log.
SQL Server provides configuration parameter Recovery interval (min) for automatic recovery. It
defines the maximum time in minutes needed for an automatic recovery per database. SQL Server
uses this option to determine when a checkpoint must be released. The default is 0, indicating
automatic configuration by SQL Server. In practice, this means a recovery time of less than one
minute and a checkpoint approximately every one minute for each database.
© SAP AG ADM520 5-6
Restore Scenarios
External factors Physical errors
(Such as fire or water (Such as a media failure)
damage)
Possible
error
situations
Logical errors
User errors (Such as a corruption)
(Such as a deleted table)
Hardware Move
(to a newer hardware)
SAP AG 2002
Errors requiring a database restore are caused by one of the following:
Hardware failure, for example when a disk crashes and affects the data or transaction log files
A logical error resulting in a database corruption
User error, such as the accidental deletion of a table
A disaster that destroys existing hardware
A move to new hardware involving the whole database system
If any of the above situations occur, use the restore procedures described in the following slides.
After a power failure, the database does not have to be restored. When power is cut off, the system
shuts down abruptly and all current transactions are aborted. SQL Server has an automatic recovery
mechanism to deal with such a situation. This mechanism is explained in later slides.
© SAP AG ADM520 5-7
Restore due to Media Failure
Physical errors
(Such as a media failure)
Directory
<SID>DATA3 C:\Tempdb
<SID>DATA2
<SID>DATA1 <SID>LOG1 <SID>LOG1
C:\Mssql\
C:\usr\sap\
PRIMARY C:\WINNT
SAP AG 2002
The procedure required to bring the database back to its original state differs depending on how files
are distributed on the system and which systems or disks are affected by the failure. Assuming that
data distribution follows the recommendations in the SAP Installation Guide and as described in
course ADM100, then the system files should be distributed across 3 volumes as follows:
Volume 1 Log files of the SAP database
Volume 2 Data files of the SAP database
Volume 3 SAP executables, SQL Server executables, Windows executables
The procedure used for a database restore depends on which volume crashed due to media failure.
© SAP AG ADM520 5-8
SAP Data Volume Crash
Procedure and Back up recent
escalation plan transaction log <SID>
<SID>DATA3
Replace disk in RAID <SID>DATA2
<SID>DATA1
system
PRIMARY
Restore SAP database <SID>
Apply differential <SID>
backups
Restore transaction logs <SID>
Check restore operation
SAP AG 2002
The SAP data files reside in a RAID5 system for increased data security. If a single disk fails, it must
be replaced and the RAID5 system must be synchronized. Database operation is not interrupted.
However, if the whole RAID5 system crashes, the SAP System comes to a standstill. An error is
written to the SQL Server error log. In this case the database must be restored.
The following steps must be taken to restore the database after an SAP data volume crash:
Immediately back up the current transaction log before shutting down SQL Server
Replace disks in the RAID5 system; the disks should be formatted and assigned the same drive
letter as the old ones
Restore the database by applying the most current database backup
If differential database backups were made after the last full database backup, the most current
differential database backup can be applied.
Restore the transaction log by applying all transaction logs created since the most current database
backup or the most current differential backup, then restore the transaction log created
immediately after the volume crashed
Check the restore
These steps must be described in detail in a separate document explaining your procedure and
escalation plan.
Note: If the disks on which the database resides are damaged, work performed after the last
transaction log backup will be lost. Back up the most current transaction log. Do this before
shutting down SQL Server. The current transaction log can only be backed up if the disks holding
the transaction log and the executables disk are undamaged.
© SAP AG ADM520 5-9
Backing up the current transaction log
SQL Server Query Analyzer - [Query - sapprod.master.sa - (untitled) - #1 New query] _ x
File Edit View Query Window Help _ x
DB: PRD
BACKUP LOG PRD to R3DUMP0 WITH NO_TRUNCATE, FORMAT
SAP AG 2002
If the disks on which the SAP database resides crashes, it is important to immediately back up the
current transaction log, otherwise the work carried out since the last transaction log backup will be
permanently lost.
The current transaction log can only be backed up, if the disks on which the transaction log resides is
undamaged and the disk on which the SQL Server executables reside is undamaged.
To back up the current transaction log, proceed as follows:
Insert a new tape into the tape device
Open the Query Analyser and enter the following transact-SQL statement:
BACKUP LOG <SID> to <backup_device> WITH NO_TRUNCATE, FORMAT
Execute the statement
© SAP AG ADM520 5-10
SAP Log Volume Crash
Procedure and
escalation plan Replace RAID system <SID>LOG1 <SID>LOG1
Restore SAP database <SID>
Apply differential <SID>
backups
Restore transaction logs <SID>
Check restore operation
SAP AG 2002
The transaction log files of the SAP database normally reside in a RAID1 system for reasons of
increased security. If one disk crashes, the mirrored disk can be used to bring the database up again.
If both disks crash, the SAP database would be marked suspect and a restore is necessary.
If the transaction log is not available anymore, the current transaction log cannot be backed up. The
data between the last transaction log backup and the crash of the RAID1 system is therefore lost.
If the SAP log volume crashes, proceed as follows:
Replace disks in the RAID1 system. The disks must be formatted and assigned the same drive
letters as the old ones
Restore the database by applying the most current database backup
If differential database backups were made after the last full database backup, the most current
differential database backup can be applied.
Restore the transaction log by applying all transaction logs created after the most current database
backup or the most current differential backup
Check the restore
These steps must be described in detail in a separate document explaining your procedure and
escalation plan.
© SAP AG ADM520 5-11
Executable Volume Crash
Procedure and Replace disks and install
escalation Plan auxiliary Windows
Directory
C:\Tempdb
Reload lost files from C:\Mssql\
latest Windows Backup C:\usr\sap\
C:\WINNT
Reboot Primary Windows
Restore msdb (and
master msdb
master) Database
Check Restore Operation
SAP AG 2002
If the disk with all other files except the SAP data and transaction log files crashes, the Windows
operating system, SAP executables, SQL Server executables, and the msdb and master databases are
lost. The operating system including the SAP System and SQL Server cannot start.
If the executable volume crashes, proceed as follows:
Replace disks. The disks must be formatted and assigned the same drive letters as the old ones.
Install an auxiliary Windows operating system.
Restore all lost files using the latest Windows backup. In this scenario, the SAP data and
transaction log files are not affected as they reside on different volumes. Do not reload these files
from the Windows backup.
Reboot Windows with the restored (primary) Windows system.
Restore the msdb database using the latest backup
You may also have to restore the master database if its contents were changed, for example, when
SQL Server configuration parameters were changed
Check the restore
These steps must be described in detail in a separate document explaining your procedure and
escalation plan.
© SAP AG ADM520 5-12
Restore due to Media Failure - Summary
One disk RAID 1 crash : RAID 5 crash: System crash: Exe disk crash:
crash Log files lost Data files lost Data + EXEs lost EXEs lost
<SID> suspect <SID> suspect SQL Server down SQL Server down
Back up log
<SID> with
no_truncate
Shut down SQL Server
Replace crashed disk(s), synchronize RAID
Windows Restore of EXEs;
not log file(s)!
SQL Restore of msdb (and master)
SQL Restore of database and transaction logs
Automatic recovery
OK Some data lost! OK OK OK
SAP AG 2002
If a hard disk containing a database data file in a RAID5 system crashes, the situation is not serious
unless the whole RAID5 system goes down, in which case the data files in the file system are lost
and must be restored.
When both RAID1 hard disks containing the transaction log files crash, a whole database restore
must also be performed. In this type of situation, you are likely to experience some data loss because
the current transaction log cannot be backed up.
If the volume containing the executables of SQL Server, the SAP System, and Windows crashes,
only these executables must be restored using Windows Restore. To bring the job history to a current
state, you should perform an SQL Server restore of database msdb. The content of the master
database rarely changes. If after a Windows Restore the content of the master database is not current,
perform a database restore of the master database.
After checking the restore, restart SQL Server. SQL Server then executes an automatic recovery to
roll back uncommitted transactions.
© SAP AG ADM520 5-13
Restore due to Logical Error
Logical errors
(Such as a corruption)
<SID>
SAP AG 2002
When a database corruption is detected, the problem must be diagnosed further to determine the
extent and cause of the damage. The optimal method of restoring the physical consistency depends
on the following considerations:
Type of object affected, for example an index or table
Location of the object, for example in database <SID>
Extent of the damage
Open a problem message using the Online Service System (OSS) and have a support engineer
analyze the error to find the most effective solution.
© SAP AG ADM520 5-14
SAP Database Corruption
Procedure and Back up recent
escalation plan <SID>
transaction log
Restore SAP database <SID>
Apply differential <SID>
backups
Restore transaction logs <SID>
Check restore operation
SAP AG 2002
To repair a corruption on the SAP database, a database restore may be necessary. In this scenario,
you do not need to recreate the RAID5 system, since the data files stored there are not damaged.
Perform the following steps:
Immediately back up the current transaction log before shutting down SQL Server
Restore the database by applying the most current database backup which is successfully checked
for not containing corruptions
If differential database backups were made after the last full database backup, the most current
differential database backup can be applied.
Restore the transaction log by applying all transaction logs written since the most current database
backup or differential backup
Check the restore
These steps must be described in detail in a separate document explaining your procedure and
escalation plan.
© SAP AG ADM520 5-15
Restore due to User Error
User errors
(Such as a deleted table)
<SID>
SAP AG 2002
A user might accidentally delete a table, or import a faulty transport. Depending on what has
happened, there are different ways of solving the problem.
To cancel incorrect changes such as the accidental deletion of a table, perform a point in time
recovery.
© SAP AG ADM520 5-16
Point In Time Recovery - Principles
Go back to the state as recorded on Wednesday at 09:30 a.m.
Database backups
Sunday Monday Tuesday Wednesday Thursday Friday
Time
Transaction log
backup 10:00
09:00
08:00
SAP AG 2002
A point in time recovery enables you to reset a database to a specific date and time. Use this method
when you need to return the database to an earlier state, due to user error.
During a point in time recovery, all transactions completed after a given point in time are rolled
back.
Point in time recovery can only take place while the transaction logs are being restored, not during a
database restore.
In this example, we can assume that the transaction log was backed up every hour, at 8.00 a.m., 9.00
a.m., and at 10.00 a.m. In order to return to Wednesday's 9.30 a.m. state, you need the transaction log
backups from Wednesday 9.00 a.m. and 10.00 a.m., since these contain the transactions executed
before 10.00 a.m. All transactions that were not completed by the specified time are rolled back. You
can only recover data up to a point in time where there was a checkpoint in operation on the
database. A checkpoint can also be set explicitly by a user through the transact-SQL statement
CHECKPOINT.
For details, see Unit 1: SQL Server Architecture.
© SAP AG ADM520 5-17
Restore due to External Factors / Hardware Move
External factors Hardware Move
(Such as fire or water
(to a newer hardware)
damage)
Directory
<SID>DATA3 C:\Tempdb
C:\Mssql\
<SID>DATA2 C:\usr\sap\
C:\WINNT
<SID>DATA1 <SID>LOG1 <SID>LOG1
Y:\<SID>LOG1
X:\<SID>DATA1
PRIMARY X:\<SID>DATA2
X:\<SID>DATA3
SAP AG 2002
If the system crashes due to external factors, the whole file system is lost and has to be set up newly.
The same procedure has to be followed when your SAP environment moves to a new hardware.
Set up all hardware including
• RAID systems
• Tape device
Install Windows
Restore SQL Server and the SAP System
© SAP AG ADM520 5-18
Complete System Setup
Replace hardware and
install auxiliary Windows
Directory
C:\Tempdb
Procedure and Reload executables from
C:\Mssql\
C:\usr\sap\
escalation plan C:\WINNT
latest Windows Backup Y:\<SID>LOG1
X:\<SID>DATA1
X:\<SID>DATA2
X:\<SID>DATA3
Reboot primary Windows
Restore msdb (and
master) database master msdb
Restore SAP database <SID>
Apply differential <SID>
backups
Restore transaction logs <SID>
Check restore operation
SAP AG 2002
If your whole system crashes, proceed as follows :
Replace hardware
It is best to format and assign the disks to the same drive letters as the old ones.
Install an auxiliary Windows operating system
Restore all executables using the latest Windows backup
SQL Server restore operations recreate lost files. Do not reload these files from the Windows
Backup.
Reboot Windows with the restored (primary) Windows system and restart SQL Server
The <SID> database will be marked suspect.
Restore the master and msdb database using the latest backup
Restore the SAP database and apply all proceeding differential backups (if available) and
transaction logs from the latest backups
Check the restore
These steps must be described in detail in a separate document explaining your procedure and
escalation plan.
© SAP AG ADM520 5-19
Master Database Not available
Procedure and
escalation plan
Directory
C:\Tempdb
Reload master files C:\Mssql\
Rebuild master database
from latest Windows backup C:\usr\sap\
C:\WINNT
Restore master database master
Check restore operation
SAP AG 2002
If the database master is corrupted, you may need to restore the database master. However, you do
not need to recreate the data files, as they are not damaged. In this scenario SQL Server cannot start,
as all the necessary information it reads is from the master database. You must therefore restore the
database master from the latest Windows backup to create a functioning master database.
Perform the following steps:
Restore the master.mdf and mastlog.ldf files from the latest Windows backup
Restore the master database from the latest SQL Server backup to get the current state of the
master database
Check the restore
If a current Windows backup of database master is not available, master can be rebuilt using the
Rebuild master utility. When master has been rebuilt, a current backup of master must be restored to
create the SAP database, the backup devices, and SQL Server logins. Using Rebuild Master is not
the recommended strategy for creating the database master, and with a proper backup strategy it is
unnecessary.
Before you rebuild your database master, consult a SQL Server support engineer.
These steps must be described in detail in a separate document explaining your procedure and
escalation plan.
© SAP AG ADM520 5-20
Restore Methods
Restore from backup history
Restore from device
Restore to a point in time
SAP AG 2002
Restoring a database backup returns the database to the same state it was in when the backup was
created. Restoring a transaction log backup reapplies all completed transactions that are in the
transaction log backup to the database. SQL Server reads through the transaction log, rolling forward
all the transactions on the transaction log. When SQL Server reaches the end of the transaction log, it
has recreated the exact state of the database at the time the backup operation started. The restore
operation then rolls back all transactions that were incomplete when the backup operation started.
The next section introduces the three different restore methods and their tools:
Restore from history
Restore from device
Restore to a point in time
© SAP AG ADM520 5-21
Restore From Backup History
Restore database x
General Options
Restore as Database: PRD
Restore: Database Filegroups or file From device database
Restore x
Parameters
General Options
Show backups for database: PRD
First backup to restore: 12/1/03 01:00: 47 AM -
Eject tapes (if any) after restoring each backup
Point in time restore Prompt before restoring each backup
...
Force restore over existing database
Restore database files as:
Restore Type Backup set date Size Restore from Backup set na
Original file name Restore as
12/1/03 01:00... 960640 Kb //./Tape0
F:\PRDLOG1\PRDLOG1.ldf F:\PRDLOG1\PRDLOG1.ldf
12/1/03 08:01... 9408 Kb //./Tape0
E:\PRDDATA2\PRDDATA2.ndf E:\PRD4DATA2\PRDDATA2.ndf
12/1/03 09:00... 256 Kb //./Tape0 E:\PRDDATA3\PRDDATA3.ndf E:\PRDDATA3\PRDDATA3.ndf
E:\PRDDATA1\PRDDATA1.mdf E:\PRDDATA1\PRDDATA1.mdf
Properties
Recovery completion state
Leave database operational. No additional logs can be restored.
Leave database nonoperational, but able to restore additional transaction logs.
OK Cancel Help Leave database read-only and able to restore additional transaction logs.
Undo file: f:\Program Files\Microsoft SQL Server\MSSQL\B ...
OK Cancel Help
SAP AG 2002
SQL Server stores log entries for each backup in msdb tables backupset and backupfile. For a restore
from backup history using these msdb records, proceed as follows:
In the Enterprise Manager, choose Tools → Database Restore. In the list, the latest database
backup and subsequent transaction log backups are already selected. Select the following
options:
• Eject tapes (if any) after restoring each backup
• Prompt before restoring each backup - If one tape device is used, you have to switch tapes
after restoring each backup.
• Force restore over existing database - If the database files exist, and you want to override
the existing database, for example after a restore due to a logical error. Do not select this
option after a restore due to media failure.
• Leave database operational; No additional transaction logs can be restored - If all
transaction logs are restored during this procedure.
Confirm your entries
During a database restore, SQL Server automatically recreates the database and its associated files,
by copying data from the backup into the database. To change original file locations, enter a new
name under Restore as. The default is the original file name.
The advantage of working with the method Restore from backup history is that you do not have to
find the latest backup.
Note: The system administrator restoring the database backup must be the only person currently
using the database to be restored.
© SAP AG ADM520 5-22
Changing Tapes
1
Restore x
Contents...
The restore operation has completed and the next restore operation is
PRD about to proceed. To continue the restore process, press OK. Otherwise,
press Cancel.
Backup set to restore: 12/4/02 11:14:09 AM -
ID04S Media Name: RL04S
Media Description:
OK Cancel
Media Set Labels:
SQL Server Enterprise Manager x
RL04S Contents...
ii Restore of database ‚PRD‘ completed successfully.
2
Ok
PRD
2
SAP AG 2002
If one tape device is used, make sure the right tape is in the tape device. Identify the tape by its tape
label. If you are unsure about its content, issue a RESTORE HEADERONLY FROM <device> or
use the Enterprise Manager to display the media header.
After a database backup is restored, a window appears asking you to insert the new tape to continue
with the restore (1). Note that this window only appears if option Prompt before restoring each
backup has been set. Switch the tape and confirm (2). Once the restore is complete, a confirmation
appears.
© SAP AG ADM520 5-23
Restore From Device
Restore Database x
General Options
Restore Database: PRD|
Backup: Database Filegroups or files From device
Parameters Choose Restore Device x
Device When the backup is restored, SQL Server will attempt to restore from the
devices listed below.
Select Device
Backup set: ID04S
Device 1 View Contents ...
Disk Tape
Restore backup set
Database - Complete Restore from: Device name Add...
Database - Differential \\.\Tape0 Edit
Transaction log
File and Filegroup: Remove
Read backup set information and add to backup history Remove All
Media verification option
Only restore from media with the following name:
OK Cancel Help
Media Name: ID04S
OK Cancel
SAP AG 2002
If the msdb is not available, the backup history is lost. In this case, you must restore the database
from the backup device or restore the msdb first, to use restore from history.
When using Restore from device, in the Enterprise Manager select option From device, then select
the device. The header of the chosen backup device, for example R3DUMP0 for the tape device is
then read. To display what has been stored on this device, choose View Contents. Select the type of
backup you want to restore (Database Complete, Database Differential, Transaction log, File or
Filegroup), then confirm your entries. When the database backup has been successfully imported,
use the same procedure to import the transaction log backups in the order in which they were backed
up.
To check a media name before performing the restore, specify the option Media verification.
Alternatively, you can recreate the backup history by choosing option Read backup set information
and add to backup history. This way the backup header is read from devices specified by you, and is
added to the backup history tables in the msdb database. Only one backup set can be added at a time
on one backup media. After adding all the necessary backup sets, you can use the method Restore
from history.
The advantage of using the method From Device is that you can restore the database even after
losing the backup history.
© SAP AG ADM520 5-24
Restore From Device Using the Query Analyzer
SQL Server Query Analyzer - [Query - sapprod.master.sa - (untitled) - #1 New query] _ x
File Edit View Query Window Help _ x
DB: PRD
RESTORE DATABASE PRD FROM R3DUMP0 WITH FILE = 1,
MEDIANAME = ‚ID04S‘, RECOVERY
RESTORE LOG PRD FROM R3DUMP0 WITH FILE = 1,
MEDIANAME = ‚RL04S‘, NORECOVERY
RESTORE DATABASE PRD FILE = ‘PRDDATA1’ FROM R3DUMP0
WITH MEDIANAME = ‘RF04S’
SAP AG 2002
You can restore a database from a device using the Query Analyzer and transact-SQL statement
RESTORE DATABASE. This statement includes the following options:
FROM: Specifies the backup devices from which to restore the backup. If the FROM clause is
not specified, a restore is not performed; instead, the database is recovered.
FILE: When specified in the WITH clause, FILE identifies the backup set to be restored. For
example, a file number of 1 indicates the first backup set on the backup medium and a file
number of 2 indicates the second backup set.
When specified in the file list clause, this option identifies the file or filegroup to be restored.
MEDIANAME: Specifies the media name for the entire backup set. If provided, the media name
must match the media name on the backup volume; otherwise, the restore operation terminates.
If no media name is given, the check for a matching media name is not performed.
NORECOVERY|RECOVERY: Instructs the restore operation (not) to roll back any
uncommitted transactions. Option NORECOVERY must be specified if another transaction log
has to be applied. Option RECOVERY is the default.
REPLACE: Instructs SQL Server to create the specified database and its related files even if
another database already exists with the same name. In this case, the existing database is deleted.
When the REPLACE option is not specified, a safety check occurs, which prevents overwriting
a different database by accident.
For details, see SQL Server Books Online.
© SAP AG ADM520 5-25
Point In Time Recovery
Restore database x
General Options
Restore as Database: PRD
Restore: Database Filegroups or file From device
Parameters
Show backups for database: PRD
First backup to restore: 12/1/03 01:00: 47 AM -
Point in time restore
12/1/03 08:30: 47 AM - ...
Point In Time Restore
Media Set Labels: x
Restore Type Backup set date Size Restore from Backup set na
Contents...
Point in time restore will halt the restoration of transaction
12/1/03 01:00... 960640 Kb //./Tape0 log entries recorded after the specified date and time.
12/1/03 08:01... 9408 Kb //./Tape0
12/1/03 09:00... 256 Kb //./Tape0
Date: Fri 12/ 1/2003
Time: 08:30 AM
Properties
OK Cancel
OK Cancel Help
SAP AG 2002
To perform a point in time recovery, you must first restore the database backup and then define the
time to which you want to recover your database. The transaction logs are then applied only up till
the specified point in time.
Use the Enterprise Manager. Choose Tools → Restore Database. Select the option Point in Time
Restore. In the dialog box, enter the time to which you wish to restore and confirm your entry.
Point in time recovery can also be executed through the transact-SQL statement RESTORE LOG
and the option STOPAT.
Point in time recovery is not supported with the bulk-logged recovery model. Bulk-logged recovery
model only allows the database to be recovered to the end of a transaction log backup when the log
backup contains bulk changes.
© SAP AG ADM520 5-26
Restore Strategies
Restore from single DB and transaction log backups
Restore from a parallel DB backup
Restore from a two-step disk backup
Restore from supplementary differential backups
SAP AG 2002
The method you choose to restore your SAP database depends on the type of error and on your
chosen backup strategy. However, all restore scenarios start with a restore of the latest database
backup.
The next section explains restore strategies, which depend on the different backup strategies
introduced in Unit 4: Database Backup.
© SAP AG ADM520 5-27
Restore from Single DB and TL Backups
Log backups Log backups Log backups
Sun, 1 Mon, 2 Tue, 3 Wed, 4 Thur, 5 Fri, 6
Full backup Full backup Full backup
ID03S 1
RL04S 2
SAP AG 2002
To restore an SAP database from a single database backup and subsequent transaction log backups,
perform the following steps:
If possible, back up the current transaction log to a new separate disk backup device, using the
option NO_TRUNCATE.
Set the SAP database to single-user mode.
Restore the most current database backup (1) and all subsequent transaction log backups (2) by
choosing one of the restore methods:
• Restore from history or
• Restore from device.
Set the option Leave the database non-operational but able to restore additional transaction
logs.
Apply the current transaction log that you recently backed up. Set the option Leave the database
operational no additional transaction logs can be restored. If this option is set, no more
transaction logs can be applied.
The database is not recovered until the current transaction log has been applied. This prevents
any transactions from being partially rolled back. Transactions can only be rolled back at the end
of the last restore operation, when the current log has been applied.
Check the restore.
© SAP AG ADM520 5-28
Restore From Differential Backups
Log backups Log backups Log backups
Sun, 1 Mon, 2 Tue, 3 Wed, 4 Thur, 5 Fri, 6
Full backup Differential Differential
ID01S 1
R+03S 2
RL04S 3
SAP AG 2002
To restore an SAP database using supplementary differential backups, perform the following steps:
If possible backup the current transaction log by using the NO_TRUNCATE option to a new
separate disk backup device.
Set the SAP database to single-user mode.
Restore the last database backup created (1).
Restore the last differential backup created since the database backup was created (2).
Apply all transaction log backups, in sequence, created after the last differential backup was
created, finishing with the transaction log backup created in the first step (3).
Perform the restore using one of the restore methods:
• Restore from history or
• Restore from device.
Check the restore.
© SAP AG ADM520 5-29
Restore from a Parallel Backup
Restore database x
General Options
Restore as Database: PRD
Backup Set Properties x
Restore: Database Filegroups or file From device
Parameters General
Show backups for database: PRD
Name: PRD DB backup
First backup to restore: 12/1/03 01:00: 47 AM -
Point in time restore
...
Description:
Restore Type Backup set date Size Restore from Backup set na
12/1/03 01:00... 10976244 Kb //./Tape0 Type: Database
12/1/03 08:01... 94098 Kb //./Tape0 Size: 10976244 Kb
12/1/03 09:00... 25689 Kb //./Tape0 Start date: 12/01/03 1:00:47 AM
Finish date: 12/01/03 2:55:19 AM
Properties
Properties Media Name: ID01S
Media Description:
Restore From:
//./Tape0
OK Cancel Help //./Tape1
Change...
OK Cancel
SAP AG 2002
To restore an SAP database from a parallel database backup and subsequent transaction log backups,
perform the following steps:
If possible, back up the current transaction log to a new separate disk backup device, by using
the option NO_TRUNCATE.
Set the SAP database to single-user mode.
Perform a restore as explained in the previous slide using one of the restore methods:
• Restore from history or
• Restore from device.
If you perform a restore from history, select Properties to find out which backup devices to
restore from.
Put the tapes into the tape devices.
Check the restore.
© SAP AG ADM520 5-30
Restore from a Two-Step Disk Backup
Log backups Log backups Log backups
Sun, 1 Mon, 2 Tue, 3 Wed, 4 Thur, 5 Fri, 6
Full backup Full backup Full backup
DISK_DEV2
1
DISK_DEV1
2
SAP AG 2002
To restore an SAP database from a two-step disk backup, perform the following steps:
If possible, back up the current transaction log to a new separate disk backup device, using the
option NO_TRUNCATE.
Set the SAP database to single-user mode.
If the disk where the last database backup has been performed has also crashed, provide
additional disk space for applying the latest Windows backup. Perform a restore as explained in
the previous slides by using one of the restore methods:
• Restore from history or
• Restore from device.
Restore the latest database backup (1) and apply all subsequent transaction log backups
including the one performed in the first step (2).
Check the restore.
© SAP AG ADM520 5-31
Checking the Restore
SAP AG 2002
The backup and restore history is stored in the msdb database. To find the history, choose
Administration → CCMS →DB Administration →Backup Logs then button Restoration History.
If time permits, run some DBCC consistency checks on the newly restored database.
Note: These checks are not absolutely required, as a consistency check should have been
executed each month and should have guaranteed that the backup on the backup media is
consistent.
© SAP AG ADM520 5-32
Summary
Now you are able to:
Describe different restore strategies
Define an appropriate restore strategy based on the
backup strategy chosen by your company
Perform a restore using SQL Server tools and identify the
restore method you should use
Check your restore
SAP AG 2002
© SAP AG ADM520 5-33
Further Documentation
SAP Installation Guide
SQL Server Books Online
ADM100 mySAP Technology Administration
SAP AG 2002
© SAP AG ADM520 5-34
Unit Actions
? Exercises
Solutions
SAP AG 2002
© SAP AG ADM520 5-35
Database Restore Exercises
Unit: Database Restore
Topic: Restore Methods
At the conclusion of this exercise, you will be able to:
• Restore a database under SQL Server
• Detect a hardware problem and determine the correct restore method
and strategy to be used to bring the database back to its original state
The customer detects a hardware problem, which makes it necessary to
restore the SAP database.
1-1 Restore a user database after a media failure
1-1-1 In this exercise you will restore the test database using the backups you
created in the previous exercise. Make sure that you have a valid database
and transaction log backup of the test database.
Execute stored procedure crash_me in the master database.
Afterwards, check whether the test database is available.
1-1-2 Check the file system to see whether the data files TestData1.mdf,
TestData2.ndf, and TestLog1.ldf are located in the directory
F:\Mssql7db\MSSQL\Data\.
Which step do you have to perform immediately before you start with the
database restore?
1-1-3 Restore the test database by choosing one of the restore methods introduced
in Unit 5: Database Restore.
Which method do you choose?
Which options do you set in the Restore Database window?
1-1-4 Check the restore operation.
© SAP AG ADM520 5-36
Database Restore Solutions
Unit: Database Restore
Topic: Restore Methods
1-1 Restore a user database after a media failure
1-1-1 Close all connections to the test database.
Open a Query Analyzer window, and execute crash_me in the master
database. The stored procedure returns message:
output
--------
NULL
(1 row(s) affected)
1-1-2 Use the Explorer to check whether the files are available.
Normally, you should back up the current transaction log immediately
before you start to restore the database. To do this use Transact SQL
statement:
backup log test to R3DUMP3 with no_truncate
The following error occurs:
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database
'test'. No entry found with that name. Make sure
that the name is entered correctly.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
When the primary database file is not available anymore, as in this
particular case, you cannot back up the current transaction log and you
experience some data loss.
1-1-3 To restore the test database, choose Tools → Restore Database … in the
Enterprise Manager. Select the database. The backup history should show
one database backup and one succeeding transaction log backup. Leave
them all marked.
Under Options, select:
Prompt before restoring each backup
Force restore over existing database
Leave database operational. No additional logs can be restored
A correct restore operation returns the message:
Restore of database ‘test‘ completed successfully.
© SAP AG ADM520 5-37
Execute transact-SQL statement on database test
select * from stores
1-1-4 Call the Backup Monitor under Tools → CCMS → DB Administration →
DB12 – Backup Logs (transaction code DB12), and check the restoration
history.
Open the Query Analyzer and perform
DBCC CHECKDB (test).
© SAP AG ADM520 5-38
Regular Maintenance and Error Handling
1 Introduction
2 SQL Server Architecture
3 How the SAP System uses SQL Server
4 Performance Monitoring and Tuning
5 Database Backup
6 Database Restore
7 Regular Maintenance and
Error Handling
SAP AG 2002
© SAP AG ADM520 6-1
Regular Maintenance and Error Handling
Contents
Error and Alert handling
Space management
Data consistency
SQL Server parameter maintenance
Objectives
At the end of this unit, you will be able to:
Handle common errors
Monitor your SAP System with the help of the CCMS Alert Monitor
Monitor database and transaction log growth and extend the
database
Perform regular administrative tasks
SAP AG 2002
© SAP AG ADM520 6-2
CCMS Alert Monitor
SAP AG 2002
It is vital to regularly monitor the availability, performance, correctness and security of your SAP
System. The SAP Basis System contains a various set of tools for monitoring and performance
analysis. These tools include a central monitoring architecture which performs the continual system
monitoring of all hardware and software components, which is implemented in the CCMS Alert
Monitor (transaction RZ20). In the SAP Easy Access menu choose Tools -> CCMS -> Monitoring ->
Alert-Monitor.
SAP provides a pre-configured collection of monitors which are integrated in the CCMS Alert
Monitor and which can be used immediately after the installation of your SAP System. Each
collection combines an area of interest such as the database monitor.
The use, layout and maintenance of the CCSM Alert Monitor has been introduced in course
ADM100 myTechnology Administration.
© SAP AG ADM520 6-3
Alerts in the MMC
SAP AG 2002
The CCMS Alert Monitor can also be viewed in the Microsoft Management Console on each SAP
Application Server.
© SAP AG ADM520 6-4
Categories of Information
Space Management
Performance
Backup / Restore
R/3 Consistency
Health
SAP AG 2002
Information of alerts for SQL Server in the monitoring tree are grouped in the following main
categories:
Space Management
Performance
Backup / Restore
R/3 Consistency
Health
Alerts for the SQL Server indicate when defined threshold values have been exceeded. Existing
threshold values reflect SAP recommendations, however they can be adjusted to meet individual
system requirements.
The categories Performance and Backup / Restore are not handled in this chapter as they were
already discussed in earlier chapters.
© SAP AG ADM520 6-5
Space Management
Autogrow option
Current sizes
Free space
SAP AG 2002
Status information and alerts related to the disk space on the database server are displayed for the
<SID> and the tempdb database. For each physical database file the autogrow option, the current
sizes and the free space is displayed.
The values are refreshed every 8 hours.
If you double click on the values, the Database Allocation Monitor (transaction DB02) is displayed.
© SAP AG ADM520 6-6
Monitoring Database Growth
1
3
2
8 9 10 6 4
7 5
11
SAP AG 2002
Database growth must be monitored regularly using the Database Allocation Monitor (Transaction
DB02). As the database grows, you may consider adding more disk space.
The main screen of the Database Allocation Monitor shows the amount of space used by the SAP
database (1) and its transaction log (2).
Button DB space history (3) displays a history of the growth of the database. Performance data
collected during the job COLLECTOR_FOR_PERFORMANCE_MONITOR (report RSCOLL00) is
stored to table MONI once a day and evaluated for this display.
The Files section lists the database and log files used. Ensure that the log files and database files
reside on separate disks (4). For data files, the PRIMARY filegroup is displayed (5). The column
Growth displays the increment by which the file can grow (6). This increment should be at least 100
MB and the free space on the disk, displayed in column Free disk (7), should be large enough to hold
at least two increments. The initial size of the file is displayed under Size(MB) (8). The used portion
of this file is displayed under Used (MB) (9). A file can grow to an unrestricted or to a limited size.
This information is displayed under Limit (10).
The last section displays the number and sizes of tables, indexes and stored procedures. Note that the
reserved size for tables and indexes is shown.
If your database grows fast, you might want to find out which table is responsible for the growth.
Choose button Space Statistics (11) and choose Top n growing tables.
© SAP AG ADM520 6-7
Monitoring Database Growth (2)
DEV
Space Allocated
Data:
DEVdata1: 3000 MB 2999,99 MB
DEVdata2: 3000 MB 2999,99 MB
DEVdata3: 3000 MB 2999,99 MB
Transaction Log space: 4999,99 MB 73,53 MB 4926,46
Total Used Free
SAP AG 2002
SQL Server uses a proportional fill strategy across all files within filegroup PRIMARY, writing an
amount of data proportional to the free space in the file (see Unit 1: SQL Server Architecture). As a
result all the files in a filegroup tend to be filled at approximately the same time. This strategy
ensures that I/O operations are distributed more evenly over the database files thus preventing
hotspots on specific files and partitions.
To display the allocated size of a file, use the task pad view in the Enterprise Manager.
When SQL Server is unable to allocate space for a database object, the following message appears:
Server: Msg 1105, Level 17, State 2, Procedure <procedure>, Line 10 Could not allocate space for
object '<object>' in database '<SID>' because the 'PRIMARY' filegroup is full.
When this occurs, the current transaction aborts. SAP System activity cannot continue. You should
monitor database growth regularly and expand the database early enough to prevent this type of
error.
© SAP AG ADM520 6-8
Automatic File Growth
DEV
Space Allocated
Data:
DEVdata1: 3000 MB 19999,99 MB
DEVdata2: 3000 MB 19999,99 MB
Expand first file by 100 MB
DEVdata3: 3000 MB 19999,99 MB
Transaction Log space: 4999,99 MB 73,53 MB 4926,46
Total Used Free
SAP AG 2002
If the database is full, the proportional fill mechanism is no longer used. SQL Server will then
automatically expand one file at a time, provided that the file is set to grow automatically. If enough
space is available on the volume used for the SAP data files, the file is expanded by one increment as
specified in autogrow.
Insert operations fill the expanded file first, before using another file. When using only one volume,
this mechanism does not affect performance. If more than one volume is used, insert operations will
not be distributed equally across the volumes. Any performance improvement that would have been
achieved by distributing insert operations equally across all volumes is therefore lost.
Each file can have a maximum size specified. If a maximum size is not specified, the file can
continue to grow until it has used all available space on the volume.
Ensure that the free space on the volume is at least twice the increment, by which the file grows. If
less space is available, you should consider purchasing additional disk space.
Note: Files should normally be expanded manually, and only grow automatically in unusual
situations.
© SAP AG ADM520 6-9
Extending the Database
Database must be extended
Can
Add new disk to Yes volume be No Add new
volume extended? volume
Move one old file
Expand files and <SID>DATA1 to new volume
set all files
to autogrow
PRIMARY
Expand files on old
+ and new volume
<SID>DATA1 and set all files
<SID>DATA2
<SID>DATA3
<SID>DATA1
to autogrow
<SID>DATA2
PRIMARY
PRIMARY
SAP AG 2002
SAP recommends that you set the database files to grow automatically. Normally, you should
expand files manually, and allow them to grow automatically only in exceptional situations, for
example if the database administrator is unable to react before more space is needed. If files are
expanded automatically in production operation, system performance can be impacted.
Once disk space is exhausted, you must extend the database.
If the volume containing the data files can be extended, you can simply add a new disk to the
volume. Increase the size of all files equally and leave them set to autogrow.
Alternatively, you can extend the database by adding a new volume. Procedures for doing this are
explained in the following slides.
Information about the database files is stored in tables sysfiles and sysfiles1. In addition to your
backup activities, keep a record of this information about the location of files on paper. You may
need the information if a failure occurs and a whole system restore is required. Stored procedure
sp_helpfile returns the physical names and attributes of files associated with the current database.
© SAP AG ADM520 6-10
Expanding a File Using the Enterprise Manager
SQL Server Enterprise Manager _ x
Console Window Help
Console Root\Microsoft SQL Server\SQL Server Group\sapprd [Windows NT]\Databases _ x
Action View Tools
7 Items
Console Root
Microsoft SQL Servers PRD Properties x
SQL Server Group
PRD master model msdb
General Transaction Log Options Permissions
sapprd [Windows NT]
Databases Name: PRD
PRD
master
northwind pubs Database files
tempdb
model
msdb File name Location Space Allocated (MB) File group
Northwind PRDDATA1 . f:\PRDDATA1\PRD4D... 4000 PRIMARY
pubs PRDDATA2 ... f:\PRDDATA2\PRDD... 4000 PRIMARY
tempdb ... f:\ PRDDATA3\PRDD... 4000 PRIMARY
PRDDATA3
Data Transformation ...
Management
Security
Support Services
File properties:
Automatically grow file
File growth Maximum file size
In megabytes: 100 Unrestricted filegrowth
By percent: 10 Restrict filegrowth (MB): 2001
OK Cancel Apply
Apply Help
SAP AG 2002
If the volume can be extended, data files on the larger volume must be expanded manually.
To expand a file, use the Enterprise Manager. There you can specify the increase in space allocated
to a particular file. If all other files have no free space left, all the insert operations will use the new
space on the expanded file. You should expand all files equally and use almost all the disk space.
Keep extra space on the disk in reserve, and ensure that files are set to grow automatically by an
increment of at least 100 MB. If you don't want to extend the database by the whole new disk space
at one time, expand one file first and the next file when the next expansion is necessary.
Use these procedures only when all files are located on one volume.
Alternatively, you can use the transact-SQL statement ALTER DATABASE to expand a file. See
Books Online for details.
© SAP AG ADM520 6-11
Extending the Database on a New Volume
2
RAID5 <SID>DATA1 <SID>DATA2 <SID>DATA3 SAP DB data fiIes
PRIMARY
1
RAID5 <SID>DATA3 2 SAP DB data fiIes
PRIMARY
SAP AG 2002
SAP recommends that you store the SAP database data files in a RAID5 or RAID0+1 system. A
RAID system can grow up to a limited size. If a database grows beyond this limit and the RAID
system cannot be further expanded, a new RAID array must be set up.
If you use an additional volume to extend your database, one file on the existing volume must be
moved to the new volume (1) to free up space on the old volume. Expand all files on both volumes
(2), or create new files if there is only one file per volume. Leave some reserve space to let the files
grow automatically. SQL Server continues to distribute the insert operations proportionally over all
new space, and the IO load is equally distributed across the two volumes.
© SAP AG ADM520 6-12
Moving a Database File
msdb
1 master ID01S
<SID>
SQL Server Query Analyzer - [Query - sappod.master.sa - (untitled)]
detail stats.
2 sp_detach_db ‘PRD’, ‘true’
C:\WINNT\System32\cmd.exe
detail stats.
Microsoft(R) Windows NT (TM)
3 (C) Copyright 19985-1996 Microsoft Corp.
C:\> mv F:\PRDDATA2\PRDDATA2.NDF G:\PRDDATA2\PRDDATA2.NDF
SQL Server Query Analyzer - [Query - sappod.master.sa - (untitled)]
detail stats.
4 sp_attach_db PRD’,‘F:\PRDDATA1\PRDDATA1.MDF’,
‘G:\PRDDATA2\PRDDATA2.NDF’
SQL Server Query Analyzer - [Query - sappod.master.sa - (untitled)]
detail stats.
5 sp_helpfile
SAP AG 2002
A database file can be moved to a different volume in order to distribute I/O operations evenly over
the existing volume. To move a database file to a new volume:
Ensure that a recent backup of the database is available and verified. (1)
Detach the database. (2)
Move one database file to the new volume. (3)
Attach the database specifying the new location of the moved file. (4)
Check the database files. (5)
Detaching a database removes the database from SQL Server, but leaves the data and transaction log
files in the database intact. Use stored procedure sp_detach_db to detach a database from the server.
Specify true as second option in the stored procedure. This prevents UPDATE STATISTICS from
running on all tables on the database before detaching.
Note: The database must not be used while it is being detached from the server, that is, there must
be no connection open to the database.
Move the file to a new volume and reattach it using stored procedure sp_attach_db. When attaching a
database, specify the physical location of the primary file and the file being moved, along with the
new location. Because the primary file contains the information needed to find the other files
comprising the database, you need only specify the location of the file that has changed location.
Check the files by running the stored procedure sp_helpfile, which returns the physical names and
attributes of files associated with the current database.
See also the SAP Online Help and Books Online on how to move a database file to a new disk.
© SAP AG ADM520 6-13
Adding a File Using the Enterprise Manager
SQL Server Enterprise Manager _ x
Console Window Help
Console Root\Microsoft SQL Server\SQL Server Group\sapprd [Windows NT]\Databases _ x
Action View Tools
7 Items
Console Root
Microsoft SQL Servers PRD Properties x
SQL Server Group
PRD master model msdb
General Transaction Log Options Permissions
sapprd [Windows NT]
Databases Name: PRD
PRD
master
northwind pubs Database files
tempdb
model
msdb File name Location Space Allocated (MB) File group
Northwind PRDDATA1 . f:\PRDDATA1\PRDD... 2000 PRIMARY
pubs PRDDATA2 ... f:\PRDDATA2\PRDD... 2000 PRIMARY
tempdb PRDDATA3 ... f:\PRDDATA3\PRDD... 2000 PRIMARY
Data Transformation PRDDATA4 ... f:\PRDDATA4\PRDD… 2000 PRIMARY
Management
Security
Support Services
File properties:
Automatically grow file
File growth Maximum file size
In megabytes: 100 Unrestricted filegrowth
By percent: 10 Restrict filegrowth (MB): 2001
OK Cancel Apply
Apply Help
SAP AG 2002
You can also use additional data and transaction log files to extend a database. When a file is added,
the file is immediately available for use by the database.
Use the Enterprise Manager to add a new file. Specify the size of the file, the maximum size to
which the file should grow, the increment by which the file is to grow each time (default = 10 %),
and the filegroup to which the file belongs. In the SAP environment, filegroup PRIMARY is used.
When you add a new file, use the SAP naming conventions.
When adding a new data file, keep extra space on the disk in reserve, and set the files to grow
automatically in increments of at least 100 MB.
You can also use the transact-SQL statement ALTER DATABASE. See Books Online on more
details.
© SAP AG ADM520 6-14
Monitoring Transaction Log Growth
1
2 3
SAP AG 2002
The transaction log grows if data in the database is changed. As the transaction log grows, the first
log file fills, then the second, and so on, using a fill-and-go strategy.
In the SAP environment, there is usually only one log file. The size of this log file must be
determined carefully. Watch the growth of the transaction log using the Database Allocation Monitor
(Transaction DB02). Observe how the transaction log fills during exceptional workload, for example
during Batch Input or an upgrade. You can monitor this at ST04 → Detailed Analysis Menu →
Performance History → folder DB space. These values are refreshed every 2 hours. Alternatively
choose DB02 → DB space history to display one value per day.
Use command DBCC SQLPERF(logspace) to display the amount of space used by the transaction
log.
The Database Allocation Monitor displays the following:
File name
Location of file
Space allocated for the transaction log files
Log size (1)
Log free size (1)
If automatic growth is configured for this log file, column Growth (2) displays the increment by
which the transaction log can grow. The increment should be 50% of the file size, and the free space
on the disk should be enough to handle at least one increment.
Column Free disk (3) displays the amount of free space on the disk.
© SAP AG ADM520 6-15
Transaction Log Full
SQL
Server
Msg 9002,
1 The log file for
database
‘<SID>' is full
<SID> 2
begin1 update begin2 insert commit2 chkp insert begin3
LSN0 LSN1 LSN2 LSN3 LSN4 LSN5 LSN6 LSN7
delete dump commit1 chkp insert insert delete delete Backup RL02A
LSN8 LSN9 LSN10 LSN11 LSN12 LSN13 LSN14 LSN15
insert update begin4 rollback chkp insert delete drop
3
LSN16 LSN17 LSN18 LSN19 LSN20 LSN21 LSN22 LSN23
update begin5 commit3 insert delete chkp insert commit4
LSN24 LSN25 LSN26 LSN27 LSN28 LSN29 LSN30 LSN31
SAP AG 2002
If the transaction log is full, SQL Server writes the following error message to the error log file and
interrupts the current transaction:
Server: Msg 9002, Level 17, State 2, Procedure <procedure>, Line 12 The log file for database
‘<SID>' is full. Back up the transaction log for the database to free up some log space
A similar message is written to the SAP System log.
If you see this error message, back up the transaction log immediately using the SAP Planning
Calendar (Transaction DB13). Use the same tape for this extra backup as used for all regularly
planned transaction log backups. Append the backup to the existing backups on the tape. Do not
specify the option Initialize. Remember that a transaction log backup truncates the inactive portion of
the log and makes space for new transaction logging. See also Unit 4: Database Backup.
Do not shut down SQL Server or the SAP System, if the transaction log is full. If you shut down
SQL Server, log entries will be lost.
With a good backup strategy, errors like this should not occur. However, the transaction log fills up
quickly during periods of an exceptionally high workload, for example during Batch Input or an
upgrade. Ensure that at least 1.5 GB space is available in the transaction log.
© SAP AG ADM520 6-16
R/3 Consistency
Tables, Views and Indexes missing in DB
SAP AG 2002
In this category objects that are defined in the SAP ABAP/4 dictionary but not on the database are
displayed.
The values are refreshed once a day.
© SAP AG ADM520 6-17
Database Allocation Monitor: ABAP/4 Dictionary
SAP AG 2002
Objects of the ABAP/4 Dictionary which were not found in the database are displayed in the
Database Allocation Monitor after choosing button Checks → Database ↔ ABAP/4 Dictionary.
Indexes that exist in the SAP ABAP/4 dictionary but not on the database can be created by
positioning the cursor on the index and click button Create on DB.
© SAP AG ADM520 6-18
Health
SQL Server configuration parameters
SQL Server and Windows Build Numbers
Errors in the SQL Server Error Log
SAP AG 2002
The health of your SAP System can be judged by monitoring the effectiveness of parameter settings
and watching out for common errors.
The most important alerts to be checked regularly are the correct settings of the SQL Server
configuration parameters, the correct versions of SQL Server build numbers and errors in the SQL
Server Error Log.
© SAP AG ADM520 6-19
SQL Server Configuration
SQL Server Query Analyzer - [Query - sappod.master.sa -]
detail stats.
name minimum maximum config_value run_value
------------- -------- ----------- ------------ ---------
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 1 1
awe enabled 0 1 0 0
c2 audit mode 0 1 0 0
cost threshold
for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default full-
text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create
memory (KB) 704 2147483647 0 0
Lightweight
pooling 0 1 0 0
locks 5000 2147483647 0 0
SAP AG 2002
The CCMS Alert Monitor shows a red alert when SQL Server Parameters have been set, but are not
active.
To change the configuration parameters select the server, than select Action → Properties to display
the Properties window.
A list of SQL Server configuration parameters can also be retrieved by running sp_configure in the
Query Analyzer. Columns minimum and maximum display the acceptable range of values. Column
config_value contains the active value. Column run_value contains the value that is currently set.
After changing a configuration parameter using sp_configure, use command reconfigure with
override to update the currently configured value of a configuration option. Because some
configuration options require a server stop and restart to update the currently running value,
reconfigure does not always update the currently running value. The option with override disables
the configuration value checking.
For dynamic configuration parameters, changes take effect immediately. For static configuration
parameters, changes take effect after you shut down and restart SQL Server. Before restarting SQL
Server, shut down the SAP System.
© SAP AG ADM520 6-20
SQL Server does not Start
Error in parameter
Error in parameter
configuration
configuration
SQL
Server Books Online
initconfig: Error 2
initconfig: Error 2
(The system cannot
(The system cannot
find the file
find the file
specified.)
specified.)
SAP Notes
...
Open a problem
message
Check for
error
messages
SAP AG 2002
If SQL Server does not start, check the SQL Server error log at file system level. To get a detailed
description of the error message, use Books Online or the SAP Notes.
Because SQL Sever sets its resources dynamically, an incorrect setting of SQL Server configuration
parameters sometimes causes SQL Server not to start. To start SQL Server with a minimal
configuration, use the option -f in the sqlservr command. Reconfigure the configuration options
using the sp_configure system stored procedure or the Enterprise Manager.
If the master database is not available, for example due to a corrupt master file, SQL Server cannot
start because it is unable to read the configuration information. An error starting with Initconfig
appears in the SQL Server error log.
If the problem cannot be solved, create a problem message immediately. Enter the SQL Server error
message.
© SAP AG ADM520 6-21
Applying Service Packs
README.txt
README.txt
2
1
follow instructions in
download the the readme file
3 SAP Application
current Service Pack
Server
Database
Server
6
SQL
master msdb Server
stop the SAP System
perform a backup
and SQL Server
and check the installation
4
5
master msdb
install the Service
Pack on the Database
Server and all SAP perform a backup if
Application Servers no current backup exists
SAP AG 2002
SQL Server and Windows build data is displayed in the first two nodes under Startup parameters in
the CCMS Alert Monitor.
The current service pack of SQL Server is always released by SAP. SAP strongly recommends that
the current service pack is installed immediately when it is available. Explicit exceptions are
mentioned in SAP Notes.
The exact SQL Server version appears in the first line of the SQL error log. The version can also be
found out using the SQL command select @@version.
The installation of a service pack is described in SAP note 417089:
Download the current service pack from the Microsoft Web Server or a CD (1).
A service pack always contains a README file from Microsoft. This file contains detailed
installation instructions (2).
Stop the SAP System and SQL Server (3).
SAP recommends that you perform an Windows backup of all server files after each configuration
change. The easiest way of running a Windows backup is to reboot it from a second Windows
installed in parallel (4). Before installing the service pack, make sure that a current backup of the
master and msdb model databases exist. Installing a service pack modifies the master and msdb
databases, making them incompatible with older versions of SQL Server. These backups are
required if you decide to reinstall SQL Server without the service pack.
Install the service pack by opening the SETUP.BAT file (5). Install the service pack on the SAP
application server in the same way as it is installed on the database server.
Perform a Windows backup, back up the master and msdb databases and check the installation (6).
© SAP AG ADM520 6-22
Error Handling
SQL Server error log in
SQL Server error log in
SAP System Log path X:\Microsoft SQL
path X:\Microsoft SQL
Server\MSSQL\LOG
Server\MSSQL\LOG
SQL Server Agent error
SQL Server Agent error
log in path X:\Microsoft
log in path X:\Microsoft
SQL Server\MSSQL\LOG
SQL Server\MSSQL\LOG
dev-trace files
Check for
error messages
in: ABAP runtime
errors
! i
?
Event Viewer
SAP AG 2002
When a problem occurs, begin by checking the following:
SAP System log
Use Transaction SM21.
SQL Server error log
Use the Database Monitor (Transaction ST04) or the Enterprise Manager. Alternatively, you can
directly edit the log files located in directory X:\Microsoft SQL Server\MSSQL\LOG.
SQL Server Agent error log
Use the Database Monitor (Transaction ST04) or the Enterprise Manager. Alternatively, you can
directly edit the log files located in directory X:\Microsoft SQL Server\MSSQL\LOG.
Developer trace files (Transaction ST11) display all messages written by the SAP work processes.
To limit the messages displayed on the database or the database interface, choose Display
Components.
ABAP runtime errors
Use transaction ST22.
Event Viewer
Choose Start → Settings → Control Panel → Administrative Tools → Event Viewer.
Search for related SAP Notes in SAPNet.
© SAP AG ADM520 6-23
Some Errors that may Occur
You may encounter the following SQL Server errors:
Database file full (Error
Transaction log full (Error
8928,
Consistency errors (Errors 601, 605, 644, 823, 2511, 2513,
8944, 8952, 8976,
Resource Problems (Error 845)
Performance Problems (Error 8645,
Deadlock (Error 1205,
Locking problems (Error 601,
Network errors (Error 11,
SAP AG 2002
Errors 1105 and 9002 indicate that either a data file or the log file has run out of space. What to do
in such situations, has been discussed in previous slides.
SQL Server as a Relational Database Management System is responsible for the physical
consistency of the database. There is a physical inconsistency (database corruption) if there are
errors in internal structures (page pointer, allocation ...) in the database. You can suspect this if there
are frequent short dumps in the SAP System, in particular if the following SQL Server error
messages appear: SQL Error 601, 605, 644, 823, 2511, 8928, 8944, 8952, 8976. See SAP note
142731 for details.
See SAP note 429561 for details on SQL Server error 845.
When SQL Server is low on memory a timeout may occur while waiting for memory resources to
execute a query. In this case SQL Server error 8645 is shown in the SQL Server error log and an
ABAP/4 short dump occurs. See SAP note 206694 for details.
SQL Server error 1205 indicates a deadlocks on a certain table. Known deadlocks are listed
according to the table affected in SAP note 565710.
SQL Server error 601 can have two different causes: either database inconsistencies or a conflict
between two database processes that were executed at the same time. See SAP note 565708.
Short Dumps of the type DBIF_RSQL_SQL_ERROR, as evidenced in ST22, may be observed in a
badly configured SQL Server system. The details of the short dumps will indicate SQL error 11
and/or SQL error 0. See SAP note 392892 for details.
© SAP AG ADM520 6-24
Database Consistency Check
Mon 12 Tue 13 Wed 14 Thu 15 Fri 16 Sat 17 Sun 18
success success success success 0:00
Mon 19 Tue 20 Wed 21 Thu 22 Fri 23
success success success success 2:00
error
Mon 26 Tue 27 Wed 28 Thu 29 Fri 30
0:00 DB 0:00 DB 0:00 DB 0:00 DB 0:00 DB
9:00 Log 9:00 Log 9:00 Log 9:00 Log 9:00 Log
Mon 02 Tue 03 Wed 04 Thu 05 Fri 06
0:00 DB 0:00 DB 0:00 DB 0:00 DB 0:00 DB
9:00 Log 9:00 Log 9:00 Log 9:00 Log 9:00 Log
SQL Server Query Analyzer - [Query - sappod.master.sa - (untitled)]
detail stats.
DBCC CHECKDB (PRD) WITH NO_INFOMSGS
SAP AG 2002
Hardware problems, such as a defective hard disk controller, may cause inconsistencies in the
database. These inconsistencies may only be detected when a table or index affected by the
inconsistency is first accessed, or during a consistency check. To prevent problem escalation and
possible loss of data, you must identify errors early.
The Database Consistency Checker (DBCC) is a system administration tool that verifies pointers
internal to a database and its structure. DBCC commands typically lock user tables, indexes, and
system tables whenever they are run. In addition, they are very I/O intensive and should not be run
during normal operation.
Run periodic checks to ensure the physical consistency of your data. Use the SAP Planning Calendar
to schedule a database consistency check at least once a month during a period of low workload, for
example on the weekend. This job executes transact-SQL statement DBCC CHECKDB WITH
NO_INFOMSGS on the SAP database.
Check the run status of the consistency checks using the SAP Planning Calendar. If the job is
highlighted in red, check for further errors in the SQL Server error log. A file which reports all
errors found is written to X:\Program Files\Microsoft SQL
Server\MSSQL\CCMS_CHECK_DB_HIST_<year>.txt.
If the run status is green, no inconsistencies were found. The SQL Server error log displays
messages:
DBCC CHECKDB (<SID>) started at ... with Logfile: X:\ Program Files\Microsoft SQL Server\
MSSQL\ CCMS_CHECK_DB_HIST<year>.TXT.
DBCC CHECKDB (<SID>) executed by <user> found 0 errors and repaired 0 errors.
© SAP AG ADM520 6-25
Database Allocation Monitor: Consistency Check
SAP AG 2002
To perform a database consistency check, you can also use the Database Allocation Monitor
(Transaction DB02). Choose button DBCC checkdb. Do not run a consistency check on the database
while production work is carried out in the system.
© SAP AG ADM520 6-26
Database Allocation Monitor: Consistency Check
on a Table
SAP AG 2002
The Database Allocation Monitor (Transaction DB02) also enables you to perform a consistency
check on a single table. Choose button Detailed Analysis and specify the table to be checked. In the
following screen, choose button Check Table.
A DBCC CHECKTABLE WITH NO_INFOMSGS is executed on the database for the specified table.
This checks the integrity of the data, index, text, ntext, and image pages for the specified table.
© SAP AG ADM520 6-27
Database Inconsistencies
DBCC CHECKDB(PRD)
DBCC CHECKDB(PRD)
executed by <user>
executed by <user>
found X errors and
found X errors and
repaired 0 errors
repaired 0 errors
...
... Books Online
Job 'SAP CCMS Check
Job 'SAP CCMS Check SAP Notes
Database PRD
Database PRD
[20021213174935]' ::
[20021213174935]'
Step 1, 'Step1' :: Began
Step 1, 'Step1' Began
Executing 12/13/02
Executing 12/13/02
6:49:31 PM
6:49:31 PM Open a problem
...
...
message
Check for
error messages
Error log messages
SAP AG 2002
If an inconsistency has been detected during the DBCC run, you must take immediate action to
prevent further error escalation and possible data loss. Check the output of the DBCC check
immediately after each run.
If an inconsistency is detected, the job scheduled with the SAP Planning Calendar is highlighted in
red. The SQL Server error log shows the following message:
DBCC CHECKDB(<SID>) executed by <user> found X errors and repaired 0 errors
Log file X:\ Program Files\Microsoft SQL Server\MSSQL\CCMS_CHECK_DB_HIST<year>.TXT
reports all inconsistencies found.
If an error occurs, create a problem message immediately. Enter the full error text found in the log
files in the problem description. In most cases, you can correct the inconsistency without data loss.
Depending on the type and location, you may be able to correct the error by removing an index on a
table, or by restoring the database.
For a detailed description of errors, see Books Online or SAP Notes.
© SAP AG ADM520 6-28
No Connection to SQL Server
Database <SID>
SQL cannot be opened
Server Books Online
Error 1105: Could SAP Notes
not allocate space
...
Open a problem
message
Error 8651: Could not
Check for perform the requested
error messages operation because ...
...
SAP AG 2002
If the SAP System cannot connect to SQL Server, the SAP Service Manager displays a grayed-out
dispatcher symbol. Check the developer trace files located in directory
X:\usr\sap\<SID>\DVEBMGS<instanceno.>\work for an error description, as follows:
A suspect SAP database. In this case error Error 945: Database <SID> cannot be
opened because some of the files could not be activated appears. Similar errors occur in
the SQL Server error log and the Event Log. See Unit 5: Database Restore.
A suspect SAP database. Error 1105: Could not allocate space for object <object> in
database <SID> because the PRIMARY filegroup is full occurs. The status column in
table sysdatabases is set to suspect if SQL Server is unable to complete recovery on a
database because the disk drive no longer has free space. Execute stored procedure
sp_resetstatus <SID>, provide additional disk space and restart SQL Server. See SAP
note 81692 for details.
Not enough memory. Error 8651: Could not perform the requested operation because
the minimum query memory is not available occurs. Change SQL server configuration
parameters as explained in Unit 3: Performance Monitoring and Tuning.
If the problem cannot be solved, create a problem message immediately. Enter the error message
shown in the developer trace file. Books Online or the SAP Notes may contain a detailed description
of the error. SAP Note 98678 discusses connection problems.
© SAP AG ADM520 6-29
Summary of this Unit
Now you are able to:
Check for errors and solve common problems
Monitor the growth of the database and the transaction log
Extend the database:
Add a disk to an existing volume
Add a new volume
Expand data files
Perform regular administrative tasks
SAP AG 2002
© SAP AG ADM520 6-30
Further Documentation
ADM100 myTechnology
Administration
SQL Server Books Online
SAP Notes
Best Practices CCMS Monitoring for
mySAP
SAP AG 2002
© SAP AG ADM520 6-31
Unit Actions
? Exercises
Solutions
SAP AG 2002
© SAP AG ADM520 6-32
Regular Maintenance and Error Handling Exercises
Unit: Regular Maintenance and Error Handling
Topic: Space Management / Database Extension
At the conclusion of this exercise, you will be able to:
• Monitor the growth of the database and transaction log
• Handle a full transaction log
• Extent a database on a new volume
The customer detects that the transaction log of his SAP database fills up
rapidly and monitors its growth. As soon as the transaction log is full, he
has to resolve the situation.
The SAP database runs out of space on the volume it resides on. The
customer has to extent the database on a new volume.
1-1 Transaction log full
1-1-1 Open a Query Analyzer window and select the test database. Execute the
stored procedure fill_log. This stored procedure will fill the transaction
log by adding rows to the stores table.
Monitor the growth of the transaction log.
What error message is displayed when the transaction log is full?
Where else can you see the error message?
1-1-2 Why did the transaction log not grow dynamically?
Check the properties of the transaction log and its file.
1-1-3 What can be done to resolve the situation?
Are the transaction log file properties set up correctly? If not, change them.
1-1-4 Display the log used space again, and check the correctness of the
transaction log backup.
1-2 Database full, and extending the database on a new volume
1-2-1 Set the recovery model to simple on the test database.
Open a Query Analyzer window and select the test database. Enter the
command fill_db and execute it. This stored procedure will fill the
database by adding rows into database table stores.
Monitor the growth of the database. Execute fill_db again and check the
growth. What do you observe?
1-2-2 What error message is displayed when the database is full?
1-2-3 Extend the test database on a new volume by 4 MB. Choose C:\temp as the
new location.
Which steps do you have to perform?
How can you extend the database and equally distribute the load across the
two volumes?
© SAP AG ADM520 6-33
Regular Maintenance and Error Handling Solutions
Unit: Regular Maintenance and Error Handling
Topic: Space Management / Database Extension
1-1 Transaction log full
1-1-1 Monitor the growth of the transaction log of the test database during the
execution of stored procedure fill_log.
In the Enterprise Manager expand the server and the Databases folder, then
mark database test. Select the Tools → Taskpad to display the space usage
of database test. Selecting the refresh button or the F5 key refreshes the
display.
The Query Analyzer displays the following message:
Server: Msg 9002, Level 17, State 2, Procedure fill_log, Line 12
The log file for database 'test' is full. Back up the transaction log for the
database to free up some log space.
Use command DBCC SQLPERF(LOGSPACE) to display the log used
space. It should display a rate of about 90%.
Error message 9002 is displayed in the SQL Server error log and in the
Event Viewer.
1-1-2 The properties of the test database can be displayed with the Enterprise
Manager. Select database test, right mouse-click and choose Properties. In
the Transaction Log Tab, the log file is displayed, with its allocated size and
the file properties. The transaction log file should be set to grow
automatically. But the file growth is restricted.
Alternatively, you can use the SAP Database Allocation Monitor under
Tools → Administration → Monitor → Performance → Database →
DB02 Table/Indexes (transaction DB02) to display the properties of all the
databases on the database server. Choose Other DB → DB list and select
the test database. The column Limit displays the maximum growth.
1-1-3 Back up the transaction log of the test database. Use either the Enterprise
Manager or the Query Analyzer. Make sure to append the backup to the
existing backups on backup medium R3DUMP3. See the exercises in Unit4:
Database Backup.
The transaction log file of the SAP database should grow by at least 100
MB and there should be enough space left on the disk, on which it resides.
Let the transaction log file of the test database grow to at least 3 MB.
Change the properties in the Enterprise Manager.
1-1-4 Use command DBCC SQLPERF(LOGSPACE) to display the log used
space.
© SAP AG ADM520 6-34
1-2 Database full, and extending the database on a new volume
1-2-1 Go to the Properties window of the test database and set the recovery model
to simple. Choose OK. The transaction log of the test database will now be
truncated every time a checkpoint occurs.
Monitor the growth of database test during the execution of stored
procedure fill_db. In the Enterprise Manager, expand the server and the
Databases folder, then mark database test. Choose View → taskpad to
display the space usage of database test. Selecting the refresh button or the
F5 key refreshes the display.
After a few executions of fill_db you see that the first data file is expanded
automatically. All insert operations will now take place in this first data file.
As soon as this data file is full the second file is expanded and insert
operations take place in this file. The proportional fill strategy is no longer
used.
1-2-2 When the database is full, the Query Analyzer displays the following
message:
Server: Msg 1105, Level 17, State 2, Procedure fill_db, Line 15
Could not allocate space for object 'testtable' in database 'test' because the
'PRIMARY' filegroup is full.
The SQL Server error log shows the same error message. Transaction DB02
shows the sizes of the data files and the used sizes of 4MB each.
1-2-3 The goal of extending the database is to distribute future insert operations
equally across the two volumes. Therefore the database has to be extended
on both volumes. Perform the following steps:
Close all connections to database test and detach the test database from the
server, using the transact-SQL command
sp_detach_db ‘test’, ‘true’
Move database data file F:\mssql7db\MSSQL\data\TestData2.ndf to
C:\temp.
Re-attach database test using transact-SQL command
sp_attach_db ‘test’,
‘F:\mssql7db\MSSQL\\data\TestData1.mdf’,
‘C:\temp\TestData2.ndf’
Execute sp_helpfile in the test database to check the result.
Extend both data files by 2 MB each. Use the Enterprise Manager or
transact-SQL commands. Make sure that you change the maximum file size
the data files are allowed to grow as well.
Execute the stored procedure fill_db again to observe how the database
allocated new space proportionally on both files.
© SAP AG ADM520 6-35
