This is hillarious!
This is hillarious!
When running what ends up as being a very large report or data set using SMS 2003 Reporting, it may seem as if there are limitations as to the amount of rows or data being returned. This is often described as there being only a partial set of data returned, and has to do with some default IIS limits. Fortunately, these limits can be changed.
To get a better idea if whether you are running up against some default configurations of SMS and IIS limiting report returns to about 10,000 rows of data, or possibly 4mb of information, you can disable friendly HTTP messages in the Internet Options on the client accessing the Web Report.
To disable friendly messages :
Response object error ‘ASP 0251 : 80004005’
Response Buffer Limit Exceeded
/smsreporting_mat/Report.asp, line 0
Execution of the ASP page caused the Response Buffer to exceed its configured limit.
Should you see this error what is likely happening is that the amount of data being returned exceeds the current IIS configuration which defaults to 10,000 rows. Fortunately this can be remedied.
The solutions to this problem can be found on the SMS 2003 FAQ at this location:
What is not always clear from the FAQ or overlooked is that there are multiple steps which are all often needed to get a complete solution. I have provided a quick and dirty hit list here to get you on your feet should you run into this limit:
Before beginning I’d like to state that the following steps are not intended to be an exhaustive resource on making modifications to your SMS registry or IIS Metabase entries. In fact, before you begin I recommend reviewing the FAQ and its referenced documents to ensure you are comfortable with the changes you are about to make. Back up the IIS metabase and the appropriate registry keys before making any of these changes. Mucking about in the Metabase and Registry with a vague idea as to results of actions taken, or where inattention to details occur could render your Site, Component Server, or IIS installation worse off than having SMS Reports failing to return all of the data you expect. Sufficient warning? I hope so.
Following these steps should allow you to increase your Reporting Servers ability to serve up those extra large data sets for reports which have previously been problematic.
One thing to keep in mind is that defaults are set for a reason. Any time values are adjusted from those defaults you’ll need to monitor your systems and actions for performance. When in doubt, back it up before you act and keep a log of the changes you make.
I just downloaded and installed Windows Live writer beta from http://windowslivewriter.spaces.live.com/ and wanted to take it for a quick spin. Very simple and easy to use interface. I am quite impressed thus far.
The sizing of SQL DBs for SMS has always be a much debated topic for many years. In SMS1.2 and 2.0 days we generally recommended 1-200kb per client plus 50 Mb for a database size.
DB Sizing changes for SMS 2003.
1.One problem with these recommendations is that today with the new feature sets of SMS 2003 we have to consider no only SMS but also its feature packs such as Patch Management and the upcoming OS Deployment and Device Management. Device management has the potential to double the number of “managed” machines in some organizations.
2.New features such as file path reporting for software inventory, a more useable Software Metering feature and the use of the DB to store client software distribution have changed the landscape considerably.
3. Customer extensions to hardware def.mofs greatly exceeded our expectations.
During the SMS2003 development cycle we closing monitored Microsoft’s own deployment as well as that of our beta enterprise customers.
The conclusion of which is that most customers can utilize a sizing estimate of 1 Mb per client for the SMS DB for most standard deployments of SMS 2003. For customers who feel they will really push one of the features above, a conservative estimate would be to use 2Mb per client.
Detailed Analysis of DB Usage.
In the databases I have examined, software inventory and statue messages are the biggest DB users. For SMS 2003 we introduced the tracking for file path for each executable we report. This is fine for most environments. However when there is more than one location of a client for a particular file, instead of reporting this as two instances counts for the same row in the software inventory table, we now report it as 2 separate rows. This is clearly going to make a big difference from SMS2.0.
In theory status messages for SMS2003 should be more performant from SMS2.0 as we eliminated a number of “less than useful” (PC statement) status messages. However on the flip side, software distribution and client reliability percentages have greatly increased, result in more status messages being delivered.
In SMS2003, as in SMS2.0, customer expansion of the hardware inventory definition file (def.mof) can also play a defining role in DB sizing. Many customers of SMS2.0 extended the def.mof to gather any number of company specific and non-company specific items.
However the mistake many made was in not considering the storage needs of these new items and designing the extensions to utilize multiple tables and indexes. By failing to do this, the performance of hardware inventory processing suffered at the hands of trying to update the same single DB table for multiple classes/categories. The larger the table the slower adds/deletes and especially inserts can be.
Tempdb and SMS Log DBs.
The tempdb is used by SMS on a regular basis. The area that normally causes problems are when a Query or Collection (using a query) is badly formed, resulting in the need to create a very large temp table to produce the results. In some cases I have seen these temp tables/DBs group to over 100% of the associated SMS DB (sometimes over 36Gb). Be very careful with queries.
The tempdb is also critical during upgrades. Temporarily increasing the size of the temp DB to over 50% of the SMS DB is sometimes necessary under these conditions. Please ensure you run setup.exe with the /testdbupgrade switch at least once before any upgrades take place.
The SMS transaction log is used to record all transactions that are written to the SMS DB. By default SMS’s backup regime is set to “simple” (see SLQ documentation for further information). Essentially what this means is that the SMS log DB is not going to be used for restores. If the machine crashes you will be able to restore the site server to the last SMS backup (you will lose all processing since then). This means that SQL will clear parts of the SMS log DB as required.
Generally, the common recommendation configuring the SMS log DB and Tempdb to be 20% of the SMS DB size is probably a very good place to start.
The exception to this is when SQL replication is used for MPs. With SQL Replication being configured on the SMS site server the SMS Log DB will not be cleared until replication is successful. Careful monitoring of replication is therefore required (through SQL Enterprise mgr) to ensure you don’t cause the SMS log DB to run out of space. If this occurs, data loss is possible.
For both SMS DBs I generally like to have “autogrow” enabled and set to grow by a percentage instead of a fixed amount of MB.
Your own experiences and careful monitoring (mainly using SQL Enterprise Manager and SQL Profiler) will allow you to determine what the best configuration is for your environment. During deployment watch the size of the DBs grow as the number of clients installed increases.
As far as disk configurations the greater the separate of these DBs from each other the better. Three separate disk arrays is obviously the best, but not always possible. If you expect large volumes of processing to occur, separation of the SMS DB and the SMS log DB will yield good results. However if a large amount of reporting is likely to occur, then separation of the tempdb from other DBs might be important. Once again it all depends. My preference would be to maximize the separation of the SMS DBs, especially is SQL replication is enabled.
With disk hardware being as cheap as it is these days, there really is no excuse not to over spec the size of the drives. As far as which RAID configuration to use, well that depends so much on the environment, in most cases RAID 10 is preferred, then RAID 1 (assuming using 2 channels) or RAID 5. But this also depends on SCSI channels available and card memory, as well as the enterprise environment (size etc) and requirements. I am not going to recommend any solutions here, as the range of options are numerous and always hotly debated.
For example separate RAID 10 array’s for each DB is very, very expensive and probably overkill. I would however consider it a very good solution for the SMS DB and possibly use RAID 1 for the SMS log db and tempdb.
Great post from: http://blogs.msdn.com/rslaten/default.aspx
SMS 2003 has many different ways to enable additional logging in the product. You should only enable additional logging if troubleshooting a specific issue. Once the issue is resolved disable the logging. Also note, in most cases when logging settings are modified the associated service (smsexec, ccmexec, etc…) needs to be restarted.
Default Logging (KB241001)
Where the SMS Executive service is installed there should be an HKLM\Software\Microsoft\SMS key. Under this key there is a “Tracing” key with different components listed below which contain logging settings. These are enabled by default and the size for each log can be configured here. This can also be configured in the UI by using SMS Service Manager.
Software Metering Processor Logging
Additional logging can be added for the SMS_SOFTWARE_METERING_PROCESSOR component (swmproc.log). You can enable this under HKLM\Software\Microsoft\SMS\Components\SMS_SOFTWARE_METERING_PROCESSOR by setting the “Verbose Logging” value to 1.
Advanced Client/Management Point Logging
The AC/MP logs are very useful but there is additional logging that can be enabled. It is called verbose and debug logging. Go to the section titled “How do I enable DEBUG/VERBOSE logging on the Advanced Client?”. This logging simply adds additional entries to the existing AC/MP logs. In addition to this also do the following:
a) Go to the key HKLM\Software\Microsoft\CCM\Logging\DebugLogging
b) Create a new VALUE of type REG_SZ named “Enabled”
c) In the “Enabled” value the data should be “True”
NAL Logging (KB243385)
This logging is only useful on the Legacy client (when communicating with a CAP) or on the site server when communicating with a site system (like a DP). This setting does not create a new log, instead it adds additional entries to the component that is using NAL.
Wuser32 Logging (Legacy Remote Control) (KB195859)
This can be enabled on the client side to assist in troubleshooting remote control issues.
Legacy Client ODP/APM Logging (KB327999)
In SMS 2.0 SP5 some logging was taken out of the Legacy client to make some of the logs easier to read. This change was migrated to the SMS 2003 Legacy client.
Legacy Client Backup Logs
Sometimes when installing the legacy client the install may fail, but not get far enough to create the ms\sms\logs directory structure so you don’t have any record of why the install failed. To save these off create a folder called SMSLOGS under %TEMP%. When the install fails again the SMS logs should be in that directory.
SQL Tracing (KB828363)
SQL Tracing is a very useful troubleshooting tool. It adds additional entries to site server components that communicate with the SMS database. The entries it adds are similiar to what you would see if SQL Profiler were running. This option usually adds a lot of data to the logs so it’s best to increase the size of your SMS logs (at least for the component you are troubleshooting) so that you can make sure and capture the problem. You might use this logging if inventory wasn’t loading into the database properly.
AD Discovery Verbose Logging
You can add additional logging to the following components: Active Directory System Discovery (adsysdis.log), Active Directory System Group Discovery (adsysgrp.log), and Active Directory User Discovery (adusrdis.log). To do this set the “Verbose Logs” value to 1 for one of the following components located under HKLM\Software\Microsoft\SMS\Components:
SMS Provider Logging (KB295040)
The SMS provider runs under WMI (smsprov.dll) and is responsible for interfacing between the SMS Admin console (SDK) and the SMS SQL database. SQL Cache logging can be enabled for the provider. This is useful when troubleshooting console performance issues.
Verbose WMI Logging (KB317872)
Additional logging can be added for WMI, which is heavily used by the SMS Provider. This is useful when troubleshooting any SMS Admin Console or SDK issue.
Software Inventory Processor Verbose Logging
Thanks to Hans Ravnaas for this information!
More verbose logging can be added to sinvproc.log. This can be configured in the following registry key: HKLM\Software\Microsoft\SMS\Components\SMS_SOFTWARE_INVENTORY_PROCESSOR.
The DiskSpaceLimitMB and AgeOutLimitDays values are used to archive inventory reports as they are put into the database. The archive files are stored in SMS\inboxes\sinv.box\Temp. This can be useful when troubleshooting inventory issues at the client. Also note, you can archive the same reports, plus more, on the client.
The following reports will be saved at the client
Software Metering usage reports
To configure an SMS 2003 Advanced Client to save off a copy of the report it sends to its MP, create an empty folder under the inventory temp directory called archive_reports.sms. The following registry value will point to the inventory temp directory:HKLM\Software\Microsoft\SMS\Mobile Client\Inventory —-> “Temp Directory”.
1. To save off inventory reports on a client that is not also an MP, create the
2. To save off inventory reports on a client that is also an MP, create the
The XML file will be saved in the inventory\temp folder.
Note: Delete the archive_reports.sms folder when you are done trouble-shooting.
* One additional step is needed to create metering reports. Create a directory under ccm\metering\temp called “archive”. The reports will show up there