Automated batch update or delete operation in SQL Server

If we perform update or delete operation on huge number of rows in a large table at once, there are chances that the SQL Server would freeze or would cause outage. And if we try to kill the transaction in the middle, we might end up in a more dangerous “rollback” state. That’s why I spent some time to build this script that helps in automated batch operation in small chunks with constant check for query performance. The script would increase the chunk size based on the time taken by the each execution so that it would end at maximum achievable chunk size that has adequate performance.

Real Time Service Monitoring with vbscript

Service monitoring sometimes become inevitable for any server admin. Here is a script to keep watching a windows service for any change in status. The script will alert you as soon as a service shuts down or starts up with no latency using minimal resources. The sample script here is to monitor sql server related services. You could alter the text pattern as marked to monitor your own service. Save the script to a vbs file and name it like For eg.”ServiceMonitoring.vbs” and double click the file to execute. The script keeps running in the background scanning the service status and sends out HTML email alert if it finds any change in the specified service status.

List out and script all indexes in a database

Its quite common that when there is some upgrade, change or release in application, there are chances that our database objects get modified. It may drop some indexes too hampering performance. I had similar situation in my profession. I had creating multiple indexes to find tune database performance, but then there was an application patch release. All those indexes we manually created were dropped by the application. Soon, there was blocking, deadlocks, long running queries, high cpu and performance became pathetic. Later it was understood that all those custom index we DBAs manually created were mercilessly removed by the application. 😥

Continue reading “List out and script all indexes in a database”