Steve JP (4/29/2013)
When you attach a database file, if the log file cant be found then it will recreate a default sized log file at the correct location. I think the new size is based on the model.ldf file size.
True its not ideal, hence my comment that it is a sledgehammer approach, unless you see a sledgehammer as a panacea in which case.......
But, if you only have a tiny maintenance window and the shrink doesn't work then yes the single file attach db is a method that does work.
In the old days (7 & 2000) the only other method to force a log file shrink was to move the active part of the log to the front before a dbcc shrinkfile was successful. It wouldn't resize to anything less that the last offset of the active virtual log file. And yes, to move the active part meant having a loop to consume a massive amount of log space with many log file backups and diff backups to maintain the SLAs. Updating a dumb row in a dumb table a million times with a replicate ('a' , 8000 ) does nudge the active part of the log file 😉
On a personal note I wouldnt want to start flipping the recovery state to simple on a business critical system unless it was during a maintenance window. All depends on the SLAs of course, but I have seen too many good intentions and "it will only be for a minute" going awry to risk it.
There is the possibility that you will not be able to attach that database without the log file. The database has to be shutdown cleanly, with no open or in flight transactions that would have to be rolled back/rolled forward. If that situation occurs, the database will not create a new log file - and you then have to put the old log file back.
The ideal solution is to perform a log backup, but in this type of case - the ideal solution probably wouldn't work. So, changing the recovery model will truncate the log. Once the log has been truncated, you can then shrink the file to the active VLF...
Issue a checkpoint - maybe a couple - to get the log to roll back to the beginning and shrink again. Repeat until you have the log file at the appropriate size.
Once you have that, switch recovery model back to full - take a full backup - and restart your transaction log backups.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs