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.

Can someone of you experts please advise me on this situation:

I have a dedicated hard drive. It contains the database and log file. I have run a query over night and the log file maxed out the size of the drive (500k left). The query did not finish so I cancelled it. But nothing seems to happen (one would expect the log file to shrink ... ?). Shall I just be patient and let the query ‘cancel’ or is there another course of action (I have been waiting for almost 3 hours now)? Thanks!

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

The log file will not shrink by itself. If the query has been canceled then you can shrink the database file by using the DBCC SHRINKFILE statement.

use MyDatabase 
GO 
DBCC SHRINKFILE (MyFile, 1024) --Change 1024 to some good size 
GO

If the query hasn't finished rolling back you'll need to wait for the rollback to finish before you can shrink the file. Usually for big queries it takes as long to rollback as it does to run.

share|improve this answer
    
Thanks. I will wait .. and accept your answer soon. –  csetzkorn Sep 20 '11 at 9:47
    
If the log file does not shrink you can refer to log_reuse_wait_desc in sys.databases to determine what is preventing the shrink operation –  Ally Reilly Sep 22 '11 at 20:02
add comment

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.