Multiple XML Files as DataSource

Hi All,

New to the forums, so, hello.

I want to import data from multiple XML files into SQL 2005 using SSIS. I know how to set up a flat data source etc... but am unsure how I would go about importing from multiple files.

Anyone got any ideas?

Thanks,
Drammy

For XML files, you obviously use the XML source.

The multiple files issue is the same for flat files or XML files, generally the For Each Loop container, which has a For Each File enumerator.

There are plenty of example of how to use this, including Books Online. Here is just one -

Looping over files with the Foreach Loop
(http://www.sqlis.com/55.aspx)

|||Thanks DarrenSQLIS,

Drammy
|||I have actually found that this doesn't work with XML files...

I'm getting an error:

TITLE: Editing Component

The component has detected potential metadata corruption during validation.
Error at Import XML Files [XML Source [1]]: The property "XMLDataVariable" on the component "XML Source" (1) was empty.

Error at Import XML Files [XML Source [1]]: The component "XML Source" (1) was unable to read the XML data.

Error at Import XML Files [XML Source [1]]: There was an error setting up the mapping. Pipeline component has returned HRESULT error code 0xC02090D0 from a method call.

It may not be possible to recover to a valid state using a component-specific editor. Do you want to use the Advanced Editor dialog box for editing this component?

BUTTONS:

&Yes
&No

Any ideas?
|||

Even if you are using expressions to populate certain properties at run time; BIDS requires some properties have an 'initia' or 'design time' value; so the task/package can be validate. Make sure you provide a value for XMLDataVariable property in XML Source component.

If you are doing so, then provide more details of what you are doing in the package.

sql

multiple wildcards in Foreach Loop

Hi, I am using Foreach Loop to loop through files in a directory...
I would like to use more than one wildcards (e.g. *.txt *.log ).. but the container does not seem to work that way. It only takes one wildcard...

Is there anyway i can pass in multiple file extensions ?
thanks

Hi mf915,

I do not know if this will help.

If you have only .txt and .log files in the directory then you can use filename.* or *.*. If there is some files in the directory that you do not want to get picked up by the foreach loop, then you will have to move the files to a seperate folder. This will only work if your .txt and .log files is in the same format for example comma separated, have the same number of columns and headings.

Kind Regards,

Joos Nieuwoudt

Multiple Trans Log Restores

Is there a way to restore multiple transactions logs when
you don't know the name of the logs files (i.e. the last 8
characters of the log file changes)?
Thanks,
JoshYou could either use xp_cmdshell and DIR into a temp table and work your code from there, or base
your restore on the backup history tables in msdb (assuming that the backup was taken on the same
machine). Both options requires a bit of coding.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Joshua Lakin" <LakinJ@.NOSPAMPLEASE.bsci.com> wrote in message
news:00a501c3c58f$8474d3f0$a401280a@.phx.gbl...
> Is there a way to restore multiple transactions logs when
> you don't know the name of the logs files (i.e. the last 8
> characters of the log file changes)?
> Thanks,
> Josh

multiple text files mining

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

1.I should use a SSIS solution using "Import Column transform" to get the files and store in a column with varbinary(max) data type; if you want to store only text in a column you can use varchar(max) data type (you can store maximum 2 GB)

2.Using SSIS solution I told there is a way to matching file (use "for each loop container")

3.For my ideea you don't to do that; you can run periodically the package created with SSIS in a job , depends on your business logic

4.Study the tutorial from here if you want create a text mining project. The ideea if you want to know let say the frequence of the terms /phrases, extracting clustering terms/concepts from the docs.

|||

does SSIS have a readymade package to compare mulitple files? if yes how does one go about it? thanx man !!!

|||

what did ou mean "compare multiple files"?

using for each loop container ou can select *.txt or *.doc files or have a special name.

if mean comparing contents of files i think you can use script task when you can customize this comparison using .net (as you should did it using .NET)

|||

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

|||

vickwal wrote:

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

You can use MS Integration Service.

I think you have-to convert (presumably MS Word) .doc format into plain text .txt.

Then you can load .txt files into a table like ESSAYS(AUTHOR varchar(255), FILENAME varchar(255), [Content] TEXT) using For Each Loop Container control.

Then use Fuzzy Lookup comparing by Content field, using same ESSAYS table as base and as Lookup table.

You can play with Similarity Threshold there.

