Solved

sql report servers lock

Posted on 2013-01-14
6
1,363 Views
Last Modified: 2013-01-31
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
Comment
Question by:dedri
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 11

Expert Comment

by:SThaya
Comment Utility
in case you are accessing remote server tables or SP's use (nolock) inside your code ...
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:dedri
Comment Utility
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
 
LVL 11

Expert Comment

by:SThaya
Comment Utility
0
 

Author Comment

by:dedri
Comment Utility
SThaya, I've already checked them, but there aren't any solution provided in this forums.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now