June 20, 2017 at 3:16 pm
Does SSRS keep track of reporting running queues?
If it does, where I can find it?
Thanks
June 20, 2017 at 3:54 pm
I don't know if SSRS keeps track of them specifically, but anything with an open session should be visible in sys.dm_exec_sessions.
June 20, 2017 at 4:04 pm
sqlfriends - Tuesday, June 20, 2017 3:16 PMDoes SSRS keep track of reporting running queues?If it does, where I can find it?
Thanks
The runningjobs table in the ReportServer database is as close to a queue as you will probably find for SSRS.
Sue
June 20, 2017 at 4:25 pm
Thanks, We have another enterprise software that has some system reports that the application has report queues , you can delete a job in the queue or restart the queue.We also add some customized SSRS reports in the site, but it won't be in the queue.I try to find out if in SSRS we can do the same thing, like remove some long queued job, but I cannot find it.And I tried the Runningjobs table, I guess it will only show up if the job is running, but not queued jobs.Does that mean SSRS do not have queued information of which reports are queued, who started the reports, and status?Thanks
June 21, 2017 at 2:37 pm
sqlfriends - Tuesday, June 20, 2017 4:25 PMThanks, We have another enterprise software powerschool that has some system reports that the application has report queues , you can delete a job in the queue or restart the queue.
We also add some customized SSRS reports in the site, but it won't be in the queue.I try to find out if in SSRS we can do the same thing, like remove some long queued job, but I cannot find it.
And I tried the Runningjobs table, I guess it will only show up if the job is running, but not queued jobs.
Does that mean SSRS do not have queued information of which reports are queued, who started the reports, and status?
Thanks
You can cancel jobs but I would guess SSRS doesn't work like Powerschool. Unfortunately, I don't know anything about Powerschool so can't give you any equivalent ways in SSRS.
You can connect to Reporting Services in SSMS and then check the jobs from there. That might work for you - see the following for more details:
Manage a Running Process
You can can also change the time interval that the running jobs table gets updated. The table isn't instantly updated - it's updated every 60 seconds. You would need to change the times in the RsReportServer.config file. The RunningRequestsDbCycle and RunningRequestsAge sections determine the frequency of the updates.
Changing those might get you closer to what you are asking for as well. You can find more information here:
RsReportServer.config Configuration File
There is another option but this forum software is causing this post to take too much time.
This is the like to an example using the API - can't use the link format for it for whatever reason:
https://gugiaji.wordpress.com/2012/10/03/ssrs-2008-r2-programmatic-list-running-reportsjobs-cancel-examples/
Sue
June 21, 2017 at 3:17 pm
I guess the job folder only has jobs currently running, it will not have jobs queued, correct?
Thanks much
June 21, 2017 at 3:43 pm
sqlfriends - Wednesday, June 21, 2017 3:17 PMI guess the job folder only has jobs currently running, it will not have jobs queued, correct?Thanks much
SSRS doesn't operate on a queueing system. You can have all of the reports trigger at the same time and it will process them and send them all out at once.
We have multiple reports that are scheduled for the same time and it really is just whichever report gets the data the fastest will send out first. If 2 reports get their data at the same time, they both get sent at the same time.
It DOES allow for scheduling jobs which will run at a scheduled time. This is done in the SQL Server Agent->Jobs folder. That is a list of jobs that are scheduled to run.
If you want to see the names and schedules and map a SQL job to a report name, this script will do that for you:USE [ReportServer]
GO
--View all of the SSRS jobs
SELECT
S.ScheduleID AS SQLAgent_Job_Name
,SUB.Description AS [Description]
,C.Name AS [Report Name]
,C.Path AS [Report Path]
,.[LastStatus] AS [Last Status]
,.[StartDate] AS [Subscription_Start_Date]
,.[EndDate] AS [Subscription_End_Date]
,.[LastRunTime] AS [Last Run Time],
next_run_date,
next_run_time
FROM ReportSchedule RS
INNER JOIN Schedule S ON (RS.ScheduleID = S.ScheduleID)
INNER JOIN Subscriptions SUB ON (RS.SubscriptionID = SUB.SubscriptionID)
INNER JOIN [Catalog] C ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)
JOIN [msdb].[dbo].[sysjobs] ON [sysjobs].name = CAST(.[ScheduleID] AS VARCHAR(255))
JOIN [msdb].[dbo].[sysjobschedules] ON [sysjobschedules].[job_id] = [sysjobs].[job_id]
ORDER BY .[LastStatus]
the next_run_date is easy to read, but next_run_time is a bit confusing and I'm still working my way around that one. I think it is in the format of hhmmss.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 21, 2017 at 3:45 pm
sqlfriends - Wednesday, June 21, 2017 3:17 PMI guess the job folder only has jobs currently running, it will not have jobs queued, correct?Thanks much
Not sure what you are looking for but if I select a report in Report Manager, it generally starts processing at that point. It sounds like you are used to a system where every report request goes into a queue of some sort before anything happens. The one I have seen most like that is concurrent manager in Oracle apps so I can only compare it to that. SSRS doesn't work like that.
There is no such thing as a pure job queue for every report request - it just works differently. Background tasks are queued to an extent with the events and notification tables and they will be handled differently based up on the number of CPUs, if any changes have been made to the config files and current load on reporting services.
Requests in running jobs table can have a status of new and then it move to running but you usually only catch those in new if you have a decent number of concurrent requests.
There are interactive requests and background tasks and requests. How those work at any given time can very much depend on the load on the report server at the time. You can get an idea of how many concurrent requests you have going on by analyzing the data in the ExecutionLog view.
Sue
June 21, 2017 at 5:05 pm
Thanks all. That explains it. It seems SSRS not using a queue system., not working that way, Thanks for that query too.
June 21, 2017 at 5:11 pm
Hey BMG002 -
Totally missed all of this post until now. Too broke to pay attention I guess...
If you look at the scalar function agent_datetime in msdb, you'll see how it works with dates and times. You can use that as a template to create your own better performing inline function to convert those.
Sue
June 22, 2017 at 9:14 am
Sue_H - Wednesday, June 21, 2017 5:11 PMHey BMG002 -
Totally missed all of this post until now. Too broke to pay attention I guess...
If you look at the scalar function agent_datetime in msdb, you'll see how it works with dates and times. You can use that as a template to create your own better performing inline function to convert those.Sue
Oh, cool. That is a neat little function. I had just been eyeballing it and doing guesses based on the last run time. It works for what we do.
The date and time scheuldes for us are not that important, but I include them. We mostly use that script to get the SQL Job name so we can re-run SSRS subscriptions if they fail or end users do something silly like delete the exported email/file and need it re-generated.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 22, 2017 at 10:26 am
Sue
June 22, 2017 at 10:45 am
Sue_H - Thursday, June 22, 2017 10:26 AMThat function is a cool little thing but it probably should have been written as an inline function. If it's not used or called much, it's not going to kill a server to use what's there. I've used my own and the built in one as well...just to be consistent in inconsistency.Sue
That is a mentality that I am very very slowly getting into. Where I work, we have a bad habit of storing things like that on a network share and just loading the script up whenever we need to run it. A lot of times, I find a script that ALMOST has what I want online (usually from this forum), then I tweak it to suit my needs. The one I posted above started like that, but it had a lot more joins and provided a lot of information I didn't care about so I removed a lot of it and then added a few extra things on my own.
One REALLY bad habit I have is finding a script online, ensure it has no UPDATE, DELETE, INSERT, or DROP commands, then reading through it multiple times to figure out what it does... and then adding a ",*" to the end of the SELECT to see what other info I can pull that may be useful.
But it does help me learn. Most (if not all) of my SQL knowledge has been learned from either PASS Summit, SQL Saturdays, my local PASS chapter, or trial and error. With trial and error being the biggest one. My best friend is "ROLLBACK TRANSACTION"... my worst enemy is when I am trying to send a message on service broker and for whatever reason, SQL ignores the "COMMIT TRANSACTION" (think it is an "out of memory" error but for whatever reason does not display it).
But I really like SSRS. We have multiple reporting platforms where I work, and for the most part, I like SSRS the best. They all have different benefits. One does "live excel" documents which are neat but the system is VERY heavily java based and does get stuck in a garbage collection loop at times. Another is very good at making reports that are very pretty and drill-down-able in a simple, easy to use interface, but has the problem that the data needs to be imported to its own magic format on a schedule, so you get no real-time data from it. The only thing that sucks about SSRS is the licensing... I think it is silly that if you install SSRS on a server that doesn't have a licensed SQL instance on it, you need a separate license for it. SSRS is not all that useful without a SQL database to talk to (from my understanding).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 22, 2017 at 11:16 am
bmg002 - Thursday, June 22, 2017 10:45 AMSue_H - Thursday, June 22, 2017 10:26 AMThat function is a cool little thing but it probably should have been written as an inline function. If it's not used or called much, it's not going to kill a server to use what's there. I've used my own and the built in one as well...just to be consistent in inconsistency.Sue
That is a mentality that I am very very slowly getting into. Where I work, we have a bad habit of storing things like that on a network share and just loading the script up whenever we need to run it. A lot of times, I find a script that ALMOST has what I want online (usually from this forum), then I tweak it to suit my needs. The one I posted above started like that, but it had a lot more joins and provided a lot of information I didn't care about so I removed a lot of it and then added a few extra things on my own.
One REALLY bad habit I have is finding a script online, ensure it has no UPDATE, DELETE, INSERT, or DROP commands, then reading through it multiple times to figure out what it does... and then adding a ",*" to the end of the SELECT to see what other info I can pull that may be useful.
But it does help me learn. Most (if not all) of my SQL knowledge has been learned from either PASS Summit, SQL Saturdays, my local PASS chapter, or trial and error. With trial and error being the biggest one. My best friend is "ROLLBACK TRANSACTION"... my worst enemy is when I am trying to send a message on service broker and for whatever reason, SQL ignores the "COMMIT TRANSACTION" (think it is an "out of memory" error but for whatever reason does not display it).But I really like SSRS. We have multiple reporting platforms where I work, and for the most part, I like SSRS the best. They all have different benefits. One does "live excel" documents which are neat but the system is VERY heavily java based and does get stuck in a garbage collection loop at times. Another is very good at making reports that are very pretty and drill-down-able in a simple, easy to use interface, but has the problem that the data needs to be imported to its own magic format on a schedule, so you get no real-time data from it. The only thing that sucks about SSRS is the licensing... I think it is silly that if you install SSRS on a server that doesn't have a licensed SQL instance on it, you need a separate license for it. SSRS is not all that useful without a SQL database to talk to (from my understanding).
Those really aren't bad habits - I do the adding a * in the scripts I find pretty often too. And adding, removing tables...just playing around to get it "right".
I think of us learn a lot from the whole trial and error process - and researching throughout. You have the advantage of having done other IT work so I would guess you do a lot of things instinctively - like checking any and all logs.
But SSRS I've always liked - started with it when it first came out with whatever service pack on 2000 and have used it pretty consistently since then. It was so much better than the alternatives then and it's still cheaper than a lot of other options today. It has it's quirks but so does every other option. I think the licensing of it ended up the way it is more so to address scale out deployments. In the early days, most people just installed it on an existing instance or used it with an instance used for other things so cost wasn't much of a factor. It's come a long way since then but it seems most changes were really more about adding things to improve presentation (graphs, charts, etc). Much of the basics are still the same.
Sue
June 22, 2017 at 11:31 am
Sue_H - Thursday, June 22, 2017 11:16 AMbmg002 - Thursday, June 22, 2017 10:45 AMSue_H - Thursday, June 22, 2017 10:26 AMThat function is a cool little thing but it probably should have been written as an inline function. If it's not used or called much, it's not going to kill a server to use what's there. I've used my own and the built in one as well...just to be consistent in inconsistency.Sue
That is a mentality that I am very very slowly getting into. Where I work, we have a bad habit of storing things like that on a network share and just loading the script up whenever we need to run it. A lot of times, I find a script that ALMOST has what I want online (usually from this forum), then I tweak it to suit my needs. The one I posted above started like that, but it had a lot more joins and provided a lot of information I didn't care about so I removed a lot of it and then added a few extra things on my own.
One REALLY bad habit I have is finding a script online, ensure it has no UPDATE, DELETE, INSERT, or DROP commands, then reading through it multiple times to figure out what it does... and then adding a ",*" to the end of the SELECT to see what other info I can pull that may be useful.
But it does help me learn. Most (if not all) of my SQL knowledge has been learned from either PASS Summit, SQL Saturdays, my local PASS chapter, or trial and error. With trial and error being the biggest one. My best friend is "ROLLBACK TRANSACTION"... my worst enemy is when I am trying to send a message on service broker and for whatever reason, SQL ignores the "COMMIT TRANSACTION" (think it is an "out of memory" error but for whatever reason does not display it).But I really like SSRS. We have multiple reporting platforms where I work, and for the most part, I like SSRS the best. They all have different benefits. One does "live excel" documents which are neat but the system is VERY heavily java based and does get stuck in a garbage collection loop at times. Another is very good at making reports that are very pretty and drill-down-able in a simple, easy to use interface, but has the problem that the data needs to be imported to its own magic format on a schedule, so you get no real-time data from it. The only thing that sucks about SSRS is the licensing... I think it is silly that if you install SSRS on a server that doesn't have a licensed SQL instance on it, you need a separate license for it. SSRS is not all that useful without a SQL database to talk to (from my understanding).
Those really aren't bad habits - I do the adding a * in the scripts I find pretty often too. And adding, removing tables...just playing around to get it "right".
I think of us learn a lot from the whole trial and error process - and researching throughout. You have the advantage of having done other IT work so I would guess you do a lot of things instinctively - like checking any and all logs.
But SSRS I've always liked - started with it when it first came out with whatever service pack on 2000 and have used it pretty consistently since then. It was so much better than the alternatives then and it's still cheaper than a lot of other options today. It has it's quirks but so does every other option. I think the licensing of it ended up the way it is more so to address scale out deployments. In the early days, most people just installed it on an existing instance or used it with an instance used for other things so cost wasn't much of a factor. It's come a long way since then but it seems most changes were really more about adding things to improve presentation (graphs, charts, etc). Much of the basics are still the same.Sue
OH I agree about SSRS. My first peek at it was with in SQL 2008 R2. And it was interesting, but had some challenges. We had it running on its own server for quite a while where I work until we had an MS audit and realized we couldn't do that. So now it resides with a lot of our low impact SQL instances. This works OK for what we do, but it was nice having its own machine for that and I'm hoping we can get back to that one day.
And I do have a pretty good IT background, so when something goes wrong, the first thing I look at is the logs. Windows, SQL, application, SQL Montior... any logs I can find. And if I don't have access to all of the logs, I'll ask the people who manage those systems to get me the logs or get me access to the logs.
One of the biggest "quriks" that we saw with SSRS (especially in 2012) was that it didn't handle non-IE browsers all that well. Chrome + SSRS 2012 gave blank reports. I don't mean "no results", it would be blank. No header, no footer, no data. There was fixes you could do to make it work, which we did implement, but managing reports in Chrome in 2012 did not work well. Now we have 2016 for SSRS and it looks and feels VERY similar to 2012 BUT works better with Chrome. The company has a "we support IE only" policy as we have a lot of other things that do not play nice with others.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply