Shaun J Stuart

Just another SQL Server weblog

Browsing Posts in SQLServerPedia Syndication

I've finally had a chance to play around with SQL Server 2012 and one of the nice things I've discovered is that it features a warning when a transaction log file has an excessive number of virtual log files.

I've written about VLFs before and any seasoned DBA will know about them. However, there are still a great number of database professionals that either do not know about them or do not know the consequences of having too many (or too few). Since VLFs are created when the transaction log is created or grows, by far the more common problem is having too many rather than having too few. Managing VLFs is something a DBA should do, but until now, there really wasn't much info within SQL Server for detecting them. You basically had to issue a DBCC LOGINFO command periodically or run a script like the one I linked to above that checks for this on a regular basis. The number of rows returned by DBCC LOGINFO equals the number of VLFs. How many is too many? That depends and is something that is debatable. What we can say however, is that there is clearly a point at which the number of VLFs becomes excessive, no matter what the situation. With SQL Server 2012, Microsoft has chosen 10,000 to be that number.

In SQL Server 2012, when a transaction log exceeds 10,000 VLFs, a warning message will be written to the SQL Server log. Let's see what that warning is and how to get it.

The code below will create a test database called VLFTest with a 1 MB log file. Change the path to suit your environment before running it.

CREATE DATABASE [VLFTest]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'VLFTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\VLFTest.mdf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'VLFTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\VLFTest_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 1024KB )
GO

The next bit of code will expand that logfile in 1 MB increments. Each increment will add 4 VLFs to the transaction log.

DECLARE @size INT

DECLARE @sql VARCHAR(100)

SET @size = 2

WHILE @size <2502
BEGIN
	SET @sql = 'ALTER DATABASE [VLFtest] MODIFY FILE ( NAME = N''VLFtest_log'', SIZE = ' +
		convert(varchar(5),@size) + 'MB )'

	EXEC (@sql)
	PRINT 'Size: ' + convert(varchar(5),@size)
	SET @size = @size +1
END

After running that code, issue the command DBCC LOGINFO ('VLFTest') and you will get a result set back with 10,004 rows, indicating the log file has 10,004 virtual log files.

Next, set the database offline, then back online (or restart SQL Server). Now check the SQL log and you will see the following:

Click to embiggen

A couple of things to note:

