Monday, February 10, 2014

Virtual log files are basically segments of the database log file. Every time the transaction log grow in size, the number of virtual log files would also grow. If you have a database with transaction log that keep growing in small increment each time, you can end up with a lot of virtual log files, and that can be a bad thing.

In SQL Server 2012, if you have a database with more than 10,000 virtual log file, you will get a warning message on the SQL Server logs. To demonstrate this:

First I would create a database called VLFTest. I set the log file for the database to have an initial size of 512 KB (for this size 2 VLF will be created).

Once the database is created, I can check the number of VLF created by running the following query:

DBCC LOGINFO ('VLFTest');

The query should return 2 rows, which would indicate that there are two VLFs created.

The next thing I would do is to grow the transaction log size by 512 KB increment for 5,000 times. Since each time we increment the transaction log by 512 KB, 2 new VLFs would be created. Thus, doing it for 5,000 times would cause it to generate 10,000 new VLFs. So at the end I would end up with 10,002 VLFs. I can do this by using the following script:

I would get back 10,002 rows back, which means that I have 10,002 VLFs on VLFTest database’s transaction log.

Now, the warning in SQL Server log unfortunately will not be trigger when the VLF count goes beyond 10,000. I found out that it will be trigger under the following conditions:

When the SQL Server is restarted

When the VLF database is set online (after it is set offline)

When we restore the database

So for a quick test, I try to set the database offline and then back online by using the following query:

USE master;GOALTER DATABASE VLFTest SET OFFLINE;GOALTER DATABASE VLFTest SET ONLINE;GO

After VLFTest database is back online, when I go to SQL Server log, I would get the following message:

Database VLFTest has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

Why Having Too Much of VLFs Count Can Be Bad Thing?

Having an excessive VLFs count can be a bad thing to your database. For one, when I tried to make the database back online after turning it offline, it took longer than expected. When I run the following query:

USE master;ALTER DATABASE VLFTest SET OFFLINE;GOSET STATISTICS TIME ON;ALTER DATABASE VLFTest SET ONLINE;GO

After running it 5 times, on average it took about 23 seconds for the database to be online. Now compare this with the following:

USE master;GOIF DB_ID('VLFTest2') IS NOT NULLBEGIN DROP DATABASE VLFTest2;END;GO

It is basically the same database, but instead of growing the transaction log file from 512 KB to 2,560,512 KB by 512 KB increment (and in the process created 10,002 VLFs), I immediately create the transaction log with size 2,560,512 KB. By doing this, I only have 16 VLFs. Now when I try to run the following query:

USE master;ALTER DATABASE VLFTest2 SET OFFLINE;GOSET STATISTICS TIME ON;ALTER DATABASE VLFTest2 SET ONLINE;GO

After running it 5 times, on average it took about 0.2 seconds to bring VLFTest2 database online. That’s quite a huge difference, 23 seconds vs. 0.2 seconds (about 2,200% difference).

I also noticed big difference in the time that it took to restore VLFTest1 database (with 10,002 VLFs) compared to the VLFTest2 database (with 16 VLFs). It took about 1 minutes and 35 seconds to restore VLFTest1 database, but it only took about 6 seconds to restore VLFTest2.

In addition to the above, having an excessive number of VLFs in your database can also have an impact on the database transaction. A while ago, Linchi Shea wrote a blog post regarding this. It can be found here.

Take Away

Having a high count of VLFs can hurt your database. To avoid it, we need to carefully plan our database size, especially in this case, the transaction log size. We also need to make sure that the transaction file growth size is not too small. This is to ensure that we don’t end up with many auto growth on the transaction log file, which cause the VLFs to grow.

Further Reading

You might also want to read Kimberly Tripp’s blog post regarding VLF. It can be found here.

Thursday, February 6, 2014

SQL Server has database mail, a solution which would allow us to send e-mail from our database server. It allows us to send either e-mail in html or text format to users. It’s great because we can use it to send query results to users (or ourselves). We can also configure SQL Server Agent to use database mail to send alerts to operator.

Database mail utilizes SMTP server to send the e-mails. In some cases, we might not have a readily available SMTP server in our environment (like in my home lab environment) or we might be block from using the SMTP server from our development environment for security reason. In those situations, I’ve been using smtp4dev. It is a neat little utility that would allow you to have a dummy SMTP server on your local computer. It is lightweight and also no installation is needed. When we run the utility, it would sit on the system tray and listen to port 25 (default SMTP port). It will receive the e-mail that get send to its way, without sending the e-mail to its final destination (the e-mail recipients). It is pretty handy when we need to run some tests.

Extract the file and save it on your system (assuming that it is your development system and it has the SQL Server).

Run smtp4dev. Notes: If you have Windows Firewall turned on, you might get a “Windows Security Alert” that ask you if you want to allow smtp4dev to communicate with either the private network, public network or both. Since normally I run smtp4dev on my development system with also has SQL Server that I want to have database mail turn on, I would just hit the cancel button.

If database mail in SQL Server has not been enable, we can enable it by using the following SQL script:

We then need to set up database mail profile, account and then associate the profile with the account in SQL Server. To do that, we can use the following script (based on the “Database Mail Simple Configuration Template”):

If everything works as plan hopefully on your tray notification, you would see the following message:

And if you open smtp4dev, you should see the following:

To view the e-mail, you can click on the “View” or “Inspect” button.

Some caveats that I’ve noticed:

I have Microsoft Office 2013 installed on my system, which includes Outlook 2013. For some reason, when I click the “View” button, I would get an error message “The attempted operation failed. An object could not be found.” and it just would not open the e-mail (if I try to open it using Outlook 2013). It is ok if I use Windows Live Mail 2012. I would normally use the “Inspect” button instead.

If you exit out smtp4dev, the e-mails that you have on the Messages tab will get deleted.

Cleaning Up

If you want to remove the database mail from your SQL Server, first you want to remove the profile association with account, the profile and the account. You can do that by using the following script: