RAID

I am trying to understand file or filegroup placement and RAID.
We have RAID 5 with 5 disks and one primary file group, no secondary.
Trying to keep this thread as simple as possible, just trying to focus on an
understanding of RAID, I sometimes read in publications that it can be
beneficial to place "each file on its own separate physical disk or disk
array".
I want to make sure I understand this statement in light of RAID 5. Does this
mean within RAID 5 I can use one of those 5 disks to place my Transaction Log,
and another one of those 5 disks to place my Primary Data file?
Message posted via http://www.droptable.com
No. RAID does not give you access to the individual disks. You access them
as a group. If you can rebuild your RAID array, consider breaking it into a
3-disk RAID5 and a 2-disk RAID1. On the RAID5, put your data. On the
RAID1, put your logs.
If you can get one more disk, then instead of a 3-disk RAID5, go with a
4-disk RAID0+1.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5d979be2bd3d1@.uwe...
I am trying to understand file or filegroup placement and RAID.
We have RAID 5 with 5 disks and one primary file group, no secondary.
Trying to keep this thread as simple as possible, just trying to focus on an
understanding of RAID, I sometimes read in publications that it can be
beneficial to place "each file on its own separate physical disk or disk
array".
I want to make sure I understand this statement in light of RAID 5. Does
this
mean within RAID 5 I can use one of those 5 disks to place my Transaction
Log,
and another one of those 5 disks to place my Primary Data file?
Message posted via http://www.droptable.com
|||So, to confirm my understanding, with my current setup of a 5 disk RAID 5,
even if it is logically partitioned, and the log and data files are on
different partitions on the RAID 5, they [the log and data files] are still
competing against each other. Is that correct?
Tom Moreau wrote:
>No. RAID does not give you access to the individual disks. You access them
>as a group. If you can rebuild your RAID array, consider breaking it into a
>3-disk RAID5 and a 2-disk RAID1. On the RAID5, put your data. On the
>RAID1, put your logs.
>If you can get one more disk, then instead of a 3-disk RAID5, go with a
>4-disk RAID0+1.
>I am trying to understand file or filegroup placement and RAID.
>We have RAID 5 with 5 disks and one primary file group, no secondary.
>Trying to keep this thread as simple as possible, just trying to focus on an
>understanding of RAID, I sometimes read in publications that it can be
>beneficial to place "each file on its own separate physical disk or disk
>array".
>I want to make sure I understand this statement in light of RAID 5. Does
>this
>mean within RAID 5 I can use one of those 5 disks to place my Transaction
>Log,
>and another one of those 5 disks to place my Primary Data file?
>
Message posted via http://www.droptable.com
|||Correct.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:5d97d9ff865a5@.uwe...
> So, to confirm my understanding, with my current setup of a 5 disk RAID 5,
> even if it is logically partitioned, and the log and data files are on
> different partitions on the RAID 5, they [the log and data files] are still
> competing against each other. Is that correct?
>
|||cbrichards via droptable.com wrote:
> So, to confirm my understanding, with my current setup of a 5 disk RAID 5,
> even if it is logically partitioned, and the log and data files are on
> different partitions on the RAID 5, they [the log and data files] are still
> competing against each other. Is that correct?
>
You'll have to see a RAID array just like a single disk - it's just
"build" from a number of physical disks, but act as one. Like already
mentioned, an option would be to reconfigure your RAID into different
arrays using each their own disks. If available it's also advised to
split the RAID arrarys on different controllers - that can also give
some better performance on a busy storage.
Regards
Steen

RAID

I am trying to understand file or filegroup placement and RAID.
We have RAID 5 with 5 disks and one primary file group, no secondary.
Trying to keep this thread as simple as possible, just trying to focus on an
understanding of RAID, I sometimes read in publications that it can be
beneficial to place "each file on its own separate physical disk or disk
array".
I want to make sure I understand this statement in light of RAID 5. Does this
mean within RAID 5 I can use one of those 5 disks to place my Transaction Log,
and another one of those 5 disks to place my Primary Data file?
--
Message posted via http://www.sqlmonster.comNo. RAID does not give you access to the individual disks. You access them
as a group. If you can rebuild your RAID array, consider breaking it into a
3-disk RAID5 and a 2-disk RAID1. On the RAID5, put your data. On the
RAID1, put your logs.
If you can get one more disk, then instead of a 3-disk RAID5, go with a
4-disk RAID0+1.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:5d979be2bd3d1@.uwe...
I am trying to understand file or filegroup placement and RAID.
We have RAID 5 with 5 disks and one primary file group, no secondary.
Trying to keep this thread as simple as possible, just trying to focus on an
understanding of RAID, I sometimes read in publications that it can be
beneficial to place "each file on its own separate physical disk or disk
array".
I want to make sure I understand this statement in light of RAID 5. Does
this
mean within RAID 5 I can use one of those 5 disks to place my Transaction
Log,
and another one of those 5 disks to place my Primary Data file?
--
Message posted via http://www.sqlmonster.com|||So, to confirm my understanding, with my current setup of a 5 disk RAID 5,
even if it is logically partitioned, and the log and data files are on
different partitions on the RAID 5, they [the log and data files] are still
competing against each other. Is that correct?
Tom Moreau wrote:
>No. RAID does not give you access to the individual disks. You access them
>as a group. If you can rebuild your RAID array, consider breaking it into a
>3-disk RAID5 and a 2-disk RAID1. On the RAID5, put your data. On the
>RAID1, put your logs.
>If you can get one more disk, then instead of a 3-disk RAID5, go with a
>4-disk RAID0+1.
>I am trying to understand file or filegroup placement and RAID.
>We have RAID 5 with 5 disks and one primary file group, no secondary.
>Trying to keep this thread as simple as possible, just trying to focus on an
>understanding of RAID, I sometimes read in publications that it can be
>beneficial to place "each file on its own separate physical disk or disk
>array".
>I want to make sure I understand this statement in light of RAID 5. Does
>this
>mean within RAID 5 I can use one of those 5 disks to place my Transaction
>Log,
>and another one of those 5 disks to place my Primary Data file?
>
--
Message posted via http://www.sqlmonster.com|||Correct.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:5d97d9ff865a5@.uwe...
> So, to confirm my understanding, with my current setup of a 5 disk RAID 5,
> even if it is logically partitioned, and the log and data files are on
> different partitions on the RAID 5, they [the log and data files] are still
> competing against each other. Is that correct?
>|||cbrichards via SQLMonster.com wrote:
> So, to confirm my understanding, with my current setup of a 5 disk RAID 5,
> even if it is logically partitioned, and the log and data files are on
> different partitions on the RAID 5, they [the log and data files] are still
> competing against each other. Is that correct?
>
You'll have to see a RAID array just like a single disk - it's just
"build" from a number of physical disks, but act as one. Like already
mentioned, an option would be to reconfigure your RAID into different
arrays using each their own disks. If available it's also advised to
split the RAID arrarys on different controllers - that can also give
some better performance on a busy storage.
Regards
Steen