1. The warning message says the log file has more than 1,000 VLFs. While 10,000 is more than 1,000 and thus, the warning is technically true, the warning is only generated once the number of VLFs passes 10,000. Pre-RTM versions of SQL 2012 had the warning appear at over 1,000 VLFs, but the released version was changed to 10,000. (You can verify this by using a @size value of less than 2502 and more than 250 in the above script. You'll end up with more than 1,000 and less than 10,000 VLFs but no warning will appear in the log.) The warning text did not get updated for the final release. There is a Connect item regarding this and it has been marked as Fixed, so I would expect this to be corrected with the next service pack or hotfix.

2. The warning only appears when the database is started up, not when the log file actually passes 10,000 VLFs via growth.

One thing I like about the warning is it gives you instructions on how to fix the problem. True, it's not very detailed instructions and it doesn't really give any indication of how using a different growth increment will solve the problem, but at least it points you in the right direction for further research.

Share

As a database professional, I see a lot of instructions from vendors on how they want their SQL Server backend configured. Many times, the recommended configuration stinks. It's not their fault - the companies typically don't have anyone on staff whose job it is to lean how to configure each database platform they support for optimum performance.

Today, I came across the opposite situation. I got some recommended SQL Server configurations that were dead-on correct. This was the recommendation:

Do not activate the option "autoshrink" in the database.

And later on, in a different document, there was this:

Do not, by any means, activate the option "autoshrink" in the database.

 

So kudos to you, UC4! You are making DBAs everywhere happy.

 

Share

We recently upgraded our installation of Great Plains from version 10 to Dynamics GP 2010 and ran into some difficulties with the upgrade hanging. We're using SQL 2008 R2 as our database back end. Let me first state that I was not involved in this upgrade and it was our vendor who was working with Microsoft on resolving this issue.

According to our vendor, the Microsoft Senior Support Escalation Engineer she was working with has seen some problems with the upgrade hanging. Microsoft has narrowed the problem down to a change that was made to the way the GL history table was upgraded in the most recent service pack and he thought the cause might be related to duplicate records in the fiscal period setup table. We did not have any duplicate records, so this was not the cause of our problems. Microsoft recommended setting the MaxDOP of the SQL Server to 1 and increasing the size of the tempdb logfile to 50 GB and ensuring there was enough disk space for that to grow, if it needed to. We made those changes and the upgrade succeeded.

I know. From a DBA viewpoint, I don't see how those changes should have any effect. Our tempdb log file was not that big on our failed upgrade attempts, but there was certainly room on the drive for it to grow that large. My log autogrow setting is 10 MB, so if it needed to grow, it should have grown to almost fill the disk before it failed, but I did not see that happening. I also fail to see how setting MaxDOP to 1 would help. (We were doing this on a test server that had no other load or databases on it.) The upgrade did take 7.5 hours to convert our 4 company databases. Our vendor said Microsoft felt this was a bit long, but if you are limiting yourself to one processor, I would expect it to take a while.

I googled this a bit and found nothing, so I'm posting this to get the information out there. I have no good explanation for why these changes worked, but they seemed to do the trick. After the upgrade, remember to change your MaxDOP setting back to what it was originally.

 

Update: The above was for a test server. Today, we are doing this on production, a system that has more RAM and more processors. Turns out, the issue is that we had a really big table (69 million rows) and the upgrade apparently copies that table to tempdb, manipulates the data,  then copies it back for some reason. We started the production upgrade without changing MaxDOP to 1 and it took much longer to work on this table - 12 hours and counting versus 8 in the test environment. I saw lots of CXPACKET waits, but nothing too horrible. The upgrade looked to be working on batches if 500,000 records. After 12 hours, I switched MaxDOP to 1 and it finished processing that table (although after 12 hours, it might have been reaching the end already..)

Share

Everyone know SQL Server loves memory. It will happily gobble up all the RAM you throw at it. On physical boxes, this may not be a big deal, especially if you plan for it and properly configure your max and min memory settings within SQL Server. RAM makes SQL Server run faster and who doesn't want that?

Of course I want to super size that sandwich! And throw on some Doritos and squeeze cheese, while you're at it.

In a virtual environment, this RAM gluttony can be a detriment. If you are just beginning to experiment with virtualizing SQL Server, odds are, the first servers you virtualize are going to be the lesser used ones. You (or your network / VM people) will likely just do a P2V of the server and you'll soon find yourself holding the keys to a shiny new VM. Presto-chango, you've just virtualized your SQL Server and you are done!

Not so fast. Think about what just happened. The P2V process cloned your physical hardware and made a VM out of it, without giving any thought to if that hardware was correct for the system. Suppose the system you just virtualized was a little used system that was built on a server that was used for a more active programs in the past. Perhaps the heavily used databases had been migrated off of this server over time and now the server is hosting half or one-third of the load it was originally built for. You could end up with a server that is way overpowered its current load.

In the virtual world, this can hurt you. Remember that each VM is sharing it's resources with other VMs on the same set of host hardware. So if your VM is running with 12 GB of RAM and 8 CPUs, that's fewer resources available to the other VMs on that host.

I will take a timeout here to point out that VM hosts do provide tools to share RAM and CPU amongst all VMs as the load on each VM changes. For example, "ballooning" is a method where a VM can borrow RAM from another VM on the same host to temporarily satisfy memory needs on another. Of course, all these sharing techniques come with a price - when they occur, performance degrades. I'm lucky at my company because the VM team here is very conservative with our SQL Server VM hosts. They never oversubscribe RAM and are very conscientious about CPU allocation. In short, I never really have to worry about resource contention amongst my SQL VMs.

Be a good corporate citizen. If you don't need so many resources, give them back. Your network and VMs admins will love you. Everyone is ALWAYS bugging them for more resources. No one ever tells them "Hey, I've got too much. You can have some back." The trick is determining if you do have too many resources.

I'm going to focus on RAM only in this post, because this is a situation I found myself in recently. As part of my normal DBA monitoring processes, I was reviewing the page life expectancy of my SQL Servers using the Typeperf data collection tool I've written about previously. I noticed one SQL Server had an absurdly high page life expectancy:

This is what too much RAM looks like

This particular server has 12 GB of RAM. Three million seconds is just over 34 days. That's a long time for SQL to keep data in memory. Also, note the pattern. The drop offs are when the server was rebooted for patching. When the server comes back up, it basically loads data into memory and never needs to flush it out again.

Now, of course, whether or not this represents a waste of resources depends on your situation. If this was for a heavily used report server, this could be a highly desired performance pattern. But in my case, this chart is for a SQL Server back-end of a phone system. There are no other databases on the system and it is not under a heavy load. Also remember what I said previously about my VM admins - they do not over-allocate RAM on SQL Server VMs. So I've clearly got RAM on this VM that could most likely be better utilized elsewhere.

So what do I do to correct this? Luckily, the solution is fairly easy. By changing SQL Server's maximum memory setting, I can restrict the amount of memory SQL Server can use to a lower value and see how that affects performance. Furthermore, this is a setting that can be changed on the fly, so no downtime is required. In my case, I configured SQL to use a maximum of 7 GB of RAM (which would reserve 1 GB for the OS on an 8 GB system) and am letting it run for a couple weeks. If no performance issues are noted, I will reconfigure this VM to have 8 GB of RAM instead of 12 GB and I will reallocate that 4 GB RAM to another one of my SQL Server VMs on that same host that I know can use more RAM. And if performance issues do crop up, it's a quick fix to add the RAM back by increasing SQL's max memory setting again. By contrast, changing the amount of RAM in a VM requires a reboot, so that is why I'm testing first by changing the SQL Server memory settings.

Share

Microsoft released SP2 for SQL Server 2008 R2 a couple weeks ago and I've been applying it to my servers. Most of the time it installed without problems, but I encountered a very puzzling error on one server. When I ran the service pack installation, I saw a DOS window pop and and disappear quickly and nothing else happened. The temporary directory that the service pack process creates was deleted.

I managed to get a copy of the temporary directory from another server while I was installing the service pack there and moved it to my troublesome server, so I could see what was happening before it got deleted. I opened an administrative DOS prompt so I could see any errors without it closing. When I ran setup.exe from the command prompt, all I saw was the copyright notice for the  service pack:

Microsoft (R) SQL Server 2008 R2 Setup 10.50.4000.00
Copyright (c) Microsoft Corporation. All rights reserved.

Then I was dropped back to the command prompt. As far as I could tell, no log files were created. I checked the normal SQL installation log file location (C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap) but that directory did not exist. UAC was disabled on this machine. I cleared the IE cache, rebooted the machine, and even verified the Windows Installer service was running. I also checked Windows Update and applied all the patches the machine needed. None of that solved my problem.

This was very strange. Without a log, I didn't know how I was going to troubleshoot this. A couple suggestions from the forums at SQLServerCentral.com pointed me in the direction of .NET, so I went into Add / Remove Programs and did a Repair in the .NET installation. That completed, but did not solve the problem.

Not believing Microsoft wouldn't make a log file somewhere, I searched the hard drive for recently created files. Bingo! I found a log file at C:\Users\<username>\AppData\Local\Temp\SqlSetup.log. Opening that showed me some steps the installer was trying to do. The last few lines were:

08/02/2012 06:54:45.749 Attempt to initialize SQL setup code group
08/02/2012 06:54:45.751 Attempting to determine security.config file path
08/02/2012 06:54:45.763 Checking to see if policy file exists
08/02/2012 06:54:45.764 .Net security policy file does exist
08/02/2012 06:54:45.766 Attempting to load .Net security policy file
08/02/2012 06:54:45.772 Error: Cannot load .Net security policy file
08/02/2012 06:54:45.774 Error: InitializeSqlSetupCodeGroupCore(64bit) failed
08/02/2012 06:54:45.777 Error: InitializeSqlSetupCodeGroup failed: 0x80004005
08/02/2012 06:54:45.779 Setup closed with exit code: 0x80004005

Hmm. It seemed the problem was related to .NET after all. Someone else had a similar problem and posted about it at http://www.sqlservercentral.com/Forums/Topic1262389-391-4.aspx. The solution for that person was to reset the .NET security policy file using the caspol.exe utility. I tried that and it did not solve my problem. However, the error log still seemed to indicate this file was the issue, so I did some more digging. I found this post from Microsoft giving the location of the security policy files. The previous post said one way to restore your system to a useable state was simply to delete these files. So that's what I did. When I re-ran the SP2 installtion, I had the same issue and, more surprisingly, the logfile still included the line ".Net security policy file does exist".

So I searched the entire drive for all occurances of Security.config and Security.cch and found another copy in the C:\Users\<username>\AppData\Roaming directory. Once I deleted that, the SP2 installation program was able to run.

 

 

Share