best location for the tran log?

I have a SQL 2000 database on RAID 5 storage. The data files and the
tran log are all on the same RAID 5 volume - the E drive. My only other
option for placement of the tran log is a partition of the system drive
- the D drive. The system drive is mirrored. The operating system
resides on the other logical partition of the system drive - the C
drive. I'm concerned that if I move the tran log to the D drive it
might conflict with operating system activity. This is a dedicated SQL
Server so I'm hoping that it won't be too much overhead on the system
drive to place the tran log there as well. Am I better off moving the
tran log to the system drive or keeping it on the E drive with the rest
of the data?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!There shouldn't be a problem with sharing a mirrored drive with the OS for
the logs if SQL Server is the only app running on the server.
Andrew J. Kelly SQL MVP
"T Dubya" <timber_toes@.bigfoot.com> wrote in message
news:%23Qx5Um0LFHA.3340@.TK2MSFTNGP14.phx.gbl...
>I have a SQL 2000 database on RAID 5 storage. The data files and the
> tran log are all on the same RAID 5 volume - the E drive. My only other
> option for placement of the tran log is a partition of the system drive
> - the D drive. The system drive is mirrored. The operating system
> resides on the other logical partition of the system drive - the C
> drive. I'm concerned that if I move the tran log to the D drive it
> might conflict with operating system activity. This is a dedicated SQL
> Server so I'm hoping that it won't be too much overhead on the system
> drive to place the tran log there as well. Am I better off moving the
> tran log to the system drive or keeping it on the E drive with the rest
> of the data?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||I'd recommend that you move the transaction log for a couple of reasons.
1. By having the transaction log and data files on the same drives you risk
a loss on failure, or an extension in time to restore.
2. RAID 5 is not as efficient as RAID 1 for performance of the write
intensive transaction log.
If the server is dedicated to SQL server then there should be very little
competition for the drive from other processes.
"T Dubya" wrote:

> I have a SQL 2000 database on RAID 5 storage. The data files and the
> tran log are all on the same RAID 5 volume - the E drive. My only other
> option for placement of the tran log is a partition of the system drive
> - the D drive. The system drive is mirrored. The operating system
> resides on the other logical partition of the system drive - the C
> drive. I'm concerned that if I move the tran log to the D drive it
> might conflict with operating system activity. This is a dedicated SQL
> Server so I'm hoping that it won't be too much overhead on the system
> drive to place the tran log there as well. Am I better off moving the
> tran log to the system drive or keeping it on the E drive with the rest
> of the data?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Thanks, Andrew, for the response. I'll give it a try. The main reason
I had reservations about it is because before I realized that the D
drive was in fact just a logical partition of the system drive, I tried
to dump the entire database to that location. The database dump ran
much slower that way than when I dumped it to the same raid 5 where the
data itself resides - about twice as slow, in fact. I just wanted to be
sure that putting the tran logs on the operating system drive wouldn't
cause a similar slow down. Since it is a dedicated SQL Server hopefully
it won't pose a problem. Thanks again.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

best location for the tran log?