RAID

I am trying to understand file or filegroup placement and RAID.
We have RAID 5 with 5 disks and one primary file group, no secondary.
Trying to keep this thread as simple as possible, just trying to focus on an
understanding of RAID, I sometimes read in publications that it can be
beneficial to place "each file on its own separate physical disk or disk
array".
I want to make sure I understand this statement in light of RAID 5. Does thi
s
mean within RAID 5 I can use one of those 5 disks to place my Transaction Lo
g,
and another one of those 5 disks to place my Primary Data file?
Message posted via http://www.droptable.comNo. RAID does not give you access to the individual disks. You access them
as a group. If you can rebuild your RAID array, consider breaking it into a
3-disk RAID5 and a 2-disk RAID1. On the RAID5, put your data. On the
RAID1, put your logs.
If you can get one more disk, then instead of a 3-disk RAID5, go with a
4-disk RAID0+1.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5d979be2bd3d1@.uwe...
I am trying to understand file or filegroup placement and RAID.
We have RAID 5 with 5 disks and one primary file group, no secondary.
Trying to keep this thread as simple as possible, just trying to focus on an
understanding of RAID, I sometimes read in publications that it can be
beneficial to place "each file on its own separate physical disk or disk
array".
I want to make sure I understand this statement in light of RAID 5. Does
this
mean within RAID 5 I can use one of those 5 disks to place my Transaction
Log,
and another one of those 5 disks to place my Primary Data file?
Message posted via http://www.droptable.com|||So, to confirm my understanding, with my current setup of a 5 disk RAID 5,
even if it is logically partitioned, and the log and data files are on
different partitions on the RAID 5, they [the log and data files] are st
ill
competing against each other. Is that correct?
Tom Moreau wrote:
>No. RAID does not give you access to the individual disks. You access the
m
>as a group. If you can rebuild your RAID array, consider breaking it into
a
>3-disk RAID5 and a 2-disk RAID1. On the RAID5, put your data. On the
>RAID1, put your logs.
>If you can get one more disk, then instead of a 3-disk RAID5, go with a
>4-disk RAID0+1.
>I am trying to understand file or filegroup placement and RAID.
>We have RAID 5 with 5 disks and one primary file group, no secondary.
>Trying to keep this thread as simple as possible, just trying to focus on a
n
>understanding of RAID, I sometimes read in publications that it can be
>beneficial to place "each file on its own separate physical disk or disk
>array".
>I want to make sure I understand this statement in light of RAID 5. Does
>this
>mean within RAID 5 I can use one of those 5 disks to place my Transaction
>Log,
>and another one of those 5 disks to place my Primary Data file?
>
Message posted via http://www.droptable.com|||Correct.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:5d97d9ff865a5@.uwe...[vbcol
=seagreen]
> So, to confirm my understanding, with my current setup of a 5 disk RAID 5,
> even if it is logically partitioned, and the log and data files are on
> different partitions on the RAID 5, they [the log and data files] are
still
> competing against each other. Is that correct?
>[/vbcol]|||cbrichards via droptable.com wrote:
> So, to confirm my understanding, with my current setup of a 5 disk RAID 5,
> even if it is logically partitioned, and the log and data files are on
> different partitions on the RAID 5, they [the log and data files] are
still
> competing against each other. Is that correct?
>
You'll have to see a RAID array just like a single disk - it's just
"build" from a number of physical disks, but act as one. Like already
mentioned, an option would be to reconfigure your RAID into different
arrays using each their own disks. If available it's also advised to
split the RAID arrarys on different controllers - that can also give
some better performance on a busy storage.
Regards
Steen

-R option in BCP

Hi all,
I m trying to use BCP utility to upload a huge text file.
As per microsoft documentation, -R is used to specify the
regional settings. My computer (client) and the sql server
(running in a network machine) has dd/mm/yyyy as the
regional settings. The incomming text file also has date
in dd/mm/yyyy format. But when i execute BCP with -R
option, i still get an invalid date format error message.
I went through a few microsoft support information, that
told me that SP1 should solve this problem. I got SP3a
installed (both in client and in the server).
So what am i missing here? I m sure some of u here
should have also faced the problem. Any type of help is
greatly appreciated.
thanks and regards,
s.ravi sankarAlready answered in several other ng's.
--
Andrew J. Kelly
SQL Server MVP
"Ravi Sankar" <ravi_pv@.lycos.com> wrote in message
news:0aff01c3b026$b426bfb0$a101280a@.phx.gbl...
> Hi all,
> I m trying to use BCP utility to upload a huge text file.
> As per microsoft documentation, -R is used to specify the
> regional settings. My computer (client) and the sql server
> (running in a network machine) has dd/mm/yyyy as the
> regional settings. The incomming text file also has date
> in dd/mm/yyyy format. But when i execute BCP with -R
> option, i still get an invalid date format error message.
> I went through a few microsoft support information, that
> told me that SP1 should solve this problem. I got SP3a
> installed (both in client and in the server).
> So what am i missing here? I m sure some of u here
> should have also faced the problem. Any type of help is
> greatly appreciated.
> thanks and regards,
> s.ravi sankar
>

