Jeffrey Williams 3188 (4/29/2013)
Steve JP (4/27/2013)
Depends as the log file doesn't always want to shrink. If it doesn't then like I said a single file attach is a sledgehammer approach, never said it was a preferred method 😛This may not actually work - and you end up having to attach with the current transaction log file. Much simpler to just switch the recovery model to simple, issue a checkpoint - validate which VLF's are in use and shrink the file until it is the size you want.
You may need to shrink a couple of times, depending on when the log rolls back to the beginning of the file - but this will work and the log will shrink. Unless there is something else causing the log to not be able to be truncated (e.g. replication).
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.