3

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...

7
  • 1
    pls, make the Q more readable, by including the environment involved in the subject/body.
    – Sunny
    Jan 19, 2010 at 17:55
  • +1. No need for any more downvotes here as far as I'm concerned. I'm not a mod here, so that's just my opinion. I agree with Sunny though, I've been caught many times responding to a question with an answer that was for a different version\edition\platform because I don't always look at the tags. Having the version\edition\platform in the question would be helpful.
    – joeqwerty
    Jan 19, 2010 at 18:15
  • As I said in my prev. comment, I'll remove the downvote after the Q is fixed. I have no edit rights yet to fix it myself.
    – Sunny
    Jan 19, 2010 at 18:20
  • @Sunny: There's no reason to edit (or downvote) simply because you failed to notice the (correctly placed) tag...
    – squillman
    Jan 19, 2010 at 18:37
  • 2
    @Sunny: Tags are the preferred method on SF. Just so you know, search engines will see the tags as part of the page body since they are in plain text. The indexes will not distinguish it as a tag vs. question body. This question shows up as the 8th result when searching on "log file too big sql server 2008", and the tag is highlighted as a hit. It's your choice to downvote, but I personally don't agree. The OP did it the right way.
    – squillman
    Jan 19, 2010 at 19:14

4 Answers 4

7

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.

5
  • Did this just last week. Worked like a charm. Jan 19, 2010 at 18:02
  • 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. Jan 19, 2010 at 18:10
  • I can't do backups because there is not enough space. Jan 19, 2010 at 18:14
  • ok, this worked like a charm. After changing to simple, I then shrank the log, which gave me my space back Jan 19, 2010 at 21:29
  • My drive has 0 bytes of space and setting the recovery model to Simple is impossible. Tells me "There is not enough space on disk" Aug 4, 2015 at 17:43
1

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

1
  • Because this is a server that I don't have access to. It is on a hosting location. Jan 19, 2010 at 18:13
1
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.

7
  • This is a very dangerous command. Do you think this is a case where I need to run it? Jan 19, 2010 at 17:46
  • This has been discontinued in SQL 2008.
    – squillman
    Jan 19, 2010 at 17:47
  • Thanks for the unreasonable down-voting, the pleasure is all mine.
    – Sunny
    Jan 19, 2010 at 17:57
  • +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.
    – joeqwerty
    Jan 19, 2010 at 18:10
  • 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. Jan 19, 2010 at 18:12
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .