Find the percentage of fragmentation in a table for a database (What’s the permission level needed?)

I can find out the percentage of fragmentation for a particular table using the below query: USE AdventureWorksGOSELECT object_name(IPS.object_id) AS [TableName], SI.name AS [IndexName], IPS.Index_type_desc, IPS.avg_fragmentation_in_percent, IPS.avg_fragment_size_in_pages, IPS.avg_page_space_used_in_percent, IPS.record_count, IPS.ghost_record_count, IPS.fragment_count, IPS.avg_fragment_size_in_pagesFROM sys.dm_db_index_physical_stats(db_id(N’master’), NULL, NULL, NULL , ‘DETAILED’) IPS JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id JOIN sys.indexes SI WITH (nolock) ON IPS.object_id…


DBCC CHECKDB – Good to know!

If you’re a DBA, then you’ll need the below content at least once in your career. Your company needs to run DBCC CHECKDB on all system and user databases to keep it safe from corruption and take necessary actions when in trouble. Sometimes 825 errors get converted to 824 eventually. And as a DBA you…

1

SQL Server Agent jobs collecting useful information, how to save them?

Hi, I was working on an OLTP environment where tempdb started growing crazy! Before even I got a hold of the server, someone restarted the SQL Service and had a smile on his face (thinking he solved the problem), well, tempdb hadn’t learnt it’s lesson from this guy 😀 I got hold of queries from…

1

What should my backup strategies be for system databases?

If you’re a DBA, you’ll say, it’s simple; in the maintenance plan, just select ‘all databases’ or if you rely on tape backups (file level), then there’s no question.   Why would I write a blog about it? Everything goes fine until we have the system databases working, the moment we don’t have backups for…

1

What did I learn?

This blog is slightly non-technical rather it’s a bit about myself. I worked in Microsoft IGTSC for 4 years. Spent sleepless nights (because I was working :p) and spent good days during days while I was working for US after-hours business on SQL Azure technologies. In this period, I gained a lot of technical skills….