急:sql2005 日志文件的增长判断条件有哪些?

Question

sql2005 日志文件的增长判断条件有哪些?

数据库引擎如何做的判断,判断的流程是什么?

在日志文件增长的过程中会消耗多少系统资源,会不会对数据库造成死锁的情况?

 

急,坐等恢复,谢谢各位高人.

Answer 1

文件的增长不会造成死锁.

Answer 2

那增长1G日志空间需要多少时间,数据库设置的最大等待时间是多少?

Answer 3

日志空间增长的速度根据数据库的使用情况决定的.

1G 日志如果批量插入的话,可能几分钟就满了.

数据库对日志没有最大等待时间.

更具体可以查看日志设置的机种方式,上网查查.

Answer 4

消息

Autogrow of file 'IMAGE_log' in database 'image' was cancelled by user or timed out after 29328 milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

我这里有一个这样的错误,日志增长的时间29秒多,且未完成,数据库接下来不停的做这个增长,导致我其他服务对数据库的写入直接报错,写入不成功,我现在不知道从什么方向查找其真正导致问题的原因.

Answer 5

You should ask yourself some questions first: what's growth rate? What's recovery mode of the db? How often to backup log if db is not in simple recovery mode? Why log grows that much? You need focus on root cause.

Answer 6

I think your problem maybe because:

1. you insert a big image into table and db keep marking log for it.

2. bucause the image is big so db need growth its log size again and again.

3. bucause db was busing marking log for this image file so you can not insert data into db during this time.

solution:

just waiting for this operation finish.

or you can restart db service.

or some operation to stop db marking log.

Answer 7

Not sure what you are talking about.

1. what do you mean 'db keep marking log for it'?

2. max image size is 2gb, why db need growth its log size again and again?

3. why can't insert data to different table in the db?

Do you know restarting sql server while there's big transaction will mark it for rollback once sql restarted? Means that db will not be available at all during rollback process, make the situation even worse. You can't stop logging in sql by the way, period. 

Answer 8

Example:

1.Image file : 2GB.

2.DB log default size: 20MB. Growth as 20MB/

DB mark this file into log need growth log for 100 time, right?

I think if DB busing to growth log file it must be wast a lot of time so I said it keep loging.

if DB is busing loging this file and some processes want to insert data into database

there must be a queue on writing log, do you think so?

so you can not insert data into database in this time I think .

 

Answer 9

If you insert lot of big images, why set log size to 20mb and growth by 20mb? It's design issue. By the way, sql will not log image file in this case. Willing to participate is fine but should do some homework,  or will misleading people.

Answer 10

the default size of log is not too big, right?

base on this kind of issue, I just think maybe nobody modified default size.

Answer 11

Yes, home work is very important.

I am now searching about log for image type.

But base on this kind of issue, it looks databasae has made log yet.

Answer 12

Could you give some decription about : sql will not log image file in this case.

Answer 13

如果每次增长仅仅是1G的话，这么长时间，存储性能不怎么样，看下有没有IO之类的错误！如果增长过于频繁，建议给这个'IMAGE_log' 文件直接设置一个比较大的初始值，到低峰的时候备份下日志（前提是你恢复模式支持），这样日志文件就会减小！不然日志文件会一直增长下，对存储是一个很大的消耗！