Fuzzy Lookup operator will produce an output for each row of base table where Similarity and Confidence columns will be. Just spool it into another table.

good luck,

Mark

multiple text files mining

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

1.I should use a SSIS solution using "Import Column transform" to get the files and store in a column with varbinary(max) data type; if you want to store only text in a column you can use varchar(max) data type (you can store maximum 2 GB)

2.Using SSIS solution I told there is a way to matching file (use "for each loop container")

3.For my ideea you don't to do that; you can run periodically the package created with SSIS in a job , depends on your business logic

4.Study the tutorial from here if you want create a text mining project. The ideea if you want to know let say the frequence of the terms /phrases, extracting clustering terms/concepts from the docs.

|||

does SSIS have a readymade package to compare mulitple files? if yes how does one go about it? thanx man !!!

|||

what did ou mean "compare multiple files"?

using for each loop container ou can select *.txt or *.doc files or have a special name.

if mean comparing contents of files i think you can use script task when you can customize this comparison using .net (as you should did it using .NET)

|||

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

|||

vickwal wrote:

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

You can use MS Integration Service.

I think you have-to convert (presumably MS Word) .doc format into plain text .txt.

Then you can load .txt files into a table like ESSAYS(AUTHOR varchar(255), FILENAME varchar(255), [Content] TEXT) using For Each Loop Container control.

Then use Fuzzy Lookup comparing by Content field, using same ESSAYS table as base and as Lookup table.

You can play with Similarity Threshold there.

Fuzzy Lookup operator will produce an output for each row of base table where Similarity and Confidence columns will be. Just spool it into another table.

good luck,

Mark

multiple text files mining

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

1.I should use a SSIS solution using "Import Column transform" to get the files and store in a column with varbinary(max) data type; if you want to store only text in a column you can use varchar(max) data type (you can store maximum 2 GB)

2.Using SSIS solution I told there is a way to matching file (use "for each loop container")

3.For my ideea you don't to do that; you can run periodically the package created with SSIS in a job , depends on your business logic

4.Study the tutorial from here if you want create a text mining project. The ideea if you want to know let say the frequence of the terms /phrases, extracting clustering terms/concepts from the docs.

|||

does SSIS have a readymade package to compare mulitple files? if yes how does one go about it? thanx man !!!

|||

what did ou mean "compare multiple files"?

using for each loop container ou can select *.txt or *.doc files or have a special name.

if mean comparing contents of files i think you can use script task when you can customize this comparison using .net (as you should did it using .NET)

|||

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

|||

vickwal wrote:

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

You can use MS Integration Service.

I think you have-to convert (presumably MS Word) .doc format into plain text .txt.

Then you can load .txt files into a table like ESSAYS(AUTHOR varchar(255), FILENAME varchar(255), [Content] TEXT) using For Each Loop Container control.

Then use Fuzzy Lookup comparing by Content field, using same ESSAYS table as base and as Lookup table.

You can play with Similarity Threshold there.

Fuzzy Lookup operator will produce an output for each row of base table where Similarity and Confidence columns will be. Just spool it into another table.

good luck,

Mark

multiple text docs evaluation

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

What is the purpose of this? If you want to discriminate against files, that should be done before saving them. If you need a difference analyzer, I suggest you look how subversion, cvs or any other source control systems handles and saves the differences.|||

the purpose is:

say i store two text documents uploaded to SQL Server from a web portal. Now i want SQL Server to determine that how much percent the content of these two files matches? Say if I want that the files shouldn't match more than 60%, and if they do they should be discarded. thanks man, help appreciated !!!

|||That sounds like a trigger might be able to do that processing during a table load/update. I will move this post to the DB forum for advice.|||thanx man! I was wondering if SQL Server 05 had some SSIP analytic readymade service do that?

multiple text docs evaluation

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

What is the purpose of this? If you want to discriminate against files, that should be done before saving them. If you need a difference analyzer, I suggest you look how subversion, cvs or any other source control systems handles and saves the differences.|||

the purpose is:

say i store two text documents uploaded to SQL Server from a web portal. Now i want SQL Server to determine that how much percent the content of these two files matches? Say if I want that the files shouldn't match more than 60%, and if they do they should be discarded. thanks man, help appreciated !!!

|||That sounds like a trigger might be able to do that processing during a table load/update. I will move this post to the DB forum for advice.|||thanx man! I was wondering if SQL Server 05 had some SSIP analytic readymade service do that?

