Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2232
  • Last Modified:

sql report servers lock

I am having SQL Reporting Server 2008. Database Engine and reporting services are on different servers. Users are complaining from several days that reports are executed slowly.
When I run sp_who2 I saw  that there are blocked process and all are related to connections to ReportServer database, no blocked processes to other databases.
On of the day I killed some of the processes and it's seem that this
Any idea why this has happen and how to proceed?
0
dedri
Asked:
dedri
Advertise Here
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to tune the reporting queries eventually.
this is not a simple process, as you need to check the queries of the one that "block" the others as they run long time, and use (block) resources long time.

if you have a reporting on the OLTP database, you might consider to replicate the users database to a reporting database, so you separate the OLTP transactions completely from the reporting queries.

depending on what reporting you have, it could be a daily full backup + restore, or indeed a full transactional replication.
0
 
SThayaTechnical MAnagerCommented:
in case you are accessing remote server tables or SP's use (nolock) inside your code ...
0
 
Eugene ZCommented:
you can try to move your SSRS db to another server -- not with user
DB

also it is known issue
Blocking in SSRS ReportServer Database
http://connect.microsoft.com/SQLServer/feedback/details/698388/blocking-in-ssrs-reportserver-database



<Posted by Microsoft on 11/15/2011 at 9:18 AM

Thank you for filing this issue. The RS database architecture can lead to blocking under heavly load. Best practices for how to address this issue are documented here: http://sqlcat.com/search/searchresults.aspx?q=reporting+services&ctypes=blog

We will consider this issue for a future version of Reporting Services.>
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Take the quiz
 
dedriAuthor Commented:
EugeneZ, The problem that is described in the provided article by you  is exactly the problem that we are having: SSRS 2008 - giving blocking error on Writelocksession Stored procedure in Report server database.
Blocking in SSRS ReportServer Database
http://connect.microsoft.com/SQLServer/feedback/details/698388/blocking-in-ssrs-reportserver-database
Additionally to add is that SSRS is not heavily loaded.
Second link forwards to url which returns 66 blog posts, and not anything specific.
Do you have any specific link for this issue?
@angelIII, It is OLAP database, @SThaya, I am not accessing remote server, the problem is with locking in the reporting database as listed in the link.
0
 
SThayaTechnical MAnagerCommented:
0
 
dedriAuthor Commented:
SThaya, I've already checked them, but there aren't any solution provided in this forums.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Access It Now
Advertise Here
Tackle projects and never again get stuck behind a technical roadblock.
Join Now