Category: Uncategorized

Backup Restore Progress and Estimated Time Remaining.

SELECT command, s.text, start_time, percent_complete, CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ‘ hour(s), ‘ + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + ‘min, ‘ + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ‘ sec’ as running_time, CAST((estimated_completion_time/3600000) as varchar) + ‘ hour(s), ‘ + CAST((estimated_completion_time %3600000)/60000 as varchar) + ‘min, ‘ + CAST((estimated_completion_time %60000)/1000 as varchar) + ‘ sec’ as est_time_to_go, dateadd(second,estimated_completion_time/1000, getdate())
Read More »

Syspolicy_Purge_History job failing at Step 3 – Erase Phantom System Health Records.

Tonight when I connected to one of the servers to fix something else, I noticed Syspolicy_Purge_History job has been failing and it has been failing for long time. In SQL server 2008 Policy Based Management was introduced. When policies  are checked for compliance the resulting data is saved in MSDB. The tables are : syspolicy_policy_execution_history_internal syspolicy_policy_execution_history_details_internal
Read More »

Capture DB usage before planning Decommission

In large establishments, where there are many servers, there are always chances of some servers or databases not being used but which remain operational. There is a need to decommission those servers or databases so that they can be used for other purposes. This should be a continuous process to analyze servers or databases which
Read More »

Drop/Delete databases older than 180 Days

We have  quite a few Dev servers where developers go on creating new databases for their developement work and testing. Whatever databases are needed are moved to QA servers after initial architecture is finalized. They wanted DBAs to delete databases which were created earlier than six months and drop them.  Now, to manually scan through
Read More »