multiple text docs evaluation

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

What is the purpose of this? If you want to discriminate against files, that should be done before saving them. If you need a difference analyzer, I suggest you look how subversion, cvs or any other source control systems handles and saves the differences.|||

the purpose is:

say i store two text documents uploaded to SQL Server from a web portal. Now i want SQL Server to determine that how much percent the content of these two files matches? Say if I want that the files shouldn't match more than 60%, and if they do they should be discarded. thanks man, help appreciated !!!

|||That sounds like a trigger might be able to do that processing during a table load/update. I will move this post to the DB forum for advice.|||thanx man! I was wondering if SQL Server 05 had some SSIP analytic readymade service do that?

Multiple tempdb files to no. of CPUs in SQL 2005

I know in SQL 2000, it was recommended to have the same number of tempdb
files as the number of CPUs.
Is it still the case for SQL 2005 ?Hassan
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
I think it depends on what your application does againts sql server
"Hassan" <hassan@.hotmail.com> wrote in message
news:e2R59sQyHHA.1208@.TK2MSFTNGP05.phx.gbl...
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
> Is it still the case for SQL 2005 ?
>|||On Wed, 18 Jul 2007 00:14:10 -0700, "Hassan" <hassan@.hotmail.com>
wrote:
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
>Is it still the case for SQL 2005 ?
According to Books On Line (BOL, also known as The Documentation) the
answer is Yes.
"Create as many files as needed to maximize disk bandwidth. Using
multiple files reduces tempdb storage contention and yields
significantly better scalability. However, do not create too many
files because this can reduce performance and increase management
overhead. As a general guideline, create one data file for each CPU on
the server (accounting for any affinity mask settings) and then adjust
the number of files up or down as necessary. Note that a dual-core CPU
is considered to be two CPUs."
Roy Harvey
Beacon Falls, CT

Multiple tempdb files to no. of CPUs in SQL 2005

I know in SQL 2000, it was recommended to have the same number of tempdb
files as the number of CPUs.
Is it still the case for SQL 2005 ?Hassan
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
I think it depends on what your application does againts sql server
"Hassan" <hassan@.hotmail.com> wrote in message
news:e2R59sQyHHA.1208@.TK2MSFTNGP05.phx.gbl...
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
> Is it still the case for SQL 2005 ?
>|||On Wed, 18 Jul 2007 00:14:10 -0700, "Hassan" <hassan@.hotmail.com>
wrote:

>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
>Is it still the case for SQL 2005 ?
According to Books On Line (BOL, also known as The Documentation) the
answer is Yes.
"Create as many files as needed to maximize disk bandwidth. Using
multiple files reduces tempdb storage contention and yields
significantly better scalability. However, do not create too many
files because this can reduce performance and increase management
overhead. As a general guideline, create one data file for each CPU on
the server (accounting for any affinity mask settings) and then adjust
the number of files up or down as necessary. Note that a dual-core CPU
is considered to be two CPUs."
Roy Harvey
Beacon Falls, CT

Multiple tempdb files to no. of CPUs in SQL 2005

I know in SQL 2000, it was recommended to have the same number of tempdb
files as the number of CPUs.
Is it still the case for SQL 2005 ?
Hassan
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
I think it depends on what your application does againts sql server
"Hassan" <hassan@.hotmail.com> wrote in message
news:e2R59sQyHHA.1208@.TK2MSFTNGP05.phx.gbl...
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
> Is it still the case for SQL 2005 ?
>
|||On Wed, 18 Jul 2007 00:14:10 -0700, "Hassan" <hassan@.hotmail.com>
wrote:

>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
>Is it still the case for SQL 2005 ?
According to Books On Line (BOL, also known as The Documentation) the
answer is Yes.
"Create as many files as needed to maximize disk bandwidth. Using
multiple files reduces tempdb storage contention and yields
significantly better scalability. However, do not create too many
files because this can reduce performance and increase management
overhead. As a general guideline, create one data file for each CPU on
the server (accounting for any affinity mask settings) and then adjust
the number of files up or down as necessary. Note that a dual-core CPU
is considered to be two CPUs."
Roy Harvey
Beacon Falls, CT

Multiple TempDB files for Multi_CPU

Hi all,
I have a clustered sql server 2005 64 bit on a SAN. The two nodes each
have quad dual core XEON CPU's. TempDB is currently on a dedicated RAID 1
array in the SAN (two 72GB 15K rpm discs). I have read that it is wise to
have as many files for tempDB as I have CPU's - ie, eight.
I have a few questions about this:
1. I am assuming that these files are created in SSMS by right-clicking
tempdb and going to the Properties\Files tab.
--Should I add one tempdev_n and one templog_n for each CPU, or just the
tempdev file (the mdf)?
--Does it make sense to leave all of the new tempdev_n files in the
PRIMARY filegroup (same as Partition?), or should I create a new filegroup
for each tempdev_n instance?
2. Will performance actually have the potential to improve if all of these
tempdev_n files are on the same RAID 1 disc array? Does anyone actually
spread these files to dedicated discs per instance?
Thanks. These questions probably expose my experience level (rank amateur),
but that's why I am asking them. I appreciate any help.
JT
--
JohnHi
Increasing the number of data files will reduce the contention in SGAM and
PFS pages in tempdb. This is described in
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
Also check out
http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc
This is more likely to occur if your application uses an excessive number of
temporary table or other features that require tempdb.
You do not have to increase the number of log files. You can use management
studio or use the ALTER DATABASE command to increase the number of files.
Also make sure that the initial size of the tempdb files are large enough for
normal use so you avoid it having to grow.
John
"JT" wrote:
> Hi all,
> I have a clustered sql server 2005 64 bit on a SAN. The two nodes each
> have quad dual core XEON CPU's. TempDB is currently on a dedicated RAID 1
> array in the SAN (two 72GB 15K rpm discs). I have read that it is wise to
> have as many files for tempDB as I have CPU's - ie, eight.
> I have a few questions about this:
> 1. I am assuming that these files are created in SSMS by right-clicking
> tempdb and going to the Properties\Files tab.
> --Should I add one tempdev_n and one templog_n for each CPU, or just the
> tempdev file (the mdf)?
> --Does it make sense to leave all of the new tempdev_n files in the
> PRIMARY filegroup (same as Partition?), or should I create a new filegroup
> for each tempdev_n instance?
> 2. Will performance actually have the potential to improve if all of these
> tempdev_n files are on the same RAID 1 disc array? Does anyone actually
> spread these files to dedicated discs per instance?
> Thanks. These questions probably expose my experience level (rank amateur),
> but that's why I am asking them. I appreciate any help.
> JT
> --
> John|||Hello John,
I agree with john that it is necessary if you do have load that requres big
tempdb. Also, setting the database size to a large value is suggested.
For SQL Server 2005 it is recommended that you create tempdb files striped
across fast disks. Since you have raid1 disk, the performance improvement
may not as much as you expect though it should improve performance when the
problem is caused by tempdb. Generally, you may want to monitor the system
by using performon as described in the articles recommended by John and add
files when necessary. Also, you may want to configure trace flag 1118 to
eliminate mixed extent allocations. Please see the following article for
more details:
328551 FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
If you have further questions or concerns on the issue, please feel free to
let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Peter and John. One follow-on question...
Given current resource restrictions, I have two FC disks to devote to the
tempdb for this SQL instance on the SAN. For reliability sake, I choose to
go with RAID 1 config (although the other instance mdf's are on a RAID 1+0
array). Would it make sense to go with RAID 0? What would happen if one of
the RAID 0 discs supporting tempdb (and ONLY tempdb) blew up?
Thanks.
--
John
"Peter Yang [MSFT]" wrote:
> Hello John,
> I agree with john that it is necessary if you do have load that requres big
> tempdb. Also, setting the database size to a large value is suggested.
> For SQL Server 2005 it is recommended that you create tempdb files striped
> across fast disks. Since you have raid1 disk, the performance improvement
> may not as much as you expect though it should improve performance when the
> problem is caused by tempdb. Generally, you may want to monitor the system
> by using performon as described in the articles recommended by John and add
> files when necessary. Also, you may want to configure trace flag 1118 to
> eliminate mixed extent allocations. Please see the following article for
> more details:
> 328551 FIX: Concurrency enhancements for the tempdb database
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
> If you have further questions or concerns on the issue, please feel free to
> let's know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi
With Raid 0 you would have some down, while you either added a new disc or
reconfigured tempdb (I think SQL Server would not restart until you did!) .
The database should roll back any usage of tempdb when the system stopped so
your database should remain consistent but you would need to reapply the
transactions that were in progress.
John
"JT" wrote:
> Thank you Peter and John. One follow-on question...
> Given current resource restrictions, I have two FC disks to devote to the
> tempdb for this SQL instance on the SAN. For reliability sake, I choose to
> go with RAID 1 config (although the other instance mdf's are on a RAID 1+0
> array). Would it make sense to go with RAID 0? What would happen if one of
> the RAID 0 discs supporting tempdb (and ONLY tempdb) blew up?
> Thanks.
> --
> John
>
> "Peter Yang [MSFT]" wrote:
> > Hello John,
> >
> > I agree with john that it is necessary if you do have load that requres big
> > tempdb. Also, setting the database size to a large value is suggested.
> >
> > For SQL Server 2005 it is recommended that you create tempdb files striped
> > across fast disks. Since you have raid1 disk, the performance improvement
> > may not as much as you expect though it should improve performance when the
> > problem is caused by tempdb. Generally, you may want to monitor the system
> > by using performon as described in the articles recommended by John and add
> > files when necessary. Also, you may want to configure trace flag 1118 to
> > eliminate mixed extent allocations. Please see the following article for
> > more details:
> >
> > 328551 FIX: Concurrency enhancements for the tempdb database
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
> >
> > If you have further questions or concerns on the issue, please feel free to
> > let's know. Thank you.
> >
> > Best Regards,
> >
> > Peter Yang
> > MCSE2000/2003, MCSA, MCDBA
> > Microsoft Online Community Support
> > ==================================================> > Get notification to my posts through email? Please refer to
> > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> > ications
> > <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> > where an initial response from the community or a Microsoft Support
> > Engineer within 1 business day is acceptable. Please note that each follow
> > up response may take approximately 2 business days as the support
> > professional working with you may need further investigation to reach the
> > most efficient resolution. The offering is not appropriate for situations
> > that require urgent, real-time or phone-based interactions or complex
> > project analysis and dump analysis issues. Issues of this nature are best
> > handled working with a dedicated Microsoft Support Engineer by contacting
> > Microsoft Customer Support Services (CSS) at
> > <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> > ==================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||Hello John,
As John mentioned, raid 0 disk failure of tempdb shall not bring data
consistency issue. However, transactions related to tempdb will roll back
and you shall reapply these transactions.
Usually it is suggested that you use RAID 1 or RAID 1+0, not RAID 0 for
tempdb, unless you are prepared to lose SQL Server access should either
drive fail in a RAID 0 striped array.
Please see:
http://www.microsoft.com/sql/prodinfo/previousversions/scalabilityfaq.mspx
and the section on tempdb
http://blogs.msdn.com/sqlcat/archive/2005/11/21/495440.aspx
917047 Microsoft SQL Server I/O subsystem requirements for the tempdb
database
http://support.microsoft.com/default.aspx?scid=kb;EN-US;917047
If you have further questions, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||"any usage of tempdb" does not read well! Try substituting "any uncommitted
transactions in progress"
John
"John Bell" wrote:
> Hi
> With Raid 0 you would have some down, while you either added a new disc or
> reconfigured tempdb (I think SQL Server would not restart until you did!) .
> The database should roll back any usage of tempdb when the system stopped so
> your database should remain consistent but you would need to reapply the
> transactions that were in progress.
> John
> "JT" wrote:
> > Thank you Peter and John. One follow-on question...
> > Given current resource restrictions, I have two FC disks to devote to the
> > tempdb for this SQL instance on the SAN. For reliability sake, I choose to
> > go with RAID 1 config (although the other instance mdf's are on a RAID 1+0
> > array). Would it make sense to go with RAID 0? What would happen if one of
> > the RAID 0 discs supporting tempdb (and ONLY tempdb) blew up?
> >
> > Thanks.
> > --
> > John
> >
> >
> > "Peter Yang [MSFT]" wrote:
> >
> > > Hello John,
> > >
> > > I agree with john that it is necessary if you do have load that requres big
> > > tempdb. Also, setting the database size to a large value is suggested.
> > >
> > > For SQL Server 2005 it is recommended that you create tempdb files striped
> > > across fast disks. Since you have raid1 disk, the performance improvement
> > > may not as much as you expect though it should improve performance when the
> > > problem is caused by tempdb. Generally, you may want to monitor the system
> > > by using performon as described in the articles recommended by John and add
> > > files when necessary. Also, you may want to configure trace flag 1118 to
> > > eliminate mixed extent allocations. Please see the following article for
> > > more details:
> > >
> > > 328551 FIX: Concurrency enhancements for the tempdb database
> > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
> > >
> > > If you have further questions or concerns on the issue, please feel free to
> > > let's know. Thank you.
> > >
> > > Best Regards,
> > >
> > > Peter Yang
> > > MCSE2000/2003, MCSA, MCDBA
> > > Microsoft Online Community Support
> > > ==================================================> > > Get notification to my posts through email? Please refer to
> > > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> > > ications
> > > <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> > > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> > > where an initial response from the community or a Microsoft Support
> > > Engineer within 1 business day is acceptable. Please note that each follow
> > > up response may take approximately 2 business days as the support
> > > professional working with you may need further investigation to reach the
> > > most efficient resolution. The offering is not appropriate for situations
> > > that require urgent, real-time or phone-based interactions or complex
> > > project analysis and dump analysis issues. Issues of this nature are best
> > > handled working with a dedicated Microsoft Support Engineer by contacting
> > > Microsoft Customer Support Services (CSS) at
> > > <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> > > ==================================================> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >|||Hello John,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

