We have been experiencing significant performance degradation on our MS-SQL 2008 R2 server over the past few weeks and it seems to be getting worse on a daily basis. This is a virtual server on Hyper-V host and throwing more resources at SQL has done nothing. What I have noticed that the size of the transactional log files for some of the SQL databases is quite large. Some of them have log files size of 36GB or more!!! Is that normal?! I don’t think so. I’m not a SQL guru but I thought that transactional log files supposed to be truncated of inactive transaction?! When it actually supposed to happen? During backup of the SQL server or this is some kind of internal SQL process? Well, if backup process doesn’t remove inactive transactions from the transaction log it must be some other procedure to maintain size of the transaction log files. I’m assuming that transactional log files needs to be “shrunk” to a normal size somehow?!
We are using MS-DPM 2010 Server to back up all VM’s on our Hyper-V host. I’m guessing that DPM server is backing up vhd files of VM’s not even knowing that this particular VM has running SQL server on it. If SQL server doesn’t have maintenance backup plan, nothing will be done to keep transactional logs under the leash.