| Home |
| Articles |
| Backup |
| Books |
| Certification |
| FAQ |
| Products |
| Replication |
| Scripts |
| Seminars |
| Training |
| TSQL |
|
| MSDN Fourms |
| Philippine SSUG |
| Fort Worth SSUG |
| Oklahoma City SSDG |
|
| Resume |
|
| MHS Enterprises |
| BlowFrog Software |
| FilAm Software |
| AcrylicAcetate.com |
| Bargain Humidors |
| Western Humidor |
|
|
Why won't my log shrink in SQL
7?
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 MS and whilst it has always worked for the author, others have
reported problems doing this. |
