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?

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

laneduncan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial