SQL Server 2005: transaction log for database is full. However there is plenty of space on the Disk

More than likely if you are looking at this entry, you are experiencing a full transaction log AND you are needing to find a quick resolution to get your database back to normal.

So I won't waste your time, beacuse you are probably frantic to get the database back online, this blog entry deals with this scenerio:

• Message from SQL Server: 'The transaction log for database 'x' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"



• SQL Server is running on Windows Server 2003 or Windows Server 2000. Disk is NTFS



• There is plenty of available disk space on the drive where the transaction log resides



• The event viewer also shows this message at the same time it reports that the transaction log is full: C:\Data\MSSQL2005External\Data\xxxxx.ldf: Operating system error 112(There is not enough space on the disk.) encountered.

In this particular case the problem was not in the SQL server management (which I spent a considerable amount of time trying to shrink the log, manually run a full backup or even a differential backup with no success). The problem was a disk quota limit was reached for the SQLServer userid.

If your log or data files reside on an NTFS drive, there is a feature in Windows Server that limits the amount of disk space a userid can allocate. Looking at the Log File viewer, Select the "Windows NT" logs and look for a log entry with source 'Ntfs' that occured the same time the SQL log failure occured. The message will say: "A user hit their quota limit on volume x:" The log entry will also show the user id that hit the limit which in my case was the userid for the SQL Server instance.


How to resolve the problem

If this is your situation, open windows explorer on the drive where the quota was reached and right click to view the properties window-





Click the Quota tab and note that quota management is enabled:

Click the Quota Entries... button. You will then see a list of quota entries for this disk- your SQL Server userid should be in this list. Double click the userid entry to see the quota settings for this user for this disk:

Change the limit disk space button to 'do not limit disk space'. I don't know why you would want to limit the SQL Server userid but if you do then set the limit higher. Click OK.

You should now be able to log transactions, do backups and other SQL tasks.

I don't know how the SQL Server userid got placed into the quota list. In fact the person who set up this server did not know he had enabled disk quota: this is a SBS setup so maybe it sets quotas on by default. In any case I just wanted to post this scenerio so that someone else does not get stuck for several hours while customers are trying to use the database.