Auto Index Maintenance for All Databases in a SQL Server Instance


A few months back, I had shared a script to automate index rebuild/reorg in a database with fragmentation analysis inplace at But that script would perform the maintenance only on a particular database at a time.


The script is modified and it now performs maintenance on all indexes across all databases in an instance. This again has two scripts.

1. analyze2.sql – Checks fragmentation level on indexes in all databases of the SQL instance and stores the results in tempdb.

2. defrag2.sql – Based on the data stored in tempdb by analyze2.sql, the script will process the maintenance, either Index rebuild or an index reorganize based on the fragmentation and the @minfrag, @maxfrag settings in defrag.sql script. Indexes fragmented below @minfrag in percentage will be excluded. Indexes fragmented between @minfrag % and @maxfrag % will be reorganized. All remaining indexes will be rebuilt.

Scripts are pasted below for your reference. Please let me know if you find some errors, bugs or issues with the code or the method used.

Continue reading “Auto Index Maintenance for All Databases in a SQL Server Instance”

Failover info events every second in SQL Server 2008 R2 SP2 Cluster


In all SQL server failover clusters in our environment, It came to our notice that the SQL Server Agent is spamming the Application eventlogs under event viewer below entries every second in the Active node.

[sqagtres] LooksAlive request.
[sqagtres] CheckServiceAlive: returning TRUE (success)


It was observed that this incident started to occur after the scheduled windows patching.


Microsoft had identified this as due to a code defect in Sqagtres.dll. To resolve this issue, we would have to patch the SQL instance with Cumulative update 3 for SQL Server 2008 R2 SP2 as mentioned in below KB.

The hotfix download is available at