TheRockStarDBA

/* 
	Created By:		David Wiseman
	Website:		http://www.wisesoft.co.uk
	Description:
	Script to generate reports based on sys.dm_io_virtual_file_stats.
	See: http://msdn.microsoft.com/en-us/library/ms190326.aspx
*/
-- I/O Stats by Database
SELECT d.name AS DatabaseName,
	ROUND(CAST(SUM(num_of_bytes_read+num_of_bytes_written) AS FLOAT) / SUM(SUM(num_of_bytes_read+num_of_bytes_written)) OVER() *100,2) AS [% Total I/O],

TheRockStarDBA

/************************************************************************************************************************************************************************************************************************************
************************************************************************************************************************************************************************************************************************************
Author		: 	Kin Shah
 
Purpose		:	Gives you the disk latency for READ / WRITE operations on DATA and LOG files.
				if Data read/write > 20 ms and Log Read / write > 15 ms then its something to look out for.

				This script generates recommendations as well ..

				This is part of my project that I am working for SQL Server health checker ..... 

TheRockStarDBA

Write-Warning "This code is not meant to be run sequentially, run it in sections following comment instructions"
exit

<##############################################################################
    create transfer file
##############################################################################>

$FilePath = "C:\temp\transfer_file.txt"
$DesiredFileSizeKB = 1024 * 7    # 7 MB

TheRockStarDBA

-- Map a databaseName with a physical device name SybaseASE
select sda.name
	,sde.name
	,sde.phyname
from master..sysdatabases sda
	,master..sysdevices sde
	,master..sysusages su
where su.dbid = sda.dbid
	and su.vdevno = sde.vdevno
	and sda.name like '%temp%' --- CHANGE HERE with your database Name!

TheRockStarDBA

/************************************************************************************************************************************************************************************************************************************
************************************************************************************************************************************************************************************************************************************
Author	:	Kin Shah
Site	:	http://sqlsybase.azurewebsites.net/

The server side trace SP uses XP_CMDSHELL for creating directories.
Change :	@FilePath = 'E:\trace\' as per your location
			@Duration = 61 -- this is the trace time in minutes .. change as per your needs

If you want to look at different columns for a given event then use 

TheRockStarDBA

/*
 +-+-+-+-+-+-+        +-+ +-+-+-+ +-+-+-+-+                                                                                                                                                                                    
 |A|u|t|h|o|r|        |:| |K|i|n| |S|h|a|h|                                                                                                                                                                                    
 +-+-+-+-+-+-+-+      +-+ +-+-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+ +-+-+-+-+-+-+ +-+-+-+-+                                                                                                                                            
 |P|u|r|p|o|s|e|      |:| |R|e|c|o|m|m|e|n|d| |p|r|o|p|e|r| |t|e|m|p|d|b| |s|i|z|e|                                                                                                                                            
 +-+-+-+-+-+-+-+-+-+  +-+ +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ +-+-+ +-+-+-+-+-+-+-+-+ +-+-+

TheRockStarDBA

CREATE VIEW dbo.MyPatchLevel
AS
WITH MostRecentBuild AS
(
    SELECT
        SRC.Build
    ,   SRC.[Release Date]
    ,   SRC.SimpleVersion
    ,   SRC.[KB / Description]
    FROM

TheRockStarDBA

/**************************************************************************************
Author:		KIN SHAH
Date	:	03/02/2011

Adapt the script as per your env --> places to change ---- CHANGE HERE !!

This script will create 
	- a blocking detection alert
	- table to hold blocking information
	- modify the 'blocked process threshold' sp_configure option to 5 mins (300 sec) 

TheRockStarDBA

/**************************************************************************************
Author:		KIN SHAH
Date	:	10/10/2015
Adapt the script as per your env --> places to change ---- CHANGE HERE !!
This script will create 
	- a trace file that will rollover after 1 hr
	- the trace file will capture apps/users/host machines that are connecting using "SA" account as well as
	- can be used for server migrations - as it will capture who is connecting to your database server instance.
Disclaimer
The views expressed on my posts on this site are mine alone and do not reflect the views of my company. All posts of mine are provided "AS IS" with no warranties, and confers no rights.

TheRockStarDBA

SET NOCOUNT ON
GO

/***********************************************************************************************************
************************************************************************************************************
Author			:		KIN SHAH - Sr.DBA (MS SQL Server and SybaseASE)

Date			:		Feb 18th 2014

Purpose			:		Find the Last_Run_Duration with Avg, Max and Min for SQL Agent RUN Times.