multiple tempdb files and large operation in tempdb.

Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advanceIt will expanded the files in a round robin fashion until its needs are met
or it errors out due to insufficient space.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance|||Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:
> It will expanded the files in a round robin fashion until its needs are met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>|||Yep. Basically, it will try to expand the first file and if that isn't
enough - even for just that txn - it will continue to the next one and so
on.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9A464240-AAD6-4871-8652-6445A7833C28@.microsoft.com...
Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:
> It will expanded the files in a round robin fashion until its needs are
met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>

multiple tempdb files and large operation in tempdb.

Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance
It will expanded the files in a round robin fashion until its needs are met
or it errors out due to insufficient space.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance
|||Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:

> It will expanded the files in a round robin fashion until its needs are met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>
|||Yep. Basically, it will try to expand the first file and if that isn't
enough - even for just that txn - it will continue to the next one and so
on.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9A464240-AAD6-4871-8652-6445A7833C28@.microsoft.com...
Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:

> It will expanded the files in a round robin fashion until its needs are
met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>

multiple tempdb filegroup

If I want to try the multiple tempdb file trick (have number of files equal
to number of processors), is it better if they are each a separate filegroup,
or is it better (or required!) if they are all members of a single filegroup?
Also, while the kb article recommends using fixed-size and equal-sized
files, it seems to me that is should not really matter, if the algorithm is
to try to use the largest file first, it will quickly become not the largest
and we will get pretty much the same round-robin as the much more limited
configurations would see.
Anyone like to comment on the performance improvements, if any, likely?
This is mostly sequential batch processing, but with fair millions of
records, and a lot more use of group-by, mostly on current 2 * dualcore
systems, and doesn't actually have a lot of tempdb contention that I've
noticed, though we'd like to see better
performance in any case.
Thanks.
Josh
If you do use multiple filegroups, you would be using a very unusually and
uncommon tempdb configuration. My suggestion is, forget about multiple
filegroups. What matters is the number of data files.
I have not done any performance tests myself on the impact of the number of
the tempdb data files, but I've done tests on the performance impact of the
number of data files in a user database, and there is significant perfromance
impact if there is significant allocation activity.
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-should-i-create-for-a-user-database.aspx
Since tempdb by its very nature is allocation intensive, I would not
question the impact of the recommend configuration on the tempdb performance
(and therefore the SQL Server performance).
Linchi
"JRStern" wrote:

