Conditional Rollback

I have inherited a lot of SQL2005 TSQL, that has the following generic catch structure:

BEGIN CATCH
IF @.@.TRANCOUNT > 0 AND (XACT_STATE()) <> 0
BEGIN
ROLLBACK TRANSACTION
END
-- task specfic steps
END CATCH

From BOL, @.@.TRANCOUNT returns the number of active transactions for the current connection and XACT_STATE()) is a scalar function that reports the transaction state of a session, indicating whether or not the session has an active transaction, and whether or not the transaction is capable of being committed.

My question is: Should the tests be OR'd rather than AND'd?

The line in question would then read:

IF @.@.TRANCOUNT > 0 OR (XACT_STATE()) <> 0

BOL says,

Both the XACT_STATE and @.@.TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @.@.TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. XACT_STATE cannot be used to determine whether there are nested transactions.

ie,

Instead of using the @.@.Trancount you can always check the XACT_STATE. Bcs @.@.TRANCOUNT never tells you wheather you have to do Commit or rollback. It only gives the number of transaction begin.

But

XACT_STATE = 0 no transaction active

XACT_STATE = 1 active transaction available & can be writable & commitable/rollbackable -- Commit/rollback

XACT_STATE = -1 active transaction available & uncommitable -- Rollbacked

|||

Thankyou Manivannan,

The TSQL in question runs after an error within the try section has been caught. My question relates as whether the functions should be OR'd instead of AND'd. Possibly only one of them should be used.

|||

Ok now, My direct answer is XACT_STATE is more enough to use....(trustable). Need not have or/and only XACT_STATE is enough to validate the condition.

|||

If I may summerise

BEGIN CATCH
IF @.@.TRANCOUNT > 0 AND (XACT_STATE()) <> 0
BEGIN
ROLLBACK TRANSACTION
END
-- task specfic steps
END CATCH
should become

BEGIN CATCH
IF (XACT_STATE()) <> 0 -- < Suggested simpler construction.
BEGIN
ROLLBACK TRANSACTION
END
-- task specfic steps
END CATCH

Is this your recommendation?

|||Yes...Exactly..

Concern about backup plan

