Transaction Log Backups Across a Network

Paul McCaskie — Wed, 08 Apr 2009 22:13:30 GMT

Today I encountered a problem with a SQL server which was very low on disk space. After some investigation I found that the transaction log had grown massively; the database itself was about 125 MB whereas the transaction log was over 66 GB. I did some research and found this is quite a common problem with SQL Server, the reason being that by default SQL Server is installed in full recovery mode. This requires transaction log backups to be run regularly whereas the server that I was looking at had a backup plan but not one for the transaction logs. This link from MSDN describes more about truncating the transaction log and this link from Kimberly Tripp explains more about the kind of problems that can occur with transaction logs.

On this particular server there was no free space available to backup the transaction log so we needed to find another way to shrink it. Occasionally in the past we have done this by changing the SQL Server to simple recovery mode, which allows us to truncate the transaction log, but this particular database was a mirrored instance which cannot be run in simple recovery mode.

The solution was a simple one, we had another sever on the network with plenty of space available, we just needed to backup the transaction log to that machine. Unfortunately this was not as easy as expected because SQL Server was running as a local service account, so had no permissions on the other machine, which meant we had to either change the account to one with network access, or alternatively create a null session share on the target server for the duration of the backup. As we did not want to change the account running the SQL Server instance as this could have had undesirable side effects, for example failing over the mirror principle to the other machine when the SQL service was restarted, the null session share method was chosen and it worked great, allowing us the backup the whole transaction log across the network.

The “Back up Database” window in SQL Server Management Studio does not allow you to browse to a network drive, unless it has been mapped to a drive letter in SQL Server, but you can still type in the UNC path, for example:

In my case I actually ran the command from SQL Server Management Studio, which looked something like this:

BACKUP LOG [DATABASE] TO DISK = N'\\ServerName\sharedbackupdirectory\logfile' WITH NOFORMAT, INIT, NAME = N'DATABASE-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

Obviously using the null session share would not be advisable on a regular basis due to the security risk, which is why the registry entry was removed after the backup, but I thought I would post this tip in case anyone else encounters this problem.

In future it would be advisable to make sure if running in full recovery mode that there is always a backup scheduled for the transaction log to prevent it getting too large, the earlier link from Kimberly Tripp has some information about maintenance plans that is worth reading.

OutOfMemoryException in ReSharper

Paul McCaskie — Wed, 25 Mar 2009 13:55:59 GMT

Last week I attended the local Southampton NxtGenUG meeting, which was an interesting talk by Ian Cooper about NHibernate as well as a nugget from Jenni Phillips about refactoring code using Refactor. I enjoy going to the user group as it gives me an insight to various technologies that I might not have time to look into otherwise as well as giving me a chance to catch up with like minded people. One of the other benefits of the user group is the swag which you can win, mostly T-shirts and the odd book, but this week I won I licence to ReSharper which I was pleased about as i have heard colleagues in the office rave about it.

I decided to install the trial of ReSharper on my machine to see how I got on with it but unfortunately I ran into a problem. The solution i am currently working on contains over 100 projects and when I opened it with Visual Studio ReSharper tried to analyse the solution I started getting a constant OutOfMemoryException which looked like this:

I opened another solution I work on with just over 30 projects and didn’t get the error, so i decided to look into possible solutions. Fortunately I found a blog post by Steven Harmen on Hacking Visual Studio to Use More Than 2Gigabytes of Memory which seems to have resolved my issue. Now I need to spend some time getting used to ReSharper, I like what I have seen so far, especially the navigation of code. If I find any useful tips I'll post them here.

Paul McCaskie's Blog

Transaction Log Backups Across a Network

OutOfMemoryException in ReSharper