Link to home
Start Free TrialLog in
Avatar of janhoedt
janhoedt

asked on

SQL reporting services (SSRS) down

Hi,

I have an SCCM 2012 environment and am running SQL reporting services on the SQL-server (lab environment). Now I notice only when I try to run reports, the service is down. It restarts, but I don't find the reason why it stops. Please advise.

User generated image
I did found an error 10311 which says: AppDomain 618 (CM_BOH.dbo[runtime].754) is marked for unload due to memory pressure.

but not sure if that is the root cause. Server is an W2008 R2 on VMWare, 4GB of ram (fix size set in SQL).

Please advise howto troubleshoot + howto be alerted when it is down (can it be done within sql?).
J.
Avatar of TempDBA
TempDBA
Flag of India image

This is memory issue. Set the startup parameter with -g 512. This will give sufficient memory to the OS. Is something heavy running on the server?
You will also like to add max and min memory setting.
http://www.myitforum.com/forums/quotAppDomain-ampltApplicationDomainampgt-is-marked-for-unload-due-to-memory-pressurequot-m200128.aspx

For getting an alert, you need to diagnize the error logs more. Check if any alertId was generated. If yes, you can create and alert in sql server agent with the alertId.
Avatar of janhoedt
janhoedt

ASKER

Thanks, I ll have a look asap!
Please specify what you mean with: "Set the startup parameter with -g 512", where should I configure, what does it do and how will it impact my system?


Note: actual memory settings sql:
User generated image
Note: I have databases which are not in use anymore. I just shutted them down, something else I should do?
I'd like to optimize performance since the sql-server is under heavy load lately.
It is a startup parameter. To configure a startup parameter:-

In SQL Server Configuration Manager, click SQL Server Services.

In the right pane, right-click SQL Server (<instance_name>), and then click Properties.

On the Startup Parameters tab, in the Specify a startup parameter box, type the parameter, and then click Add.

For example, to start in single-user mode, type -m in the Specify a startup parameter box and then click Add. (When you restart SQL Server in single-user mode, stop the SQL Server Agent. Otherwise, SQL Server Agent might connect first and prevent you from connecting as a second user.)

Click OK. Restart the Database Engine.


Other database engine startup parameters are mentioned below:-
http://msdn.microsoft.com/en-us/library/ms190737.aspx

If you have many non sql server running, Here is how you can find out how much memory to leave:-
http://www.johnsansom.com/sql-server-memory-configuration-determining-memtoleave-settings/
Thanks! But please specify what is meant with: "Set the startup parameter with -g 512": what does it do and how will it impact my system?
No other services running but sql.
Ok, I checked the -g 512. My vm with SQL has 4GB, shall I only assign 512, not 1024?
Note: no other applications are running besides sql & sql reporting services.
Please also note the setting within SQL, is this correct?

 User generated image
Avatar of George Simos
SQL Server will happily eat all your memory, you should constrain it according to your load, is this a production or a lab environment?

How many clients are you serving?
Please do answer my question: I was advised to set -g 512, should I or not (or should I set 1024)?
Is the mem setting (screenshot above) correct?
Config is lab, 1 sccm mainly, then vcenter server and 2 neglectable databases (hardly used).
No, this is the server level configuration. Here set the minimum to 1024 and maximum to a value of memory you have leaving something for OS.
Coming back to the -g setting,  it was the second screen shot I was talking about:-
http://sqlbuzz.wordpress.com/tag/sql-server-startup-parameters/
Thanks.
-you mention to set 1024 as minimum, mem of server as max, wheras I read different things: setting fix to size of server (according Trainsignal.com) , 0 to 512 http://blog-en.netvnext.com/2011/11/installing-sccm-2012-rc1-primary-site.html?m=1 
really confusing, don t know which one to set
- -g still not clear, 512, 1024? no screenshot on your link
In SCCM environments with the SQL Server on the site system (the ConfigMgr Server) we usually use the following rules:

1) If the System's Memory is Max 4GB then use 50% of the Memory
2 If the System's Memory is Greater than 6GB then use 70% of the Memory

The setting above is the Max memory that SQL Server will be allowed to use (memory limit or capping)

Keep in mind that using CM on a VM with other workloads is not a good idea and you should consider leaving it and SQL Server as the only workloads (without using SQL for other Services just the Reporting Services).

All you have to do is set your SQL Server's Max Server Memory attribute to 2047MB for your situation as the rules above (rule #1 applies to your issue), this upper amount of memory will not be used at once but gradually as the system/apps demand.

ConfigMgr is a very demanding Server Application, always consider placing it by itself with it's DB without any other roles/workloads on it, especially if you serve many clients with it  you will eventually face Back-pressure events and backlogging when the hardware reaches it's limits.

Take a look at a picture of my Lab's SQL Server settings:
User generated image
Thanks. But again, please advise how to set my mem settings: 0 to 2048, 0 to ... and no -g startup then?
ASKER CERTIFIED SOLUTION
Avatar of George Simos
George Simos
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks, that s loud and clear!
Also check this about SSRS from the official docs please: Configure Available Memory for Report Server Applications because it makes clear the pressure errors.
Any news?
Better performance already!
Performance of underlying ESX (only vm running on this ESX). CPU usage went down since I changed the mem-value as suggested!
Don't fully understand though that less cpu is used when memory is changed. Thought it would be 2 sperate processes.

User generated image
Oh ... reporting services was down again ....
When starting reporting services:

System.ServiceProcess.TimeoutException: Time out has expired and the operation has not been completed.
   at System.ServiceProcess.ServiceController.WaitForStatus(ServiceControllerStatus desiredStatus, TimeSpan timeout)
   at ReportServicesConfigUI.Panels.ServerInformationPanel.StartStopServiceTask(Boolean start)

but start is status "pending" ...

After a while it is started and cpu increases, but not dramatically:
User generated image

Note: vm is on ESX which 1 CPU/2 cores, 1 assigned 1 CPU & 2 cores to vm (first it was just 1 CPU, 1 core). Might have to play with this too. ESX has 8 GB but only assigned 4GB to vm in order to be able to run other vm's when necessary (only got 3 hosts of which 1 I prefer to shut down if possible).
The SCCM Database is on another host?
Yes
Thanks for accepting my answer, your problem's solution lies in the way you will correctly setup your environment, in your case I would move the SCCM DB on the Site Server and offload the reporting role to another server along with the SSRS.
All these only after upgrading the VM Host and the VM Guest with more memory and faster disks for better I/O.
Matt Willis gave you some good points in your other question, because SSDs are expensive you could also setup a Raid 10 volume that will hold your VMs or the VM Data and literally boost the performance on the VM Guests.
Hope you sort it out soon!