1

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'?

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
Esselans
  • 113
  • 1
  • 5

1 Answers1

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.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
Midhun C N
  • 370
  • 6
  • 17