One fine morning, users started complaining my SQL server instance performance…They sent me a screenshot which indirectly says, their queries have timed out. I quickly logged…
SELECT v.[id], d.dirname, Count(v.[version]) MaxNumberOfVersions, Sum(v.[size]) TotalDocLibSize, Max(v.[size]) LargestDocSize FROM [dbo].[alldocversions] v JOIN [dbo].alldocs d ON v.id = d.id GROUP BY v.id, d.dirname ORDER BY 4 DESC
SELECT o.name, reservedpages = Sum(a.total_pages), usedpages = Sum(a.used_pages), reservedMB = ( Sum(a.total_pages) * 8 / 1024 ), usedMB = ( Sum(a.used_pages) * 8 / 1024 ), pages = Sum(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END), rows = Sum(CASE WHEN ( p.index_id < 2 ) AND ( a.type = 1 ) THEN p.rows ELSE 0 END) FROM sys.objects o JOIN sys.partitions p ON p.object_id = o.object_id JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE o.type = ‘U’ GROUP BY o.name ORDER BY 3 DESC
Use below queries one by one and hopefully, you will be able to find and fix your Database Mail issue. sysmail_help_queue_sp @queue_type = ‘Mail’ select…
As part of SQL server database maintenance, the indexes have to be defragmented on regular basis by rebuild or reorganize for consistant performance. Most of…