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

Newbie help: sql string conversion

Hi,
I am trying to avoid a horrendous amount of coding and see if i can get away
with a complex sql statement.

I have data values (measurements) which I have stored in the database in the
form of STRING so that i can keep the original format.

They look like this:

000078 -> 7.8 degrees
-99999M -> Missing data
000345 -> 34.5 degrees
-00993 -> -99.3 degrees
000011 -> 1.1 degrees

you get the idea.
They represent numbers (positive or negative) the last place is the decimal
, they all have 6 characters except the missing data which is -99999M (7
places).

How would I construct an SQL querry to be able to allow the user to retrieve
temperature between e.g. > -2.5 and <12.4 ?

TIA
-steveWhy is the data stored in this format? If these are numeric measurements you
will be much better off storing them with a numeric datatype. Storing
numbers as strings will just make your queries difficult and slow and also
make it hard to maintain any data integrity. Fix the design and convert the
data to numeric form is my advice.

If you've no other choice you could try something like this:

SELECT col
FROM Measurements
WHERE CAST(LEFT(col,6) AS INTEGER) > -2.5
AND CAST(LEFT(col,6) AS INTEGER) < 12.4

--
David Portas
SQL Server MVP
--|||steve,

SELECT * FROM Table1
WHERE CAST(CASE RIGHT(Measurement, 1) WHEN 'M' THEN NULL ELSE Measurement
END AS decimal) * .1
BETWEEN -2.5 AND 12.4

-Andy

"steve" <noemail.@.try.com> wrote in message
news:S5wgd.49358$5t4.774343@.wagner.videotron.net.. .
> Hi,
> I am trying to avoid a horrendous amount of coding and see if i can get
> away with a complex sql statement.
> I have data values (measurements) which I have stored in the database in
> the form of STRING so that i can keep the original format.
> They look like this:
> 000078 -> 7.8 degrees
> -99999M -> Missing data
> 000345 -> 34.5 degrees
> -00993 -> -99.3 degrees
> 000011 -> 1.1 degrees
> you get the idea.
> They represent numbers (positive or negative) the last place is the
> decimal , they all have 6 characters except the missing data which
> is -99999M (7 places).
> How would I construct an SQL querry to be able to allow the user to
> retrieve temperature between e.g. > -2.5 and <12.4 ?
> TIA
> -steve
>|||hmm i see your point and thanx for your answer.
As I said I'd rather keep them in this format for now.
However!
I was thinking is there a fast way that through an sql querry that I can
duplicate a table with a different name of course that will have the same
data but on the "proper" format?
give me a couple of keywords and I'll look google them if you can

Thanx again!

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le
message de news: avqdndYSfcazCB_cRVn-pA@.giganews.com...
> Why is the data stored in this format? If these are numeric measurements
> you will be much better off storing them with a numeric datatype. Storing
> numbers as strings will just make your queries difficult and slow and also
> make it hard to maintain any data integrity. Fix the design and convert
> the data to numeric form is my advice.
> If you've no other choice you could try something like this:
> SELECT col
> FROM Measurements
> WHERE CAST(LEFT(col,6) AS INTEGER) > -2.5
> AND CAST(LEFT(col,6) AS INTEGER) < 12.4
> --
> David Portas
> SQL Server MVP
> --|||Create a new table, then use the query I gave you to INSERT into it:

INSERT INTO NewTable (...)
SELECT ...
FROM OldTable

But if the data is changing, maintaining two copies of it is hard work and
unnecessary. The usual practice is to validate and transform data once and
then maintain it in a consistent, strongly-typed relational format in the
database. If you validate the data properly once then you won't need the
original format again. If you don't then you pay the price every time you
query the table.

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Why is the data stored in this format? If these are numeric measurements
> you will be much better off storing them with a numeric datatype.
> Storing numbers as strings will just make your queries difficult and
> slow and also make it hard to maintain any data integrity. Fix the
> design and convert the data to numeric form is my advice.

And store the missing values as NULL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I think i *will* loose my mind!
Thanks both of you for your help.

You see, the problem is that i can have a few flags at the end of the string
which mean somehing. e.g. M means missing, T means Trace, E means estimated,
etc.Information that i should have. If i split each column into two ,
well...possible but a lot of work and too much overhead since most
measurements are "clean".

That's the reason i kept the data in their original format.

Now if i dont convert to strings how the heck am i going to create the sql
string to send to the database with things like:
temperature between so and so, humidity bigger than 50, blah blah blah...
The string is created by an interface in VB where the user scrolls down
various controls and selects things. But how would you enter the bounds of
temperature in a textbox since its not stored as an integer AND it might
have a stupid letter at the end of its value!!!

I could do the coding after the results are returned.
I think My Biggest problem is string comparisons!!! If the user wants a
temperature between -7 and +15 how well and reliably can I create code to
compare "-00007" and "000015".

Just some thoughts from my brainstorming...

"Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> a crit dans le
message de news: CIwgd.1071$wN4.303@.newssvr16.news.prodigy.com...
> steve,
> SELECT * FROM Table1
> WHERE CAST(CASE RIGHT(Measurement, 1) WHEN 'M' THEN NULL ELSE Measurement
> END AS decimal) * .1
> BETWEEN -2.5 AND 12.4
> -Andy
> "steve" <noemail.@.try.com> wrote in message
> news:S5wgd.49358$5t4.774343@.wagner.videotron.net.. .
>> Hi,
>> I am trying to avoid a horrendous amount of coding and see if i can get
>> away with a complex sql statement.
>>
>> I have data values (measurements) which I have stored in the database in
>> the form of STRING so that i can keep the original format.
>>
>> They look like this:
>>
>> 000078 -> 7.8 degrees
>> -99999M -> Missing data
>> 000345 -> 34.5 degrees
>> -00993 -> -99.3 degrees
>> 000011 -> 1.1 degrees
>>
>> you get the idea.
>> They represent numbers (positive or negative) the last place is the
>> decimal , they all have 6 characters except the missing data which
>> is -99999M (7 places).
>>
>> How would I construct an SQL querry to be able to allow the user to
>> retrieve temperature between e.g. > -2.5 and <12.4 ?
>>
>> TIA
>> -steve
>>
>>
>>
>>|||steve wrote:
> I think i *will* loose my mind!
> Thanks both of you for your help.
> You see, the problem is that i can have a few flags at the end of the string
> which mean somehing. e.g. M means missing, T means Trace, E means estimated,
> etc.Information that i should have. If i split each column into two ,
> well...possible but a lot of work and too much overhead since most
> measurements are "clean".

That's not a "problem"! Basic database design says you shouldn't keep
multiple pieces of information in the same column. Doing it right
wouldn't be that difficult code wise. You simply have a column with your
valid codes in a check constraint and have the default be the "clean"
code if that is the most common entry.

> That's the reason i kept the data in their original format.
> Now if i dont convert to strings how the heck am i going to create the sql
> string to send to the database with things like:
> temperature between so and so, humidity bigger than 50, blah blah blah...
> The string is created by an interface in VB where the user scrolls down
> various controls and selects things. But how would you enter the bounds of
> temperature in a textbox since its not stored as an integer AND it might
> have a stupid letter at the end of its value!!!

You can do all that manipulation on the front in via code.

> I could do the coding after the results are returned.
> I think My Biggest problem is string comparisons!!! If the user wants a
> temperature between -7 and +15 how well and reliably can I create code to
> compare "-00007" and "000015".
> Just some thoughts from my brainstorming...

Honestly, your brainstorming is confusing the heck out of me. Or maybe
you don't understand the numeric data type. A query of a numeric column
for all values between -7 and +15 would be very simple: WHERE Temp
BETWEEN -7 and 15. Numeric data is not stored with leading 0's.

Zach

>
> "Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> a crit dans le
> message de news: CIwgd.1071$wN4.303@.newssvr16.news.prodigy.com...
>>steve,
>>
>>SELECT * FROM Table1
>>WHERE CAST(CASE RIGHT(Measurement, 1) WHEN 'M' THEN NULL ELSE Measurement
>>END AS decimal) * .1
>> BETWEEN -2.5 AND 12.4
>>
>>-Andy
>>
>>"steve" <noemail.@.try.com> wrote in message
>>news:S5wgd.49358$5t4.774343@.wagner.videotron.net.. .
>>
>>>Hi,
>>>I am trying to avoid a horrendous amount of coding and see if i can get
>>>away with a complex sql statement.
>>>
>>>I have data values (measurements) which I have stored in the database in
>>>the form of STRING so that i can keep the original format.
>>>
>>>They look like this:
>>>
>>>000078 -> 7.8 degrees
>>>-99999M -> Missing data
>>>000345 -> 34.5 degrees
>>>-00993 -> -99.3 degrees
>>>000011 -> 1.1 degrees
>>>
>>>you get the idea.
>>>They represent numbers (positive or negative) the last place is the
>>>decimal , they all have 6 characters except the missing data which
>>>is -99999M (7 places).
>>>
>>>How would I construct an SQL querry to be able to allow the user to
>>>retrieve temperature between e.g. > -2.5 and <12.4 ?
>>>
>>>TIA
>>>-steve
>>>
>>>
>>>
>>>
>>
>>
>|||> You see, the problem is that i can have a few flags at the end of the
> string which mean somehing. e.g. M means missing, T means Trace, E means
> estimated

Then you have a non-atomic column, which is a violation of the most
fundamental relational design principles. This information belongs in a
separate column.

--
David Portas
SQL Server MVP
--|||> But how would you enter the bounds of temperature in a textbox since its
> not stored as an integer AND it might have a stupid letter at the end of
> its value!!!

> I think My Biggest problem is string comparisons!!! If the user wants a
> temperature between -7 and +15 how well and reliably can I create code to
> compare "-00007" and "000015".

Yep, it's lousy... So why waste time on it when you could just redesign the
table properly :-)

--
David Portas
SQL Server MVP
--