Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
1  
pls, make the Q more readable, by including the environment involved in the subject/body. –  Sunny Jan 19 '10 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 '10 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 '10 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 '10 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 '10 at 19:14

4 Answers 4

up vote 5 down vote accepted

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.

share|improve this answer
    
Did this just last week. Worked like a charm. –  Stuart Branham Jan 19 '10 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. –  Ed Leighton-Dick Jan 19 '10 at 18:10
    
I can't do backups because there is not enough space. –  Jonas Stawski Jan 19 '10 at 18:14
    
ok, this worked like a charm. After changing to simple, I then shrank the log, which gave me my space back –  Jonas Stawski Jan 19 '10 at 21:29

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

share|improve this answer
    
Because this is a server that I don't have access to. It is on a hosting location. –  Jonas Stawski Jan 19 '10 at 18:13
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.

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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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