RCC Historical reports not working

[ManiacMark]

Hi, first time poster but long time reader of this forum and many tips have helped alot but i can not figure out my current situation.

I have a BCM50 with RCC v2.5.462.1.61 and have had several problems with it lately. At one point it was stuck not downloading any new calls for about a month. Finally got Avaya involved and after 2 days of them playing with it they got the calls flowing again. But now my historical reports are timing out. Real time is fine.

I originally thought it was a cacheflush/auto-close problem as the tempdb was constantly restarting. I disabled auto-close but still historical reports take forever and then time out.

Here is my sql server logs. It looks like i have at least 1 problem with rccuser permissions. Any advice would be appreciated as i don't want to go through the long process of getting avaya involved again.

2011-02-20 16:30:59.53 Server Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 3)

2011-02-20 16:30:59.53 Server Error: 17054, Severity: 16, State: 1.
2011-02-20 16:30:59.53 Server The current event was not reported to the Windows Events log. Operating system error = 1502(The event log file is full.). You may need to clear the Windows Events log if it is full.
2011-02-20 16:30:59.53 Server (c) 2005 Microsoft Corporation.
2011-02-20 16:30:59.53 Server All rights reserved.
2011-02-20 16:30:59.53 Server Server process ID is 4444.
2011-02-20 16:30:59.53 Server Error: 17054, Severity: 16, State: 1.
2011-02-20 16:30:59.53 Server The current event was not reported to the Windows Events log. Operating system error = 1502(The event log file is full.). You may need to clear the Windows Events log if it is full.
2011-02-20 16:30:59.53 Server Authentication mode is MIXED.
2011-02-20 16:30:59.54 Server Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2011-02-20 16:30:59.54 Server This instance of SQL Server last reported using a process ID of 2032 at 2/20/2011 4:30:58 PM (local) 2/20/2011 9:30:58 PM (UTC). This is an informational message only; no user action is required.
2011-02-20 16:30:59.54 Server Registry startup parameters:
2011-02-20 16:30:59.54 Server -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2011-02-20 16:30:59.54 Server -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2011-02-20 16:30:59.54 Server -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2011-02-20 16:30:59.56 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2011-02-20 16:30:59.56 Server Detected 2 CPUs. This is an informational message; no user action is required.
2011-02-20 16:31:00.00 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2011-02-20 16:31:00.01 Server Database mirroring has been enabled on this instance of SQL Server.
2011-02-20 16:31:00.01 spid4s Starting up database 'master'.
2011-02-20 16:31:00.18 spid4s 3 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
2011-02-20 16:31:00.22 spid4s 0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
2011-02-20 16:31:00.22 spid4s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2011-02-20 16:31:00.36 spid4s SQL Trace ID 1 was started by login "sa".
2011-02-20 16:31:00.39 spid4s Starting up database 'mssqlsystemresource'.
2011-02-20 16:31:00.40 spid4s The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2011-02-20 16:31:00.79 spid4s Server name is 'REPORTING\RCCSQLEXPR'. This is an informational message only. No user action is required.
2011-02-20 16:31:00.79 spid8s Starting up database 'model'.
2011-02-20 16:31:00.79 spid4s Starting up database 'msdb'.
2011-02-20 16:31:01.15 Server A self-generated certificate was successfully loaded for encryption.
2011-02-20 16:31:01.15 Server Server is listening on [ 'any' <ipv4> 1612].
2011-02-20 16:31:01.15 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\RCCSQLEXPR ].
2011-02-20 16:31:01.15 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$RCCSQLEXPR\sql\query ].
2011-02-20 16:31:01.15 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2011-02-20 16:31:01.28 Logon Error: 17187, Severity: 16, State: 1.
2011-02-20 16:31:01.28 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: <local machine>]
2011-02-20 16:31:01.28 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2011-02-20 16:31:01.48 spid8s Clearing tempdb database.
2011-02-20 16:31:02.43 Logon Error: 18456, Severity: 14, State: 16.
2011-02-20 16:31:02.43 Logon Login failed for user 'rccuser'. [CLIENT: <local machine>]
2011-02-20 16:31:03.46 Logon Error: 18456, Severity: 14, State: 16.
2011-02-20 16:31:03.46 Logon Login failed for user 'rccuser'. [CLIENT: 127.0.0.1]
2011-02-20 16:31:03.65 spid8s Starting up database 'tempdb'.
2011-02-20 16:31:03.68 Logon Error: 18456, Severity: 14, State: 16.
2011-02-20 16:31:03.68 Logon Login failed for user 'rccuser'. [CLIENT: 127.0.0.1]
2011-02-20 16:31:03.68 Logon Error: 18456, Severity: 14, State: 16.
2011-02-20 16:31:03.68 Logon Login failed for user 'rccuser'. [CLIENT: 127.0.0.1]
2011-02-20 16:31:03.88 Logon Error: 18456, Severity: 14, State: 16.
2011-02-20 16:31:03.88 Logon Login failed for user 'sa'. [CLIENT: <local machine>]
2011-02-20 16:31:03.96 spid4s Recovery is complete. This is an informational message only. No user action is required.
2011-02-20 16:31:03.96 spid11s The Service Broker protocol transport is disabled or not configured.
2011-02-20 16:31:03.96 spid11s The Database Mirroring protocol transport is disabled or not configured.
2011-02-20 16:31:03.96 spid11s Service Broker manager has started.
2011-02-20 16:31:05.88 spid51 Starting up database 'CCRDB'.
2011-02-20 16:31:12.00 spid53 Starting up database 'ReportServer$RCCSQLEXPR'.
2011-02-20 16:32:30.51 spid60 Using 'xpstar90.dll' version '2005.90.3042' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2011-02-20 16:33:14.82 spid60 Using 'xplog70.dll' version '2005.90.3042' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2011-02-20 16:33:28.05 spid59 Starting up database 'ReportServer$RCCSQLEXPRTempDB'.
2011-02-20 16:35:45.27 spid59 Starting up database 'RCCReports'.
2011-02-20 16:36:37.48 spid59 Starting up database 'RCCReports'.
2011-02-20 16:38:30.09 spid16s SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2011-02-20 16:38:30.10 spid16s SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2011-02-20 16:38:30.10 spid16s SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2011-02-21 09:44:58.71 spid57 Starting up database 'RCCReports'.
2011-02-21 09:46:06.15 spid22s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2011-02-21 09:46:06.35 spid22s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2011-02-21 09:46:06.35 spid22s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

 

