CODEWITHSUNDEEP
sqlsql-server-2008-r2
Streklin
Streklin

Reputation: 849

Unable to delete log entries in database due to insufficient space

We ran into an issue where our log table got far larger far faster than was really anticipated and appear to have filled up most of our allocated database space. We don't have the ability to increase the amount of available space, so we decided to clear out the log files. However, apparently we don't have enough space to do this either (even after truncating the log files). Is there any way around this?

Here is the command we are running (I've changed the table and db names) and the error that is generated.

DELETE FROM [db].[dbo].[logtable] 

   Msg 9002, Level 17, State 4, Line 2

The transaction log for database 'db' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Msg 1101, Level 17, State 12, Line 2

Could not allocate a new page for database '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.

Upvotes: 1

Views: 1586

Answers (4)

Bueller
Bueller

Reputation: 2344

This link might help you. I read that you have already truncated the table but the space allocation has not changed. In order to recover unused space in SQL Server you have to go throught the Backup process or one of the other methods identified here https://web.archive.org/web/20081109185636/http://sqlserver2000.databases.aspfaq.com:80/how-do-i-reclaim-space-in-sql-server.html

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

Sounds like you're running the database in FULL recovery mode without ever backing up the transaction log. As appropriate for your situation, you should either switch to SIMPLE recovery mode or begin backups of the transaction log. To check this:

SELECT name, recovery_model_desc 
    FROM sys.databases
    WHERE name = 'YourDatabaseName'

Upvotes: 1

Pleun
Pleun

Reputation: 8920

Well, perhaps you can truncate logtable, depending on your table structure.

Or do it step by step (batched):

WHILE @@ROWCOUNT <> 0
begin
delete t1
from (select top (100) *
        from logtable
      end   
 end

Upvotes: 1

E.J. Brennan
E.J. Brennan

Reputation: 46879

You should be able to use the truncate table instead.

http://msdn.microsoft.com/en-us/library/ms177570.aspx

Upvotes: 1

Related Questions