SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Other Development Tools
 Converting from a Stored proc to SSIS?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lebz
Starting Member

South Africa
7 Posts

Posted - 02/07/2011 :  05:41:57  Show Profile  Reply with Quote
We execute the stored proc each night with the following command:



declare @EndDateTime varchar(10)

set @EndDateTime = convert(varchar(10),dateadd(d,-1,getdate()),120)

exec sp_House_Keeping @EndDateTime ,'Yes',4



What we have found, is that the DB log file is growing too extreme sizes while the job is running. We know that this is because we are doing a bulk delete in a single transaction and all of the deleted rows end up in the LDF file in case the transaction requires a rollback.

One of the workarounds, is to use either a while loop or a cursor to delete each record in its own transaction.



Please can you convert this current stored proc into a SSIS package, having individual steps in SSIS for each step in the stored proc.Please can you convert this current stored proc into a SSIS package, having individual steps in SSIS for each step in the stored proc and that will also solve our Log file problem (deletes running in cursor or while loops). Do not worry too much about the current PRINT commands that is used. We do not require this anymore.

/****** Object: StoredProcedure [dbo].[sp_House_Keeping] Script Date: 02/03/2011 18:04:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp_House_Keeping]
@EndDate datetime = null,
@Keep4DA varchar(3) = null,
@Days4DA int = null
AS

SET NOCOUNT ON

create table #todel (
msgid varchar(50)
)

print '-*-*-*-*-*-*-*START-*-*-*-*-*-*-'
print '------' + convert(varchar(24),getdate(),120) + '-------'
print '--------------------------------'

-- If no parameters were set set defualts
IF @EndDate is null
SET @EndDate = convert(varchar(10),getdate(),120)
IF @Keep4DA is null or @Keep4DA = 'No'
select @Days4DA = 0, @Keep4DA = 'No'
IF @Keep4DA = 'Yes' and @Days4DA is null
SET @Days4DA = 14

print 'Date : ' + convert(varchar(24),@EndDate,120)
print 'Keep 4 DA : ' + @Keep4DA
print 'Days 4 DA : ' + convert(varchar(10),@Days4DA)

insert into #todel
select originalmessageid from activemessages where timestamp < @EndDate
print 'To Delete : ' + convert(varchar(20),@@ROWCOUNT)
delete #todel
from #todel
join retrymessages on msgid = originalmessageid
print 'In Retry : ' + convert(varchar(20),@@ROWCOUNT)

delete #todel
from #todel
join incompletemessages on msgid = messageid
print 'Incomplete : ' + convert(varchar(20),@@ROWCOUNT)

IF @Keep4DA = 'Yes'
BEGIN
delete #todel
from #todel
join
(select
a1.originalmessageid
from activemessages a1
left outer join activemessages a2 on a1.originalmessageid = a2.referencemessageid and a2.messagetype in (25, 28, 47)
where a1.messagetype in (23, 26, 45)
and a1.timestamp < @EndDate
and a2.originalmessageid is null
and a1.timestamp > dateadd(dd,-@Days4DA,@EndDate)
) no_da on msgid = originalmessageid
print 'No DAs : ' + convert(varchar(20),@@ROWCOUNT)
END

print '-----------DELETING-------------'

delete am
from activemessages am
join #todel on originalmessageid = msgid
print 'AM : ' + convert(varchar(20),@@ROWCOUNT)

delete mr
from messagerecipients mr
left outer join activemessages am on mr.originalmessageid = am.originalmessageid
where am.originalmessageid is null
print 'MR : ' + convert(varchar(20),@@ROWCOUNT)

delete mrs
from messagerecipientstates mrs
left outer join activemessages am on mrs.originalmessageid = am.originalmessageid
where am.originalmessageid is null
print 'MRS: ' + convert(varchar(20),@@ROWCOUNT)

/* commented out so that we have a history of the exception...
delete ex
from messageexception ex
left outer join activemessages am on ex.originalmessageid = am.originalmessageid
where am.originalmessageid is null
print 'EX : ' + convert(varchar(20),@@ROWCOUNT)
*/

drop table #todel

print 'Re-Indexing...'
DBCC DBREINDEX(activemessages)
DBCC DBREINDEX(messagerecipients)
DBCC DBREINDEX(messagerecipientstates)
DBCC DBREINDEX(messageexception)

print '--------------------------------'
print '------' + convert(varchar(24),getdate(),120) + '-------'
print '-*-*-*-*-*-*-*DONE*-*-*-*-*-*-*-'

SET NOCOUNT OFF
GO


tshepo

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 02/07/2011 :  05:49:33  Show Profile  Reply with Quote
Why would you expect people to do your work for you for free?!?

How many emails are you sending out a day to make your logfile grow beyond endurance? Do you spam the world on a daily basis?

You don't need SSIS for this, you just need to write a loop.

WHILE EXISTS ( your criteria )
BEGIN
<do your delete (you can use TOP (n) to help with this)>
END
Go to Top of Page

lebz
Starting Member

South Africa
7 Posts

Posted - 02/07/2011 :  07:35:07  Show Profile  Reply with Quote
about 100 emails per day.could you write the loop please.thankx

tshepo
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 02/07/2011 :  08:54:24  Show Profile  Reply with Quote
No, I am not your slave. I work for a living and give my time here for free.

If you can not work out how to do it, employ someone who can.

Oh, if your logfile are growing too big with just 100 messages a day, you have more serious issues or you have far too little disk space..

Edited by - RickD on 02/07/2011 08:55:44
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 02/07/2011 :  09:58:13  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
quote:
What we have found, is that the DB log file is growing too extreme sizes while the job is running.


For 100 emails? i think your problem lies elsewhere.

Expecting others to simply write code for you, for free...is a little much. You are not really asking for help...you have asked us to do your job for you. Good luck with that.



Poor planning on your part does not constitute an emergency on my part.
Go to Top of Page

lebz
Starting Member

South Africa
7 Posts

Posted - 02/07/2011 :  10:33:29  Show Profile  Reply with Quote
ok i hear you, lets forget the email part. lets say 10 emails. Please assist where possible. I nreally need to know how to convert from a stored proc to SSIS.

tshepo
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 02/07/2011 :  11:28:11  Show Profile  Reply with Quote
why do you need SSIS at all? I have already shown you how to do this in SQL, but it seems you either did not follow it or you still expect someone to do this for you.

If you really want to learn SSIS, pick up a book..
Go to Top of Page

lebz
Starting Member

South Africa
7 Posts

Posted - 02/07/2011 :  12:18:00  Show Profile  Reply with Quote
ok thanx. I get your point. is this the answer:
WHILE EXISTS ( your criteria )
BEGIN
<do your delete (you can use TOP (n) to help with this)>
END?
i need more information.could you show me the steps to cobert this into SSIS perhaps please i beg you.

tshepo
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 02/07/2011 :  12:21:26  Show Profile  Reply with Quote
Whats the fascination with SSIS still?

SQL has a great thing called Books Online. If you drop your fascination with SSIS for a moment and actually look up the words in CAPS, you might learn something. If you can find them in BoL, then google will help you, look for deleting in batch, sql server..
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 02/07/2011 :  14:07:04  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
It makes no sense to convert from a stored procedure to SSIS based on what you describe.



Poor planning on your part does not constitute an emergency on my part.
Go to Top of Page

lebz
Starting Member

South Africa
7 Posts

Posted - 02/08/2011 :  03:37:11  Show Profile  Reply with Quote
okay i get you.

how do u convert this in to SSIS?

declare @EndDateTime varchar(10)

set @EndDateTime = convert(varchar(10),dateadd(d,-1,getdate()),120)

exec sp_House_Keeping @EndDateTime ,'Yes',4


tshepo
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 02/08/2011 :  05:27:44  Show Profile  Reply with Quote
I give up...

Create a SQL task and put your bl**dy code in the SQL task, Then you will have your prized SSIS piece of carp instead of the prefectly resonable T-SQL piece of carp you have at the moment.

Good luck in the future..
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000