We could easily grant execute permission to a user on all stored procedures on a database as below. USE GO GRANT EXECUTE TO GO But…
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…
Problem Detaching a database with drop all connections enabled was running continuously without completing. When checked what was causing the block or lock, I found…
Below query will be helpful in finding all missing indexes in a database. I got this query from one of my friend. I am not…
Use below code and make necessary modifications to make it work as per your needs. I am extremely sorry that I couldn’t test below code.…
I had a requirement from application team that I should rename a database when the application is running and the transactions were happening. The reason…