We are configuring a new database server at work. I have decided to do a RAID
1 Array for the transaction log and a RAID 5 array for the data.
The fastest growing part of our database is a table that stores photos. It
is currently about 12Gb in size. We expect it to grow the next few years by
17Gb, 26Gb, 33Gb, then platueau at 37Gb growth a year. At the end of year 5
that puts us at about 165Gb, and at the end of year 10 at about 500Gb.
Although these are rough projections, I still need to be able to begin
planning accordingly.
What is the best hardware configuration to allow this kind of growth? I
would like something that is expandable, so that I can buy according to our
current needs, and add to it as needed.
Any advice is greatly appreciated.
-Dan
Hi
In effect, you can only plan for the next 3-4 years. By then, the current
disk subsystems will need to be retired.
In effect, you need about 150Gb for Data and probably 400Gb for backups
during this period. A few 147Gb drives will do the job nicely.
For best performance have a seperate SCSI controller for the logs, backup
and data drives. You may want to split the data up into 2 drive sets to get
better throughput.
With the size and price of drives, do you really need RAID-5? What about
RAD-10? The write performance is just so much better.
Do you need high abailabilty? If so, you need to look at Windows Clusters.
This will guide you in the direction of the drive enclosure as it will need
to be a SAN. EMC has a few nice babies.
Regards
Mike
"Dan" wrote:
> We are configuring a new database server at work. I have decided to do a RAID
> 1 Array for the transaction log and a RAID 5 array for the data.
> The fastest growing part of our database is a table that stores photos. It
> is currently about 12Gb in size. We expect it to grow the next few years by
> 17Gb, 26Gb, 33Gb, then platueau at 37Gb growth a year. At the end of year 5
> that puts us at about 165Gb, and at the end of year 10 at about 500Gb.
> Although these are rough projections, I still need to be able to begin
> planning accordingly.
> What is the best hardware configuration to allow this kind of growth? I
> would like something that is expandable, so that I can buy according to our
> current needs, and add to it as needed.
> Any advice is greatly appreciated.
> -Dan
|||Hi Dan,
Statistically speaking the company you are working for will not be in
business in 10 years! Anyhow. Let me tell you what I did for one of our
businesses that had a quad-processor (700 MHZ) server running SQL Server:
1-got a dual processor (3 GHZ) server.
2-Got 15000 RPM drives (50% in disk speed increase right of the bat!)
3-Created 3 RAID 1 arrays: one for OS, one for DB and one of transaction
log. Make sure that your controller supports split seeks, which devides the
read I/Os among the mirrored pair, allowing two disks to seek simultaneously.
Ideally you can use RAID 10.
4-be carefull with the edition of SQL Server. The server edition will limit
SQL to use a max. of 2 GB. Also, you will not be able to create indexes on
views.
Microsoft SQL Administrator's companion book has a section on Capacity
Planning (for memory, processor, disk-subsystem) that you can have a look.
The problem we had with our quad-processor server was that we had to pay
close to 10,000$ to get 2 more CPUs since they were older CPUs. Note also
that storage often becomes a problem. With 200 servers to maintain by our
team, we often need to plan some upgrades for servers that are running low in
HD space (after few years) so make sure you have enough space on your
database server. Last think, it is very hard to load balance a SQL Server
(manual process as far as I know) because chances are that new servers will
get data from your database server in the future and/or the load will
increase.
"Dan" wrote:
> We are configuring a new database server at work. I have decided to do a RAID
> 1 Array for the transaction log and a RAID 5 array for the data.
> The fastest growing part of our database is a table that stores photos. It
> is currently about 12Gb in size. We expect it to grow the next few years by
> 17Gb, 26Gb, 33Gb, then platueau at 37Gb growth a year. At the end of year 5
> that puts us at about 165Gb, and at the end of year 10 at about 500Gb.
> Although these are rough projections, I still need to be able to begin
> planning accordingly.
> What is the best hardware configuration to allow this kind of growth? I
> would like something that is expandable, so that I can buy according to our
> current needs, and add to it as needed.
> Any advice is greatly appreciated.
> -Dan
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Monday, March 12, 2012
Hardware planning for growth
We are configuring a new database server at work. I have decided to do a RAID
1 Array for the transaction log and a RAID 5 array for the data.
The fastest growing part of our database is a table that stores photos. It
is currently about 12Gb in size. We expect it to grow the next few years by
17Gb, 26Gb, 33Gb, then platueau at 37Gb growth a year. At the end of year 5
that puts us at about 165Gb, and at the end of year 10 at about 500Gb.
Although these are rough projections, I still need to be able to begin
planning accordingly.
What is the best hardware configuration to allow this kind of growth? I
would like something that is expandable, so that I can buy according to our
current needs, and add to it as needed.
Any advice is greatly appreciated.
-DanHi
In effect, you can only plan for the next 3-4 years. By then, the current
disk subsystems will need to be retired.
In effect, you need about 150Gb for Data and probably 400Gb for backups
during this period. A few 147Gb drives will do the job nicely.
For best performance have a seperate SCSI controller for the logs, backup
and data drives. You may want to split the data up into 2 drive sets to get
better throughput.
With the size and price of drives, do you really need RAID-5? What about
RAD-10? The write performance is just so much better.
Do you need high abailabilty? If so, you need to look at Windows Clusters.
This will guide you in the direction of the drive enclosure as it will need
to be a SAN. EMC has a few nice babies.
Regards
Mike
"Dan" wrote:
> We are configuring a new database server at work. I have decided to do a RAID
> 1 Array for the transaction log and a RAID 5 array for the data.
> The fastest growing part of our database is a table that stores photos. It
> is currently about 12Gb in size. We expect it to grow the next few years by
> 17Gb, 26Gb, 33Gb, then platueau at 37Gb growth a year. At the end of year 5
> that puts us at about 165Gb, and at the end of year 10 at about 500Gb.
> Although these are rough projections, I still need to be able to begin
> planning accordingly.
> What is the best hardware configuration to allow this kind of growth? I
> would like something that is expandable, so that I can buy according to our
> current needs, and add to it as needed.
> Any advice is greatly appreciated.
> -Dan|||Hi Dan,
Statistically speaking the company you are working for will not be in
business in 10 years! Anyhow. Let me tell you what I did for one of our
businesses that had a quad-processor (700 MHZ) server running SQL Server:
1-got a dual processor (3 GHZ) server.
2-Got 15000 RPM drives (50% in disk speed increase right of the bat!)
3-Created 3 RAID 1 arrays: one for OS, one for DB and one of transaction
log. Make sure that your controller supports split seeks, which devides the
read I/Os among the mirrored pair, allowing two disks to seek simultaneously.
Ideally you can use RAID 10.
4-be carefull with the edition of SQL Server. The server edition will limit
SQL to use a max. of 2 GB. Also, you will not be able to create indexes on
views.
Microsoft SQL Administrator's companion book has a section on Capacity
Planning (for memory, processor, disk-subsystem) that you can have a look.
The problem we had with our quad-processor server was that we had to pay
close to 10,000$ to get 2 more CPUs since they were older CPUs. Note also
that storage often becomes a problem. With 200 servers to maintain by our
team, we often need to plan some upgrades for servers that are running low in
HD space (after few years) so make sure you have enough space on your
database server. Last think, it is very hard to load balance a SQL Server
(manual process as far as I know) because chances are that new servers will
get data from your database server in the future and/or the load will
increase.
"Dan" wrote:
> We are configuring a new database server at work. I have decided to do a RAID
> 1 Array for the transaction log and a RAID 5 array for the data.
> The fastest growing part of our database is a table that stores photos. It
> is currently about 12Gb in size. We expect it to grow the next few years by
> 17Gb, 26Gb, 33Gb, then platueau at 37Gb growth a year. At the end of year 5
> that puts us at about 165Gb, and at the end of year 10 at about 500Gb.
> Although these are rough projections, I still need to be able to begin
> planning accordingly.
> What is the best hardware configuration to allow this kind of growth? I
> would like something that is expandable, so that I can buy according to our
> current needs, and add to it as needed.
> Any advice is greatly appreciated.
> -Dan
1 Array for the transaction log and a RAID 5 array for the data.
The fastest growing part of our database is a table that stores photos. It
is currently about 12Gb in size. We expect it to grow the next few years by
17Gb, 26Gb, 33Gb, then platueau at 37Gb growth a year. At the end of year 5
that puts us at about 165Gb, and at the end of year 10 at about 500Gb.
Although these are rough projections, I still need to be able to begin
planning accordingly.
What is the best hardware configuration to allow this kind of growth? I
would like something that is expandable, so that I can buy according to our
current needs, and add to it as needed.
Any advice is greatly appreciated.
-DanHi
In effect, you can only plan for the next 3-4 years. By then, the current
disk subsystems will need to be retired.
In effect, you need about 150Gb for Data and probably 400Gb for backups
during this period. A few 147Gb drives will do the job nicely.
For best performance have a seperate SCSI controller for the logs, backup
and data drives. You may want to split the data up into 2 drive sets to get
better throughput.
With the size and price of drives, do you really need RAID-5? What about
RAD-10? The write performance is just so much better.
Do you need high abailabilty? If so, you need to look at Windows Clusters.
This will guide you in the direction of the drive enclosure as it will need
to be a SAN. EMC has a few nice babies.
Regards
Mike
"Dan" wrote:
> We are configuring a new database server at work. I have decided to do a RAID
> 1 Array for the transaction log and a RAID 5 array for the data.
> The fastest growing part of our database is a table that stores photos. It
> is currently about 12Gb in size. We expect it to grow the next few years by
> 17Gb, 26Gb, 33Gb, then platueau at 37Gb growth a year. At the end of year 5
> that puts us at about 165Gb, and at the end of year 10 at about 500Gb.
> Although these are rough projections, I still need to be able to begin
> planning accordingly.
> What is the best hardware configuration to allow this kind of growth? I
> would like something that is expandable, so that I can buy according to our
> current needs, and add to it as needed.
> Any advice is greatly appreciated.
> -Dan|||Hi Dan,
Statistically speaking the company you are working for will not be in
business in 10 years! Anyhow. Let me tell you what I did for one of our
businesses that had a quad-processor (700 MHZ) server running SQL Server:
1-got a dual processor (3 GHZ) server.
2-Got 15000 RPM drives (50% in disk speed increase right of the bat!)
3-Created 3 RAID 1 arrays: one for OS, one for DB and one of transaction
log. Make sure that your controller supports split seeks, which devides the
read I/Os among the mirrored pair, allowing two disks to seek simultaneously.
Ideally you can use RAID 10.
4-be carefull with the edition of SQL Server. The server edition will limit
SQL to use a max. of 2 GB. Also, you will not be able to create indexes on
views.
Microsoft SQL Administrator's companion book has a section on Capacity
Planning (for memory, processor, disk-subsystem) that you can have a look.
The problem we had with our quad-processor server was that we had to pay
close to 10,000$ to get 2 more CPUs since they were older CPUs. Note also
that storage often becomes a problem. With 200 servers to maintain by our
team, we often need to plan some upgrades for servers that are running low in
HD space (after few years) so make sure you have enough space on your
database server. Last think, it is very hard to load balance a SQL Server
(manual process as far as I know) because chances are that new servers will
get data from your database server in the future and/or the load will
increase.
"Dan" wrote:
> We are configuring a new database server at work. I have decided to do a RAID
> 1 Array for the transaction log and a RAID 5 array for the data.
> The fastest growing part of our database is a table that stores photos. It
> is currently about 12Gb in size. We expect it to grow the next few years by
> 17Gb, 26Gb, 33Gb, then platueau at 37Gb growth a year. At the end of year 5
> that puts us at about 165Gb, and at the end of year 10 at about 500Gb.
> Although these are rough projections, I still need to be able to begin
> planning accordingly.
> What is the best hardware configuration to allow this kind of growth? I
> would like something that is expandable, so that I can buy according to our
> current needs, and add to it as needed.
> Any advice is greatly appreciated.
> -Dan
Hardware planning for growth
We are configuring a new database server at work. I have decided to do a RAI
D
1 Array for the transaction log and a RAID 5 array for the data.
The fastest growing part of our database is a table that stores photos. It
is currently about 12Gb in size. We expect it to grow the next few years by
17Gb, 26Gb, 33Gb, then platueau at 37Gb growth a year. At the end of year 5
that puts us at about 165Gb, and at the end of year 10 at about 500Gb.
Although these are rough projections, I still need to be able to begin
planning accordingly.
What is the best hardware configuration to allow this kind of growth? I
would like something that is expandable, so that I can buy according to our
current needs, and add to it as needed.
Any advice is greatly appreciated.
-DanHi
In effect, you can only plan for the next 3-4 years. By then, the current
disk subsystems will need to be retired.
In effect, you need about 150Gb for Data and probably 400Gb for backups
during this period. A few 147Gb drives will do the job nicely.
For best performance have a seperate SCSI controller for the logs, backup
and data drives. You may want to split the data up into 2 drive sets to get
better throughput.
With the size and price of drives, do you really need RAID-5? What about
RAD-10? The write performance is just so much better.
Do you need high abailabilty? If so, you need to look at Windows Clusters.
This will guide you in the direction of the drive enclosure as it will need
to be a SAN. EMC has a few nice babies.
Regards
Mike
"Dan" wrote:
> We are configuring a new database server at work. I have decided to do a R
AID
> 1 Array for the transaction log and a RAID 5 array for the data.
> The fastest growing part of our database is a table that stores photos. It
> is currently about 12Gb in size. We expect it to grow the next few years b
y
> 17Gb, 26Gb, 33Gb, then platueau at 37Gb growth a year. At the end of year
5
> that puts us at about 165Gb, and at the end of year 10 at about 500Gb.
> Although these are rough projections, I still need to be able to begin
> planning accordingly.
> What is the best hardware configuration to allow this kind of growth? I
> would like something that is expandable, so that I can buy according to ou
r
> current needs, and add to it as needed.
> Any advice is greatly appreciated.
> -Dan|||Hi Dan,
Statistically speaking the company you are working for will not be in
business in 10 years! Anyhow. Let me tell you what I did for one of our
businesses that had a quad-processor (700 MHZ) server running SQL Server:
1-got a dual processor (3 GHZ) server.
2-Got 15000 RPM drives (50% in disk speed increase right of the bat!)
3-Created 3 RAID 1 arrays: one for OS, one for DB and one of transaction
log. Make sure that your controller supports split seeks, which devides the
read I/Os among the mirrored pair, allowing two disks to seek simultaneously
.
Ideally you can use RAID 10.
4-be carefull with the edition of SQL Server. The server edition will limit
SQL to use a max. of 2 GB. Also, you will not be able to create indexes on
views.
Microsoft SQL Administrator's companion book has a section on Capacity
Planning (for memory, processor, disk-subsystem) that you can have a look.
The problem we had with our quad-processor server was that we had to pay
close to 10,000$ to get 2 more CPUs since they were older CPUs. Note also
that storage often becomes a problem. With 200 servers to maintain by our
team, we often need to plan some upgrades for servers that are running low i
n
HD space (after few years) so make sure you have enough space on your
database server. Last think, it is very hard to load balance a SQL Server
(manual process as far as I know) because chances are that new servers will
get data from your database server in the future and/or the load will
increase.
"Dan" wrote:
> We are configuring a new database server at work. I have decided to do a R
AID
> 1 Array for the transaction log and a RAID 5 array for the data.
> The fastest growing part of our database is a table that stores photos. It
> is currently about 12Gb in size. We expect it to grow the next few years b
y
> 17Gb, 26Gb, 33Gb, then platueau at 37Gb growth a year. At the end of year
5
> that puts us at about 165Gb, and at the end of year 10 at about 500Gb.
> Although these are rough projections, I still need to be able to begin
> planning accordingly.
> What is the best hardware configuration to allow this kind of growth? I
> would like something that is expandable, so that I can buy according to ou
r
> current needs, and add to it as needed.
> Any advice is greatly appreciated.
> -Dan
D
1 Array for the transaction log and a RAID 5 array for the data.
The fastest growing part of our database is a table that stores photos. It
is currently about 12Gb in size. We expect it to grow the next few years by
17Gb, 26Gb, 33Gb, then platueau at 37Gb growth a year. At the end of year 5
that puts us at about 165Gb, and at the end of year 10 at about 500Gb.
Although these are rough projections, I still need to be able to begin
planning accordingly.
What is the best hardware configuration to allow this kind of growth? I
would like something that is expandable, so that I can buy according to our
current needs, and add to it as needed.
Any advice is greatly appreciated.
-DanHi
In effect, you can only plan for the next 3-4 years. By then, the current
disk subsystems will need to be retired.
In effect, you need about 150Gb for Data and probably 400Gb for backups
during this period. A few 147Gb drives will do the job nicely.
For best performance have a seperate SCSI controller for the logs, backup
and data drives. You may want to split the data up into 2 drive sets to get
better throughput.
With the size and price of drives, do you really need RAID-5? What about
RAD-10? The write performance is just so much better.
Do you need high abailabilty? If so, you need to look at Windows Clusters.
This will guide you in the direction of the drive enclosure as it will need
to be a SAN. EMC has a few nice babies.
Regards
Mike
"Dan" wrote:
> We are configuring a new database server at work. I have decided to do a R
AID
> 1 Array for the transaction log and a RAID 5 array for the data.
> The fastest growing part of our database is a table that stores photos. It
> is currently about 12Gb in size. We expect it to grow the next few years b
y
> 17Gb, 26Gb, 33Gb, then platueau at 37Gb growth a year. At the end of year
5
> that puts us at about 165Gb, and at the end of year 10 at about 500Gb.
> Although these are rough projections, I still need to be able to begin
> planning accordingly.
> What is the best hardware configuration to allow this kind of growth? I
> would like something that is expandable, so that I can buy according to ou
r
> current needs, and add to it as needed.
> Any advice is greatly appreciated.
> -Dan|||Hi Dan,
Statistically speaking the company you are working for will not be in
business in 10 years! Anyhow. Let me tell you what I did for one of our
businesses that had a quad-processor (700 MHZ) server running SQL Server:
1-got a dual processor (3 GHZ) server.
2-Got 15000 RPM drives (50% in disk speed increase right of the bat!)
3-Created 3 RAID 1 arrays: one for OS, one for DB and one of transaction
log. Make sure that your controller supports split seeks, which devides the
read I/Os among the mirrored pair, allowing two disks to seek simultaneously
.
Ideally you can use RAID 10.
4-be carefull with the edition of SQL Server. The server edition will limit
SQL to use a max. of 2 GB. Also, you will not be able to create indexes on
views.
Microsoft SQL Administrator's companion book has a section on Capacity
Planning (for memory, processor, disk-subsystem) that you can have a look.
The problem we had with our quad-processor server was that we had to pay
close to 10,000$ to get 2 more CPUs since they were older CPUs. Note also
that storage often becomes a problem. With 200 servers to maintain by our
team, we often need to plan some upgrades for servers that are running low i
n
HD space (after few years) so make sure you have enough space on your
database server. Last think, it is very hard to load balance a SQL Server
(manual process as far as I know) because chances are that new servers will
get data from your database server in the future and/or the load will
increase.
"Dan" wrote:
> We are configuring a new database server at work. I have decided to do a R
AID
> 1 Array for the transaction log and a RAID 5 array for the data.
> The fastest growing part of our database is a table that stores photos. It
> is currently about 12Gb in size. We expect it to grow the next few years b
y
> 17Gb, 26Gb, 33Gb, then platueau at 37Gb growth a year. At the end of year
5
> that puts us at about 165Gb, and at the end of year 10 at about 500Gb.
> Although these are rough projections, I still need to be able to begin
> planning accordingly.
> What is the best hardware configuration to allow this kind of growth? I
> would like something that is expandable, so that I can buy according to ou
r
> current needs, and add to it as needed.
> Any advice is greatly appreciated.
> -Dan
Wednesday, March 7, 2012
Hard time master log files
Hi,
I have a small DB, about 200MB, but my transaction log files grows regulary
over 900MB.
The only way I have been able to manage the log file is to detach the
database, delete the log file and reattach the DB.
Is there any better way to manage growing log files?
Thanks in advance
ChristianHi,
Check the recovery model for your database using the below command:-
Select DATABASEPROPERTYEX(<dbname>,Recovery )
If the recovery is Simple then the LDF should grow big intil u have some
batch operation. In that once the trasnaction is over you could
shrink the LDF file using DBCC SHRINKFILE command
If the recovery model is either FULL or BULK_LOGGED then you need to do the
Transaction log backup using BACKUP LOG command. This
will keep the LDF file not to grow.
Thanks
Hari
SQL Server MVP
"Christian Perthen" <abracadabara@.dontreplytothi
dress.com> wrote in
message news:e2YgKk8QFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a small DB, about 200MB, but my transaction log files grows
> regulary
> over 900MB.
> The only way I have been able to manage the log file is to detach the
> database, delete the log file and reattach the DB.
> Is there any better way to manage growing log files?
> Thanks in advance
> Christian
>
I have a small DB, about 200MB, but my transaction log files grows regulary
over 900MB.
The only way I have been able to manage the log file is to detach the
database, delete the log file and reattach the DB.
Is there any better way to manage growing log files?
Thanks in advance
ChristianHi,
Check the recovery model for your database using the below command:-
Select DATABASEPROPERTYEX(<dbname>,Recovery )
If the recovery is Simple then the LDF should grow big intil u have some
batch operation. In that once the trasnaction is over you could
shrink the LDF file using DBCC SHRINKFILE command
If the recovery model is either FULL or BULK_LOGGED then you need to do the
Transaction log backup using BACKUP LOG command. This
will keep the LDF file not to grow.
Thanks
Hari
SQL Server MVP
"Christian Perthen" <abracadabara@.dontreplytothi
dress.com> wrote inmessage news:e2YgKk8QFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a small DB, about 200MB, but my transaction log files grows
> regulary
> over 900MB.
> The only way I have been able to manage the log file is to detach the
> database, delete the log file and reattach the DB.
> Is there any better way to manage growing log files?
> Thanks in advance
> Christian
>
Monday, February 27, 2012
Handling Transaction
hi friends,
I like to put a set of sql statements under a transaction and wish the sql server to take care of commit / roll back the entire set depending upon the success/failure of the statements in the set. I want the whole set is either to success or to failure.
When I go through the docs, i find that SQL Server 2000 operates three transaction modes:
Autocommit transactions : Each individual statement is a transaction.
Explicit transactions : Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
Implicit transactions: A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.
As one can see, it seems that it is not possible to define "atomic compound sql statements". Even if i used explicit transaction, it is not possible to achieve this, since i couldn't find a mechanism to handle errors for a group of statements.
I wonder how to write atomic compound sql statement in the sql server.
can anybody please help me on this...
JakeThe only solution that I am aware of is enclosing the inserts/updates into one transaction and check each for success. If one fails; rollback, if all succeed; commit.|||hi Kaiowas,
thanks for the suggestion. instead of checking for success & failure for each line, i found another way of doing this.
If we put the set of statements in a BEGIN TRANSACTION ... COMMIT TRANSACTION block, the commit transaction will get execute iff all the statements succeed. Otherwise all the statements get rolled back. This solves my problem.
But still there is no way to handle exception for a block of statements.
Does Yukon release have support for this? any idea?
Jake
Originally posted by Kaiowas
The only solution that I am aware of is enclosing the inserts/updates into one transaction and check each for success. If one fails; rollback, if all succeed; commit.
I like to put a set of sql statements under a transaction and wish the sql server to take care of commit / roll back the entire set depending upon the success/failure of the statements in the set. I want the whole set is either to success or to failure.
When I go through the docs, i find that SQL Server 2000 operates three transaction modes:
Autocommit transactions : Each individual statement is a transaction.
Explicit transactions : Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
Implicit transactions: A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.
As one can see, it seems that it is not possible to define "atomic compound sql statements". Even if i used explicit transaction, it is not possible to achieve this, since i couldn't find a mechanism to handle errors for a group of statements.
I wonder how to write atomic compound sql statement in the sql server.
can anybody please help me on this...
JakeThe only solution that I am aware of is enclosing the inserts/updates into one transaction and check each for success. If one fails; rollback, if all succeed; commit.|||hi Kaiowas,
thanks for the suggestion. instead of checking for success & failure for each line, i found another way of doing this.
If we put the set of statements in a BEGIN TRANSACTION ... COMMIT TRANSACTION block, the commit transaction will get execute iff all the statements succeed. Otherwise all the statements get rolled back. This solves my problem.
But still there is no way to handle exception for a block of statements.
Does Yukon release have support for this? any idea?
Jake
Originally posted by Kaiowas
The only solution that I am aware of is enclosing the inserts/updates into one transaction and check each for success. If one fails; rollback, if all succeed; commit.
Handling out-dated transaction records
For performance issue, I believe many program should have a house-keeping procedure to clean up transaction history. Is there any best practice to perform this? Or should it be done simply by moving transaction data from the transaction table into a history table? Any better or consideration that I should be concerned of?Depends on your environment. Many places would like to keep records for atleast 2 years, but if you are in the medical field and some of your stuff may fall under HIPAA, then you need to keep the records I believe for like 7+ years. In those cases, I usually have an audit table that contains all the transaction data (It's written to via a trigger). Then I keep the transaction table fairly clean (only recent/open/pending), and if you need historical data, then I run my queries off the audit table instead which is never purged and has a good set of indexes on it.
Subscribe to:
Posts (Atom)