Very interesting post from James DiMauro on SQLServerCentral.com about recovering a database when transaction log deleted, no backup available and you couldn’t set the database in emergency mode.
Maybe some issues appear when using SSMS 2005 Performance Dashboard.
Found solutions for some problems at MSDN.
Because the whole thread is confusing i collect the solutions at this site to get a clear overview.
Note: As always try at your own risk. Make a backup of your files before try the solutions.
Issue 1 – Difference of two datetime columns caused overflow at runtime.
Open C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\setup.sql.
Search for following code:
sum(convert(bigint, datediff(ms, login_time, getdate()))) – sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,
and replace this line with the code below.
sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) – sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,
Issue 2 – Blue Zone Problem (Server is running for 24 or more days).
Open C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\recent_cpu.rdl.
Go to line number 3271 and delete all lines till line number 3306.
Replace the delete lines with following code:
from (select s.session_id,
r.request_id,
s.login_time,
– s.host_name,
s.program_name,
s.login_name,
s.status as session_status,
s.last_request_start_time,
s.last_request_end_time,
s.cpu_time as session_cpu_time,
r.cpu_time as request_cpu_time,
– s.logical_reads as session_logical_reads,
– r.logical_reads as request_logical_reads,
r.start_time as request_start_time,
r.status as request_status,
r.command,
master.dbo.fn_varbintohexstr(r.sql_handle) as sql_handle,
master.dbo.fn_varbintohexstr(r.plan_handle) as plan_handle,
r.statement_start_offset,
r.statement_end_offset,
case
– Steve: Fixes begin here:
when convert(bigint, CAST ( DATEDIFF ( minute, start_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond,
DATEADD ( minute,DATEDIFF ( minute, start_time, getdate() ), Start_time ),getdate() ))
> 0
then convert(float, r.cpu_time) / convert(bigint, CAST ( DATEDIFF ( minute, start_time, getdate()) AS BIGINT)*60000
+ DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, start_time, getdate() ), Start_time ),getdate() )) else convert(float, 1.0) end
as avg_request_cpu_per_ms,
isnull (datediff(ms, case when r.start_time < @WithActivitySince then @WithActivitySince else r.start_time end, getdate()), 0)
as request_ms_in_window,
case when s.login_time > getdate() then convert(float, s.cpu_time) / (datediff(dd, s.login_time, getdate()) * cast(86400000 as bigint) + datediff(ms, dateadd(dd, datediff(dd, s.login_time, getdate()), s.login_time), getdate())) else convert(float, 1.0)
end as avg_session_cpu_per_ms,
convert(bigint,isnull(datediff(s, case when s.login_time < @WithActivitySince then @WithActivitySince else s.login_time end, case when r.request_id is null then s.last_request_end_time else getdate() end), 0) )* 1000
as session_ms_in_window
from sys.dm_exec_sessions s
left join sys.dm_exec_requests as r on s.session_id = r.session_id and s.session_id = 1
– Steve: Fixes end here
where (s.last_request_end_time > @WithActivitySince or r.request_id is not null)) as d
where (avg_request_cpu_per_ms * request_ms_in_window) + (avg_session_cpu_per_ms * session_ms_in_window) > 1000.0</CommandText>
<QueryParameters>
<QueryParameter Name=”@WithActivitySince”>
<Value>=Parameters!WithActivitySince.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>DataSource1</DataSourceName>
</Query>
Issue 3 – Arithmetic overflow error converting expression to data type datetime.
This problem appear mostly when clicking the blue cpu zone.
Open C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\recent_cpu.rdl.
Search for WithActivitySince and replace the parameter from String to DateTime.