I inherited this backup strategy, and I do not have authority to change it. I
am concerned about how I will handle this if there is a crash and I need to
do a point in time restore.
Full backups are done 6 days a week, Sunday through Friday.
The transaction log is backed up once a week, on Sunday.
In the event that the system crashed on Wednesday, I have the previous
night's backup, but what about transactions during the day. If I run a
transaction log backup at the point of the crash, it would give me all
transactions since Sunday. Is there a way to specify only transactions for
the current day?Bev
>Is there a way to specify only transactions for
> the current day?
No, as you say , you need to do a point in time restore
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
>I inherited this backup strategy, and I do not have authority to change it.
>I
> am concerned about how I will handle this if there is a crash and I need
> to
> do a point in time restore.
> Full backups are done 6 days a week, Sunday through Friday.
> The transaction log is backed up once a week, on Sunday.
> In the event that the system crashed on Wednesday, I have the previous
> night's backup, but what about transactions during the day. If I run a
> transaction log backup at the point of the crash, it would give me all
> transactions since Sunday. Is there a way to specify only transactions
> for
> the current day?
>|||Given the backup plan that I have been saddled with, what would you recommend
I do in such a case?
"Uri Dimant" wrote:
> Bev
> >Is there a way to specify only transactions for
> > the current day?
> No, as you say , you need to do a point in time restore
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
> >I inherited this backup strategy, and I do not have authority to change it.
> >I
> > am concerned about how I will handle this if there is a crash and I need
> > to
> > do a point in time restore.
> > Full backups are done 6 days a week, Sunday through Friday.
> > The transaction log is backed up once a week, on Sunday.
> > In the event that the system crashed on Wednesday, I have the previous
> > night's backup, but what about transactions during the day. If I run a
> > transaction log backup at the point of the crash, it would give me all
> > transactions since Sunday. Is there a way to specify only transactions
> > for
> > the current day?
> >
>
>|||Bev
If you are unable to change it, i'd suggest to do some testing with what you
are having right now
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:F11E52A8-E55F-4CB9-BA35-3842F0BA2FAB@.microsoft.com...
> Given the backup plan that I have been saddled with, what would you
> recommend
> I do in such a case?
> "Uri Dimant" wrote:
>> Bev
>> >Is there a way to specify only transactions for
>> > the current day?
>> No, as you say , you need to do a point in time restore
>>
>> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
>> >I inherited this backup strategy, and I do not have authority to change
>> >it.
>> >I
>> > am concerned about how I will handle this if there is a crash and I
>> > need
>> > to
>> > do a point in time restore.
>> > Full backups are done 6 days a week, Sunday through Friday.
>> > The transaction log is backed up once a week, on Sunday.
>> > In the event that the system crashed on Wednesday, I have the previous
>> > night's backup, but what about transactions during the day. If I run a
>> > transaction log backup at the point of the crash, it would give me all
>> > transactions since Sunday. Is there a way to specify only transactions
>> > for
>> > the current day?
>> >
>>|||I should admit that this is the weirdest backup strategy I've ever seen.
First, I want you to take a look at the following strategy which is a
classic:
- Take a Full backup once a week
- Take Differential backups once a day
- Take Transaction Log backups every 30 mins \ 10 mins everyday.
This plan suits most of the environments. Of course every environment is
particular and needs special care. However, Transaction Log backup's aim is
make you able to restore your database to the point of failure or restoring
your database to a specific time.
After taking a full backup, your transaction backup would be useless because
you would not need it. For example in your situation you say that you take
tran backup every Sunday. On Monday you'll be taking a full backup and when
your database crashes on Tuesday, then you'd use only your full backup to
restore your database because your full backup would include the records
that your tran backup has.
According to your current plan, if you take your tran backup at Sunday
midnight and taking your full backup Monday midnight, then your tran backup
would be useful only during this period. If your database crashes on Monday
afternoon, then you'd restore your full backup that you took on Friday and
then you'd restore your tran backup that you took on Sunday. However, your
database would be restored until Sunday midnight which is the moment that
you took the tran backup.
In the short, probably only your full backups would be useful for you in
case of a database crash. I highly recommend you to review your backup
strategy immediately.
You may want to take a look at the following documents from BOL to learn
more about backup in SQL Server. I believe they'll be useful for you.
Backup Overview
http://msdn2.microsoft.com/en-us/library/ms175477.aspx
Working with Transaction Log Backups
http://msdn2.microsoft.com/en-us/library/ms190440.aspx
--
Ekrem Ã?nsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
>I inherited this backup strategy, and I do not have authority to change it.
>I
> am concerned about how I will handle this if there is a crash and I need
> to
> do a point in time restore.
> Full backups are done 6 days a week, Sunday through Friday.
> The transaction log is backed up once a week, on Sunday.
> In the event that the system crashed on Wednesday, I have the previous
> night's backup, but what about transactions during the day. If I run a
> transaction log backup at the point of the crash, it would give me all
> transactions since Sunday. Is there a way to specify only transactions
> for
> the current day?
>|||Hi Bev,
You need tp explain that to be able to restore to a failure point, you must
have a full backup AND an unbroken chain of transaction log backups,
preferably off the machine being backed up (tape, NAS, or some other copy).
It's a business decision that they have made, you need to explain what they
actually decided.
FWIW, the maximum time I will allow to go by without a transaction log
backup is 60 minutes, the minimum is 1 minute (and in some narrow cases, I'm
thinking about doing it more often, I haven't, but I'm thinking about it).
Jay
(PS. this is an example of what I call the BS you have to deal with in IT.)
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:F11E52A8-E55F-4CB9-BA35-3842F0BA2FAB@.microsoft.com...
> Given the backup plan that I have been saddled with, what would you
> recommend
> I do in such a case?
> "Uri Dimant" wrote:
>> Bev
>> >Is there a way to specify only transactions for
>> > the current day?
>> No, as you say , you need to do a point in time restore
>>
>> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
>> >I inherited this backup strategy, and I do not have authority to change
>> >it.
>> >I
>> > am concerned about how I will handle this if there is a crash and I
>> > need
>> > to
>> > do a point in time restore.
>> > Full backups are done 6 days a week, Sunday through Friday.
>> > The transaction log is backed up once a week, on Sunday.
>> > In the event that the system crashed on Wednesday, I have the previous
>> > night's backup, but what about transactions during the day. If I run a
>> > transaction log backup at the point of the crash, it would give me all
>> > transactions since Sunday. Is there a way to specify only transactions
>> > for
>> > the current day?
>> >
>>|||Question: Once you have crashed, CAN you run a transaction log backup? I
would have thought no.
If you can't then the tlog run on Sunday is useless as soon as the next full
backup is done.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
>I inherited this backup strategy, and I do not have authority to change it.
>I
> am concerned about how I will handle this if there is a crash and I need
> to
> do a point in time restore.
> Full backups are done 6 days a week, Sunday through Friday.
> The transaction log is backed up once a week, on Sunday.
> In the event that the system crashed on Wednesday, I have the previous
> night's backup, but what about transactions during the day. If I run a
> transaction log backup at the point of the crash, it would give me all
> transactions since Sunday. Is there a way to specify only transactions
> for
> the current day?
>|||> Question: Once you have crashed, CAN you run a transaction log backup? I would have thought no.
It would depend on what the crash mean.
If the SQL Server instance is running but the database in inaccessible (say for instance that the
mdf file is lost), then you can do a log backup using the NO_TRUNCATE option (assuming the log
file(s) are there, of course).
If the SQL Server instance cannot be started but the ldf file(s) are available, you can copy them to
another machine. Create a "dummy" database there. Stop that instance. Delete the database files for
the newly created database. "Slide in" the ldf file from the crashed database where the ldf file
were for the newly created database. Start the instance. And then backup the log using NO_TRUNCATE.
If the whole machine is gone, well, say no more... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:%23OmsEskFIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Question: Once you have crashed, CAN you run a transaction log backup? I would have thought no.
> If you can't then the tlog run on Sunday is useless as soon as the next full backup is done.
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
>>I inherited this backup strategy, and I do not have authority to change it. I
>> am concerned about how I will handle this if there is a crash and I need to
>> do a point in time restore.
>> Full backups are done 6 days a week, Sunday through Friday.
>> The transaction log is backed up once a week, on Sunday.
>> In the event that the system crashed on Wednesday, I have the previous
>> night's backup, but what about transactions during the day. If I run a
>> transaction log backup at the point of the crash, it would give me all
>> transactions since Sunday. Is there a way to specify only transactions for
>> the current day?
>|||> Is there a way to specify only transactions for
> the current day?
Only if you first can produce a log backup (for the crashed database). The restore would then be:
Restore the most recent database backup done before your Sunday log backup.
Restore the Sunday log backup.
Restore the log backup you produce after the crash.
I agree with others that this is among the weirdest backup strategies I've heard of. I hope you can
communicate this with proper persons so you can change it to a reasonable backup strategy.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
>I inherited this backup strategy, and I do not have authority to change it. I
> am concerned about how I will handle this if there is a crash and I need to
> do a point in time restore.
> Full backups are done 6 days a week, Sunday through Friday.
> The transaction log is backed up once a week, on Sunday.
> In the event that the system crashed on Wednesday, I have the previous
> night's backup, but what about transactions during the day. If I run a
> transaction log backup at the point of the crash, it would give me all
> transactions since Sunday. Is there a way to specify only transactions for
> the current day?
>|||I am happy to say that I finally got approval for the new backup strategy,
which I first proposed three months ago and which is very close to what Ekrem
suggested.
I suspect that much of the heel-dragging stemmed from the fact that I am
relative novice while the person who recommended the current plan is a guru.
But then he's not around anymore to clean up the mess if we do crash.
"Jay" wrote:
> Hi Bev,
> You need tp explain that to be able to restore to a failure point, you must
> have a full backup AND an unbroken chain of transaction log backups,
> preferably off the machine being backed up (tape, NAS, or some other copy).
> It's a business decision that they have made, you need to explain what they
> actually decided.
> FWIW, the maximum time I will allow to go by without a transaction log
> backup is 60 minutes, the minimum is 1 minute (and in some narrow cases, I'm
> thinking about doing it more often, I haven't, but I'm thinking about it).
> Jay
> (PS. this is an example of what I call the BS you have to deal with in IT.)
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:F11E52A8-E55F-4CB9-BA35-3842F0BA2FAB@.microsoft.com...
> > Given the backup plan that I have been saddled with, what would you
> > recommend
> > I do in such a case?
> >
> > "Uri Dimant" wrote:
> >
> >> Bev
> >> >Is there a way to specify only transactions for
> >> > the current day?
> >> No, as you say , you need to do a point in time restore
> >>
> >>
> >> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> >> news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
> >> >I inherited this backup strategy, and I do not have authority to change
> >> >it.
> >> >I
> >> > am concerned about how I will handle this if there is a crash and I
> >> > need
> >> > to
> >> > do a point in time restore.
> >> > Full backups are done 6 days a week, Sunday through Friday.
> >> > The transaction log is backed up once a week, on Sunday.
> >> > In the event that the system crashed on Wednesday, I have the previous
> >> > night's backup, but what about transactions during the day. If I run a
> >> > transaction log backup at the point of the crash, it would give me all
> >> > transactions since Sunday. Is there a way to specify only transactions
> >> > for
> >> > the current day?
> >> >
> >>
> >>
> >>
>
>|||> I suspect that much of the heel-dragging stemmed from the fact that I am
> relative novice while the person who recommended the current plan is a guru.
I think you meant "...who recommended the current plan is *considered* a guru". Big difference. ;-)
Glad you got the approval. Good luck!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:CE00E476-0C1A-4E2B-88C7-CF78C31607DF@.microsoft.com...
>I am happy to say that I finally got approval for the new backup strategy,
> which I first proposed three months ago and which is very close to what Ekrem
> suggested.
> I suspect that much of the heel-dragging stemmed from the fact that I am
> relative novice while the person who recommended the current plan is a guru.
> But then he's not around anymore to clean up the mess if we do crash.
> "Jay" wrote:
>> Hi Bev,
>> You need tp explain that to be able to restore to a failure point, you must
>> have a full backup AND an unbroken chain of transaction log backups,
>> preferably off the machine being backed up (tape, NAS, or some other copy).
>> It's a business decision that they have made, you need to explain what they
>> actually decided.
>> FWIW, the maximum time I will allow to go by without a transaction log
>> backup is 60 minutes, the minimum is 1 minute (and in some narrow cases, I'm
>> thinking about doing it more often, I haven't, but I'm thinking about it).
>> Jay
>> (PS. this is an example of what I call the BS you have to deal with in IT.)
>> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> news:F11E52A8-E55F-4CB9-BA35-3842F0BA2FAB@.microsoft.com...
>> > Given the backup plan that I have been saddled with, what would you
>> > recommend
>> > I do in such a case?
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Bev
>> >> >Is there a way to specify only transactions for
>> >> > the current day?
>> >> No, as you say , you need to do a point in time restore
>> >>
>> >>
>> >> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> >> news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
>> >> >I inherited this backup strategy, and I do not have authority to change
>> >> >it.
>> >> >I
>> >> > am concerned about how I will handle this if there is a crash and I
>> >> > need
>> >> > to
>> >> > do a point in time restore.
>> >> > Full backups are done 6 days a week, Sunday through Friday.
>> >> > The transaction log is backed up once a week, on Sunday.
>> >> > In the event that the system crashed on Wednesday, I have the previous
>> >> > night's backup, but what about transactions during the day. If I run a
>> >> > transaction log backup at the point of the crash, it would give me all
>> >> > transactions since Sunday. Is there a way to specify only transactions
>> >> > for
>> >> > the current day?
>> >> >
>> >>
>> >>
>> >>
>>

