In this post I decided to add the scripts that occasionally helped me to manage Microsoft SQL servers. Those are small scripts that I gathered searching the WEB (ok, google), some are as is and some are edited to cover the current needs.

Script #1 | Get the waste space of a database

With this script we can get the Total Plan Cache Size In MBs, the Total Single Used Plans In MBs and the % of Wasted space from single used plans.

 

declare @TotalPlanCacheSizeInMBs decimal(18,2), @TotalSingleUsedPlansInMBs decimal(18,2)
select @TotalPlanCacheSizeInMBs = (sum(cast(size_in_bytes as decimal(18,2)))/1024)/1024
, @TotalSingleUsedPlansInMBs = (sum(cast((case when usecounts = 1 and objtype in ('Adhoc','Prepared') then size_in_bytes else 0 end) as decimal(12,2)))/1024)/1024
from sys.dm_exec_cached_plans
select @TotalPlanCacheSizeInMBs AS [Total Plan Cache Size In MBs]
, @TotalSingleUsedPlansInMBs AS [Total Single Used Plans In MBs]
, cast((@TotalSingleUsedPlansInMBs * 100 ) / @TotalPlanCacheSizeInMBs as decimal(18,2)) AS [% of Wasted space from single used plans]
go

 

Script #2 | Get the Current Backup-Restore Progress

With this script we can see if currently there is a Backup or restore progress running on the server, for all databases. Also if you have started a backup or restore process from the GUI and accidentally closed the progress window then with this script you can monitor the progress.

USE master

SELECT
session_id as SPID,
CONVERT(VARCHAR(50),start_time,100) AS start_time,
percent_complete,
CONVERT(VARCHAR(50),dateadd(second,estimated_completion_time/1000, getdate()),100) as estimated_completion_time,
command, a.text AS Query
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command LIKE 'BACKUP%' OR r.command LIKE 'RESTORE%'

 

Script #3 | Get who is connected

With this script we can see who is currently connected and to what database.

SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame
;

 

Script #4 | Get the Index Fragmentation

With this script we can get the Index Fragmentation of a database. It will return the Table, the Index, the Average Fragmentation in % and the Page Count.

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.page_count desc

 

That’s all until now, I will try to keep updating this post.