We have our BBLEARN Transaction Logs file size increasing rapidly by 10GB-20GB per day!!! it is now around 250GB, what is the recommended way to control the growth of this file by BB, and When i have to truncate the logs and also when i have to shrink the logs file, is this normal? or we have some problem? please help?? we are using MS SQL Server 2008 R2
Ala'eddin,
This is a SQL Server problem, not specifically a Blackboard problem.
Look at this article on shrinking the transaction log: http://msdn.microsoft.com/en-us/library/ms178037.aspx
Notice what is says in the very first "Note" area: Typically, truncation occurs automatically under the simple recovery model when database is backed up and under the full recovery model when the transaction log is backed up.
Also, look at the Transaction Log Truncation article at http://msdn.microsoft.com/en-us/library/ms189085.aspx :
Except when delayed for some reason, log truncation occurs automatically as follows:
Under the simple recovery model, after a checkpoint.
Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup. For more information, see "Log truncation under the full and bulk-logged recovery models," later in this topic.
However, having the log file truncated means that the transaction records are dropped--it is not the same as shrinking the file and freeing up actual space on the disk.
Open up the SQL Server Management Console, right-click on BBLEARN and choose Tasks > Shrink > Files > Log File. On that screen, you can see how much free space there is in your transaction log file. If you have a 250Gb transaction log file and you are doing complete database backups every day, you probably have 99%+ of the file free. Look at the instructions for shrinking files under http://msdn.microsoft.com/en-us/library/ms190757.aspx. Also, look at the "Log truncation under the full and bulk-logged recovery models" section of the previous article.
Sometimes, though, I've had a hard time getting the log file to shrink unless a backup or a database checkpoint has just been run. I can usually get around this by manually running a database checkpoint (http://msdn.microsoft.com/en-us/library/ms188748.aspx), then shrinking the file. A checkpoint writes all the transaction records that are recorded in the transaction log back to the database.
If you stop and restart the SQL server, you really do not help the situation, though--I know this from personal experience! So I'm not surprised that you had problems restarting the DB server.
Mike
Blackboard's recommendation is the Full Recovery model, so that's not a mistake in their maintenance plan recommendation article. However, at this point you really need to just get things functioning. Most schools (mine included) use Full Recovery, and do a nightly full backup plus transaction log backups every few hours, to keep the transaction log size down. I haven't tried to run in a SQL Cluster environment, though.
If it is never shrunk, the transaction log can easily grow 10-20Gb a day. However, the transaction log should be getting shrunk automatically whenever you do a full database backup or a transaction log backup, when a checkpoint occurs. What is your backup schedule like? I'd generally recommend doing a full database backup at least once a day, and a transaction log backup several times a day--as often as necessary to keep the transaction log file size to no more than a couple of gigabytes.
If you haven't seen it already, take a look at the Behind the Blackboard article on setting up a maintenance plan for SQL Server 2008: http://kb.blackboard.com/display/KB/SQL+Server+2008+Maintenance+Plan+Wizard
Thank you Mike
Thank you so much for your valuable information , this declare the issue, You right this is an MS SQL related issue , and I think that Blackboard meant the Simple Recovery Mode in their Maintenace Plan Recommendation Article, I'm going to make it under Simple Recovery Mode and I'll shrink the Log File when needed I think When More Than 5 GB will be fair enough, I hope That's will resolve the issue, It is more bad than retarting the SQL Server, we have SQL Cluster , and even when failing over from node to node it did go to Recovery Mode which is embarssing , What raised the issue is that we had a SAN Link Disconnection Last Week which guide us to All these issues
Thank You , Your help is really appricated
Kind Regards...