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.
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 establish regular transaction log backups, you should be able to shrink the log file and reclaim that disk space. Note shrinking 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.