Javier Villegas - SQL DBA

Handling Indexes Fragmentation

2012-01-26T10:39:00.001-03:00

This is a handy SQL Template to get information about the indexes fragmentation on a particular user database. It can be used to rebuild all the indexes based on a rule (ex. index fragmentation > 30% ). or just to get a report of the current index fragmentation status
the script has below characteristics:

the variable @EXECUTE is to decide whether you want to get the current index fragmentation for all the tables or proceed with the full index rebuild
Since it is a template, you have to specify the database name and an e-mail address (to get a report) (see the screenshot)
I never rebuild indexes on a database when the recovery model is FULL so the script checks for that
If the SQL Edition is Enterprise, then it uses ONLINE= ON option
It uses MAXDOP = 0
It generates a report with the previous and current index fragmentation. This is a HTML report that you should receive by e-mail

This is how you specify the variables in the sql template

Note that @EXECUTE variable is currently 0 so the script will just report the index fragmentation. Remember to change it to 1 when you really want to start the index rebuild process

USE <Database,sysname,model>

SET NOCOUNT ON

DECLARE @EXECUTE bit = 0 -- // 0 = PRINT , 1 = EXECUTE

IF @EXECUTE = 1 SELECT 'REINDEX TABLES' ELSE SELECT 'PRINTING STATEMENTS / REPORT'

SELECT @@servername,serverproperty('Edition'),getdate(),db_name() as Database_Name, recovery_model_desc as [Recovery Model]

FROM sys.databases where name = ''

--ALTER DATABASE SET RECOVERY SIMPLE

--ALTER DATABASE SET RECOVERY FULL

IF (SELECT recovery_model_desc FROM sys.databases where name = '') = 'FULL' AND @EXECUTE = 1

BEGIN

SELECT 'The database is in FULL recovery model, Reindex cannot be executed'

GOTO FINAL

END

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @rows int;

DECLARE @prtime datetime;

DECLARE @command varchar(max);

DECLARE @Counter int = 1;

DECLARE @Total int = 0;

DECLARE @Initial_Time datetime;

DECLARE @tableHTML_Fragmentation varchar(max);

select @Initial_Time = getdate()

IF (object_id( 'tempdb..#tablesrows' ) IS NOT NULL) DROP TABLE ..#tablesrows ;

SELECT object_id,OBJECT_NAME(object_id) TableName,SUM(Rows) Rows

INTO #tablesrows

FROM sys.partitions WHERE index_id < 2 GROUP BY object_id

IF (object_id( 'tempdb..#TMP_DBA_IDX_FRAG' ) IS NOT NULL) DROP TABLE ..#TMP_DBA_IDX_FRAG ;

CREATE TABLE #TMP_DBA_IDX_FRAG (

objectid int,

indexid int,

partitionnumber int,

avg_fragmentation_in_percent float,

new_avg_fragmentation_in_percent float,

[Rows] bigint)

INSERT INTO #TMP_DBA_IDX_FRAG

SELECT

IDX.object_id AS objectid,

IDX.index_id AS indexid,

IDX.partition_number AS partitionnum,

IDX.avg_fragmentation_in_percent AS frag,

0 as new_avg_fragmentation_in_percent,

RC.Rows

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') as IDX

INNER JOIN #tablesrows RC ON IDX.object_id=RC.object_id

WHERE index_id > 0 and avg_fragmentation_in_percent >10;

SELECT @Total = count(*) FROM #TMP_DBA_IDX_FRAG;

IF @EXECUTE = 1 PRINT 'REINDEX TABLES' ELSE PRINT 'PRINTING STATEMENTS'

PRINT '======================================================='

PRINT ''

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT objectid,indexid,partitionnumber,avg_fragmentation_in_percent,[Rows] FROM #TMP_DBA_IDX_FRAG

order by rows desc,objectid,indexid;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)

BEGIN;

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag,@rows;

IF @@FETCH_STATUS < 0 BREAK;

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

FROM sys.partitions

WHERE object_id = @objectid AND index_id = @indexid;

SET @prtime = getdate()

SET @command = NULL

--IF @frag < 30.0 and @frag > 15.0

-- SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE; --('+cast(@frag as varchar)+'%) // '+cast(@rows as varchar) +' rows';

IF @frag >= 30.0

BEGIN

SELECT @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (MAXDOP =0'+CASE serverproperty('EngineEdition') WHEN 3 THEN ',ONLINE =ON' END+');--('+cast(@frag as varchar)+'%) // '+cast(@rows as varchar) +' rows';

END

IF @partitioncount > 1

SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

BEGIN TRY

IF @command is not null IF @EXECUTE = 1 EXEC (@command) ELSE PRINT (@command);

END TRY

BEGIN CATCH

PRINT cast(ERROR_NUMBER() as varchar(10))+'//'+ ERROR_MESSAGE();

IF ERROR_NUMBER() = 2725 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (MAXDOP =0);--('+cast(@frag as varchar)+'%) // '+cast(@rows as varchar) +' rows (OFFLINE)'

IF @EXECUTE = 1 EXEC (@command) ELSE PRINT (@command)

END CATCH

PRINT N'Executed ('+ cast(@Counter as varchar)+'/'+cast(@Total as varchar)+'):' + @command;

PRINT N'Execution Time: ' +CONVERT ( varchar(30) , getdate() ,120)+' ('+SUBSTRING(CONVERT ( varchar(30) , getdate()-@prtime ,120),12,1000)+')'

PRINT N'-----------'

SET @Counter=@Counter+1

END;

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

UPDATE #TMP_DBA_IDX_FRAG

SET new_avg_fragmentation_in_percent = IDX.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') as IDX

WHERE objectid = IDX.object_id and indexid=IDX.index_id

select

t.name

,i.name

,round(avg_fragmentation_in_percent,2) as avg_fragmentation_in_percent

,round(new_avg_fragmentation_in_percent,2) as new_avg_fragmentation_in_percent

,[Rows]

from #TMP_DBA_IDX_FRAG M (nolock)

inner join sys.tables t on M.objectid =t.object_id

inner join sys.indexes i on M.indexid =i.index_id and M.objectid =i.object_id

order by 1

select @@servername as Server_Name,getdate() as [Now],avg(avg_fragmentation_in_percent) as Previous_avg_frag,avg(new_avg_fragmentation_in_percent) as New_avg_frag

from #TMP_DBA_IDX_FRAG (nolock)

where rows > 4000

DECLARE @avg_fragmentation_in_percent float

DECLARE @new_avg_fragmentation_in_percent float

select @avg_fragmentation_in_percent=avg(avg_fragmentation_in_percent),

@new_avg_fragmentation_in_percent=avg(new_avg_fragmentation_in_percent)

from #TMP_DBA_IDX_FRAG (nolock)

where rows > 4000

SET @tableHTML_Fragmentation =
N'
Previous AVG Fragmentation ('+cast(@avg_fragmentation_in_percent as varchar)+') - New AVG Fragmentation ('+cast(@new_avg_fragmentation_in_percent as varchar)+')

'+
N'
'+
N'
'+
N'
'+
CAST ( ( SELECT td = t.name, '',
td = i.name, '',
td = cast(round(avg_fragmentation_in_percent,2) as varchar(10)), '',
td = cast(round(new_avg_fragmentation_in_percent,2) as varchar(10)), '',
td = cast([Rows] as varchar(10))
FROM #TMP_DBA_IDX_FRAG M
inner join sys.tables t on M.objectid =t.object_id
inner join sys.indexes i on M.indexid =i.index_id and M.objectid =i.object_id
ORDER BY 1
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'
Table Name
Index Name
AVG Fragmentation
NEW AVG Fragmentation
Rows
' ;

DECLARE @MailText varchar(max);

DECLARE @Subj varchar(250)

Set @Subj='DBREINDEX on ('+rtrim(@@servername)+') Database () HAS FINISHED'

SELECT @MailText = 'FULL DATABASE REINDEX ON '+rtrim(@@servername)+' for STARTED AT '+CONVERT(varchar(30), @MailText, 120)+char(13)+' ,FINISHED AT ' +CONVERT(varchar(30), getdate(), 120)

IF @avg_fragmentation_in_percent IS NOT NULL AND @EXECUTE = 1

BEGIN

EXEC msdb.dbo.sp_send_dbmail

@subject =@Subj

, @body =@tableHTML_Fragmentation

, @recipients =''

, @execute_query_database = ''

, @body_format = 'HTML' ;

END

----------------------------------

-- Drop the temporary table.

IF (object_id( 'tempdb..#tablesrows' ) IS NOT NULL) DROP TABLE ..#tablesrows ;

IF (object_id( 'tempdb..#TMP_DBA_IDX_FRAG' ) IS NOT NULL) DROP TABLE ..#TMP_DBA_IDX_FRAG ;

FINAL:

SELECT name,compatibility_level,recovery_model_desc from sys.databases
GO

Check Statistics

2012-01-18T17:31:00.003-03:00

This script helps to check when was the last time the statistics were updated for all tables/indexes in all the databases, also provides a table modification rate value (percentage) so then you can perform update stats with fullscan for tables/indexes based on a defined rule (ex. stats for tables/indexes not updated in the last 30 days and/or the modification rate is over 30%)

select @@ServerName, getdate()

/* **************************************************************** */

use master

DECLARE @db sysname,

@cmd varchar(4000)

SET NOCOUNT ON

IF (object_id( 'tempdb..#DBA_STATISTICS_STATUS' ) IS NOT NULL) DROP TABLE #DBA_STATISTICS_STATUS ;

CREATE TABLE #DBA_STATISTICS_STATUS(

id int identity primary key,

database_name sysname,

table_schema sysname,

table_name sysname,

index_name sysname,

table_id int,

index_id int,

modifiedRows int,

rowcnt int,

ModifiedPercent DECIMAL(18,8),

lastStatsUpdate datetime

)

DECLARE Cursor_Statistics CURSOR FOR

SELECT name FROM sys.databases (NOLOCK)

WHERE NAME not in ('tempdb') and Is_Read_only = 0

order by name

OPEN Cursor_Statistics

FETCH NEXT FROM Cursor_Statistics

INTO @db

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @cmd = 'USE ['+@db+'];

Insert into #DBA_STATISTICS_STATUS

select

db_name() as database_name,

schemas.name as table_schema,

tbls.name as table_name,

i.name as index_name,

i.id as table_id,

i.indid as index_id,

i.rowmodctr as modifiedRows,

(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt,

convert(DECIMAL(18,8), convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPercent,

stats_date( i.id, i.indid ) as lastStatsUpdate

from sysindexes i

inner join sysobjects tbls on i.id = tbls.id

inner join sysusers schemas on tbls.uid = schemas.uid

inner join information_schema.tables tl

on tbls.name = tl.table_name

and schemas.name = tl.table_schema

--and tl.table_type=''BASE TABLE''

where 0 < i.indid and i.indid < 255

and table_schema <> ''sys''

and i.rowmodctr <> 0

and i.status not in (8388704,8388672)

and (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) > 0

--PRINT @cmd

EXEC (@cmd)

FETCH NEXT FROM Cursor_Statistics

INTO @db

END

CLOSE Cursor_Statistics

DEALLOCATE Cursor_Statistics

SELECT * from #DBA_STATISTICS_STATUS WHERE ModifiedPercent >= 20 or datediff(DD,lastStatsUpdate,getdate()) >= 30

ORDER BY database_name,rowcnt desc

DECLARE @table sysname,

@schema sysname,

@index sysname

DECLARE Cursor_Statistics CURSOR FOR

SELECT database_name,table_schema,table_name,index_name from #DBA_STATISTICS_STATUS WHERE ModifiedPercent >= 20 or datediff(DD,lastStatsUpdate,getdate()) >= 30

ORDER BY database_name,rowcnt desc

OPEN Cursor_Statistics

FETCH NEXT FROM Cursor_Statistics

INTO @db,@schema,@table,@index

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @cmd = 'USE ['+@db+'];

UPDATE STATISTICS '+@schema+'.'+@table+' '+@index+' WITH FULLSCAN;

PRINT @cmd

EXEC (@cmd)

PRINT '--'+cast(getdate() as varchar(20))

FETCH NEXT FROM Cursor_Statistics

INTO @db,@schema,@table,@index

END

CLOSE Cursor_Statistics

DEALLOCATE Cursor_Statistics

select @@ServerName, getdate()

/* **************************************************************** */

IF (object_id( 'tempdb..#DBA_STATISTICS_STATUS' ) IS NOT NULL) DROP TABLE #DBA_STATISTICS_STATUS ;

New AlwaysOn DMV on SQL 2012

2012-01-03T11:58:00.001-03:00

This is the first post this year so I decided to publish something related to the upcoming SQL version (SQL Server 2012)

AlwaysOn is the new High Availability - Disaster Recovery Technique (HA/DR) which is a huge improvement compared with the existing Database Mirroring. (up to 4 read/only replicas. More info http://www.microsoft.com/sqlserver/en/us/future-editions/mission-critical/SQL-Server-2012-high-availability.aspx)

SQL Server 2012 has a new set of Dynamic Management Views and Functions (DMV/DMF). Most of them are related to AlwaysOn. Note that all of them start with sys.dm_hadr_%

They provide all the state of the Availability groups, replicas, cluster, etc. Useful when you have to troubleshoot

select * from sys.dm_hadr_auto_page_repair

select * from sys.dm_hadr_availability_group_states

select * from sys.dm_hadr_availability_replica_states

select * from sys.dm_hadr_cluster

select * from sys.dm_hadr_cluster_members

select * from sys.dm_hadr_cluster_networks

select * from sys.dm_hadr_database_replica_cluster_states

select * from sys.dm_hadr_database_replica_states

select * from sys.dm_hadr_instance_node_map

select * from sys.dm_hadr_name_id_map

Get Top queries by CPU Time

2011-12-27T11:07:00.001-03:00

This script will help you to identify the TOP X heaviest statements executed on the SQL Server since the last time the service was started. The result set returns CPU Times, Logical reads and writes, as well as I/O stats.

It is very useful when you need to start to troubleshoot a performance issue.

select top 10 rank() over(order by total_worker_time desc,sql_handle,statement_start_offset) as row_no

, (rank() over(order by total_worker_time desc,sql_handle,statement_start_offset))%2 as l1

, creation_time

, last_execution_time

, (total_worker_time+0.0)/1000 as total_worker_time

, (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]

, total_logical_reads as [LogicalReads]

, total_logical_writes as [logicalWrites]

, execution_count

, total_logical_reads+total_logical_writes as [AggIO]

, (total_logical_reads+total_logical_writes)/(execution_count + 0.0) as [AvgIO]

, case when sql_handle IS NULL

then ' '

else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ) )

end as query_text

, db_name(st.dbid) as database_name

, CASE DB_NAME()

WHEN 'Interlink_Main90' THEN OBJECT_NAME(st.objectid)

ELSE CAST(st.objectid as varchar(20))

END as [Object]

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(sql_handle) st

where total_worker_time > 0

order by total_worker_time desc

Find missing indexes

2011-12-16T11:47:00.000-03:00

This script will help you to identify all the missing indexes indexes detected by the database engine since the last time the service was started. it provides the equality and inequality columns for the proposed index as well as the included ones.

select

-- mid.database_id,

-- mid.object_id,

mid.equality_columns,

mid.inequality_columns,

mid.included_columns,

mid.statement,

migs.unique_compiles,

migs.user_seeks,

-- migs.user_scans,

migs.last_user_seek,

datediff(mi,migs.last_user_seek,getdate()) as [Min_since_last_U_Seek], --Amount of minutes since the last user seek

--migs.last_user_scan,

round(migs.avg_total_user_cost,2) as avg_total_user_cost,

migs.avg_user_impact,

round(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans),0) as SortOrd --Calculated value to measure the missing index

--select mid.*, migs.*

from sys.dm_db_missing_index_details mid (nolock)

join sys.dm_db_missing_index_groups mig(nolock)

on mid.index_handle = mig.index_handle

join sys.dm_db_missing_index_group_stats migs(nolock)

on mig.index_group_handle = migs.group_handle

--where mid.statement like '%My_Database%'

order by migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

--migs.user_seeks desc, mid.statement

Script to fix Orphaned users after restoring (SQL 2005 , 2008 , 2008 R2)

2011-12-05T15:14:00.000-03:00

This is a very useful script I've been using to troubleshoot and fix Orphaned users in sql after restoring databases to a testing environment.

I do restore my production databases to a test server on a daily basis so at the end of the process I run this script. It remaps the orphan database users to the sql logins (the sql logins must be created beforehand)

use master

IF (object_id( 'tempdb..##TMP_ORPHANED_USERS' ) IS NOT NULL) DROP TABLE ##TMP_ORPHANED_USERS ;

CREATE TABLE ##TMP_ORPHANED_USERS(

ServerName sysname

,DatabaseName sysname

,UserName varchar(500)

,UserType varchar(500)

,DefaultSchemaName varchar(500)

,ResolvedID varchar(500)

)

