What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI
COURSE of the MONTH
10 days, left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
sql report servers lock
Posted on 2013-01-14
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?
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?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
- +1
6 Comments
Active today
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.
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.
Active today
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.>
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.>
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.
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.
Featured Post
What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses
Course of the Month10 days, left to enroll
762 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.