R

Our SQL Server 2005 - clustered - 64 bit - 4 CPU - 8 GB Memory - PerfMon counters:

Paging File:% usage peak (for the _Total instance) = 100%

Process:Page File Bytes Peak (for the sqlservr.exe process) = 8673640448

SQLServer:Buffer Manager:Total Pages = 617171

SQLServer:Memory Manager:Maximum Workspace Memory = 5780079

SQLServer:Memory Manager:Total Server Memory = 4954511

In the Windows Task Manager, the mem usage for sqlservr.exe goes up as high as 6 GB and then it drops down to 0. This process of going up and down repeats itself every 10 minutes or so. Appreciate any advice. Thanks.

Hi,

I see that the sql server process can consume as much memory as it requires. So the RAM or memory configurations seems to be done.

Can a query loads a huge data to memory. A full table scan with millions of records with GB's?

Eralper

Quoted identifiers

SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
I get the following entries in the text log file that the step creates:
Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
[1] Database cdw: Index Rebuild (leaving 5%% free space)...
Rebuilding indexes for table 'Accounts'
Rebuilding indexes for table 'Advisor Names'
Rebuilding indexes for table 'Advisor Rep Nums'
Rebuilding indexes for table 'Asset Master'
Rebuilding indexes for table 'BP Claim Filed'
Rebuilding indexes for table 'Breakpoints'
Rebuilding indexes for table 'BSE Trans Prefixes'
Rebuilding indexes for table 'BSE Trans Prefixes Rev'
Rebuilding indexes for table 'BSE Trans Prefixes Std'
Rebuilding indexes for table 'Core_MM_NFS'
Rebuilding indexes for table 'Core_MM_Pershing'
Rebuilding indexes for table 'Dates'
Rebuilding indexes for table 'Dates_MktOpen'
Rebuilding indexes for table 'DAZL Delete'
Rebuilding indexes for table 'Direct Business Transactions'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL Server Driver][SQL Server]DBCC failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
Well it would be nice if it didn't say "INCORRECT SETTING" but instead
would say that the setting should be ON, or should be OFF. (Quoted
Identifiers is ON in my database, according to Select
databasepropertyex.)
After reading all about quoted identifiers, I didn't think they were
stored by table -- I thought it was a database-wide property setting.
According to BOL, "When a table is created, the QUOTED IDENTIFIER option
is always stored as ON in the table's meta data even if the option is
set to OFF when the table is created."
SO, why would so many tables successfully get reindexed and then one of
them (was it 'Direct Business Transactions' or the next one in line?)
fail?
This doesn't make sense. What direction does Set Quoted Identifiers
need to be in for the reindex step of the DB maintenance plan to
succeed? I wish the actual DBCC command was displayed somewhere in this
log also, that would make debugging easier.
Thanks for any information.
David WalkerMore than likely you have a computed column or an indexed view. Both require
certain settings that the MP can't handle. Although I believe this was fixed
in SP4. Otherwise you need to do the reindex in your own custom job with
the proper settings set.
--
Andrew J. Kelly SQL MVP
"DWalker" <none@.none.com> wrote in message
news:uWrSsgLMGHA.1532@.TK2MSFTNGP12.phx.gbl...
> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker|||Hi David
Check out http://support.microsoft.com/default.aspx?scid=kb;en-us;902388,
the particular table probably failed because it is the first that has the
index on a computed column.
If not you may want to look at SQL profiler to see exactly what statements
are being sent to the database by the maintenance plan.
John
"DWalker" wrote:
> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker
>|||Thanks to you both. I do have SP4. I'll double-check that table for a
computed column; I created it a long time ago.
David
=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
> Hi David
> Check out
> http://support.microsoft.com/default.aspx?scid=kb;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:
> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
Ah, so the maintenance plan generator can't maintain any table that has
computed coumns? Interesting.
It also was complaining about other settings that required the database to
be in single-user mode. WTF? It tries to run statements that require
single-user mode? Maybe it ought to try to put the database in single-user
mode, or maybe that's not a good idea. The check-marks in the maintenance
plan generator ought to tell you that single-user mode is required.
My conclusion from this is that the maintenace plan generator is not
suitable for use on databases in the real world. Is that the right
conclusion?
Thanks.
David|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:
> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
So yes, the table did have one computed column. The table can't be
maintained with a maintenance plan?
Or the indexes can't ever be rebuilt?
David|||Hi David
You may want to look at the code in:
http://support.microsoft.com/default.aspx?scid=kb;en-us;301292
John
"DWalker" wrote:
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
> news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:
> > More than likely you have a computed column or an indexed view. Both
> > require certain settings that the MP can't handle. Although I believe
> > this was fixed in SP4. Otherwise you need to do the reindex in your
> > own custom job with the proper settings set.
> >
> So yes, the table did have one computed column. The table can't be
> maintained with a maintenance plan?
> Or the indexes can't ever be rebuilt?
> David
>|||=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
> Hi David
> Check out
> http://support.microsoft.com/default.aspx?scid=kb;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>
That article is sure confusing; it says "These statements require that
the QUOTED_IDENTIFIER SET option is set to ON."
It *IS* set to On in my database. Then it goes on to say, I think, that
the command is built wrong by the maintenance wizard. Why it couldn't
have been fixed in SP4 without requiring me to add the -
SupportcomputedColumn parameter is not explained...
I added the -SupportComputedColumn option to the plan step, and it will
probably fix it, but I'm still a bit confused by the wording in the
article.
Tthanks for the pointer though!
David Walker|||> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database.
The database setting is essentially useless since it will be overridden by session setting. And most
API's will set this setting, whether the developer is aware of it or not.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DWalker" <none@.none.com> wrote in message news:%23ZTTnSYMGHA.1532@.TK2MSFTNGP12.phx.gbl...
> =?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
>> Hi David
>> Check out
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;902388, the
>> particular table probably failed because it is the first that has the
>> index on a computed column.
>> If not you may want to look at SQL profiler to see exactly what
>> statements are being sent to the database by the maintenance plan.
>> John
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker|||Hi David
You may want to use the send feedback option at the bottom of the article.
John
"DWalker" wrote:
> =?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
> > Hi David
> >
> > Check out
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;902388, the
> > particular table probably failed because it is the first that has the
> > index on a computed column.
> >
> > If not you may want to look at SQL profiler to see exactly what
> > statements are being sent to the database by the maintenance plan.
> >
> > John
> >
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker
>|||=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:D4BDB694-0A2E-408D-AA7A-562DBEC42515@.microsoft.com:
> Hi David
> You may want to use the send feedback option at the bottom of the
> article.
> John
>
I'll do that.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OsfT#fYMGHA.3460@.TK2MSFTNGP15.phx.gbl:
>> That article is sure confusing; it says "These statements require
>> that the QUOTED_IDENTIFIER SET option is set to ON."
>> It *IS* set to On in my database.
> The database setting is essentially useless since it will be
> overridden by session setting. And most API's will set this setting,
> whether the developer is aware of it or not.
>
Thanks, Tibor.
Davidsql

Quoted identifiers

SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
I get the following entries in the text log file that the step creates:
Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
[1] Database cdw: Index Rebuild (leaving 5%% free space)...
Rebuilding indexes for table 'Accounts'
Rebuilding indexes for table 'Advisor Names'
Rebuilding indexes for table 'Advisor Rep Nums'
Rebuilding indexes for table 'Asset Master'
Rebuilding indexes for table 'BP Claim Filed'
Rebuilding indexes for table 'Breakpoints'
Rebuilding indexes for table 'BSE Trans Prefixes'
Rebuilding indexes for table 'BSE Trans Prefixes Rev'
Rebuilding indexes for table 'BSE Trans Prefixes Std'
Rebuilding indexes for table 'Core_MM_NFS'
Rebuilding indexes for table 'Core_MM_Pershing'
Rebuilding indexes for table 'Dates'
Rebuilding indexes for table 'Dates_MktOpen'
Rebuilding indexes for table 'DAZL Delete'
Rebuilding indexes for table 'Direct Business Transactions'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC
SQL Server Driver][SQL Server]DBCC failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
Well it would be nice if it didn't say "INCORRECT SETTING" but instead
would say that the setting should be ON, or should be OFF. (Quoted
Identifiers is ON in my database, according to Select
databasepropertyex.)
After reading all about quoted identifiers, I didn't think they were
stored by table -- I thought it was a database-wide property setting.
According to BOL, "When a table is created, the QUOTED IDENTIFIER option
is always stored as ON in the table's meta data even if the option is
set to OFF when the table is created."
SO, why would so many tables successfully get reindexed and then one of
them (was it 'Direct Business Transactions' or the next one in line?)
fail?
This doesn't make sense. What direction does Set Quoted Identifiers
need to be in for the reindex step of the DB maintenance plan to
succeed? I wish the actual DBCC command was displayed somewhere in this
log also, that would make debugging easier.
Thanks for any information.
David WalkerMore than likely you have a computed column or an indexed view. Both require
certain settings that the MP can't handle. Although I believe this was fixed
in SP4. Otherwise you need to do the reindex in your own custom job with
the proper settings set.
Andrew J. Kelly SQL MVP
"DWalker" <none@.none.com> wrote in message
news:uWrSsgLMGHA.1532@.TK2MSFTNGP12.phx.gbl...
> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker|||Hi David
Check out http://support.microsoft.com/defaul...b;en-us;902388,
the particular table probably failed because it is the first that has the
index on a computed column.
If not you may want to look at SQL profiler to see exactly what statements
are being sent to the database by the maintenance plan.
John
"DWalker" wrote:

> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker
>|||Thanks to you both. I do have SP4. I'll double-check that table for a
computed column; I created it a long time ago.
David
examnotes <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:

> Hi David
> Check out
> http://support.microsoft.com/defaul...b;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:

> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
Ah, so the maintenance plan generator can't maintain any table that has
computed coumns? Interesting.
It also was complaining about other settings that required the database to
be in single-user mode. WTF? It tries to run statements that require
single-user mode? Maybe it ought to try to put the database in single-user
mode, or maybe that's not a good idea. The check-marks in the maintenance
plan generator ought to tell you that single-user mode is required.
My conclusion from this is that the maintenace plan generator is not
suitable for use on databases in the real world. Is that the right
conclusion?
Thanks.
David|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:

> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
So yes, the table did have one computed column. The table can't be
maintained with a maintenance plan?
Or the indexes can't ever be rebuilt?
David|||Hi David
You may want to look at the code in:
http://support.microsoft.com/defaul...kb;en-us;301292
John
"DWalker" wrote:

> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
> news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:
>
> So yes, the table did have one computed column. The table can't be
> maintained with a maintenance plan?
> Or the indexes can't ever be rebuilt?
> David
>|||examnotes <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:

> Hi David
> Check out
> http://support.microsoft.com/defaul...b;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>
That article is sure confusing; it says "These statements require that
the QUOTED_IDENTIFIER SET option is set to ON."
It *IS* set to On in my database. Then it goes on to say, I think, that
the command is built wrong by the maintenance wizard. Why it couldn't
have been fixed in SP4 without requiring me to add the -
SupportcomputedColumn parameter is not explained...
I added the -SupportComputedColumn option to the plan step, and it will
probably fix it, but I'm still a bit confused by the wording in the
article.
Tthanks for the pointer though!
David Walker|||> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database.
The database setting is essentially useless since it will be overridden by s
ession setting. And most
API's will set this setting, whether the developer is aware of it or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DWalker" <none@.none.com> wrote in message news:%23ZTTnSYMGHA.1532@.TK2MSFTNGP12.phx.gbl...[v
bcol=seagreen]
> examnotes <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
>
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker[/vbcol]|||Hi David
You may want to use the send feedback option at the bottom of the article.
John
"DWalker" wrote:

