A. This is because the log file is internally divided into smaller virtual logs and shrinking will only happen when the active part of the log is at the beginning of your log file. You can use the dbcc loginfo(dbname) command to see where the active part of the transaction log is - any logs marked with a Status of 2 are active.
The output below shows that the active virtual logfile is the last in the physical file, therefore no real shrinkage is possible.
FileId FileSize StartOffset FSeqNo Status Parity CreateTime
------ -------- ----------- ------ ------ ------ -----------------------
2 13041664 8192 0 0 0 1999-03-16 10:27:24.917
2 13041664 13049856 0 0 0 1999-03-16 10:25:56.730
2 13041664 26091520 0 0 0 1999-03-16 10:25:56.730
2 13041664 39133184 0 0 0 1999-03-16 10:25:56.730
2 13041664 52174848 0 0 0 1999-03-16 10:25:56.730
2 13041664 65216512 0 0 0 1999-03-16 10:25:56.730
2 13041664 78258176 0 0 0 1999-03-16 10:25:56.730
2 13557760 91299840 5 2 64 1999-04-06 12:32:27.833
In order to be able to shrink the log :-
1. Backup or truncate the log to make sure earlier Virtual Log Files are reusable (check their status)
2. Execute dummy transactions (in a loop) against a test table until the active virtual log moves back to the start of the list.
3. Execute DBCC SHRINKDATABASE or DBCC SHRINKFILE to mark a shrinkpoint. (The actual shrink is performed asynchronously normally)
4. Issue a backup or truncate log command to force the shrinkage to happen immediately.
If it is at the end of the logfile you could write a small while loop that does some inserts in a test table to move the active part to the beginning of the real file. Then the shrinkfile command should work ok - note that shrinkfile works asynchronously.
As a last resort you can always checkpoint the database, shutdown SQL Server and delete the physical logfile. When SQL restarts it will create a new 2Mb log device. Note that this is unsupported by Microsoft and whilst it has always worked for the author, others have reported problems doing this.