i've some ldf file very big: 45 gbyte.. against a mdf data file of 'only' 1gbyte.
My questions are:
-
Is it safe to use DBCC ShrinkFile for the log file (ldf) ?
-
How to programmaticaly limit the ldf file size ?
Thank you
Sql-server – Shrink Log file in sql server: is it safe
sql server
Related Topic
- Sql-server – how to consolidate multiple log files into one ldf file in sql2000
- Sql-server – SQL Server: Log file grows more and more, and do not get merged with the MDF file
- Sql-server – Rebalancing data between files on SQL Server gradually
- Sql-server – Is backing up SQL Server data and log file supported
- Sql-server – SQL Server Log File Won’t Shrink due cause “log are pending replication” on non replicated DB
- Sql-server – SQL Server database filled the hard drive and freeing up space isn’t possible
Best Answer
The short answer is "No, It's not recommended to shrink your log file(s)"
What is a solution is to run a full backup of your database, then detach your database, rename the log file to be something like database.ldf.old then reattach the database, without specifying the new location of the log file. This will create a new, empty log file.
The correct way to ensure that your log files do not grow excessively is to carry out regular backups of the transaction log. This is in addition to carrying out the regular full backups. This however, only applies if your database is using the Full recovery model. You can find which model it is using by right clicking on the database in Management Studio, then clicking on properties. Navigate to the options page and on that screen you will see the Collation Type, Recovery Model and Compatibility Level.
Unless you require the ability to restore to any point in time by replaying the transaction log to a specific transaction, using Simple recovery mode and ensuring that you backup your database regularly by doing a full backup will prevent your log file from growing excessively. It is also recommended that you have your log files on a separate volume (at a minimum) to stop a log file from possibly preventing login to your server (i.e. if it is on your boot partition, and fills the disk completely)