[Quirkafleeg]

Login failed for rccuser" messages usually occur as a result of moving around CCRDB databases between PCs. The login failure would explain why the reports fail, because the connection between Reporting Services and the RCCReports database (which is where reports are created before rendering) uses rccuser.

There are a number of possibilities to fixing this, any one may work (although usually only 1 actually does):
1. Rerun the IVU,
2. Reattach the user to the database using SQLCMD in command line,
3. Backup the database, drop the database using SQLCMD, copy the backup back in, then run the IVU,
4. (Brute force approach) Uninstall RCC and then reinstall it.

(Steps 2 and 3 do require the knowledge of the actual SQL to run)


The bigger worry is the login failure for "sa". However, this would affect RCC showing up in the browser. It could suggest a deeper problem with SQL Server itself.

Finally, RCC 2.5.462 was built in July 2009. The latest build is 2.5.464, built in March 2010.
It may be worth demanding an upgrade from Avaya.

 

[pkellow]

Personally, I'd be looking at installing the same version of RCC on a different PC of a similar spec, and recovering the database from the pc with the issue to this pc. Then purging the database to minimum amount of data to meet customer requirements. Then see if this improves the reports. When running a report, behind the hood, the report generation will require any and all appropriate tables to be searched, data extracted, and report presented. Running a report might seem trivial from the GUI, however it has to do a lot of processing. The bigger the DB, the longer the report will take. If the report still times out, then throw higher spec pc at it, or contact Avaya as they may need to implement indexing if upgrading to latest rcc version does not resolve. RCC should be capable of handling 6 months of data.

Alternatively you could backup the entire rcc database, run the purge on the current pc, then see if this improves.