Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1044
  • Last Modified:

DBCC SHRINKFILE

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
babids
Asked:
babids
  • 8
  • 6
  • 2
1 Solution
 
fruhjCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi babids,

run DBCC OPENTRAN  and check whether there are any open transactions

Aneesh R!
0
 
fruhjCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
babidsAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
Just read  this article in BOL

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80Tools\Books\architec.chm::/8_ar_da2_1uzr.htm
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
babidsAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
post the output of the above query, i need to analyze it
0
 
babidsAuthor Commented:
Disregard above comments. Out of 343 FileID, 42 has Status of 2. Any thoughts
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Does these status =2  came as the last value ?
0
 
babidsAuthor Commented:
yeah last 5 has status = 2. what does it mean?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
one more thing ?
Are you using sql server 7 ?
also post the last 6 of the above result
0
 
babidsAuthor Commented:
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
 
babidsAuthor Commented:
By the way, I am running Microsoft SQL Server  2000.  
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Thanks for Grade "B"

Did that Solve your problem ?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now