FAQ: Microsoft SQL Server
Q1: Is it possible to disable the transaction log completely ?
A1: No.As the transaction log contains the 'redo' and 'undo' information for the transactions, it can't be disabled. This information is needed to ensure the logical consistency in a case of a rollback of transactions. Even when the database is in "simple" or "bulk-logged" mode, the SQL Server logs transactions within the transaction log. The effects of the different database modes for the transaction log are described in the note 421644.
Q2: How large the transaction log can become ?
A2: This value is very difficult to determine, as it depends on different factors e.g. the log backup interval, the runtime of transactions and the amount of data that is changed or inserted during these transactions, just to name a few. Setting special database modes can decrease the amount of the used log space in some cases. The notes 400251 and 421644 gives you an overview over the effects of different database modes for the transaction log.
Q3: What do I have to configure in SQL Server for the access of the NT users "<sid>adm" and "SAPService<SID>" ?
A3: The configuration within the SQL Server is normally done by r3setup or sapinst. In some rare cases it might be necessary to create and/or configure the logins for the SAP system by hand. As there are different logins for different R/3 releases and configurations, you should consider the notes 151603, 551915 and 28893 to ensure the correct configuration of the logins and user.
Q4: My SAP System runs under Oracle (DB2, SAPDB, ...) and I want to exchange data with a non-SAP Microsoft SQL Server directly from ABAP.
A4: The establishing and use of connections from an R/3 system to another non-SAP database (from the same vendor or not) is called multi-connect and is described in note 178949.
Q5: I want to change the passwords of SQL Server logins for security reasons.
A5: As a change of the password of any SQL Server login may have a big impact to your SAP system, you should consider the note 28893 before changing any password.
Q6: I'd like to change the physical layout of my SQL Server database.
A6: All possible and supported file operations on SQL Server databases are described in note 363018. Please follow the detailed explanations given within this note.
Q7: The transaction log of the SQL Server is full.
A7: When you encounter the error 9002, which indicates a full transactions log, you should read note 421644, which deals with the root causes and solutions of this problem.
Q8: I'd like to make a homogeneous system copy of the <SID> database.
A8: Note 151603 explains in detail how you could perform a homogeneous system copy by use of the SQL Server commands sp_detach_db and sp_attach_db. With the described procedure, you don't need to export/import the complete database by means of r3load, what's speed up the system copy process.
Q9: I encounter connection problems with external tools (tp, saplicense, r3trans, etc.).
A9: If you encounter connections problem with the external tools of a SAPsystem (tp, saplicense, r3trans, etc.) the notes 128126 and 98676 describe the potential causes and their solutions.
Q10: Which releases of SQL Server are supported by SAP?
A10: All supported Service Packs for MS SQL Server 7, SQL Server 2000 and SQL Server 2005 are listed in note 62988.
Q11: The transactions ST04, DB02, DB12 and DB13 don't work as expected.
A11: If you have problems with the transactions ST04, DB02, DB12 or DB13 you should check the installation of the SAP Database Monitor for MS SQL Server by means of note 139945 and note 166861.
Q12: Is there more detailed FAQ about MS SQL Server?
A12: Yes. The detailed FAQ, that covers a number of different questions is attached to this note in MS Word format, file "SQLServerFAQ.zip".
Q13: Is there more information on SAP BW performance on MS SQL Server 2000?
A13. Yes. The document "SAP BW 3.x performance on Microsoft®
SQL Server 2000" is attached to this note with the file name "SAP_BW_3_1_2.zip". You can also download that document from the SAP Service marketplace www.service.sap.com/bi --> Performance --> Database-specific --> "Performance: SAP BW 3.x Performance on Microsoft SQL Server 2000 (doc)"
Q14: I deleted half of the rows of table XYZ. Why is the size of XYZ nearly unchanged?
A14: The size of a table as displayed in the SAP database monitor is taken from SQL Server systemtables. While the number of rows is accurate, SQL Server does not guarantee the reserved, data, and index size to reflect the current size. For performance reasons these data are not maintained synchronously with DML operations.
The table size information can be brought up to date by running the updateusage command from the SAP database monitor or executing the dbcc updateusage on XYZ manually.
Q15: I deleted half of the rows of table XYZ. Why did not drop the size of XYZ signigicantly, even after executing dbcc updateusage ?
A15: SQL Server automatically reclaims extents of a table which are no more used and makes them available for other objects as database free space. But if only one table row is left in an extent, this 64kB unit will stay allocated to the table as SQL Server does not automatically shift rows to other pages.
If large parts of table XYZ were deleted it may happen that only very few of the extents of XYZ have been completely emptied. Instead it could be that many extents are holding only a few records resulting in inefficient space usage. In this case SQL Server can not reclaim the extents as free space automatically. A reorganization of table XYZ will do. A reorganization will move the rows of XYZ into fewer but filled data pages.
Typical examples are if table XYZ was archived, XYZ is used to hold data temporarily, or XYZ contained data which turned out to be superfluous.
See SAP Note 159316 on "Reorganizing tables on SQL Server SQL 2000/2005".