RE: System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'OrganizationLifecycle'
stoker Remember it's not officially supported, to edit Dynamics DB's directly without them explictly saying so(via case, technote e.g.), so you do this at your own risk.
In SQL Management Studio, go to the Org_MSCRM db and edit the Stored Procedur p.ReindexAll and remove the following section at the beginning:
if (@StatsMaintenanceinBG=1)
begin
Print 'starting statistics maintenance';
retry:
declare @avg_cpu_percent int
declare @avg_data_io_percent int
declare @avg_log_write_percent int
declare @starttime datetime
select @starttime = getdate()
select top 1 @avg_cpu_percent=avg_cpu_percent,@avg_data_io_percent=avg_data_io_percent,@avg_log_write_percent=avg_log_write_percent from sys.dm_db_resource_stats
order by end_time desc
while (@avg_cpu_percent 500 (rows/100.00)*25 or modification_counter >sqrt (700 * rows ) ) and type='U'
order by sp.rows asc
if (@@rowcount>0)
begin
SET @Statement = 'UPDATE STATISTICS ' '[' @Schema '].' '[' @tableName ']' '[' @StatName ']' ' WITH FULLSCAN'
PRINT @Statement
begin TRY
exec sp_executesql @Statement
End try
begin catch
declare @error int
-- Save the @@error values in local variable before it is cleared.
SET @error = @@error
--PRINT 'There is exception moving further '; -- add error details
PRINT @error
if (@error=601)
begin
SET @Statement = 'UPDATE STATISTICS ' '[' @Schema '].' '[' @tableName ']' '[' @StatName ']'
exec sp_executesql @Statement
end
End catch;
print '--done'
end
else
begin
Print 'No stats need update waiting for 2 minutes'
WAITFOR DELAY '00:02';
end
select top 1 @avg_cpu_percent=avg_cpu_percent,@avg_data_io_percent=avg_data_io_percent,@avg_log_write_percent=avg_log_write_percent from sys.dm_db_resource_stats
order by end_time desc
end
print @avg_cpu_percent
print @avg_data_io_percent
print @avg_log_write_percent
Print 'Resource usage is high so waiting for 2 minutes before retrying'
WAITFOR DELAY '00:02';
if (datediff(hh,@starttime,getdate())
You will see an error on 24h basis where the async service fails to run this job calling p.ReindeAll stating that the Table: "sys.dm_db_resource_stats" doesn't exist..
I compared this to previous versions of Dynamics and p.ReindexAll did not have this.
But with the above said, It's quite common to run Index/statistics maintenance separately from Dynamics "built-in" job with AsyncService.
Best regards. /Philip
