Adding a new datafile

Hi:
I have SQL Server 2000; a database with three file groups located in three
different operating system files; the database size is 200GB; for
performance reasons we added an additional storage to the server and we have
an additional disk volume, what we want to do is to add a new datafile
(datafile4) and then use any command or procedure to equally split database
information (data) among the four datafiles.
How this split can be safely performed ?
Any idea or procedure ?
Thanks and best regards,
FedericoThere is no command that will automatically split the underlying table data
across all 4 data devices.
The process of moving tables to different filegroups is manual, but if
planned out correctly, can greatly improve the DBs read/write performance,
especially if the underlying data devices/files are on seperate drives.
You will need to decide how to place the various tables in your database
across the 4 files/filegroups. You may choose to place often accessed table
s
on different filegroups to improve performance when these table are accessed
simultaneously. Alternatively you may also want to place any 'archive' or
'historical' tables on a seperate data device as well. You may also choose
to place certain table's indexes on seperate filegroups then the actual
table. Which tables are moved to which filegroups and which drives those
filegroup's files reside on largely depends on the database's schema and th
e
way in which the database's tables are accessed.
Once you hae created the new data device and file group, search BOL for
"Placing Tables on Filegroups' to find out how to move existing tables
between filegroups.
Regards,
Dave
"Federico G. Babelis" wrote:

> Hi:
>
> I have SQL Server 2000; a database with three file groups located in three
> different operating system files; the database size is 200GB; for
> performance reasons we added an additional storage to the server and we ha
ve
> an additional disk volume, what we want to do is to add a new datafile
> (datafile4) and then use any command or procedure to equally split databas
e
> information (data) among the four datafiles.
>
> How this split can be safely performed ?
> Any idea or procedure ?
>
> Thanks and best regards,
> Federico
>
>
>

Adding a new datafile

Hi:
I have SQL Server 2000; a database with three file groups located in three
different operating system files; the database size is 200GB; for
performance reasons we added an additional storage to the server and we have
an additional disk volume, what we want to do is to add a new datafile
(datafile4) and then use any command or procedure to equally split database
information (data) among the four datafiles.
How this split can be safely performed ?
Any idea or procedure ?
Thanks and best regards,
FedericoThere is no command that will automatically split the underlying table data
across all 4 data devices.
The process of moving tables to different filegroups is manual, but if
planned out correctly, can greatly improve the DBs read/write performance,
especially if the underlying data devices/files are on seperate drives.
You will need to decide how to place the various tables in your database
across the 4 files/filegroups. You may choose to place often accessed tables
on different filegroups to improve performance when these table are accessed
simultaneously. Alternatively you may also want to place any 'archive' or
'historical' tables on a seperate data device as well. You may also choose
to place certain table's indexes on seperate filegroups then the actual
table. Which tables are moved to which filegroups and which drives those
filegroup's files reside on largely depends on the database's schema and the
way in which the database's tables are accessed.
Once you hae created the new data device and file group, search BOL for
"Placing Tables on Filegroups' to find out how to move existing tables
between filegroups.
Regards,
Dave
"Federico G. Babelis" wrote:
> Hi:
>
> I have SQL Server 2000; a database with three file groups located in three
> different operating system files; the database size is 200GB; for
> performance reasons we added an additional storage to the server and we have
> an additional disk volume, what we want to do is to add a new datafile
> (datafile4) and then use any command or procedure to equally split database
> information (data) among the four datafiles.
>
> How this split can be safely performed ?
> Any idea or procedure ?
>
> Thanks and best regards,
> Federico
>
>
>sql

Adding a Log Provider

Hi

I've added a log provider to several packages, and for some reason, everytime I execute them, I end up with 2 log files. The first one is the right log file I was waiting for, and the second one is an almost empty file in my Desktop, that only contains this line

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

I would really appreciatte if anyone could tell me what am I possibly doing wrong.

Thanks.

Does the text log provider use a connection manager with dynamic connection string, e.g. a connection string which is an expression, or uses a value which changes during package lifetime (like the milliseconds of GETDATE()), or uses a variable which itself is an expression?