I have a SQL 2000 database on RAID 5 storage. The data files and the
tran log are all on the same RAID 5 volume - the E drive. My only other
option for placement of the tran log is a partition of the system drive
- the D drive. The system drive is mirrored. The operating system
resides on the other logical partition of the system drive - the C
drive. I'm concerned that if I move the tran log to the D drive it
might conflict with operating system activity. This is a dedicated SQL
Server so I'm hoping that it won't be too much overhead on the system
drive to place the tran log there as well. Am I better off moving the
tran log to the system drive or keeping it on the E drive with the rest
of the data?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!There shouldn't be a problem with sharing a mirrored drive with the OS for
the logs if SQL Server is the only app running on the server.
--
Andrew J. Kelly SQL MVP
"T Dubya" <timber_toes@.bigfoot.com> wrote in message
news:%23Qx5Um0LFHA.3340@.TK2MSFTNGP14.phx.gbl...
>I have a SQL 2000 database on RAID 5 storage. The data files and the
> tran log are all on the same RAID 5 volume - the E drive. My only other
> option for placement of the tran log is a partition of the system drive
> - the D drive. The system drive is mirrored. The operating system
> resides on the other logical partition of the system drive - the C
> drive. I'm concerned that if I move the tran log to the D drive it
> might conflict with operating system activity. This is a dedicated SQL
> Server so I'm hoping that it won't be too much overhead on the system
> drive to place the tran log there as well. Am I better off moving the
> tran log to the system drive or keeping it on the E drive with the rest
> of the data?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||I'd recommend that you move the transaction log for a couple of reasons.
1. By having the transaction log and data files on the same drives you risk
a loss on failure, or an extension in time to restore.
2. RAID 5 is not as efficient as RAID 1 for performance of the write
intensive transaction log.
If the server is dedicated to SQL server then there should be very little
competition for the drive from other processes.
"T Dubya" wrote:
> I have a SQL 2000 database on RAID 5 storage. The data files and the
> tran log are all on the same RAID 5 volume - the E drive. My only other
> option for placement of the tran log is a partition of the system drive
> - the D drive. The system drive is mirrored. The operating system
> resides on the other logical partition of the system drive - the C
> drive. I'm concerned that if I move the tran log to the D drive it
> might conflict with operating system activity. This is a dedicated SQL
> Server so I'm hoping that it won't be too much overhead on the system
> drive to place the tran log there as well. Am I better off moving the
> tran log to the system drive or keeping it on the E drive with the rest
> of the data?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>

best location for the tran log?

I have a SQL 2000 database on RAID 5 storage. The data files and the
tran log are all on the same RAID 5 volume - the E drive. My only other
option for placement of the tran log is a partition of the system drive
- the D drive. The system drive is mirrored. The operating system
resides on the other logical partition of the system drive - the C
drive. I'm concerned that if I move the tran log to the D drive it
might conflict with operating system activity. This is a dedicated SQL
Server so I'm hoping that it won't be too much overhead on the system
drive to place the tran log there as well. Am I better off moving the
tran log to the system drive or keeping it on the E drive with the rest
of the data?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
There shouldn't be a problem with sharing a mirrored drive with the OS for
the logs if SQL Server is the only app running on the server.
Andrew J. Kelly SQL MVP
"T Dubya" <timber_toes@.bigfoot.com> wrote in message
news:%23Qx5Um0LFHA.3340@.TK2MSFTNGP14.phx.gbl...
>I have a SQL 2000 database on RAID 5 storage. The data files and the
> tran log are all on the same RAID 5 volume - the E drive. My only other
> option for placement of the tran log is a partition of the system drive
> - the D drive. The system drive is mirrored. The operating system
> resides on the other logical partition of the system drive - the C
> drive. I'm concerned that if I move the tran log to the D drive it
> might conflict with operating system activity. This is a dedicated SQL
> Server so I'm hoping that it won't be too much overhead on the system
> drive to place the tran log there as well. Am I better off moving the
> tran log to the system drive or keeping it on the E drive with the rest
> of the data?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||I'd recommend that you move the transaction log for a couple of reasons.
1. By having the transaction log and data files on the same drives you risk
a loss on failure, or an extension in time to restore.
2. RAID 5 is not as efficient as RAID 1 for performance of the write
intensive transaction log.
If the server is dedicated to SQL server then there should be very little
competition for the drive from other processes.
"T Dubya" wrote:

