Solved

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

Posted on 2010-09-15
11
1,183 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 4

Expert Comment

by:pbarry1
ID: 33687928
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
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33688379
pbarry1,

I am not sure if you noticed but they are using Simple Recovery Model not Full.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 8

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

Author Comment

by:dteshome
ID: 33693905
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
 

Author Comment

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

no change
0
 
LVL 75

Expert Comment

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

Expert Comment

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

Accepted Solution

by:
pbarry1 earned 500 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.

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
ID: 33703382
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
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.

D.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

734 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