SQL Server Script: Look for Blocking


SELECT tl.resource_type ,
tl.resource_database_id ,
tl.resource_associated_entity_id ,
tl.request_mode ,
tl.request_session_id ,
wt.blocking_session_id ,
wt.wait_type ,
wt.wait_duration_ms
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.dm_os_waiting_tasks AS wt
ON tl.lock_owner_address = wt.resource_address
ORDER BY wait_duration_ms DESC ;


SQL Server Script: Longest Running Query


SELECT DISTINCT TOP 100
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO


SQL Server: What Version of SQL Server is Running

Log into SQL Server Management Studio, click "New Query", copy/paste the following, and click Execute:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

SQL Server Script: Table and row count information

USE {Database Name Here}
SELECT OBJECT_NAME(ps.[object_id]) AS [TableName] ,
i.name AS [IndexName] ,
SUM(ps.row_count) AS [RowCount]
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.indexes AS i ON i.[object_id] = ps.[object_id]
AND i.index_id = ps.index_id
WHERE i.type_desc IN ( 'CLUSTERED', 'HEAP' )
AND i.[object_id] > 100
AND OBJECT_SCHEMA_NAME(ps.[object_id]) <> 'sys'
GROUP BY ps.[object_id] ,
i.name
ORDER BY SUM(ps.row_count) DESC ;


SQL Server Script: Get Free Space in TempDB


SELECT SUM(unallocated_extent_page_count) AS [free pages] ,
( SUM(unallocated_extent_page_count) * 1.0 / 128 ) AS [free space in MB]
FROM sys.dm_db_file_space_usage ;

-- Quick TempDB Summary
SELECT SUM(user_object_reserved_page_count) * 8.192 AS [UserObjectsKB] ,
SUM(internal_object_reserved_page_count) * 8.192 AS [InternalObjectsKB] ,
SUM(version_store_reserved_page_count) * 8.192 AS [VersonStoreKB] ,
SUM(unallocated_extent_page_count) * 8.192 AS [FreeSpaceKB]
FROM sys.dm_db_file_space_usage ;


SQL Server Script: Connections by IP


SELECT ec.client_net_address ,
es.[program_name] ,
es.[host_name] ,
es.login_name ,
COUNT(ec.session_id) AS [connection count]
FROM sys.dm_exec_sessions AS es
INNER JOIN sys.dm_exec_connections AS ec
ON es.session_id = ec.session_id
GROUP BY ec.client_net_address ,
es.[program_name] ,
es.[host_name] ,
es.login_name
ORDER BY ec.client_net_address ,
es.[program_name] ;


SQL Server Script: Backup Job Status


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
DB_NAME(er.[database_id]) [DatabaseName]
,er.[session_id] AS [SessionID]
,er.[command] AS [CommandType]
,est.[text] [StatementText]
,er.[status] AS [Status]
,CONVERT(DECIMAL(5, 2), er.[percent_complete]) AS [Complete_Percent]
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m]
,er.[last_wait_type] [LastWait]
,er.[wait_resource] [CurrentWait]
FROM sys.dm_exec_requests AS er
INNER JOIN sys.dm_exec_sessions AS es ON er.[session_id] = es.[session_id]
CROSS APPLY sys.dm_exec_sql_text(er.[sql_handle]) est
WHERE er.[command] = 'BACKUP DATABASE'


SQL Server Script: Back up All Databases

Run this script to back up all SQL Databases to the specified location. The portion highlighted in Yellow is where you would put the location you would like the databases saved in... All databases will be backed up as {Day of Week} - {Database Name}.bak

---


DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR
select
DATABASE_NAME = db_name(s_mf.database_id)
from
sys.master_files s_mf
where
-- ONLINE
s_mf.state = 0

-- Only look at databases to which we have access
and has_dbaccess(db_name(s_mf.database_id)) = 1

-- Not master, tempdb or model
and db_name(s_mf.database_id) not in ('Master','tempdb','model')
group by s_mf.database_id
order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
declare @DBFileName varchar(256)
set @DBFileName = datename(dw, getdate()) + ' - ' +
replace(replace(@DBName,':','_'),'\','_')

exec ('BACKUP DATABASE [' + @DBName + '] TO DISK = N''c:\SQL backups\' +
@DBFileName + '.bak' + ''' WITH NOFORMAT, INIT, NAME = N''' +
@DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 100')

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR


---

IIS 7.x: List all sites and their bindings

From an elevated command prompt: CD %windir%/system32/inetsrv.
Type Appcmd list site and hit Enter

SQL Server Script: Move TempDB Script

Move TempDB Script


ALTER DATABASE tempdb
MODIFY FILE(NAME='tempdev', FILENAME='\tempdb.mdf')
ALTER DATABASE tempdb
MODIFY FILE(NAME='templog', FILENAME='\templog.ldf')


Replace with the file path of the new location you want the TempDB files to be... If you have multiple tempDB files, you will need to run create the MODIFY FILE line for each file name...

You must restart sql server for this to take effect!

SQL Server Script: Split TempDB's Based on Number of Procs

SQL Server: Split TempDB Script


USE master
GO

CREATE TABLE #numprocs
(
[Index] INT,
[Name] VARCHAR(200),
Internal_Value VARCHAR(50),
Character_Value VARCHAR(200)
)

DECLARE @BASEPATH VARCHAR(200)
DECLARE @PATH VARCHAR(200)
DECLARE @SQL_SCRIPT VARCHAR(500)
DECLARE @CORES INT
DECLARE @FILECOUNT INT
DECLARE @SIZE INT
DECLARE @GROWTH INT
DECLARE @ISPERCENT INT

INSERT INTO #numprocs
EXEC xp_msver

SELECT @CORES = Internal_Value FROM #numprocs WHERE [Index] = 16
PRINT @CORES

SET @BASEPATH = (select SUBSTRING(physical_name, 1, CHARINDEX(N'tempdb.mdf', LOWER(physical_name)) - 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 2 and FILE_ID = 1)
PRINT @BASEPATH

SET @FILECOUNT = (SELECT COUNT(*)
FROM master.sys.master_files
WHERE database_id = 2 AND TYPE_DESC = 'ROWS')

SELECT @SIZE = size FROM master.sys.master_files WHERE database_id = 2 AND FILE_ID = 1
SET @SIZE = @SIZE / 128

SELECT @GROWTH = growth FROM master.sys.master_files WHERE database_id = 2 AND FILE_ID = 1
SELECT @ISPERCENT = is_percent_growth FROM master.sys.master_files WHERE database_id = 2 AND FILE_ID = 1

WHILE @CORES > @FILECOUNT
BEGIN
SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
ADD FILE
(
FILENAME = ''' + @BASEPATH + 'tempdb' + RTRIM(CAST(@CORES as CHAR)) + '.ndf'',
NAME = tempdev' + RTRIM(CAST(@CORES as CHAR)) + ',
SIZE = ' + RTRIM(CAST(@SIZE as CHAR)) + 'MB,
FILEGROWTH = ' + RTRIM(CAST(@GROWTH as CHAR))
IF @ISPERCENT > 0
SET @SQL_SCRIPT = @SQL_SCRIPT + '%'
SET @SQL_SCRIPT = @SQL_SCRIPT + ')'

EXEC(@SQL_SCRIPT)
SET @CORES = @CORES - 1
END
GO
DROP TABLE #numprocs