Solved

dbcc shrinkfile did not work

Posted on 2007-03-29
12
510 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
  • 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

920 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

16 Experts available now in Live!

Get 1:1 Help Now