1

This question already has an answer here:

Already i asked this question but still i'm not getting the very clear answer. In my server holds more than 20 live databases in that my logfile(.ldf) is use to grow more than 5 GB per day.

Using the below script i'm shrinking my logfile size on day-to-day basis.But after reading the articles i'll come to know my travel is in wrong way.Know i need to start the Logfile shrinking in a good way.

USE ABC;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE ABC
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (ABC_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE ABC
SET RECOVERY FULL;
GO

My Database Recovery Model is 'FULL' because if i'm keeping my database recovery model in simple might be data loss can happen for that only i'm keeping my database in the Full recovery model.

Doing the above Querying is a bad idea. Here i need expertise suggestion to keep my database logfile in a best way.As well as i need to avoid the log file growing for each and every day.

marked as duplicate by Aaron Bertrand Sep 25 '14 at 12:00

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 4
    Are you running regular transaction log backups? – dbafromthecold Sep 25 '14 at 11:18
  • 1
    If you are using this particular method of "shrinking" the log then you might as well be in SIMPLE recovery. Every time you do it you are breaking the backup chain. I'm guessing that @DBAFromTheCold is right and you are not taking regular log backups. – Kenneth Fisher Sep 25 '14 at 11:30
  • You are doing blunder by taking DB in simple recovery – Shanky Sep 25 '14 at 11:40
  • No. I'm not running regular log backup – RickyRam Sep 25 '14 at 12:50
  • Using Maintenance plan whether we can control the logfile growth... – RickyRam Sep 25 '14 at 12:57
2

In order for you to manage the transaction log size of your database(s) which is in full recovery mode you should be taking regular and frequent transaction log backups. The frequency should be according to RPO and RTO defined

Do NOT change the recovery mode and shrink the log. You stated in your question that you didn't want any data loss by doing this you would lose point in time recovery. In the full recovery mode this is done by taking regular transaction log backups so that in the event of a failure, the backups can be used to restore the database to a point in time.

By changing the recovery mode, you are breaking the log chain which will prevent your ability to restore the database to a point in time.

Log backups will free space in the log by marking virtual log inactive or reusable. This allows space within the log to be re-used and prevents growth (if the log is big enough initially).

You should also be aware that putting the database in the simple recovery mode will not prevent any transaction log growth. The log will record all active transactions and will grow accordingly. The difference is that in the simple recovery model, the virtual log files are marked for re-use after a database checkpoint.

Here's a couple of links for more information on the transaction log:-

Overview of the transaction log:- http://msdn.microsoft.com/en-us/library/ms190925.aspx
Transaction log backups:- http://msdn.microsoft.com/en-us/library/ms191429.aspx

  • If i'll take the regular transnational log backup i can avoid the logfile growth right... – RickyRam Sep 25 '14 at 12:55
  • 1
    That depends. Do you have any long running transactions that may stop the vlf truncation from happening and have you pre-sized your transaction log to a good size for that database? Do you have anything else that could hold the log hostage such as availability groups, replication, etc? All things to think about. – Sean Gallardy Sep 25 '14 at 13:08

Not the answer you're looking for? Browse other questions tagged or ask your own question.