Last week, I was collecting data using dm_os_performance_counters and storing it into a warehouse so that I can generate reports, except that my script failed with ‘Divide by zero error encountered.’  I started to think … none of the databases on this SQL instance can possible have a 0 size database!  Due to the nature of these databases, it was just impossible.  I had to figure out a way to get around the error, I wanted to use the function isnull() but instead I went with the nullif() function.

The nullif() function showed that database, ABC, was the culprit.  The output from dm_os_performance_counters show 0 for data and log file

I don’t understand how this came about.  So I had to think back ….

A few months ago, I was working on a database that maxed out the int data type in SQL. int takes up 4 bytes and has a positive (unassigned) range of 2^31-1 (2,147,483,647). The database doesn’t make use of the negative (assigned) range, which is 2^31 (-2,147,483,648). SQL doesn’t let you increase the unassigned range by taking one from the assigned; Other programming language such as C/C++/C# and even VB let you perform such actions. The decided ended up converting the int column to bigint, which took a very long time on a VM server with 4GB of memory. I think it took over 3 days.

After the sysadmin brought up the VM for me, I backup and restore the database to the new VM. The disks were SAN attached. After 3 days of converting, I ran a DBCC CHECKDB on the database to verify that everything is good and no corrupted occured. All was well. Instead of backing up and restoring the database again, wasting time, I decided to have the sysadmin unmount the SAN from this VM and present it to the original server as a new drive. From there, I would just attach the database and save me a lot of time.

I ran sp_helpdb on the ABC.  The output clearly show that the database is 6xxGB!

And here the actual data file itself showed the same:

So I asked around and Peja Tao over on the msdn forum was able to provide some assistance.  He suggested that I perform a backup and restore of the database, if this does not work, I would have to contact Microsoft’s CSS.  Over the weekend, I backup and restored the database, afterward, the numbers for this database via dm_os_performance_counter showed up correctly.

I’m not sure if my situation is a one off, but I don’t think I will be doing anymore SAN drive remount from one server to another anymore.