Solved

DBCC SHRINKFILE

Posted on 2006-06-15
17
1,040 Views
Last Modified: 2008-02-01
My SQL DB log file is getting too big.
When i ran DBCC SHRINKFILE(My_Log,100), it says "Cannot shrink log file 2 (my_Log) because all logical log files are in use".

When i change targent size to 200, it says "Cannot shrink file '2' in database 'DBName'  to 25600 pages as it only contains 14048 pages.

When this DBCC SHRINKFILE actually works? I am cofusedand... and how we gonna know that it's shrinking the log file.
I think it is supposed to lower the log file size, right? My log files are in GB and still in GB.  

Please Advice.
0
Comment
Question by:babids
[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
  • 8
  • 6
  • 2
17 Comments
 
LVL 12

Expert Comment

by:fruhj
ID: 16912589
I think you have to do a DB backup no_truncate first, that clears the internal flags so SQL server basically clears out the Logfiles.
then you follow that up with the SHRINGKFILE to reduce the physical file size as seen by the OS.
Sorry for not having the exact syntax, I'm not in front of a server to check....
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16912766
Hi babids,

run DBCC OPENTRAN  and check whether there are any open transactions

Aneesh R!
0
 
LVL 12

Expert Comment

by:fruhj
ID: 16912855
hey babids,

  Just a side note, Aneesh has helped me out in the past - i think he was the one that got my DBCC shrinkfile working!
 He is top notch!
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:babids
ID: 16934269
Hello Aneesh
when I ran DBCC OPENTRAN: I got "NO Active OPen Transactions." message.

My log file is 3.09 GB. When i ran query DBCC SHRINKFILE(DB_Log,500), it does not seems like it is shrinking the log file, as the size of the log file is still 3.09 GB.

I am also running this query as a JOB but does not seems like it is shrinking the log fie.





0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16939399
Just read  this article in BOL

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80Tools\Books\architec.chm::/8_ar_da2_1uzr.htm
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17074841
run  dbcc loginfo(dbname)   command to see where the active part of the transaction log is - any logs marked with a Status of 2 are active.

Please post the output of  dbcc loginfo(dbname)
0
 

Author Comment

by:babids
ID: 17081606
I ran dbcc loginfo. and it seems like all the parts of transaction log file are active as all of them have status of 2. my log file is 3.17 GB now.


0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17082527
post the output of the above query, i need to analyze it
0
 

Author Comment

by:babids
ID: 17082743
Disregard above comments. Out of 343 FileID, 42 has Status of 2. Any thoughts
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17083002
Does these status =2  came as the last value ?
0
 

Author Comment

by:babids
ID: 17083280
yeah last 5 has status = 2. what does it mean?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17083441
one more thing ?
Are you using sql server 7 ?
also post the last 6 of the above result
0
 

Author Comment

by:babids
ID: 17085561
FileId      FileSize      StartOffset      FSeqno      Statu      Parity      CreateLSN
2      100663296      16114778112      1124521      2      128      1123931000013540100068
2      100663296      16215441408      1124517      2      128      1123931000013540100068
2      100663296      16316104704      1124516      2      128      1123931000013540100068
2      100663296      16416768000      1124515      2      128      1123931000013540100068
2      100663296      16517431296      1124514      2      128      1123931000013540100068
2      100663296      16618094592      1124513      2      128      1123931000013540100068
2      100663296      16718757888      1124512      2      128      1123931000013540100068


This loginfo is for different database whose log file is 15 GB.I have Shrink databse job on schedule as: DBCC SHRINKDATABASE (N'DBNAME', 0). I created this job from All Tasks --> shrink Database. Is this statement is wrong? Its not shrinking the Database at all.
0
 

Author Comment

by:babids
ID: 17085614
By the way, I am running Microsoft SQL Server  2000.  
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 100 total points
ID: 17087326
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17105210
Thanks for Grade "B"

Did that Solve your problem ?
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

623 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