Solved

dbcc shrinkfile did not work

Posted on 2007-03-29
12
521 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
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.

777 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