1

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.

vijayp
  • 2,786
  • 1
  • 17
  • 20
RickyRam
  • 19
  • 1
  • 3

1 Answers1

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

dbafromthecold
  • 982
  • 1
  • 9
  • 19