Post reply

Disabling SSRS

  • November 16, 2016 at 2:59 am

    #315743

    Hi

    In the process of decommissioning some old servers

    I've inherited a SQL 2000 instance and I'm struggling to find any information on how to correctly disable SSRS

    Basically, I want to prevent access, backup the databases then take the databases off-line

    Logic been, that I can re-enable if anybody shouts over the next month or so (although this would be a last resort)

    I'm aware I can audit usage, specifically looking at reports run and communicate accordingly (already done)

    Currently if I try to take ReportServer or ReportServerTempDB off-line I get 'Database state cannot be changed while other users are using database 'Report Server''

    Thanks

    - Damian

  • November 16, 2016 at 3:43 am

    #1912733

    The SSRS Service is always active in the database, even when it's not really doing anything.

    SQL Server Reporting Services has two parts:

    The Service and the Databases it points to and the databases it uses.

    you want to go to the server running SSRS, and go to Administrative Tools>> Services

    Find "SQL Server Reporting Services" and stop and disable the service.

    then you can take the databases offline.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 16, 2016 at 4:23 am

    #1912737

    I thought it was a listed as a service

    Certainly on my 2012 instance, SQL Server Reporting Services is listed

    It's missing from my 2000 instance

    Is it difference in 2000?

    - Damian

  • November 16, 2016 at 4:49 am

    #1912744

    ok, clearly having a daft moment

    The service name is ReportServer

    Stopped this service and I can still access our Reports site ad run reports

    Also, the database cannot be taken off-line

    Am I missing something else here?

    - Damian

  • November 16, 2016 at 6:13 am

    #1912762

    well, sp_who/sp_who2 should at l least show what is connecting to that database, I would just force the database off line then.

    in 2005 and above, i know we could do it with the command ALTER DATABASE ReportServer SET OFFLINE WITH ROLLBACK IMMEDIATE

    but it might be different with SQL2000;

    i remember i used to have a cursor that killed all connections to specific databases, way back in those foggy SQL2000 days.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 16, 2016 at 8:41 pm

    #1912948

    I think that in the SQL 2000 version of SSRS, you have 2 web sites that are managed by IIS. These sites are used by people either running reports or managing reports. These web sites can be stopped using the IIS manager - in Computer Management, this should be in "Services and Applications"

    The Windows Service is responsible for executing background processes such as report subscriptions and snapshots. Users to not directly interact with this service. This service is the one you already found.

    If you need to stop all user activity, you will need to stop the 2 web sites and also the windows service

  • November 17, 2016 at 3:37 am

    #1913002

    Perfect, thanks Lowell and Happy Cat

    Stopped the web site via IIS

    Stopped the ReportServer process via Services

    Killed a SPID that was using the ReportServer DB

    Now, can no longer access the web site and managed to take the DB off-line

    Also, just brought everything back up again without any issues

    Thanks

    - Damian

  • November 18, 2016 at 2:46 am

    #1913353

    Now sit back and see who screams when they can't run their report 😀

  • November 18, 2016 at 2:50 am

    #1913355

    That's the plan!

    - Damian

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply