sql server - How large does a log file get after it updates every database table field? -


my general question is, have sql server 2005 database that's 20g in size , log file empty. if run series of updates touch each table , each row once, log file grow same size database? or depend on other factors?

i'm asking question because of database update i'm performing. have database used proprietary software. updates made every table , every field. software not have verbose output, checkbox "not done"/"done". because there's no descriptive output, i'm trying figure out how tell if it's close end.

so, log file same size database when has been touched? or log file contain different information doesn't depend on database size in way?

as rule of thumb size of log generated update 1.5x size of update. if update 20gb worth of data, generate 30gb worth of log.

but there things consider:

  1. size of log generated not need translates log file size. under simple recovery model log file used can automatically recycled transaction commits (i'm omitting details brevity) 30gb log size can fit in 1gb file, continuously recycling used log. under full , bulk recovery model log gets recycled when database log backup performed. if don't update in 1 transaction, log should recycle , not grow.

  2. some operations can minimally logged. truncate, insert bulk being typical examples. minimal logging takes tiny fraction of normal logging.

right should looking @ log percent used (dbcc sqlperf(logspace)) , monitor if log growth events occurring.


Comments

Popular posts from this blog

apache - Add omitted ? to URLs -

redirect - bbPress Forum - rewrite to wwww.mysite prohibits login -

php - How can I stop spam on my custom forum/blog? -