SQL Reporting Services Error- Maximum request length exceeded

When trying to deploy a large report to your SSRS server, you may run into an error like this:

Error 2 There was an exception running the extensions specified in the config file. ---> Maximum request length exceeded.

You could, quite honestly, run into this error in a lot of situations involving a web app, but we're talking specifically about SSRS in this post.

The basic problem here, is that your posting an amount of data to a web app larger than it is configured to accept. Hence, it is throwing an error, and simply saying "no!"

It's an easy fix though! You've got to tweak the web.config for the web app, which in the case of reporting server, is usually somewhere like this:

C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer

Find the web.config file for your reporting services instance, open it up, and track down the line that looks something like this



Now just add a max request length attribute in there to fix the problem, adjust your size as needed. This is 5meg.



And now you'll need to restart IIS. start->run->"iisreset"



Good luck!

Disable/Enable all scheduled jobs in SQL Server 2005

Declare @job_id Nvarchar(200)
DECLARE @String1 NVARCHAR(1000)
DECLARE @jobs TABLE (job_id NVARCHAR(200))
SET NOCOUNT ON
-- job id
INSERT @jobs
select job_id from msdb..sysjobs

WHILE EXISTS (SELECT TOP 1 job_id FROM @jobs WHERE job_id IS NOT NULL)
BEGIN
SELECT TOP 1 @job_id = job_id
FROM @jobs
SET @String1 ='EXEC msdb..sp_update_job @job_id = ''' +@job_id + ''', @enabled = 0 '
print @String1
EXECUTE sp_executesql @String1
DELETE FROM @jobs WHERE job_id = @job_id
END


Declare @job_id Nvarchar(200)
DECLARE @String1 NVARCHAR(1000)
DECLARE @jobs TABLE (job_id NVARCHAR(200))
SET NOCOUNT ON
-- job id
INSERT @jobs
select job_id from msdb..sysjobs

WHILE EXISTS (SELECT TOP 1 job_id FROM @jobs WHERE job_id IS NOT NULL)
BEGIN
SELECT TOP 1 @job_id = job_id
FROM @jobs
SET @String1 ='EXEC msdb..sp_update_job @job_id = ''' +@job_id + ''', @enabled = 1 '
print @String1
EXECUTE sp_executesql @String1
DELETE FROM @jobs WHERE job_id = @job_id
END

Package0

The asynchronous_file_target holds the raw format Event data in a proprietary binary file format that persists beyond server restarts and can be provided to another person via ftp or email for remote disconnected analysis of the events.

The bucketizer performs grouping of Events as they are processed by the target into buckets based on the Event data and the Targets configuration. There are two bucketizer targets in Extended Events; a synchronous_bucketizer and an asynchronous_bucketizer.

Dirty Pages

Clean pages can be fl ushed from cache using dbcc dropcleanbuffers, which can be handy when you’re troubleshooting development and test environments because it forces subsequent reads to be fulfi lled from disk, rather than cache, but doesn’t touch any dirty pages.

You can use the following query, which is based on the sys.dm_os_buffer_descriptors DMV, to see how many dirty pages exist in each database:

SELECT db_name(database_id) AS 'Database',count(page_id) AS 'Dirty Pages'
FROM sys.dm_os_buffer_descriptors
WHERE is_modified =1
GROUP BY db_name(database_id)
ORDER BY count(page_id) DESC

Buffer Pool

The buffer pool contains and manages SQL Server’s data cache. Information on its contents can be found in the sys.dm_os_buffer_descriptors DMV.

For example, the following query will return the amount of data cache usage in MB per database:

SELECT count(*)*8/1024 AS 'Cached Size (MB)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 'Cached Size (MB)' DESC



Monitoring SQL Server’s buffer pool is a great way to look out for memory pressure


MSSQL$:Memory Manager\Total Server Memory (KB): This indicates the current
size of the buffer pool.
MSSQL$:Memory Manager\Target Server Memory (KB): This indicates the ideal
size for the buffer pool.
MSSQL$:Buffer Manager\Page Life Expectancy: This is the amount of time, in
seconds, that SQL Server expects a page that has been loaded into the buffer pool to remain in cache.

open the firewall port for SQL Server on Windows Server 2008/R2

How to: Configure a Windows Firewall for Integration Services

http://msdn.microsoft.com/en-us/library/ms141198.aspx

How do I open the firewall port for SQL Server on Windows Server 2008?

http://support.microsoft.com/kb/968872

SharePoint 2010 All-in-One For Dummies

SharePoint 2010 All-in-One For Dummies