1 2 3 Previous Next

SAP on SQL Server

40 Posts

We can use below select command to check backup/restoration percentage. 

We can execute this query when MS SQL Backup or restoration is in progress.

Also this query will show the processes which are running in DB (Backup, Restore, rebuild, compression)

 

===========================================

 

SELECT command,

s.text,

start_time,

percent_complete,

CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '

+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '

+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,

CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '

+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '

+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,

dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

 

===========================================


We can execute this query from SAP level.

Go to DB02 --> Performance --> Additional Functions --> SQL Command Editor.


This will show the output of any SQL query statements.

We recently received some incidents from customers having problems to finish their database upgrade on SQL Server.


An essential part of upgrading SQL Server is running the Database Upgrade Completion, part of the SAP Tools for MS SQL Server, which is delivered within SWPM. It will perform many SAP specific tasks and will also increase the database compatibility level.

 

During the execution, the error was always the same, in sapinst_dev.log:

 

 

Errors when executing sql command: [Microsoft][ODBC SQL Server Driver]Associated statement is not prepared

[Microsoft][ODBC SQL Server Driver][SQL Server]The server principal 'DOMAIN\sidadm' already exists.

SOLUTION: If this message is displayed as a warning, you can ignore it. If this is an error, contact SAP support.

 

 

 

Well, the error is actually coherent but somehow strange. Firstly, one of the Database Upgrade Completion tasks is to adjust/modify the accounts <sapsid>adm (or simply <sid>adm or sidadm) and also SAPService<SID>. So, the first workaround was to try with another domain account, in the first case, with a Domain Administrator user. It simply worked!

 

Since we had already a few cases about this, we released a SAP Knowledge Base Article #2344768 explaining the scenario and the workaround, stating that the investigation for the root cause was still on the way.

 

The most challenging aspect was that it did not occur in every scenario. In other situations, a simply error was logged:

 


WARNING    2016-07-19 10:05:16.532 (DOMAINTEST\sidadm) [iaxmbcom.cpp:127] id=dbmodmssql.odbcdll.sql CMssLogger::logMessage

Errors when executing sql command: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not drop login 'DOMAINTEST\sidadm' as the user is currently logged in.

SOLUTION: If this message is displayed as a warning, you can ignore it. If this is an error, contact SAP support.


But it didn't prevent the Upgrade Completion to complete.

 

I remembered then about the Installation Guide for SAP NetWeaver for SQL Server. It is very specific to not use the <sapsid>adm account for installations, which for the same reason I never used it for a system copy (which in concept, also does a target installation).

Well, turns out, this is also true for running the SAP Tools for MS SQL Server! It wasn't clear before, but now it is documented in SAP Note 683447 too!

 

So I have updated the SAP Knowledge Base Article 2344768 stating that the root cause has been determined and now its solution is a final solution. You can still read a preview of this KBA without logging into SAP One Launchpad, but for the full content you have to be logged on.

 

 

Recommended readings and resources:

 

 

SAP Notes and KBAs:

NumberDescription
683447SAP Tools for MS SQL Server
2344768Database Upgrade Completion fails with "Server Principal Already Exists"
1890950Generic Installation Options
1609770Tracing database queries executed by sapinst, SWPM or SAP Tools for SQL Server

 

 

Additional spaces:

Database

Software Logistics

SAP NetWeaver Administrator

SAP NetWeaver Technology Platform

 

About SAP NW on SQL Server PS blog:

SAP NW on SQL Server PS blog is a non-periodic blog about SAP NetWeaver based products on SQL Server, maintained by the Product Support engineers which work on BC-DB-MSS and BW-SYS-DB-MSS components.

 

About the author:

Luis Darui is a Support Engineer at SAP Product Support, MCP with SQL Server. He works for SAP since 2014, with Databases and Operating systems.

Often in Product Support, we receive support incidents asking how to remote connect to a SQL Server database.

 

SAP NetWeaver (and all Business Suite systems based on NetWeaver, including SolMan) can connect to a database through 2 different options: DBCON/Multiconnect (ABAP Stack) and UDConnect (JAVA Stack). This is explained in SAP KBA 1601608 - How to access an external SQL Server database.

 

UDConnect

 

UDConnect can be used for BW/BI purposes, it uses a JDBC (Java Database Conectivity) driver to connect to a SQL Server instance. It can be used on Windows, Linux and Unix operating systems.

 

UDConnect cannot be used for DBACockpit. DBAcockpit requires the usage of DBCON and an ABAP Stack.

 

DBCON/Multiconnect

 

DBCON/Multiconnect uses the DBSL and the SQL Server Native Client (SNAC or more recent ODBC Driver for xx for SQL Server native) to connect to the database. This is only available to SAP NetWeaver (and all Business Suite systems based on NetWeaver, including SolMan) with an ABAP Stack. If your system is JAVA only, you cannot use DBCON.

 

The restriction with DBCON/Multiconnect and SQL Server is the database client. Microsoft provides client software only for Windows and Linux. Operating systems line AIX, HP-UX, Solaris and other Unix-based does not have a native client, and so there is no dbmssslib.dll or dbmssslib.so for those operating systems.

 

SAP Note 1644499 - Database connectivity from Linux to SQL Server

SAP Note 1774329 - Preparing your SAP instance to connect to remote SQL server

 

An alternative for AIX, HP-UX, Solaris and others

 

Customers can have their SAP NetWeaver/Business Suite/SolMan ABAP Stack running on AIX, HP-UX, Solaris and other operating systems and still manage to have a connection to an external SQL Server.

 

It is possible to install a Dialog Instance with Windows or Linux operating systems and then create the DBCON to SQL Server. For DBACockpit for example, you just have to redirect all jobs to this application server.

 

SAP KBA 2221305 - Workaround to connect remote SQLServer from AIX, HP-UX or Solaris machine

 

 

Best regards,

Luis Darui

SAP Product Support

Hi all.

I want to share with you, what do you do to extract info from SQL Server to MsExcel.

 

You have to follow this steps:

 

1.- create a New View in SQL.

 

vista en SQL2.JPG

 

You have to write a query (this is a single example):

 

SELECT docnum, doctotal, cardcode, cardname

FROM OINV

WHERE docdate between getdate()-30 and getdate()

 

this query show you the invoices saved in last 30 days.

 

2.- Go to MsExcel --> Main Menu --> Data  --> other sources --> from SQL server

vista en SQL3.JPG

 

3.- clic on: From SQL Server

vista en SQL4.JPG

 

In this windows, you have to write a loca IP of your server of SQL. In my case is 10.10.1.4

You have to write a user (in my case the user is INFO), write a password.

clic in NEXT bottom

 

4- Select the database where you saved your view

and  (below) Select the view

vista en SQL5.JPG

 

5.- clic on finish bottom

vista en SQL6.JPG

 

6- clic on OK

vista en SQL7.JPG

 

7- The result of your SQL View, show on msExcel


vista en SQL8.JPG

 

8- Ready. You have a SAP information from your SQL Database on MsExcel.

9- If you want to refresh the information, you have to go to:

 

msExcel Main Menu --> Data --> update all

in the windows that apear you have to write

user (in my case the user is "info")

password

 

clic on OK.

 

And the info on msExcel from your SAP database will be updated.

vista en SQL10.JPG

 

Note: the user info and the password you have to create on SQL Server like a security login. (I show you in the image below)

 

vista en SQL11.jpg

 

I hope that it help you.

Best regards.

 

Saul Hernandez.

Hi all.

I want to share this scritp.

 

Whit this one you can make a SQL Job and scheduler it to execute the backup on a specific date and time.

 

I wish It help us.

 

Best regards.

Saul.

 

 

 

