Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS and T-SQL's CHECKPOINT Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 1:29 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 23, 2014 9:42 AM
Points: 57, Visits: 385
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!
======================================================
Post #1448215
Posted Tuesday, April 30, 2013 1:50 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 8:53 AM
Points: 411, Visits: 1,310
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?
Post #1448222
Posted Tuesday, April 30, 2013 2:30 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 23, 2014 9:42 AM
Points: 57, Visits: 385
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!
======================================================
Post #1448236
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse