SQL: reduce ldf file

DISCLAIMER FIRST: I am not a DBA.

There are two parts to this. 1st. Reducing the size of the file now. Please take this with a bucket of salt. Preferably have a window of outage if possible.  You may take a full back-up of the database, carry out this activity and then once again take the full back-up of the database. Your log file size is now reduced.

2nd. Going forward, take a full back-up of the database every week-end (or whatever frequency you choose, it could be daily also) and then take transaction log back-up at regular interval, such as 2 hours. This helps you attain point in time recovery. Which means, in case of any issues, you can first restore the last full back-up and then all the subsequent transaction log back-up up to the last one.

Brent Ozar, SQLServerCentral, among others, are probably the best resource to gain more insight on this topic.

Important point here is taking transaction log back-up at a regular interval, will result in reusing the log file space again and again and ultimately result in not bloating it as it has now. This is the case with Full recovery model.

Please mention SQL Server version as well. As my comment will not be applicable to earlier versions. It will also help other experts to help you out better.

Take a full backup, and log backup first,

Then you can change to simple (ideally after hours)

Ideally another full backup and then your log file is ready to be resized.

Use SSMS, right click on your Database, go into TASKS, then SHRINK then FILES - a window will open. Use the drop down to select log file. Use the reorganise pages radio button and set your size.

Change back to full recovery, and one last  backup / log backup (it sets pointers in the log if you need to recover.

Sounds fiddly with all the backups, but what you are doing is resetting logical pointers in the log file to ensure you have a clean base to work from....

There are other methods available, but lets start with the more conventional first :)

Dont shrink to minimum, it will grow again, and that can cause (fragmentation) problems.....

I have written a couple of articles that might provide more insights : https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_657-Managing-the-Transaction-Log-for-the-Accidental-DBA.html
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_691-Managing-Fragmentation-for-the-Accidental-DBA.html

Hi LD16,

Here are the steps from an exercise I performed recently in production. Also, I agree with Nitin to have some sort of down time specially if this is a production environment.
Please note this database was not part of any sort of replication.

sp_helpdb DB_NAME                                                                                                                    

DB_NAME		1	D:\SQLData\DB_NAME.mdf	PRIMARY		2812928 KB	Unlimited		               1024 KB		data only
DB_NAME_log	2	E:\SqlLogs\DB_NAME_1.ldf	NULL		        3164032 KB	2147483648 KB		10%		        log only

DBCC LOGINFO -- 260 Rows 

Executed recursively 2-3 times 

USE DB_NAME
GO
DBCC SHRINKFILE (N'DB_NAME_log' )
GO

No Help,

 sp_helpdb DB_NAME 

DB_NAME		1	D:\SQLData\DB_NAME.mdf	PRIMARY	2812928 KB	Unlimited		         1024 KB		data only
DB_NAME_log	2	E:\SqlLogs\DB_NAME_1.ldf	NULL	        2984064 KB	2147483648 KB		10%		log only

USE [DB_NAME]
GO
ALTER DATABASE [DB_NAME] SET RECOVERY SIMPLE WITH NO_WAIT
GO

sp_helpdb DB_NAME 

DB_NAME		1	D:\SQLData\DB_NAME.mdf	PRIMARY	2812928 KB	Unlimited		         1024 KB		data only
DB_NAME_log	2	E:\SqlLogs\DB_NAME_1.ldf	NULL	        2984064 KB	2147483648 KB		10%		log only

USE DB_NAME
GO
DBCC SHRINKFILE (N'DB_NAME_log' )
GO

sp_helpdb DB_NAME 

DB_NAME		1	D:\SQLData\DB_NAME.mdf	PRIMARY	2812928 KB	Unlimited		         1024 KB		data only
DB_NAME_log	2	E:\SqlLogs\DB_NAME_1.ldf	NULL	            32448 KB	2147483648 KB		10%		log only


Put database back into FULL mode 

USE [DB_NAME]
GO
ALTER DATABASE [DB_NAME] SET RECOVERY FULL WITH NO_WAIT
GO

DBCC LOGINFO

Generate script for backing up and overwriting TLOG (or append your choice) and put it as a scheduled job
Do note difference the difference in INIT and NOINIT in T-LOG backup script

	BACKUP LOG [DB_NAME] TO  DISK = N'D:\SqlBkups\DB_NAME_TLOG.trn' WITH 
	NOFORMAT, INIT,  NAME = N'DB_NAME-Transaction Log  Backup', 
	SKIP, NOREWIND, NOUNLOAD,  STATS = 10
	GO


