SCCM Reporting – The 10000 limit and report timeouts

By default, SCCM reports will only return the first 10000 rows from a report. If, like me, you have reports which will bring back a lot more than 10000 (my record so far is 25009) then you will have to make a change or two!First thing is to add a registry key value on your reporting point, to enable 10000+ rows.

  1. To do this, open the registry editor on the reporting point computer and navigate to:
    x86 machine
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\Reporting
    x64 machine
    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\SMS\Reporting
  2. Create a new DWORD value and call it Rowcount
  3. Set the decimal value to the maximum number of rows you want to return in the SCCM report (Microsoft suggests that if you want to return all rows, no matter the number, set the hexadecimal value to 0xffffffff which is the equivalent of -1. Also, be aware that when you increase the maximum number of rows to be returned, if it is a large amount of data or if the queries were written inefficiently this may cause performance issues on the computer when the report is run) .

Now after I did the above, I still received an error when running the very large report. After a bit of digging I found this article to change ASP script timeout setting – http://technet.microsoft.com/en-ca/library/bb632464.aspx

Again, after changing this setting, I still received the error! A bit more digging and I found another article from Microsoft. This time its to change the timeout settings for the ActiveX control that the ASP script calls when retrieving SQL data for the report – http://technet.microsoft.com/en-ca/library/bb632677.aspx
So did it work this time? Erm, no. But to be fair, I am trying to view a report with over 25000 rows!

Finally, I decided to see if the problem was something else, rather than a timeout issue:

  1. I copied the query from the SCCM report and ran it directly on the SQL box (I did have to slightly edit the query to do this, by simply adding the collection ID directly rather than having the value come from a list)
  2. The query completed in around 20 seconds which made me think it wasnt a timeout issue but more the size of the data it was bringing back.
  3. To this end I changed the ASP Response Buffering Limit:
    IIS6
    Open %WindowsRoot%\System32\InetSrv\MetaBase.xml and search for “AspBufferingLimit”. 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.
    IIS7
    Here is a good blog post explaining what you need to do – http://blogs.technet.com/b/dominikheinz/archive/2011/09/08/sccm-report-cannot-be-displayed-error-500.aspx
  4. The default is set to 4194304. Wondering what to change it to? Well, I’ve read on technet it should be 1MB (1000000) for every 1000 rows but as 4194304 can handle 10000 rows, I changed it to about 12MB (12194304) and it was finally able to handle my 25000 rows report!
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: