We help IT Professionals succeed at work.

Performance degradation on MS-SQL 2008 R2 server

Olevo
Olevo asked
on
694 Views
Last Modified: 2012-06-27
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.
Comment
Watch Question

QPR

Commented:
Can't comment much on the performance issues as this can be 1,001 possibilities.
Log file sizes.... in a nutshell.

Regular backups of the transaction log will (maybe) keep it to a smaller size as the inactive portion of the log is overwritten following a backup. This means that the log does not need to grow in order to accommodate new transactions.

BUT
Even a regularly backed up transaction log can grow depending on traffic..... an index rebuild, a large amount of bulk data added.. that sort of thing.

Your 36GB log file may only contain 1MB of data but is still taking 36GB of physical diskspace.

A log file will not shrink itself following a backup.. it will however mark non-active transactions as overwritable.

If you need to shrink the transaction log then this can be done in either t-sql or through management studio. Whether you should or not is a different matter depending on circumstance.

What size is your TempDB currently?

Author

Commented:
What size is your TempDB currently?

Name: tempdb
Size: 87.56 MB
Space Available: 32.70 MB
QPR

Commented:
ok, that's ok
Should have said (but you may know) that a normal backup software won't touch the 2 (or more) SQL files, the mdf and the ldf.
SQL Server needs to be used to backup the data file(s) and log. The backup software will then pick up these .bak and .trn files.

Author

Commented:
Created backup database maintenance plans on SQL server itself, one for the daily full backup and other for the hourly transactional files.  After running successful backup jobs my transaction log file is still 36GB in size! Well, I can’t backup 36GB transaction log file every hour! So, I guess I need to shrink it, to what size? Or, SQL will automatically shrink it to smaller size?!

Commented:
When you take another transactionlog backup it will be much smaller, it only contains the new transactions.   After a transactionlog backup the transactions are cleared out of the file and so made place for the new transactions.
If you select your database in management studio and go to the files you will see there is a lot of free space in your logfile.

On a quiet moment you can shrink your transactionlog file to a size that represents a normal size.

Author

Commented:
Size of the log file still 36GB, however in SQL management studio when I select database to shrink (just log file) it says that 99% free space available in log file!!! Wow, runing backup must cleared transactions from the log file and now 99% of the file is free. Clicking to run shrink log file job desn't do anything, file still 36GB! How do you shrink log file, please?

Author

Commented:
Since my database wasn’t shrunk before would you advice to shrink it at least once? The current size of the database file right now is around 8GB and, as you know, 36GB for the log file. If I choose task to shrink database what would be correct settings to choose with % of maximum free space in files after shrinking? 3%, 5%, 10% or more?! I’m guessing that when I choose to shrink database, shrinkage of the log file will be included during this task as well?!

Other option would be to choose shrinking just the log file. I have tried this with “Release unused space” option… The sizes of the log file still the same – 36GB! I’m guessing that I need to choose other option: “Reorganise pages before realising unused space. Shrink file to: 37,485MB”. By the way, I didn’t specify this number to be 47MB?! It must be calculated based on the size of my current log size?!
 
I would really appreciate if someone could give me advices here, please. I need to do something (shrink or not) before Monday morning. Hopefully after shrinking performance of the SQL server will go back to normal.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.