Avatar of cnxmax
cnxmax
 asked on

Single Stored Procedure to Remove Log Files

I have a database that gets a lot of activity, but it's not really data that is critical for long-term archiving. I need to know if I can create a stored procedure that I can run a few times a day to basically delete all of the log files.

From what I understand, log files need to be backed-up before they are removed. I need some kind of procedure to do the backup then remove the log files (or shrink them). If it's possible to delete the logs without backup I would prefer that.

Is something like this possible?
Email ProtocolsMicrosoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
cm0605

8/22/2022 - Mon
cnxmax

ASKER
I think I might have figured it out. If I'm doing regular daily full backups, will this code work?
dump transaction somedatabase with truncate_only
checkpoint
 
Use [somedatabase]
Go
DBCC SHRINKFILE (N'somedatabase_log' , 0, TRUNCATEONLY)
GO

Open in new window

ASKER CERTIFIED SOLUTION
ola

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
laneduncan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
chapmandew

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ola

I think that you have to consider how critical your data is and what your requirements on recoverability are.

I think that you can do one of two things.
1. Simple recovery model and no transaction log backups. Here you can only restore to the latest full or differential backup.
2. Full recovery model and regularly transaction log backups. Here you could do a point in time restore.

I would not recommend you do do regularly shrinking. You could do a one-time shrink if your transaction log for some reason has grown very large.

Ola Hallengren
http://ola.hallengren.com
SOLUTION
cm0605

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy