Go Premium for a chance to win a PS4. Enter to Win


Cannot shrink  Transaction Log File - although no Open Trans, DB in Simple Recovery, etc.

Posted on 2010-09-15
Medium Priority
Last Modified: 2012-05-10
I attempted to shring log file repeatedly without success.
I use
dbcc shrinkfile(mlsMail_log, 1)
The DB is in Simple Recovery Mode

Have tried
backup log mlsMail with truncate_only
several times, no change
DBCC sqlperf(logspace) - shows less than .01 % used
DBCC OPENTRAN  - shows no open Trans
DBCC LOGINFO('mlsmail') shows the following:

FileId             FileSize         StartOffSet   FSeqNo        Status            Parity           Create LSN
2      134152192      8192      42215      0      128      0
2      134152192      134160384      42216      2      128      0
Question by:dteshome
  • 4
  • 3
  • 2
  • +2

Expert Comment

ID: 33687928

your log file is not contiguous, so you need to create more transactions before being able to shrink it.  Here's an example:

DECLARE @vTemp CHAR(4000)
SET @vTemp = REPLICATE ('x', 4000)
CREATE TABLE TempForShrink (c1 CHAR(4000), c2 CHAR(4000))
INSERT INTO TempForShrink (c1, c2) VALUES (@vTemp, @vTemp)
INSERT INTO TempForShrink (c1, c2) VALUES (@vTemp, @vTemp)
INSERT INTO TempForShrink (c1, c2) VALUES (@vTemp, @vTemp)
INSERT INTO TempForShrink (c1, c2) VALUES (@vTemp, @vTemp)
INSERT INTO TempForShrink (c1, c2) VALUES (@vTemp, @vTemp)
INSERT INTO TempForShrink (c1, c2) VALUES (@vTemp, @vTemp)
INSERT INTO TempForShrink (c1, c2) VALUES (@vTemp, @vTemp)
INSERT INTO TempForShrink (c1, c2) VALUES (@vTemp, @vTemp)
INSERT INTO TempForShrink (c1, c2) VALUES (@vTemp, @vTemp)
INSERT INTO TempForShrink (c1, c2) VALUES (@vTemp, @vTemp)
DROP TABLE TempForShrink

Then you do your "backup log mlsMail with truncate_only " .  You may need to repeat this a couple of times before it really shrinks.  On my servers, I have created a SP that does it in a loop until I get the size I want.
LVL 39

Expert Comment

ID: 33688324
Unless you do no inserts/updates/deletes to your database, you do not want a 1MB transaction log.  The reason being is that even when in simple recovery mode, your database transaction log must be at least as large as the maximum amount of transaction log required between log truncations.

If you have a 1MB transaction log, set to auto grow, and you update 1B records in a table, the log will grow large enough to fit that update in the transaction log.  At the next log truncation (usually at checkpoints) the log will be cleared (but not shrunk).

So auto-shrink is bad, 1MB T-Log is bad.  Make it large enough to fit your largest transaction + some growth room.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33688379

I am not sure if you noticed but they are using Simple Recovery Model not Full.
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


Expert Comment

by:Som Tripathi
ID: 33688790
Run a 'checkpoint' and then try to shrink the log.

I am not sure about the concept of creating a table, inserting records and dropping table is standard method. At least I never did this.

Author Comment

ID: 33693905
Hi, everyone

I have tried what pbarry1 suggested ( ... create table tempforShrink ...)
done the
backup log mlsMail with truncate_only
dbcc shrinkfile(mlsMail_log, 1)

several times,
Result, same! no change

see output of  DBCC LOGINFO('mlsmail')  below:

FileId      FileSize             StartOffset          FSeqNo      Status      Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2           134152192            8192                 42223       0           128    0
2           134152192            134160384            42224       2           128    0

Author Comment

ID: 33693913
I have also done what sommerville suggested:
checkpoint and then shrink ...

no change
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33697576
What does no change mean?  What is the size?

Expert Comment

ID: 33697924
Please post the results of the "dbcc shrinkfile(mlsMail_log, 1)".

Accepted Solution

