Truncate all Transaction Logs on the server

Question

Post reply

Truncate all Transaction Logs on the server

rem1th0mas

SSC Enthusiast

Points: 141
More actions
January 9, 2008 at 3:59 am

#377900

Hi,
Here's my problem. Working in a web company i've got a couple of sql servers to look after.
Being a web developper and not a DBA my knowledges are limited. (That's to excuse the sillyness of the question.)
Every first monday of the month for each single database I run the command
DBCC SHRINKFILE(MyDB_log)
BACKUP LOG MyDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(MyDB_log)
to free some space on the servers. That's as boring as it gets and I was wondering if nobody had written a clever script which will loop through each db on the server and run such script.
(When building new db, we include this script in a daily maintenance sproc)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

Chandra Sekhara Vyas Dhara

SSChampion

Points: 10207

January 9, 2008 at 4:14 am

#766086

Create a maintance plan and put it as job Schedule

rem1th0mas SSC Enthusiast Points: 141 More actions · Answer 2

Thanks Ted,

A maintenance plan is a good way to deal with the issue on new database.

I've got up to 120 database on a server. Creating or modifying maintenance plan for each of them will take me hours.

What i'm loooking for is a script which will loop through each db and truncate its log.

Any idea?

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

To get the file names for all 120 log files, you'll need to run some type of query to pull them out of sys.database_files. If you just wanted to truncate all the logs you could do this:

sp_msforeachdb 'backup log ? with truncate_only'

Other than that, first blush, create a cursor that hits all the databases, queries against sys.database_files for the files and then builds your query as an ad hoc. It'll be a bit weird since you have to get the database file names from each db.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

rem1th0mas SSC Enthusiast Points: 141 More actions · Answer 4

exactly what I was lookign for for the past 2 yrs.

You've made my day... and my year 😉

Thanks for all your help

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Everyone else covered the how, so I'll cover they why.

Be aware that by doing this you are breaking the log chain and preventing yourself from being able to do restores to point of failure (if failure was after the log truncate and before a full/diff DB backup

If you need point in time restores, rather schedule regular log backups. That will ensure that the log doesn't get too large in the first place

If you don't need point in time restores, then set the database into simple recovery mode so that the log auto truncates

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass