Skip to content

Instantly share code, notes, and snippets.

@gitfvb
Created January 8, 2024 10:45
Show Gist options
  • Save gitfvb/776527653bfe61779576b71cb2ddeffe to your computer and use it in GitHub Desktop.
Save gitfvb/776527653bfe61779576b71cb2ddeffe to your computer and use it in GitHub Desktop.
Helpful queries for SQLServer

Show tables with rows and size

SELECT
t.NAME AS TableName,
MAX(p.rows) AS RowCounts,
(SUM(a.total_pages) * 8) / 1024.0 as TotalSpaceMB,
(SUM(a.used_pages) * 8) / 1024.0 as UsedSpaceMB,
(SUM(a.data_pages) * 8) /1024.0 as DataSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE i.OBJECT_ID > 255
AND i.index_id IN (0,1)
GROUP BY t.NAME
ORDER BY TotalSpaceMB DESC

Clean up JobQueue

delete from JobQueueCommands where JobId in (
select JobId 
  FROM [WS_GV].[dbo].[JobQueue]
where  DATEDIFF(DAY, TimeAdded, GetDate()) >= 30
)
delete from JobQueueResults where JobId in (
select JobId 
  FROM [WS_GV].[dbo].[JobQueue]
where  DATEDIFF(DAY, TimeAdded, GetDate()) >= 30
)
delete 
  FROM [WS_GV].[dbo].[JobQueue]
where  DATEDIFF(DAY, TimeAdded, GetDate()) >= 30
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment