Growth is either down to 2 reasons:
1 NO DISK SPACE
2 LDF Autogrowth restriction has been reached
First thing I would do is Change Recovery from FULL to SIMPLE if not done so already
Database recovery model - Change recovery model to Simple - SolarWinds Worldwide, LLC. Help and Support
SQL Mgt Studio - LDF - Database Recovery mode when setup for DR - FULL vs SIMPLE - SolarWinds Worldwide, LLC. Help and S…
Here is Good explanation of Simple VS Full Recovery:
http://msdn.microsoft.com/en-us/library/ms189275.aspx
Transaction Log Size
The transaction log should be sized based on the amount of data modifications made to a database and the frequency of the log backups.
Large data modifications, such as data loads or index rebuilds should be taken into account when calculating a log file size.
In simple recovery model the transaction log should not grow as the interval between checkpoints (which truncate the log) is based on the amount of data modifications made.
If the log does grow, it may be that there are long-running transactions or transactions that have been left open. Either may indicate a problem with the application.
In full or bulk-logged recovery model, if the transaction log grows it may indicate that the frequency of data modifications has increased and as such,
the interval between log backups should be decreased. It may also indicate long running transactions or that the log backup jobs are not running properly.
RESOLUTION STEPS :-
.LDF file grows out of control, How to recover:
1.) Make sure recovery model is set for SIMPLE and NOT FULL
http://knowledgebase.solarwinds.com/kb/questions/1359/Changing+Recovery+Model+of+database+back+to+Simple
2.) Backup the database, this will mark the data in the transaction logs as committed and no longer necessary.
Do not delete the LDF File if in-memory is being used, instead disable in-meory first, and always backup your database first before doing anything on the database and backup your db on a schedule.
3.) Shrink the database again to reclaim the log space. (PLEASE NOTE YOU MUST HAVE SOME FREE SPACE ON THE DISK IN ORDER TO RUN THE SHRINK )
How to: Shrink a Database (SQL Server Management Studio)
How to: Shrink a File (SQL Server Management Studio)
- 4.) If above doesn’t work as last resort is to detach the database (do not force it to detach if it gives an error that it can not detach, this will damage the database)
once detached delete the .LDF file and reattach the database. - 4.) Stop Orion Services.
5.) In the SQL Mgt Studio or Orion Database Manger, right-click on the NetPerfMon database and choose "Detach".
6.) Navigate to that directory where the MDF and the LDF files are stored, and delete ONLY the LDF file.
7.) Go back into the database manager, right-click on your SQL server, and choose "Attach Database."
8.) Select the MDF file, and hit okay.
9) Remove the LDF file entry hit OK
It will recreate will 0KB LDF File automatically.
Future Proof so it doesn’t recoccur
After above all should be fine, but have you check LDF File growth doesnt reoccur.
Also once set to SIMPLE Recovery, LDF file should never really be over 1GB if all running smoothly.
But can also configure LDF to Restricted Growth, so say LDF File on Drive over 100GB Free,
You could restrict LDF to few dozen GB just to be safe,
in case LDF growth reoccurred and LDF goes 100GB and brings down server due lack disk space.
How can i Examine / Open / Analyze what is in Transaction Log file ?
SQL Server transaction log format is not documented and therefore can’t be used to read data from it directly.
There are tools such as ApexSQL Log that can read the transaction log but it’s only because they probably spent a ton of time reverse engineering its format.
ApexSQL Log - SQL Server log explorer | ApexSQL
Options for reading are to:
a) figure the format on your own (not recommended)
b) get yourself a third party tool
c) using functions such as fn_dblog that are also not documented but can give you some details.
For more details please see the post below
http://stackoverflow.com/questions/7748653/how-to-open-the-sql-server-transaction-log-fileldf
How to read the SQL Server Database Transaction Log
http://solutioncenter.apexsql.com/read-a-sql-server-transaction-log/
