Shrinking Log Space

Information about the log off variant of alter database.

Although the log off option specifies the range the range of pages to be removed as logical pages, it is the associated physical pages that are actually removed. The logical pages remain in the database as unusable since they form a hole. A hole is one or more allocation units for which there is no associated physical storage.
Information about which allocation units—space that is divided into units of 256 data pages when you create a database or add space to a database—exist on the devices that are available in the master.dbo.sysusages table, which lists disk pieces by database ID, starting logical page number, size in logical pages, device ID, and starting offset in the device.
If the specified to page is less than the from page, the pages are switched—that is, the to page becomes the from page, and vice versa. If from and to name the same page, the command affects only the allocation unit containing that page. The command does not adjust the to page in a way that causes a command error.
The entire device is affected if you do not provide any clauses. This is equivalent to log offdevice from 0. This command specifies physical storage, removing every log page on the specified device from the database:
```
alter database sales_db log off mylogdev
```
If alter database detects an error, it does not execute, and returns a message indicating the reason, such as:
- The database log becomes too small.
- The fragments to be removed contain pages that are allocated to syslogs. That is, the active log occupies space in the log fragments to be removed.
- The amount of log free space after the fragment is removed is too small to accommodate the last chance threshold.