Tom,

To start off with an improperly sized log file such as that of which you are describing can actually hurt your performance in a magnitude that is unacceptable. You may find that resizing that TLog will increase your performance for that database by tenfold or better!

In a discussion on one of my other articles I actually provided some concrete testing that you can reproduce to see (as well as show the vendor) how an improperly sized log file affects your system. Go to: http://www.sqlservercentral.com/Forums/FindPost817363.aspx to see the testing and results; as well as the post I added immediately afterwards on how to create the testing yourself.

Now on to some things to ponder...

I'm not sure why there would be a 192gb TLog for a 8GB database.

First question: Is this actually space that is actively used?

Second question: Are backups being performed?

Third question: Does the TLog come empty to you and sized at this much?

If yes to # 1; check on regular backup schedule for TLog and adjust appropriately...or adjust recovery model if TLogs aren't being used at all. Once TLogs are getting regularly backed up, or the model is set to SIMPLe then you want to measure the TLog capacity before each backup or on a regular basis to deterimine how much space is actually required. I'd then resize it to that plus 10-20% for spikes.

If no to # 2: Start regularly making TLog backups; or set recovery model to SIMPLE. If yes, run a script just before TLog backup to determine capacity of TLog. Store the results to a table so you can create a historical record. After some time then review and adjust size as in above scenario.

If yes to # 3: Tell the vendor this is ridiculous to come that size! There's almost certainly no way a TLog could ever use that much space in running transactions. They (the vendor) needs to seriously discuss this with their DBA's to ensure this isn't continuing to happen. To require a client to have 200gb of space available for a DB that ultimately takes up 8gb of space is unacceptable in any shops definition. You don't by a 10,000 square foot house and only live in the garage...do you?? People do the opposite, use garage to store junk/vehicles and live in house...database is same way.

Also show them the results of timing of queries if you can get some. 1 set of times using their 192 GB of space and 1 of yours with say 4 GB of TLog space. You'll blow them away in time, and in how silly they are for not figuring out this gaping black whole for time!

I can't even imagine what the backup time is for that TLog, even with only 1% capacity used it's got to be minutes instead of seconds...and just even that much more time with more space used. You might also even do a time caparison of how much productivity ( and CPU resources ) you are loosing because of this serious misjudgement on their part. Even if they come back and say "we just want to ensure you don't run into TLog problems" you can refute "that's great, but what about the IO problems you are causing me when I back this up??"...and if you ever use this on a SIMPLE model...that will even further hurt your IO!!

I hope this gets you started. Feel free to PM me and I can discuss some other ways to go about this.

Thanks,

James