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.