SQL Server LIMS Monitoring Scripts
PowerShell scripts for SQL Server performance monitoring in LIMS environments
region SQL Server Connection and Health
1. SQL Server connection tester with detailed diagnostics
function Test-SQLServerForLIMS {
param(
[string]$ServerName = "localhost",
[string]$DatabaseName = "LIMS",
[string]$Username,
[string]$Password,
[switch]$IntegratedSecurity = $true,
[int]$TimeoutSeconds = 30
)
# Build connection string
if ($IntegratedSecurity) {
$connectionString = "Server=$ServerName;Database=$DatabaseName;Integrated Security=true;Connection Timeout=$TimeoutSeconds;"
} else {
$connectionString = "Server=$ServerName;Database=$DatabaseName;User Id=$Username;Password=$Password;Connection Timeout=$TimeoutSeconds;"
}
Write-Host "Testing SQL Server connection for LIMS..." -ForegroundColor Yellow
Write-Host "Server: $ServerName" -ForegroundColor Gray
Write-Host "Database: $DatabaseName" -ForegroundColor Gray
try {
# Test basic connectivity
$stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$connection.Open()
# Get server information
$command = New-Object System.Data.SqlClient.SqlCommand("SELECT @@VERSION, @@SERVERNAME, DB_NAME(), SYSTEM_USER, GETDATE()", $connection)
$reader = $command.ExecuteReader()
$serverInfo = @{}
if ($reader.Read()) {
$serverInfo = @{
Version = $reader[0].ToString()
ServerName = $reader[1].ToString()
Database = $reader[2].ToString()
User = $reader[3].ToString()
ServerTime = $reader[4]
}
}
$reader.Close()
# Test a simple query performance
$testCommand = New-Object System.Data.SqlClient.SqlCommand("SELECT COUNT(*) FROM sys.tables", $connection)
$testResult = $testCommand.ExecuteScalar()
$stopwatch.Stop()
$connection.Close()
$result = [PSCustomObject]@{
Status = "Success"
Server = $ServerName
Database = $DatabaseName
ResponseTimeMs = $stopwatch.ElapsedMilliseconds
SqlServerVersion = $serverInfo.Version.Split("`n")[0]
ServerName = $serverInfo.ServerName
ConnectedAs = $serverInfo.User
ServerTime = $serverInfo.ServerTime
TableCount = $testResult
Timestamp = Get-Date
}
Write-Host "✓ Connection successful" -ForegroundColor Green
Write-Host "Response time: $($stopwatch.ElapsedMilliseconds)ms" -ForegroundColor Gray
Write-Host "Connected as: $($serverInfo.User)" -ForegroundColor Gray
Write-Host "Server time: $($serverInfo.ServerTime)" -ForegroundColor Gray
return $result
}
catch {
Write-Host "✗ Connection failed" -ForegroundColor Red
Write-Host "Error: $($_.Exception.Message)" -ForegroundColor Red
# Provide specific guidance for common errors
if ($_.Exception.Message -like "*login failed*") {
Write-Host "Hint: Check username/password or SQL Server authentication mode" -ForegroundColor Yellow
}
elseif ($_.Exception.Message -like "*server was not found*") {
Write-Host "Hint: Check server name, SQL Server service status, and firewall" -ForegroundColor Yellow
}
elseif ($_.Exception.Message -like "*timeout*") {
Write-Host "Hint: Check network connectivity and SQL Server responsiveness" -ForegroundColor Yellow
}
return [PSCustomObject]@{
Status = "Failed"
Server = $ServerName
Database = $DatabaseName
Error = $_.Exception.Message
Timestamp = Get-Date
}
}
}
2. SQL Server performance monitoring for LIMS
function Get-SQLServerLIMSPerformance {
param(
[string]$ServerName = "localhost",
[string]$DatabaseName = "LIMS",
[string]$ConnectionString
)
if (-not $ConnectionString) {
$ConnectionString = "Server=$ServerName;Database=$DatabaseName;Integrated Security=true;Connection Timeout=30;"
}
$performanceQueries = @{
"DatabaseInfo" = @"
SELECT
name AS DatabaseName,
database_id,
create_date,
state_desc AS Status,
recovery_model_desc AS RecoveryModel,
page_verify_option_desc AS PageVerifyOption
FROM sys.databases
WHERE name = '$DatabaseName'
"@
"DatabaseSize" = @"
SELECT
type_desc AS FileType,
name AS LogicalName,
physical_name AS PhysicalName,
CAST(size/128.0 AS DECIMAL(12,2)) AS SizeMB,
CAST(FILEPROPERTY(name,'SpaceUsed')/128.0 AS DECIMAL(12,2)) AS UsedMB,
CAST((size-FILEPROPERTY(name,'SpaceUsed'))/128.0 AS DECIMAL(12,2)) AS FreeMB,
CAST((FILEPROPERTY(name,'SpaceUsed')*100.0)/size AS DECIMAL(5,2)) AS PercentUsed
FROM sys.database_files
"@
"ActiveConnections" = @"
SELECT
program_name,
host_name,
login_name,
COUNT(*) AS ConnectionCount,
COUNT(CASE WHEN status = 'running' THEN 1 END) AS ActiveCount,
COUNT(CASE WHEN status = 'sleeping' THEN 1 END) AS IdleCount
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND database_id = DB_ID('$DatabaseName')
GROUP BY program_name, host_name, login_name
ORDER BY ConnectionCount DESC
"@
"WaitStats" = @"
SELECT TOP 10
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms,
CAST(wait_time_ms / 1000.0 AS DECIMAL(12,2)) AS wait_time_seconds,
CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) AS percentage
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',
'SLEEP_SYSTEMTASK','SQLTRACE_WAIT','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', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
AND wait_time_ms > 0
ORDER BY wait_time_ms DESC
"@
"BlockingSessions" = @"
SELECT
r.session_id AS blocked_session_id,
r.blocking_session_id,
s.login_name AS blocked_user,
s.host_name AS blocked_machine,
s.program_name AS blocked_program,
bs.login_name AS blocking_user,
bs.host_name AS blocking_machine,
bs.program_name AS blocking_program,
r.wait_type,
r.wait_time,
r.last_wait_type,
st.text AS sql_text
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_sessions bs ON r.blocking_session_id = bs.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.blocking_session_id > 0
"@
"LongRunningQueries" = @"
SELECT
r.session_id,
s.login_name,
s.host_name,
s.program_name,
r.status,
r.command,
r.start_time,
DATEDIFF(minute, r.start_time, GETDATE()) AS runtime_minutes,
r.cpu_time,
r.logical_reads,
r.writes,
st.text AS sql_text,
qp.query_plan
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE r.session_id > 50
AND DATEDIFF(minute, r.start_time, GETDATE()) > 5
ORDER BY runtime_minutes DESC
"@
"TopQueries" = @"
SELECT TOP 10
qs.execution_count,
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(12,2)) AS total_elapsed_seconds,
CAST(qs.total_elapsed_time / qs.execution_count / 1000000.0 AS DECIMAL(12,4)) AS avg_elapsed_seconds,
CAST(qs.total_cpu_time / 1000000.0 AS DECIMAL(12,2)) AS total_cpu_seconds,
qs.total_logical_reads,
qs.total_logical_writes,
qs.creation_time,
qs.last_execution_time,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text NOT LIKE '%sys.dm_%'
ORDER BY qs.total_elapsed_time DESC
"@
}
$results = @{}
Write-Host "Gathering SQL Server performance data for LIMS..." -ForegroundColor Yellow
try {
$connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
$connection.Open()
foreach ($query in $performanceQueries.GetEnumerator()) {
Write-Host "Collecting $($query.Key)..." -ForegroundColor Cyan
try {
$command = New-Object System.Data.SqlClient.SqlCommand($query.Value, $connection)
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataset) | Out-Null
$results[$query.Key] = $dataset.Tables[0]
}
catch {
Write-Warning "Failed to execute $($query.Key): $($_.Exception.Message)"
$results[$query.Key] = $null
}
}
$connection.Close()
# Display key findings
Write-Host "`nSQL Server Performance Summary:" -ForegroundColor Green
# Database size
if ($results["DatabaseSize"]) {
$totalSizeMB = ($results["DatabaseSize"] | Measure-Object SizeMB -Sum).Sum
$totalUsedMB = ($results["DatabaseSize"] | Measure-Object UsedMB -Sum).Sum
$usedPercent = [math]::Round(($totalUsedMB / $totalSizeMB) * 100, 1)
Write-Host "Database size: $([math]::Round($totalSizeMB,1))MB ($usedPercent% used)" -ForegroundColor Cyan
}
# Active connections
if ($results["ActiveConnections"]) {
$totalConnections = ($results["ActiveConnections"] | Measure-Object ConnectionCount -Sum).Sum
$activeConnections = ($results["ActiveConnections"] | Measure-Object ActiveCount -Sum).Sum
Write-Host "Connections: $totalConnections total, $activeConnections active" -ForegroundColor Cyan
}
# Blocking sessions
if ($results["BlockingSessions"] -and $results["BlockingSessions"].Rows.Count -gt 0) {
Write-Host "⚠ WARNING: $($results["BlockingSessions"].Rows.Count) blocked sessions detected!" -ForegroundColor Red
} else {
Write-Host "✓ No blocking sessions detected" -ForegroundColor Green
}
# Long running queries
if ($results["LongRunningQueries"] -and $results["LongRunningQueries"].Rows.Count -gt 0) {
$maxRuntime = ($results["LongRunningQueries"] | Measure-Object runtime_minutes -Maximum).Maximum
Write-Host "⚠ WARNING: $($results["LongRunningQueries"].Rows.Count) long-running queries (max: $maxRuntime minutes)" -ForegroundColor Yellow
} else {
Write-Host "✓ No long-running queries detected" -ForegroundColor Green
}
return $results
}
catch {
Write-Error "Failed to gather SQL Server performance data: $($_.Exception.Message)"
return $null
}
}
3. Generate SQL Server health check script
function New-SQLServerLIMSHealthScript {
param(
[string]$OutputPath = "C:\temp\sql_server_lims_health.sql",
[string]$DatabaseName = "LIMS"
)
$sqlScript = @"
-- SQL Server LIMS Health Check Script
-- Generated: $(Get-Date)
-- Database: $DatabaseName
SET NOCOUNT ON
PRINT '================================================'
PRINT 'SQL SERVER LIMS HEALTH CHECK - ' + CONVERT(varchar, GETDATE(), 120)
PRINT '================================================'
PRINT ''
PRINT '1. SERVER INFORMATION'
SELECT
@@SERVERNAME AS ServerName,
@@VERSION AS Version,
SERVERPROPERTY('ProductLevel') AS ServicePack,
SERVERPROPERTY('Edition') AS Edition,
GETDATE() AS CurrentTime
PRINT ''
PRINT '2. DATABASE STATUS'
SELECT
name AS DatabaseName,
state_desc AS Status,
recovery_model_desc AS RecoveryModel,
CAST(size*8.0/1024 AS DECIMAL(10,2)) AS SizeMB
FROM sys.databases
WHERE name = '$DatabaseName'
PRINT ''
PRINT '3. DATABASE FILE USAGE'
USE [$DatabaseName]
SELECT
type_desc AS FileType,
name AS LogicalName,
CAST(size/128.0 AS DECIMAL(12,2)) AS SizeMB,
CAST(FILEPROPERTY(name,'SpaceUsed')/128.0 AS DECIMAL(12,2)) AS UsedMB,
CAST((size-FILEPROPERTY(name,'SpaceUsed'))/128.0 AS DECIMAL(12,2)) AS FreeMB,
CAST((FILEPROPERTY(name,'SpaceUsed')*100.0)/size AS DECIMAL(5,2)) AS PercentUsed
FROM sys.database_files
PRINT ''
PRINT '4. ACTIVE CONNECTIONS'
SELECT
program_name,
login_name,
COUNT(*) AS ConnectionCount,
COUNT(CASE WHEN status = 'running' THEN 1 END) AS ActiveCount
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND database_id = DB_ID('$DatabaseName')
GROUP BY program_name, login_name
HAVING COUNT(*) > 2
ORDER BY ConnectionCount DESC
PRINT ''
PRINT '5. BLOCKING SESSIONS (CRITICAL ISSUE IF ANY)'
IF EXISTS (SELECT 1 FROM sys.dm_exec_requests WHERE blocking_session_id > 0)
BEGIN
PRINT '*** BLOCKING DETECTED ***'
SELECT
r.session_id AS BlockedSession,
r.blocking_session_id AS BlockingSession,
s.login_name AS BlockedUser,
bs.login_name AS BlockingUser,
r.wait_type,
r.wait_time,
SUBSTRING(st.text, 1, 200) AS SQLText
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_sessions bs ON r.blocking_session_id = bs.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.blocking_session_id > 0
END
ELSE
PRINT 'No blocking sessions detected'
PRINT ''
PRINT '6. LONG RUNNING QUERIES (>5 MINUTES)'
IF EXISTS (SELECT 1 FROM sys.dm_exec_requests r INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id WHERE r.session_id > 50 AND DATEDIFF(minute, r.start_time, GETDATE()) > 5)
BEGIN
SELECT
r.session_id,
s.login_name,
r.status,
r.start_time,
DATEDIFF(minute, r.start_time, GETDATE()) AS RuntimeMinutes,
SUBSTRING(st.text, 1, 200) AS SQLText
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id > 50
AND DATEDIFF(minute, r.start_time, GETDATE()) > 5
ORDER BY RuntimeMinutes DESC
END
ELSE
PRINT 'No long running queries detected'
PRINT ''
PRINT '7. TOP WAIT STATISTICS'
SELECT TOP 10
wait_type,
waiting_tasks_count,
CAST(wait_time_ms / 1000.0 AS DECIMAL(12,2)) AS WaitTimeSeconds,
CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) AS Percentage
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',
'SLEEP_SYSTEMTASK','SQLTRACE_WAIT','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP')
AND wait_time_ms > 1000
ORDER BY wait_time_ms DESC
PRINT ''
PRINT '8. ERROR LOG SUMMARY (RECENT ERRORS)'
EXEC xp_readerrorlog 0, 1, 'error'
EXEC xp_readerrorlog 0, 1, 'fail'
EXEC xp_readerrorlog 0, 1, 'timeout'
PRINT ''
PRINT '9. LIMS-SPECIFIC CHECKS'
-- Check for tables with no recent activity (potential issues)
SELECT
t.name AS TableName,
p.rows AS RowCount,
i.last_user_update,
DATEDIFF(day, i.last_user_update, GETDATE()) AS DaysSinceUpdate
FROM sys.tables t
INNER JOIN sys.dm_db_partition_stats p ON t.object_id = p.object_id AND p.index_id IN (0,1)
LEFT JOIN sys.dm_db_index_usage_stats i ON t.object_id = i.object_id AND i.index_id IN (0,1)
WHERE t.name LIKE '%sample%' OR t.name LIKE '%test%' OR t.name LIKE '%result%'
ORDER BY DaysSinceUpdate DESC
PRINT ''
PRINT 'Health check complete. Review any warnings or errors above.'
PRINT 'Timestamp: ' + CONVERT(varchar, GETDATE(), 120)
"@
$sqlScript | Out-File -FilePath $OutputPath -Encoding UTF8
Write-Host "SQL Server LIMS health check script created: $OutputPath" -ForegroundColor Green
Write-Host "Run this in SSMS: File -> Open -> $OutputPath" -ForegroundColor Cyan
Write-Host "Or from command line: sqlcmd -S servername -d $DatabaseName -i `"$OutputPath`"" -ForegroundColor Cyan
}
endregion
Usage examples
Write-Host @"
SQL SERVER LIMS MONITORING COMMANDS:
-
Test SQL Server connection:
Test-SQLServerForLIMS -ServerName "SQLSERVER01" -DatabaseName "LIMS_PROD" -
Get comprehensive performance data:
$perf = Get-SQLServerLIMSPerformance -ServerName "SQLSERVER01" -DatabaseName "LIMS_PROD"$perf["BlockingSessions"] # View blocking sessions
`$perf["LongRunningQueries"] # View slow queries -
Generate health check script:
New-SQLServerLIMSHealthScript -DatabaseName "LIMS_PROD" -
With SQL authentication:
Test-SQLServerForLIMS -ServerName "SERVER" -DatabaseName "LIMS" -Username "lims_user" -Password "password" -IntegratedSecurity:`$false
"@ -ForegroundColor Green
Last updated: 2025-08-26 20:00 UTC