pbarry1 earned 2000 total points
ID: 33698488
I was able to reproduce your problem on one of my servers.  Here's a radical way of shrinking your log file.

  - make sure you read ALL this post before doing anything.
  - this procedure is not recommended on a production database and cannot be used if your database contains more than one data file
  - this procedure should not be used on a replicated database.
  - the database will be unavailable during the detach/attach steps.
  - make sure you don't skip the "BACKUP" step. It's your safety net.

1) Make a FULL backup of your database and make sure it is valid.  If anything goes wrong, you will find it quite useful...
2) Change the Autogrowth value of the log file to 1 MB
3) Detach the database
4) Do a copy of your data file under a new name (I know, step 1 makes a backup of the DB, why do another copy?  Well, better safe then sorry!)
5) Rename the log file under a new name (ex: LogFilename.old)
6) Execute this command: (replace the "DB Data Pathname" and "DB Log Filename" with your DB info)

   EXEC sp_attach_single_file_db @dbname = 'mlsMail', @physname = N'[DB Data Pathname]\[DB Data filename].mdf';

   => You should receive a message saying something like that:
        File activation failure. The physical file name "[DB Log PathName]\[DB Log Filename].ldf" may be incorrect.
        New log file '[DB Data Pathname]\[DB Log Filename].LDF' was created.

  - The new log file will be created on the "Data" drive with the same size as the log file of your [model] database (usually under 1 MB).  Make sure you will have enough space if SQLServer decides to recreate the log file with the same size as the current log file (250 MB) -> I have never seen this, but just in case...  
  - If you want your log and data files on different drives, you will need to move the log file after this procedure.  A simple detach/move log file/attach will do the work.
  - The user that executes the "sp_attach_single_file_db" will be the owner of the database.  That might not be what you want.  If not, change the DB owner afterward or use the correct user to issue the command (needs to be a member of the "sysadmin" role).
  - After the new log file is created, change the Autogrowth value of the log file to any value you want.  But I would follow BrandonGalderisi advice: a 1 MB log file is usually not a good idea.
  - If you want to test the procedure before doing it for real, here's a way to reproduce a DB with the same problem:
       WARNING: this DB will have a log file of approx 1 GB.  Make sure you have enough free space.
       a) Create a new database with:
                 - a data file of 3 MB (autogrowth = 10%)
                 - a log file of 1000 MB (autogrowth = 256 MB)
       b) Issue a DBCC shrinkfile(2, 1) on the new DB
       => Whatever you do, you won't be able the shrink the log below 250 MB (approx.).

Now, why does your log file didn't shrink?  The answer is that during one of the "DBCC shrinkfile", you ended up with 2 "virtual" logs of approx 125 MB each at the beginning of the log file.  Since a database log file must have at least 2 "virtual" logs, it can't be shrunk further.  And when that happens, there's nothing that can be done with commands such as "DBCC shrinkfile".  The only way around this is to recreate a new log file.

P.S. to: acperkins - yes, I did notice that the DB was in Simple mode.  But for SQLServer, it doesn't really make a difference as to whether you can shrink a log file or not after you issue the BACKUP ... WITH TRUNCATE_ONLY.  I was trying to force SQL Server to fill all the virtual logs so it would wrap around to smaller ones.  What I didn't notice was that there were already only two of them.  And they were larger than what dteshome was trying to achieve.  My apology for that.

Author Comment

ID: 33703382

"no change" means "no change"

The result of applying all the suggestions posted (with the exception of the latest by pbarry1: which I am looking at) is no change!

Here are the results:

sp_helpdb mlsmail

name      FILEiD      fILEnAME                           sIZE                
mlsMail      1      G:\sqldata\mlsMail.mdf      56903680 KB      
mlsMail_log      2      F:\sqldata\mlsMail_log.ldf      262024 KB      

dbcc shrinkfile(mlsMail_log, 1)


Cannot shrink log file 2 (mlsMail_log) because total number of logical log files cannot be fewer than 2.
DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
20     2           32753       32753       32752       32752

(1 row(s) affected)

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

Author Closing Comment

ID: 33703941
Thank you for the outstanding analysis!  pbarry1

I followed your instruction and was able to resolve this problem.

Much appreciate the excellent analysis and commitment t to detail.


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question