SSRS, Report Past Date TIME Executed

Use below query to get Last Execution Date and TIme of the Report.

=================================================================

use ReportServer
go

Set transaction isolation level read uncommitted
select        x.itemID,
        x.name,
        x.path,
        x.lastRunDate,
        x.avgTimeDataRetrieval,
        x.avgTimeProcessing,
        x.avgTimeRendering,
        x.avgTimeDataRetrieval + x.avgTimeProcessing + x.avgTimeRendering as avgTimeTotal,
        x.avgRowCount,
        lastStat.status,
        x.rsSuccessY,
        x.rsSuccessN,
        (x.rsSuccessY*1.0) / (x.totalEntries*1.0) as rsSuccessYpct,
                                x.lastNonSuccess,
        x.totalEntries
from        (           

        select        c.itemID,
                c.name,
                c.path,
                max(e.timeStart) as lastRunDate,
                avg(e.timeDataRetrieval) as avgTimeDataRetrieval,
                avg(e.timeProcessing) as avgTimeProcessing,
                avg(e.timeRendering) as avgTimeRendering,
                avg(e.[rowCount]) as avgRowCount,
                sum(case when e.status = 'rsSuccess' then 1 else 0 end) as rsSuccessY,
                sum(case when e.status = 'rsSuccess' then 0 else 1 end) as rsSuccessN,
                                                               (select max(timeStart) from executionLog where reportID = c.itemID and status != 'rsSuccess' and timeStart >=                                                                            dateAdd(mm,-6,getDate())) as lastNonSuccess,
                count(c.itemID) as totalEntries
        from        executionLog e
                inner join catalog c on e.reportID = c.itemID
        where        e.timeStart >= dateAdd(mm,-6,getDate())
        group by                    c.itemID,
                c.name,
                c.path
        ) x
        left join (       

                                select        reportID,
                max(timeStart) lastRunDate,
                [status]
        from        executionLog
        group by                    reportID, [status]           
        ) lastStat on x.itemID = lastStat.reportID and x.lastRunDate = lastStat.lastRunDate
       
       =================================================================

MSDB Restored, Version of SQL is same, But failes to run Maintenace plan

Please set the sys subsytem values to proper values.

Example :-

use msdb
go
select * from msdb.dbo.syssubsystems


update syssubsystems set subsystem_dll ='E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\binn\SQLCMDSS.DLL'
where subsystem='CmdExec'


Restart SQL Agent once Done.

Table with values you can find here :- http://support.microsoft.com/?kbid=903205

Map the dll with your DLL file location, It must work

RESTORE requires MAXTRANSFERSIZE

MAXTRANSFERSIZE

Specifies the maximum size of each block of memory to be used when SQL Backup stores backup data. You may want to specify this argument if a SQL Server reports that it has insufficient memory to service requests from SQL Backup.

Valid values are integers in multiples of 65536, up to a maximum value of 1048576.

For example:

MAXTRANSFERSIZE = 262144

If not specified, defaults to 1048576. However, if you have created the following DWORD registry key, SQL Backup uses the defined value as the default value:

HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\\MAXTRANSFERSIZE (32-bit)

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\\MAXTRANSFERSIZE (64-bit)

Data Compression

Data compression can be configured for the following database objects:

• A whole table that is stored as a heap.
  
• A whole table that is stored as a clustered index.
  
• A whole nonclustered index.
  
• A whole indexed view.
  
• For partitioned tables and indexes, the compression option can be configured for each partition, and the various partitions of an object do not have to have the same compression setting.
  

More On :- More On :-

DBCC ERROR :-The page may be invalid or may have an incorrect alloc unit ID in its header

BELOW IS THE DBCC ERR:-

 

Table error: page (1:735428) allocated to object ID 2025058250, index ID 0, partition ID 132714217473000, alloc unit ID 132724217472000 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.

There are 75408262 rows in 744739 pages for object "tblancellationHistory".

CHECKDB found 0 allocation errors and 1 consistency errors in table 'tblCancellationHistory' (object ID 2025058250).

 TO KNOW THE OBJECT, ANYWAY ITS ALREADY MENTIONED IN THE ERROR MSG :

USE

go

SELECT au.allocation_unit_id, OBJECT_NAME(p.object_id) AS table_name, fg.name AS filegroup_name,
au.type_desc AS allocation_type, au.data_pages, partition_number
FROM sys.allocation_units AS au
JOIN sys.partitions AS p ON au.container_id = p.partition_id
JOIN sys.filegroups AS fg ON fg.data_space_id = au.data_space_id
WHERE au.allocation_unit_id = (allocation_unit_id) -- in above case 132724217472000
ORDER BY au.allocation_unit_id

Once you get the Object name try the following ,

DBCC CHECKTABLE(TABLENAME, REPAIR_REBUILD)

The above dont have any impact on data, so there is no data loss.

If that dint solve the purpose, we have to opt for DATA LOSS

DBCC CHECKTABLE(TABLENAME, REPAIR_ALLOW_DATA_LOSS)

Make sure we have enough permission to run the above.

 If it dint solve we need to contact microsoft, hopefully it will resolve.

A program running on this computer is trying to display a message.

To disable Interactive Services Detection

 

1. Click Start > Control Panel and then double-click Administrative Tools.
2. Double-click Services.
3. Scroll down and double-click Interactive Services Detection.
4. On the General tab, change the Startup type to Manual or Disabled.
5. Click OK and restart the computer.

Enrolling the instance. Step failed. An exception occurred while executing a Transact-SQL statement or batch.

Failed Operation to Enroll an Instance of SQL Server into a SQL Server Utility

---

If you connect to the instance of SQL Server to enroll using SQL Server Authentication, and you specify a proxy account that belongs to a different Active Directory domain than the domain where the UCP is located, instance validation succeeds, but the enrollment operation fails with the following error message:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Additional information: Could not obtain information about Windows NT group/user '', error code 0x5. (Microsoft SQL Server, Error: 15404)
This issue occurs in the following example scenario:

1. The UCP is a member of "Domain_1."
   
2. A one-way domain trust relationship is in place: that is, "Domain_1" is not trusted by "Domain_2" but "Domain_2" is trusted by "Domain_1."
   
3. The instance of SQL Server to enroll into the SQL Server Utility is also a member of "Domain_1."
   
4. During the enroll operation, connect to the instance of SQL Server to enroll using “sa”. Specify a proxy account from "Domain_2."
   
5. Validation succeeds but enrollment fails.
   

The workaround for this issue, using the example above, is to connect to the instance of SQL Server to enroll into the SQL Server Utility using “sa” and provide a proxy account from "Domain_1."

1. Connect utility using SA
2. For enrolling use Domain 1 usr and pwd



More On  :- http://msdn.microsoft.com/en-us/library/ee210592.aspx