Clicky

i have run an update statement, and it is 7 hours since it was started (lot of files to update).. but when i look at the log change date in windows, the time is prior to when the update was run..

is this normal that log is not yet updated, before the UPDATE statement had completed?

asked 12/14/2011 06:34

25112's gravatar image

25112 ♦♦


17 Answers:
If you're talking about the SQL log, yes, that is very common.

The dates on SQL data and log files are often not updated until the file is closed, which is usually when the instance is shut down.
link

answered

ScottPletcher's gravatar image

ScottPletcher

i see..

so when the log is written or committed etc, windows does not consider the files as updated? (when i update a word document, windows immediately updates the date part)
link

answered 2011-12-15 at 06:59:22

25112's gravatar image

25112

(when i update a word document, windows immediately updates the date part)

because the file has been closed...

why should the database continually suffer the overhead of opening and closing the log when it is "potentially" dealing with many concurrent transactions....

it is after all in total control of the log and the data within it..
link

answered 2011-12-15 at 15:01:06

Lowfatspread's gravatar image

Lowfatspread

thanks - that is a good explanation..

now, how do we understand this fact..

in some servers, the log file date part is not updated for a while.. but in another, it has a more recent date.. both are operational database, and not closed in the above context.. what else could affect this?
link

answered 2011-12-15 at 15:10:55

25112's gravatar image

25112

That is correct, SQL Server does not update the Transaction Log file.

But to answer your real question, in order to find out how many rows have been updated (or conversely how many are missing) you can do a COUNT(*) query of the table using the NOLOCK hint and the same conditions.
link

answered 2011-12-15 at 15:12:55

acperkins's gravatar image

acperkins

>>what else could affect this? <<
The database has been inadvertently set to Auto-close.
link

answered 2011-12-15 at 15:14:08

acperkins's gravatar image

acperkins

>> the log file date part is not updated for a while.. but in another, it has a more recent date.. both are operational database, and not closed in the above context <<

Verify that the db in q is not in AutoClose mode.

Also, if it is detached (for sure), or set offline (I would think), the date would get updated, even if it was subsequently (re)attached or set online again.


The log file date time is not really relevant to SQL Server processing.  SQL will give you an error if an UPDATE fails, that is what you should go by, not a file date stamp.
link

answered 2011-12-15 at 15:22:25

ScottPletcher's gravatar image

ScottPletcher

>> SQL Server does not update the Transaction Log file. <<

SQL does of course write to the log file, it just does adjust/update the "last modified" date stamp for the file.
link

answered 2011-12-15 at 15:23:18

ScottPletcher's gravatar image

ScottPletcher

Absolutely.  I meant "SQL Server does not update the Transaction Log file time stamp."
link

answered 2011-12-15 at 15:24:46

acperkins's gravatar image

acperkins

how can you check if Auto-close is enabled at the moment?
link

answered 2011-12-15 at 15:26:48

25112's gravatar image

25112

i am following you.. thanks

>>does adjust/update the "last modified" date stamp for the file.

you meant to say it will update the time stamp only in the circumstance of
1)auto close
2)attach detach
only
right?
link

answered 2011-12-15 at 15:27:23

25112's gravatar image

25112

Or set offline.

AFAIK, that's the only things that update it.

I know we've got an instance that's been up since March and the dates on the files are in March, nowhere close to December, and all those dbs are active (tables being updated, inserted, deleted).
link

answered 2011-12-15 at 15:29:12

ScottPletcher's gravatar image

ScottPletcher

>>how can you check if Auto-close is enabled at the moment? <<
SELECT DATABASEPROPERTYEX('YourDatabaseGoesHere', 'IsAutoClose');

link

answered 2011-12-15 at 15:32:07

acperkins's gravatar image

acperkins

>>AFAIK, that's the only things that update it.
1)auto close
2)attach detach
3)Offline

really appreciate it.. could there be a 4th one :) because the 2 databases i compared.. both have auto close OFF.. they are not attached/detached or put offline lately.. but one has recent date on log file and the other one is really old, like the example you gave me..
link

answered 2011-12-15 at 17:52:49

25112's gravatar image

25112

thanks acperkins
link

answered 2011-12-16 at 08:39:17

25112's gravatar image

25112

Yeah, it's certainly possible that some other specific condition in SQL might cause the file date to get updated.

Almost sure it's not just any autogrowth; that would happen enough that many files would be updated.

[And of course I'm not referring to any Express version :-), altho I would think it's probably got the same general approach to that as the other SQLs, since it's all the same core engine.]
link

answered 2011-12-16 at 08:39:27

ScottPletcher's gravatar image

ScottPletcher

that's good enough :)
link

answered 2011-12-16 at 08:52:00

25112's gravatar image

25112

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 12/14/2011 06:34

Seen: 268 times

Last updated: 12/16/2011 02:29