If the log name is dynamic to the point where it may change within the package lifetime itself (between the validation and execution times), its quite possible to end up with two files names.

Two files may be created because the log provider (at least the stock text log provider) writes to the connection manager's path once during validation, and a second time during execution. There is a sample log provider called the HtmlLogProvider in the SQL Server samples which writes the log file name during the log provider's CloseLog method, and so will produce a single file name. Other alternates to the text log provider include the stock SQL Server log provider, or simply capturing the output of dtexec, if the package is so executed.

|||

Hi

I'm using an expression to build the connection string for the Text Log Provider. Something like:

@.[User::LogPath] + "logfilename.log"

The idea was to be able to dynamically change the path log files are going to be saved to, something I will probably never do. You know how these things are. I will check the sample log provider you mention.

Thanks a lot

|||Do you set the LogPath variable dynamically?|||

Hi

If by dynamically you mean if I change its value during the package excution, the answer is no. The variable 'LogPath' holds the path where the log file will be created, and the LogProvider connection string is set using the expression in the previous post.

Cheers.

|||

Hi jgomez,

1) Double click the SSIS Log Provider Connection Manager and check if it uses the Existing file or Creates a new File.

2) Assign the following expression for Connections String Property of SSIS Log Provider Connection Manager:

@.[User::LogPath] + Logfile.log

Thanks

Subhash Subramanyam

|||

Hi

I think I know why I had this problem. I'm invoking several packages from my SSIS, using package configurations to set the value of the LogPath variable . Everytime a new package is invoked, using the parent package variable to create the log provider, I end up with 2 differents log files as previously stated.

So I guess jaegd and jwelch were right, and this behaviour is caused by the evaluation of the log path during validation and execution times.

Thank you

Cheers

|||Logging starts before parent package variable configurations are applied. So it would start the log with the original value (saved in the package at design time), then start a new log file when the parent package variable was applied.sql

Adding a Log Provider

Hi

I've added a log provider to several packages, and for some reason, everytime I execute them, I end up with 2 log files. The first one is the right log file I was waiting for, and the second one is an almost empty file in my Desktop, that only contains this line

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

I would really appreciatte if anyone could tell me what am I possibly doing wrong.

Thanks.

Does the text log provider use a connection manager with dynamic connection string, e.g. a connection string which is an expression, or uses a value which changes during package lifetime (like the milliseconds of GETDATE()), or uses a variable which itself is an expression?

If the log name is dynamic to the point where it may change within the package lifetime itself (between the validation and execution times), its quite possible to end up with two files names.

Two files may be created because the log provider (at least the stock text log provider) writes to the connection manager's path once during validation, and a second time during execution. There is a sample log provider called the HtmlLogProvider in the SQL Server samples which writes the log file name during the log provider's CloseLog method, and so will produce a single file name. Other alternates to the text log provider include the stock SQL Server log provider, or simply capturing the output of dtexec, if the package is so executed.

|||

Hi

I'm using an expression to build the connection string for the Text Log Provider. Something like:

@.[User::LogPath] + "logfilename.log"

The idea was to be able to dynamically change the path log files are going to be saved to, something I will probably never do. You know how these things are. I will check the sample log provider you mention.

Thanks a lot

|||Do you set the LogPath variable dynamically?|||

Hi

If by dynamically you mean if I change its value during the package excution, the answer is no. The variable 'LogPath' holds the path where the log file will be created, and the LogProvider connection string is set using the expression in the previous post.

Cheers.

|||

Hi jgomez,

1) Double click the SSIS Log Provider Connection Manager and check if it uses the Existing file or Creates a new File.

2) Assign the following expression for Connections String Property of SSIS Log Provider Connection Manager:

@.[User::LogPath] + Logfile.log

Thanks

Subhash Subramanyam

|||

Hi

I think I know why I had this problem. I'm invoking several packages from my SSIS, using package configurations to set the value of the LogPath variable . Everytime a new package is invoked, using the parent package variable to create the log provider, I end up with 2 differents log files as previously stated.

So I guess jaegd and jwelch were right, and this behaviour is caused by the evaluation of the log path during validation and execution times.

Thank you

Cheers

|||Logging starts before parent package variable configurations are applied. So it would start the log with the original value (saved in the package at design time), then start a new log file when the parent package variable was applied.

Adding a Log Provider

Hi

I've added a log provider to several packages, and for some reason, everytime I execute them, I end up with 2 log files. The first one is the right log file I was waiting for, and the second one is an almost empty file in my Desktop, that only contains this line

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

I would really appreciatte if anyone could tell me what am I possibly doing wrong.

Thanks.

Does the text log provider use a connection manager with dynamic connection string, e.g. a connection string which is an expression, or uses a value which changes during package lifetime (like the milliseconds of GETDATE()), or uses a variable which itself is an expression?

If the log name is dynamic to the point where it may change within the package lifetime itself (between the validation and execution times), its quite possible to end up with two files names.

Two files may be created because the log provider (at least the stock text log provider) writes to the connection manager's path once during validation, and a second time during execution. There is a sample log provider called the HtmlLogProvider in the SQL Server samples which writes the log file name during the log provider's CloseLog method, and so will produce a single file name. Other alternates to the text log provider include the stock SQL Server log provider, or simply capturing the output of dtexec, if the package is so executed.

|||

Hi

I'm using an expression to build the connection string for the Text Log Provider. Something like:

@.[User::LogPath] + "logfilename.log"

The idea was to be able to dynamically change the path log files are going to be saved to, something I will probably never do. You know how these things are. I will check the sample log provider you mention.

Thanks a lot

|||Do you set the LogPath variable dynamically?|||

Hi

If by dynamically you mean if I change its value during the package excution, the answer is no. The variable 'LogPath' holds the path where the log file will be created, and the LogProvider connection string is set using the expression in the previous post.

Cheers.

|||

Hi jgomez,

1) Double click the SSIS Log Provider Connection Manager and check if it uses the Existing file or Creates a new File.

2) Assign the following expression for Connections String Property of SSIS Log Provider Connection Manager:

@.[User::LogPath] + Logfile.log

Thanks

Subhash Subramanyam

|||

Hi

I think I know why I had this problem. I'm invoking several packages from my SSIS, using package configurations to set the value of the LogPath variable . Everytime a new package is invoked, using the parent package variable to create the log provider, I end up with 2 differents log files as previously stated.

So I guess jaegd and jwelch were right, and this behaviour is caused by the evaluation of the log path during validation and execution times.

Thank you

Cheers

|||Logging starts before parent package variable configurations are applied. So it would start the log with the original value (saved in the package at design time), then start a new log file when the parent package variable was applied.

add text to a column

Hi,
I am mapping the .csv files to a destination table in sql server.
The names of each column in the .csv file is the same as the ones in the destination table.
In the destination table there is an extra field i.e. FileTypeField

Here is the question;
When transfering the data from .csv to the destination table i.e flat file source to oledb destination, I would like to place a text into the field FileTypeField
I do not see an option to do this. Can you help please?
Thanks

Use a derived column transformation to add a column to the data flow. Then you'll just map that new column to the destination table.|||Thanks

Add data files to tempdb.