Add a scheduler Job 

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'BACKUP_TLOG_DB_NAME', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_netsend=2, 
		@notify_level_page=2, 
		@delete_level=0, 
		@description=N'TLOG BACKUP DB_NAME', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'BACKUP_TLOG_DB_NAME', @server_name = N'USHSTCDBD1'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'BACKUP_TLOG_DB_NAME', @step_name=N'Backup TLOG', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_fail_action=2, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'BACKUP LOG [DB_NAME] TO  DISK = N''D:\SqlBkups\DB_NAME_TLOG.trn'' WITH NOFORMAT, INIT,  NAME = N''DB_NAME-Transaction Log  Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO', 
		@database_name=N'master', 
		@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'BACKUP_TLOG_DB_NAME', 
		@enabled=1, 
		@start_step_id=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_netsend=2, 
		@notify_level_page=2, 
		@delete_level=0, 
		@description=N'TLOG BACKUP DB_NAME', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', 
		@notify_email_operator_name=N'', 
		@notify_netsend_operator_name=N'', 
		@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'BACKUP_TLOG_DB_NAME', @name=N'Backup TLOG BKHIST', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=15, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20170905, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

Select allOpen in new window

NOTE: This is just a suggestion and shall be tested thoroughly.

Thanks,
Suri

what kind of data recovery do you want? if your database server dies today, would you be ok with yesterdays data in last nights backup or do you need all the information from today as well?  if last night is ok you can change to simple mode any time then do a full backup straight away. Check backups are running each night.
Too lose minimum data, leave in full mode and setup transaction log backups as well as full backups and dif on a schedule to suit your needs.
Too shrink the log file after the backup, dbcc shrinkfile(logfilename, 1000) . you need to find your logfile name in properties of database.
this doesn't guarantee it will stay that size but is a start.

Thank you very much for your feedback.
What I would like to understand is why a previous backup in full mode is required prior changing to simple mode. Can I restore the backup issued from a full backup recovery to the same database which has been changed in simple mode ?
@Mark Wills: the fact that I change to simple mode to full mode in first place and I perform resize operation log file and then I change again to full mode will generate ldf file increase again? If I don't use log file why not keep in simple mode?
If I need to recovery a database and restore in another server the most important for me are the tables and data related and not log transaction so in case of issue if I just restore to another server the last backup generated by maintenance plan after the crash. This operation will not be enough? I prefer to avoid file space saturation which can generate data base corruptions instead of having database in full recovery mode as I don't use log file and either transactions.

Thank you for your feedback.

@Scott: Thank you very much for your feedback.
I was able to reduce ldf file as much as possible after changing from full to simple recovery mode and shrinking log files.

@LD16,

The Log File is an important beast, a necessary evil if you like....

It is easy enough to manage, and a lot of that management can be automated (it is a relatively straight forward process to schedule regular LOG backups).

The reason why it is important is for Database Recovery, part of your Disaster Recovery (DR) plan.... A security blanket for your Database.

So, if your database is important for your operations, then revert to FULL recovery and schedule LOG backups to run every hour (or more frequently) and a full backup each night.

It really depends on how your organisation works, and what the Database is used for.

If a developers database, then sure, you might leave it in Simple. If a production database then it is ill-advised to leave it in Simple, unless you have alternate methods of recovering data up until that point of failure. Like manually re-keying Orders....

And as truth would always predict LOG (size) troubles (if not pro-actively managed), it should also be expected that (at some point in time), there will be a failure of the database. And typically at a most in-opportune time.

Imagine (as the DBA) having to tell your users that they have lost all the data since last successful backup from the night before.

Now we dont know your environment, so cannot, and should not, make any recommendation other than FULL backup (best practise).

But again, it depends on your environment and if (by way of example) if Orders come in by some FTP / Web feed / email process, then you might have a viable/alternate recovery plan. But make sure your Disaster Recovery (DR) plan is viable.

Failure to plan for  DR is planning to fail.  

Contrary to Scott's opening sentence, the reason why you really should backup Log file first is NOT for recovery. It is to set those pointers I was talking about.... And if in Simple, you cannot use the log files, so that last sentence doesnt make sense either (other than you really must take a full backup which also resets the log chains).

You should read those articles.... Dont shrink to as small as it can be - that can create other problems.  

