Truncating multiple trans logs

Question

Truncating multiple trans logs

dlapitan

SSC Veteran

Points: 281
More actions
July 27, 2010 at 3:27 pm

#221809

Is there a way in SSIS that I can truncate multiple trans logs at a time? The logs grow quite fast on our dev server and I need a way to automate a daily job to truncate the logs. I understand the command to do it per db but is there a way to basically instruct it clear logs of all dbs on the server?
Databases are constantly being created on the server so I would want to basically step away of having to check and run the command to clear the logs of a particular db. I just want it to run for every db on the server.
Any ideas??

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

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

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

Radical idea: Switch the databases to simple recovery and you won't have to manually truncate the logs. Switch Model to simple recovery and any new databases will also be in Simple recovery.

Please read through this - Managing Transaction Logs[/url]

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

No reason in a dev environment to leave the recovery model in full. Just switch to simple recovery.

And since your logs are growing substantially, you may want to find the causes of so much growth and fix those processes.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

dlapitan SSC Veteran Points: 281 More actions · Answer 3

Thanks for the advice. Switching them to simple mode is perhaps the...simplest! solution.

The only reason I can think of for the rapid growth of the logs is that developers are importing lots of external data, which is all being recorded by the logs.

Thanks again for the help.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Your welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ray Mond SSCertifiable Points: 7159 More actions · Answer 5

The only reason I can think of for the rapid growth of the logs is that developers are importing lots of external data, which is all being recorded by the logs.

In that case, you'll also have to ensure that the import process isn't pulling in huge amounts of data in a single transaction, otherwise the simple recovery model wouldn't help in keeping the transaction log to a manageable size.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.