> If I want to try the multiple tempdb file trick (have number of files equal
> to number of processors), is it better if they are each a separate filegroup,
> or is it better (or required!) if they are all members of a single filegroup?
> Also, while the kb article recommends using fixed-size and equal-sized
> files, it seems to me that is should not really matter, if the algorithm is
> to try to use the largest file first, it will quickly become not the largest
> and we will get pretty much the same round-robin as the much more limited
> configurations would see.
>
> Anyone like to comment on the performance improvements, if any, likely?
> This is mostly sequential batch processing, but with fair millions of
> records, and a lot more use of group-by, mostly on current 2 * dualcore
> systems, and doesn't actually have a lot of tempdb contention that I've
> noticed, though we'd like to see better
> performance in any case.
> Thanks.
> Josh

multiple tempdb filegroup

If I want to try the multiple tempdb file trick (have number of files equal
to number of processors), is it better if they are each a separate filegroup
,
or is it better (or required!) if they are all members of a single filegroup
?
Also, while the kb article recommends using fixed-size and equal-sized
files, it seems to me that is should not really matter, if the algorithm is
to try to use the largest file first, it will quickly become not the largest
and we will get pretty much the same round-robin as the much more limited
configurations would see.
Anyone like to comment on the performance improvements, if any, likely?
This is mostly sequential batch processing, but with fair millions of
records, and a lot more use of group-by, mostly on current 2 * dualcore
systems, and doesn't actually have a lot of tempdb contention that I've
noticed, though we'd like to see better
performance in any case.
Thanks.
JoshIf you do use multiple filegroups, you would be using a very unusually and
uncommon tempdb configuration. My suggestion is, forget about multiple
filegroups. What matters is the number of data files.
I have not done any performance tests myself on the impact of the number of
the tempdb data files, but I've done tests on the performance impact of the
number of data files in a user database, and there is significant perfromanc
e
impact if there is significant allocation activity.
http://sqlblog.com/blogs/linchi_she...r-database.aspx
Since tempdb by its very nature is allocation intensive, I would not
question the impact of the recommend configuration on the tempdb performance
(and therefore the SQL Server performance).
Linchi
"JRStern" wrote:

