DBA

We have a database lock situation happens on one production applications, it's caused by the application design, when one user make change and does not save it quickly, the other users will be blocked with no helpful information but hang screen. I build this procedure to run every minute and send email alert to dba team and support staff, so we can talk to the blocker users to save the change or logoff for other users to continue their works. This procedure only list the top 1 waiter and blocker, this is good enough for this application. Here is the script. CREATE  procedure [dbo].[alert_on_databaselock] as declare @blockee_session_id varchar(100) declare @blockee_wait_type varchar(100) declare @wait_duration_ms varchar(100) declare @blocker_session_id varchar(100) declare @start_time varchar(100) declare @status varchar(100) declare @blockee_command varchar(100) declare @database_id varchar(100) declare @blockee_user_id varchar(100) declare @blockee_host_name var

Use the following query to list table size, it's running ok on sqlserver 2010. http://stackoverflow.com/questions/3927231/how-can-you-tell-what-tables-are-taking-up-the-most-space-in-a-sql-server-2005-d SELECT t . NAME AS TableName , i . name AS indexName , SUM ( p . rows ) AS RowCounts , SUM ( a . total_pages ) AS TotalPages , SUM ( a . used_pages ) AS UsedPages , SUM ( a . data_pages ) AS DataPages , ( SUM ( a . total_pages ) * 8 ) / 1024 AS TotalSpaceMB , ( SUM ( a . used_pages ) * 8 ) / 1024 AS UsedSpaceMB , ( SUM ( a . data_pages ) * 8 ) / 1024 AS DataSpaceMB FROM sys . tables t INNER JOIN sys . indexes i ON t . OBJECT_ID = i . object_id INNER JOIN sys . partitions p ON i . object_id = p . OBJECT_ID AND i . index_id = p . index_id INNER JOIN sys . allocation_units a ON p . partition_id = a . container_id WHERE t . NAME NOT LIKE 'dt%' AND i . OBJECT_ID > 255 AND i . index_id &

I use the following query to display the top 20 hot segments which have the biggest logical reads today. query: with hotsegmentvw as ( select * from ( SELECT obj# obj_id ,dataobj# data_obj_id,      sum(logical_reads_delta) AS total_logical_reads FROM dba_hist_seg_stat a WHERE     a.snap_id in (select snap_id from dba_hist_snapshot  where trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate)) GROUP BY obj#,dataobj# order by sum(logical_reads_delta) desc ) where rownum <= 20 ) select owner,object_name, total_logical_reads from hotsegmentvw a,dba_objects b where a.obj_id = b.object_id      AND a.data_obj_id = b.data_object_id order by total_logical_reads / sample result: OWNER           OBJECT_NAME                    TOTAL_LOGICAL_READS --------------- ------------------------------ ------------------- ODYSSEY         SPOT_CHANNEL_I4                          334938608 ODYSSEY         SCHEDULE_AVAIL                           369947968 BSS             VERSION_U1