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 in and checked if there are any blocks.
WHERE blocked <> 0
There were several blocks caused by a naughty spid 80.
When I checked DBCC INPUTBUFFER(80), To my surprise, I got below scary result.
Continue reading “FETCH API_CURSOR0000000000005BE4 Blocking Several Transactions”
FROM [dbo].[alldocversions] v
JOIN [dbo].alldocs d
ON v.id = d.id
GROUP BY v.id,
ORDER BY 4 DESC
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
rows = Sum(CASE
WHEN ( p.index_id < 2 )
AND ( a.type = 1 ) THEN p.rows
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 * from sysmail_event_log
- select * from sysmail_allitems
- select is_broker_enabled from sys.databases where name=’msdb’
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 the time, the index maintenance jobs fail in the middle for large databases due to insufficient storage in log files and the entire rebuild process has to be restarted. This consumes a lot of time and also causes prolonged performance issues.
To tackle this, I have scripts which could resume from where it stopped. Even if the defragmentation(Rebuild/Reorg) fails in the middle due to some reason, it will continue from the table it stopped.
Continue reading “Resumable Auto-Index Rebuild and Reorganize Script”