> If I want to try the multiple tempdb file trick (have number of files equa
l
> to number of processors), is it better if they are each a separate filegro
up,
> or is it better (or required!) if they are all members of a single filegro
up?
> Also, while the kb article recommends using fixed-size and equal-sized
> files, it seems to me that is should not really matter, if the algorithm i
s
> to try to use the largest file first, it will quickly become not the large
st
> and we will get pretty much the same round-robin as the much more limited
> configurations would see.
>
> Anyone like to comment on the performance improvements, if any, likely?
> This is mostly sequential batch processing, but with fair millions of
> records, and a lot more use of group-by, mostly on current 2 * dualcore
> systems, and doesn't actually have a lot of tempdb contention that I've
> noticed, though we'd like to see better
> performance in any case.
> Thanks.
> Josh

multiple tempdb filegroup

If I want to try the multiple tempdb file trick (have number of files equal
to number of processors), is it better if they are each a separate filegroup,
or is it better (or required!) if they are all members of a single filegroup?
Also, while the kb article recommends using fixed-size and equal-sized
files, it seems to me that is should not really matter, if the algorithm is
to try to use the largest file first, it will quickly become not the largest
and we will get pretty much the same round-robin as the much more limited
configurations would see.
Anyone like to comment on the performance improvements, if any, likely?
This is mostly sequential batch processing, but with fair millions of
records, and a lot more use of group-by, mostly on current 2 * dualcore
systems, and doesn't actually have a lot of tempdb contention that I've
noticed, though we'd like to see better
performance in any case.
Thanks.
JoshIf you do use multiple filegroups, you would be using a very unusually and
uncommon tempdb configuration. My suggestion is, forget about multiple
filegroups. What matters is the number of data files.
I have not done any performance tests myself on the impact of the number of
the tempdb data files, but I've done tests on the performance impact of the
number of data files in a user database, and there is significant perfromance
impact if there is significant allocation activity.
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-should-i-create-for-a-user-database.aspx
Since tempdb by its very nature is allocation intensive, I would not
question the impact of the recommend configuration on the tempdb performance
(and therefore the SQL Server performance).
Linchi
"JRStern" wrote:
> If I want to try the multiple tempdb file trick (have number of files equal
> to number of processors), is it better if they are each a separate filegroup,
> or is it better (or required!) if they are all members of a single filegroup?
> Also, while the kb article recommends using fixed-size and equal-sized
> files, it seems to me that is should not really matter, if the algorithm is
> to try to use the largest file first, it will quickly become not the largest
> and we will get pretty much the same round-robin as the much more limited
> configurations would see.
>
> Anyone like to comment on the performance improvements, if any, likely?
> This is mostly sequential batch processing, but with fair millions of
> records, and a lot more use of group-by, mostly on current 2 * dualcore
> systems, and doesn't actually have a lot of tempdb contention that I've
> noticed, though we'd like to see better
> performance in any case.
> Thanks.
> Josh