Hi,
According KB article 328551 FIX: Concurrency enhancements for the tempdb
database. I like to add data files to my tempdb in Prod server. Please help
me check if the following statements are right if the server has 4 CPUs.
alter database tempdb
modify file (name = 'tempdev', size = 250MB, maxsize = 2000MB,
filegrowth = 50MB)
go
alter database tempdb
add filegroup temp_group1
go
alter database tempdb
add file (
name = tempdev1,
filename = 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\tempdev1.ndf',
size = 250MB,
maxsize = 2000MB,
filegrowth = 50MB)
to filegroup temp_group1
go
alter database tempdb
add filegroup temp_group2
go
alter database tempdb
add file (
name = tempdev2,
filename = 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\tempdev2.ndf',
size = 250MB,
maxsize = 2000MB,
filegrowth = 50MB)
to filegroup temp_group2
go
alter database tempdb
add filegroup temp_group3
go
alter database tempdb
add file (
name = tempdev3,
filename = 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\tempdev3.ndf',
size = 250MB,
maxsize = 2000MB,
filegrowth = 50MB)
to filegroup temp_group3
go
Regards!
ChenYou cannot add new filegroups to tempdb, only files.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:2690FA6F-12B1-42FB-874D-AC3AEC0C8D0B@.microsoft.com...
> Hi,
> According KB article 328551 FIX: Concurrency enhancements for the tempdb
> database. I like to add data files to my tempdb in Prod server. Please
> help
> me check if the following statements are right if the server has 4 CPUs.
> alter database tempdb
> modify file (name = 'tempdev', size = 250MB, maxsize = 2000MB,
> filegrowth = 50MB)
> go
> alter database tempdb
> add filegroup temp_group1
> go
> alter database tempdb
> add file (
> name = tempdev1,
> filename = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\tempdev1.ndf',
> size = 250MB,
> maxsize = 2000MB,
> filegrowth = 50MB)
> to filegroup temp_group1
> go
> alter database tempdb
> add filegroup temp_group2
> go
> alter database tempdb
> add file (
> name = tempdev2,
> filename = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\tempdev2.ndf',
> size = 250MB,
> maxsize = 2000MB,
> filegrowth = 50MB)
> to filegroup temp_group2
> go
> alter database tempdb
> add filegroup temp_group3
> go
> alter database tempdb
> add file (
> name = tempdev3,
> filename = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\tempdev3.ndf',
> size = 250MB,
> maxsize = 2000MB,
> filegrowth = 50MB)
> to filegroup temp_group3
> go
> Regards!
> Chen
>

Add data files to tempdb.

Hi,
According KB article 328551 FIX: Concurrency enhancements for the tempdb
database. I like to add data files to my tempdb in Prod server. Please help
me check if the following statements are right if the server has 4 CPUs.
alter database tempdb
modify file (name = 'tempdev', size = 250MB, maxsize = 2000MB,
filegrowth = 50MB)
go
alter database tempdb
add filegroup temp_group1
go
alter database tempdb
add file (
name = tempdev1,
filename = 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\tempdev1.ndf',
size = 250MB,
maxsize = 2000MB,
filegrowth = 50MB)
to filegroup temp_group1
go
alter database tempdb
add filegroup temp_group2
go
alter database tempdb
add file (
name = tempdev2,
filename = 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\tempdev2.ndf',
size = 250MB,
maxsize = 2000MB,
filegrowth = 50MB)
to filegroup temp_group2
go
alter database tempdb
add filegroup temp_group3
go
alter database tempdb
add file (
name = tempdev3,
filename = 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\tempdev3.ndf',
size = 250MB,
maxsize = 2000MB,
filegrowth = 50MB)
to filegroup temp_group3
go
Regards!
Chen
You cannot add new filegroups to tempdb, only files.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:2690FA6F-12B1-42FB-874D-AC3AEC0C8D0B@.microsoft.com...
> Hi,
> According KB article 328551 FIX: Concurrency enhancements for the tempdb
> database. I like to add data files to my tempdb in Prod server. Please
> help
> me check if the following statements are right if the server has 4 CPUs.
> alter database tempdb
> modify file (name = 'tempdev', size = 250MB, maxsize = 2000MB,
> filegrowth = 50MB)
> go
> alter database tempdb
> add filegroup temp_group1
> go
> alter database tempdb
> add file (
> name = tempdev1,
> filename = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\tempdev1.ndf',
> size = 250MB,
> maxsize = 2000MB,
> filegrowth = 50MB)
> to filegroup temp_group1
> go
> alter database tempdb
> add filegroup temp_group2
> go
> alter database tempdb
> add file (
> name = tempdev2,
> filename = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\tempdev2.ndf',
> size = 250MB,
> maxsize = 2000MB,
> filegrowth = 50MB)
> to filegroup temp_group2
> go
> alter database tempdb
> add filegroup temp_group3
> go
> alter database tempdb
> add file (
> name = tempdev3,
> filename = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\tempdev3.ndf',
> size = 250MB,
> maxsize = 2000MB,
> filegrowth = 50MB)
> to filegroup temp_group3
> go
> Regards!
> Chen
>

Add data files to tempdb.

