Cannot shrink transaction log

Question

Bit of a headscratcher here.

I have a db that has less than 1GB of data, but a 40GB log file. Transaction logs are backed up daily, and there is not a lot of activity on this database; approximately once a week it records new payroll information and then regurgitates this data for reporting purposes. The database is set to Auto Shrink.

running sp_spaceused @updateusage = true yields the following information:

database_name   database_size   unallocated space
PayrollImports  39412.06 MB 105.00 MB

reserved    data    index_size  unused
321728 KB   278640 KB   42816 KB    272 KB

running DBCC shrinkfile (N'PayrollImports_log', 1 , notruncate) yields the following:

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
19  2   4991088 3456    4991088 3456

...the discrepancy between the UsedPages and the EstimatedPages is mystifying, but I continue with DBCC shrinkfile (N'PayrollImports_log', 1 , truncateonly) and get:

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
19  2   4991088 3456    4991088 3456

Nothing has changed at this point. The log file is still 40GB. So I think, maybe I have some open transaction. Running dbcc opentran should verify:

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Crap. Well maybe my indexes are fragmented. I will defrag them with sp_msForEachTable 'DBCC indexdefrag([PayrollImports], ''?'')' and try to shrink again:

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
19  2   4991088 3456    4991088 3456

Still nothing has changed. Ok, how about I reindex with sp_msForEachTable 'DBCC dbreindex([?])'?

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

...and now we get:

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
19  2   4991088 3456    4991088 3456

no change. All right, how about sp_msForEachTable 'ALTER INDEX ALL ON [PayrollImports].[?] REBUILD WITH (FILLFACTOR = 10)'?

Immediately, this fails with:

Cannot find the object "(One of my tables)" because it does not exist or you do not have permissions.

Huh? It's there, all right. I do a select top 10 * from (My table) and it comes up empty. Well, that's not right at all. This is a lookup table that should have over 200 rows. Is this a data corruption issue maybe? I collect the data from my development environment, re-insert it in.

But I am out of ideas. I cannot shrink this thing. What else can I try? Why are my UsedPages so incredibly higher than my EstimatedPages? What is going on here?

Answer 1

Check your output for DBCC LOGINFO within the context of your database. This will show you the status of all VLFs within the log file. You shrink operation will not reduce the log file past the last active one (Status=2). As VLFs are used in a sequential, round-robin fashion, you will need to run transaction log backups enough times until your active VLFs are at physical point in the file that you want to shrink to.

While you state the database is not very active, transaction log backups once a day seem very infrequent and, depending on the kind of transactions that run against this database, could be contributing to the continued growth of this database. I would recommend running your log backups at least hourly. If you don't need this kind of recovery in your database and just need to restore to sometime in the last 24 hours, I suggest you convert the database to SIMPLE mode and do a full backup once a day.

Mike Walsh provides a detailed explanation here.

Your reindexing will do very little to actually shrink or allow the file to shrink and, in fact, may hamper it. This is because reindexing will create additional transactions in your log that will be active until you run a log backup.

Additionally, some best practices recommendations:

• Turn off autoshrink. This is an incredibly bad practice for a number of reasons. You are likely creating more problems for yourself by enabling this "feature".
• Do not ever use truncate_only. This basically makes your log useless for purposes of recovery. You might as well go with SIMPLE mode and full backups at this point.