SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS and T-SQL's CHECKPOINT


SSIS and T-SQL's CHECKPOINT

Author
Message
John Waclawski
John Waclawski
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 418
Currently we have a rather elaborate backup job we run nightly on a very large reporting server.
We do our backups and for the databases we aren't backing up (due to them being replicated from a live server) we run a T-SQL CHECKPOINT on them. All in all, with every every step, this is over 157 steps. And when we add a new database, it's quite elaborate adding it in since we try to keep things alphabetized.

I've been building a SSIS package so all I have to do is fix 1 line of code to add a new database. This part works great! I won't go into detail with this other then it works very well.

I want to add the next step to this which is a loop that will read t-sql code and get a list of replicated databases that we run CHECKPOINT on. Now to be clear...this is NOT the checkpoint package within SSIS. This is the t-sql code "CHECKPOINT".

I've added a "Execute SQL Task" that is within a Sequence container, which is housed within a ForEach Loop Container, that reads from a "Execute SQL Task". The looping works fine. I have the t-sql CHECKPOINT within my "Execute SQL Task" but I'm not 100% sure if this working right.

So this leads me to several questions:

1. Will this work?
2. Is there a way to check to see if my CHECKPOINT ran on my list of databases?
3. If this isn't the right way, how would I do it?

I hope this makes sense for everyone.

Thank you for your time.

======================================================
John
SQL Server 200x Newb and proud of it! :-D
======================================================
sql-lover
sql-lover
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4473 Visits: 1930
John,

What's the purpose of the T-SQL CHECKPOINT, what are you trying to accomplish.

Also, not because a database is being replicated, can't be backed up. Actually, I have few databases that are involved in replication and they do have backups as well. I guess that I am not following you ... what could be the reason for not running backup on those, space maybe?
John Waclawski
John Waclawski
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 418
We backup the databases on the production server. No need to backup the database on the reporting server (the replicated server). Would be rather redundant.

Also, the T-SQL Checkpoint is done to write all the dirty pages to disk. We do that just to "clear things out" of the databases we aren't backing up.

I'd like to do that in my SSIS package. I know that's somewhat vague but this is what we've been doing for quite sometime but I'd like to simplify the process by using SSIS.

======================================================
John
SQL Server 200x Newb and proud of it! :-D
======================================================
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search