Hi,
According KB article 328551 FIX: Concurrency enhancements for the tempdb
database. I like to add data files to my tempdb in Prod server. Please help
me check if the following statements are right if the server has 4 CPUs.
alter database tempdb
modify file (name = 'tempdev', size = 250MB, maxsize = 2000MB,
filegrowth = 50MB)
go
alter database tempdb
add filegroup temp_group1
go
alter database tempdb
add file (
name = tempdev1,
filename = 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\tempdev1.ndf',
size = 250MB,
maxsize = 2000MB,
filegrowth = 50MB)
to filegroup temp_group1
go
alter database tempdb
add filegroup temp_group2
go
alter database tempdb
add file (
name = tempdev2,
filename = 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\tempdev2.ndf',
size = 250MB,
maxsize = 2000MB,
filegrowth = 50MB)
to filegroup temp_group2
go
alter database tempdb
add filegroup temp_group3
go
alter database tempdb
add file (
name = tempdev3,
filename = 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\tempdev3.ndf',
size = 250MB,
maxsize = 2000MB,
filegrowth = 50MB)
to filegroup temp_group3
go
Regards!
ChenYou cannot add new filegroups to tempdb, only files.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:2690FA6F-12B1-42FB-874D-AC3AEC0C8D0B@.microsoft.com...
> Hi,
> According KB article 328551 FIX: Concurrency enhancements for the tempdb
> database. I like to add data files to my tempdb in Prod server. Please
> help
> me check if the following statements are right if the server has 4 CPUs.
> alter database tempdb
> modify file (name = 'tempdev', size = 250MB, maxsize = 2000MB,
> filegrowth = 50MB)
> go
> alter database tempdb
> add filegroup temp_group1
> go
> alter database tempdb
> add file (
> name = tempdev1,
> filename = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\tempdev1.ndf',
> size = 250MB,
> maxsize = 2000MB,
> filegrowth = 50MB)
> to filegroup temp_group1
> go
> alter database tempdb
> add filegroup temp_group2
> go
> alter database tempdb
> add file (
> name = tempdev2,
> filename = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\tempdev2.ndf',
> size = 250MB,
> maxsize = 2000MB,
> filegrowth = 50MB)
> to filegroup temp_group2
> go
> alter database tempdb
> add filegroup temp_group3
> go
> alter database tempdb
> add file (
> name = tempdev3,
> filename = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\tempdev3.ndf',
> size = 250MB,
> maxsize = 2000MB,
> filegrowth = 50MB)
> to filegroup temp_group3
> go
> Regards!
> Chen
>

Add an export format

I want to add an export option for the report dropdown menus that will allow
users to export reports to tab delimited files. This will allow them to open
up the data of a report directly in excel.
Does anyone know if this can be done?First you can render a report in Excel already using one of the rendering
extensions... You may have the report rendered in excel immediately by
calling the report from a URL passing the rendering extention , or having a
user subscribe to the report in Excel.
However if you really wish to, you may write a new rendering extension which
renders a tab delimited report, and load it into reporting services, and
modify some config files... This is documented in books on line... look for
rendering extensions..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:A5CBA5C7-9FA1-40A1-8132-45DC9E838D60@.microsoft.com...
>I want to add an export option for the report dropdown menus that will
>allow
> users to export reports to tab delimited files. This will allow them to
> open
> up the data of a report directly in excel.
> Does anyone know if this can be done?|||Were you able to get this adding to the dropdown menu? -Peter
"Wayne Snyder" wrote:
> First you can render a report in Excel already using one of the rendering
> extensions... You may have the report rendered in excel immediately by
> calling the report from a URL passing the rendering extention , or having a
> user subscribe to the report in Excel.
> However if you really wish to, you may write a new rendering extension which
> renders a tab delimited report, and load it into reporting services, and
> modify some config files... This is documented in books on line... look for
> rendering extensions..
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> news:A5CBA5C7-9FA1-40A1-8132-45DC9E838D60@.microsoft.com...
> >I want to add an export option for the report dropdown menus that will
> >allow
> > users to export reports to tab delimited files. This will allow them to
> > open
> > up the data of a report directly in excel.
> >
> > Does anyone know if this can be done?
>
>