0

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!

cs0815
  • 183
  • 7

1 Answers1

1

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.

mrdenny
  • 27,174
  • 4
  • 41
  • 69
  • 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