dbcc shrinkfile did not work

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???
motioneyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rboyd56Commented:
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
Kevin HillSr. SQL Server DBACommented:
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
motioneyeAuthor Commented:
what is the check point command that I can run in QA?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Kevin HillSr. SQL Server DBACommented:
Use YourDatabase
go

CheckPoint



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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
motioneyeAuthor Commented:
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
Kevin HillSr. SQL Server DBACommented:
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
motioneyeAuthor Commented:
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
Kevin HillSr. SQL Server DBACommented:
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
motioneyeAuthor Commented:
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
Kevin HillSr. SQL Server DBACommented:
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
motioneyeAuthor Commented:
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
Kevin HillSr. SQL Server DBACommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.