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
Solved

dbcc shrinkfile did not work

Posted on 2007-03-29
12
526 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2014 always on 31 58
Query for timesheet application 3 15
sql server query 18 33
SQL Quer 4 21
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

766 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