0

I am running SQL Server in Windows 10. I have gotten one of the following two errors in all recent queries:

The transaction log for database 'MyDB' is full due to 'ACTIVE_TRANSACTION'.

An error occurred while executing batch. Error message is: There is not enough space on the disk.

I have tried DBCC SQLPERF('logspace') to analyze disk space. The database has very little log space after attempting to perform a query as suggested here. I do not anticipate being able to resolve the issue by shrinking the log file. I tried CREATE DATABASE, then 

SELECT * 
INTO new_db.table 
FROM old_db.table

The following error occurs:

Msg 1101, Level 17, State 12, Line 2
Could not allocate a new page for database 'new_db' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

I have deleted log files from C:\Users\username\AppData\Local\Temp, but the same exceptions occur. I have also cleaned files from my hard drive and recycle bin, as well as defragmenting my hard drive. I have tried adjusting the path for query results to a different directory (Tools>Options>Query Results). I have also dropped a number of tables from the database, exited SQL Server and reconnected, as well as shutting down my computer and restarting it.

Greg
  • 84
  • 1
  • 1
  • 7
  • How much actual disk space do you have free? You can try commiting smaller batches so less log file is used but basically it sounds like you don't have enough disk space to do what you want. If you use an Azure database for $5 a month (effectively free on a trial account), you'll never have to worry about this again. – Nick.Mc Mar 23 '20 at 14:19

1 Answers1

3

The first thing to understand is what kind of recovery mode the database is using.

If you are in FULL recovery mode, it's not enough to take regular backups. You must also take frequent (every 20 minutes, or even faster) transaction log backups. Sql Server will never recycle the transaction log unless you do this, and it will continue to grow until you run out of space.

After you have run a transaction log backup, you should be able to shrink the log file and reclaim that disk space. Note this should not be a regular process. Only shrink the DB to regain the space after initially fixing an error like missing transaction log backups.

If you are not in FULL recovery mode, you may be able to just manually clear or expand the transaction log in Sql Server management studio.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • Thank you for your response! It turned out that my problem had as much to do with poor database management as with the transaction log. I am using a work laptop, and I was not able to find any method to backup the transaction log (or the database). Dropping some tables that were over-joined (too many columns) allowed the database to operate normally without ballooning and occupying the entire disk. – Greg Mar 27 '20 at 03:18