We take full backups of the DB every day (in full recovery mode) and keep them for 10 days. Because of our requirements, we don't want to take incremental or differential backups of the DBs.
Now we are observing that the ldf file size is growing too large. The question, is there any way to handle this scenario?
We don't want the ldf file to increase in size but at the same time we don't want to lose full backups.
Please then modify your database to be in 'SIMPLE' recovery mode instead of 'FULL' or 'BULK LOGGED'. The full recovery mode never truncates the transaction log, unless you do a transaction log backup. Only after it's backed up will SQL Server reuse the empty space in the transaction log.
Now setting the DB to simple mode, taking a full backup, and running a checkpoint is easy. Managing the transaction log itself might take some more work. You will want to ensure that you don't have too many VLFs (Virtual Log Files), which if you have the default setting of 1 mb growth you most likely do.
Here are the things to do:
ALTER DATABASE DBNAME SET RECOVERY SIMPLE WITH NO_WAIT GO
Then take a full backup. Do a checkpoint with a
USE DBNAME
CHECKPOINT;
Now let's see how many VLFs your transaction log has:
USE DBNAME
dbcc loginfo();
The number of rows returned are the number of VLFs. Assuming the status is 0 on the last VLFs you can shrink and reduce the number to a reasonable one. How big should your transaction log be you ask? If you don't know the work load make a educated guess and wait a week, reindex, and see how big it is. It should probably be about that size.
To shrink the log file you can use the GUI and script it out. Here is a youtube video on how to use SQL Server Management Studio (SSMS) to use the GUI to shrink: https://www.youtube.com/watch?v=xcT4DIMG-tI
Once you shrink it run DBCC LOGINFO(); again to see how many VLFs you have. It should be signifigantly lower. If it isn't, your final VLF is probably status: active (not 0). If that's the case you will want to wait for the transactions to finish where it is no longer active, perhaps checkpoint just in case, and run your shrink again.
Now you should be down to a low number of files and a small size. You don't want more than like 100 vLFs, 50 is the approx unofficial number a lot of people use to keep it around. Expand your transaction log a few chunks at a time so you have more than 10 but less than 60 VLFs. In a week, review your transaction log VLFs and size to see what it should actually be set to moving fwd and monitor it.
Notes: -Please don't use shrink DB or shrink logs unless it's a very specific situation like this, under most conditions this is really bad practice.
-Read up on VLFs and log file maintenance from the best.
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
http://adventuresinsql.com/2009/12/a-busyaccidental-dbas-guide-to-managing-vlfs/
