Newbie question on physical file last mod date vs. virtual DB/Log

I think I understand the basic features of log files and how to
backup/truncate them to avoid oversized files. Where I need
help is understanding why the O/S rarely puts a new datetime
on the physical files. It appears to only update the log file
time when the log file grows. The DB file may go a month or more
without updating the timestamp. Our backups of the DB grow in
size and appear to be OK. CHECKPOINT commands don't force
the physical disk to update. Is there any other way to do it
(short of drastic measures like detaching the DB)?
Here's my concern: Suppose the server crashes due to a power
failure/UPS failure/whatever. When it restarts it will look at the
log to recover the DB. The physical DB file appears to be a month old
and the log datetime appears to be a day or two old.
If I truncated my log at any time in the last month then it seems like
it will not be able to recover correctly.
Is this really a problem or is it OK?
Environment: SQL Server 2000 on Win2K ServerWhy and when Windows updates the datetime for the file, I don't know. Perhaps somebody in the
windows forum can answer that. However:
> Here's my concern: Suppose the server crashes due to a power
> failure/UPS failure/whatever. When it restarts it will look at the
> log to recover the DB.
Correct. SQL Server know where to find the ldf file, it is stored both in the mdf file as well as in
the master database.
> The physical DB file appears to be a month old
Doesn't matter to SQL Server.
> and the log datetime appears to be a day or two old.
Can you explain what you mean by "log datetime"?
> If I truncated my log at any time in the last month then it seems like
> it will not be able to recover correctly.
What do you mean by "truncated"? Something like BACKUP LOG ... WITH TRUNCATE_ONLY? As long as you
haven't deleted the log file and replaced with an older version, you are fine. SQL server will not
remove log records needed to do recovery of the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Don Anthony" <DonAnthony@.discussions.microsoft.com> wrote in message
news:0817C2B3-1F18-4B50-A4C8-6A54D52D0D87@.microsoft.com...
>I think I understand the basic features of log files and how to
> backup/truncate them to avoid oversized files. Where I need
> help is understanding why the O/S rarely puts a new datetime
> on the physical files. It appears to only update the log file
> time when the log file grows. The DB file may go a month or more
> without updating the timestamp. Our backups of the DB grow in
> size and appear to be OK. CHECKPOINT commands don't force
> the physical disk to update. Is there any other way to do it
> (short of drastic measures like detaching the DB)?
> Here's my concern: Suppose the server crashes due to a power
> failure/UPS failure/whatever. When it restarts it will look at the
> log to recover the DB. The physical DB file appears to be a month old
> and the log datetime appears to be a day or two old.
> If I truncated my log at any time in the last month then it seems like
> it will not be able to recover correctly.
> Is this really a problem or is it OK?
> Environment: SQL Server 2000 on Win2K Server
>|||Re: Can you explain what you mean by "log datetime"?
Answer: The last modification time on the log file (i.e, both
the DB file and the Log file appear to be "old" on the disk).
Re: What do you mean by "truncated"?
Something like BACKUP LOG ... WITH TRUNCATE_ONLY?
Answer: Yes.
If the log file had a recent last modification time (the Windows File)
then it all makes sense. It seems like "magic" because it "looks" like
SQL Server does recovery with an very old DB file and a not-so-recent
Log file. Is it possible the disk files are actually updated by SQL
Server without changing the disk file last modification time?
Thank you for your help.|||> Is it possible the disk files are actually updated by SQL
> Server without changing the disk file last modification time?
Yes, this is what is happening. Again, check with the Windows people under what conditions the NTFS
file timestamps are changed. I understand that you find this ... interesting, but just don't worry
about the file timestamps.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Don Anthony" <DonAnthony@.discussions.microsoft.com> wrote in message
news:898EB1A4-A02E-456F-B82A-4391608EAD6A@.microsoft.com...
> Re: Can you explain what you mean by "log datetime"?
> Answer: The last modification time on the log file (i.e, both
> the DB file and the Log file appear to be "old" on the disk).
> Re: What do you mean by "truncated"?
> Something like BACKUP LOG ... WITH TRUNCATE_ONLY?
> Answer: Yes.
> If the log file had a recent last modification time (the Windows File)
> then it all makes sense. It seems like "magic" because it "looks" like
> SQL Server does recovery with an very old DB file and a not-so-recent
> Log file. Is it possible the disk files are actually updated by SQL
> Server without changing the disk file last modification time?
> Thank you for your help.
>|||Don Anthony wrote:
> Re: Can you explain what you mean by "log datetime"?
> Answer: The last modification time on the log file (i.e, both
> the DB file and the Log file appear to be "old" on the disk).
> Re: What do you mean by "truncated"?
> Something like BACKUP LOG ... WITH TRUNCATE_ONLY?
> Answer: Yes.
> If the log file had a recent last modification time (the Windows File)
> then it all makes sense. It seems like "magic" because it "looks" like
> SQL Server does recovery with an very old DB file and a not-so-recent
> Log file. Is it possible the disk files are actually updated by SQL
> Server without changing the disk file last modification time?
> Thank you for your help.
>
I'm not sure, but I'd think that the file timestamp for the logfile and
database file are only updated when the files are actually changed. That
could e.g. be when the file grows or shrinks.
I've just looked at one of our databases, and here the date for the
database file is 26. febr. 2006 and for the logfile it's 3. jan. 2006.
Like Tibors says, SQL server isn't using these file timestamps for
anything, so help yourself and don't worry about them...:-).
Regards
Steen