1

This question already has an answer here:

I have a server with 25GB of free space. The software stopped working because the transaction log is full. I noticed that the database has two logs files; one is 45Gb and the other is 65Gb.

I think the guy working here before me (he is not anymore) added a second log file so the software would continue working, however there is almost no space left on disk.

I tried using SQL Server Management Studio to take a full backup but I can't since the transaction log is full.

What are my options? I can shrink the logs but I don't have ANY backup and can't do any of them.

Should I change it to simple mode first? Can I script the whole database and save it as 'backup'?

marked as duplicate by Max Vernon, RolandoMySQLDBA, Tom V, Daniel Hutmacher, Andriy M Apr 2 '16 at 9:22

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3

I assume you don't have space in your drives to take a log backup. You can use the following query to trick SQL server into thinking that you are actually saving the log backup to disk, But you're not.

BACKUP LOG dbname TO DISK = 'NUL:';

Also if you're running SQL server 2005 or less, You can use the below query

BACKUP LOG dbname WITH TRUNCATE_ONLY;

Once the query completes you can go ahead and shrink the log file as usual using

USE dbname;
DBCC SHRINKFILE(fileid , 0);

Also do note that the above backup query will break your log backup chain and your log-shipping setup, if you have one.

  • 3
    good advice, however you should always ensure you take a full backup and ensure regular database and log backups are scheduled after doing a backup to disk='NUL:' – Max Vernon Apr 1 '16 at 21:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.