> I have a SQL 2000 database on RAID 5 storage. The data files and the
> tran log are all on the same RAID 5 volume - the E drive. My only other
> option for placement of the tran log is a partition of the system drive
> - the D drive. The system drive is mirrored. The operating system
> resides on the other logical partition of the system drive - the C
> drive. I'm concerned that if I move the tran log to the D drive it
> might conflict with operating system activity. This is a dedicated SQL
> Server so I'm hoping that it won't be too much overhead on the system
> drive to place the tran log there as well. Am I better off moving the
> tran log to the system drive or keeping it on the E drive with the rest
> of the data?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
|||Thanks, Andrew, for the response. I'll give it a try. The main reason
I had reservations about it is because before I realized that the D
drive was in fact just a logical partition of the system drive, I tried
to dump the entire database to that location. The database dump ran
much slower that way than when I dumped it to the same raid 5 where the
data itself resides - about twice as slow, in fact. I just wanted to be
sure that putting the tran logs on the operating system drive wouldn't
cause a similar slow down. Since it is a dedicated SQL Server hopefully
it won't pose a problem. Thanks again.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Begin tran/commit

Hi,
Is it good to have two transaction i.e two begin/commit in one SP.
like
beging
update..
commit
begin
update...
commit.
any performance improvements in any case or otherwise..If those two updates can be commited or rolled back independently from one
another, then your solution is OK.
But if they should be treated as a single atomic unit of work, you should
either enclose them into a single transaction (be it each in its own nested
trasaction or not), and make sure they are committed (or rolled back) as a
single unit of work (i.e. if one needs to be rolled back, the other either
never begins or is rolled back).
Other than that, based on your post, I see no problem. Unless this is your
entire procedure, in which case I see a gazzillion of them. :)
ML|||hi Sunny
This depends on your requirement. You can have 2 transactions in a SP.
If u have 2 different sets of queries, u can group them into two different
queries
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sunny" wrote:

> Hi,
> Is it good to have two transaction i.e two begin/commit in one SP.
> like
> beging
> update..
> commit
> begin
> update...
> commit.
> any performance improvements in any case or otherwise..

BEGIN TRAN increments @@TRANCOUNT to 2

I have a problem with the below code that seems to open 2 transactions(why
not just one) - what am I doing wrong?
Regards,
Janusz
SET IMPLICIT_TRANSACTIONS ON
GO
BEGIN TRAN
COMMIT
PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUNT)Hi,
The first transaction was opened for SET IMPLICIT_TRANSACTIONS ON the next
was opened for Begin Tran.
If you run the following code you will find the same, I am not sure what is
your requirement here.
SET IMPLICIT_TRANSACTIONS ON
GO
BEGIN TRAN
COMMIT
PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUNT)
COMMIT --This is for the the imlicit transaction on
PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUNT)
o/p
After commiting trans. Opened trans::1
After commiting trans. Opened trans::0
Vishal Khajuria
SUNGARD SCT INDIA
"rejki" wrote:

> I have a problem with the below code that seems to open 2 transactions(why
> not just one) - what am I doing wrong?
> Regards,
> Janusz
> SET IMPLICIT_TRANSACTIONS ON
> GO
> BEGIN TRAN
> COMMIT
> PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUN
T)
>|||See SET IMPLICIT_TRANSACTIONS in BOL.
It has a perfect example showing the variation in @.@.TRANCOUNT
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"rejki" <rejki@.discussions.microsoft.com> wrote in message
news:DE9B1D00-8D58-4F10-9614-866EC64BF1E5@.microsoft.com...
>I have a problem with the below code that seems to open 2 transactions(why
> not just one) - what am I doing wrong?
> Regards,
> Janusz
> SET IMPLICIT_TRANSACTIONS ON
> GO
> BEGIN TRAN
> COMMIT
> PRINT 'After commiting trans. Opened trans::' +
> convert(varchar,@.@.TRANCOUNT)
>|||Thanks for reply,
If you add print statement after "SET IMPLICIT_TRANSACTIONS ON" you will see
that it does not open transaction. @.@.TRANCOUNT goes to 2 after "BEGIN TRAN"
statement.
The problem I am having is that I do only one commit, but when I execute my
SQL script (similar to one I posted) SQL Analyzer thinks that I have still
uncommitted transaction and when I try to exit SQL Analyzer it asks me
whether I want to close open transaction.
I could add one more “commit” but I would prefer to understand what is g
oing
on there.
Regards,
Janusz
"Vishal Khajuria" wrote:
> Hi,
> The first transaction was opened for SET IMPLICIT_TRANSACTIONS ON the next
> was opened for Begin Tran.
> If you run the following code you will find the same, I am not sure what i
s
> your requirement here.
> SET IMPLICIT_TRANSACTIONS ON
> GO
> BEGIN TRAN
> COMMIT
> PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUN
T)
> COMMIT --This is for the the imlicit transaction on
> PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUN
T)
> o/p
> After commiting trans. Opened trans::1
> After commiting trans. Opened trans::0
>
> Vishal Khajuria
> SUNGARD SCT INDIA
>
> "rejki" wrote:
>|||Hi,
What happens with IMPLICIT_TRANSACTIONS is this:
If there's already a transaction in progress (say at a higher scope in a cal
ling stored procedure or some such), nothing else happens; you simply join t
hat transaction.
If, however, there's no current transaction in progress, then executing any
DML/DDL statement will start a new transaction. In this case, all you need t
o do is COMMIT or ROLLBACK. A better approach even than this is to use XACT_
ABORT, which will ensure that any failures below 21 rollback the current tra
nsaction scope. That way, you can raise a suitable error instead of just rol
ling back (or worse, writing extra code to rollback and raise an error!).
The following demonstrates...
SET IMPLICIT_TRANSACTIONS ON
SET XACT_ABORT ON
-- now do some work.
SELECT|||Sorry, here's the complete demonstration
SET IMPLICIT_TRANSACTIONS ON
SET XACT_ABORT ON
-- now do some work.
UPDATE table1
SET field1 = NULL
WHERE field2 = 'some value'
IF EXISTS (
SELECT a.field1, b.field6
FROM tbl2 a
INNER JOIN tbl2 a
WHERE a.field9 > 0
)
BEGIN
RAISERROR (N'Failed to update fact table.', 16, 1)
END
-- if we got this far then we're good!
COMMIT TRANSACTION
GO|||One other thing, by way of explanation about the following
bit of SQL:

IF EXISTS (
SELECT a.field1, b.field6
FROM tbl2 a
INNER JOIN tbl2 a
WHERE a.field9 > 0
)

This is testing some condition to see if the operation was
successful. You may not care about the outcome, in which case you
can just COMMIT, but given that you're in a transaction in the
first place, I'd guess you're going to want to test some sort
of condition to see if this really worked out, before deciding
whether or not to commit your changes.
Cheers,
Tim

Begin Tran / Commit Tran

Any reason why you would need to wrap a transaction around a single update?
BEGIN TRANSACTION
UPDATE ThisTable
SET blah blah blah
WHERE foo = @.bar
COMMIT TRANSACTION
?!?
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James HetfieldNo need. The only reason I can think of if you were to name the transaction.
However, naming
transaction has little benefit.
I think you can see the transaction names as they are rolled forward and rol
led back during
automatic recovery in the errorlog (at least you used to be able to do that)
. That is about the only
reason I can think of. Without a name = waste of precious keyboard keys.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message news:eNrwSe0pFHA.1024@.TK2MSFTNGP09.phx
.gbl...
> Any reason why you would need to wrap a transaction around a single update
?
> BEGIN TRANSACTION
> UPDATE ThisTable
> SET blah blah blah
> WHERE foo = @.bar
> COMMIT TRANSACTION
> ?!?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||> No need. The only reason I can think of if you were to name the
> transaction. However, naming transaction has little benefit.
> I think you can see the transaction names as they are rolled forward and
> rolled back during automatic recovery in the errorlog (at least you used
> to be able to do that). That is about the only reason I can think of.
> Without a name = waste of precious keyboard keys.
Yeah, ok, that's what I thought. Thanks for the much needed "sanity-check".
--
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||If that's all that's there in the proc, then it's a waste of keystrokes.
Otherwise, if it ain't broke, don't fix it. The real question is, where's
the error handling and the ROLLBACK? someone may have modified another
working procedure that updated multiple tables and just changed the names to
protect the innocent. It won't hurt anything to leave it in, and if you
ever need to add an update to another table, you've already got all of the
error handling and cleanup in place.
You may have a structure something like:
CREATE PROCEDURE procName ...
BEGIN
..
SET @._TRANCOUNT = @.@.TRANCOUNT
IF @._TRANCOUNT > 0
SAVE TRANSACTION transactionName
ELSE
BEGIN TRANSACTION transactionName
..
IF @._TRANCOUNT = 0 -- only commit if new tran started
COMMIT TRANSACTION transactionName
RETURN 0 -- indicate success
ERROR:
IF @._TRANCOUNT IS NOT NULL -- only roll back if necessary
IF @.@.TRANCOUNT > 0 -- roll back any changes made by this proc
ROLLBACK TRANSACTION transactionName
IF @.RC > 0 RETURN @.RC -- return error from called sp
IF @._ERROR > 0 RETURN @._ERROR -- return error raised in this sp
RETURN -1 -- indicate failure
END
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eNrwSe0pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Any reason why you would need to wrap a transaction around a single
update?
> BEGIN TRANSACTION
> UPDATE ThisTable
> SET blah blah blah
> WHERE foo = @.bar
> COMMIT TRANSACTION
> ?!?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>

Begin Tran ...... Commit Tran

What is the need of BEGIN TRAN...COMMIT TRAN? Also why you are setting Transaction isolation level to SERIALIZABLE?

Quote:

Originally Posted by sajithamol

What is the need of BEGIN TRAN...COMMIT TRAN? Also why you are setting Transaction isolation level to SERIALIZABLE?

Hi ,
Go through the sql online books.|||

Quote:

Originally Posted by sajithamol

What is the need of BEGIN TRAN...COMMIT TRAN? Also why you are setting Transaction isolation level to SERIALIZABLE?

Refer
http://msdn2.microsoft.com/en-us/li...83(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/ms173763.aspx
http://www.mssqlcity.com/Articles/General/TIL.htm|||

Quote:

Originally Posted by sajithamol

What is the need of BEGIN TRAN...COMMIT TRAN? Also why you are setting Transaction isolation level to SERIALIZABLE?

simple to roll back the qry process done

BEGIN TRAN . . . WITH MARK . . .

Hallo All,

Can somebody explain why the same function works different with MS SQL 2000 and MS SQL 2005?
On both systems 2000 and 2005 I have 2x databases named ACCT and PROD (actually only a test environment).
On both systems I try to execute the following statements:
BEGIN TRAN TRAN_01 WITH MARK 'My TRAN_01'
USE PROD
INSERT INTO [PROD].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])
VALUES('PROT_COMMENT', 1004, 1004)
USE ACCT
INSERT INTO [ACCT].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])
VALUES('PROT_COMMENT', 1004, 1004)
COMMIT TRAN TRAN_01
After executing the statements I start the following query:
SELECT * FROM [msdb].[dbo].[logmarkhistory]
On MS SQL 2000 I get as results:
PROD TRAN_01 My TRAN_01 SUPPORT\Administrator 3944000000107600001 2006-03-29 17:15:13.930
ACCT TRAN_01 My TRAN_01 SUPPORT\Administrator 8000000009200001 2006-03-29 17:15:13.930
Seems to be correct. I think it is the way it should work according to the documentation.

On MS SQL 2005 I only get the following results:
PROD TRAN_01 My TRAN_01 SU 29000000107800001 2006-03-29 17:31:32.283
There are no entries in the table for the ACCT database and the account / user_name is shown incorrectly.
It seems to be an ERROR in the processing of such marked transactions in MS SQL 2005.See the Books Online Topic "Ensuring Recoverability of Related Databases". I believe that there is a change in SQL Server 2005.|||Hello ALL,
I have the solution now.
It have nothing to do with some changes in SQL Server 2005.
It seems just to be the following. I habe never made a backup
of the ACCT database in the past. After I executed the full
online backup of the ACCT database for the first time I get
exactly the same results as with MS SQL 2000.