Alerts and Jobs for transaction log processing

Scenario: I have an alert defined which monitors the transaction log. It should start a job which should backup the transaction log once the log is 75 percent full.

So far so good.

The challenge is to give the database name to the command for backing up (in our case it is a external tool) the appropriate transaction log

I know, that you can use tokens (A-DBN) to do this.

I know, that there is a bug in this area, so tokens are not set correctly in SP1

Question:

Is there any trick/alternative to create a workaround?

Regards

Norbert

If you created a WMI Event alert you may be able to read the Database name as part of the event $(WMI(DatabaseName))

Aid with store procedure

Hello, I have a problem, I want to execute in a single transaction three insert in store procedure, as it could implement. Thanks for its attentione.g.

create proc usp
as
begin tran
insert tb1 values(...)
if @.@.error<>0 goto rollbk
insert tb2 values(...)
if @.@.error<>0 goto rollbk
insert tb3 values(...)
if @.@.error=0
begin
commit
goto done
end

rollbk:
rollback

done:
return

GO

Ageing from system date by number of days

Can anyone help with how I write a script that takes a balance and then age
it by its transaction dates into 30, 60 90 days etc?
My script is as follows and the "AS2 on the 2nd line is coming out as
incorrect syntax:
SELECT
GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(MONTH, TransactionDate, GETDATE()) AS no_of_days_since,
SUM(GoodsValueInAccountCurrency) AS THIRTY WHERE (no_of_days_since BETWEEN 1
AND 30) AND
SUM(GoodsValueInAccountCurrency) AS SIXTY WHERE (no_of_days_since between 31
and 60) AND
SUM(GoodsValueInAccountCurrency) AS NINETY WHERE (no_of_days_since between
61 and 90)AND
SUM(GoodsValueInAccountCurrency) AS MORE WHERE (no_of_days_since > 90)
FROM PLPostedSupplierTran
"WendyUK" wrote:

> Can anyone help with how I write a script that takes a balance and then age
> it by its transaction dates into 30, 60 90 days etc?
|||Wendy,
A couple of things:
1 - DATEDIFF (MONTH... ) does not give you what you want since February 28
and March 1 are one month apart. Use DAY instead of MONTH.
2 - WHERE appears after the FROM and only 1 per query. For your columns,
read about the CASE statement. You will need something like the following
untested code:
SELECT GoodsValueInAccountCurrency, TransactionDate,
SUM (CASE ((DATEDIFF(DAY,TransactionDate, GETDATE()) - 1) / 30)
WHEN(0) THEN GoodsValueInAccountCurrency
ELSE 0
END ) AS THIRTY,
SUM (CASE ((DATEDIFF(DAY,TransactionDate, GETDATE()) - 1) / 30)
WHEN(1) THEN GoodsValueInAccountCurrency
ELSE 0
END ) AS SIXTY,
. . . etc
RLF
"WendyUK" <WendyUK@.discussions.microsoft.com> wrote in message
news:681A829D-F604-41D0-A485-B9B92132180C@.microsoft.com...[vbcol=seagreen]
> My script is as follows and the "AS2 on the 2nd line is coming out as
> incorrect syntax:
> SELECT
> GoodsValueInAccountCurrency, TransactionDate,
> DATEDIFF(MONTH, TransactionDate, GETDATE()) AS no_of_days_since,
> SUM(GoodsValueInAccountCurrency) AS THIRTY WHERE (no_of_days_since BETWEEN
> 1
> AND 30) AND
> SUM(GoodsValueInAccountCurrency) AS SIXTY WHERE (no_of_days_since between
> 31
> and 60) AND
> SUM(GoodsValueInAccountCurrency) AS NINETY WHERE (no_of_days_since between
> 61 and 90)AND
> SUM(GoodsValueInAccountCurrency) AS MORE WHERE (no_of_days_since > 90)
>
> FROM PLPostedSupplierTran
>
> "WendyUK" wrote:

again dead lock

hi i m getting below error.

why i m getting this error...

Transaction(Process ID 61) was deadlockeds on lock resources with another process and has been
chosen as the deadlock victim.Return the transaction.82

below is procedure

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE Usp_CMSUpdateSchemGroup
(
@.CMS_Upload_Master_ID numeric =null,
@.Maker numeric =null,
@.BnkName varchar(50)=null
)
AS
BEGIN

update Tbl_CMS_UploadDetails
set CMS_SchemeGroup = d.MICR_SchemeGroup
from Tbl_CMS_UploadDetails INNER JOIN
(
Select a.MICR_CMSCode
, a.MICR_SchemeGroup
From Tbl_MICRMst a INNER JOIN
Tbl_BankMst b ON a.MICR_BankName = rtrim(ltrim(b.Bank_Name))
WHERE a.MICR_AuthStatus = 2
and a.MICR_Optype = 0
and b.Bank_isactive=1

) d ON Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))
where Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))

declare @.Format_ID numeric
select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name=@.BnkName

print @.Format_ID --+ @.Bank_Name

update Tbl_CMS_UploadDetails
set Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending',
Format_ID=@.Format_ID
Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID

--Added By Gopal For HDFC Bounce cases 24-Feb-07
Update Tbl_CMS_UploadDetails Set amount =-amount where format_id='83' and drcr ='D' and amount>0 and compare_status='Pending'
Update Tbl_CMS_UploadDetails Set amount =-amount where format_id in('82','83') and rtrim(ltrim(drcr)) ='C' and amount<0 and compare_status='Pending'
Update Tbl_CMS_UploadDetails Set amount =-amount where format_id='82' and rtrim(ltrim(drcr)) ='B' and amount>0 and compare_status='Pending'
--Added by Nachiket for failure

Update Tbl_CMS_UploadDetails Set amount =-amount where format_id in ('89','91','86') and amount>0 and compare_status='Pending'

Update Tbl_CMS_UploadDetails Set amount =abs(amount) where format_id in ('85','88','90') and amount<0 and compare_status='Pending'
Declare @.recCount int
select @.recCount=count(*) from tbl_cms_uploaddetails where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID
update tbl_cms_uploadMaster set rec_count =@.recCount
Where
CMS_Upload_Master_ID=@.Cms_Upload_Master_Id
Declare @.amount decimal
select @.amount=SUM(Amount) from tbl_cms_uploaddetails where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID
update tbl_cms_uploadMaster set Amount =@.amount
Where
CMS_Upload_Master_ID=@.Cms_Upload_Master_Id

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Deadlocking occurs because 2 processes are competing for the same resources like this

Process 1 is holding Table A & is waiting for Table B to complete.

Process 2 is holding Table B & is waiting for Table A to complete.

Neither will let go off the resource it has. SQL Server detects this dead-locking and will randomly choose one to kill.

DBCC TRACEON (3650, 1205, -1) will write the details of the 2 processes of the deadlocking into the errorlog each times that it occurs but the error log can grow very fast.

An easy way to avoid deadlocking is to ensure the all processes access tables in the same order (e.g. Customer, Accounts, Transactions or maybe in alphabetical order if this works for you)

regards,

Niall.

|||hey thanx for reply...but how to solve my problem?|||

You need to run the traceon to find out what the other process (involved in the deadlocking) is doing. Then you can figure out which objects are used by both procedures/routines and how the order of access to these objects might differ.

Once you have that, it can be fixed.

Niall.

After Transaction Log Grows, it won't shrink.

The other day I had someone truncate an extremely large transaction log (100GB) but it wouldn't return the space to the OS, not even after a backup and restore operation. There’s an interesting bit of trivia around shrinking this file. When the log file is truncated, you may not see all of the space returned to the OS. The reason is the way the data might be spread. Here’s the way we think data looks in the log file:

<BeginFile>xxxxxxxx--<EndFile>

So when you truncate it and it gets another couple of transactions, it should do this:

<BeginFile>xx<EndFile>

But in fact it might look more like this:

<BeginFile>xx-xx-xxxxxxx<EndFile>

So if you truncate the entries and a couple are left it really looks like this:

<BeginFile>x--x<EndFile>

So when you shrink it, you don’t gain any space back because of this file marking. So what do you do?

