SQL Server Performance Forum – Threads Archive

Question about Transaction Logs

I have a data warehouse that holds data that is moved from DB2 on the mainframe to SQL Server nightly. This data is read only and thus the only "transactions" that would be in the logs would be the inserts nightly. Since the tables are truncated and moved fully, I can’t think of any reason that the transaction logs would need to be allowed to grow, for one of our big DB’s – 97GB. If you guys concur that in my situation I could stand to not have the transaction logs, how can I limit their size but still have everything function normally? Thanks for help and input in advance.

What database recovery model do you have?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

On this instance where the data is refreshed nightly, and it is read only, we take no backups because our backups are the databases on DB2 which is backed up nightly.

You cannot remove the transaction log from a database, rather you can define the SIMPLE recovery model (which coudl be one of answer from Luis). During the import you can keep the recovery model to SIMPLE and shrink the Tlog after the insert using DBCC SHRINKFILE statement and then run DBREINDEX in order to get optimum performance.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>