1. Is there any benifit to creating a distinct index file for each column to be indexe
Index file 1 - Field
Index File 2 - Field
Index file 3 - Field
Index file 4 - Field
2. Is there a benifit to creating multiple groups of indexes ie
Index file 1 - Field A, Field B, Filed
Index File 2 - Field B, Field
Index file 3 - Field B, FieldHi Mark.
There are benefits / limitations in taking either approach.
Multi-column indexes may "cover" more queries or join predicates than single
column queries and provide better performance that way.
Distinct indexes provide compact index width for fast join or seek
operations where query predicates or filter arguments are on those columns.
SQL Server can also intersect indexes during optimization which recovers
some of the benefits that would have come from multi-column indexes.
If your application is relatively read only (data mart / warehouse) & not
bound by disk space limitations you might find that the more indexes you
have the better, as there are simply more options for the optimizer to suit
more queries.
If your application is more of an online transaction processinng
application, you probably want to limit which indexes you add as each index
needs updating as rows are inserted, updated & deleted.
The Index Tuning Wizard can be helpful in determining which indexes are the
best for the database & queries you're using.
HTH
Regards,
Greg Linwood
SQL Server MVP
"MarkFirth" <anonymous@.discussions.microsoft.com> wrote in message
news:406C0C47-5B5C-45EF-93C2-DD721278F61F@.microsoft.com...
> 1. Is there any benifit to creating a distinct index file for each column
to be indexed
> Index file 1 - Field A
> Index File 2 - Field B
> Index file 3 - Field B
> Index file 4 - Field H
> 2. Is there a benifit to creating multiple groups of indexes ie:
> Index file 1 - Field A, Field B, Filed C
> Index File 2 - Field B, Field C
> Index file 3 - Field B, Field H
>|||Mark
It depends on what are you going to query and how.
If you issue SELECT Field A, Field B, Filed C FROM
SomeTable ORDER BY Field A, Field B, Filed C or
WHERE clause
so having covering index on these columns will gain perfomance improving.
"MarkFirth" <anonymous@.discussions.microsoft.com> wrote in message
news:406C0C47-5B5C-45EF-93C2-DD721278F61F@.microsoft.com...
> 1. Is there any benifit to creating a distinct index file for each column
to be indexed
> Index file 1 - Field A
> Index File 2 - Field B
> Index file 3 - Field B
> Index file 4 - Field H
> 2. Is there a benifit to creating multiple groups of indexes ie:
> Index file 1 - Field A, Field B, Filed C
> Index File 2 - Field B, Field C
> Index file 3 - Field B, Field H
>sql
Showing posts with label files. Show all posts
Showing posts with label files. Show all posts
Friday, March 23, 2012
Multiple FTP tasks that connect to same server at same time error
I have this job that download 4 files once a month from the same server. The files are sizable and I need to download them in less than 5 hours total. In 2000 I use an active x script to generate the ftp script then execute the script. all four files download at the same time in 4 different tasks with no issues.
I am rewrote the process in 2005 so that it uses the IS FTP function but when all 4 ftp tasks kick off they all fail... instantly. the initially shared the same FTP connection manager so I created different ones for each and still the same result
the error is one that relates to changing directories.... Now if I just run one of the tasks it runs fine it is just when more than one try to run at once. I ended up putting in 10 second delays between each ftp task kicking off and it works just fine...
Does this sound like a bug?
Also... I am on SQL 2005 Enterprise SP1 on Windows 2003 enterprise SP1.
I can also reproduce the problem when multiple FTP tasks share the same FTP connection manager. But everything works fine if I create individual FTP connection manager for each task. This does sound like a bug to me so I am going to log a bug issue. We will investigate the problem and address it ASAP. Thanks you|||Thanks...Multiple flat files to multiple tables
Hi,
I have searched but not found quite the best way to look at this so far..
I have an application that outputs data to several text files (up to 30). These have commonality by an object name, but then contain completely different column data.
In DTS I had each of the source text file connections going to one OLE DB connection and then individual transform data tasks pointing to the one OLE DB connection.
Looking at SSIS, it would appear that I would need to have one source and one destination for each of these and therefore 30 parallel data flows?
Just wondering if there is a neater way of doing this?
It is a regular data import that happens a few times a day - the text files are named the same as the SQL tables - ie app_userdata.txt goes to app_userdata table.
Hope that explains ok and thanks in advance.
Mike
Since each source file and each destination table has its own schema, I don't know of any better way than what you describe.
Personally I would rather see 30 packages, each with one data flow, rather than one package with 30 data flows, but that's largely a personal preference, not a hard and fast rule or anything...
|||Thanks for the response - hadn't considered a package for each so will take a look at that|||The advantage that I see in the "one package per destination table" approach is that it makes ongoing maintenance much simpler. For smaller projects it seems like overkill, but it serves me well...Multiple flat file import to corresponding SQL server tables
We are trying to use SSIS Import export wizrd to import the flat files (CSV format) that we have into MS SQL Server 2005 database tables. We have huge number of CSV files. Is there a way by which we can import these flat (CSV) files in to corresponding SQL server tables in a single shot. I would really appreciate this help as it is painful to convert each and every file using the Import Export wizard.
Thank you very much,
Regards,
Srinivas Raveendra
I am also facing the same problem.|||The simplest way I can see that is to create a package per every file-table destination pair. Each package could have a ForEach loop to process all files that have the same structure.|||The way we have accomplished this task is to have a table that holds the names of all the files and the names of the tables each file is loaded into.
The SSIS package reads all the rows from this table and runs a foreach loop container for each record; The looping process firstly truncates the staging table and then bulk inserts based on the variable values for each iteration of the loop container. the syntax for the Bulk Insert would look something like this:
EXEC('BULK INSERT ' + @.strTable + ' FROM ' + @.strFileDir + @.strFile + '.csv''')
If the files you are importing are not exactly the same format as the tables you are importing them into. ie the columns are in a different order or there are more/less columns in the file than in the table, you will need to have format files set up and the syntax with the format file would look something like this:
EXEC('BULK INSERT ' + @.strTable + ' FROM ' + @.strFileDir + @.strFile + '.csv'' WITH (FORMATFILE=''' + @.strFormatDir + @.strFormat + '.fmt'')')
As a note we have the variables @.strFileDir and @.strFormatDir set at the package level, and dynamically controlled through a package configuration.
For more information on Bulk Insert see Books Online: http://msdn2.microsoft.com/en-us/library/ms188365.aspx
Hopefully this helps.
sqlMultiple files/filegroups
Hi,
We have a database which has reached our disk capacity of 340GB and we
are looking at our options on how to resolve the disk space issue. The
problem is I'm not too familiar with setting up additional filegroups
and/or secondary files, or how they function.
The database consists of only one table which stores all our product
hi-res photography, each image being about 20MBs. The table is not
heavily accessed - either read or write - but it is our central storage
location for our studio photographs.
We could delete all images for discontinued product, but we would then
have to rely on a tape backup if we needed access to them in the
future.
Other options I think we have are to: -
1. Create a new database on a separate disk and have a scheduled
archive task to move any discontinued product images to a table in this
database. Place this DB on a separate disk. This means our 'live' db
would be much smaller and quicker to backup
2. Create a new filegroup for existing DB and create an archive table
on this group (file on different disk). Archive periodically and use a
view to join the two tables. Only perform regular database backups on
the 'live' filegroup
3. Create a secondary file on the same filegroup (primary) as the
'live' table and let the system start to fill this up with data as new
records are added. Downside of this is the backup size will not reduce.
On point 3. I'm not sure how this works - will the primary file start
to reduce in size, or will it remain at capacity and the secondary file
will start to increase in size? Is there something I can do to start
balancing the file sizes?
Also would be interested to know opinions on what the best approach
would be and if I have any misconceptions about the above options (or
if I've missed any)!
Thanks,
Andrew Duncan
Hi Tibor,
Thanks for the advice. Option 1 does seem the simplest, so I'll be
opting for that.
Rgds,
Andrew
Tibor Karaszi wrote:[vbcol=seagreen]
> Some thoughts:
>
> Sounds like a lot of manual work.
>
> This is probably what I would look for, but it require that your code adapts for this.
>
> You still need to adapt your code. But you now have a more complex backup strategy compared to 1. If
> you are on 2005, you can archive, set file group to read-only, backup the filegroup. If you now
> restore that filegroup backup, you don't need to restore all subsequent log backup. In 2000, you
> *do* need to restore all subsequent log backups, even if the file group was in read-only since the
> file group backup was performed.
>
> Space will be used from the file according to free space on each file. So you want to pre-allocate
> file size for the new file, and then shrink the old file to push data over to the new file (and then
> possibly increase the old file again so you have some free space).
> I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all of the data when you
> restore but backup/restore is a bit more complex. 2 doesn't really buy you anything if you are on
> 2000.
> If you go for 1, you can get data from different point in time when you restore (as you have two
> databases).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <andrew@.duncanhcmc.com> wrote in message
> news:1163749909.367437.223570@.e3g2000cwe.googlegro ups.com...
sql
We have a database which has reached our disk capacity of 340GB and we
are looking at our options on how to resolve the disk space issue. The
problem is I'm not too familiar with setting up additional filegroups
and/or secondary files, or how they function.
The database consists of only one table which stores all our product
hi-res photography, each image being about 20MBs. The table is not
heavily accessed - either read or write - but it is our central storage
location for our studio photographs.
We could delete all images for discontinued product, but we would then
have to rely on a tape backup if we needed access to them in the
future.
Other options I think we have are to: -
1. Create a new database on a separate disk and have a scheduled
archive task to move any discontinued product images to a table in this
database. Place this DB on a separate disk. This means our 'live' db
would be much smaller and quicker to backup
2. Create a new filegroup for existing DB and create an archive table
on this group (file on different disk). Archive periodically and use a
view to join the two tables. Only perform regular database backups on
the 'live' filegroup
3. Create a secondary file on the same filegroup (primary) as the
'live' table and let the system start to fill this up with data as new
records are added. Downside of this is the backup size will not reduce.
On point 3. I'm not sure how this works - will the primary file start
to reduce in size, or will it remain at capacity and the secondary file
will start to increase in size? Is there something I can do to start
balancing the file sizes?
Also would be interested to know opinions on what the best approach
would be and if I have any misconceptions about the above options (or
if I've missed any)!
Thanks,
Andrew Duncan
Hi Tibor,
Thanks for the advice. Option 1 does seem the simplest, so I'll be
opting for that.
Rgds,
Andrew
Tibor Karaszi wrote:[vbcol=seagreen]
> Some thoughts:
>
> Sounds like a lot of manual work.
>
> This is probably what I would look for, but it require that your code adapts for this.
>
> You still need to adapt your code. But you now have a more complex backup strategy compared to 1. If
> you are on 2005, you can archive, set file group to read-only, backup the filegroup. If you now
> restore that filegroup backup, you don't need to restore all subsequent log backup. In 2000, you
> *do* need to restore all subsequent log backups, even if the file group was in read-only since the
> file group backup was performed.
>
> Space will be used from the file according to free space on each file. So you want to pre-allocate
> file size for the new file, and then shrink the old file to push data over to the new file (and then
> possibly increase the old file again so you have some free space).
> I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all of the data when you
> restore but backup/restore is a bit more complex. 2 doesn't really buy you anything if you are on
> 2000.
> If you go for 1, you can get data from different point in time when you restore (as you have two
> databases).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <andrew@.duncanhcmc.com> wrote in message
> news:1163749909.367437.223570@.e3g2000cwe.googlegro ups.com...
sql
Multiple files/filegroups
Hi,
We have a database which has reached our disk capacity of 340GB and we
are looking at our options on how to resolve the disk space issue. The
problem is I'm not too familiar with setting up additional filegroups
and/or secondary files, or how they function.
The database consists of only one table which stores all our product
hi-res photography, each image being about 20MBs. The table is not
heavily accessed - either read or write - but it is our central storage
location for our studio photographs.
We could delete all images for discontinued product, but we would then
have to rely on a tape backup if we needed access to them in the
future.
Other options I think we have are to: -
1. Create a new database on a separate disk and have a scheduled
archive task to move any discontinued product images to a table in this
database. Place this DB on a separate disk. This means our 'live' db
would be much smaller and quicker to backup
2. Create a new filegroup for existing DB and create an archive table
on this group (file on different disk). Archive periodically and use a
view to join the two tables. Only perform regular database backups on
the 'live' filegroup
3. Create a secondary file on the same filegroup (primary) as the
'live' table and let the system start to fill this up with data as new
records are added. Downside of this is the backup size will not reduce.
On point 3. I'm not sure how this works - will the primary file start
to reduce in size, or will it remain at capacity and the secondary file
will start to increase in size? Is there something I can do to start
balancing the file sizes?
Also would be interested to know opinions on what the best approach
would be and if I have any misconceptions about the above options (or
if I've missed any)!
Thanks,
Andrew DuncanSome thoughts:
> We could delete all images for discontinued product, but we would then
> have to rely on a tape backup if we needed access to them in the
> future.
Sounds like a lot of manual work.
> 1. Create a new database on a separate disk and have a scheduled
> archive task to move any discontinued product images to a table in this
> database. Place this DB on a separate disk. This means our 'live' db
> would be much smaller and quicker to backup
This is probably what I would look for, but it require that your code adapts for this.
> 2. Create a new filegroup for existing DB and create an archive table
> on this group (file on different disk). Archive periodically and use a
> view to join the two tables. Only perform regular database backups on
> the 'live' filegroup
You still need to adapt your code. But you now have a more complex backup strategy compared to 1. If
you are on 2005, you can archive, set file group to read-only, backup the filegroup. If you now
restore that filegroup backup, you don't need to restore all subsequent log backup. In 2000, you
*do* need to restore all subsequent log backups, even if the file group was in read-only since the
file group backup was performed.
> 3. Create a secondary file on the same filegroup (primary) as the
> 'live' table and let the system start to fill this up with data as new
> records are added. Downside of this is the backup size will not reduce.
> On point 3. I'm not sure how this works - will the primary file start
> to reduce in size, or will it remain at capacity and the secondary file
> will start to increase in size? Is there something I can do to start
> balancing the file sizes?
Space will be used from the file according to free space on each file. So you want to pre-allocate
file size for the new file, and then shrink the old file to push data over to the new file (and then
possibly increase the old file again so you have some free space).
I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all of the data when you
restore but backup/restore is a bit more complex. 2 doesn't really buy you anything if you are on
2000.
If you go for 1, you can get data from different point in time when you restore (as you have two
databases).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<andrew@.duncanhcmc.com> wrote in message
news:1163749909.367437.223570@.e3g2000cwe.googlegroups.com...
> Hi,
> We have a database which has reached our disk capacity of 340GB and we
> are looking at our options on how to resolve the disk space issue. The
> problem is I'm not too familiar with setting up additional filegroups
> and/or secondary files, or how they function.
> The database consists of only one table which stores all our product
> hi-res photography, each image being about 20MBs. The table is not
> heavily accessed - either read or write - but it is our central storage
> location for our studio photographs.
> We could delete all images for discontinued product, but we would then
> have to rely on a tape backup if we needed access to them in the
> future.
> Other options I think we have are to: -
> 1. Create a new database on a separate disk and have a scheduled
> archive task to move any discontinued product images to a table in this
> database. Place this DB on a separate disk. This means our 'live' db
> would be much smaller and quicker to backup
> 2. Create a new filegroup for existing DB and create an archive table
> on this group (file on different disk). Archive periodically and use a
> view to join the two tables. Only perform regular database backups on
> the 'live' filegroup
> 3. Create a secondary file on the same filegroup (primary) as the
> 'live' table and let the system start to fill this up with data as new
> records are added. Downside of this is the backup size will not reduce.
> On point 3. I'm not sure how this works - will the primary file start
> to reduce in size, or will it remain at capacity and the secondary file
> will start to increase in size? Is there something I can do to start
> balancing the file sizes?
> Also would be interested to know opinions on what the best approach
> would be and if I have any misconceptions about the above options (or
> if I've missed any)!
> Thanks,
> Andrew Duncan
>|||Hi Tibor,
Thanks for the advice. Option 1 does seem the simplest, so I'll be
opting for that.
Rgds,
Andrew
Tibor Karaszi wrote:
> Some thoughts:
> > We could delete all images for discontinued product, but we would then
> > have to rely on a tape backup if we needed access to them in the
> > future.
> Sounds like a lot of manual work.
>
> > 1. Create a new database on a separate disk and have a scheduled
> > archive task to move any discontinued product images to a table in this
> > database. Place this DB on a separate disk. This means our 'live' db
> > would be much smaller and quicker to backup
> This is probably what I would look for, but it require that your code adapts for this.
>
> > 2. Create a new filegroup for existing DB and create an archive table
> > on this group (file on different disk). Archive periodically and use a
> > view to join the two tables. Only perform regular database backups on
> > the 'live' filegroup
> You still need to adapt your code. But you now have a more complex backup strategy compared to 1. If
> you are on 2005, you can archive, set file group to read-only, backup the filegroup. If you now
> restore that filegroup backup, you don't need to restore all subsequent log backup. In 2000, you
> *do* need to restore all subsequent log backups, even if the file group was in read-only since the
> file group backup was performed.
>
> > 3. Create a secondary file on the same filegroup (primary) as the
> > 'live' table and let the system start to fill this up with data as new
> > records are added. Downside of this is the backup size will not reduce.
> > On point 3. I'm not sure how this works - will the primary file start
> > to reduce in size, or will it remain at capacity and the secondary file
> > will start to increase in size? Is there something I can do to start
> > balancing the file sizes?
> Space will be used from the file according to free space on each file. So you want to pre-allocate
> file size for the new file, and then shrink the old file to push data over to the new file (and then
> possibly increase the old file again so you have some free space).
> I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all of the data when you
> restore but backup/restore is a bit more complex. 2 doesn't really buy you anything if you are on
> 2000.
> If you go for 1, you can get data from different point in time when you restore (as you have two
> databases).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <andrew@.duncanhcmc.com> wrote in message
> news:1163749909.367437.223570@.e3g2000cwe.googlegroups.com...
> > Hi,
> >
> > We have a database which has reached our disk capacity of 340GB and we
> > are looking at our options on how to resolve the disk space issue. The
> > problem is I'm not too familiar with setting up additional filegroups
> > and/or secondary files, or how they function.
> >
> > The database consists of only one table which stores all our product
> > hi-res photography, each image being about 20MBs. The table is not
> > heavily accessed - either read or write - but it is our central storage
> > location for our studio photographs.
> >
> > We could delete all images for discontinued product, but we would then
> > have to rely on a tape backup if we needed access to them in the
> > future.
> >
> > Other options I think we have are to: -
> >
> > 1. Create a new database on a separate disk and have a scheduled
> > archive task to move any discontinued product images to a table in this
> > database. Place this DB on a separate disk. This means our 'live' db
> > would be much smaller and quicker to backup
> >
> > 2. Create a new filegroup for existing DB and create an archive table
> > on this group (file on different disk). Archive periodically and use a
> > view to join the two tables. Only perform regular database backups on
> > the 'live' filegroup
> >
> > 3. Create a secondary file on the same filegroup (primary) as the
> > 'live' table and let the system start to fill this up with data as new
> > records are added. Downside of this is the backup size will not reduce.
> >
> > On point 3. I'm not sure how this works - will the primary file start
> > to reduce in size, or will it remain at capacity and the secondary file
> > will start to increase in size? Is there something I can do to start
> > balancing the file sizes?
> >
> > Also would be interested to know opinions on what the best approach
> > would be and if I have any misconceptions about the above options (or
> > if I've missed any)!
> >
> > Thanks,
> >
> > Andrew Duncan
> >
We have a database which has reached our disk capacity of 340GB and we
are looking at our options on how to resolve the disk space issue. The
problem is I'm not too familiar with setting up additional filegroups
and/or secondary files, or how they function.
The database consists of only one table which stores all our product
hi-res photography, each image being about 20MBs. The table is not
heavily accessed - either read or write - but it is our central storage
location for our studio photographs.
We could delete all images for discontinued product, but we would then
have to rely on a tape backup if we needed access to them in the
future.
Other options I think we have are to: -
1. Create a new database on a separate disk and have a scheduled
archive task to move any discontinued product images to a table in this
database. Place this DB on a separate disk. This means our 'live' db
would be much smaller and quicker to backup
2. Create a new filegroup for existing DB and create an archive table
on this group (file on different disk). Archive periodically and use a
view to join the two tables. Only perform regular database backups on
the 'live' filegroup
3. Create a secondary file on the same filegroup (primary) as the
'live' table and let the system start to fill this up with data as new
records are added. Downside of this is the backup size will not reduce.
On point 3. I'm not sure how this works - will the primary file start
to reduce in size, or will it remain at capacity and the secondary file
will start to increase in size? Is there something I can do to start
balancing the file sizes?
Also would be interested to know opinions on what the best approach
would be and if I have any misconceptions about the above options (or
if I've missed any)!
Thanks,
Andrew DuncanSome thoughts:
> We could delete all images for discontinued product, but we would then
> have to rely on a tape backup if we needed access to them in the
> future.
Sounds like a lot of manual work.
> 1. Create a new database on a separate disk and have a scheduled
> archive task to move any discontinued product images to a table in this
> database. Place this DB on a separate disk. This means our 'live' db
> would be much smaller and quicker to backup
This is probably what I would look for, but it require that your code adapts for this.
> 2. Create a new filegroup for existing DB and create an archive table
> on this group (file on different disk). Archive periodically and use a
> view to join the two tables. Only perform regular database backups on
> the 'live' filegroup
You still need to adapt your code. But you now have a more complex backup strategy compared to 1. If
you are on 2005, you can archive, set file group to read-only, backup the filegroup. If you now
restore that filegroup backup, you don't need to restore all subsequent log backup. In 2000, you
*do* need to restore all subsequent log backups, even if the file group was in read-only since the
file group backup was performed.
> 3. Create a secondary file on the same filegroup (primary) as the
> 'live' table and let the system start to fill this up with data as new
> records are added. Downside of this is the backup size will not reduce.
> On point 3. I'm not sure how this works - will the primary file start
> to reduce in size, or will it remain at capacity and the secondary file
> will start to increase in size? Is there something I can do to start
> balancing the file sizes?
Space will be used from the file according to free space on each file. So you want to pre-allocate
file size for the new file, and then shrink the old file to push data over to the new file (and then
possibly increase the old file again so you have some free space).
I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all of the data when you
restore but backup/restore is a bit more complex. 2 doesn't really buy you anything if you are on
2000.
If you go for 1, you can get data from different point in time when you restore (as you have two
databases).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<andrew@.duncanhcmc.com> wrote in message
news:1163749909.367437.223570@.e3g2000cwe.googlegroups.com...
> Hi,
> We have a database which has reached our disk capacity of 340GB and we
> are looking at our options on how to resolve the disk space issue. The
> problem is I'm not too familiar with setting up additional filegroups
> and/or secondary files, or how they function.
> The database consists of only one table which stores all our product
> hi-res photography, each image being about 20MBs. The table is not
> heavily accessed - either read or write - but it is our central storage
> location for our studio photographs.
> We could delete all images for discontinued product, but we would then
> have to rely on a tape backup if we needed access to them in the
> future.
> Other options I think we have are to: -
> 1. Create a new database on a separate disk and have a scheduled
> archive task to move any discontinued product images to a table in this
> database. Place this DB on a separate disk. This means our 'live' db
> would be much smaller and quicker to backup
> 2. Create a new filegroup for existing DB and create an archive table
> on this group (file on different disk). Archive periodically and use a
> view to join the two tables. Only perform regular database backups on
> the 'live' filegroup
> 3. Create a secondary file on the same filegroup (primary) as the
> 'live' table and let the system start to fill this up with data as new
> records are added. Downside of this is the backup size will not reduce.
> On point 3. I'm not sure how this works - will the primary file start
> to reduce in size, or will it remain at capacity and the secondary file
> will start to increase in size? Is there something I can do to start
> balancing the file sizes?
> Also would be interested to know opinions on what the best approach
> would be and if I have any misconceptions about the above options (or
> if I've missed any)!
> Thanks,
> Andrew Duncan
>|||Hi Tibor,
Thanks for the advice. Option 1 does seem the simplest, so I'll be
opting for that.
Rgds,
Andrew
Tibor Karaszi wrote:
> Some thoughts:
> > We could delete all images for discontinued product, but we would then
> > have to rely on a tape backup if we needed access to them in the
> > future.
> Sounds like a lot of manual work.
>
> > 1. Create a new database on a separate disk and have a scheduled
> > archive task to move any discontinued product images to a table in this
> > database. Place this DB on a separate disk. This means our 'live' db
> > would be much smaller and quicker to backup
> This is probably what I would look for, but it require that your code adapts for this.
>
> > 2. Create a new filegroup for existing DB and create an archive table
> > on this group (file on different disk). Archive periodically and use a
> > view to join the two tables. Only perform regular database backups on
> > the 'live' filegroup
> You still need to adapt your code. But you now have a more complex backup strategy compared to 1. If
> you are on 2005, you can archive, set file group to read-only, backup the filegroup. If you now
> restore that filegroup backup, you don't need to restore all subsequent log backup. In 2000, you
> *do* need to restore all subsequent log backups, even if the file group was in read-only since the
> file group backup was performed.
>
> > 3. Create a secondary file on the same filegroup (primary) as the
> > 'live' table and let the system start to fill this up with data as new
> > records are added. Downside of this is the backup size will not reduce.
> > On point 3. I'm not sure how this works - will the primary file start
> > to reduce in size, or will it remain at capacity and the secondary file
> > will start to increase in size? Is there something I can do to start
> > balancing the file sizes?
> Space will be used from the file according to free space on each file. So you want to pre-allocate
> file size for the new file, and then shrink the old file to push data over to the new file (and then
> possibly increase the old file again so you have some free space).
> I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all of the data when you
> restore but backup/restore is a bit more complex. 2 doesn't really buy you anything if you are on
> 2000.
> If you go for 1, you can get data from different point in time when you restore (as you have two
> databases).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <andrew@.duncanhcmc.com> wrote in message
> news:1163749909.367437.223570@.e3g2000cwe.googlegroups.com...
> > Hi,
> >
> > We have a database which has reached our disk capacity of 340GB and we
> > are looking at our options on how to resolve the disk space issue. The
> > problem is I'm not too familiar with setting up additional filegroups
> > and/or secondary files, or how they function.
> >
> > The database consists of only one table which stores all our product
> > hi-res photography, each image being about 20MBs. The table is not
> > heavily accessed - either read or write - but it is our central storage
> > location for our studio photographs.
> >
> > We could delete all images for discontinued product, but we would then
> > have to rely on a tape backup if we needed access to them in the
> > future.
> >
> > Other options I think we have are to: -
> >
> > 1. Create a new database on a separate disk and have a scheduled
> > archive task to move any discontinued product images to a table in this
> > database. Place this DB on a separate disk. This means our 'live' db
> > would be much smaller and quicker to backup
> >
> > 2. Create a new filegroup for existing DB and create an archive table
> > on this group (file on different disk). Archive periodically and use a
> > view to join the two tables. Only perform regular database backups on
> > the 'live' filegroup
> >
> > 3. Create a secondary file on the same filegroup (primary) as the
> > 'live' table and let the system start to fill this up with data as new
> > records are added. Downside of this is the backup size will not reduce.
> >
> > On point 3. I'm not sure how this works - will the primary file start
> > to reduce in size, or will it remain at capacity and the secondary file
> > will start to increase in size? Is there something I can do to start
> > balancing the file sizes?
> >
> > Also would be interested to know opinions on what the best approach
> > would be and if I have any misconceptions about the above options (or
> > if I've missed any)!
> >
> > Thanks,
> >
> > Andrew Duncan
> >
Multiple files/filegroups
Hi,
We have a database which has reached our disk capacity of 340GB and we
are looking at our options on how to resolve the disk space issue. The
problem is I'm not too familiar with setting up additional filegroups
and/or secondary files, or how they function.
The database consists of only one table which stores all our product
hi-res photography, each image being about 20MBs. The table is not
heavily accessed - either read or write - but it is our central storage
location for our studio photographs.
We could delete all images for discontinued product, but we would then
have to rely on a tape backup if we needed access to them in the
future.
Other options I think we have are to: -
1. Create a new database on a separate disk and have a scheduled
archive task to move any discontinued product images to a table in this
database. Place this DB on a separate disk. This means our 'live' db
would be much smaller and quicker to backup
2. Create a new filegroup for existing DB and create an archive table
on this group (file on different disk). Archive periodically and use a
view to join the two tables. Only perform regular database backups on
the 'live' filegroup
3. Create a secondary file on the same filegroup (primary) as the
'live' table and let the system start to fill this up with data as new
records are added. Downside of this is the backup size will not reduce.
On point 3. I'm not sure how this works - will the primary file start
to reduce in size, or will it remain at capacity and the secondary file
will start to increase in size? Is there something I can do to start
balancing the file sizes?
Also would be interested to know opinions on what the best approach
would be and if I have any misconceptions about the above options (or
if I've missed any)!
Thanks,
Andrew DuncanSome thoughts:
> We could delete all images for discontinued product, but we would then
> have to rely on a tape backup if we needed access to them in the
> future.
Sounds like a lot of manual work.
> 1. Create a new database on a separate disk and have a scheduled
> archive task to move any discontinued product images to a table in this
> database. Place this DB on a separate disk. This means our 'live' db
> would be much smaller and quicker to backup
This is probably what I would look for, but it require that your code adapts
for this.
> 2. Create a new filegroup for existing DB and create an archive table
> on this group (file on different disk). Archive periodically and use a
> view to join the two tables. Only perform regular database backups on
> the 'live' filegroup
You still need to adapt your code. But you now have a more complex backup st
rategy compared to 1. If
you are on 2005, you can archive, set file group to read-only, backup the fi
legroup. If you now
restore that filegroup backup, you don't need to restore all subsequent log
backup. In 2000, you
*do* need to restore all subsequent log backups, even if the file group was
in read-only since the
file group backup was performed.
> 3. Create a secondary file on the same filegroup (primary) as the
> 'live' table and let the system start to fill this up with data as new
> records are added. Downside of this is the backup size will not reduce.
> On point 3. I'm not sure how this works - will the primary file start
> to reduce in size, or will it remain at capacity and the secondary file
> will start to increase in size? Is there something I can do to start
> balancing the file sizes?
Space will be used from the file according to free space on each file. So yo
u want to pre-allocate
file size for the new file, and then shrink the old file to push data over t
o the new file (and then
possibly increase the old file again so you have some free space).
I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all of
the data when you
restore but backup/restore is a bit more complex. 2 doesn't really buy you a
nything if you are on
2000.
If you go for 1, you can get data from different point in time when you rest
ore (as you have two
databases).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<andrew@.duncanhcmc.com> wrote in message
news:1163749909.367437.223570@.e3g2000cwe.googlegroups.com...
> Hi,
> We have a database which has reached our disk capacity of 340GB and we
> are looking at our options on how to resolve the disk space issue. The
> problem is I'm not too familiar with setting up additional filegroups
> and/or secondary files, or how they function.
> The database consists of only one table which stores all our product
> hi-res photography, each image being about 20MBs. The table is not
> heavily accessed - either read or write - but it is our central storage
> location for our studio photographs.
> We could delete all images for discontinued product, but we would then
> have to rely on a tape backup if we needed access to them in the
> future.
> Other options I think we have are to: -
> 1. Create a new database on a separate disk and have a scheduled
> archive task to move any discontinued product images to a table in this
> database. Place this DB on a separate disk. This means our 'live' db
> would be much smaller and quicker to backup
> 2. Create a new filegroup for existing DB and create an archive table
> on this group (file on different disk). Archive periodically and use a
> view to join the two tables. Only perform regular database backups on
> the 'live' filegroup
> 3. Create a secondary file on the same filegroup (primary) as the
> 'live' table and let the system start to fill this up with data as new
> records are added. Downside of this is the backup size will not reduce.
> On point 3. I'm not sure how this works - will the primary file start
> to reduce in size, or will it remain at capacity and the secondary file
> will start to increase in size? Is there something I can do to start
> balancing the file sizes?
> Also would be interested to know opinions on what the best approach
> would be and if I have any misconceptions about the above options (or
> if I've missed any)!
> Thanks,
> Andrew Duncan
>|||Hi Tibor,
Thanks for the advice. Option 1 does seem the simplest, so I'll be
opting for that.
Rgds,
Andrew
Tibor Karaszi wrote:[vbcol=seagreen]
> Some thoughts:
>
> Sounds like a lot of manual work.
>
> This is probably what I would look for, but it require that your code adap
ts for this.
>
> You still need to adapt your code. But you now have a more complex backup
strategy compared to 1. If
> you are on 2005, you can archive, set file group to read-only, backup the
filegroup. If you now
> restore that filegroup backup, you don't need to restore all subsequent lo
g backup. In 2000, you
> *do* need to restore all subsequent log backups, even if the file group wa
s in read-only since the
> file group backup was performed.
>
> Space will be used from the file according to free space on each file. So
you want to pre-allocate
> file size for the new file, and then shrink the old file to push data over
to the new file (and then
> possibly increase the old file again so you have some free space).
> I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all
of the data when you
> restore but backup/restore is a bit more complex. 2 doesn't really buy you
anything if you are on
> 2000.
> If you go for 1, you can get data from different point in time when you re
store (as you have two
> databases).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <andrew@.duncanhcmc.com> wrote in message
> news:1163749909.367437.223570@.e3g2000cwe.googlegroups.com...
We have a database which has reached our disk capacity of 340GB and we
are looking at our options on how to resolve the disk space issue. The
problem is I'm not too familiar with setting up additional filegroups
and/or secondary files, or how they function.
The database consists of only one table which stores all our product
hi-res photography, each image being about 20MBs. The table is not
heavily accessed - either read or write - but it is our central storage
location for our studio photographs.
We could delete all images for discontinued product, but we would then
have to rely on a tape backup if we needed access to them in the
future.
Other options I think we have are to: -
1. Create a new database on a separate disk and have a scheduled
archive task to move any discontinued product images to a table in this
database. Place this DB on a separate disk. This means our 'live' db
would be much smaller and quicker to backup
2. Create a new filegroup for existing DB and create an archive table
on this group (file on different disk). Archive periodically and use a
view to join the two tables. Only perform regular database backups on
the 'live' filegroup
3. Create a secondary file on the same filegroup (primary) as the
'live' table and let the system start to fill this up with data as new
records are added. Downside of this is the backup size will not reduce.
On point 3. I'm not sure how this works - will the primary file start
to reduce in size, or will it remain at capacity and the secondary file
will start to increase in size? Is there something I can do to start
balancing the file sizes?
Also would be interested to know opinions on what the best approach
would be and if I have any misconceptions about the above options (or
if I've missed any)!
Thanks,
Andrew DuncanSome thoughts:
> We could delete all images for discontinued product, but we would then
> have to rely on a tape backup if we needed access to them in the
> future.
Sounds like a lot of manual work.
> 1. Create a new database on a separate disk and have a scheduled
> archive task to move any discontinued product images to a table in this
> database. Place this DB on a separate disk. This means our 'live' db
> would be much smaller and quicker to backup
This is probably what I would look for, but it require that your code adapts
for this.
> 2. Create a new filegroup for existing DB and create an archive table
> on this group (file on different disk). Archive periodically and use a
> view to join the two tables. Only perform regular database backups on
> the 'live' filegroup
You still need to adapt your code. But you now have a more complex backup st
rategy compared to 1. If
you are on 2005, you can archive, set file group to read-only, backup the fi
legroup. If you now
restore that filegroup backup, you don't need to restore all subsequent log
backup. In 2000, you
*do* need to restore all subsequent log backups, even if the file group was
in read-only since the
file group backup was performed.
> 3. Create a secondary file on the same filegroup (primary) as the
> 'live' table and let the system start to fill this up with data as new
> records are added. Downside of this is the backup size will not reduce.
> On point 3. I'm not sure how this works - will the primary file start
> to reduce in size, or will it remain at capacity and the secondary file
> will start to increase in size? Is there something I can do to start
> balancing the file sizes?
Space will be used from the file according to free space on each file. So yo
u want to pre-allocate
file size for the new file, and then shrink the old file to push data over t
o the new file (and then
possibly increase the old file again so you have some free space).
I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all of
the data when you
restore but backup/restore is a bit more complex. 2 doesn't really buy you a
nything if you are on
2000.
If you go for 1, you can get data from different point in time when you rest
ore (as you have two
databases).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<andrew@.duncanhcmc.com> wrote in message
news:1163749909.367437.223570@.e3g2000cwe.googlegroups.com...
> Hi,
> We have a database which has reached our disk capacity of 340GB and we
> are looking at our options on how to resolve the disk space issue. The
> problem is I'm not too familiar with setting up additional filegroups
> and/or secondary files, or how they function.
> The database consists of only one table which stores all our product
> hi-res photography, each image being about 20MBs. The table is not
> heavily accessed - either read or write - but it is our central storage
> location for our studio photographs.
> We could delete all images for discontinued product, but we would then
> have to rely on a tape backup if we needed access to them in the
> future.
> Other options I think we have are to: -
> 1. Create a new database on a separate disk and have a scheduled
> archive task to move any discontinued product images to a table in this
> database. Place this DB on a separate disk. This means our 'live' db
> would be much smaller and quicker to backup
> 2. Create a new filegroup for existing DB and create an archive table
> on this group (file on different disk). Archive periodically and use a
> view to join the two tables. Only perform regular database backups on
> the 'live' filegroup
> 3. Create a secondary file on the same filegroup (primary) as the
> 'live' table and let the system start to fill this up with data as new
> records are added. Downside of this is the backup size will not reduce.
> On point 3. I'm not sure how this works - will the primary file start
> to reduce in size, or will it remain at capacity and the secondary file
> will start to increase in size? Is there something I can do to start
> balancing the file sizes?
> Also would be interested to know opinions on what the best approach
> would be and if I have any misconceptions about the above options (or
> if I've missed any)!
> Thanks,
> Andrew Duncan
>|||Hi Tibor,
Thanks for the advice. Option 1 does seem the simplest, so I'll be
opting for that.
Rgds,
Andrew
Tibor Karaszi wrote:[vbcol=seagreen]
> Some thoughts:
>
> Sounds like a lot of manual work.
>
> This is probably what I would look for, but it require that your code adap
ts for this.
>
> You still need to adapt your code. But you now have a more complex backup
strategy compared to 1. If
> you are on 2005, you can archive, set file group to read-only, backup the
filegroup. If you now
> restore that filegroup backup, you don't need to restore all subsequent lo
g backup. In 2000, you
> *do* need to restore all subsequent log backups, even if the file group wa
s in read-only since the
> file group backup was performed.
>
> Space will be used from the file according to free space on each file. So
you want to pre-allocate
> file size for the new file, and then shrink the old file to push data over
to the new file (and then
> possibly increase the old file again so you have some free space).
> I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all
of the data when you
> restore but backup/restore is a bit more complex. 2 doesn't really buy you
anything if you are on
> 2000.
> If you go for 1, you can get data from different point in time when you re
store (as you have two
> databases).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <andrew@.duncanhcmc.com> wrote in message
> news:1163749909.367437.223570@.e3g2000cwe.googlegroups.com...
Multiple files in FileGroup on RAID
We are putting out DBs on a RAID array and have the data and indexes in
their own file groups. My question is where can I find information about any
performance gain in I/O based on the number of files I have in the
FileGroup.
The array is made up of 14 drives with 1 hot spare (RAID 5). How many
files?
Thx"cw3" <cw@.3mc.com> wrote in message
news:%23yenkyNnDHA.3688@.TK2MSFTNGP11.phx.gbl...
> We are putting out DBs on a RAID array and have the data and indexes in
> their own file groups. My question is where can I find information about
any
> performance gain in I/O based on the number of files I have in the
> FileGroup.
> The array is made up of 14 drives with 1 hot spare (RAID 5). How many
> files?
>
Spreading out the data to multiple files in a filegroup on top of a single
RAID 5 array is redundant. Each file will be spread out over all 14 drives
by the RAID striping to begin with. It's useless do do it again with
multiple files per filegroup.
In your setup you needn't even bother putting data and indexes into seperate
filegroups.
If you had your disks RAIDed into 7 seperate 2 drive RAID 1 mirrors, then
you might spread a filegroup over multiple volumes for performance and
capacity.
But a better configuration might be to bundle the mirrored sets into 4-drive
stripe and mirror sets.
David|||While there can be performance gains with having multiple files per file
group there is no chart etc. that exists to show what your after. The gains
are going to be greatest with lots of drive arrays and lots of processors
when you have multiple files. Chances are you will not max out the
capabilities of the way you have it now with that hardware setup.
--
Andrew J. Kelly
SQL Server MVP
"cw3" <cw@.3mc.com> wrote in message
news:%23yenkyNnDHA.3688@.TK2MSFTNGP11.phx.gbl...
> We are putting out DBs on a RAID array and have the data and indexes in
> their own file groups. My question is where can I find information about
any
> performance gain in I/O based on the number of files I have in the
> FileGroup.
> The array is made up of 14 drives with 1 hot spare (RAID 5). How many
> files?
> Thx
>
their own file groups. My question is where can I find information about any
performance gain in I/O based on the number of files I have in the
FileGroup.
The array is made up of 14 drives with 1 hot spare (RAID 5). How many
files?
Thx"cw3" <cw@.3mc.com> wrote in message
news:%23yenkyNnDHA.3688@.TK2MSFTNGP11.phx.gbl...
> We are putting out DBs on a RAID array and have the data and indexes in
> their own file groups. My question is where can I find information about
any
> performance gain in I/O based on the number of files I have in the
> FileGroup.
> The array is made up of 14 drives with 1 hot spare (RAID 5). How many
> files?
>
Spreading out the data to multiple files in a filegroup on top of a single
RAID 5 array is redundant. Each file will be spread out over all 14 drives
by the RAID striping to begin with. It's useless do do it again with
multiple files per filegroup.
In your setup you needn't even bother putting data and indexes into seperate
filegroups.
If you had your disks RAIDed into 7 seperate 2 drive RAID 1 mirrors, then
you might spread a filegroup over multiple volumes for performance and
capacity.
But a better configuration might be to bundle the mirrored sets into 4-drive
stripe and mirror sets.
David|||While there can be performance gains with having multiple files per file
group there is no chart etc. that exists to show what your after. The gains
are going to be greatest with lots of drive arrays and lots of processors
when you have multiple files. Chances are you will not max out the
capabilities of the way you have it now with that hardware setup.
--
Andrew J. Kelly
SQL Server MVP
"cw3" <cw@.3mc.com> wrote in message
news:%23yenkyNnDHA.3688@.TK2MSFTNGP11.phx.gbl...
> We are putting out DBs on a RAID array and have the data and indexes in
> their own file groups. My question is where can I find information about
any
> performance gain in I/O based on the number of files I have in the
> FileGroup.
> The array is made up of 14 drives with 1 hot spare (RAID 5). How many
> files?
> Thx
>
Multiple files import
Hi,
I would like to make some DTS package that loads into one table all txt
files that are located in one directory (but I don't know how to do it).
Any help appreciated.
br,
jakubjacub
There are great articles for the subject on www.sqldts.com
"jakub" <jakub.wojnar@.gmail.com> wrote in message
news:RjHtg.33433$Nb2.616542@.news1.nokia.com...
> Hi,
> I would like to make some DTS package that loads into one table all txt
> files that are located in one directory (but I don't know how to do it).
> Any help appreciated.
> br,
> jakub
>|||thanks uri - I'm starting to read...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1esGWxpGHA.3564@.TK2MSFTNGP03.phx.gbl...
> jacub
> There are great articles for the subject on www.sqldts.com
>
> "jakub" <jakub.wojnar@.gmail.com> wrote in message
> news:RjHtg.33433$Nb2.616542@.news1.nokia.com...
>> Hi,
>> I would like to make some DTS package that loads into one table all txt
>> files that are located in one directory (but I don't know how to do it).
>> Any help appreciated.
>> br,
>> jakub
>>
>
I would like to make some DTS package that loads into one table all txt
files that are located in one directory (but I don't know how to do it).
Any help appreciated.
br,
jakubjacub
There are great articles for the subject on www.sqldts.com
"jakub" <jakub.wojnar@.gmail.com> wrote in message
news:RjHtg.33433$Nb2.616542@.news1.nokia.com...
> Hi,
> I would like to make some DTS package that loads into one table all txt
> files that are located in one directory (but I don't know how to do it).
> Any help appreciated.
> br,
> jakub
>|||thanks uri - I'm starting to read...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1esGWxpGHA.3564@.TK2MSFTNGP03.phx.gbl...
> jacub
> There are great articles for the subject on www.sqldts.com
>
> "jakub" <jakub.wojnar@.gmail.com> wrote in message
> news:RjHtg.33433$Nb2.616542@.news1.nokia.com...
>> Hi,
>> I would like to make some DTS package that loads into one table all txt
>> files that are located in one directory (but I don't know how to do it).
>> Any help appreciated.
>> br,
>> jakub
>>
>
Multiple files import
Hi,
I would like to make some DTS package that loads into one table all txt
files that are located in one directory (but I don't know how to do it).
Any help appreciated.
br,
jakubjacub
There are great articles for the subject on www.sqldts.com
"jakub" <jakub.wojnar@.gmail.com> wrote in message
news:RjHtg.33433$Nb2.616542@.news1.nokia.com...
> Hi,
> I would like to make some DTS package that loads into one table all txt
> files that are located in one directory (but I don't know how to do it).
> Any help appreciated.
> br,
> jakub
>|||thanks uri - I'm starting to read...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1esGWxpGHA.3564@.TK2MSFTNGP03.phx.gbl...
> jacub
> There are great articles for the subject on www.sqldts.com
>
> "jakub" <jakub.wojnar@.gmail.com> wrote in message
> news:RjHtg.33433$Nb2.616542@.news1.nokia.com...
>sql
I would like to make some DTS package that loads into one table all txt
files that are located in one directory (but I don't know how to do it).
Any help appreciated.
br,
jakubjacub
There are great articles for the subject on www.sqldts.com
"jakub" <jakub.wojnar@.gmail.com> wrote in message
news:RjHtg.33433$Nb2.616542@.news1.nokia.com...
> Hi,
> I would like to make some DTS package that loads into one table all txt
> files that are located in one directory (but I don't know how to do it).
> Any help appreciated.
> br,
> jakub
>|||thanks uri - I'm starting to read...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1esGWxpGHA.3564@.TK2MSFTNGP03.phx.gbl...
> jacub
> There are great articles for the subject on www.sqldts.com
>
> "jakub" <jakub.wojnar@.gmail.com> wrote in message
> news:RjHtg.33433$Nb2.616542@.news1.nokia.com...
>sql
Multiple Files for database
So, due to some oversights in planning, I've got a 200gb database that
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?
Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||On 2005-06-16 13:35:42 -0800, "David Gugick"
<david.gugick-nospam@.quest.com> said:
> Ben Hanson wrote:
> Is the file full of data or is there empty space? If there's sufficient
> empty space, you can use DBCC SHRINKFILE to recover disk space. It may
> take some time to run, so best to do this off-hours. If you just need
> to move tables, you can create the new database file and recreate the
> clustered indexes on the new data file for those tables you want to
> move.
> What's your drive array setup?
Details:
Win2k3 server running MSSQL server 2000 SP3
400gb RAID5, 4 x 146gb - Data
36gb RAID1, 2 x 36gb - System
This is a GIS server running ArcSDE. SQL is the database backend for
SDE in this case. The database is mostly read intensive, with few
writes/changes. There are two databases, one for vector data(300mb
used, 20gb allocated), and one for raster data(186gb used, 190gb
allocated). I just did a shrink database to free up 30gb of unused
space on the raster database.
What I'm trying to figure out is how to move from one database:one
massive file to one database:multiple small files. I can't split to
new databases without breaking current SDE connections which are
database specific.
This also begs the question, In a database with multiple files in one
file group, how does SQL decide which one to write to?
|||> What I'm trying to figure out is how to move from one database:one massive file to one
> database:multiple small files.
You can't do that without some free space. You can add a new file and shrink the original file.
Well, thinking about it, it is *possible* that the shrink can at the same time autogrow the other
file. But I believe that shrink doesn't shrink the physical file size until the very end, after
moving of the data. If that is the case, you need free space:
Add new file.
Shrink original file so that SQL server pushes the data to the new file.
> This also begs the question, In a database with multiple files in one file group, how does SQL
> decide which one to write to?
The one with more free space.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Hanson" <benhanson@.borough.kenai.ak.us> wrote in message
news:2005061615392575249%benhanson@.boroughkenaiaku s...
> On 2005-06-16 13:35:42 -0800, "David Gugick" <david.gugick-nospam@.quest.com> said:
>
> Details:
> Win2k3 server running MSSQL server 2000 SP3
> 400gb RAID5, 4 x 146gb - Data
> 36gb RAID1, 2 x 36gb - System
> This is a GIS server running ArcSDE. SQL is the database backend for SDE in this case. The
> database is mostly read intensive, with few writes/changes. There are two databases, one for
> vector data(300mb used, 20gb allocated), and one for raster data(186gb used, 190gb allocated). I
> just did a shrink database to free up 30gb of unused space on the raster database.
> What I'm trying to figure out is how to move from one database:one massive file to one
> database:multiple small files. I can't split to new databases without breaking current SDE
> connections which are database specific.
> This also begs the question, In a database with multiple files in one file group, how does SQL
> decide which one to write to?
>
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?
Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||On 2005-06-16 13:35:42 -0800, "David Gugick"
<david.gugick-nospam@.quest.com> said:
> Ben Hanson wrote:
> Is the file full of data or is there empty space? If there's sufficient
> empty space, you can use DBCC SHRINKFILE to recover disk space. It may
> take some time to run, so best to do this off-hours. If you just need
> to move tables, you can create the new database file and recreate the
> clustered indexes on the new data file for those tables you want to
> move.
> What's your drive array setup?
Details:
Win2k3 server running MSSQL server 2000 SP3
400gb RAID5, 4 x 146gb - Data
36gb RAID1, 2 x 36gb - System
This is a GIS server running ArcSDE. SQL is the database backend for
SDE in this case. The database is mostly read intensive, with few
writes/changes. There are two databases, one for vector data(300mb
used, 20gb allocated), and one for raster data(186gb used, 190gb
allocated). I just did a shrink database to free up 30gb of unused
space on the raster database.
What I'm trying to figure out is how to move from one database:one
massive file to one database:multiple small files. I can't split to
new databases without breaking current SDE connections which are
database specific.
This also begs the question, In a database with multiple files in one
file group, how does SQL decide which one to write to?
|||> What I'm trying to figure out is how to move from one database:one massive file to one
> database:multiple small files.
You can't do that without some free space. You can add a new file and shrink the original file.
Well, thinking about it, it is *possible* that the shrink can at the same time autogrow the other
file. But I believe that shrink doesn't shrink the physical file size until the very end, after
moving of the data. If that is the case, you need free space:
Add new file.
Shrink original file so that SQL server pushes the data to the new file.
> This also begs the question, In a database with multiple files in one file group, how does SQL
> decide which one to write to?
The one with more free space.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Hanson" <benhanson@.borough.kenai.ak.us> wrote in message
news:2005061615392575249%benhanson@.boroughkenaiaku s...
> On 2005-06-16 13:35:42 -0800, "David Gugick" <david.gugick-nospam@.quest.com> said:
>
> Details:
> Win2k3 server running MSSQL server 2000 SP3
> 400gb RAID5, 4 x 146gb - Data
> 36gb RAID1, 2 x 36gb - System
> This is a GIS server running ArcSDE. SQL is the database backend for SDE in this case. The
> database is mostly read intensive, with few writes/changes. There are two databases, one for
> vector data(300mb used, 20gb allocated), and one for raster data(186gb used, 190gb allocated). I
> just did a shrink database to free up 30gb of unused space on the raster database.
> What I'm trying to figure out is how to move from one database:one massive file to one
> database:multiple small files. I can't split to new databases without breaking current SDE
> connections which are database specific.
> This also begs the question, In a database with multiple files in one file group, how does SQL
> decide which one to write to?
>
Multiple Files for database
So, due to some oversights in planning, I've got a 200gb database that
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On 2005-06-16 13:35:42 -0800, "David Gugick"
<david.gugick-nospam@.quest.com> said:
> Ben Hanson wrote:
> Is the file full of data or is there empty space? If there's sufficient
> empty space, you can use DBCC SHRINKFILE to recover disk space. It may
> take some time to run, so best to do this off-hours. If you just need
> to move tables, you can create the new database file and recreate the
> clustered indexes on the new data file for those tables you want to
> move.
> What's your drive array setup?
Details:
Win2k3 server running MSSQL server 2000 SP3
400gb RAID5, 4 x 146gb - Data
36gb RAID1, 2 x 36gb - System
This is a GIS server running ArcSDE. SQL is the database backend for
SDE in this case. The database is mostly read intensive, with few
writes/changes. There are two databases, one for vector data(300mb
used, 20gb allocated), and one for raster data(186gb used, 190gb
allocated). I just did a shrink database to free up 30gb of unused
space on the raster database.
What I'm trying to figure out is how to move from one database:one
massive file to one database:multiple small files. I can't split to
new databases without breaking current SDE connections which are
database specific.
This also begs the question, In a database with multiple files in one
file group, how does SQL decide which one to write to?|||> What I'm trying to figure out is how to move from one database:one massive file to one[vbc
ol=seagreen]
> database:multiple small files.[/vbcol]
You can't do that without some free space. You can add a new file and shrink
the original file.
Well, thinking about it, it is *possible* that the shrink can at the same ti
me autogrow the other
file. But I believe that shrink doesn't shrink the physical file size until
the very end, after
moving of the data. If that is the case, you need free space:
Add new file.
Shrink original file so that SQL server pushes the data to the new file.
> This also begs the question, In a database with multiple files in one file
group, how does SQL
> decide which one to write to?
The one with more free space.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Hanson" <benhanson@.borough.kenai.ak.us> wrote in message
news:2005061615392575249%benhanson@.borou
ghkenaiakus...
> On 2005-06-16 13:35:42 -0800, "David Gugick" <david.gugick-nospam@.quest.co
m> said:
>
>
> Details:
> Win2k3 server running MSSQL server 2000 SP3
> 400gb RAID5, 4 x 146gb - Data
> 36gb RAID1, 2 x 36gb - System
> This is a GIS server running ArcSDE. SQL is the database backend for SDE
in this case. The
> database is mostly read intensive, with few writes/changes. There are two
databases, one for
> vector data(300mb used, 20gb allocated), and one for raster data(186gb use
d, 190gb allocated). I
> just did a shrink database to free up 30gb of unused space on the raster d
atabase.
> What I'm trying to figure out is how to move from one database:one massive
file to one
> database:multiple small files. I can't split to new databases without bre
aking current SDE
> connections which are database specific.
> This also begs the question, In a database with multiple files in one file
group, how does SQL
> decide which one to write to?
>
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On 2005-06-16 13:35:42 -0800, "David Gugick"
<david.gugick-nospam@.quest.com> said:
> Ben Hanson wrote:
> Is the file full of data or is there empty space? If there's sufficient
> empty space, you can use DBCC SHRINKFILE to recover disk space. It may
> take some time to run, so best to do this off-hours. If you just need
> to move tables, you can create the new database file and recreate the
> clustered indexes on the new data file for those tables you want to
> move.
> What's your drive array setup?
Details:
Win2k3 server running MSSQL server 2000 SP3
400gb RAID5, 4 x 146gb - Data
36gb RAID1, 2 x 36gb - System
This is a GIS server running ArcSDE. SQL is the database backend for
SDE in this case. The database is mostly read intensive, with few
writes/changes. There are two databases, one for vector data(300mb
used, 20gb allocated), and one for raster data(186gb used, 190gb
allocated). I just did a shrink database to free up 30gb of unused
space on the raster database.
What I'm trying to figure out is how to move from one database:one
massive file to one database:multiple small files. I can't split to
new databases without breaking current SDE connections which are
database specific.
This also begs the question, In a database with multiple files in one
file group, how does SQL decide which one to write to?|||> What I'm trying to figure out is how to move from one database:one massive file to one[vbc
ol=seagreen]
> database:multiple small files.[/vbcol]
You can't do that without some free space. You can add a new file and shrink
the original file.
Well, thinking about it, it is *possible* that the shrink can at the same ti
me autogrow the other
file. But I believe that shrink doesn't shrink the physical file size until
the very end, after
moving of the data. If that is the case, you need free space:
Add new file.
Shrink original file so that SQL server pushes the data to the new file.
> This also begs the question, In a database with multiple files in one file
group, how does SQL
> decide which one to write to?
The one with more free space.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Hanson" <benhanson@.borough.kenai.ak.us> wrote in message
news:2005061615392575249%benhanson@.borou
ghkenaiakus...
> On 2005-06-16 13:35:42 -0800, "David Gugick" <david.gugick-nospam@.quest.co
m> said:
>
>
> Details:
> Win2k3 server running MSSQL server 2000 SP3
> 400gb RAID5, 4 x 146gb - Data
> 36gb RAID1, 2 x 36gb - System
> This is a GIS server running ArcSDE. SQL is the database backend for SDE
in this case. The
> database is mostly read intensive, with few writes/changes. There are two
databases, one for
> vector data(300mb used, 20gb allocated), and one for raster data(186gb use
d, 190gb allocated). I
> just did a shrink database to free up 30gb of unused space on the raster d
atabase.
> What I'm trying to figure out is how to move from one database:one massive
file to one
> database:multiple small files. I can't split to new databases without bre
aking current SDE
> connections which are database specific.
> This also begs the question, In a database with multiple files in one file
group, how does SQL
> decide which one to write to?
>
Multiple Files for database
So, due to some oversights in planning, I've got a 200gb database that
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com
multiple files for 1 group but same "disk"... performance?
Hi,
To try to improve my performance on a system where I have no control (a long
long story...)
My problem:
the HD subsystem is "bad", I'm on a SAN and most of my wait types are caused
by the disks.
I have only 1 disk available (300gb in Raid 5, but I don't know how many
disks behind this)
so I have 1 partition for my database!!! (the throughput is bad)
My server has 4 processors (8 with HT enabled)
I know that SQL perform better if I have 1 file by processor because SQL
server create 1 thread by file (I'm right?)
so, if I create 8 files but all in the same group, does this help me?
if yes, how can I redistribute the usage of my current tables, which are in
only 1 file for the moment, on all files (without having to copy the tables
in a new table)
or I have to create 8 groups with 1 file only and choose which table I put
in which group?
or this idea will improove... nothing!
thanks.
jeromeHello Jeje,
Seems we are in the same boat, I too have a large SAN with RAID 5 and
500 gb over 10 disks.
The optimization I have gone with is creating file groups for large
tables and sperate file groups for indexes.
Eg
FactSalesData -fact table data
FactSalesIndex - fact table index
You can only specify a file group for a table to use; you can't
specify the file as far as I know.
One other thing to consider is the use of logical drives on you single
partition, this will allow you to stream the disk queues via the OS,
well that's the theory.
So with sperate file groups for indexes and data you should reduce the
file lock contention.
For more tips on file groups see
http://www.sql-server-performance.com/filegroups.asp
Also check out http://www.sql-server-performance.com/ew_san.asp
It has some great ideas on SAN configuration.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/|||thanks
do you see any improvement?
my firsts tests wher I divide my primary group into 8 files don't change
anything (or a too small change)
I'll try to use separated filegroups.
<Myles.Matheson@.gmail.com> wrote in message
news:1124192466.494874.179750@.f14g2000cwb.googlegroups.com...
> Hello Jeje,
> Seems we are in the same boat, I too have a large SAN with RAID 5 and
> 500 gb over 10 disks.
> The optimization I have gone with is creating file groups for large
> tables and sperate file groups for indexes.
> Eg
> FactSalesData -fact table data
> FactSalesIndex - fact table index
> You can only specify a file group for a table to use; you can't
> specify the file as far as I know.
> One other thing to consider is the use of logical drives on you single
> partition, this will allow you to stream the disk queues via the OS,
> well that's the theory.
> So with sperate file groups for indexes and data you should reduce the
> file lock contention.
> For more tips on file groups see
> http://www.sql-server-performance.com/filegroups.asp
>
> Also check out http://www.sql-server-performance.com/ew_san.asp
> It has some great ideas on SAN configuration.
> Hope this helps,
>
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>
To try to improve my performance on a system where I have no control (a long
long story...)
My problem:
the HD subsystem is "bad", I'm on a SAN and most of my wait types are caused
by the disks.
I have only 1 disk available (300gb in Raid 5, but I don't know how many
disks behind this)
so I have 1 partition for my database!!! (the throughput is bad)
My server has 4 processors (8 with HT enabled)
I know that SQL perform better if I have 1 file by processor because SQL
server create 1 thread by file (I'm right?)
so, if I create 8 files but all in the same group, does this help me?
if yes, how can I redistribute the usage of my current tables, which are in
only 1 file for the moment, on all files (without having to copy the tables
in a new table)
or I have to create 8 groups with 1 file only and choose which table I put
in which group?
or this idea will improove... nothing!
thanks.
jeromeHello Jeje,
Seems we are in the same boat, I too have a large SAN with RAID 5 and
500 gb over 10 disks.
The optimization I have gone with is creating file groups for large
tables and sperate file groups for indexes.
Eg
FactSalesData -fact table data
FactSalesIndex - fact table index
You can only specify a file group for a table to use; you can't
specify the file as far as I know.
One other thing to consider is the use of logical drives on you single
partition, this will allow you to stream the disk queues via the OS,
well that's the theory.
So with sperate file groups for indexes and data you should reduce the
file lock contention.
For more tips on file groups see
http://www.sql-server-performance.com/filegroups.asp
Also check out http://www.sql-server-performance.com/ew_san.asp
It has some great ideas on SAN configuration.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/|||thanks
do you see any improvement?
my firsts tests wher I divide my primary group into 8 files don't change
anything (or a too small change)
I'll try to use separated filegroups.
<Myles.Matheson@.gmail.com> wrote in message
news:1124192466.494874.179750@.f14g2000cwb.googlegroups.com...
> Hello Jeje,
> Seems we are in the same boat, I too have a large SAN with RAID 5 and
> 500 gb over 10 disks.
> The optimization I have gone with is creating file groups for large
> tables and sperate file groups for indexes.
> Eg
> FactSalesData -fact table data
> FactSalesIndex - fact table index
> You can only specify a file group for a table to use; you can't
> specify the file as far as I know.
> One other thing to consider is the use of logical drives on you single
> partition, this will allow you to stream the disk queues via the OS,
> well that's the theory.
> So with sperate file groups for indexes and data you should reduce the
> file lock contention.
> For more tips on file groups see
> http://www.sql-server-performance.com/filegroups.asp
>
> Also check out http://www.sql-server-performance.com/ew_san.asp
> It has some great ideas on SAN configuration.
> Hope this helps,
>
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>
multiple files for 1 group but same "disk"... performance?
Hi,
To try to improve my performance on a system where I have no control (a long
long story...)
My problem:
the HD subsystem is "bad", I'm on a SAN and most of my wait types are caused
by the disks.
I have only 1 disk available (300gb in Raid 5, but I don't know how many
disks behind this)
so I have 1 partition for my database!!! (the throughput is bad)
My server has 4 processors (8 with HT enabled)
I know that SQL perform better if I have 1 file by processor because SQL
server create 1 thread by file (I'm right?)
so, if I create 8 files but all in the same group, does this help me?
if yes, how can I redistribute the usage of my current tables, which are in
only 1 file for the moment, on all files (without having to copy the tables
in a new table)
or I have to create 8 groups with 1 file only and choose which table I put
in which group?
or this idea will improove... nothing!
thanks.
jerome
Hello Jeje,
Seems we are in the same boat, I too have a large SAN with RAID 5 and
500 gb over 10 disks.
The optimization I have gone with is creating file groups for large
tables and sperate file groups for indexes.
Eg
FactSalesData -fact table data
FactSalesIndex - fact table index
You can only specify a file group for a table to use; you can't
specify the file as far as I know.
One other thing to consider is the use of logical drives on you single
partition, this will allow you to stream the disk queues via the OS,
well that's the theory.
So with sperate file groups for indexes and data you should reduce the
file lock contention.
For more tips on file groups see
http://www.sql-server-performance.com/filegroups.asp
Also check out http://www.sql-server-performance.com/ew_san.asp
It has some great ideas on SAN configuration.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||thanks
do you see any improvement?
my firsts tests wher I divide my primary group into 8 files don't change
anything (or a too small change)
I'll try to use separated filegroups.
<Myles.Matheson@.gmail.com> wrote in message
news:1124192466.494874.179750@.f14g2000cwb.googlegr oups.com...
> Hello Jeje,
> Seems we are in the same boat, I too have a large SAN with RAID 5 and
> 500 gb over 10 disks.
> The optimization I have gone with is creating file groups for large
> tables and sperate file groups for indexes.
> Eg
> FactSalesData -fact table data
> FactSalesIndex - fact table index
> You can only specify a file group for a table to use; you can't
> specify the file as far as I know.
> One other thing to consider is the use of logical drives on you single
> partition, this will allow you to stream the disk queues via the OS,
> well that's the theory.
> So with sperate file groups for indexes and data you should reduce the
> file lock contention.
> For more tips on file groups see
> http://www.sql-server-performance.com/filegroups.asp
>
> Also check out http://www.sql-server-performance.com/ew_san.asp
> It has some great ideas on SAN configuration.
> Hope this helps,
>
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>
sql
To try to improve my performance on a system where I have no control (a long
long story...)
My problem:
the HD subsystem is "bad", I'm on a SAN and most of my wait types are caused
by the disks.
I have only 1 disk available (300gb in Raid 5, but I don't know how many
disks behind this)
so I have 1 partition for my database!!! (the throughput is bad)
My server has 4 processors (8 with HT enabled)
I know that SQL perform better if I have 1 file by processor because SQL
server create 1 thread by file (I'm right?)
so, if I create 8 files but all in the same group, does this help me?
if yes, how can I redistribute the usage of my current tables, which are in
only 1 file for the moment, on all files (without having to copy the tables
in a new table)
or I have to create 8 groups with 1 file only and choose which table I put
in which group?
or this idea will improove... nothing!
thanks.
jerome
Hello Jeje,
Seems we are in the same boat, I too have a large SAN with RAID 5 and
500 gb over 10 disks.
The optimization I have gone with is creating file groups for large
tables and sperate file groups for indexes.
Eg
FactSalesData -fact table data
FactSalesIndex - fact table index
You can only specify a file group for a table to use; you can't
specify the file as far as I know.
One other thing to consider is the use of logical drives on you single
partition, this will allow you to stream the disk queues via the OS,
well that's the theory.
So with sperate file groups for indexes and data you should reduce the
file lock contention.
For more tips on file groups see
http://www.sql-server-performance.com/filegroups.asp
Also check out http://www.sql-server-performance.com/ew_san.asp
It has some great ideas on SAN configuration.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||thanks
do you see any improvement?
my firsts tests wher I divide my primary group into 8 files don't change
anything (or a too small change)
I'll try to use separated filegroups.
<Myles.Matheson@.gmail.com> wrote in message
news:1124192466.494874.179750@.f14g2000cwb.googlegr oups.com...
> Hello Jeje,
> Seems we are in the same boat, I too have a large SAN with RAID 5 and
> 500 gb over 10 disks.
> The optimization I have gone with is creating file groups for large
> tables and sperate file groups for indexes.
> Eg
> FactSalesData -fact table data
> FactSalesIndex - fact table index
> You can only specify a file group for a table to use; you can't
> specify the file as far as I know.
> One other thing to consider is the use of logical drives on you single
> partition, this will allow you to stream the disk queues via the OS,
> well that's the theory.
> So with sperate file groups for indexes and data you should reduce the
> file lock contention.
> For more tips on file groups see
> http://www.sql-server-performance.com/filegroups.asp
>
> Also check out http://www.sql-server-performance.com/ew_san.asp
> It has some great ideas on SAN configuration.
> Hope this helps,
>
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>
sql
Multiple Files -- DTS
I have a rather large sale transaction DB. Basic header, and detail tables. I am providing a third party company with daily sales information, and I need to give them back data from about 8 or 9 months ago. I currently have a DTS package that gets sales for the current day, but since I have to go back, I have to manually edit the query in the DTS package, and change the date range...UNLESS ...
Blah, blah, blah. The problem is that they can only take the data in Daily files. So, there would be ONE file for each day. I really don't need to be manually running these jobs, so I'm wondering if someone could point me to a way of writing a package (maybe ActiveX, not sure) that would run through a loop, basically, of dates, and create a seperate file for each day. Versus having to edit a generic DTS package, and changing the date range 350 times...would it be an option to select the current daterange from a table. Then, as a last step, update daterange to the next (or have a daterange column and another column that acts as a 'done this one' and update that column)? If the package simply exports the table contents you could also consider creating a dynamic sp but I am not sure if that's an option for you.|||That does make sense, but I am not sure how I would go about "looping" through the Daterange, and then creating a file for each day based on the query that is in the one DTS package.|||This is what I've done, and it's led me to another question:
@.Date1=(SELECT BeginDate FROM DateRangeTable)
@.Date2=(SELECT EndDate FROM DateRangeTable)
...SELECT BLAH, BLAH...WHERE Date BETWEEN @.Date1 AND @.Date2
UPDATE DateRange SET Loaded='YES' WHERE BeginDate=@.Date1
EXEC master..xp_cmdshell 'REN I:\DailySales ' + @.Date1
--
By itself, the EXEC xp_cmdshell runs just fine. However, when I include The Main query, It doesn't work at all with NO errors...anyone know what gives?|||Does it have to be formatted a certain way? You could certainly do it in ActiveX, or simply use xp_cmdshell to execute BCP:
EXEC master..xp_cmdshell 'bcp ' + 'SELECT BLAH, BLAH...WHERE Date BETWEEN @.Date1 AND @.Date2'|||Yes, the DTS Package has a certain format that I haven't been able to figure out how to match with bcp.|||I haven't worked with BCP for sometime, but it does have a parameter for a "format file". If you can't get BCP to format it the way you want, you can do it in ActiveX.
Blah, blah, blah. The problem is that they can only take the data in Daily files. So, there would be ONE file for each day. I really don't need to be manually running these jobs, so I'm wondering if someone could point me to a way of writing a package (maybe ActiveX, not sure) that would run through a loop, basically, of dates, and create a seperate file for each day. Versus having to edit a generic DTS package, and changing the date range 350 times...would it be an option to select the current daterange from a table. Then, as a last step, update daterange to the next (or have a daterange column and another column that acts as a 'done this one' and update that column)? If the package simply exports the table contents you could also consider creating a dynamic sp but I am not sure if that's an option for you.|||That does make sense, but I am not sure how I would go about "looping" through the Daterange, and then creating a file for each day based on the query that is in the one DTS package.|||This is what I've done, and it's led me to another question:
@.Date1=(SELECT BeginDate FROM DateRangeTable)
@.Date2=(SELECT EndDate FROM DateRangeTable)
...SELECT BLAH, BLAH...WHERE Date BETWEEN @.Date1 AND @.Date2
UPDATE DateRange SET Loaded='YES' WHERE BeginDate=@.Date1
EXEC master..xp_cmdshell 'REN I:\DailySales ' + @.Date1
--
By itself, the EXEC xp_cmdshell runs just fine. However, when I include The Main query, It doesn't work at all with NO errors...anyone know what gives?|||Does it have to be formatted a certain way? You could certainly do it in ActiveX, or simply use xp_cmdshell to execute BCP:
EXEC master..xp_cmdshell 'bcp ' + 'SELECT BLAH, BLAH...WHERE Date BETWEEN @.Date1 AND @.Date2'|||Yes, the DTS Package has a certain format that I haven't been able to figure out how to match with bcp.|||I haven't worked with BCP for sometime, but it does have a parameter for a "format file". If you can't get BCP to format it the way you want, you can do it in ActiveX.
Wednesday, March 21, 2012
Multiple Files
I have a job with a single step that executes a stored procedure that performs the following steps:
1. Checks for the existance of a file A in a folder A
2. If it exists,
a. executes the cmdshell to run a DTS package to drop a table, recreate it and load the data in the file A to table X
b. runs other stored procedures that use the data in table X to create other tables Y and Z
c. executes the cndshell to remove and rename the file A from Folder A into Folder B
What I'd like to do is use this same stored procedure if possible, but create a job or another store procedure that would loop thru and process multiple files in Folder A instead of just one.
Any suggestions would be greatly appreciated
My post in this thread should point you in a workable direction.
Multiple file import SQLExpress
Hi everyone one,
I'm very new to SQLExpress and I'm having difficulty with a procedure I
found on the net
to import multiple files into a table:
http://www.databasejournal.com/feat...cle.php/3325701
The method I'm using is method one.
I've done everything as outlined, however when running the procedure within
SQLexpress I receive the error message
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
I'm presuming it has something to do with the Fieldterminator remark, the
double quote and comma specified in the bulk insert statement:
set @.Query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
WITH
( FIELDTERMINATOR = "," , ROWTERMINATOR = "\n")'
I've tried running the bulk insert with single quotes and it runs fine..with
double quotes not so good I get the same error so thats why I think its a
syntax error...but when I replace single quotes in the procedure, the
procedure doesn't compile...i.e.
set @.Query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
WITH
( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n')'
....I'm at a loss as to how to fix it!
Could someone help please, thanks so much.Try using single quotes instead of double quotes to delimit literals so that
you aren't sensitive to your QUOTED_IDENTIFIER setting. When you need a
quote within a literal, specify 2 single quotes:
set @.Query ='BULK INSERT '+ @.Tablename + ' FROM '''+ @.Filepath+@.Filename+'''
WITH
( FIELDTERMINATOR = '','' , ROWTERMINATOR = ''\n'')'
Happy Holidays
Dan Guzman
SQL Server MVP
"Dale" <dale@.nospam.com> wrote in message
news:es09z0vDGHA.472@.TK2MSFTNGP12.phx.gbl...
> Hi everyone one,
> I'm very new to SQLExpress and I'm having difficulty with a procedure I
> found on the net
> to import multiple files into a table:
> http://www.databasejournal.com/feat...cle.php/3325701
> The method I'm using is method one.
> I've done everything as outlined, however when running the procedure
> within SQLexpress I receive the error message
> Msg 102, Level 15, State 1, Line 2
> Incorrect syntax near ','.
> I'm presuming it has something to do with the Fieldterminator remark, the
> double quote and comma specified in the bulk insert statement:
> set @.Query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
> WITH
> ( FIELDTERMINATOR = "," , ROWTERMINATOR = "\n")'
> I've tried running the bulk insert with single quotes and it runs
> fine..with double quotes not so good I get the same error so thats why I
> think its a syntax error...but when I replace single quotes in the
> procedure, the procedure doesn't compile...i.e.
> set @.Query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
> WITH
> ( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n')'
> ....I'm at a loss as to how to fix it!
> Could someone help please, thanks so much.
>
>|||Thanks Dan, that worked, now on to my "real" data...
the fields are separated by a comma but enclosed in a double quote i.e.
"12345","67899"
How do I get the field terminator set correctly? I just know you're going
to
tell me to remove the double quotes...(sigh)...theres a ton of files...
Thanks again
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OX$4aOwDGHA.344@.TK2MSFTNGP11.phx.gbl...
> Try using single quotes instead of double quotes to delimit literals so
> that you aren't sensitive to your QUOTED_IDENTIFIER setting. When you
> need a quote within a literal, specify 2 single quotes:
> set @.Query ='BULK INSERT '+ @.Tablename + ' FROM '''+
> @.Filepath+@.Filename+'''
> WITH
> ( FIELDTERMINATOR = '','' , ROWTERMINATOR = ''\n'')'
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Dale" <dale@.nospam.com> wrote in message
> news:es09z0vDGHA.472@.TK2MSFTNGP12.phx.gbl...
>|||You need to create a format file when you have a more complicated file
format. Sample SQL 2000 format file:
8.0
3
1 SQLCHAR 0 1 "\"" 0 quote ""
2 SQLCHAR 0 10 "\",\"" 1 Col1 ""
3 SQLCHAR 0 10 "\"\r\n" 2 Col2 ""
Specify the format file using FORMATFILE in your BULK INSERT statement
instead of FIELDTERMINATOR and ROWTERMINATOR:
set @.Query ='BULK INSERT '+ @.Tablename + ' FROM '''+
@.Filepath+@.Filename+'''
WITH
( FORMATFILE = ''' + @.FormatFilename + ''')'
See the Books Online for format file details.
Happy Holidays
Dan Guzman
SQL Server MVP
"Dale" <dale@.nospam.com> wrote in message
news:OtBJ2LxDGHA.3064@.TK2MSFTNGP10.phx.gbl...
> Thanks Dan, that worked, now on to my "real" data...
> the fields are separated by a comma but enclosed in a double quote i.e.
> "12345","67899"
> How do I get the field terminator set correctly? I just know you're going
> to
> tell me to remove the double quotes...(sigh)...theres a ton of files...
> Thanks again
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OX$4aOwDGHA.344@.TK2MSFTNGP11.phx.gbl...
>|||Thanks Dan...this certainly got complicated fast!! I'm guessing this format
file is likened to a specifications file in msaccess.
At any rate, when trying to run the bcp utility:
bcp adventureworks.humanresources.department format nul -T -n -f
importtest-f-n.txt
I receive an error, unable to open a connection...that remote access may not
be enabled? I've checked using the surface configuration tool and remote
looks to be enabled.
Secondly if I get this bcp utililty to work..can it be run against a "txt"
file (haven't been able to get this to work either)..it doesn't make sense
to me to run the utility against the table rec'ving the data if it isn't the
source of the import?...I can't see any other way to create this format file
other than manually?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eqAaAwxDGHA.3528@.TK2MSFTNGP12.phx.gbl...
> You need to create a format file when you have a more complicated file
> format. Sample SQL 2000 format file:
> 8.0
> 3
> 1 SQLCHAR 0 1 "\"" 0 quote ""
> 2 SQLCHAR 0 10 "\",\"" 1 Col1 ""
> 3 SQLCHAR 0 10 "\"\r\n" 2 Col2 ""
> Specify the format file using FORMATFILE in your BULK INSERT statement
> instead of FIELDTERMINATOR and ROWTERMINATOR:
> set @.Query ='BULK INSERT '+ @.Tablename + ' FROM '''+
> @.Filepath+@.Filename+'''
> WITH
> ( FORMATFILE = ''' + @.FormatFilename + ''')'
> See the Books Online for format file details.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Dale" <dale@.nospam.com> wrote in message
> news:OtBJ2LxDGHA.3064@.TK2MSFTNGP10.phx.gbl...
>|||> bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-f-n.txt
Since you haven't specified a server, BCP will connect to the default
instance on the local machine. Is that your intent?
> it doesn't make sense to me to run the utility against the table rec'ving
> the data if it isn't the source of the import?
A format file describes the format of your text file (source or target).
When you run BCP using the 'format' option, the table is neither a source
nor target; the table schema is used to facilitate creating a format file
with one field per column. Since you have specified '-n', the generated
format file will be appropriate only for native file format rather than the
comma-delimited/quoted field format you specified in your original post.
> I can't see any other way to create this format file other than manually?
Although BCP can't directly create a format file for a
comma-delimited/quoted field format, you can use BCP to create a default
character format file and then change the generated to match your actual
file. That way, you at least won't need to enter all the columns. For
example:
bcp adventureworks.humanresources.department format
nul -T -c -fimporttest-f-n.txt -SMyServer
Hope this helps.
Dan Guzman
SQL Server MVP
"Dale" <dale@.nospam.com> wrote in message
news:uMsEFo7DGHA.516@.TK2MSFTNGP15.phx.gbl...
> Thanks Dan...this certainly got complicated fast!! I'm guessing this
> format file is likened to a specifications file in msaccess.
> At any rate, when trying to run the bcp utility:
> bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-f-n.txt
> I receive an error, unable to open a connection...that remote access may
> not be enabled? I've checked using the surface configuration tool and
> remote looks to be enabled.
> Secondly if I get this bcp utililty to work..can it be run against a "txt"
> file (haven't been able to get this to work either)..it doesn't make sense
> to me to run the utility against the table rec'ving the data if it isn't
> the source of the import?...I can't see any other way to create this
> format file other than manually?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eqAaAwxDGHA.3528@.TK2MSFTNGP12.phx.gbl...
>|||Thanks again Dan
I agree it would be easier to run the bcp utility w/correct syntax for the
purposes intended. However it seems...I cannot get it to run:
C:\>bcp adventureworks.humanresources.department format nul -T -n -f
importtest-
f-n.txt -<servername>
Warning: Packetsize size must be between 512 and 65535 -- defaulting to
4096.
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a
con
nection to SQL Server [2].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while
establishing a
connection to the server. When connecting to SQL Server 2005, this failure
may
be caused by the fact that under the default settings SQL Server does not
allow
remote connections.
I have checked everything that a newbie can think of, it looks to me like
remote connections are enabled, connect via tcp/ip or named pipes, I've even
specified a port 3308 to connect on via firewall...
Ok as silly as it sounds, does bcp work with SQLexpress?...|||Hi Dale
You are missing -S<servername> is this a typo?
If you are using the default server then you can miss out specifying
completely
John
"Dale" <dale@.nospam.com> wrote in message
news:ejWme09DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Thanks again Dan
> I agree it would be easier to run the bcp utility w/correct syntax for the
> purposes intended. However it seems...I cannot get it to run:
> C:\>bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-
> f-n.txt -<servername>
> Warning: Packetsize size must be between 512 and 65535 -- defaulting to
> 4096.
> SQLState = 08001, NativeError = 2
> Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open
> a con
> nection to SQL Server [2].
> SQLState = HYT00, NativeError = 0
> Error = [Microsoft][SQL Native Client]Login timeout expired
> SQLState = 08001, NativeError = 2
> Error = [Microsoft][SQL Native Client]An error has occurred while
> establishing a
> connection to the server. When connecting to SQL Server 2005, this failure
> may
> be caused by the fact that under the default settings SQL Server does not
> allow
> remote connections.
> I have checked everything that a newbie can think of, it looks to me like
> remote connections are enabled, connect via tcp/ip or named pipes, I've
> even specified a port 3308 to connect on via firewall...
> Ok as silly as it sounds, does bcp work with SQLexpress?...
>|||In addition to John's response, the instance needs to be restarted after you
enable remote connections. You might try restarting the instance to make
sure the change isn't pending.
> I have checked everything that a newbie can think of, it looks to me like
> remote connections are enabled, connect via tcp/ip or named pipes, I've
> even specified a port 3308 to connect on via firewall...
> Ok as silly as it sounds, does bcp work with SQLexpress?...
Is this the default or named instance? How did you configure port 3308?
Note that for a named instance, you'll need to enable/start the SQL Browser
service in order to remotely connect by server\instance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dale" <dale@.nospam.com> wrote in message
news:ejWme09DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Thanks again Dan
> I agree it would be easier to run the bcp utility w/correct syntax for the
> purposes intended. However it seems...I cannot get it to run:
> C:\>bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-
> f-n.txt -<servername>
> Warning: Packetsize size must be between 512 and 65535 -- defaulting to
> 4096.
> SQLState = 08001, NativeError = 2
> Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open
> a con
> nection to SQL Server [2].
> SQLState = HYT00, NativeError = 0
> Error = [Microsoft][SQL Native Client]Login timeout expired
> SQLState = 08001, NativeError = 2
> Error = [Microsoft][SQL Native Client]An error has occurred while
> establishing a
> connection to the server. When connecting to SQL Server 2005, this failure
> may
> be caused by the fact that under the default settings SQL Server does not
> allow
> remote connections.
> I have checked everything that a newbie can think of, it looks to me like
> remote connections are enabled, connect via tcp/ip or named pipes, I've
> even specified a port 3308 to connect on via firewall...
> Ok as silly as it sounds, does bcp work with SQLexpress?...
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:574887
Dale (dale@.nospam.com) writes:
> Thanks Dan...this certainly got complicated fast!! I'm guessing this
> format file is likened to a specifications file in msaccess. At any
> rate, when trying to run the bcp utility:
> bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-f-n.txt
> I receive an error, unable to open a connection...that remote access may
> not be enabled? I've checked using the surface configuration tool and
> remote looks to be enabled
You probably need to add:
-S .\SQLEXPRESS
if you leave out the server name, BCP tries to connect to the default
instance. But by default SQL Express installs as a *named* instance with
the name SQLEXPRESS. (You can have several instances of SQL Server on
your machine, but only one can be the default instance.)
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
I'm very new to SQLExpress and I'm having difficulty with a procedure I
found on the net
to import multiple files into a table:
http://www.databasejournal.com/feat...cle.php/3325701
The method I'm using is method one.
I've done everything as outlined, however when running the procedure within
SQLexpress I receive the error message
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
I'm presuming it has something to do with the Fieldterminator remark, the
double quote and comma specified in the bulk insert statement:
set @.Query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
WITH
( FIELDTERMINATOR = "," , ROWTERMINATOR = "\n")'
I've tried running the bulk insert with single quotes and it runs fine..with
double quotes not so good I get the same error so thats why I think its a
syntax error...but when I replace single quotes in the procedure, the
procedure doesn't compile...i.e.
set @.Query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
WITH
( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n')'
....I'm at a loss as to how to fix it!
Could someone help please, thanks so much.Try using single quotes instead of double quotes to delimit literals so that
you aren't sensitive to your QUOTED_IDENTIFIER setting. When you need a
quote within a literal, specify 2 single quotes:
set @.Query ='BULK INSERT '+ @.Tablename + ' FROM '''+ @.Filepath+@.Filename+'''
WITH
( FIELDTERMINATOR = '','' , ROWTERMINATOR = ''\n'')'
Happy Holidays
Dan Guzman
SQL Server MVP
"Dale" <dale@.nospam.com> wrote in message
news:es09z0vDGHA.472@.TK2MSFTNGP12.phx.gbl...
> Hi everyone one,
> I'm very new to SQLExpress and I'm having difficulty with a procedure I
> found on the net
> to import multiple files into a table:
> http://www.databasejournal.com/feat...cle.php/3325701
> The method I'm using is method one.
> I've done everything as outlined, however when running the procedure
> within SQLexpress I receive the error message
> Msg 102, Level 15, State 1, Line 2
> Incorrect syntax near ','.
> I'm presuming it has something to do with the Fieldterminator remark, the
> double quote and comma specified in the bulk insert statement:
> set @.Query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
> WITH
> ( FIELDTERMINATOR = "," , ROWTERMINATOR = "\n")'
> I've tried running the bulk insert with single quotes and it runs
> fine..with double quotes not so good I get the same error so thats why I
> think its a syntax error...but when I replace single quotes in the
> procedure, the procedure doesn't compile...i.e.
> set @.Query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
> WITH
> ( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n')'
> ....I'm at a loss as to how to fix it!
> Could someone help please, thanks so much.
>
>|||Thanks Dan, that worked, now on to my "real" data...
the fields are separated by a comma but enclosed in a double quote i.e.
"12345","67899"
How do I get the field terminator set correctly? I just know you're going
to
tell me to remove the double quotes...(sigh)...theres a ton of files...
Thanks again
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OX$4aOwDGHA.344@.TK2MSFTNGP11.phx.gbl...
> Try using single quotes instead of double quotes to delimit literals so
> that you aren't sensitive to your QUOTED_IDENTIFIER setting. When you
> need a quote within a literal, specify 2 single quotes:
> set @.Query ='BULK INSERT '+ @.Tablename + ' FROM '''+
> @.Filepath+@.Filename+'''
> WITH
> ( FIELDTERMINATOR = '','' , ROWTERMINATOR = ''\n'')'
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Dale" <dale@.nospam.com> wrote in message
> news:es09z0vDGHA.472@.TK2MSFTNGP12.phx.gbl...
>|||You need to create a format file when you have a more complicated file
format. Sample SQL 2000 format file:
8.0
3
1 SQLCHAR 0 1 "\"" 0 quote ""
2 SQLCHAR 0 10 "\",\"" 1 Col1 ""
3 SQLCHAR 0 10 "\"\r\n" 2 Col2 ""
Specify the format file using FORMATFILE in your BULK INSERT statement
instead of FIELDTERMINATOR and ROWTERMINATOR:
set @.Query ='BULK INSERT '+ @.Tablename + ' FROM '''+
@.Filepath+@.Filename+'''
WITH
( FORMATFILE = ''' + @.FormatFilename + ''')'
See the Books Online for format file details.
Happy Holidays
Dan Guzman
SQL Server MVP
"Dale" <dale@.nospam.com> wrote in message
news:OtBJ2LxDGHA.3064@.TK2MSFTNGP10.phx.gbl...
> Thanks Dan, that worked, now on to my "real" data...
> the fields are separated by a comma but enclosed in a double quote i.e.
> "12345","67899"
> How do I get the field terminator set correctly? I just know you're going
> to
> tell me to remove the double quotes...(sigh)...theres a ton of files...
> Thanks again
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OX$4aOwDGHA.344@.TK2MSFTNGP11.phx.gbl...
>|||Thanks Dan...this certainly got complicated fast!! I'm guessing this format
file is likened to a specifications file in msaccess.
At any rate, when trying to run the bcp utility:
bcp adventureworks.humanresources.department format nul -T -n -f
importtest-f-n.txt
I receive an error, unable to open a connection...that remote access may not
be enabled? I've checked using the surface configuration tool and remote
looks to be enabled.
Secondly if I get this bcp utililty to work..can it be run against a "txt"
file (haven't been able to get this to work either)..it doesn't make sense
to me to run the utility against the table rec'ving the data if it isn't the
source of the import?...I can't see any other way to create this format file
other than manually?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eqAaAwxDGHA.3528@.TK2MSFTNGP12.phx.gbl...
> You need to create a format file when you have a more complicated file
> format. Sample SQL 2000 format file:
> 8.0
> 3
> 1 SQLCHAR 0 1 "\"" 0 quote ""
> 2 SQLCHAR 0 10 "\",\"" 1 Col1 ""
> 3 SQLCHAR 0 10 "\"\r\n" 2 Col2 ""
> Specify the format file using FORMATFILE in your BULK INSERT statement
> instead of FIELDTERMINATOR and ROWTERMINATOR:
> set @.Query ='BULK INSERT '+ @.Tablename + ' FROM '''+
> @.Filepath+@.Filename+'''
> WITH
> ( FORMATFILE = ''' + @.FormatFilename + ''')'
> See the Books Online for format file details.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Dale" <dale@.nospam.com> wrote in message
> news:OtBJ2LxDGHA.3064@.TK2MSFTNGP10.phx.gbl...
>|||> bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-f-n.txt
Since you haven't specified a server, BCP will connect to the default
instance on the local machine. Is that your intent?
> it doesn't make sense to me to run the utility against the table rec'ving
> the data if it isn't the source of the import?
A format file describes the format of your text file (source or target).
When you run BCP using the 'format' option, the table is neither a source
nor target; the table schema is used to facilitate creating a format file
with one field per column. Since you have specified '-n', the generated
format file will be appropriate only for native file format rather than the
comma-delimited/quoted field format you specified in your original post.
> I can't see any other way to create this format file other than manually?
Although BCP can't directly create a format file for a
comma-delimited/quoted field format, you can use BCP to create a default
character format file and then change the generated to match your actual
file. That way, you at least won't need to enter all the columns. For
example:
bcp adventureworks.humanresources.department format
nul -T -c -fimporttest-f-n.txt -SMyServer
Hope this helps.
Dan Guzman
SQL Server MVP
"Dale" <dale@.nospam.com> wrote in message
news:uMsEFo7DGHA.516@.TK2MSFTNGP15.phx.gbl...
> Thanks Dan...this certainly got complicated fast!! I'm guessing this
> format file is likened to a specifications file in msaccess.
> At any rate, when trying to run the bcp utility:
> bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-f-n.txt
> I receive an error, unable to open a connection...that remote access may
> not be enabled? I've checked using the surface configuration tool and
> remote looks to be enabled.
> Secondly if I get this bcp utililty to work..can it be run against a "txt"
> file (haven't been able to get this to work either)..it doesn't make sense
> to me to run the utility against the table rec'ving the data if it isn't
> the source of the import?...I can't see any other way to create this
> format file other than manually?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eqAaAwxDGHA.3528@.TK2MSFTNGP12.phx.gbl...
>|||Thanks again Dan
I agree it would be easier to run the bcp utility w/correct syntax for the
purposes intended. However it seems...I cannot get it to run:
C:\>bcp adventureworks.humanresources.department format nul -T -n -f
importtest-
f-n.txt -<servername>
Warning: Packetsize size must be between 512 and 65535 -- defaulting to
4096.
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a
con
nection to SQL Server [2].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while
establishing a
connection to the server. When connecting to SQL Server 2005, this failure
may
be caused by the fact that under the default settings SQL Server does not
allow
remote connections.
I have checked everything that a newbie can think of, it looks to me like
remote connections are enabled, connect via tcp/ip or named pipes, I've even
specified a port 3308 to connect on via firewall...
Ok as silly as it sounds, does bcp work with SQLexpress?...|||Hi Dale
You are missing -S<servername> is this a typo?
If you are using the default server then you can miss out specifying
completely
John
"Dale" <dale@.nospam.com> wrote in message
news:ejWme09DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Thanks again Dan
> I agree it would be easier to run the bcp utility w/correct syntax for the
> purposes intended. However it seems...I cannot get it to run:
> C:\>bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-
> f-n.txt -<servername>
> Warning: Packetsize size must be between 512 and 65535 -- defaulting to
> 4096.
> SQLState = 08001, NativeError = 2
> Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open
> a con
> nection to SQL Server [2].
> SQLState = HYT00, NativeError = 0
> Error = [Microsoft][SQL Native Client]Login timeout expired
> SQLState = 08001, NativeError = 2
> Error = [Microsoft][SQL Native Client]An error has occurred while
> establishing a
> connection to the server. When connecting to SQL Server 2005, this failure
> may
> be caused by the fact that under the default settings SQL Server does not
> allow
> remote connections.
> I have checked everything that a newbie can think of, it looks to me like
> remote connections are enabled, connect via tcp/ip or named pipes, I've
> even specified a port 3308 to connect on via firewall...
> Ok as silly as it sounds, does bcp work with SQLexpress?...
>|||In addition to John's response, the instance needs to be restarted after you
enable remote connections. You might try restarting the instance to make
sure the change isn't pending.
> I have checked everything that a newbie can think of, it looks to me like
> remote connections are enabled, connect via tcp/ip or named pipes, I've
> even specified a port 3308 to connect on via firewall...
> Ok as silly as it sounds, does bcp work with SQLexpress?...
Is this the default or named instance? How did you configure port 3308?
Note that for a named instance, you'll need to enable/start the SQL Browser
service in order to remotely connect by server\instance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dale" <dale@.nospam.com> wrote in message
news:ejWme09DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Thanks again Dan
> I agree it would be easier to run the bcp utility w/correct syntax for the
> purposes intended. However it seems...I cannot get it to run:
> C:\>bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-
> f-n.txt -<servername>
> Warning: Packetsize size must be between 512 and 65535 -- defaulting to
> 4096.
> SQLState = 08001, NativeError = 2
> Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open
> a con
> nection to SQL Server [2].
> SQLState = HYT00, NativeError = 0
> Error = [Microsoft][SQL Native Client]Login timeout expired
> SQLState = 08001, NativeError = 2
> Error = [Microsoft][SQL Native Client]An error has occurred while
> establishing a
> connection to the server. When connecting to SQL Server 2005, this failure
> may
> be caused by the fact that under the default settings SQL Server does not
> allow
> remote connections.
> I have checked everything that a newbie can think of, it looks to me like
> remote connections are enabled, connect via tcp/ip or named pipes, I've
> even specified a port 3308 to connect on via firewall...
> Ok as silly as it sounds, does bcp work with SQLexpress?...
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:574887
Dale (dale@.nospam.com) writes:
> Thanks Dan...this certainly got complicated fast!! I'm guessing this
> format file is likened to a specifications file in msaccess. At any
> rate, when trying to run the bcp utility:
> bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-f-n.txt
> I receive an error, unable to open a connection...that remote access may
> not be enabled? I've checked using the surface configuration tool and
> remote looks to be enabled
You probably need to add:
-S .\SQLEXPRESS
if you leave out the server name, BCP tries to connect to the default
instance. But by default SQL Express installs as a *named* instance with
the name SQLEXPRESS. (You can have several instances of SQL Server on
your machine, but only one can be the default instance.)
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
multiple exports files from one report?
Hi
I have a simple report I would like to run for 60 users. Is there a way to
have one report cycle though a dataset and create 60 exported .mhtml files? I
really don't want to create 60 reports and 60 subscriptions :(
All the exported files would reside in the same folder.
Any help would be much appreciated!
Thanks,
MarcusHi,
You should take a look at the Data Driven subscription. This will enable to
generate multiple reports, with different parameters, just the way you want.
For more info:
http://msdn.microsoft.com/library/en-us/rswork/htm/rms_subscribing_v1_7oj9.asp
--
| Jan Pieter Posthuma
--
"Marcus K" wrote:
> Hi
> I have a simple report I would like to run for 60 users. Is there a way to
> have one report cycle though a dataset and create 60 exported .mhtml files? I
> really don't want to create 60 reports and 60 subscriptions :(
> All the exported files would reside in the same folder.
> Any help would be much appreciated!
> Thanks,
> Marcus|||Thanks, I'm not sure what I've done, but I can't get the data-driven
subscrition option to show up. I've already stored the credentials in the
report. It must be something with the security on the site, but I've got
everyone as Site Administration during our testing... weird
"Jan Pieter Posthuma" wrote:
> Hi,
> You should take a look at the Data Driven subscription. This will enable to
> generate multiple reports, with different parameters, just the way you want.
> For more info:
> http://msdn.microsoft.com/library/en-us/rswork/htm/rms_subscribing_v1_7oj9.asp
> --
> | Jan Pieter Posthuma
> --
>
> "Marcus K" wrote:
> > Hi
> >
> > I have a simple report I would like to run for 60 users. Is there a way to
> > have one report cycle though a dataset and create 60 exported .mhtml files? I
> > really don't want to create 60 reports and 60 subscriptions :(
> >
> > All the exported files would reside in the same folder.
> >
> > Any help would be much appreciated!
> >
> > Thanks,
> > Marcus
I have a simple report I would like to run for 60 users. Is there a way to
have one report cycle though a dataset and create 60 exported .mhtml files? I
really don't want to create 60 reports and 60 subscriptions :(
All the exported files would reside in the same folder.
Any help would be much appreciated!
Thanks,
MarcusHi,
You should take a look at the Data Driven subscription. This will enable to
generate multiple reports, with different parameters, just the way you want.
For more info:
http://msdn.microsoft.com/library/en-us/rswork/htm/rms_subscribing_v1_7oj9.asp
--
| Jan Pieter Posthuma
--
"Marcus K" wrote:
> Hi
> I have a simple report I would like to run for 60 users. Is there a way to
> have one report cycle though a dataset and create 60 exported .mhtml files? I
> really don't want to create 60 reports and 60 subscriptions :(
> All the exported files would reside in the same folder.
> Any help would be much appreciated!
> Thanks,
> Marcus|||Thanks, I'm not sure what I've done, but I can't get the data-driven
subscrition option to show up. I've already stored the credentials in the
report. It must be something with the security on the site, but I've got
everyone as Site Administration during our testing... weird
"Jan Pieter Posthuma" wrote:
> Hi,
> You should take a look at the Data Driven subscription. This will enable to
> generate multiple reports, with different parameters, just the way you want.
> For more info:
> http://msdn.microsoft.com/library/en-us/rswork/htm/rms_subscribing_v1_7oj9.asp
> --
> | Jan Pieter Posthuma
> --
>
> "Marcus K" wrote:
> > Hi
> >
> > I have a simple report I would like to run for 60 users. Is there a way to
> > have one report cycle though a dataset and create 60 exported .mhtml files? I
> > really don't want to create 60 reports and 60 subscriptions :(
> >
> > All the exported files would reside in the same folder.
> >
> > Any help would be much appreciated!
> >
> > Thanks,
> > Marcus
Multiple docs open in Query Analyzer
Is there a keystroke such as "CTRL-N" that would allow me to have
multiple .SQL files open at once in SQL Query Analyzer?
multiple .SQL files open at once in SQL Query Analyzer?
I feel like I am "jumping through hoops" to edit multiple documents.
I have to keep selecting "connect" from the menu bar to get a new
document for editing.
TIA-
Brian> Is there a keystroke such as "CTRL-N" that would allow me to have
> multiple .SQL files open at once in SQL Query Analyzer?
You wrote it but you didn't try it? :)
CTRL+N or click New Query in the Toolbar (first button on the left). That
opens a new window/connection using the current login and then you can open
a document in that window.
--
David Portas
SQL Server MVP
--|||Thanks, David.
I am so embarassed by my lack of faith in M$ to incorporate common
features in their apps. Will be much more intuitive next time.
Brian
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<WOKdnZit74Ffm-ndRVn_iw@.giganews.com>...
> > Is there a keystroke such as "CTRL-N" that would allow me to have
> > multiple .SQL files open at once in SQL Query Analyzer?
> You wrote it but you didn't try it? :)
> CTRL+N or click New Query in the Toolbar (first button on the left). That
> opens a new window/connection using the current login and then you can open
> a document in that window.
Subscribe to:
Posts (Atom)