Link to home
Start Free TrialLog in
Avatar of sqlagent007
sqlagent007Flag for United States of America

asked on

Log Shipping log files are 2.5GB for a database that is only 500MB

I have configured log shipping on one of my databases that is only 3GB total size. 0.5 GB for the data and 2.5 GB carved out for the logs. When I issue the command < DBCC SQLPERF ( LOGSPACE ) >, I am showing that I am using only 0.5% of the log. Why are all my .trn files in the shared directory 2.5 GB? Is there a way to shrink them? By my calculations there is only 130 MB of transactions that are different...

Please help.
Avatar of chapmandew
chapmandew
Flag of United States of America image

Have you reindexed recently or anything that would cause a lot of transactions to be written?  you can shrink them and it will not break your log chain...just DO NOT truncate them

use databasethelogisattachedto

dbcc shrinkfile('logfilename',1000)  --where 1000 is in MB
Avatar of sqlagent007

ASKER

There is no recent activity to fill the log. When I issue this statement

DBCC SQLPERF ( LOGSPACE )

It shows that I am not even using half of the 1GB, so I guess I wonder why the logs are not truncing automatically?

Also, since the actual space used on the log is only 130 or so MB, why are the .trn files showing 2.5 GB? I thougt they only backed up the data inside the log not the entire file&

Please help I am confused.
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes, that is what is freaking me out. They are 2.5 GB every time. there is no replication or anything else going on. I will try a shrink and see what happens. The database is basically a lookup DB, so it does not even get that many transactions per day.

thanks!
weird....anything returned by this:

dbcc opentran
no open transactions...and... the last 2 .trn files are only 245 KB...this looks more accurate.

Thanks for all your help!