May 30, 2008 at 1:37 am
I have a t-sql script the runs successive "INSERT INTO's". Even though the database is in Simple Recovery mode the trans log grows considerbaly after each INSERT INTO. (The tables involved have millions of records.)
I'd like to trigger "something" between successive INSERT\INTO's that would cause the trans log to shrink (physically on the disk) before running the next INSERT\INTO. .Any recommendations appreciated.
TIA,
barkingdog
May 30, 2008 at 2:48 am
You don't want to physically shrink the transaction log. It'll just have to grow again.
Since you're in simple recovery, run a checkpoint at regular intervals between your inserts. In simple, checkpoint truncates the transaction log, discarding inactive log records and makes the space in the log file available for reuse.
Size the log file based on the space needed for the biggest of the select ... into statements
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply