Performance degradation on MS-SQL 2008 R2 server
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.
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
What size is your TempDB currently?
Name: tempdb
Size: 87.56 MB
Space Available: 32.70 MB
Name: tempdb
Size: 87.56 MB
Space Available: 32.70 MB
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.
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.
ASKER
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?!
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.
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.
ASKER
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?