> examnotes <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
>
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker
>

Quoted identifiers

SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
I get the following entries in the text log file that the step creates:
Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
[1] Database cdw: Index Rebuild (leaving 5%% free space)...
Rebuilding indexes for table 'Accounts'
Rebuilding indexes for table 'Advisor Names'
Rebuilding indexes for table 'Advisor Rep Nums'
Rebuilding indexes for table 'Asset Master'
Rebuilding indexes for table 'BP Claim Filed'
Rebuilding indexes for table 'Breakpoints'
Rebuilding indexes for table 'BSE Trans Prefixes'
Rebuilding indexes for table 'BSE Trans Prefixes Rev'
Rebuilding indexes for table 'BSE Trans Prefixes Std'
Rebuilding indexes for table 'Core_MM_NFS'
Rebuilding indexes for table 'Core_MM_Pershing'
Rebuilding indexes for table 'Dates'
Rebuilding indexes for table 'Dates_MktOpen'
Rebuilding indexes for table 'DAZL Delete'
Rebuilding indexes for table 'Direct Business Transactions'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL Server Driver][SQL Server]DBCC failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
Well it would be nice if it didn't say "INCORRECT SETTING" but instead
would say that the setting should be ON, or should be OFF. (Quoted
Identifiers is ON in my database, according to Select
databasepropertyex.)
After reading all about quoted identifiers, I didn't think they were
stored by table -- I thought it was a database-wide property setting.
According to BOL, "When a table is created, the QUOTED IDENTIFIER option
is always stored as ON in the table's meta data even if the option is
set to OFF when the table is created."
SO, why would so many tables successfully get reindexed and then one of
them (was it 'Direct Business Transactions' or the next one in line?)
fail?
This doesn't make sense. What direction does Set Quoted Identifiers
need to be in for the reindex step of the DB maintenance plan to
succeed? I wish the actual DBCC command was displayed somewhere in this
log also, that would make debugging easier.
Thanks for any information.
David Walker
More than likely you have a computed column or an indexed view. Both require
certain settings that the MP can't handle. Although I believe this was fixed
in SP4. Otherwise you need to do the reindex in your own custom job with
the proper settings set.
Andrew J. Kelly SQL MVP
"DWalker" <none@.none.com> wrote in message
news:uWrSsgLMGHA.1532@.TK2MSFTNGP12.phx.gbl...
> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker
|||Hi David
Check out http://support.microsoft.com/default...;en-us;902388,
the particular table probably failed because it is the first that has the
index on a computed column.
If not you may want to look at SQL profiler to see exactly what statements
are being sent to the database by the maintenance plan.
John
"DWalker" wrote:

> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker
>
|||Thanks to you both. I do have SP4. I'll double-check that table for a
computed column; I created it a long time ago.
David
=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:

> Hi David
> Check out
> http://support.microsoft.com/default...;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:

> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
Ah, so the maintenance plan generator can't maintain any table that has
computed coumns? Interesting.
It also was complaining about other settings that required the database to
be in single-user mode. WTF? It tries to run statements that require
single-user mode? Maybe it ought to try to put the database in single-user
mode, or maybe that's not a good idea. The check-marks in the maintenance
plan generator ought to tell you that single-user mode is required.
My conclusion from this is that the maintenace plan generator is not
suitable for use on databases in the real world. Is that the right
conclusion?
Thanks.
David
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:

> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
So yes, the table did have one computed column. The table can't be
maintained with a maintenance plan?
Or the indexes can't ever be rebuilt?
David
|||Hi David
You may want to look at the code in:
http://support.microsoft.com/default...b;en-us;301292
John
"DWalker" wrote:

> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
> news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:
>
> So yes, the table did have one computed column. The table can't be
> maintained with a maintenance plan?
> Or the indexes can't ever be rebuilt?
> David
>
|||=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:

> Hi David
> Check out
> http://support.microsoft.com/default...;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>
That article is sure confusing; it says "These statements require that
the QUOTED_IDENTIFIER SET option is set to ON."
It *IS* set to On in my database. Then it goes on to say, I think, that
the command is built wrong by the maintenance wizard. Why it couldn't
have been fixed in SP4 without requiring me to add the -
SupportcomputedColumn parameter is not explained...
I added the -SupportComputedColumn option to the plan step, and it will
probably fix it, but I'm still a bit confused by the wording in the
article.
Tthanks for the pointer though!
David Walker
|||> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database.
The database setting is essentially useless since it will be overridden by session setting. And most
API's will set this setting, whether the developer is aware of it or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DWalker" <none@.none.com> wrote in message news:%23ZTTnSYMGHA.1532@.TK2MSFTNGP12.phx.gbl...
> =?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
>
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker
|||Hi David
You may want to use the send feedback option at the bottom of the article.
John
"DWalker" wrote:

> =?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
>
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker
>

Quoted Field and Escape Character Issue

I am attempting to import a flat file and have come accross and issue that I do not know how to fix in SSIS. The issue is that some of the text fields use quoted identifiers. This is not an issue in itself. The problem is they also use quotes as escape character if quotes are on the field.

So I see instances of "" because inside the quoted field is a quote. How do i specify an escape character?

Unfortuanately, the current Flat file parser does not know how to parse embedded qualifiers.

As a workaround, you should probably keep the qualifiers in the flat file source and process them downstream using either the script component or Derived Column.

Thanks.

Quick question on log file sizes