You asked for 'best practices', so at the very least read : https://docs.microsoft.com/en-us/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file?view=sql-server-2017#Recommendations or http://www.sqlservercentral.com/articles/Administration/64582/ 

And pleased to hear that you have successfully shrunk files.

Little bit disappointed that you didnt see any helpful advice/steps from my previous post....

Cheers,
Mark Wills

Edited extensively: 2018-08-15, 1:45PM CT.

Using logs to recover is a complex process, far more involved than just taking log backups.  You must guarantee you can retrieve them, know how to apply them, and be willing and able to do so.  Unless you will truly do all of that, there's no point in doing log backups.  Keep in mind: if you are missing even 1 log backup file in the chain, you cannot recover using log backups.

Simple model has another advantage of doing less logging, naturally resulting in less logging overhead, including the potential for minimal logging of bulk inserts ( which is not available in the full recovery model).

Of course there is a downside: you can't recover to a specific point-in-time.  If that's critical to you, you will require log backups and must accept the accompanying overhead.

But you have some lesser recovery options in simple mode.  For example, you could do differential backups at fixed intervals, say every hour or two.  It's somewhat easier to recover using diffs, since you only ever have to apply one of them.  But you must still guarantee that you have access to the diff backup file.  Again, if you don't need point-in-time recovery, you could do the diff backup to the same filename, meaning you only need space for one diff.  The total volume of db modifications will be a big factor in whether diff backups are practical for a given db.  

Diff backups are (usually) great for very large dbs that have few mods during normal operations, but can also be very useful for db with heavier amounts of activity.  For standard data, 1GB is a huge amount of data, particularly with backup compression.  
Many dbs are non-critical, for which I use simple recovery with weekly fulls and daily diffs.  
For those dbs that are trully critical, naturally I use full recovery with tran log backukps.  
For some deliberately non-critical / utility dbs, I use delayed durability for maximum performance.

I've been a full-time professional DBA for 30+ years, and focusing almost exclusively on SQL Server for the last 17+ years.  I do understand db backups and recoverability.  Often db data is not quite as "non-loseable" as people imagine.  Do you want to lose data, ever?  No, but it is really worth the resources to insure you don't?  Most often not.  Remember, even with tran log backups, you can lose recent data (after the last tran log backup), so tran backups are not a guarantee against lost data either.  Often it's better, i.e. more practical, to accept that maybe once every seven years or so you might have to actually lose some data, but avoiding huge amounts of cost and overhead trying to prevent it.

Good post Scott....

I agree. It can be very difficult (and very expensive) setting up an environment where any transaction loss is considered a disaster. In those environments, the DBA would be immediately "retired" if the attitude was "better to accept that maybe once every seven years or so you might have to actually lose some data".

But we arent talking about that environment. We are (in my opinion) talking about being aware of the impact (on the business) of decisions you are about to make.

And FULL recovery isnt just for Restore, it can also be used to help repair a corrupted database (e.g. Page Recovery).

Using the tools available to you will help minimise impact of data loss. And, one needs to assume it will happen (even if seven years).

And the tools arent that difficult to use. Which might be too easy to say given experience, but then, there is always EE to help :)

At less than a hundred dollars per Terabyte, even a portable SSD can be used to archive backups (and that can be automated as well). So, costs dont have to be big to have that peace of mind even in the smallest of environments.

Because we dont know the environment, all we can really do is educate (to a point) and leave the decision in the hands of those responsible for, or, have vested interest in that environment.
.
Cheers,
Mark Wills

At less than a hundred dollars per Terabyte, even a portable SSD can be used to archive backups (and that can be automated as well). So, costs dont have to be big to have that peace of mind even in the smallest of environments.

Again, that's just on the surface.  It's still not anywhere near that easy:

How long do you keep backup files?  Who purges them?
Who tests the restores?  And on what schedule and on which database(s)?

Who does restores?  If it's a single person, what do you do if he/she is not available or leaves the company?
How do you find a specific backup file(s) when you need them?

Who insures the drive has power and cooling?
Who makes sure that external drive isn't stolen?
What if backups quit working in the middle of the night?

I think issues like those will cause you far more work than the extremely rare data loss if/when it actually occurs.  Not that it shouldn't be done, but this process is complex and requires lots of on-going work.  At International Paper, for example, we had a whole team of people who managed the backups to make them work consistently and properly.  Most places don't have those resources.

Before you just say "slap an external drive on there", be sure you understand what you might be in for if you're the one who ends up having to maintain it!

