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 column to VLDB 200GB table

The column I'm adding needs to be part of the clustered PK (it will be the
last of three columns) so I need to recreate all the indexes.
My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've tried
two methods so far.
Method 1:
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_copyoftablewithnewfield
(
) ON PRIMARY
IF EXISTS(SELECT * FROM dbo.originaltable)
EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields>)
SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.originaltable
GO
EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
'OBJECT'
GO
<recreate PK constraint>
<rebuild indexes>
COMMIT
Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
Con's: Tons of wasted space and time. It took about 15 hours.
Method 2:
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
<drop PK constraint>
<drop indexes>
ALTER TABLE [dbo].[originaltable] ADD
[newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_newfield] DEFAULT
((1))
<recreate PK constraint>
<rebuild indexes>
COMMIT TRANSACTION
Pro's: No making a copy of the entire table taking up 200GB more space in
the db data file
Con's: My tempdb grew to accomodate the row versioning info for every row in
the 200GB table. It took over 30 hours.
A lot of time and disk space is wasted with both.
Since the db is going to be unavailable to users I have some flexibility
here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then trying
method 2 again which should stop the versioning in tempdb and then turning it
back on.
I was also curious if setting the database recovery mode to SIMPLE would cut
down on db log usage and then I could set it back to FULL when done.
Do these really need to be in a transaction? If there's some hardware
failure or something unexpected I can just restore from backup and do the
conversion again. If the presence of the transaction itself is causing more
disk usage for logging or any other slowdown, I think I'd rather do without.
Given the amount of time this conversion takes, I wanted to get some
feedback other than "just try it" before doing any new tests.
Thanks.Demi
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
It is always worth taking backup of the database or snapshot as you are in
SQL Server 2005.
I'd use ALTER TABLE... (set it up with SIMPLE and ALLOW_SNAPSHOT_ISOLATION
OFF ) approach and there is no need BEGIN TRAN ....
"Demi" <Demi@.discussions.microsoft.com> wrote in message
news:D0A356E6-BFB7-498F-B5FA-871B6D3AFD75@.microsoft.com...
> The column I'm adding needs to be part of the clustered PK (it will be the
> last of three columns) so I need to recreate all the indexes.
> My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've
> tried
> two methods so far.
> Method 1:
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_copyoftablewithnewfield
> (
> ) ON PRIMARY
> IF EXISTS(SELECT * FROM dbo.originaltable)
> EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields>)
> SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK
> TABLOCKX)')
> GO
> DROP TABLE dbo.originaltable
> GO
> EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
> 'OBJECT'
> GO
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT
> Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
> Con's: Tons of wasted space and time. It took about 15 hours.
> Method 2:
> SET XACT_ABORT ON
> GO
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> BEGIN TRANSACTION
> <drop PK constraint>
> <drop indexes>
> ALTER TABLE [dbo].[originaltable] ADD
> [newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_newfield]
> DEFAULT
> ((1))
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT TRANSACTION
> Pro's: No making a copy of the entire table taking up 200GB more space in
> the db data file
> Con's: My tempdb grew to accomodate the row versioning info for every row
> in
> the 200GB table. It took over 30 hours.
> A lot of time and disk space is wasted with both.
> Since the db is going to be unavailable to users I have some flexibility
> here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then
> trying
> method 2 again which should stop the versioning in tempdb and then turning
> it
> back on.
> I was also curious if setting the database recovery mode to SIMPLE would
> cut
> down on db log usage and then I could set it back to FULL when done.
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
> Given the amount of time this conversion takes, I wanted to get some
> feedback other than "just try it" before doing any new tests.
> Thanks.sql

Adding a column to VLDB 200GB table

The column I'm adding needs to be part of the clustered PK (it will be the
last of three columns) so I need to recreate all the indexes.
My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've tried
two methods so far.
Method 1:
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_copyoftablewithnewfield
(
) ON PRIMARY
IF EXISTS(SELECT * FROM dbo.originaltable)
EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields>)
SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.originaltable
GO
EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
'OBJECT'
GO
<recreate PK constraint>
<rebuild indexes>
COMMIT
Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
Con's: Tons of wasted space and time. It took about 15 hours.
Method 2:
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
<drop PK constraint>
<drop indexes>
ALTER TABLE [dbo].[originaltable] ADD
[newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_newfield] DEFAULT
((1))
<recreate PK constraint>
<rebuild indexes>
COMMIT TRANSACTION
Pro's: No making a copy of the entire table taking up 200GB more space in
the db data file
Con's: My tempdb grew to accomodate the row versioning info for every row in
the 200GB table. It took over 30 hours.
A lot of time and disk space is wasted with both.
Since the db is going to be unavailable to users I have some flexibility
here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then trying
method 2 again which should stop the versioning in tempdb and then turning it
back on.
I was also curious if setting the database recovery mode to SIMPLE would cut
down on db log usage and then I could set it back to FULL when done.
Do these really need to be in a transaction? If there's some hardware
failure or something unexpected I can just restore from backup and do the
conversion again. If the presence of the transaction itself is causing more
disk usage for logging or any other slowdown, I think I'd rather do without.
Given the amount of time this conversion takes, I wanted to get some
feedback other than "just try it" before doing any new tests.
Thanks.
Demi
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
It is always worth taking backup of the database or snapshot as you are in
SQL Server 2005.
I'd use ALTER TABLE... (set it up with SIMPLE and ALLOW_SNAPSHOT_ISOLATION
OFF ) approach and there is no need BEGIN TRAN ....
"Demi" <Demi@.discussions.microsoft.com> wrote in message
news:D0A356E6-BFB7-498F-B5FA-871B6D3AFD75@.microsoft.com...
> The column I'm adding needs to be part of the clustered PK (it will be the
> last of three columns) so I need to recreate all the indexes.
> My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've
> tried
> two methods so far.
> Method 1:
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_copyoftablewithnewfield
> (
> ) ON PRIMARY
> IF EXISTS(SELECT * FROM dbo.originaltable)
> EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields>)
> SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK
> TABLOCKX)')
> GO
> DROP TABLE dbo.originaltable
> GO
> EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
> 'OBJECT'
> GO
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT
> Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
> Con's: Tons of wasted space and time. It took about 15 hours.
> Method 2:
> SET XACT_ABORT ON
> GO
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> BEGIN TRANSACTION
> <drop PK constraint>
> <drop indexes>
> ALTER TABLE [dbo].[originaltable] ADD
> [newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_newfield]
> DEFAULT
> ((1))
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT TRANSACTION
> Pro's: No making a copy of the entire table taking up 200GB more space in
> the db data file
> Con's: My tempdb grew to accomodate the row versioning info for every row
> in
> the 200GB table. It took over 30 hours.
> A lot of time and disk space is wasted with both.
> Since the db is going to be unavailable to users I have some flexibility
> here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then
> trying
> method 2 again which should stop the versioning in tempdb and then turning
> it
> back on.
> I was also curious if setting the database recovery mode to SIMPLE would
> cut
> down on db log usage and then I could set it back to FULL when done.
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
> Given the amount of time this conversion takes, I wanted to get some
> feedback other than "just try it" before doing any new tests.
> Thanks.

Adding a column to VLDB 200GB table

The column I'm adding needs to be part of the clustered PK (it will be the
last of three columns) so I need to recreate all the indexes.
My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've tried
two methods so far.
Method 1:
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_copyoftablewithnewfield
(
) ON PRIMARY
IF EXISTS(SELECT * FROM dbo.originaltable)
EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields> )
SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.originaltable
GO
EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
'OBJECT'
GO
<recreate PK constraint>
<rebuild indexes>
COMMIT
Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
Con's: Tons of wasted space and time. It took about 15 hours.
Method 2:
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
<drop PK constraint>
<drop indexes>
ALTER TABLE [dbo].[originaltable] ADD
[newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_newfi
eld] DEFAULT
((1))
<recreate PK constraint>
<rebuild indexes>
COMMIT TRANSACTION
Pro's: No making a copy of the entire table taking up 200GB more space in
the db data file
Con's: My tempdb grew to accomodate the row versioning info for every row in
the 200GB table. It took over 30 hours.
A lot of time and disk space is wasted with both.
Since the db is going to be unavailable to users I have some flexibility
here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then tryin
g
method 2 again which should stop the versioning in tempdb and then turning i
t
back on.
I was also curious if setting the database recovery mode to SIMPLE would cut
down on db log usage and then I could set it back to FULL when done.
Do these really need to be in a transaction? If there's some hardware
failure or something unexpected I can just restore from backup and do the
conversion again. If the presence of the transaction itself is causing more
disk usage for logging or any other slowdown, I think I'd rather do without.
Given the amount of time this conversion takes, I wanted to get some
feedback other than "just try it" before doing any new tests.
Thanks.Demi
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
It is always worth taking backup of the database or snapshot as you are in
SQL Server 2005.
I'd use ALTER TABLE... (set it up with SIMPLE and ALLOW_SNAPSHOT_ISOLATION
OFF ) approach and there is no need BEGIN TRAN ....
"Demi" <Demi@.discussions.microsoft.com> wrote in message
news:D0A356E6-BFB7-498F-B5FA-871B6D3AFD75@.microsoft.com...
> The column I'm adding needs to be part of the clustered PK (it will be the
> last of three columns) so I need to recreate all the indexes.
> My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've
> tried
> two methods so far.
> Method 1:
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_copyoftablewithnewfield
> (
> ) ON PRIMARY
> IF EXISTS(SELECT * FROM dbo.originaltable)
> EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields> )
> SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK
> TABLOCKX)')
> GO
> DROP TABLE dbo.originaltable
> GO
> EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
> 'OBJECT'
> GO
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT
> Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
> Con's: Tons of wasted space and time. It took about 15 hours.
> Method 2:
> SET XACT_ABORT ON
> GO
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> BEGIN TRANSACTION
> <drop PK constraint>
> <drop indexes>
> ALTER TABLE [dbo].[originaltable] ADD
> [newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_new
field]
> DEFAULT
> ((1))
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT TRANSACTION
> Pro's: No making a copy of the entire table taking up 200GB more space in
> the db data file
> Con's: My tempdb grew to accomodate the row versioning info for every row
> in
> the 200GB table. It took over 30 hours.
> A lot of time and disk space is wasted with both.
> Since the db is going to be unavailable to users I have some flexibility
> here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then
> trying
> method 2 again which should stop the versioning in tempdb and then turning
> it
> back on.
> I was also curious if setting the database recovery mode to SIMPLE would
> cut
> down on db log usage and then I could set it back to FULL when done.
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
> Given the amount of time this conversion takes, I wanted to get some
> feedback other than "just try it" before doing any new tests.
> Thanks.