I'll be taking over an existing database in the near future and I noticed
something that seemed odd to me. That would be that the log file rather
large, 15GB. On the days that I've been monitoring this, I have never seen
more the 1% of the log space being used. When I asked about this, the answer
I got back was this: The log file was created that large so that no matter
how much data was in it, the log file would be physically contiguous.
My question, is this line of reasoning sound? If the physical file
contiguous, does this improve SQL performance?
TIAAbsolutely for a number of reasons and true for data files as well. Log
files are read and written to in a mostly sequential manor. If the file is
contiguous on disk the heads will not have to move back and forth as it
reads or writes. On a small system you may never notice the difference but
on a busy system it can. The other reason is that having plenty of free
space will ensure the log file never has to grow which is expensive. Is
15GB too big? That depends on what you are doing with it. While you may
only see 1% usage during the day what about during reindexing? It is always
better to have too much free space than too little.
Andrew J. Kelly SQL MVP
"JD" <joeydba@.yahoo.com> wrote in message news:421a5260$1@.news.qgraph.com...
> I'll be taking over an existing database in the near future and I noticed
> something that seemed odd to me. That would be that the log file rather
> large, 15GB. On the days that I've been monitoring this, I have never
> seen
> more the 1% of the log space being used. When I asked about this, the
> answer
> I got back was this: The log file was created that large so that no matter
> how much data was in it, the log file would be physically contiguous.
> My question, is this line of reasoning sound? If the physical file
> contiguous, does this improve SQL performance?
> TIA
>|||Thank you Andrew for the reply.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OYaMyrGGFHA.4004@.tk2msftngp13.phx.gbl...
> Absolutely for a number of reasons and true for data files as well. Log
> files are read and written to in a mostly sequential manor. If the file
is
> contiguous on disk the heads will not have to move back and forth as it
> reads or writes. On a small system you may never notice the difference but
> on a busy system it can. The other reason is that having plenty of free
> space will ensure the log file never has to grow which is expensive. Is
> 15GB too big? That depends on what you are doing with it. While you may
> only see 1% usage during the day what about during reindexing? It is
always
> better to have too much free space than too little.
> --
> Andrew J. Kelly SQL MVP
>
> "JD" <joeydba@.yahoo.com> wrote in message
news:421a5260$1@.news.qgraph.com...
noticed[vbcol=seagreen]
matter[vbcol=seagreen]
>

Quick question on log file sizes

I'll be taking over an existing database in the near future and I noticed
something that seemed odd to me. That would be that the log file rather
large, 15GB. On the days that I've been monitoring this, I have never seen
more the 1% of the log space being used. When I asked about this, the answer
I got back was this: The log file was created that large so that no matter
how much data was in it, the log file would be physically contiguous.
My question, is this line of reasoning sound? If the physical file
contiguous, does this improve SQL performance?
TIAAbsolutely for a number of reasons and true for data files as well. Log
files are read and written to in a mostly sequential manor. If the file is
contiguous on disk the heads will not have to move back and forth as it
reads or writes. On a small system you may never notice the difference but
on a busy system it can. The other reason is that having plenty of free
space will ensure the log file never has to grow which is expensive. Is
15GB too big? That depends on what you are doing with it. While you may
only see 1% usage during the day what about during reindexing? It is always
better to have too much free space than too little.
--
Andrew J. Kelly SQL MVP
"JD" <joeydba@.yahoo.com> wrote in message news:421a5260$1@.news.qgraph.com...
> I'll be taking over an existing database in the near future and I noticed
> something that seemed odd to me. That would be that the log file rather
> large, 15GB. On the days that I've been monitoring this, I have never
> seen
> more the 1% of the log space being used. When I asked about this, the
> answer
> I got back was this: The log file was created that large so that no matter
> how much data was in it, the log file would be physically contiguous.
> My question, is this line of reasoning sound? If the physical file
> contiguous, does this improve SQL performance?
> TIA
>|||Thank you Andrew for the reply.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OYaMyrGGFHA.4004@.tk2msftngp13.phx.gbl...
> Absolutely for a number of reasons and true for data files as well. Log
> files are read and written to in a mostly sequential manor. If the file
is
> contiguous on disk the heads will not have to move back and forth as it
> reads or writes. On a small system you may never notice the difference but
> on a busy system it can. The other reason is that having plenty of free
> space will ensure the log file never has to grow which is expensive. Is
> 15GB too big? That depends on what you are doing with it. While you may
> only see 1% usage during the day what about during reindexing? It is
always
> better to have too much free space than too little.
> --
> Andrew J. Kelly SQL MVP
>
> "JD" <joeydba@.yahoo.com> wrote in message
news:421a5260$1@.news.qgraph.com...
> > I'll be taking over an existing database in the near future and I
noticed
> > something that seemed odd to me. That would be that the log file rather
> > large, 15GB. On the days that I've been monitoring this, I have never
> > seen
> > more the 1% of the log space being used. When I asked about this, the
> > answer
> > I got back was this: The log file was created that large so that no
matter
> > how much data was in it, the log file would be physically contiguous.
> >
> > My question, is this line of reasoning sound? If the physical file
> > contiguous, does this improve SQL performance?
> >
> > TIA
> >
> >
>

Quick question on log file sizes

