troubleshooting Question

Performance degradation on MS-SQL 2008 R2 server

Avatar of Olevo
OlevoFlag for Australia asked on
Microsoft SQL Server 2008
9 Comments2 Solutions696 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
QPR

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros