babids
asked on
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.
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.
Hi babids,
run DBCC OPENTRAN and check whether there are any open transactions
Aneesh R!
run DBCC OPENTRAN and check whether there are any open transactions
Aneesh R!
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!
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!
ASKER
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.
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.
Just read this article in BOL
mk:@MSITStore:C:\Program%2 0Files\Mic rosoft%20S QL%20Serve r\80Tools\ Books\arch itec.chm:: /8_ar_da2_ 1uzr.htm
mk:@MSITStore:C:\Program%2
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)
Please post the output of dbcc loginfo(dbname)
ASKER
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.
post the output of the above query, i need to analyze it
ASKER
Disregard above comments. Out of 343 FileID, 42 has Status of 2. Any thoughts
Does these status =2 came as the last value ?
ASKER
yeah last 5 has status = 2. what does it mean?
one more thing ?
Are you using sql server 7 ?
also post the last 6 of the above result
Are you using sql server 7 ?
also post the last 6 of the above result
ASKER
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.
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.
ASKER
By the way, I am running Microsoft SQL Server 2000.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for Grade "B"
Did that Solve your problem ?
Did that Solve your problem ?
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....