DECLARE @db sysname,

@cmd varchar(4000)

SET NOCOUNT ON

DECLARE C_databases CURSOR FOR

SELECT name FROM sys.databases (NOLOCK)

WHERE database_id >4 and is_read_only = 0

order by name

OPEN C_Databases

FETCH NEXT FROM C_Databases

INTO @db

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @cmd = 'USE ['+@db+'];INSERT INTO ##TMP_ORPHANED_USERS select @@servername,db_name(),[name],type_desc,Default_Schema_Name,SUSER_SNAME(SID) from sys.database_principals

where type_desc IN (''SQL_USER'',''WINDOWS_USER'',''WINDOWS_GROUP'') and

principal_id >4 and

SID not in (select SID from sys.server_principals)

and [name] <> ''guest'''

--PRINT @cmd

--PRINT ''

EXEC (@cmd)

FETCH NEXT FROM C_Databases

INTO @db

END

CLOSE C_Databases

DEALLOCATE C_Databases

select * from ##TMP_ORPHANED_USERS

DECLARE @db sysname,

@user sysname,

@defaultschema varchar(500),

@cmd varchar(4000)

SET NOCOUNT ON

DECLARE C_OrpUsers CURSOR FOR

SELECT DatabaseName,UserName,DefaultSchemaName FROM ##TMP_ORPHANED_USERS (NOLOCK)

OPEN C_OrpUsers

FETCH NEXT FROM C_OrpUsers

INTO @db,@user,@defaultschema

WHILE @@FETCH_STATUS = 0

BEGIN

--SELECT @cmd = 'USE '+@db+';IF EXISTS (select * from sys.schemas where name = '''+@user+''')DROP SCHEMA ['+@user+'];DROP USER ['+@user+'];'

SELECT @cmd = 'USE '+@db+';ALTER USER ['+@user+'] WITH DEFAULT_SCHEMA =['+@defaultschema+'],LOGIN = ['+@user+']'

BEGIN TRY

PRINT @cmd

EXEC (@cmd)

END TRY

BEGIN CATCH

SELECT

@db as DatabaseName

,@User as UserName

,ERROR_NUMBER() AS ErrorNumber

,ERROR_SEVERITY() AS ErrorSeverity

,ERROR_STATE() AS ErrorState

,ERROR_PROCEDURE() AS ErrorProcedure

,ERROR_LINE() AS ErrorLine

,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

FETCH NEXT FROM C_OrpUsers

INTO @db,@user,@defaultschema

END

CLOSE C_OrpUsers

DEALLOCATE C_OrpUsers

IF (object_id( 'tempdb..##TMP_ORPHANED_USERS' ) IS NOT NULL) DROP TABLE ##TMP_ORPHANED_USERS ;

SQL Waits Stats

2011-11-25T10:51:00.001-03:00

Waits are represented by SQL Server wait statistics. SQL Server tracks wait information any time that a user connection or session_id is waiting. This wait information is summarized and categorized across all connections so that a performance profile can be obtained for a given work load. Therefore, SQL Server wait types identify and categorize user (or thread) waits from an application workload or user perspective.

Queues measure system resources and utilization. The queues part of performance is represented by Performance Monitor objects and counters and other sources of information. Performance Monitor counters measure various aspects of performance such as transfer rates for disks or the processor time consumed. SQL Server object counters are exposed to Performance Monitor using the dynamic management view (DMV) sys.dm_os_performance_counters. Thus, Performance Monitor counters show performance from a resource point of view.

Associations or correlations of wait types to performance counters, and interesting performance counter ratios and comparisons round out the picture. The association of waits and queues allows one to eliminate irrelevant counters insofar as the performance bottleneck is concerned and focus effectively on the problem area. Comparisons of one counter to another provide perspective in order to draw the right conclusion. For example, say you encounter 1000 lock waits during a production workload. Does this cause a serious performance problem? To determine what role these play in overall performance you need to also consider the total number of lock requests (how many locks are requested overall vs. how many result in lock waits), the wait time or duration of the lock waits, and the time span of the test. If the total number of lock requests was 10 million, perhaps 1000 waits is insignificant. Further, if the associated lock wait time is 50 seconds overall, and the workload is over 8 hours, this too is insignificant. On the other hand, if you average a 50 second wait for EACH of the 1000 lock waits, this IS significant. In sum, associations or correlations allow us to determine relevancy to overall performance.

Application performance can be easily explained by looking at SQL Server waits and System or Resource queues. In SQL Server the dynamic management view (DMV) sys.dm_os_wait_stats provides a valuable source of wait information from an application perspective. The system or resource perspective uses Performance Monitor counters and other data sources to provide a breakdown of system resource usage according to resource queues. Taken together, the value of the application and resource perspectives used by the waits and queues methodology enables us to narrow in on the bottleneck and eliminate the irrelevant performance data.

--Script

-- Isolate top waits for server instance since last restart or statistics clear

-- Clear Wait Stats
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
SET NOCOUNT ON
select @@servername as servername,*,CASE wait_time_ms WHEN 0 THEN 0 ELSE wait_time_ms/waiting_tasks_count END as wait_time_avg_ms
from
sys.dm_os_wait_stats (NOLOCK)
where
wait_type in ('WRITELOG','SOS_SCHEDULER_YIELD','CXPACKET','LOGBUFFER','PAGEIOLATCH_SH','CMEMTHREAD') OR wait_type like 'RESOURCE_SEMAPHORE%' or wait_type like '%NETWORK%'
ORDER BY waiting_tasks_count desc;
/*
select text,query_plan,requested_memory_kb,granted_memory_kb,used_memory_kb
from sys.dm_exec_query_memory_grants MG
cross apply sys.dm_exec_sql_text(sql_handle) t
cross apply sys.dm_exec_query_plan(MG.plan_handle)
*/
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

-- Common Significant Wait types with BOL explanations

-- *** Network Related Waits ***
-- ASYNC_NETWORK_IO Occurs on network writes when the task is blocked behind the network

-- *** Locking Waits ***
-- LCK_M_IX Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock
-- LCK_M_IU Occurs when a task is waiting to acquire an Intent Update (IU) lock
-- LCK_M_S Occurs when a task is waiting to acquire a Shared lock

-- *** I/O Related Waits ***
-- ASYNC_IO_COMPLETION Occurs when a task is waiting for I/Os to finish
-- IO_COMPLETION Occurs while waiting for I/O operations to complete.
-- This wait type generally represents non-data page I/Os. Data page I/O completion waits appear
-- as PAGEIOLATCH_* waits
-- PAGEIOLATCH_SH Occurs when a task is waiting on a latch for a buffer that is in an I/O request.
-- The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
-- PAGEIOLATCH_EX Occurs when a task is waiting on a latch for a buffer that is in an I/O request.
-- The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.
-- WRITELOG Occurs while waiting for a log flush to complete.
-- Common operations that cause log flushes are checkpoints and transaction commits.
-- PAGELATCH_EX Occurs when a task is waiting on a latch for a buffer that is not in an I/O request.
-- The latch request is in Exclusive mode.
-- BACKUPIO Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data

-- *** CPU Related Waits ***
-- SOS_SCHEDULER_YIELD Occurs when a task voluntarily yields the scheduler for other tasks to execute.
-- During this wait the task is waiting for its quantum to be renewed.

-- THREADPOOL Occurs when a task is waiting for a worker to run on.
-- This can indicate that the maximum worker setting is too low, or that batch executions are taking
-- unusually long, thus reducing the number of workers available to satisfy other batches.
-- CX_PACKET Occurs when trying to synchronize the query processor exchange iterator
-- You may consider lowering the degree of parallelism if contention on this wait type becomes a problem

Get CPU usage history

2011-11-03T08:46:00.000-03:00

This script helps you to easily check the CPU usage of your SQL Server. You can get SQL CPU usage , Other processes CPU usage(Non-SQL) and the total usage for the previous 2 hours. Helpful to understand the usage patterns as well as to create a baseline.

-- CPU Usage History

SET NOCOUNT ON

declare @ts_now bigint

--SELECT @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info -- (Use this line if server is SQL 2005)

SELECT @ts_now = ms_ticks from sys.dm_os_sys_info -- (Use this line if server is SQL 2008 or above)

select /* top 1 */ record_id,

dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,

SQLProcessUtilization,

--SystemIdle,

100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization,

SQLProcessUtilization + (100 - SystemIdle - SQLProcessUtilization) as CPU_Usage

from (

select

record.value('(./Record/@id)[1]', 'int') as record_id,

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,

timestamp

from (

select timestamp, convert(xml, record) as record

from sys.dm_os_ring_buffers

where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

and record like '%<SystemHealth>%') as x

) as y

order by record_id desc

SQL Pass 2011 - Code Names

2011-10-28T16:05:00.000-03:00

SQL Server Code Names

Un poco de historia acerca de los nombres en codigo de las versiones de SQL

Desde SQL 4.21 hasta SQL 2012 "Denali"

Some history about SQL code names

From SQL 4.21 to SQL 2012 "Denali"

SQL Denali es SQL Server 2012

2011-10-19T12:55:00.000-03:00

Voy a ir subiendo fotos y links de SQL PASS Summit 2011 en Seattle, Wa

Lo mas importante fue la introducción de SQL Server 2012

Esteré subiendo comentario y fotos del los tres Key Notes. Haciendo especial referencia a el caso de estudio de MSC utilizando AlwaysOn

All you need to know about your backups and Database Mirror Status

2011-10-10T11:36:00.000-03:00

This script shows all the backup status information as well as the current database mirroring status (if enabled)

- Database Name
- Last Full backup
- Last Transaction Log backup
- Last Restore
- Recovery Model and Compatibility Level
- Database Mirror
- Curent Role
- Status
- Transaction Log usage (Percentage)

-- MAIN SCRIPT

--// SQL Version

USE

msdb

-- Last Full Backup Info

SELECT

D.name as Database_Name

--,D.database_id as dbid

,B.last_backup_finish_date as [Last Full Backup Finish Date]

,TR.last_TRLog_backup_finish_date as [Last T.Log Backup Finish Date] --Transaction Log

,R.last_restore_date as [Last Restore Date]

,D.recovery_model_desc+' ('+cast(D.compatibility_level as varchar(3))+')' as [Recovery Model / Compatibility Level]

,DM.mirroring_role_desc+'('+DM.mirroring_state_desc+')' as [DBMirror Info]

, LOG.cntr_value as [% T.Log Used]

FROM sys.databases D

LEFT JOIN (

SELECT

BS.database_name

--,BS.backup_start_date

,max(BS.backup_finish_date) as last_backup_finish_date

--,BS.backup_size

FROM

msdb.dbo.backupset BS (NOLOCK)

WHERE

BS.backup_start_date >= CAST(CONVERT(varchar(10),dateadd(mm,-3,getdate()),120) AS datetime)

AND BS.type='D'

GROUP BY

BS.database_name

--ORDER BY

-- BS.backup_start_date ASC

) B on D.name=B.database_name

LEFT JOIN (

SELECT

BS.database_name

,max(BS.backup_finish_date) as last_TRLog_backup_finish_date

FROM

msdb.dbo.backupset BS (NOLOCK)

INNER JOIN

msdb.dbo.backupmediafamily MF(NOLOCK) ON BS.media_set_id = MF.media_set_id

WHERE

BS.backup_start_date >= CAST(CONVERT(varchar(10),dateadd(mm,-1,getdate()),120) AS datetime)

AND BS.type='L'

GROUP BY

BS.database_name

) TR on D.name=TR.database_name

LEFT JOIN (

SELECT

rh.destination_database_name,

max(rh.restore_date) as last_restore_date

FROM

msdb.dbo.restorehistory rh (NOLOCK)

INNER JOIN msdb.dbo.backupset BS (NOLOCK) ON rh.backup_set_id=BS.backup_set_id

WHERE

BS.type= 'D' AND

RH.restore_date >=CAST(CONVERT(varchar(10),dateadd(mm,-3,getdate()),120) AS datetime)

GROUP BY

rh.destination_database_name

-- ORDER BY

-- max(rh.restore_date) desc

) R on D.name=R.destination_database_name

LEFT JOIN

msdb.sys.database_mirroring dm (nolock) on d.database_id=dm.database_id

LEFT JOIN (

select object_name,instance_name,cntr_value from sys.dm_os_performance_counters

where object_name = 'SQLServer:Databases'

--and instance_name = 'Interlink_Main90'

and counter_name in ('Percent Log Used')

--order by cntr_value desc

) LOG on d.name=LOG.instance_name

order by

--D.name asc,

R.last_restore_date desc

Extended Events - SQL Errors

2011-10-03T22:06:00.000-03:00

This script creates an Extended Events Session (XEvent) to capture SQL errors. Very useful to detect the object and the statement that is raising the error.

The first part creates the session and the second one is to retrieve the errors that are been raising.

IMPORTANT: This is a template so make sure you enter the proper values (Path and session name)

USE master

-- Create XEvents Session

exec master.dbo.xp_cmdshell 'Dir "*.*"'

CREATE EVENT SESSION <XEvents_Session_Name,varchar(1000),sql_text_and_errors>

ON SERVER

ADD EVENT sqlserver.error_reported

(

-- ACTION( sqlserver.tsql_stack )

ACTION (sqlserver.tsql_stack, sqlserver.sql_text, sqlserver.database_id, sqlserver.username, sqlserver.client_app_name, sqlserver.client_hostname)

WHERE

((

[error]<> 2528 -- DBCC execution completed...

AND [error]<> 3014 -- BACKUP LOG successfully processed ...

AND [error]<> 4035 -- Processed 0 pages for database ...

AND [error]<> 5701 -- Changed database context to ,,,

AND [error]<> 5703 -- Changed language setting to ...

AND [error]<> 18265 -- Log was backed up. ...

AND [error]<> 14205 -- (unknown)

AND [error]<> 14213 -- Core Job Details:

AND [error]<> 14214 -- Job Steps:

AND [error]<> 14215 -- Job Schedules:

AND [error]<> 14216 -- Job Target Servers:

AND [error]<> 14549 -- (Description not requested.)

AND [error]<> 14558 -- (encrypted command)

AND [error]<> 14559 -- (append output file)

AND [error]<> 14560 -- (include results in history)

AND [error]<> 14561 -- (normal)

AND [error]<> 14562 -- (quit with success)

AND [error]<> 14563 -- (quit with failure)

AND [error]<> 14564 -- (goto next step)

AND [error]<> 14565 -- (goto step)

AND [error]<> 14566 -- (idle)

AND [error]<> 14567 -- (below normal)

AND [error]<> 14568 -- (above normal)

AND [error]<> 14569 -- (time critical)

AND [error]<> 14570 -- (Job outcome)

AND [error]<> 14635 -- Mail queued.

AND [error]<> 14638 -- Activation successful.

AND [error]<= 50000 -- Exclude User Errors

)))

----------------------------------------------------------------------------------

-- <<<< Target File Mode >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

-- ****** Make sure you have enough disk space - Also you must monitor the disk while the session is running ******

ADD TARGET package0.asynchronous_file_target

(SET

filename=''

,max_file_size=4000

)

----------------------------------------------------------------------------------

WITH

(

MAX_MEMORY = 4096KB,

EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,

MAX_DISPATCH_LATENCY = 1 SECONDS,

MEMORY_PARTITION_MODE = NONE,

TRACK_CAUSALITY = ON, -- Very important to get the event in order later on

STARTUP_STATE = ON -- Note that we start on

);

----------------------------------------------------------------------------------------------

-- STEP: Start the session & see which sessions are currently running

ALTER EVENT SESSION <XEvents_Session_Name,varchar(1000),sql_text_and_errors>

ON SERVER

STATE = START

----------------------------------------------------------------------------------------------

-- STEP: Wait for events to be recoredd

-- <<<< Ring Buffer Mode >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SELECT

len( target_data ) as [Len Buffer]

FROM sys.dm_xe_session_targets st (nolock)

JOIN sys.dm_xe_sessions s (nolock) ON

s.address = st.event_session_address

WHERE

s.name = ''

-- <<<< Target File Mode >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

exec master.dbo.xp_cmdshell 'Dir "*.*"'

----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

--*********************************************************************************************

-----------------------------------------------------------------------------------------------

-- Retrieve XEvents output

SET NOCOUNT ON

DECLARE @outputfile varchar(500)='' -- !!!! Edit your custom path here

---------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

-- <<<< Target File Mode >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

-- STEP: Get the Events captured - Note that the session can be still running & this can be repeated

IF (object_id( 'tempdb..#EventXML' ) IS NOT NULL) DROP TABLE #EventXML ;

DECLARE

@path NVARCHAR(260) = @outputfile+'*',

@mdpath NVARCHAR(260) = @outputfile+'*.xem',

@initial_file_name NVARCHAR(260) = NULL,

@initial_offset BIGINT = NULL

Select

Identity(int,1,1) as ID

,cast

(

Replace(

E.event_data, char(3), '?'

) as xml

) as X

into #EventXML

FROM

master.sys.fn_xe_file_target_read_file (@path, @mdpath, @initial_file_name, @initial_offset) E

----------------------------------------------------------------------------------------------

-- STEP: Shred the XML for the above event types

IF (object_id( 'tempdb..#EventDetail' ) IS NOT NULL) DROP TABLE #EventDetail ;

--Shred the XML

SELECT

node.value('./@timestamp', 'datetime') AS event_time,

node.value('./@name', 'varchar(4000)') AS event_name

,CASE node.value('./@name', 'varchar(4000)')

WHEN 'error_reported' THEN node.value('(./data)[5]', 'varchar(4000)')

ELSE NULL

END AS [Message]

,CASE node.value('./@name', 'varchar(4000)')

WHEN 'error_reported' THEN node.value('(./data)[1]', 'int')

ELSE NULL

END AS Error_Value

,CASE node.value('./@name', 'varchar(4000)')

WHEN 'error_reported' THEN node.value('(./action)[7]', 'varchar(50)')

WHEN 'sp_statement_completed' THEN node.value('(./action)[1]', 'varchar(50)')

ELSE node.value('(./action)[2]', 'varchar(50)')

END AS activity_guid

,cast(null as int) as activity_sequence

,cast

(

CASE

WHEN node.value('./@name', 'varchar(4000)') IN ('sp_statement_starting', 'error_reported') THEN node.value('(./action)[1]', 'varchar(4000)')

ELSE NULL

END

as xml

) AS TSql_stack

,CASE node.value('./@name', 'varchar(4000)')

WHEN 'error_reported' THEN node.value('(./action)[2]', 'varchar(4000)')

ELSE NULL

END AS SQL_Text

,CASE node.value('./@name', 'varchar(4000)')

WHEN 'error_reported' THEN node.value('(./action)[3]', 'int')

ELSE NULL

END AS [DBID]

,cast(null as int) as ObjectID

,CASE node.value('./@name', 'varchar(4000)')

WHEN 'error_reported' THEN node.value('(./action)[4]', 'varchar(256)')

ELSE NULL

END AS [UserName]

,CASE node.value('./@name', 'varchar(4000)')

WHEN 'error_reported' THEN node.value('(./action)[5]', 'varchar(256)')

ELSE NULL

END AS [AppName]

,CASE node.value('./@name', 'varchar(4000)')

WHEN 'error_reported' THEN node.value('(./action)[6]', 'varchar(256)')

ELSE NULL

END AS [HostName]

,cast(null as varbinary(1000) ) AS handle

,cast( null as int) as offsetstart

,cast( null as int) as offsetend

,cast(null as varchar(4000) ) as Statement_Text

,cast( null as sysname) as [DatabaseName]

,cast(null as sysname) as [ObjectName]

,#EventXML.*

INTO #EventDetail

FROM #EventXML

CROSS APPLY #EventXML.x.nodes('//event') n (node)

-- Select count(*) as Events FROM #EventDetail

-- SELECT * FROM #EventDetail

----------------------------------------------------------------------------------------------

-- STEP: Separate Activity GUID from Sequence number - for sorting later on (should be combined w above step)

Update D Set

activity_sequence = CONVERT(int, RIGHT(activity_guid, LEN(activity_guid) - 37))

,activity_guid = CONVERT(uniqueidentifier, LEFT(activity_guid, 36))

FROM #EventDetail D

-- SELECT * FROM #EventDetail

----------------------------------------------------------------------------------------------

-- STEP: Extract handles & Offsets (should be combined w above step)

--Get the SQL handles

Update D Set

Handle = CONVERT(varbinary(1000), frame.node.value('@handle', 'varchar(1000)'), 1)

,offsetstart = frame.node.value('@offsetStart', 'int')

,offsetend = frame.node.value('@offsetEnd', 'int')

FROM #EventDetail D

OUTER APPLY D.tsql_stack.nodes('(/frame)[1]') frame (node)

-- SELECT * FROM #EventDetail

----------------------------------------------------------------------------------------------

-- STEP: For each handle, grab the SQL text (should be combined w single table above)

Update D Set

statement_text = Left( SUBSTRING(t.text, (IsNull(offsetstart,0)/2) + 1, ((case when IsNull(offsetend,0) > 0 then offsetend else 2*IsNull(len(t.text),0) end - IsNull(offsetstart,0))/2) + 1) , 4000 )

,[DatabaseName] = DB.Name

,objectid = T.objectid

FROM

#EventDetail D

cross APPLY sys.dm_exec_sql_text(D.handle) t

inner join master.sys.sysdatabases db (nolock) on db.dbid = D.dbid

-- select count(*) as Results from #EventDetail

----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

-- Dereference ObjectName

set nocount on

Declare @Tbl table ( Name sysname )

Declare @Stmt varchar(max)

Declare @DBName sysname

Declare @ObjectName sysname

Declare @ObjectId int

DECLARE curObj CURSOR

FOR Select Distinct [DatabaseName], objectid from #EventDetail where ObjectName is null and [DatabaseName] is not null

OPEN curObj

WHILE (1=1)

BEGIN

FETCH NEXT FROM curObj INTO @DBName, @ObjectId

IF (@@fetch_status <> 0)

break

Set @Stmt = 'select Name from ' + @DBName + '.sys.sysobjects (nolock) where id = ' + convert( varchar(10), @ObjectId)

Insert into @Tbl

exec ( @Stmt )

Set @ObjectName = null

Select @ObjectName = Name from @Tbl

Delete from @Tbl

Update #EventDetail Set ObjectName = @ObjectName where [DatabaseName] = @DBName and objectid = @ObjectId

END

CLOSE curObj

DEALLOCATE curObj

set nocount off

-- Show Details

Select

getdate() as now

,@@servername as Server_Name

,count(*) as [# of Errors]

,[message]

,[DatabaseName]

,[ObjectName]

,min([statement_text]) as Ex1_statement_text

,max([statement_text]) as Ex2_statement_text

,min([SQL_Text]) as Ex1_SQL_Text

,max([SQL_Text]) as Ex2_SQL_Text

--,[event_name]

,min([event_time]) as Min_event_time

,max([event_time]) as Max_event_time

,[Error_Value]

,[AppName]

,min([HostName]) as Ex1_HostName

,max([HostName]) as Ex2_HostName

,min([UserName]) as Ex1_UserName

,max([UserName]) as Ex2_UserName

from #EventDetail

where

IsNull([Error_Value],0) < 50000

group by

[DatabaseName]

,[ObjectName]

,[event_name]

,[Error_Value]

,[message]

,[AppName]

order by

[# of Errors] desc

Introducing SQL Server Extended Events

Información util acerca de las bases de datos

2011-10-03T12:15:00.000-03:00

Este es mi script para ver informacion acerca de todas las bases de datos disponibles en la instancia del SQL Server
- Bases de datos
- id
- Nombre
- Tamaño
- Disco en el que esta alojado el archivo de datos y log
- tamaños de los archivos de datos y log
- Espacio libre en disco
- Porcentaje de utilización del archivo
- Valores de Auto growth
- Recovery Model
- Compativility Level
- Ultimo backup (Full y Transacion Log)
- Ultimo restore

Archivo Principal

-- MAIN SCRIPT

SET NOCOUNT ON

IF (object_id( 'tempdb..#TMPFIXEDDRIVES' ) IS NOT NULL) DROP TABLE #TMPFIXEDDRIVES

IF (object_id( 'tempdb..#TMPSPACEUSED' ) IS NOT NULL) DROP TABLE #TMPSPACEUSED

IF (object_id( 'tempdb..#HDB' ) IS NOT NULL) DROP TABLE #HDB

CREATE TABLE #TMPFIXEDDRIVES (

DRIVE CHAR(1),

MBFREE INT)

INSERT INTO #TMPFIXEDDRIVES

EXEC xp_FIXEDDRIVES

CREATE TABLE #TMPSPACEUSED (

DBNAME VARCHAR(255),

FILEID INT,

FILENME VARCHAR(255),

SPACEUSED FLOAT)

--// SP_HELPDB

SET NOCOUNT ON

CREATE TABLE #HDB (

name sysname not null,

db_size varchar(25) not null,

owner varchar(40) not null,

dbid int not null,

created smalldatetime not null,

status varchar(500) not null,

compatibility_level int not null

)

INSERT INTO #HDB exec sp_helpdb;

--select * from #HDB

INSERT INTO #TMPSPACEUSED

EXEC( 'sp_msforeachdb''use [?]; Select ''''?'''' DBName,fileid, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')

SELECT @@servername as servername,

A.Database_id as DBid,

A.NAME AS DATABASENAME

,CASE D.FILEID

WHEN 1 THEN ltrim(XX.db_size)

ELSE NULL

END as db_size

,CASE D.FILEID

WHEN 1 THEN XX.owner

ELSE NULL

END as owner

,CASE D.FILEID

WHEN 1 THEN XX.created

ELSE NULL

END as created

,C.DRIVE,

C.MBFREE AS DISKFREE_MB,

D.FILEID as FileId,

B.NAME AS FILENAME,

CASE B.TYPE

WHEN 0 THEN 'DATA'

ELSE TYPE_DESC

END AS FILETYPE,

(B.SIZE * 8 / 1024)AS FILESIZE_MB,

ROUND((B.SIZE * 8 / 1024) - (D.SPACEUSED / 128),2) as SPACEFREE_MB,

ROUND(100-((((B.SIZE * 8 / 1024) - (D.SPACEUSED / 128))*100)/

CASE(B.SIZE * 8 / 1024)

WHEN 0 THEN 1

ELSE (B.SIZE * 8 / 1024)

END

),2) as [%USED],

b.size,

b.max_size,

b.growth,

b.is_percent_growth,

CASE (B.SIZE * 8 / 1024)

WHEN 0 THEN 1 ELSE 0 END as VALID,

B.PHYSICAL_NAME,

CASE B.TYPE

WHEN 0 THEN A.recovery_model_desc

ELSE NULL

END AS REC_MODEL,

CASE B.TYPE

WHEN 0 THEN A.compatibility_level

ELSE NULL

END AS COMP_LEVEL

,CASE D.FILEID

WHEN 1 THEN BR.last_backup_finish_date

ELSE NULL

END as [Backup]

,CASE D.FILEID

WHEN 1 THEN BR.last_TRLog_backup_finish_date

ELSE NULL

END as TRBackup

,CASE D.FILEID

WHEN 1 THEN BR.last_restore_date

ELSE NULL

END as [Restore]

FROM SYS.DATABASES A

JOIN SYS.MASTER_FILES B

ON A.DATABASE_ID = B.DATABASE_ID

JOIN #TMPFIXEDDRIVES C

ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE

JOIN #TMPSPACEUSED D

ON A.NAME = D.DBNAME

AND B.NAME = D.FILENME

INNER JOIN #HDB XX on XX.dbid= A.Database_id

INNER JOIN

(

SELECT D.database_id,B.last_backup_finish_date,TR.last_TRLog_backup_finish_date,R.last_restore_date

FROM sys.databases D

LEFT JOIN (

SELECT

BS.database_name

,max(BS.backup_finish_date) as last_backup_finish_date

FROM

msdb.dbo.backupset BS (NOLOCK)

INNER JOIN

msdb.dbo.backupmediafamily MF(NOLOCK) ON BS.media_set_id = MF.media_set_id

WHERE

BS.backup_start_date >= CAST(CONVERT(varchar(10),dateadd(mm,-3,getdate()),120) AS datetime)

AND BS.server_name = @@servername and BS.type='D'

GROUP BY

BS.database_name

) B on D.name=B.database_name

LEFT JOIN (

SELECT

BS.database_name

,max(BS.backup_finish_date) as last_TRLog_backup_finish_date

FROM

msdb.dbo.backupset BS (NOLOCK)

INNER JOIN

msdb.dbo.backupmediafamily MF(NOLOCK) ON BS.media_set_id = MF.media_set_id

WHERE

BS.backup_start_date >= CAST(CONVERT(varchar(10),dateadd(mm,-1,getdate()),120) AS datetime)

AND BS.server_name = @@servername and BS.type='L'

GROUP BY

BS.database_name

) TR on D.name=TR.database_name

LEFT JOIN (

SELECT

rh.destination_database_name,

max(rh.restore_date) as last_restore_date

FROM

msdb.dbo.restorehistory rh (NOLOCK)

INNER JOIN msdb.dbo.backupset BS (NOLOCK) ON rh.backup_set_id=BS.backup_set_id

WHERE

BS.type= 'D' AND

RH.restore_date >=CAST(CONVERT(varchar(10),dateadd(mm,-3,getdate()),120) AS datetime)

GROUP BY

rh.destination_database_name

) R on D.name=R.destination_database_name

) BR on A.Database_id=BR.database_id

--WHERE A.NAME <> 'model'

ORDER BY DATABASENAME

DROP TABLE #TMPFIXEDDRIVES

DROP TABLE #TMPSPACEUSED

DROP TABLE #HDB