The ..SMS\Logs\Datalodr.log and the Windows NT Application Event Log onboth the SQL Server computer and the Systems Management Server site serverprovide information about possible causes. You should also check the SQLServer error log.
NOTE: Before you begin troubleshooting, dump the Systems Management Serverand Master databases and then back up the Systems Management Serverdirectory structure and the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMSregistry key.
The site speed (set in Site Properties on the Services tab) should never beset to Very Fast. A setting of Medium or Fast will process MIFs fasterbecause the Site Configuration Manager does not have to continually performwatchdog cycles which gives the Dataloader utility a chance to rununinterrupted for a longer period of time.
SQL Server Configuration
If Open Objects is set to less than 5,000, Dataloader can get backed up.Tempdb should be 20 to 30 percent of the largest database on the SQLServer. User connections must be set appropriately.
For additional information about important tuning parameters, see thefollowing article in the Microsoft Knowledge Base:
SMS: SQL Server Tuning Parameters for Systems Management Server
Use Performance Monitor to check the Cache Hit Ratio and make sure that thedatabase is in cache (it should be above 99 percent), not paging out. AddRAM and/or dedicated memory to SQL Server if necessary.
Database and Transaction Log Size
Make sure that the Systems Management Server database and its transactionlog are not full, and that the transaction log is 20 percent of the SystemsManagement Server database size. If the database is less than 20 percentfree, failures may occur.
Make sure that the Systems Management Server Executive Service is runningon the site server.
Check the Windows NT event log for a high number of clientresynchronizations (Event 186). If there are several resyncs, they mayoccur because duplicate SMSIDs are assigned. For additional information,please see the following articles in the Microsoft Knowledge Base:
SMS Unique ID (SMSID) Allocation138021
How Inventory Dataloader Finds Matching Computers in SMS
SQL Server Service Pack
Apply (or re-apply) the latest SQL Server Service Pack.
SQL Server Tracing
You can enable SQL statement input and output in the Dataloder.log bysetting the
registry parameter to 1. To disable SQL tracing, set the value to 0.
The Systems Management Server Executive service must be stopped andrestarted for the change to take effect.
NOTE: SQL Tracing should be disabled when you are not performingtroubleshooting steps. Leaving it enabled causes the Dataloader to slowdown and the log to fill up more quickly.
Run SQL Server DBCC CHECKDB and DBCC NEWALLOC statements on the SystemsManagement Server database in Single User Mode.
Check the ..SMS\Site.srv\Siterep.box to see if files are present. If thereare, and the site is a child primary site, ensure that site-to-sitecommunication is functioning properly. To determine this, do the following:
- Look in the appropriate sender log.
- Check the addresses.
- Check the Windows NT Application Event Log.
- Check the Outbox scheduling.
If files are present in the Siterep.box subdirectory, temporarily move themto another subdirectory to see if the MIF processing improves.
User Group MIFS
Check the SMS\Site.srv\Dataloder.box\Deltamif.col directory for UMF files.If there are several UMF files, and they are large, you probably have alarge number of users, groups, or a complicated trust matrix in yourenvironment.
You may want to change the default polling interval for user and groupenumeration using the Setgug.exe utility. For additional information aboutthe utility, please see the following article in the Microsoft KnowledgeBase:
SMS: Site Config Manager Service Locks Trusted Service Account
Check the SMS\Site.srv\Dataloder.box\Deltamif.col andSMS\Site.srv\Dataloder.box\Deltamif.col\Process directories and note theMIF size. Larger MIFs (that is, MIFs that contain custom information orextensive software inventory) will be processed more slowly.
If SQL Server devices and Systems Management Server directories are on thesame drive or the drive is mirrored with a single disk controller, checkfor I/O disk errors reported in the SQL Server log file and/or PerformanceMonitor.
If disk I/O appears to be a bottleneck, you may need to move SQL Serverdevices to another physical disk drive, install a second controller, and soforth.
Run the SMSMaint.sql script from the BackOffice Resource Kit.
Monitor the SQL Server spid performance by using SQL WHO and PerformanceMonitor to correlate this to the Dataloader thread. For additionalinformation, see the following article in the Microsoft Knowledge Base:
How to Correlate Spid, Kpid, and Thread
Decrease inventory frequency if necessary. For example, if there are sixchild sites of 2,000 to 4,000 computers, plus clients that inventory everytime they log on, and they report to a central site, an expected backlogwill occur.
Systems Management Server 1.2 Dataloader is multithreaded. If MIFs arelarge, and/or the number of clients is large, a Systems Management Serverversion 1.0 or 1.1 site may have a MIF backup and the troubleshootingsuggestions provided in this article may not help. If the site is runningSystems Management Server 1.0 or 1.1, consider upgrading to SystemsManagement Server version 1.2 (and the latest Systems Management Serverservice pack).