Solved

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

Posted on 2010-09-15
11
1,175 Views
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
0
Comment
Question by:dteshome
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 4

Expert Comment

by:pbarry1
Comment Utility
Hi,

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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
pbarry1,

I am not sure if you noticed but they are using Simple Recovery Model not Full.
0
 
LVL 8

Expert Comment

by:Som Tripathi
Comment Utility
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.
0
 

Author Comment

by:dteshome
Comment Utility
Hi, everyone

I have tried what pbarry1 suggested ( ... create table tempforShrink ...)
done the
backup log mlsMail with truncate_only
and
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
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:dteshome
Comment Utility
I have also done what sommerville suggested:
checkpoint and then shrink ...

no change
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
What does no change mean?  What is the size?
0
 
LVL 4

Expert Comment

by:pbarry1
Comment Utility
Please post the results of the "dbcc shrinkfile(mlsMail_log, 1)".
0
 
LVL 4

Accepted Solution

by:
pbarry1 earned 500 total points
Comment Utility
I was able to reproduce your problem on one of my servers.  Here's a radical way of shrinking your log file.

WARNINGS:
  - 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.

STEPS:
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.

IMPORTANT:
  - 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.
0
 

Author Comment

by:dteshome
Comment Utility
Hi,

"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
RESULT

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)

RESULT

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
0
 

Author Closing Comment

by:dteshome
Comment Utility
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.

D.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL server 2008 SP4 29 31
How to iterate through each record without using cursor 2 20
Log Backup 2 11
Report Builder 9 22
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now