Concatinating SmallInt Data Type

Newbie question regarding a db I have inherited.

A table FullDocuments has a DocNo column with smallint data type and a SequenceNo column also with smallint data type.

DocNohas numbers that represent persons. SequenceNo has numbers thatrepresent specific documents associated with each person (DocNo).

So DocNo 5 and Sequence 3 represents the 3rd document associated with person 5.

My SELECT statement looks like this:

SELECT ReadingNo, SequenceNo

This returns data like this: 5 3

I would like to concatenate the SELECT statement to return like this: 5-3

So I made Sql like this:

SELECT ReadingNo + '-" + SequenceNo

Whichreturns a alias ('No Column Named') result value of 8 which is anarithmetic result instead of a string concatination that I want.

So my questions are:

1. Should the original database designer have used string data types forthese columns since they will never be used for math purposes?

2. Do I need to cast them to string data type (like nchar(4) - sinceneither column will ever exceed 4 digits) to get the result I desire?

3. Or can I keep them as smallint and modify my SELECT statement to allow concatination yielding a string result?

Select Cast(ReadingNo As VarChar(5)) + '-' + Cast(SequenceNo As VarChar(5)) As DocNo

Basically what you need to do is cast (or convert) the two columns as either char or varchar so the sql engine knows to concat instead of adding.

|||

Excellent. Thanks so much. For future reference (should I have to create such a db from scratch), should I use varchar data type in this situation? Or is there a good reason (such as file size) to use the smallint type for such data?

|||

Depends on the business needs of the colums in your database.

If you have a need for the columns as int, smallint ect then use that.

When designing databases always look at 1st what your going to do with the data you are storing to help you determine best model.

|||

Thanks again for your input.