Sql ANSI Padding: according

According to Microsoft, sysfiles.status:

0x80 = File has been written to since last backup.

Unfortunately, this query is not returning what I expect. That is, if I have inserted/updated/deleted any records, I belive that some underlying file must be written to.

So, I tried:

Code Snippet

select * from sysfiles where status & 0x80 <> 0

I updated several fields in my database, doubled and tripled the size etc. I can't get the above query to return any results.

Does anyone have any experience with this particular flag? I was hoping to check the sysfiles and know if a backup was needed or not.

Thanks mucho.

Interesting. I just tried this with 7.0, 2000, and 2005, and that bit is never set. And 6.5 uses sysdevices rather than sysfiles. I wonder if somewhere in the development cycle they decided not to implement that, and it was never taken out of the documentation drafts. Either that or it's a very long standing, little-known bug. ;-)

There might be some ways to take advantage of the differential changed map to determine if a database has changed since the last backup, but the only way I know to read it isn't well suited for inclusion in a batch job.

Code Snippet

DBCC TRACEON(3604)
DBCC PAGE(databasename, 1, 6, 3)

Granted, a database larger than about 4 GB is probably going to have more than one dcm page, and I'm not entirely sure how to determine where subsequent pages are located.

I have a few questions regarding the ANSI_PADDING option. I see, according
to MSDN (http://msdn2.microsoft.com/en-us/library/ms187403.aspx), that this
option is deprecated:
> This feature will be removed in a future version of Microsoft SQL
> Server. Avoid using this feature in new development work, and
> plan to modify applications that currently use this feature.
My questions are:
1) What will this setting be fixed to once it is no longer supported?
According to MSDN, their recommendation is that we always have it set to ON,
and it states the default value is also ON. So I would assume that it will
be fixed to ON once it is no longer supported, but I don't see this
explicitly stated anywhere.
2) What will happen to existing databases with columns defined with this
option turned on? Will they be converted such that the columns will have
ANSI_PADDING ON, or will the OFF setting be preserved? I would assume that
they will be converted, but again, this is not explicitly stated anywhere.
If this information is listed somewhere, please direct me to the reference.
Thanks in advance for your time.
JeradJerad,
1)
I suggest you do a feedback on that BOL page. They should document what the setting will be when it
is deprecated.
I can bet a large sum that it will be ON. The Sybase heritage had the OFF behavior, where ANSI SQL
has the ON behavior. So, in some version (possibly 6.0) we could use this setting to change the
behavior. Over time, the ANSI (ON) behavior has become more and more dominating (tools defaulting to
this etc). Considering that some features require ON behavior (indexed views etc), and ANSI SQL is
ON, this will be ON when deprecated. But of course, the text should tell us that, so I suggest you
BOL feedback.
2) I have a feeling that no-one know at this moment what happens to existing databases when the
setting is deprecated. This is probably one of the things that MS need to work out with the
customers during beta. So, this I can understand why they can't comment on at this moment. But I
suggest you mention this in your BOL feedback. You never know, they might already know what will
happen...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jerad Rose" <no@.spam.com> wrote in message news:uRheZ08aHHA.348@.TK2MSFTNGP02.phx.gbl...
>I have a few questions regarding the ANSI_PADDING option. I see, according to MSDN
>(http://msdn2.microsoft.com/en-us/library/ms187403.aspx), that this option is deprecated:
>> This feature will be removed in a future version of Microsoft SQL
>> Server. Avoid using this feature in new development work, and
>> plan to modify applications that currently use this feature.
> My questions are:
> 1) What will this setting be fixed to once it is no longer supported? According to MSDN, their
> recommendation is that we always have it set to ON, and it states the default value is also ON.
> So I would assume that it will be fixed to ON once it is no longer supported, but I don't see this
> explicitly stated anywhere.
> 2) What will happen to existing databases with columns defined with this option turned on? Will
> they be converted such that the columns will have ANSI_PADDING ON, or will the OFF setting be
> preserved? I would assume that they will be converted, but again, this is not explicitly stated
> anywhere.
> If this information is listed somewhere, please direct me to the reference.
> Thanks in advance for your time.
> Jerad
>|||Thanks for your response, Tibor. I will follow your advise, and submit
feedback via BOL.
Thanks again.
Jerad
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$4zIM%23aHHA.2076@.TK2MSFTNGP04.phx.gbl...
> Jerad,
> 1)
> I suggest you do a feedback on that BOL page. They should document what
the setting will be when it
> is deprecated.
> I can bet a large sum that it will be ON. The Sybase heritage had the OFF
behavior, where ANSI SQL
> has the ON behavior. So, in some version (possibly 6.0) we could use this
setting to change the
> behavior. Over time, the ANSI (ON) behavior has become more and more
dominating (tools defaulting to
> this etc). Considering that some features require ON behavior (indexed
views etc), and ANSI SQL is
> ON, this will be ON when deprecated. But of course, the text should tell
us that, so I suggest you
> BOL feedback.
>
> 2) I have a feeling that no-one know at this moment what happens to
existing databases when the
> setting is deprecated. This is probably one of the things that MS need to
work out with the
> customers during beta. So, this I can understand why they can't comment on
at this moment. But I
> suggest you mention this in your BOL feedback. You never know, they might
already know what will
> happen...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jerad Rose" <no@.spam.com> wrote in message
news:uRheZ08aHHA.348@.TK2MSFTNGP02.phx.gbl...
> >I have a few questions regarding the ANSI_PADDING option. I see,
according to MSDN
> >(http://msdn2.microsoft.com/en-us/library/ms187403.aspx), that this
option is deprecated:
> >
> >> This feature will be removed in a future version of Microsoft SQL
> >> Server. Avoid using this feature in new development work, and
> >> plan to modify applications that currently use this feature.
> >
> > My questions are:
> >
> > 1) What will this setting be fixed to once it is no longer supported?
According to MSDN, their
> > recommendation is that we always have it set to ON, and it states the
default value is also ON.
> > So I would assume that it will be fixed to ON once it is no longer
supported, but I don't see this
> > explicitly stated anywhere.
> >
> > 2) What will happen to existing databases with columns defined with this
option turned on? Will
> > they be converted such that the columns will have ANSI_PADDING ON, or
will the OFF setting be
> > preserved? I would assume that they will be converted, but again, this
is not explicitly stated
> > anywhere.
> >
> > If this information is listed somewhere, please direct me to the
reference.
> >
> > Thanks in advance for your time.
> >
> > Jerad
> >
>|||> I can bet a large sum that it will be ON.
Tibor, I hope someone took you up on your bet. In fact, the setting will
always be ON. The topic is unclear (Thanks, Jerad, for submitting your
feedback). What's actually being deprecated is the ability to set the option
to OFF.
> 2) I have a feeling that no-one know at this moment what happens to
> existing databases when the setting is deprecated.
Again, you're correct. The migration/upgrade story for this is likely not
firmly in place given that doesn't go into effect for another two releases.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$4zIM%23aHHA.2076@.TK2MSFTNGP04.phx.gbl...
> Jerad,
> 1)
> I suggest you do a feedback on that BOL page. They should document what
> the setting will be when it is deprecated.
> I can bet a large sum that it will be ON. The Sybase heritage had the OFF
> behavior, where ANSI SQL has the ON behavior. So, in some version
> (possibly 6.0) we could use this setting to change the behavior. Over
> time, the ANSI (ON) behavior has become more and more dominating (tools
> defaulting to this etc). Considering that some features require ON
> behavior (indexed views etc), and ANSI SQL is ON, this will be ON when
> deprecated. But of course, the text should tell us that, so I suggest you
> BOL feedback.
>
> 2) I have a feeling that no-one know at this moment what happens to
> existing databases when the setting is deprecated. This is probably one of
> the things that MS need to work out with the customers during beta. So,
> this I can understand why they can't comment on at this moment. But I
> suggest you mention this in your BOL feedback. You never know, they might
> already know what will happen...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jerad Rose" <no@.spam.com> wrote in message
> news:uRheZ08aHHA.348@.TK2MSFTNGP02.phx.gbl...
>>I have a few questions regarding the ANSI_PADDING option. I see,
>>according to MSDN
>>(http://msdn2.microsoft.com/en-us/library/ms187403.aspx), that this option
>>is deprecated:
>> This feature will be removed in a future version of Microsoft SQL
>> Server. Avoid using this feature in new development work, and
>> plan to modify applications that currently use this feature.
>> My questions are:
>> 1) What will this setting be fixed to once it is no longer supported?
>> According to MSDN, their recommendation is that we always have it set to
>> ON, and it states the default value is also ON. So I would assume that it
>> will be fixed to ON once it is no longer supported, but I don't see this
>> explicitly stated anywhere.
>> 2) What will happen to existing databases with columns defined with this
>> option turned on? Will they be converted such that the columns will have
>> ANSI_PADDING ON, or will the OFF setting be preserved? I would assume
>> that they will be converted, but again, this is not explicitly stated
>> anywhere.
>> If this information is listed somewhere, please direct me to the
>> reference.
>> Thanks in advance for your time.
>> Jerad
>|||> Tibor, I hope someone took you up on your bet.
I'm afraid no-one did... :-)
Thanks for jumping in and clarify, Gail.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:OK7GGNBbHHA.3408@.TK2MSFTNGP03.phx.gbl...
>> I can bet a large sum that it will be ON.
> Tibor, I hope someone took you up on your bet. In fact, the setting will always be ON. The topic
> is unclear (Thanks, Jerad, for submitting your feedback). What's actually being deprecated is the
> ability to set the option to OFF.
>> 2) I have a feeling that no-one know at this moment what happens to existing databases when the
>> setting is deprecated.
> Again, you're correct. The migration/upgrade story for this is likely not firmly in place given
> that doesn't go into effect for another two releases.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e$4zIM%23aHHA.2076@.TK2MSFTNGP04.phx.gbl...
>> Jerad,
>> 1)
>> I suggest you do a feedback on that BOL page. They should document what the setting will be when
>> it is deprecated.
>> I can bet a large sum that it will be ON. The Sybase heritage had the OFF behavior, where ANSI
>> SQL has the ON behavior. So, in some version (possibly 6.0) we could use this setting to change
>> the behavior. Over time, the ANSI (ON) behavior has become more and more dominating (tools
>> defaulting to this etc). Considering that some features require ON behavior (indexed views etc),
>> and ANSI SQL is ON, this will be ON when deprecated. But of course, the text should tell us that,
>> so I suggest you BOL feedback.
>>
>> 2) I have a feeling that no-one know at this moment what happens to existing databases when the
>> setting is deprecated. This is probably one of the things that MS need to work out with the
>> customers during beta. So, this I can understand why they can't comment on at this moment. But I
>> suggest you mention this in your BOL feedback. You never know, they might already know what will
>> happen...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jerad Rose" <no@.spam.com> wrote in message news:uRheZ08aHHA.348@.TK2MSFTNGP02.phx.gbl...
>>I have a few questions regarding the ANSI_PADDING option. I see, according to MSDN
>>(http://msdn2.microsoft.com/en-us/library/ms187403.aspx), that this option is deprecated:
>> This feature will be removed in a future version of Microsoft SQL
>> Server. Avoid using this feature in new development work, and
>> plan to modify applications that currently use this feature.
>> My questions are:
>> 1) What will this setting be fixed to once it is no longer supported? According to MSDN, their
>> recommendation is that we always have it set to ON, and it states the default value is also ON.
>> So I would assume that it will be fixed to ON once it is no longer supported, but I don't see
>> this explicitly stated anywhere.
>> 2) What will happen to existing databases with columns defined with this option turned on? Will
>> they be converted such that the columns will have ANSI_PADDING ON, or will the OFF setting be
>> preserved? I would assume that they will be converted, but again, this is not explicitly stated
>> anywhere.
>> If this information is listed somewhere, please direct me to the reference.
>> Thanks in advance for your time.
>> Jerad
>>
>

2012年3月25日星期日

Anyone have experience with SYSFILES?

2012年3月11日星期日

any suggestions for this report design

2012年2月11日星期六

Antivirus on a 2003 Cluster

ANSI_PADDING Deprecated

Sql ANSI Padding

Blog Archive

About Me