Virus scan exclusions for SQL Server

As a guide on server hosting SQL Servers , the following file type list should be considered in adding to exclusion lists.
This will assist to minimise performance issues.

1) "?:\Microsoft SQL Server\*"
2)"?:\Program Files\Microsoft SQL Server\MSSQL*\*.* "
3)"All files of type MDF"
4) "All files of type LDF"
5)"All files of type NDF"
6)"All files of type BAK"
7)"All files of type TRN"
8)"All files of type TRC"
9)"All files of type ERRORLOG*"
10) Any other directories relevant to the environment

Source:Jack Vamvas (http://www.sqlserver-dba.com)

Query baseline testing

To retain cosnsistency for baseline testing and assist in query tuning use the following 2 commands:

Use DBCC DROPCLEANBUFFERS to flush out already cached plans.This will force the SQL Statement to recompile intstead of re using the cache available

Use DBCC FREEPROCCACHE to clear buffers from the buffer pool.

Running these 2 commands in between code changes allows a consistent comparision

/****************CODE START*********************
use master
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
************************************************/

Source:Jack Vamvas (http://www.sqlserver-dba.com)

Free space on drives using Powershell

This script  using Powershell , takes a  list of SQL Servers , iterates through
the list and returns the results from the procedure : master.dbo.xp_fixeddrives.
Run this script as a standalone script or as part of a more comprehensive health monitoring snapshot

Depending on your security architecture , there may be a requirement to adjust the security method used to authenticate on the SQL Server

############START#################

foreach ($svr in get-content "C:\MySQLServers.txt"){
    $dt = new-object "System.Data.DataTable"
    $cn = new-object System.Data.SqlClient.SqlConnection >
"server=$svr;database=master;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $svr
    $sql.CommandText = "EXEC master.dbo.xp_fixeddrives"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table -autosize
}

##############FINISH########################

Source:Jack Vamvas (http://www.sqlserver-dba.com)