I'll be taking over an existing database in the near future and I noticed
something that seemed odd to me. That would be that the log file rather
large, 15GB. On the days that I've been monitoring this, I have never seen
more the 1% of the log space being used. When I asked about this, the answer
I got back was this: The log file was created that large so that no matter
how much data was in it, the log file would be physically contiguous.
My question, is this line of reasoning sound? If the physical file
contiguous, does this improve SQL performance?
TIA
Absolutely for a number of reasons and true for data files as well. Log
files are read and written to in a mostly sequential manor. If the file is
contiguous on disk the heads will not have to move back and forth as it
reads or writes. On a small system you may never notice the difference but
on a busy system it can. The other reason is that having plenty of free
space will ensure the log file never has to grow which is expensive. Is
15GB too big? That depends on what you are doing with it. While you may
only see 1% usage during the day what about during reindexing? It is always
better to have too much free space than too little.
Andrew J. Kelly SQL MVP
"JD" <joeydba@.yahoo.com> wrote in message news:421a5260$1@.news.qgraph.com...
> I'll be taking over an existing database in the near future and I noticed
> something that seemed odd to me. That would be that the log file rather
> large, 15GB. On the days that I've been monitoring this, I have never
> seen
> more the 1% of the log space being used. When I asked about this, the
> answer
> I got back was this: The log file was created that large so that no matter
> how much data was in it, the log file would be physically contiguous.
> My question, is this line of reasoning sound? If the physical file
> contiguous, does this improve SQL performance?
> TIA
>
|||Thank you Andrew for the reply.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OYaMyrGGFHA.4004@.tk2msftngp13.phx.gbl...
> Absolutely for a number of reasons and true for data files as well. Log
> files are read and written to in a mostly sequential manor. If the file
is
> contiguous on disk the heads will not have to move back and forth as it
> reads or writes. On a small system you may never notice the difference but
> on a busy system it can. The other reason is that having plenty of free
> space will ensure the log file never has to grow which is expensive. Is
> 15GB too big? That depends on what you are doing with it. While you may
> only see 1% usage during the day what about during reindexing? It is
always
> better to have too much free space than too little.
> --
> Andrew J. Kelly SQL MVP
>
> "JD" <joeydba@.yahoo.com> wrote in message
news:421a5260$1@.news.qgraph.com...[vbcol=seagreen]
noticed[vbcol=seagreen]
matter
>

Quick Bulk Insert question

On a Friday afternoon, no less.

Anyway, in a nutshell, I have a flat file (tab-delimited, row terminator is ":0D0A" (or '\r\n'). I want to bulk import it into a table that is defined EXACTLY like the table (on a remote system) that the data comes from (I stole the remote tables DDL by scripting the table definition).

Some of the columns in the table are BIT data types.

In the flat (text) file that I get, those column values are present as the literal words "true" and "false".

When I do the bulk insertBULK INSERT dbo.Staging_Funds
FROM 'D:\TradeAnalysis\ImportedFiles\MutualFunds.txt '
WITH ( FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\r\n',
TABLOCK) it fails saying there is a data type mismatch between the file and the local table column that corresponds to the first BIT data type in the file.

Thinking (uh-oh) there has got to be an easier way, I modify my bulk insert and add a DATAFILETYPE = 'native' to the bulk insert. This gets me past the error (or not) but results in a DIFFERENT error. Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.I do, they are.

Is this an either/or situation? Meaning, if I use "native" format, does it ignore the fieldterminator for some reason?

I am tempted to modify my staging table to just pull in the flag (BIT) columns as char(5) columns, and be done with it (we don't use the flag bits at present anyway) but there is also a part of me that says this should be doable, for crissake.

Any quick guidance or thoughts (s'OK, the thoughts can be slower...it's Friday, I know)Native is the easy way out.

-PatP|||Yeah, but then I get the error about the line being too long. I'll play with it more on Monday. For the time being I just made the bit columns varchar. THAT is the easy way out ;)

It somehow just doesn't "feel right" though.|||If you BCP the data out using Native, then BCP it back in using Native, you'd better not get any error! That would be a really, really bad sign.

-PatP|||OK, I'm just putting this out there...
Can you use DTS to change 'true' into 1 and 'false' into 0? I am not as savvy as our resident curmudgeon on these matters, but it's something I'd investigate.

hth

quick and easy install question

I would like to install SQL Server 2000 Personal Edition, but I don't know
where to find the install file? Is it on the SQL Server Enterprise cd? Is
it on its own cd?
Thanks,
KeithOn its own CD, which you need to find somewhere. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Keith" <Keith@.noemail.com> wrote in message
news:eMJuLpEDEHA.3348@.TK2MSFTNGP11.phx.gbl...
> I would like to install SQL Server 2000 Personal Edition, but I don't know
> where to find the install file? Is it on the SQL Server Enterprise cd?
Is
> it on its own cd?
> Thanks,
> Keith
>

Queuing Files

I have a requirement of instantiating a job which will copy and move the
newly created file once a file has been created in a folder. How can I
perform this in SQL Server. Can you please give your thoughts about this.
What I was planning was to write a visual basic code which will pool the
files getting created into the folder and accordingly copy and paste into
another folder. I feel this is not a better method.
I really wanted a queuing system to be implemented.Hi, there's a script I am using to copy files:
USE [msdb]
GO
/****** Object: Job [CopyToCluster] Script Date: 03/20/2006 15:24:31 ******/
BEGIN TRANSACTION
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 03/20/2006
15:24:31 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database
Maintenance' AND category_class=1)
BEGIN
EXEC @.ReturnCode = msdb.dbo.sp_add_category @.class=N'JOB', @.type=N'LOCAL',
@.name=N'Database Maintenance'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @.jobId BINARY(16)
EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=N'CopyToCluster',
@.enabled=1,
@.notify_level_eventlog=2,
@.notify_level_email=0,
@.notify_level_netsend=0,
@.notify_level_page=0,
@.delete_level=0,
@.description=N'Copies all files rom the local backup directory to
\\datacluster\\Backup',
@.category_name=N'Database Maintenance',
@.owner_login_name=N'YOUR_USER', @.job_id = @.jobId OUTPUT
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Copy files] Script Date: 03/20/2006 15:24:31 ******/
EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'Copy
files',
@.step_id=1,
@.cmdexec_success_code=0,
@.on_success_action=1,
@.on_success_step_id=0,
@.on_fail_action=2,
@.on_fail_step_id=0,
@.retry_attempts=2,
@.retry_interval=0,
@.os_run_priority=0, @.subsystem=N'CmdExec',
@.command=N'c:\copybackups.cmd $(DATE)$(TIME)',
@.flags=4
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.jobId, @.start_step_id =
1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id=@.jobId,
@.name=N'CopyBackupFilesSched',
@.enabled=1,
@.freq_type=4,
@.freq_interval=1,
@.freq_subday_type=1,
@.freq_subday_interval=0,
@.freq_relative_interval=0,
@.freq_recurrence_factor=0,
@.active_start_date=20060106,
@.active_end_date=99991231,
@.active_start_time=50000,
@.active_end_time=235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_name
= N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
and this is copypackups.cmd:
rem PR 2006-01-09
rem This batch file copies daily backups of databases to network location
rem
@.echo . > c:\copybackups.log
@.echo %1 Copying files from c:\SQLBackup to \\datacluster\Backup... >>
c:\copybackups.log
xcopy c:\SQLBackup\*.* \\datacluster\Backup\*.* /L /R /H /D /V /Y /F /C >>
c:\copybackups.log
xcopy c:\SQLBackup\*.* \\datacluster\Backup\*.* /R /H /D /V /Y /F /C >>
c:\copybackups.log
@.echo done >> c:\copybackups.log
@.echo . >> c:\copybackups.log
Note that this file does not use date and time passed by the job. I have
another job that removes older files from local directory.
HTH
Peter|||Thank you very much for your response.
What I need is watch for newly added files to a folder. The moment a new
file is added, I need to copy and paste into another location. This is
something like a service which keeps on monitoring a folder for new files
coming in.
Finally once the day is fininshed I will have a copy of the master folder in
another location also. I need to do this on receipt of each file, not finall
y
at the end of a day.
Thanks & Regards,
VB Babunath
"Rogas69" wrote:

> Hi, there's a script I am using to copy files:
> USE [msdb]
> GO
> /****** Object: Job [CopyToCluster] Script Date: 03/20/2006 15:24:31 ******/
> BEGIN TRANSACTION
> DECLARE @.ReturnCode INT
> SELECT @.ReturnCode = 0
> /****** Object: JobCategory [Database Maintenance] Script Date: 03/20/2006
> 15:24:31 ******/
> IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Databa
se
> Maintenance' AND category_class=1)
> BEGIN
> EXEC @.ReturnCode = msdb.dbo.sp_add_category @.class=N'JOB', @.type=N'LOCAL',
> @.name=N'Database Maintenance'
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> END
> DECLARE @.jobId BINARY(16)
> EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=N'CopyToCluster',
> @.enabled=1,
> @.notify_level_eventlog=2,
> @.notify_level_email=0,
> @.notify_level_netsend=0,
> @.notify_level_page=0,
> @.delete_level=0,
> @.description=N'Copies all files rom the local backup directory to
> \\datacluster\\Backup',
> @.category_name=N'Database Maintenance',
> @.owner_login_name=N'YOUR_USER', @.job_id = @.jobId OUTPUT
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> /****** Object: Step [Copy files] Script Date: 03/20/2006 15:24:31 ******/
> EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'Co
py
> files',
> @.step_id=1,
> @.cmdexec_success_code=0,
> @.on_success_action=1,
> @.on_success_step_id=0,
> @.on_fail_action=2,
> @.on_fail_step_id=0,
> @.retry_attempts=2,
> @.retry_interval=0,
> @.os_run_priority=0, @.subsystem=N'CmdExec',
> @.command=N'c:\copybackups.cmd $(DATE)$(TIME)',
> @.flags=4
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> EXEC @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.jobId, @.start_step_id
=
> 1
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> EXEC @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id=@.jobId,
> @.name=N'CopyBackupFilesSched',
> @.enabled=1,
> @.freq_type=4,
> @.freq_interval=1,
> @.freq_subday_type=1,
> @.freq_subday_interval=0,
> @.freq_relative_interval=0,
> @.freq_recurrence_factor=0,
> @.active_start_date=20060106,
> @.active_end_date=99991231,
> @.active_start_time=50000,
> @.active_end_time=235959
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_nam
e
> = N'(local)'
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> COMMIT TRANSACTION
> GOTO EndSave
> QuitWithRollback:
> IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
> EndSave:
>
> and this is copypackups.cmd:
> rem PR 2006-01-09
> rem This batch file copies daily backups of databases to network location
> rem
> @.echo . > c:\copybackups.log
> @.echo %1 Copying files from c:\SQLBackup to \\datacluster\Backup... >>
> c:\copybackups.log
> xcopy c:\SQLBackup\*.* \\datacluster\Backup\*.* /L /R /H /D /V /Y /F /C >>
> c:\copybackups.log
> xcopy c:\SQLBackup\*.* \\datacluster\Backup\*.* /R /H /D /V /Y /F /C >>
> c:\copybackups.log
> @.echo done >> c:\copybackups.log
> @.echo . >> c:\copybackups.log
>
> Note that this file does not use date and time passed by the job. I have
> another job that removes older files from local directory.
> HTH
> Peter
>
>

questions on SQL Profiler

I am using SQL Profiler to monitor one of my database.
The temporary trace file is stored under C:\Documents and Settings\user
\Local Settings\Temp
by default. Is it possible to re-configure it to place to another
location as the file become huge after some time.

ThanksOn Apr 17, 2:26 pm, "akkha1...@.gmail.com" <akkha1...@.gmail.comwrote:

Quote:

Originally Posted by

I am using SQL Profiler to monitor one of my database.
The temporary trace file is stored under C:\Documents and Settings\user
\Local Settings\Temp
by default. Is it possible to re-configure it to place to another
location as the file become huge after some time.
>
Thanks

Are you using SQL 2k or SQL 2k5? In SQL 2005, in the New Trace dialog,
there is a "save to file" option, where you can specify the drive to
log trace data to.

Alternatively, if you're doing the trace programmatically, you can set
a param to denote location to save trace to.

Chadd|||akkha1234@.gmail.com (akkha1234@.gmail.com) writes:

Quote:

Originally Posted by

I am using SQL Profiler to monitor one of my database.
The temporary trace file is stored under C:\Documents and Settings\user
\Local Settings\Temp
by default. Is it possible to re-configure it to place to another
location as the file become huge after some time.

You can change the TEMP and TMP environment variables in the System
applet in the Control Panel. (Under the Advanced tab.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Changing the location of trace file does not serve the purposes.
Changing the TMP is the answer and you have to log off and then login
to make it to be effective for profiler.
Thanks for both of you anyway for the prompt response. Now I know a
bit more about the windows environment.