SMS 2003 Reporting and the 10,000 Row Limit

  • Article

Good Day!

 

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 :

  • On the Client open Internet Options and select the Advanced tab.
  • Next scroll down to “Show friendly HTTP Error Messages” and uncheck the box. These will now be disabled allowing the somewhat unfriendly error message below to be returned.

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:

https://www.microsoft.com/technet/prodtechnol/sms/sms2003/techfaq/tfaq10.mspx#EXH

 

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.

 __________________________________

  1. First you should adjust the registry value on the SMS Reporting Point server to allow for returning a larger Row Set.
    1. You can change the limit by modifying the registry on the reporting point machine.
    2. Under the HKEY_LOCAL_MACHINE\Software\Microsoft\SMS\Reporting key, add the DWORD value Rowcount and assign it the value for your desired maximum limit.
    3. The maximum row count in decimal is 32767.
    4. If you need to return more than 32,767 records, you can set the row count to 0xffffffff hexadecimal, which will return all rows. However, this significantly increases the workload on the SMS site database.

 

  1.  Next increase the IIS 6.0 Metabase’s ASPBufferingLimit as needed.
    1. The ASPBufferingLimit defaults to 4mb and is set in the IIS Metabase.
    2. To change the default, open %WindowsRoot%\System32\InetSrv\MetaBase.xml and search for “AspBufferingLimit”. Adjust the setting to allow for 1 MB per 1000 records. You can make changes to the MetaBase.xml file while IIS is running only if the edit-while-running feature is enabled. Otherwise, you must stop IIS before editing the MetaBase.xml file.
    3. For more information about writing changes to MetaBase.xml, per the FAQ, please reference “Configuring the Metabase” in the Server Administration Guide on Microsoft TechNet.

 

  1. Finally you’ll likely need to adjust the Timeout values for running ASP Scripts. This is best adjusted from within the IIS manager. The steps for doing so are provided here but have been taken from this information on the Microsoft Technet site: https://technet2.microsoft.com/WindowsServer/f/?en/Library/9dd29efe-3cfb-4f04-a6bf-ba07df5942c41033.mspx
    1. Open the IIS Manager on your Reporting Point.
    2. Double-click on the local computer and then drill down to and open the Default Web Site properties.
    3. Select the Home Directory tab and click on the Configuration button and then the Options tab.
    4. In the Application Configuration section increase the Asp Script Timeout value to 180 sec. The default is 90.

 

__________________________________

 

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.

 

Good luck!

 

Brent G. Dunsire

Supportability Program Manager

Systems Management Server and SCRM