Oh dear Scott...
So, those problems / challenges dont exist if we follow your advice and leave it in SIMPLE ?
Dont think so. You should still backup a database regardless of recovery model, and so, those issues you raise exist any way. That's why every site *should* have some kind of DR plan....

Just to reference some of the other posts in this thread and provide worthy mentions....

Page recovery : https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-pages-sql-server?view=sql-server-2017

Free e:Book (via Nittens post) : https://www.red-gate.com/library/sql-server-backup-and-restore

And have endorsed a few posts in this thread that would have been worthy of point recognition.... For example Suri : #a42651763  good script, just needs to prefix comments with "--" or "/*   */"

@Mark:

So how long have you been a professional DBA?  Just curious.

Presumably they're already doing full backups, so they are managing that part now.  You stated that it was easy to just slap an external SSD on there and start doing tran log backups that way.  It's not.

Backup and recovery is a complex process, not just a physical device.

For, say, tran log backups every 30 minutes for 100 dbs, that's ~4800 files a day.  That's a lot to manage, esp. if that's not your actual job, but just another task you have to do.

Since most people aren't skilled or practiced in restoring log files, and it's only needed very rarely, it's something that should be done only when  necessary.

I did state that if you need to recover to a point-in-time, you must use full recovery and do log backups.

Whatever you choose to do, be aware of all the work required to do it.  Don't promise your boss(es) and/or your client(s) that you will have full recovery to some time period until/unless you are truly prepared to do it.

@Scott :

I have been a DBA on and off for a while, and on different platforms. I have run departments of DBA's. I have employed DBA's so I dont have to get my hands dirty every time. I have run/worked in consulting firms specialising in IT services. Some of the sites I have worked with/on/for include some of the biggest and some of the smallest. I have been a Gold Partner and a MVP for data platform and SQL Server respectively.

I have had the great pleasure of actively and collaboratively working with some of the best minds in the business (worldwide)...

I have done well enough out of IT where I can now pick and choose what I do and who I do it for.

I do not call myself a Professional DBA anymore - it would be demeaning of all I have achieved. But, I do consider myself well enough experienced, despite your constant "holier than thou" taunts and challenges.  

I have no idea if they have 1 db or 100 db's in FULL recovery mode. I have no idea if a trans log backup is needed every 30 minutes, or just overnight.

I do know that trans log backups are required in a FULL recovery mode, and (guessing) that not doing them has resulted in an unanticipated  large log file in this environment. As Nitten also alluded to in his first post. Suri even included script to automate.

I do know that they have more than 1 db (the opening sentence in the question header), and so, might be applicable to other db's.

I referred to an external SSD by way of example of cost - it does not have to be an "all-in" approach requiring big costs and/or resources. I mentioned it primarily by way of example in that context. Secondarily, a reaction to the thought that maybe there is a disk space problem (again from the first sentence).  

We were asked " I want to avoid database corruption or and unwanted consequence. What is the best practice to reduce ldf file?"

In which case, FULL recovery, and regular backups of the Transaction Log does meet both requirements.

Yes, there are always consequences of decisions.... Thats why we need to educate and let them make the decision that best suits their environment.

The OP is obviously a Premium member, and so, has the vast resources of EE to help in the advent of needing Recovery, automation, backup options and hardware configurations.... For some members it does pay to belong....

Some of the attitude you continually expose is not appreciated. Being such an experienced Professional DBA, and with all the points and levels you have attained, I should be expecting more wisdom, and less attitude. Which is why I endorsed your second post...

The only really obvious "attitude" I've seen here is "Oh dear Scott...".

The rest is a technical discussion.  Yes, it's easy to just say "use full for everything, don't lose any data".  I was just pointing out that it requires very complex steps to actually accomplish that.  IF you don't have someone on your staff who's experienced in that process, consider carefully before you commit to doing it.  In practical terms, it may be better to use another approach -- such as simple recovery with relatively frequent differentials -- and understand the risk.

I'm not saying "don't use full."  I'm saying fully consider its full requirements and possible alternatives.  It's very common for tran log backups to go from every 1 hr to every 30 min to every 15 min.  At that point, 100 db = ~10,000 files a day.  Just be aware of that.  And full recovery will certainly not reduce the size of the ldf, it will increase it.  That's another reason log backups often have to be done more often than originally expected.

As to corruption, that's an extremely low risk with modern dbms's and business-level drives.  Yes, corruption checks should still be run periodically, but I wouldn't go overboard worrying about corruption.