File timestamps not advancing

Our production SQLServer has automated nightly reboots. Last week,
after one of these reboots, SQLServer came up, then immediately
crashed. The log has a bunch of errors saying things like "Problem
creating symptom dump file due to internal exception" and "Invalid
access to memory location". The SQLServer service restarted itself
within seconds, and SQLServer continued without error.
Since then, when I look at the data and log files in Windows
Explorer or from a command prompt, the file modification timestamps
and the byte count are "stuck" at the time of the crash or restart
(they were seconds apart, so I can't tell which). There are a
couple dozen databases on this server, and they're all stuck this
way. In some cases, it's only the data file; in others, it's only
the log file; in still others, both files are stuck. Of the files
that aren't stuck at the time of the crash, they've advanced once
or twice, but none are advancing when the server reboots.
That's the part that bothers me. The server has been rebooted each
night since then, and I would expect the files would be touched at
least as often as each reboot. The only one that seems to be
behaving is tempdb, which is of course recreated after each
restart.
There's a production app running against these databases, and I'm
not aware of any user complaints or problems with the app. I've
also found that if I either detach/attach or offline/online a
database, the log and data file timestamps advance to that point.
This doesn't fix them, though: the timestamps get "stuck" at that
new point. I think SQLServer is still writing to and reading from
these files, but maybe it's not closing them properly during the
nightly reboot?
I'm concerned that this is a predictor of a worse failure, maybe
something to come or something that's happened that I haven't
noticed yet. Any ideas?> That's the part that bothers me. The server has been rebooted each
> night since then, and I would expect the files would be touched at
> least as often as each reboot.
No, that's not true. The stamps on the files will only change when the file
actually changes size (e.g. an autogrow event, or a manual DBCC
SHRINKDATABASE or DBCC SHRINKFILE, or a detach/attach).
Why on earth are you rebooting the server every night? I think you might
want to deal with those problems instead of worrying about the time and size
properties of the MDF and LDF files.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||> No, that's not true. The stamps on the files will only change
> when the file actually changes size (e.g. an autogrow event, or
> a manual DBCC SHRINKDATABASE or DBCC SHRINKFILE, or a
> detach/attach).
> Why on earth are you rebooting the server every night? I think
> you might want to deal with those problems instead of worrying
> about the time and size properties of the MDF and LDF files.
>
Thanks for the response, Aaron. Actually, it is true. SQLServer
must flush to the files and release filehandles on a normal
shutdown of the services. If you have a test server, restart the
SQLServer service and watch the timestamps on your data and log
files.
Why I'm rebooting the server each night doesn't enter into this.
I'd like to focus on the problem at hand, which started after the
SQLServer crash. Any other ideas? All thoughts are appreciated!|||> Thanks for the response, Aaron. Actually, it is true. SQLServer
> must flush to the files and release filehandles on a normal
> shutdown of the services.
Whoops, you're right. I think I was cross-wired there for a few minutes.|||> Whoops, you're right. I think I was cross-wired there for a few
> minutes.
>
No sweat. Interesting what it suggests, though, isn't it? SQLServer
may not be properly closing the files when the reboot happens. It
may be something with the way the service is stopping. Maybe
SQLServer is somehow aborting, instead of stopping gracefully.
Nothing shows in the logs, though.
Anyone have other thoughts?|||> Anyone have other thoughts?
Reboot only after you have successfully brought SQL Server down gracefully?
You can stop SQL Server programmatically using NET STOP ...