There are two ways to shrink a large log file, both of which are a bit painful, and not always practical in production. The first is to write enough transactions into the log to “bump” the data all the way to the end. Since that makes it contiguous, when you truncate it you’ll get the shrink you’re looking for – probably. You can do this by creating a table, adding thousands or millions of rows to it, and then deleting them and the table. Of course this slows down a production system while you do it, and it’s difficult to figure the math.

The other way is more problematic in a production system, but works every time. Take a full backup of the database. Follow the process to “detach” the database as described in Books Online. Find the transaction log file for that database (and only that database) and delete it. Then, “attach” the database. The system will automatically build a new, clean log file. Obviously, there are a lot of places you can be careless here, so I recommend this process only for extreme cases.

Hi,

have a look here:

http://www.aspfaq.com/show.asp?id=2471

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Good info - but you'll find that the DBCC shrink commands won't work with the TL as I mentioned. It's less a question than some real-world experience - but thanks for the links! I think they will help people to understand how the logs and files are used.

advice on transaction log

I have a database that i make a copy of everynight. I delete all the tables
in the destination database and recreate it, ensuring that the destination
database reflects all the new tables and columns from the original. This
destination database is used for reporting only, so the last step i do in
the copy is set the destination database to readonly. The copy is done
using a dts package that is scheduled to run at a given time everynight.
Since i delete all the tables everynight, i don't do backups. Therefore my
transaction log continues to grow. I don't ever need any information from
the transaction log, since if the database fails then i would just recreate
the whole database by running the dts package (this only takes 10 minutes).
What is the best way to keep the transction log to a minimum size.
Thank you
Mike
Set the recovery mode to Simple on the readonly database. That will
cause any transactions completed to be automatically truncated from
the log.
Allen White
On Wed, 26 Jan 2005 10:34:28 -0500, "Mike Read"
<mike.read.spam.no@.allwaresolutions.com> wrote:

>I have a database that i make a copy of everynight. I delete all the tables
>in the destination database and recreate it, ensuring that the destination
>database reflects all the new tables and columns from the original. This
>destination database is used for reporting only, so the last step i do in
>the copy is set the destination database to readonly. The copy is done
>using a dts package that is scheduled to run at a given time everynight.
>Since i delete all the tables everynight, i don't do backups. Therefore my
>transaction log continues to grow. I don't ever need any information from
>the transaction log, since if the database fails then i would just recreate
>the whole database by running the dts package (this only takes 10 minutes).
>What is the best way to keep the transction log to a minimum size.
>Thank you
>Mike
>
|||Mike
The answer to your question is that you need to set your database to simple
recovery mode.
I would however suggest you look into using log shipping or snapshot
replication to maintain your read only database. These are much better
methods to do what you want.
Hope this helps
John
"Mike Read" wrote:

> I have a database that i make a copy of everynight. I delete all the tables
> in the destination database and recreate it, ensuring that the destination
> database reflects all the new tables and columns from the original. This
> destination database is used for reporting only, so the last step i do in
> the copy is set the destination database to readonly. The copy is done
> using a dts package that is scheduled to run at a given time everynight.
> Since i delete all the tables everynight, i don't do backups. Therefore my
> transaction log continues to grow. I don't ever need any information from
> the transaction log, since if the database fails then i would just recreate
> the whole database by running the dts package (this only takes 10 minutes).
> What is the best way to keep the transction log to a minimum size.
> Thank you
> Mike
>
>

advice on transaction log

I have a database that i make a copy of everynight. I delete all the tables
in the destination database and recreate it, ensuring that the destination
database reflects all the new tables and columns from the original. This
destination database is used for reporting only, so the last step i do in
the copy is set the destination database to readonly. The copy is done
using a dts package that is scheduled to run at a given time everynight.
Since i delete all the tables everynight, i don't do backups. Therefore my
transaction log continues to grow. I don't ever need any information from
the transaction log, since if the database fails then i would just recreate
the whole database by running the dts package (this only takes 10 minutes).
What is the best way to keep the transction log to a minimum size.
Thank you
MikeSet the recovery mode to Simple on the readonly database. That will
cause any transactions completed to be automatically truncated from
the log.
Allen White
On Wed, 26 Jan 2005 10:34:28 -0500, "Mike Read"
<mike.read.spam.no@.allwaresolutions.com> wrote:

>I have a database that i make a copy of everynight. I delete all the table
s
>in the destination database and recreate it, ensuring that the destination
>database reflects all the new tables and columns from the original. This
>destination database is used for reporting only, so the last step i do in
>the copy is set the destination database to readonly. The copy is done
>using a dts package that is scheduled to run at a given time everynight.
>Since i delete all the tables everynight, i don't do backups. Therefore my
>transaction log continues to grow. I don't ever need any information from
>the transaction log, since if the database fails then i would just recreate
>the whole database by running the dts package (this only takes 10 minutes).
>What is the best way to keep the transction log to a minimum size.
>Thank you
>Mike
>|||Mike
The answer to your question is that you need to set your database to simple
recovery mode.
I would however suggest you look into using log shipping or snapshot
replication to maintain your read only database. These are much better
methods to do what you want.
Hope this helps
John
"Mike Read" wrote:

> I have a database that i make a copy of everynight. I delete all the tabl
es
> in the destination database and recreate it, ensuring that the destination
> database reflects all the new tables and columns from the original. This
> destination database is used for reporting only, so the last step i do in
> the copy is set the destination database to readonly. The copy is done
> using a dts package that is scheduled to run at a given time everynight.
> Since i delete all the tables everynight, i don't do backups. Therefore m
y
> transaction log continues to grow. I don't ever need any information from
> the transaction log, since if the database fails then i would just recreat
e
> the whole database by running the dts package (this only takes 10 minutes)
.
> What is the best way to keep the transction log to a minimum size.
> Thank you
> Mike
>
>

advice on transaction log

I have a database that i make a copy of everynight. I delete all the tables
in the destination database and recreate it, ensuring that the destination
database reflects all the new tables and columns from the original. This
destination database is used for reporting only, so the last step i do in
the copy is set the destination database to readonly. The copy is done
using a dts package that is scheduled to run at a given time everynight.
Since i delete all the tables everynight, i don't do backups. Therefore my
transaction log continues to grow. I don't ever need any information from
the transaction log, since if the database fails then i would just recreate
the whole database by running the dts package (this only takes 10 minutes).
What is the best way to keep the transction log to a minimum size.
Thank you
MikeSet the recovery mode to Simple on the readonly database. That will
cause any transactions completed to be automatically truncated from
the log.
Allen White
On Wed, 26 Jan 2005 10:34:28 -0500, "Mike Read"
<mike.read.spam.no@.allwaresolutions.com> wrote:
>I have a database that i make a copy of everynight. I delete all the tables
>in the destination database and recreate it, ensuring that the destination
>database reflects all the new tables and columns from the original. This
>destination database is used for reporting only, so the last step i do in
>the copy is set the destination database to readonly. The copy is done
>using a dts package that is scheduled to run at a given time everynight.
>Since i delete all the tables everynight, i don't do backups. Therefore my
>transaction log continues to grow. I don't ever need any information from
>the transaction log, since if the database fails then i would just recreate
>the whole database by running the dts package (this only takes 10 minutes).
>What is the best way to keep the transction log to a minimum size.
>Thank you
>Mike
>|||Mike
The answer to your question is that you need to set your database to simple
recovery mode.
I would however suggest you look into using log shipping or snapshot
replication to maintain your read only database. These are much better
methods to do what you want.
Hope this helps
John
"Mike Read" wrote:
> I have a database that i make a copy of everynight. I delete all the tables
> in the destination database and recreate it, ensuring that the destination
> database reflects all the new tables and columns from the original. This
> destination database is used for reporting only, so the last step i do in
> the copy is set the destination database to readonly. The copy is done
> using a dts package that is scheduled to run at a given time everynight.
> Since i delete all the tables everynight, i don't do backups. Therefore my
> transaction log continues to grow. I don't ever need any information from
> the transaction log, since if the database fails then i would just recreate
> the whole database by running the dts package (this only takes 10 minutes).
> What is the best way to keep the transction log to a minimum size.
> Thank you
> Mike
>
>