kriki: Transaction log file does not shrink

[Blog bot]

Ever tried to shrink your transaction log and it doesn’t shrink?

(VERY IMPORTANT: Generally it is NOT a good idea to shrink the logfiles, but because it is NOT a production server and I need space instead of performance, it is a good idea to shrink the logfiles [oh, and I also have simple recovery model]).

First, the transaction log is used to write all transactions in it and when it is committed, SQL Server moves that changed data to the database-file(s) (How SQL Server does this is not in the scope of this post.)

Internally, SQL Server is using virtual log files inside the transaction log file (see http://mibuso.com/blogs/kriki/2008/1...ansaction-log/).

Why doesn’t my transaction log shrink?

Generally the virtual log file in use is the last one in the physical file. That means that physical file cannot be shrunk until that virtual logfile is not active anymore. (It can also be active because of other reasons like replication, log backups, log shipping, … but again this is out of scope of this post [and you need full recovery model].)

Now, how to make SQL Server move to another virtual log file?

Easy, use the database. Or better : make writes to it like filling up a table with some data and then delete it again until you get to another virtual log file.

And this is what I did this time, but it didn’t work. I created an extra transaction log and make sure SQL was writing in that one. But how do I know it is writing in that new physical logfile and that the first physical logfile is not active anymore?

"DBCC LOGINFO" gives me that info (see again http://mibuso.com/blogs/kriki/2008/1...ansaction-log/).

It generates something like this (I have 2 physical log files with 1 virtual log file in each):

FileId        FileSize        StartOffset        FSeqNo        Status        Parity        CreateLSN

2        1966014464        8192        440        2        64        0

5        13041664        8192        428        0        64        0

The first column is the "FileId". In SQL, each file in the DB has its own Id.

"Status" is the field that I am interested in. 2 means it is in use and 0 means it is not in use. So I know that the first virtual (and in this case also physical) logfile is in use.

I started creating and deleting records, until I got to this situation:

FileId        FileSize        StartOffset        FSeqNo        Status        Parity        CreateLSN

2        1966014464        8192        440        0        64        0

5        13041664        8192        428        2        64        0

The current size of the logfile is around 1.9 GB (the column "FileSize" is in Bytes).

Fine, the first logfile is not active anymore, so I can shrink it.

But it didn’t shrink the file. When I launched the shrink using the GUI I didn’t get any error but maybe there was some message anyway.

So I made it generate the code and launched it manually.

DBCC SHRINKFILE (N’CRONUS_Log’ , 100)

This command shrinks (or at least tries to…) the logfile of the logical databasefile name "CRONUS_Log" to 100 MB but it failed.

Lets see if there is some message…

In the Messages window I got something like this:

Cannot shrink log file 2 (CRONUS_log) because total number of logical log files cannot be fewer than 2.

So what?

Well, I have only 2 virtual logfiles in it. Even if I have more virtual logfiles in the second logfile, I would still be unable to shrink the first logfile because you cannot shrink a logfile to a size SMALLER than the first virtual logfile.

Removing the physical logfile then? No, you can’t because it is the primary logfile and you cannot delete that.

BTW: it is also a good idea to generate the commands that will be run when doing something using the GUI. Sometimes you get some interesting (or important) message and also you can learn something.



Читать дальше