USE [YourDataBase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[SP_CrearBackupsDiario]
--This script create a backup of every database.
--This script use a cursor that go one by one making the backup.
--Saul Hernandez.
AS
DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'c:\backups\'  
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR  
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  
WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  
       FETCH NEXT FROM db_cursor INTO @name  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor 
GO

You probably have heard that SAP Product Support is creating Knowledge Base Articles with embedded videos.

Our Product Support team is now working on to create articles with videos to help customers and partners on how to perform specific tasks, troubleshoot, etc.


We added recently a specific wiki page in our SCN Wiki SAP on SQL Server to list these articles, in order to facilitate users who are navigating to find them. You can then navigate to 'KBA VIDEOS' section or you can access directly it from by clicking here.


In time we will be adding more articles with embedded videos.


As always, feedback is welcome. You can leave your comments, questions or suggestions about them in the wiki page.


Related Spaces:


SAP NetWeaver Application Server

SAP NetWeaver Administrator

SAP NetWeaver Technology Platform

Database

 

 

Best Regards,

Luis Darui

SAP Product Support - SAP on SQL Server

Hi!

 

I've been an SAP Basis Administrador and Database Administrator for almost eight years, and this guideline helps me to do the fastest Homogeneous System Copy's / System Refresh that I have known so far. Since I work in a somewhat demanding environment with tight schedules, sometimes the fastest solution is the best solution for my clients.

 

This is a guide that I'm working with my own personal experiences. All of the information can be found in the SAP Community Network. The respective links are in the guide as well as the respective appreciation for the people that have wasted time writing the posts. I just gathered the information.

 

Thank you for your time and effort.


Best regards

 

Rui Fontinha



MS SQL Server

 

Manual R/3 System Copy

 

 

     1- Important SAP Notes:

 

Note 1294762 - SCHEMA4SAP.VBS

Note 683447 - SAP Tools for MS SQL Server

Note 151603 - Copying an SQL Server database

 

     2 - Important: This method can be followed only for already installed systems (or) for System Refresh (Only works for Homogeneous System Copies).

 

     3 – Steps:

 

     Source System: 

 

    1. Stop SAP (R/3) System.
    2. Open the SQL Server Management Studio.
    3. Please note down the properties of your current DB.
    4. Take the DB offline.
    5. Detach the Database.
    6. Copy the Data (MDF, NDF) files and log (LDF) files to the Target system.

 

     Target System:

 

    1. Stop SAP (R/3) System.
    2. Open the SQL Server Management Studio.
    3. Please note down the properties of your current DB.
    4. Take the DB offline.
    5. Detach the Database.
    6. Rename source system  files <SID> with target <SID> in the Data (MDF,NDF) files and log (LDF) files as follow:

<SID>DATA1.mdf

<SID>DATA2.ndf

<SID>LOG1.ldf


    1. Attach Database for SQL Server in the target system using SQL Server Management Studio.
    2. Convert the Database to the new target system <SID> and schema using the SCHEMA4SAP.VBS from note 1294762. You can also run the STM Tool provided by SAP for the schema conversion from note 683447.
    3. Please do the Homogeneous System Copy Post Procedures.

 

     Additional Comments:

 

     Re: Schema4SAP vs. SWPM->System Copy->Database instance?

 

     Link: https://scn.sap.com/thread/3386983

 

     Beate Grötschnig

 

     Schema4sap.vbs does not touch anything else but the database. If offers two options:

 

     Schema repair and schema move.

 

     A schema repair script will fix all logins on SQL Server level and the mapping to the corresponding users in the SID DB. If any users or logins are missing,      the script will create them for you. If any authorizations or privileges are not set correctly the script will set them for you.

 

     The schema move script will move everything in your database into a different schema, e.g. if you copy the DB from P11 to Q11 you can use the schema      move to move all objects in the DB to schema Q11.

 

     SWPM in contrast does not touch DB level only. If you use SWPM for a system copy it will:

 

     - Install the SAP software on the target machine, install common system files, do all the schema move and schema repair tasks for you, and so on.

 

     Schema4SAP.vbs within the course of a system copy is useful if the target system is already fully installed and the only task you want to carry out is      refresh the target system with a new copy of the source DB. Then you can simply restore the source DB and use schema4sap.vbs to move and repair the      schema.

 

     If you want to copy a system to a host with no SAP being installed yet, you'll have to carry out a full system copy with SWPM by following the system      copy guide.

 

     Regards, Beate.


EOF

Hi everyone!

 

I'm very proud to announce that as of November 1st, 2015 the new SCN Wiki SAP on SQL Server is available for community access in the following address:

 

http://wiki.scn.sap.com/wiki/display/SAPSQL/SAP+on+SQL+Server

Make sure to bookmark this in your favorite web browser!

 

sap_on_sqlserver.png

 

 

What is SAP on SQL Server SCN Wiki?

SAP on SQL Server is a SCN Wiki space dedicated to provide SAP NetWeaver and MS SQL Server related content for the SAP community.

There is a lot of content about SQL Server around in MSDN blog SAP on Microsoft Platform and Books Online, so in the Wiki you'll find content that is really relevant and required for SAP NetWeaver.

 

 

Who maintains SAP on SQL Server SCN Wiki?

Currently, this SCN Wiki is maintained by the SAP support employees. Working closely with SAP Customers, they're able to get involved with many scenarios, questions and issues and then transform them into new content for the wiki.


What changes in the existing SCN space SAP on SQL Server?

Nothing is changed. The existing SCN space will continue existing, and the SCN discussion for SAP on SQL Server will still be used for questions, discussions, blog posts etc. The purpose of having a SCN Wiki for SAP on SQL Server is the ability to organize content under topics and create wiki documents to help SAP community, customers and partners to understand SAP NetWeaver and SQL Server (in addition to the existing content) and troubleshoot problems that affect their systems.


Where should I post my feedback for the existing wiki pages or content?

In each wiki page there is a place to write comments. We expect feedback and questions related to the wiki content there:

wiki_comments-feedback.png


If I have an issue or am looking for help, where should I post?

Nothing changes, you should still use the SCN Discussion SAP on SQL Server for this:

SAP on SQL Server

 

 

Kind Regards,

Luis Darui

Hi everyone,

 

Months ago I worked with some SAP customers where they had some problems to startup their SAP Netweaver system after implementing AlwaysOn feature on SQL Server.

 

Basically the problem was strdbs.cmd script in yellow status and never starting the disp+work. This also affects the strdbsj2ee.cmd script during the startup of a JAVA stack.

 

After some troubleshooting it was discovered that the problem was related with the environment variable MSSQL_SERVER values. It contained the AlwaysOn Availability Group Listener and the parameter MultiSubnetFailover, like "MyAAGListener;MultiSubnetFailover=YES". Later it was discovered that the same would happen to customers that use DBM with FailoverPartner, because like AlwaysOn, you need to add connection sgring options to MSSQL_SERVER (and to the default profile also).

 

More recently, SQL Server named instances or default instances running on non-default port and AlwaysOn Availability Group Listener running on TCP/IP port other than 1433 (default). This happens because you need to add the TCP/IP port number after the Database Server hostname/Instance or AlwaysOn Availability Group Listener. Here are some examples:

 

MSSQL_SERVER = SAPDBSRV,12205

or
MSSQL_SERVER = SAPDBSRV\ERP,12205

or

MSSQL_SERVER = SAPDB-ERP-LIST,1205

 

 

The more recent versions of strdbs.cmd and strdbsj2ee.cmd introduced a new code that in the above scenarios causes strdbs.cmd/strdbsj2ee.cmd to hang in yellow state during the startup of SAP NetWeaver ABAP/JAVA systems.


The SAP Knowledge Base Article 2137130 - SAP startup hangs with SQL Server address the above scenarios and provide the solution and possible workaround for them.


Related spaces:


SAP NetWeaver Application Server

SAP NetWeaver Administrator

SAP NetWeaver Technology Platform

SAP on Microsoft Windows

 

 

In order to view the contents of the SAP Notes and KBAs, you will need to be logged into Service Marketplace.

 

 

I hope you enjoy this blog and any comments or suggestions are welcome.

 

Kind regards,

Luis Darui

Hello,

 

During this week I've encountered some customers having difficult to upgrade their SAP NetWeaver JAVA stack on SQL Server. (It means that this post is specific for customers running SAP NetWeaver systems with JAVA stack on SQL Server.)

 

They were getting stuck in the PRE-PROCESSING phase with SUM SP13 PL6. Later it was identified this was an error in SUM SP13 PL6 causing SUM to look at wrong location for the JDBC driver.

 

This is fixed with SUM SP13 PL7. You can download it from http://service.sap.com/sltoolset (You need to be logged into Service Marketplace).

 

If you have downloaded previous version of SUM and is planning to upgrade your JAVA system running on SQL Server, we recommend you to download the version SP13 PL7 of SUM, or higher, otherwise you will get errors during the upgrade.

 

Please find more information on SAP Knowledge Base Article 2211685.

 

Small update:

 

The errors in question are:

 

[Error ]: Could not connect

[Error ]: com.sap.sql.log.OpenSQLException: Could not load class com.microsoft.sqlserver.jdbc.SQLServerDriver with class loader System

[Error ]: Could not establish open SQL connection. Exception com.sap.sql.log.OpenSQLException: Could not load class com.microsoft.sqlserver.jdbc.SQLServerDriver with class loader System

 

Related spaces:

 

Software Logistics

SAP NetWeaver Administrator

SAP NetWeaver Application Server

 

Best Regards,

Luis Darui

Moving the SAP DATA FILES from existed to newly added drive

 

If your SAP database or log files are running out of disk space, you have to add new disks to the file system.


Security Permissions :

 

Your Windows user account must be a member of the db_owner fixed server role in the SQL Server instance to detach DQS databases.

Your Windows user account must have CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission to attach a database.

You must have the dqs_administrator role on the DQS_MAIN database to terminate any running activities or stop any running processes in DQS.

 

If an existing device cannot be extended, you have to add an entire new device to your system.

In this case, you first have to configure the new device as an NTFS partition. Then you have to move existing SAP database files have to the new device by detaching from and re-attaching the database to the SQL Server.

 

Case study - I have taken an instance SM1.

 

Procedure –

 

1. Shut down the instance.

2. Detach the Database.

3. Copy the Data files from Existed drive.

4. Paste the copied Data files to newly added drive.

5. Attach the Database.

6. Start the instance.

1.JPG                                      

  • Instance has shut down.

 

  1. In the SQL Server Management Studio right-click the SAPSM1 database and choose Tasks -->Detach, The Detach Database dialog box appears.

2.JPG

Select the “Drop connections” check box, Choose OK to detach the database.

3.JPG

The SAP database is detached from the SQL Server and is no longer visible in the SQL Server Management Studio.

 
Copy the required SM1 Data files from Existing device,

4.JPG

Paste the SM1 Data files to newly added drive

5.JPG

In the SQL Server Management Studio, expand the server, right-click Databases and choose Attach. The Attach Database dialog box appears.

6.JPG

 

Specify the mdf file of the SAPSM1 database by choosing the Add button.

All the files belonging to the SAPSM1 database are listed under Original File Name.

7.JPG

We have to change the path of which one need to change

Now where we have located, it’s assigned that path only. Choose ok to attach the Data base.

8.JPG

 

Now able to view the SAP Instance in SQL server Management studio, and is again visible and accessible for the SQL Server. The SQL Server can now use the newly added drive.

9.JPG

 

 

Started the SAP instance, and Instance is up on running.

This blog post will cover some part of this topic which seems to be the most painful and unpredictable  situation: database corruption. They're mostly physical inconsistencies, associated with storage and raid controllers (physical devices), drivers, software controllers, etc.

 

Database corruptions can happen at any time due to many factors, but most of them caused by hardware issues. You should be prepared for it.

 

Of course this blog post is intended to help BASIS administrators or new DBAs starting with SQL Server. If you're already an experienced SQL Server DBA and is new to SAP, this blog has some useful information, but nothing that you haven't seen before I hope. If you're an "old sea dog" on SQL Server and SAP, then probably the content of this blog is irrelevant with redundant information that bothers you every day .

 

 

Identifying database corruptions:

 

I can say that 2 DBCC CHECKDB are enough, most of the times, to check the database consistency.  1 result against the database and a second result against either the same database on the same database server/instance after you clear the buffers (it may be only a transient corruption, but still a corruption!) or you restore the current database against a second server (preferable) or another instance on the same server and get the results to compare.

 

Make sure you read completely the SAP Note 142731 - DBCC checks of SQL Server.

 

 

Performing the first DBCC CHECKDB:

 

In the SQL Server Management Studio perform the command:


DBCC CHECKDB ('<SID>') with no_infomsgs;

dbcc_checkdb1.png

 

Save the results for later analysis.



Re-validating the results:

 

When occurs a first access a database page, it's first stored in the data buffer of the SQL Server. Due to software bugs or defective RAM modules, a transient corruption may occur. When you invalidate the data cache, the database corruption can simply go away - but you shouldn't sit and forget about it! Always investigate the source of the corruption! For this it's necessary to have a second result to ensure that the database is really free of corruption or the corruption was transient. Or in the worst case, confirm a database corruption.

 

 

 

When you invalidate the data cache, the SQL Server will have to read all the data from the disks again. If you use a RAID system, some pages can be out of sync (RAID system mirror not identical). Due to optimization the reads are taken sometimes from the original and sometimes from the mirror. With this, the results of the DBCC CHECKDB can differ each time you execute it after you clear the data cache.

 

 

I personally suggest to backup / restore your database into another server/instance. Preferably, to another hardware, to eliminate a complete investigation over a system that is suspect.


  1. Reset the data cache:


    To clear the buffer cache, perform the command:

    DBCC DROPCLEANBUFFERS;
    
    

    dbcc_dropcleanbufers.png

    Note: This command invalidate the buffer cache and if you perform it in a production system, the database will have to read all the data from the disk again. The users will observe bad performance and even TIME OUTS when running programs and transactions. If it's not feasible for you to invalidate the buffer cache, then backup and restore the database into another server/instance!

     

  2. Backup the database and restore it into another server/instance.

    This option will eliminate some questions about the effectiveness of the analysis and efforts in order to fix/restore your database/system. I won't enter in details on how to backup / restore your database.

  3. Repeat the DBCC CHECKDB, save the results to analyze it.

 

 

An inconsistency has been detected. What now?

 

Well it's time to identify them in the results. Depending on the following message, you're able to repair the tables:

CHECKDB found 0 allocation errors and 8 consistency errors in database 'NW1'.

repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (NW1).


The above results indicates that the corruption is not severe and you can fix without data loss, which is the opposite of the following error message:

CHECKDB found 10 allocation errors and 1739 consistency errors in database 'NW1'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (NW1).


It indicates that at least one table is severely corrupted and cannot be repaired.


I won't go in deep on methods that can help you to extract the data from a severely damaged table because it's not the goal of this blog post. You may also keep in mind that depending on the table that is affected you can also rebuild the table with data from other tables (it depends on the component that is responsible for the table involved) or it's a table that doesn't store sensitive data and can simply be truncated or dropped and recreated. But for this I strongly suggest you to read the entire blog. I'll approach later with some suggestions if your minimum repair level is "repair_allow_data_loss" - also known as RADL.

 

For each table that is listed in the CHECKDB results is necessary to perform another DBCC CHECKTABLE to check the level of the damage to each table:

DBCC CHECKTABLE ('<sid>.<TABLE_NAME>');

dbcc_checktable.png

 

For the tables listed with "repair_rebuild" or "repair_fast" as the minimum repair level, you can repair the tables with the DBCC CHECKTABLE and REPAIR_REBUILD. However, you have to set the database to SINGLE_USER first:

 

ALTER DATABASE <SID> SET SINGLE_USER;

After completing, you can repair each table:

DBCC CHECKTABLE ('<sid>.<TABLE_NAME>',REPAIR_REBUILD);

dbcc_checktable2.png

 

 

After completing the repairs, perform another DBCC CHECKDB whether your database is now clear. If the result is positive, set the database back to MULTI_USER:

 

ALTER DATABASE <SID> SET MULTI_USER;

 

Before releasing the system back online to the users, take a full backup for safety

 

 

The minimum repair level is "repair_allow_data_loss". What to do?

 

If the table was marked as the minimum repair level as "repair_allow_data_loss" it means that this corruption cannot be fixed. In some cases (most of them), the database catalog is still consistent and the affected objects are application tables. Depending on which data was affected, maybe it can be (or in most of the cases, cannot be) reconstructed from redundant application data.  You can identify the application areas that are responsible for those tables and try to approach them with the list of the affected tables and see if they can recover. It's explained on SAP Note 1597910.

 

It's not guaranteed that it's possible to recreate a database table with the complete data it had before. But you can give a try.

 

For all instances, in case you have a database object corruption with "repair_allow_data_loss", the best solution is to rely on your backups. Restore your database into another server. Start with the latest good full backup and start restoring the transaction log backups until you recover your system completely.

 

If a restore is not possible due to retention policy of your backups or your backups are also corrupted/not consistent, then the best approach is to contact Microsoft directly or a third party. SAP urges to customers to maintain a minimal (and efficient) backup strategy and even provide functions which assist the administrators in the areas of backup and database consistency checks. Disaster recovery solutions and efficient backup strategies have proved to be more efficient than to try to remedy those situations.

 

To understand the SAP Goals and Policy with the handling of the database corruptions on SQL Server, read carefully and completely the SAP Note 1597910.

 

 

Action required! Investigate and eliminate the source of corruptions!

 

All work done so far to fix/restore your database may be jeopardized if you simply ignore the fact that something went (and probably still) wrong in your database server. Investigate and eliminate the source of the corruption is as important as fixing/restoring the database. Even that the corruption can be caused by software or a bug, you can almost always find the root cause of the corruption in the hardware or driver problems.

 

 

In order to prevent database corruptions, what can I do?

 

You can't prevent database corruptions. It's something that is beyond your control. It can happens even if follow all the best practices. The difference is the impact / downtime and the risk that you lose your data is even lesser or 0 if you follow all the recommendations from SAP and Microsoft.

 

You can work proactively to:

  • Keep your software updated: regularly apply patches to your Windows and SQL Server;
  • Keep your drivers tested and updated.
  • An efficient backup strategy - database corruptions do not spread through transaction logs (unless the storage where you keep your backups is also damaged). Read the SAP Note 1297986 for suggestions to backup/restore strategies for SQL Server.  Testing the consistency of the backups is also part of the backup strategy.

  • Regularly perform DBCC checks to make sure your database is free of corruption.
  • As of SQL Server 2012 you can benefit from AlwaysOn. As there is no shared storage between the primary and secondary replica, if the primary replica is corrupted, there is a chance that the secondary is not. Consider implementing an efficient disaster recovery strategy, it can save a lot of time and your heart may live longer.
  • Do not neglect a backup strategy only because you have a high availability like Windows Failover Cluster or AlwaysOn. Backup is a must and you shouldn't even consider think about giving it up!
  • DO NEVER RUN DBCC WITH REPAIR_ALLOW_DATA_LOSS! Unless it's explicitly asked to you from SAP. And I haven't asked!



Best Regards,

Luis Darui

Support Engineer, SAP NetWeaver DB/OS Platforms
SAP Labs Latin America.

1. Check the Data Cache Hit Ratio of your database!

 

One of the most important things to configure is the memory for SQL Server. If you observe a bad database performance and a low data cache hit ratio for a long time during database load, you should increase the memory for SQL Server. Low Data Cache Hit ratio values means that your server will be performing more I/O operations than it should be.

hit_ratio.png

 

If you run your SQL Server on a Virtual Machine and your data cache Hit Ratio doesn't increase overtime, despite of having enough memory to increase the data cache Hit Ratio, check the number of "Free Pages". If the value is high , it may indicate that VM is using a dynamic memory feature, which is also known as "Memory Ballooning". If the data cache Hit Ratio is low, there will be a lot of I/O operations and it will "mislead" you to think that your I/O subsystem is not working properly.

 

 

Check the following blog page of our Microsoft colleagues on MSDN and read the white paper to better understand:

 

Virtualization – Overcommitting memory and how to detect it within the VM

 

 

You can use the RAMMAP to analyze this:

Introduction to the new Sysinternals tool: RAMMap

 

 

 

2. Check the I/O performance!

 

For the most recent SAP NetWeaver systems, you can access the DBACockpit transaction and open the Performance folder and select "I/O Performance". Hit the button "Current Values" and observe the ms/Read columns for the data files and the ms/Write columns for the transaction log.

 

ioperformance.png

 

 

You can get more details by double clicking the files to compare the data load/write and the ms/Read/Write.

If your database have been restarted recently and your data cache Hit Ratio is not high enough, you might notice high data loads (Reads/sec and ms/Read).

 

 

In normal operation, the read time of the data files shouldn't be higher than 15ms and the write to the transaction log shouldn't be higher than 3ms.

This kind of error is usually caused by hardware. Check and fix the I/O subsystem or approach another I/O subsystem vendor in order to get assistance to deliver an acceptable performance.

 

 

More information:

987961 - FAQ: SQL Server I/O performance

Juergen's whitepaper:

SAP with Microsoft SQL Server 2008 and SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability - Part I: SAP Architecture and SQL Server Basic Configurations, Features Used, and Windows Configurations

 

 

Ah, make sure you don't have more than one transaction log file. It's also a common mistake for DBAs starting with SQL Server to use other database vendor recommendations .

 

 

3. Check whether locked pages can be used!

 

The major part of the main memory allocated by the SQL Server is the Data Cache. It's important that the Data Cache is not paged out, otherwise the database will read the pages from the disk and not from the memory. With SQL Server 2005 and higher versions, it's possible to disallow the operating system to page out pages allocated by the SQL Server.

Check the SAP Note 1134345 - Using locked pages for SQL Server

 

 

4. Check the database parameters

 

Bad or incorrect parametrization can impact the SQL Server I/O performance, specially for the memory settings. Check whether your SQL Server parameters are set according to bellow notes (select the note that fits your SQL Server version):

 

879941 - Configuration Parameters for SQL Server 2005

1237682 - Configuration Parameters for SQL Server 2008

1702408 - Configuration Parameters for SQL Server 2012

1986775 - Configuration Parameters for SQL Server 2014

 

 

5. Database Compression

 

As of SQL Server 2008 you can use row or page compression in SQL Server. Compressing the database objects can significantly decrease the amount of space occupied. With less space occupied, you'll have less I/O operations. Database compression is default in all newly installed systems as of May 2011.

 

 

To compress your database you must first fulfill all the prerequisites from the following SAP Note:

 

1488135  - Database compression for SQL Server

 

 

How to compress:

 

    1. Open the SA38 transaction and run the report "MSSCOMPRESS"

    2. Filter the tables that are not compressed:

db_compress.png

 

    3. If uncompressed objects are found, you should compress them. The above SAP Note 1488135 gives more background on how to do this.

 

 

In order to open the SAP Notes you must be logged into Service Marketplace.

 

 

Best Regards,

Luis Darui

Are you facing disconnections (sql/database error 0, 11, 121, -1 or 10054) with your SAP system?

 

The following points might help you to solve this issue!

 

1. Refer to Dale's presentation: Analyzing Network Errors on SQL Server.

 

2. Follow all the steps in the SAP KBA 1478133.

 

3. Some of the following points are common errors that are prone to cause this issue:

  • Using a ODBC client instead the SQL Native Client;
  • Using older versions of the SNAC (always use at least the same version of your database instance);
  • Older versions of DBSL and SAP Kernel;
  • Running old versions of SQL Server (always run the latest Service Pack and the latest Security update, hotfixes and cumulative updates provided by Microsoft - see Note 62988 and KBA 1733195).
  • Hardware problems, outdated NIC drivers, Server Memory, etc (hardware, physical network, etc);

 

4. You can perform the following steps to try to avoid those errors:

  1. On the server side, increase the registry setting HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters.
    TcpMaxDataRetransmissions to 10 {up from the default setting of 5}.
  2. On the client side, both the "Keep Alive" and "Keep Alive interval" can be safely increased via the SQL Server Configuration Manager tool.
    The numbers are in milliseconds and the defaults are 30000 and 1000 respectively.
    Increase "Keep Alive" to 90000 and "Keep Alive interval" to 5000.

 

5. For further information, double check Notes 1794178 and 1593183.

 

6. Is your SAP system running on VMware? If yes, also check:

 

Related Spaces:


In order to view the contents of the SAP Notes and KBAs, you will need to be logged into Service Marketplace.


Hope you like this blog and any comments/suggestions are welcome!

 

Best regards,

Eduardo Rezende

Performance issue becomes a real problem for some SAP systems. Especially if your DB size is big, there are many customer codes.

 

In one of our customer's system we had a performance issue. We had a meeting with all parts involved in SAP Systems. HW vendor, Network group, Business, System Admins and BASIS. The result of the meeting was an action list.

Beside all architectural actions, one of the action was finding fragmanted indexes and applying defragmantation and it was my duty to do the needful.


This blog is output of this action and about index defragmantation on SQL Server.

 

I will try to explain the topic in the following order.

  1. List the indexes that will be possibly defragmanted and effecting the system performance
  2. Analyze the indexes
  3. Defragmant the indexes
  4. Run new Statistics for the indexes
  5. Results and Chart
  6. Pros and Cons of defragmanting

 

And these are the details of the steps:

 

1. List the Indexes

When we were listing the fragmanted indexes our scope was on the indexes that are used in expensive SQL Statements. We also checked the indexes of the tables that are accessed sequentially on process overview.

 

Go to DBACOCKPIT --> Performance --> SQL Statements

SQL_1.jpg

Double Click on one of the SQL Statements.

Go to Explain Tree Tab to see the index names used in the SQL Statements.

SQL_2.jpg

For other indexes we use SM66 Global Work Process Overview.

Check the sequential reads and related tables.

 

SQL_3.jpg

After taking the list of related tables you go to SE11 and take the index names.

SQL_4.jpg

 

2. Analyze the Index

Go to DBACOCKPIT --> Space --> Single Table Analysis

Enter the related table name and select the index name.

Select DBCC showcontig  button.

     SQL_5.jpg

 

     Run one of the options Fast, All Levels and Normal.

     SQL_6.jpg

Yo can also use SQL SQL Server Management Studio to find DBCC Showcontig results using similar Queries like below.

     SQL_7.jpg

DBCC SHOWCONTIG allows you to measure fragmentation and page density levels on indexes.

 

The result is analyzed according to the document "Microsoft SQL Server 2000 Index Defragmentation Best Practices" mentioned in SAP Note 159316 - Reorganizing tables on SQL Server. You can check the "Analyzing Output from DBCC SHOWCONTIG" section in the document for details.


The document says that  "High values for logical scan fragmentation can lead to degraded performance of index scans. Consider defragmenting indexes with 20 percent or more logical fragmentation."


It states also that "indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."

 

So we selected all indexes that has logical scan fragmantation more than 20% and more pages.

 

3. Defragmant the indexes

    

We used SQL Server Management Studio Query editor to run scripts for defragmanting.

Examples:   DBCC INDEXDEFRAG (<SID>, "<sid>.EKPO", [EKPO~0])

                    DBCC INDEXDEFRAG (<SID>, "<sid>.RFBLG", [RFBLG~0])

 

Duration of defragmantation changes according to the index sizes and Logical Scan Fragmentation percentage. As you see below screen it took 3 hours. Some lasts more than a day.

 

This process also generates logs. So you must monitor your log space during the process. Otherwise your system can stop due to lack of log space.

 

DBCC INDEXDEFRAG reports the estimated percentage completed every five minutes.

 

DBCC INDEXDEFRAG can be stopped at any point in the process, and any completed work is retained.

 

SQL_9.jpg

4- Update Statistics

     We updated statistics of the related tables after defragmantation to obtain the changed statistics.

     SQL_10.jpg

5- Results and Graph

    When we rerun DBCC Showcontig command after defragmantation we can easily see the decrease in Logical Scan Fragmentation.

     The value became an acceptable value as it is seen in below picture.

     SQL_11.jpg

     As a result, we took the I/O Stall read and write times from DBACOCKPIT --> Performace --> History --> Database Collector Data Time Series table.

     SQL_12.jpg

     We exported data to an excel sheet and took the averages of all read and write times for the whole period. (Red and orange lines in below chart).

     From the chart below it can easily be seen that I/O values decreased significantly. So it worth defragmenting.

     SQL_13.jpg


6- Pros and Cons of defragmanting

  • First of all, SAP explicitly recommends not to reorganize or rebuild any database objects on a regular basis. You should not even reorganize or rebuild objects as an attempt to solve a performance problem as long as it is not evident that fragmentation is the root cause of the problem (which it hardly ever is).

 

  • You can do this process for particular indexes that are fragmented with high percentages, has alot off pages and used in expensive sql statements.

 

  • It is time consuming job

 

  • Generates lots of logs, monitoring is must

 

  • Defragmentation can be done online. Instead DBREINDEX can be used for offline solution.

 

Resources:

159316 - Reorganizing tables on SQL Server

1660220 - Microsoft SQL Server: Common misconceptions

Microsoft SQL Server 2000 Index Defragmentation Best Practices

Actions

Filter Blog

By author:
By date:
By tag: