KB Article #101849
Troubleshooting SQL Server in EMF.
Summary:
This technote discusses troubleshooting the SQL Server running under EMF, for common problems EMF users encounter. Not all points below discuss problem symptoms, because the symptoms vary. However, all the points indicate compliance requirements. In some cases, the reader is directed to other technotes for more information.
Detailed Information:
1) Required for EMF:
- If you are running SQL on NT 4, you must use SQL 7, not SQL 2000.
- If you are running SQL on Windows 2000, you must use SQL 2000, not SQL 7.
- As of EMF 5.5, NT 4 is no longer supported, and you must use Windows 2000/SQL 2000 for EMF 5.5.x and 5.6.x.
- As of EMF 6.0, Windows 2003 must be used.
2) Always check the Tumbleweed Compatibility Matrix on the Support Portal to ensure that your are on the correct SQL service pack.
3) You must ensure that the account under which the SQL server services run has local administrator privileges on the SQL machine. The local Windows system account can be used in a single-machine EMF installation.
4) You must always ensure that the MSSQLSERVER and sqlserveragent services are running. For more information on the types of errors resulting from noncompliance with this requirement, please see related article Cannot Logon with EMF Web Administrator on the right.
5) The SQL server must be run in mixed authentication mode (not "Windows only" mode). Please also see related article Cannot Logon with EMF Web Administrator on the right.
6) Always have adequate disk space on the partition(s) that contain the EMFMail database data files and the transaction log. We highly recommend creating Backup Maintenance Plans for both the data files and transaction log, to recover unused space. Please see related article EMF SQL Backup and Recovery on the right.
Some sites do not require SQL backups, because they have determined it is OK for them to lose existing database data if their SQL server fails. Note that these sites may still run into space issues on the SQL partition(s). Below are three SQL statements that these sites can use to manually reclaim unused space, and reduce the size of the database. Note that using a formal Backup Maintenance Plan is always preferred, but these sites can use these statements:
-- Backup EMFMail DB to DISK ('X' must be a local drive)
BACKUP DATABASE EMFMail
TO DISK = 'x:\EMFBackups\EMFMail_DB_Backup.dat'
WITH INIT
-- Truncate Transaction Log (Full Recovery Model Only)
BACKUP LOG EMFMail WITH NO_LOG
-- Shrink Database & Transaction Log
DBCC SHRINKDATABASE (EMFMail, 50)
Before running any of these statements, the user should stop all the EMF services. These statements can be run in SQL Query Analyzer. The first statement is a full database backup. The second statement will release all internal space inside the transaction log file; the second statement is normally run immediately after the first statement, because after a full database backup, the transactions in the transaction log are unnecessary, and can be deleted. Note that the second statement does NOT return unused transaction log file space to the Windows operating system; this is accomplished by the third statement. Note also that the second statement is used only in the Full Recovery Model. The Simple Model manages the space in the Transaction Log differently.
Note that these statements may each take several minutes to run to completion. After running them, be sure to restart the EMF services.
The EMF Best Practices Guide discusses high performance and redundancy options for allocating and maintaining the EMF SQL database.
7) If you have chosen the option to have a fixed max size for your EMFMail database data files and transaction log (i.e., you are not using Autogrow with Unrestricted file growth), EMF will stop accepting inbound connections, and log the event at the major level:
Event ID: 1072. Error: EMF database has limited space available.
Inbound connections will be rejected until more space becomes available.
when any of the EMFMail data file groups (except MMSPrimary), including the Transaction Log, becomes 80% full. Acceptance resumes at 75%.
Previous to EMF 5.6, only the following file groups were checked:
- MMSBodyChunks file group
- MMSMessageData file group
- MMSEventData file group
- Transaction log
- apply step 6 above
- check if you are logging the EMF relay and/or policy engine at Trace or Debug level, and change to Normal level
- delete some events from the EMF event log
- reduce the EMF event log retention period
- the queue sizes: retaining Body Chunks of large messages should
be kept in check - increase the max size of the relevant data file; to do so, open Enterprise Manager > your-server > EMFMail properties > Data Files tab, highlight the relevant data file, and increase the value in the Space Allocated column. Then move the cursor out if the column. It may take a minute or two to increase the size. Note that this increase is permanent; you cannot decrease data file size using this technique.
9) SQL uses your server's RAM memory very aggressively. Make sure that you do not have SQL set to use 100% of RAM, especially if there are one or more EMF services installed on the SQL server machine. Having this setting at or near 100% of available RAM may cause mail flow to stop, since the EMF services may be prevented from continuing due to lack of RAM. In SQL 2000, this RAM setting is made in Enterprise Manager on the SQL server machine's Properties' Memory tab. We recommend setting the SQL RAM usage to 50% of available RAM.
10) If you are using SQL replication, whenever you upgrade EMF to a new version or patch level, you must ensure that SQL replication is disabled. The EMF Best Practices Guide discusses disabling replication, and then re-enabling it after upgrading EMF. See also related article Troubleshooting SQL Replication on the right.
11) If you have been using SQL Transactional replication prior to EMF 5.0 patch 4 or EMF 5.5, you will have to take corrective action on your database before upgrading to patch 4, 5.5, or later. Please see related article Upgrade fails with could not drop constraint error on the right.
12) You cannot change the name of the SQL Server machine without performing one of the following:
- reinstalling SQL Server (must be done for SQL Server 7, optional for SQL 2000)
- for SQL 2000 only: you can connect to SQL Server using the new computer name after you have restarted the server; however, to correct the sysservers system table, you should manually run these procedures in SQL Query Analyzer against the master database:
use master
go
sp_dropserver OLD_SERVER_NAME
go
sp_addserver NEW_SERVER_NAME, local
go
Restart SQL Server, and verify the change by the following two commands:
use master
go
sp_helpserver
go
select @@servername
go
Additional Information:
Please also see:
- related article Advanced SQL troubleshooting
- related article 17xx errors in the EMF Event Log
- related article Clearing the EMFMail database transaction log (Full Recovery Model)
- related article Clearing the EMFMail database transaction log (Simple Recovery Model)
- the SQL Server Online Books for more information