Solved

dbcc shrinkfile did not work

Posted on 2007-03-29
12
537 Views
Last Modified: 2007-11-27
Hi,
I run the dbcc shrinkfile (2) but QA return me a msg like below

Cannot shrink log file 2 (Report_Log) because all logical log files are in use.

(1 row(s) affected)

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


what is this???
0
Comment
Question by:motioneye
[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
  • 6
  • 5
12 Comments
 
LVL 16

Expert Comment

by:rboyd56
ID: 18818425
If this is the transaction log that you are trying to shrink, it appears the transaction log has not been truncated or backed up.

If you are in Full recovery mode, you backup the transaction log to disk or truncate the log to empty the space.

Backuip log database to disk 'backup file'
or
Backup log database with truncate_only
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18818428
1. Have you backed up the t-log?
2. If so, run a Checkpoint command in Query Analyzer 15 or so times, then try the shrink
0
 

Author Comment

by:motioneye
ID: 18818706
what is the check point command that I can run in QA?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 21

Accepted Solution

by:
Kevin3NF earned 500 total points
ID: 18822708
Use YourDatabase
go

CheckPoint



Run that  a dozen times...just hit F5 over and over
0
 

Author Comment

by:motioneye
ID: 18828376
Hi,
after running checkpoint for several time and many times, I manage to shrink the file,,, Is that mean checkpoint will check pointed every transaction and saved it then only the Tlog is free to be truncated or shrink?
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18828402
nope...checkpoint basically moved some existing data in the file to the front of the log file instead of the end, where it was sitting.

Think of it as "defragging" the log file, although that is not the correct technical explanation.  If you want the details, look up Virtual Log files in Books ONline or Google
0
 

Author Comment

by:motioneye
ID: 18830814
Ok,
So can I say that I should run the dbcc loginfo to check whether the log has been written to disk  or not and if the active portion was not on top I should start to run the checkpoint untill all the status=0 then only file can be truncate or shrinking,
is this make sense???
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18830853
half right....except for very bizarre circumstances, the log info will be written to the data file via an automatic checkpoint that occurrs about every 30 seconds, if I remember correctly

The manual checkpoint did have the effect of moving the VLFs around, enabling a successful shrink.

My soapbox:  Do not shrink and then let autogrow data or log files.  Set them to an appropriate size and growm them manually as needed.  This is a normal DBA task.  Autogrow is a fail-safe, not a maintenance task :)
0
 

Author Comment

by:motioneye
ID: 18831387
Hi,
in our environemnet we do practise multiple db in one instance, if let say I do not shrink the file and let it grow then it will end up with no space left in disk and this will cause other that need for grow  stop from responding.
but if let say I do a checkpoint in QA do I need to restart the s
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18831827
If you back up the T-log file regularly, the contents within it are removed and the internal space is freed, which means the physical file does not need to grow.  If you don't back up the T-log, you get huge filegrowth.
0
 

Author Comment

by:motioneye
ID: 18832459
Yes, we backup the Tlog regulary but Tlog will not shrink the file size, that is why in my earlier questions I did mentioned that the shrink didn't work..even after few trial with shrinking almost 20 trial and only I manage to redue the small protion of free space from Tlog.
so do u think if I run the checkpoint together with dbcc loginfo to check on the sequence of the Tlog, would this help me?
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18832565
My point is that if you manually set the t-log to an appropriate size, you will not need to shrink it, and you will rarely need to grow it.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

730 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