Posts

Showing posts from October, 2019

tempdb monitoring queries.

--Script to find historic tempdb usage by session. SELECT TOP 5 * FROM sys.dm_db_task_space_usage WHERE session_id > 50 ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC ; -- Finding the top 5 sessions running tasks that use tempdb SELECT * FROM sys.dm_db_session_space_usage WHERE session_id > 50 ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC ; -- Script to total tempdb usage by type across all files. SELECT SUM(user_object_reserved_page_count) AS user_object_pages, SUM(internal_object_reserved_page_count) AS internal_object_pages, SUM(version_store_reserved_page_count) AS version_store_pages, total_in_use_pages = SUM(user_object_reserved_page_count) + SUM(internal_object_reserved_page_count) + SUM(version_store_reserved_page_count), SUM(unallocated_extent_page_count) AS total_free_pages FROM sys.dm_db_file_space_usage ; --- To get text and plan SELECT session_id, text, query_plan FROM sys...

SQL Server Services and cluster info along with last reboot time of Server

The below powershell script provide the information of when the server was last rebooted and the status of SQL Server Services and Cluster resources if the sql server is clustered environment. clear $servernames = "C:\ServerList\ServerList.txt" Get-Content $servernames | ForEach-Object { $ComputerName = $_ $S = Get-WmiObject ` -Class Win32_SystemServices ` -ComputerName $ComputerName IF ( $S | Select PartComponent | Where-Object { $_ -like "*ClusSvc*" }) { Write-OutPut " $ComputerName is clustered" Invoke-command ` -ComputerName $ComputerName ` -ScriptBlock { Get-WmiObject Win32_operatingsystem | Select-Object CSName , @{Label = 'LastBootTime' ;EXPRESSION = { $_ . ConverttoDateTime( $_ . lastbootuptime)}} } Invoke-Command ` -ComputerName $ComputerName ` -ScriptBlock { Get-Service -Name "*sql*" } | Format-Table -AutoSize Invoke-Command ` -ComputerName ...