log file too big and not enough space

I have an 88GB log file for a database hosted on SQL Server 2008 that I'm trying to back up using transaction log backup so I can then shrink. The problem is that I don't have enough space neither on C nor D, making this a catch 22.

What can I do to shrink the log? Can I force it to shrink without doing a backup? If I can what are the consequences of doing so?

Thanks...

From serverfault jstawski

• BACKUP LOG <DatabaseName>  WITH TRUNCATE_ONLY
  

  This was for 2005. Sorry, I did not see the tags - its better if this information was in the subject or in the body of the question.

  Anyway, I googled this for you:

  http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d64dc18b-52f8-4c8b-8be2-1a837988776e

  From: Nick Kavadias

  You can send the file to the bit bucket with the regulat BACKUP LOG command:

  BACKUP
   LOG
   DBNAME TO
   DISK
  ='NUL'
  

  What does is discard your transaction log, so if your database is in bulk or full logging, you now have a broken chain of transaction log backups (which you should be taking at often intervals). So please, make sure if you run this command take a full or differential database backup afterwards!

  If your in an environment where you dont need to do log backups then consider switching to a simple recovery model.

  jstawski : This is a very dangerous command. Do you think this is a case where I need to run it?

  squillman : This has been discontinued in SQL 2008.

  Sunny : Thanks for the unreasonable down-voting, the pleasure is all mine.

  joeqwerty : +1 to get you back to even, because I hate "drive by" downvotes. Your answer may not be the recommended course of action (and isn't valid for SQL 2008), but a simple correction without the downvote would have sufficed. Some attention to the spirit of the answer as well as to the content of the answer is warranted, IMHO.

  jstawski : I didn't down vote you, maybe squillman did. If you downvoted my question because you thought I downvoted you then that's very adult of you.

  squillman : I downvoted you because the answer was wrong. Now that you've corrected it I have removed the downvote.

  Sunny : @jstawski: I still have no edit rights and I can not edit your Q. The downvote is temp, until you fix it. That was the only way to ask you to pay attention for now. Usually, on other sites, where I have edit right, I would just edit the question.
  From Sunny

• Why not get an external USB HDD, connect it to the server and back up to this drive?

  jstawski : Because this is a server that I don't have access to. It is on a hosting location.
  From joeqwerty

• You could switch the recovery mode to simple and then back again to full. Then you should be able to shrink it and start again from that point.

  This would mean that you could only recover from your last full backup (so good to try it after a full backup). Also it will break replication.

  Stuart B : Did this just last week. Worked like a charm.

  Ed Leighton-Dick : This is the best option, but you do need to do a full backup immediately after switching to make this work. Just changing the recovery model doesn't truncate the log on its own. In addition, if you switch back to the full recovery model afterward, you need to do another full backup to get it to start retaining transactions again.

  jstawski : I can't do backups because there is not enough space.

  jstawski : ok, this worked like a charm. After changing to simple, I then shrank the log, which gave me my space back
  From JamesRyan

• If you have the Enterprise edition of SQL2008 you can try running a compressed log backup. Or, as mentioned above you can put the database into simple mode and perform a full backup - however you will lose the ability to do point in time restoration.

  From SuperCoolMoss