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”

Extend, Expand or Resize an LVM root partition ‘/’ in linux

If your linux root partition ‘/’ is an LVM, you could easily extend the drive. Make use of the commands listed later in this post.

First let me explain how an LVM is setup.

Different components in an LVM setup
Physical Volumes These are real physical hard drives or partitions attached to the machine.Favorite commands: pvdisplay, pvcreate, pvresize
Volume Groups Several physical volumes are combined to form a volume group. Multiple Volume groups are also possible. Additional PVs are added to a VG to expand it anytime online.Favorite commands: vgdisplay, vgextend, vgcreate
Logical Volumes  Logical Volumes are carved out of a Volume Group. Logical Volumes could be expanded anytime if its VG has enough free space availableFavorite commands: lvdisplay, lvextend, lvcreate

In short, several physical volumes(PV) are first created. Volume groups(VG) are then built on top of multiple PVs. Logical Volumes(LV) are then carved out of any VG until the VGs run out of storage. Adding more PVs to a VG would help increase the storage in the VG.

Now lets get to work. Continue reading.

Continue reading “Extend, Expand or Resize an LVM root partition ‘/’ in linux”

SQL Server 2012 Daily Health Check

Being a DBA, it is good to have some kind of automated health check report that gets delivered to inbox with quick glance of DB health. Hence this script will be very useful. Copy this script and schedule as a SQL agent job. Provide the mail profile and recipient addresses. The script was built with help from several colleagues and references from different SQL forums.

Continue reading to view and copy the script. Since the script is too long, it could not be posted in homepage.
Continue reading “SQL Server 2012 Daily Health Check”

SQL Server 2014 Licensing

Here I would like to help for common question that every DBA has when it comes to SQL Server Licensing.

Refer for different licenses available.


1. What is Per User license?

Ans) Its not the number SQL users that could be created. Its the number of people who use the SQL Server on a server.

2. Per core license:

Ans) Its the number of physical cores placed in sockets on a physical machine. Its not the number of cores you see in task manager.

How to Solve Rubix Cube

Solving Rubik’s cube is very simple if we follow some algorithm correctly. There are several algorithms available in the web or youtube. Out of them, below algorithm appeared to be easy for me.

To follow this algorithm, below points has to be understood.

  1. Center cube and it’s opposite cube never moves. They stay opposite to each other all the time. They have only one side and a single color.
  2. Edge cubes have two colors and will stay sandwiched between two corner cubes all the time.
  3. Corner cubes has three colors and they always are at the corner.

This algorithm goes through three layers. When you keep the cube on the floor, the cubes that touch the floor belongs to first layer. The second layer is above the first layer. Third layer will be the top layer.

Solve the First Layer:

Choose a color for the First layer. I choose white. Use your skill and make a cross at the bottom. Refer Wikihow to form the cross at the bottom face of the cube as shown in below picture.

Correct Incorrect

Refer Wikihow for Second layer.

Solve the Third Layer:

Now you would have got bottom two cube layers done. Let’s now proceed with the last and top most layer.

Make IInd Cross Opposite to First Face.

F→ R↑ T← R↓ T→ F←
  1. Rotate Front towards Right.
  2. Lift the Right Column.
  3. Slide Top Row to Left.
  4. Put back the Right Column Down.
  5. Slide Top Row to Right.
  6. Rotate back Front towards Left.

IInd Cross Arms.

R↑ T² R↓ T→ R↑ T→ R↓ T→
  1. Lift Right Column Up.
  2. Slide Top Row left Twice.
  3. Put Right Column Down.
  4. Slide Top Row towards Right.
  5. Repeat Step 1.
  6. Repeat Step 4.
  7. Repeat Step 3.
  8. Repeat Step 4.

Place Top Corners.

L↑ T← R↑ T→ L↓ T← R↓ T→
  1. Lift Left Column Up.
  2. Slide Top Row towards Left.
  3. Lift Right Column Up.
  4. Slide Top Row towards Right.
  5. Put Left Column Down.
  6. Repeat Step 2.
  7. Put Right Column Down.
  8. Repeat Step 4.

Flip Top Corner Colors.

R↑ T² R↓ T→ R↑ T→ R↓
L↑ T² L↓ T← L↑ T← L↓
  1. Lift Right Column Up.
  2. Slide Top Row left Twice.
  3. Put Right Column Down.
  4. Slide Top Row towards Right.
  5. Repeat Step 1.
  6. Repeat Step 4.
  7. Repeat Step 3.
    1. Lift Left Column Up.
    2. Slide Top Row left Twice.
    3. Put Left Column Down.
    4. Slide Top Row left.
    5. Repeat Step A.
    6. Repeat Step D.
    7. Repeat Step C.
    8. Repeat Step D.

Auto restore multiple databases in One shot using powershell

Below script helps restore several databases on to a SQL Server instance in one go. We just have to place all the .bak files in a folder and execute the script. before running the script, its required to import SQLPS in the powershell prompt as below.

Save below script as restore_all.ps1

Execute the script like in below example.

Git | Frequently used commands

To check for any changes in the files.

View Change history and comments.

To see the change history per file

See changed files history

Commit the change

To revert back a commit

To create a clone of your local repository

To create a bare clone(A clone with the .git database alone) as a central repository

To see remote branches

Add/remove a remote branch

Push local changes to remote branch

If you want to start tracking a remote branch with git status command, run below command.

Continue reading “Git | Frequently used commands”

Automatically add Partition for Partitioned Tables

This stored procedure helps automatically create new partition on a new file group to an already partitioned table. I have tested the script in my testlab. Please review and let me know for any corrections if required.

Continue reading “Automatically add Partition for Partitioned Tables”