Hi:
Is the maintenance plan correct ?
1. Perform DBCC CheckDB on Saturday 8:00PM
2. Transactions log backup will occurs from 8:00AM to 11:59PM occurs
every 15 minute, from Monday to Friday and Saturday will start on
8:00AM to 4:00AM
3. Database Full backup will start on 9:00PM from Monday to Friday and
Saturday will backup on 4:00PM.
4. System will remove the transaction log backup which is more than 4
days.
5. Rebuild index will start from 8:35PM to 10:59PM on every Friday. (I
use script to rebuild the index, so that only the fragmented index file
will be rebuild)
Note: Replication will start from 8:00AM to 8:30PM, occurs every 15
minute, from Monday to Saturday.
Will the replication cause the rebuild index fail ? Can transaction log
backup and rebuild index happen at the same time ? I'm afraid the log
file will grow too large during rebuilt index if i don't perform trx
log backup.
Please Advice
Thanks
JCVoonRegarding point 5, do you mean you are running an index defrag? An index
rebuild is on offline operation which will mean the tables will go offline
as the index is being rebuilt. For large tables or if you are a 24x7 shop
this is probably not what you want. An index defrag will cause your tlogs to
ballon considerably, so you might want to shrink them somewhat after the
defrag.
Replication is not impacted by index defrags or rebuilds. Lengthy rebuilds
will cause the tlogs to grow, but you can dump them in the middle of a
rebuild.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136456692.089320.162270@.g49g2000cwa.googlegroups.com...
> Hi:
> Is the maintenance plan correct ?
> 1. Perform DBCC CheckDB on Saturday 8:00PM
> 2. Transactions log backup will occurs from 8:00AM to 11:59PM occurs
> every 15 minute, from Monday to Friday and Saturday will start on
> 8:00AM to 4:00AM
> 3. Database Full backup will start on 9:00PM from Monday to Friday and
> Saturday will backup on 4:00PM.
> 4. System will remove the transaction log backup which is more than 4
> days.
> 5. Rebuild index will start from 8:35PM to 10:59PM on every Friday. (I
> use script to rebuild the index, so that only the fragmented index file
> will be rebuild)
> Note: Replication will start from 8:00AM to 8:30PM, occurs every 15
> minute, from Monday to Saturday.
> Will the replication cause the rebuild index fail ? Can transaction log
> backup and rebuild index happen at the same time ? I'm afraid the log
> file will grow too large during rebuilt index if i don't perform trx
> log backup.
> Please Advice
> Thanks
> JCVoon
>|||Hilary Cotter
Regarding point 5, I'm running an index rebuild, there will be no
operation after 8:00PM
I'm rely on the transaction log backup to minimise the log file grow
during rebuild index, is it possible ?
And for full database backup, the transaction log backup may trigger
during full database backup, is there any side effect ?
The rebuild index and full database backup, which should come first ?
any side effect if the full database backup and rebuild index happen at
the same time ? because i've time overlap on the full database backup
and rebuild index.
Thanks for your help.
Regards
JCvoon
Showing posts with label maintenance. Show all posts
Showing posts with label maintenance. Show all posts
Monday, March 12, 2012
Need adviceon SQL Server 2000 Maintenance Plan
Hi:
Is the maintenance plan correct ?
1. Perform DBCC CheckDB on Saturday 8:00PM
2. Transactions log backup will occurs from 8:00AM to 11:59PM occurs
every 15 minute, from Monday to Friday and Saturday will start on
8:00AM to 4:00AM
3. Database Full backup will start on 9:00PM from Monday to Friday and
Saturday will backup on 4:00PM.
4. System will remove the transaction log backup which is more than 4
days.
5. Rebuild index will start from 8:35PM to 10:59PM on every Friday. (I
use script to rebuild the index, so that only the fragmented index file
will be rebuild)
Note: Replication will start from 8:00AM to 8:30PM, occurs every 15
minute, from Monday to Saturday.
Will the replication cause the rebuild index fail ? Can transaction log
backup and rebuild index happen at the same time ? I'm afraid the log
file will grow too large during rebuilt index if i don't perform trx
log backup.
Please Advice
Thanks
JCVoonRegarding point 5, do you mean you are running an index defrag? An index
rebuild is on offline operation which will mean the tables will go offline
as the index is being rebuilt. For large tables or if you are a 24x7 shop
this is probably not what you want. An index defrag will cause your tlogs to
ballon considerably, so you might want to shrink them somewhat after the
defrag.
Replication is not impacted by index defrags or rebuilds. Lengthy rebuilds
will cause the tlogs to grow, but you can dump them in the middle of a
rebuild.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136456692.089320.162270@.g49g2000cwa.googlegroups.com...
> Hi:
> Is the maintenance plan correct ?
> 1. Perform DBCC CheckDB on Saturday 8:00PM
> 2. Transactions log backup will occurs from 8:00AM to 11:59PM occurs
> every 15 minute, from Monday to Friday and Saturday will start on
> 8:00AM to 4:00AM
> 3. Database Full backup will start on 9:00PM from Monday to Friday and
> Saturday will backup on 4:00PM.
> 4. System will remove the transaction log backup which is more than 4
> days.
> 5. Rebuild index will start from 8:35PM to 10:59PM on every Friday. (I
> use script to rebuild the index, so that only the fragmented index file
> will be rebuild)
> Note: Replication will start from 8:00AM to 8:30PM, occurs every 15
> minute, from Monday to Saturday.
> Will the replication cause the rebuild index fail ? Can transaction log
> backup and rebuild index happen at the same time ? I'm afraid the log
> file will grow too large during rebuilt index if i don't perform trx
> log backup.
> Please Advice
> Thanks
> JCVoon
>|||Hilary Cotter
Regarding point 5, I'm running an index rebuild, there will be no
operation after 8:00PM
I'm rely on the transaction log backup to minimise the log file grow
during rebuild index, is it possible ?
And for full database backup, the transaction log backup may trigger
during full database backup, is there any side effect ?
The rebuild index and full database backup, which should come first ?
any side effect if the full database backup and rebuild index happen at
the same time ? because i've time overlap on the full database backup
and rebuild index.
Thanks for your help.
Regards
JCvoon
Is the maintenance plan correct ?
1. Perform DBCC CheckDB on Saturday 8:00PM
2. Transactions log backup will occurs from 8:00AM to 11:59PM occurs
every 15 minute, from Monday to Friday and Saturday will start on
8:00AM to 4:00AM
3. Database Full backup will start on 9:00PM from Monday to Friday and
Saturday will backup on 4:00PM.
4. System will remove the transaction log backup which is more than 4
days.
5. Rebuild index will start from 8:35PM to 10:59PM on every Friday. (I
use script to rebuild the index, so that only the fragmented index file
will be rebuild)
Note: Replication will start from 8:00AM to 8:30PM, occurs every 15
minute, from Monday to Saturday.
Will the replication cause the rebuild index fail ? Can transaction log
backup and rebuild index happen at the same time ? I'm afraid the log
file will grow too large during rebuilt index if i don't perform trx
log backup.
Please Advice
Thanks
JCVoonRegarding point 5, do you mean you are running an index defrag? An index
rebuild is on offline operation which will mean the tables will go offline
as the index is being rebuilt. For large tables or if you are a 24x7 shop
this is probably not what you want. An index defrag will cause your tlogs to
ballon considerably, so you might want to shrink them somewhat after the
defrag.
Replication is not impacted by index defrags or rebuilds. Lengthy rebuilds
will cause the tlogs to grow, but you can dump them in the middle of a
rebuild.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136456692.089320.162270@.g49g2000cwa.googlegroups.com...
> Hi:
> Is the maintenance plan correct ?
> 1. Perform DBCC CheckDB on Saturday 8:00PM
> 2. Transactions log backup will occurs from 8:00AM to 11:59PM occurs
> every 15 minute, from Monday to Friday and Saturday will start on
> 8:00AM to 4:00AM
> 3. Database Full backup will start on 9:00PM from Monday to Friday and
> Saturday will backup on 4:00PM.
> 4. System will remove the transaction log backup which is more than 4
> days.
> 5. Rebuild index will start from 8:35PM to 10:59PM on every Friday. (I
> use script to rebuild the index, so that only the fragmented index file
> will be rebuild)
> Note: Replication will start from 8:00AM to 8:30PM, occurs every 15
> minute, from Monday to Saturday.
> Will the replication cause the rebuild index fail ? Can transaction log
> backup and rebuild index happen at the same time ? I'm afraid the log
> file will grow too large during rebuilt index if i don't perform trx
> log backup.
> Please Advice
> Thanks
> JCVoon
>|||Hilary Cotter
Regarding point 5, I'm running an index rebuild, there will be no
operation after 8:00PM
I'm rely on the transaction log backup to minimise the log file grow
during rebuild index, is it possible ?
And for full database backup, the transaction log backup may trigger
during full database backup, is there any side effect ?
The rebuild index and full database backup, which should come first ?
any side effect if the full database backup and rebuild index happen at
the same time ? because i've time overlap on the full database backup
and rebuild index.
Thanks for your help.
Regards
JCvoon
Need adviceon SQL Server 2000 Maintenance Plan
Hi:
Is the maintenance plan correct ?
1. Perform DBCC CheckDB on Saturday 8:00PM
2. Transactions log backup will occurs from 8:00AM to 11:59PM occurs
every 15 minute, from Monday to Friday and Saturday will start on
8:00AM to 4:00AM
3. Database Full backup will start on 9:00PM from Monday to Friday and
Saturday will backup on 4:00PM.
4. System will remove the transaction log backup which is more than 4
days.
5. Rebuild index will start from 8:35PM to 10:59PM on every Friday. (I
use script to rebuild the index, so that only the fragmented index file
will be rebuild)
Note: Replication will start from 8:00AM to 8:30PM, occurs every 15
minute, from Monday to Saturday.
Will the replication cause the rebuild index fail ? Can transaction log
backup and rebuild index happen at the same time ? I'm afraid the log
file will grow too large during rebuilt index if i don't perform trx
log backup.
Please Advice
Thanks
JCVoon
Regarding point 5, do you mean you are running an index defrag? An index
rebuild is on offline operation which will mean the tables will go offline
as the index is being rebuilt. For large tables or if you are a 24x7 shop
this is probably not what you want. An index defrag will cause your tlogs to
ballon considerably, so you might want to shrink them somewhat after the
defrag.
Replication is not impacted by index defrags or rebuilds. Lengthy rebuilds
will cause the tlogs to grow, but you can dump them in the middle of a
rebuild.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136456692.089320.162270@.g49g2000cwa.googlegr oups.com...
> Hi:
> Is the maintenance plan correct ?
> 1. Perform DBCC CheckDB on Saturday 8:00PM
> 2. Transactions log backup will occurs from 8:00AM to 11:59PM occurs
> every 15 minute, from Monday to Friday and Saturday will start on
> 8:00AM to 4:00AM
> 3. Database Full backup will start on 9:00PM from Monday to Friday and
> Saturday will backup on 4:00PM.
> 4. System will remove the transaction log backup which is more than 4
> days.
> 5. Rebuild index will start from 8:35PM to 10:59PM on every Friday. (I
> use script to rebuild the index, so that only the fragmented index file
> will be rebuild)
> Note: Replication will start from 8:00AM to 8:30PM, occurs every 15
> minute, from Monday to Saturday.
> Will the replication cause the rebuild index fail ? Can transaction log
> backup and rebuild index happen at the same time ? I'm afraid the log
> file will grow too large during rebuilt index if i don't perform trx
> log backup.
> Please Advice
> Thanks
> JCVoon
>
|||Hilary Cotter
Regarding point 5, I'm running an index rebuild, there will be no
operation after 8:00PM
I'm rely on the transaction log backup to minimise the log file grow
during rebuild index, is it possible ?
And for full database backup, the transaction log backup may trigger
during full database backup, is there any side effect ?
The rebuild index and full database backup, which should come first ?
any side effect if the full database backup and rebuild index happen at
the same time ? because i've time overlap on the full database backup
and rebuild index.
Thanks for your help.
Regards
JCvoon
Is the maintenance plan correct ?
1. Perform DBCC CheckDB on Saturday 8:00PM
2. Transactions log backup will occurs from 8:00AM to 11:59PM occurs
every 15 minute, from Monday to Friday and Saturday will start on
8:00AM to 4:00AM
3. Database Full backup will start on 9:00PM from Monday to Friday and
Saturday will backup on 4:00PM.
4. System will remove the transaction log backup which is more than 4
days.
5. Rebuild index will start from 8:35PM to 10:59PM on every Friday. (I
use script to rebuild the index, so that only the fragmented index file
will be rebuild)
Note: Replication will start from 8:00AM to 8:30PM, occurs every 15
minute, from Monday to Saturday.
Will the replication cause the rebuild index fail ? Can transaction log
backup and rebuild index happen at the same time ? I'm afraid the log
file will grow too large during rebuilt index if i don't perform trx
log backup.
Please Advice
Thanks
JCVoon
Regarding point 5, do you mean you are running an index defrag? An index
rebuild is on offline operation which will mean the tables will go offline
as the index is being rebuilt. For large tables or if you are a 24x7 shop
this is probably not what you want. An index defrag will cause your tlogs to
ballon considerably, so you might want to shrink them somewhat after the
defrag.
Replication is not impacted by index defrags or rebuilds. Lengthy rebuilds
will cause the tlogs to grow, but you can dump them in the middle of a
rebuild.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136456692.089320.162270@.g49g2000cwa.googlegr oups.com...
> Hi:
> Is the maintenance plan correct ?
> 1. Perform DBCC CheckDB on Saturday 8:00PM
> 2. Transactions log backup will occurs from 8:00AM to 11:59PM occurs
> every 15 minute, from Monday to Friday and Saturday will start on
> 8:00AM to 4:00AM
> 3. Database Full backup will start on 9:00PM from Monday to Friday and
> Saturday will backup on 4:00PM.
> 4. System will remove the transaction log backup which is more than 4
> days.
> 5. Rebuild index will start from 8:35PM to 10:59PM on every Friday. (I
> use script to rebuild the index, so that only the fragmented index file
> will be rebuild)
> Note: Replication will start from 8:00AM to 8:30PM, occurs every 15
> minute, from Monday to Saturday.
> Will the replication cause the rebuild index fail ? Can transaction log
> backup and rebuild index happen at the same time ? I'm afraid the log
> file will grow too large during rebuilt index if i don't perform trx
> log backup.
> Please Advice
> Thanks
> JCVoon
>
|||Hilary Cotter
Regarding point 5, I'm running an index rebuild, there will be no
operation after 8:00PM
I'm rely on the transaction log backup to minimise the log file grow
during rebuild index, is it possible ?
And for full database backup, the transaction log backup may trigger
during full database backup, is there any side effect ?
The rebuild index and full database backup, which should come first ?
any side effect if the full database backup and rebuild index happen at
the same time ? because i've time overlap on the full database backup
and rebuild index.
Thanks for your help.
Regards
JCvoon
Need advice on db maintenance plan
Hi:
I need advice on creating the maintenance plan.
12:00 AM ~ 1:59 AM DTS / Archive Job
02:00 AM ~ 2:59 AM Full DB Backup
03:00 AM ~ 3:59 AM DB Integrity Check
04:00 AM ~ 5:59 AM Defrag Index
04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
Can the trx log backup schedule to start from today 4:00 AM until next
day 1:59 AM ? or i need to create two scheduler job to backup the trx
log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
11:59:59PM ?
I schedule the log backup to start from 4:00 AM is because i need the
trx log backup to truncate the log file during defrag index (04:00 AM ~
5:59 AM).
I need to backup the trx log during the DTS/Archive Job (12:00 AM to
1:59 AM) before start the full db backup.
If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
what will happen at 2:00AM when the full db backup start ? will it run
the trx log backup first or the full db backup first ? will the
scheduler queue the request or it just simply ignore one of them ?
If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
and integrity check), what will happen if the db crash after the the
full db backup completed (after 2:59 AM), i only able to recover the db
up to 2:00AM ?
Please help.
Thanks
JCVoon
What version of SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143183567.147117.216070@.e56g2000cwe.googlegr oups.com...
> Hi:
> I need advice on creating the maintenance plan.
> 12:00 AM ~ 1:59 AM DTS / Archive Job
> 02:00 AM ~ 2:59 AM Full DB Backup
> 03:00 AM ~ 3:59 AM DB Integrity Check
> 04:00 AM ~ 5:59 AM Defrag Index
> 04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
> Can the trx log backup schedule to start from today 4:00 AM until next
> day 1:59 AM ? or i need to create two scheduler job to backup the trx
> log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
> 1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
> 11:59:59PM ?
> I schedule the log backup to start from 4:00 AM is because i need the
> trx log backup to truncate the log file during defrag index (04:00 AM ~
> 5:59 AM).
> I need to backup the trx log during the DTS/Archive Job (12:00 AM to
> 1:59 AM) before start the full db backup.
> If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
> what will happen at 2:00AM when the full db backup start ? will it run
> the trx log backup first or the full db backup first ? will the
> scheduler queue the request or it just simply ignore one of them ?
> If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
> and integrity check), what will happen if the db crash after the the
> full db backup completed (after 2:59 AM), i only able to recover the db
> up to 2:00AM ?
>
> Please help.
> Thanks
> JCVoon
>
|||Tibor Karaszi:
Oops...sorry, forgot to mention. It is SQL Server 2000 ent
Regards
JCVoon
|||Prior to 2005, a database backup will block a log backup. So you can have your log backups scheduled
if you wish, the log backup job will just sit a wait until the database has been performed. Also,
Agent will not start a job if it is already running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143188648.197598.226630@.t31g2000cwb.googlegr oups.com...
> Tibor Karaszi:
> Oops...sorry, forgot to mention. It is SQL Server 2000 ent
> Regards
> JCVoon
>
|||Tibor Karaszi
Thanks.
>Prior to 2005, a database backup will block a log backup.
I'm using SQL 2000, block the log backup means the log backup will be
abort and generate error ? will the log backup block the full database
backup ?
Regards
JCVoon
|||> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ?
No, it will wait until the database backup has finished.
> will the log backup block the full database
> backup ?
Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143253350.681426.250150@.t31g2000cwb.googlegr oups.com...
> Tibor Karaszi
> Thanks.
> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ? will the log backup block the full database
> backup ?
> Regards
> JCVoon
>
|||Thanks
JCVoon
I need advice on creating the maintenance plan.
12:00 AM ~ 1:59 AM DTS / Archive Job
02:00 AM ~ 2:59 AM Full DB Backup
03:00 AM ~ 3:59 AM DB Integrity Check
04:00 AM ~ 5:59 AM Defrag Index
04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
Can the trx log backup schedule to start from today 4:00 AM until next
day 1:59 AM ? or i need to create two scheduler job to backup the trx
log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
11:59:59PM ?
I schedule the log backup to start from 4:00 AM is because i need the
trx log backup to truncate the log file during defrag index (04:00 AM ~
5:59 AM).
I need to backup the trx log during the DTS/Archive Job (12:00 AM to
1:59 AM) before start the full db backup.
If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
what will happen at 2:00AM when the full db backup start ? will it run
the trx log backup first or the full db backup first ? will the
scheduler queue the request or it just simply ignore one of them ?
If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
and integrity check), what will happen if the db crash after the the
full db backup completed (after 2:59 AM), i only able to recover the db
up to 2:00AM ?
Please help.
Thanks
JCVoon
What version of SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143183567.147117.216070@.e56g2000cwe.googlegr oups.com...
> Hi:
> I need advice on creating the maintenance plan.
> 12:00 AM ~ 1:59 AM DTS / Archive Job
> 02:00 AM ~ 2:59 AM Full DB Backup
> 03:00 AM ~ 3:59 AM DB Integrity Check
> 04:00 AM ~ 5:59 AM Defrag Index
> 04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
> Can the trx log backup schedule to start from today 4:00 AM until next
> day 1:59 AM ? or i need to create two scheduler job to backup the trx
> log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
> 1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
> 11:59:59PM ?
> I schedule the log backup to start from 4:00 AM is because i need the
> trx log backup to truncate the log file during defrag index (04:00 AM ~
> 5:59 AM).
> I need to backup the trx log during the DTS/Archive Job (12:00 AM to
> 1:59 AM) before start the full db backup.
> If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
> what will happen at 2:00AM when the full db backup start ? will it run
> the trx log backup first or the full db backup first ? will the
> scheduler queue the request or it just simply ignore one of them ?
> If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
> and integrity check), what will happen if the db crash after the the
> full db backup completed (after 2:59 AM), i only able to recover the db
> up to 2:00AM ?
>
> Please help.
> Thanks
> JCVoon
>
|||Tibor Karaszi:
Oops...sorry, forgot to mention. It is SQL Server 2000 ent
Regards
JCVoon
|||Prior to 2005, a database backup will block a log backup. So you can have your log backups scheduled
if you wish, the log backup job will just sit a wait until the database has been performed. Also,
Agent will not start a job if it is already running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143188648.197598.226630@.t31g2000cwb.googlegr oups.com...
> Tibor Karaszi:
> Oops...sorry, forgot to mention. It is SQL Server 2000 ent
> Regards
> JCVoon
>
|||Tibor Karaszi
Thanks.
>Prior to 2005, a database backup will block a log backup.
I'm using SQL 2000, block the log backup means the log backup will be
abort and generate error ? will the log backup block the full database
backup ?
Regards
JCVoon
|||> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ?
No, it will wait until the database backup has finished.
> will the log backup block the full database
> backup ?
Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143253350.681426.250150@.t31g2000cwb.googlegr oups.com...
> Tibor Karaszi
> Thanks.
> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ? will the log backup block the full database
> backup ?
> Regards
> JCVoon
>
|||Thanks
JCVoon
Friday, March 9, 2012
Need advice on db maintenance plan
Hi:
I need advice on creating the maintenance plan.
12:00 AM ~ 1:59 AM DTS / Archive Job
02:00 AM ~ 2:59 AM Full DB Backup
03:00 AM ~ 3:59 AM DB Integrity Check
04:00 AM ~ 5:59 AM Defrag Index
04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
Can the trx log backup schedule to start from today 4:00 AM until next
day 1:59 AM ? or i need to create two scheduler job to backup the trx
log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
11:59:59PM ?
I schedule the log backup to start from 4:00 AM is because i need the
trx log backup to truncate the log file during defrag index (04:00 AM ~
5:59 AM).
I need to backup the trx log during the DTS/Archive Job (12:00 AM to
1:59 AM) before start the full db backup.
If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
what will happen at 2:00AM when the full db backup start ? will it run
the trx log backup first or the full db backup first ? will the
scheduler queue the request or it just simply ignore one of them ?
If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
and integrity check), what will happen if the db crash after the the
full db backup completed (after 2:59 AM), i only able to recover the db
up to 2:00AM ?
Please help.
Thanks
JCVoonWhat version of SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143183567.147117.216070@.e56g2000cwe.googlegroups.com...
> Hi:
> I need advice on creating the maintenance plan.
> 12:00 AM ~ 1:59 AM DTS / Archive Job
> 02:00 AM ~ 2:59 AM Full DB Backup
> 03:00 AM ~ 3:59 AM DB Integrity Check
> 04:00 AM ~ 5:59 AM Defrag Index
> 04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
> Can the trx log backup schedule to start from today 4:00 AM until next
> day 1:59 AM ? or i need to create two scheduler job to backup the trx
> log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
> 1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
> 11:59:59PM ?
> I schedule the log backup to start from 4:00 AM is because i need the
> trx log backup to truncate the log file during defrag index (04:00 AM ~
> 5:59 AM).
> I need to backup the trx log during the DTS/Archive Job (12:00 AM to
> 1:59 AM) before start the full db backup.
> If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
> what will happen at 2:00AM when the full db backup start ? will it run
> the trx log backup first or the full db backup first ? will the
> scheduler queue the request or it just simply ignore one of them ?
> If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
> and integrity check), what will happen if the db crash after the the
> full db backup completed (after 2:59 AM), i only able to recover the db
> up to 2:00AM ?
>
> Please help.
> Thanks
> JCVoon
>|||Tibor Karaszi:
Oops...sorry, forgot to mention. It is SQL Server 2000 ent
Regards
JCVoon|||Prior to 2005, a database backup will block a log backup. So you can have yo
ur log backups scheduled
if you wish, the log backup job will just sit a wait until the database has
been performed. Also,
Agent will not start a job if it is already running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143188648.197598.226630@.t31g2000cwb.googlegroups.com...
> Tibor Karaszi:
> Oops...sorry, forgot to mention. It is SQL Server 2000 ent
> Regards
> JCVoon
>|||Tibor Karaszi
Thanks.
>Prior to 2005, a database backup will block a log backup.
I'm using SQL 2000, block the log backup means the log backup will be
abort and generate error ? will the log backup block the full database
backup ?
Regards
JCVoon|||> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ?
No, it will wait until the database backup has finished.
> will the log backup block the full database
> backup ?
Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143253350.681426.250150@.t31g2000cwb.googlegroups.com...
> Tibor Karaszi
> Thanks.
>
> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ? will the log backup block the full database
> backup ?
> Regards
> JCVoon
>|||Thanks
JCVoon
I need advice on creating the maintenance plan.
12:00 AM ~ 1:59 AM DTS / Archive Job
02:00 AM ~ 2:59 AM Full DB Backup
03:00 AM ~ 3:59 AM DB Integrity Check
04:00 AM ~ 5:59 AM Defrag Index
04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
Can the trx log backup schedule to start from today 4:00 AM until next
day 1:59 AM ? or i need to create two scheduler job to backup the trx
log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
11:59:59PM ?
I schedule the log backup to start from 4:00 AM is because i need the
trx log backup to truncate the log file during defrag index (04:00 AM ~
5:59 AM).
I need to backup the trx log during the DTS/Archive Job (12:00 AM to
1:59 AM) before start the full db backup.
If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
what will happen at 2:00AM when the full db backup start ? will it run
the trx log backup first or the full db backup first ? will the
scheduler queue the request or it just simply ignore one of them ?
If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
and integrity check), what will happen if the db crash after the the
full db backup completed (after 2:59 AM), i only able to recover the db
up to 2:00AM ?
Please help.
Thanks
JCVoonWhat version of SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143183567.147117.216070@.e56g2000cwe.googlegroups.com...
> Hi:
> I need advice on creating the maintenance plan.
> 12:00 AM ~ 1:59 AM DTS / Archive Job
> 02:00 AM ~ 2:59 AM Full DB Backup
> 03:00 AM ~ 3:59 AM DB Integrity Check
> 04:00 AM ~ 5:59 AM Defrag Index
> 04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
> Can the trx log backup schedule to start from today 4:00 AM until next
> day 1:59 AM ? or i need to create two scheduler job to backup the trx
> log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
> 1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
> 11:59:59PM ?
> I schedule the log backup to start from 4:00 AM is because i need the
> trx log backup to truncate the log file during defrag index (04:00 AM ~
> 5:59 AM).
> I need to backup the trx log during the DTS/Archive Job (12:00 AM to
> 1:59 AM) before start the full db backup.
> If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
> what will happen at 2:00AM when the full db backup start ? will it run
> the trx log backup first or the full db backup first ? will the
> scheduler queue the request or it just simply ignore one of them ?
> If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
> and integrity check), what will happen if the db crash after the the
> full db backup completed (after 2:59 AM), i only able to recover the db
> up to 2:00AM ?
>
> Please help.
> Thanks
> JCVoon
>|||Tibor Karaszi:
Oops...sorry, forgot to mention. It is SQL Server 2000 ent
Regards
JCVoon|||Prior to 2005, a database backup will block a log backup. So you can have yo
ur log backups scheduled
if you wish, the log backup job will just sit a wait until the database has
been performed. Also,
Agent will not start a job if it is already running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143188648.197598.226630@.t31g2000cwb.googlegroups.com...
> Tibor Karaszi:
> Oops...sorry, forgot to mention. It is SQL Server 2000 ent
> Regards
> JCVoon
>|||Tibor Karaszi
Thanks.
>Prior to 2005, a database backup will block a log backup.
I'm using SQL 2000, block the log backup means the log backup will be
abort and generate error ? will the log backup block the full database
backup ?
Regards
JCVoon|||> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ?
No, it will wait until the database backup has finished.
> will the log backup block the full database
> backup ?
Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143253350.681426.250150@.t31g2000cwb.googlegroups.com...
> Tibor Karaszi
> Thanks.
>
> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ? will the log backup block the full database
> backup ?
> Regards
> JCVoon
>|||Thanks
JCVoon
Need advice on db maintenance plan
Hi:
I need advice on creating the maintenance plan.
12:00 AM ~ 1:59 AM DTS / Archive Job
02:00 AM ~ 2:59 AM Full DB Backup
03:00 AM ~ 3:59 AM DB Integrity Check
04:00 AM ~ 5:59 AM Defrag Index
04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
Can the trx log backup schedule to start from today 4:00 AM until next
day 1:59 AM ? or i need to create two scheduler job to backup the trx
log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
11:59:59PM ?
I schedule the log backup to start from 4:00 AM is because i need the
trx log backup to truncate the log file during defrag index (04:00 AM ~
5:59 AM).
I need to backup the trx log during the DTS/Archive Job (12:00 AM to
1:59 AM) before start the full db backup.
If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
what will happen at 2:00AM when the full db backup start ? will it run
the trx log backup first or the full db backup first ? will the
scheduler queue the request or it just simply ignore one of them ?
If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
and integrity check), what will happen if the db crash after the the
full db backup completed (after 2:59 AM), i only able to recover the db
up to 2:00AM ?
Please help.
Thanks
JCVoonWhat version of SQL Server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143183567.147117.216070@.e56g2000cwe.googlegroups.com...
> Hi:
> I need advice on creating the maintenance plan.
> 12:00 AM ~ 1:59 AM DTS / Archive Job
> 02:00 AM ~ 2:59 AM Full DB Backup
> 03:00 AM ~ 3:59 AM DB Integrity Check
> 04:00 AM ~ 5:59 AM Defrag Index
> 04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
> Can the trx log backup schedule to start from today 4:00 AM until next
> day 1:59 AM ? or i need to create two scheduler job to backup the trx
> log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
> 1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
> 11:59:59PM ?
> I schedule the log backup to start from 4:00 AM is because i need the
> trx log backup to truncate the log file during defrag index (04:00 AM ~
> 5:59 AM).
> I need to backup the trx log during the DTS/Archive Job (12:00 AM to
> 1:59 AM) before start the full db backup.
> If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
> what will happen at 2:00AM when the full db backup start ? will it run
> the trx log backup first or the full db backup first ? will the
> scheduler queue the request or it just simply ignore one of them ?
> If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
> and integrity check), what will happen if the db crash after the the
> full db backup completed (after 2:59 AM), i only able to recover the db
> up to 2:00AM ?
>
> Please help.
> Thanks
> JCVoon
>|||Tibor Karaszi:
Oops...sorry, forgot to mention. It is SQL Server 2000 ent
Regards
JCVoon|||Prior to 2005, a database backup will block a log backup. So you can have your log backups scheduled
if you wish, the log backup job will just sit a wait until the database has been performed. Also,
Agent will not start a job if it is already running.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143188648.197598.226630@.t31g2000cwb.googlegroups.com...
> Tibor Karaszi:
> Oops...sorry, forgot to mention. It is SQL Server 2000 ent
> Regards
> JCVoon
>|||Tibor Karaszi
Thanks.
>Prior to 2005, a database backup will block a log backup.
I'm using SQL 2000, block the log backup means the log backup will be
abort and generate error ? will the log backup block the full database
backup ?
Regards
JCVoon|||> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ?
No, it will wait until the database backup has finished.
> will the log backup block the full database
> backup ?
Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143253350.681426.250150@.t31g2000cwb.googlegroups.com...
> Tibor Karaszi
> Thanks.
>>Prior to 2005, a database backup will block a log backup.
> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ? will the log backup block the full database
> backup ?
> Regards
> JCVoon
>|||Thanks
JCVoon
I need advice on creating the maintenance plan.
12:00 AM ~ 1:59 AM DTS / Archive Job
02:00 AM ~ 2:59 AM Full DB Backup
03:00 AM ~ 3:59 AM DB Integrity Check
04:00 AM ~ 5:59 AM Defrag Index
04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
Can the trx log backup schedule to start from today 4:00 AM until next
day 1:59 AM ? or i need to create two scheduler job to backup the trx
log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
11:59:59PM ?
I schedule the log backup to start from 4:00 AM is because i need the
trx log backup to truncate the log file during defrag index (04:00 AM ~
5:59 AM).
I need to backup the trx log during the DTS/Archive Job (12:00 AM to
1:59 AM) before start the full db backup.
If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
what will happen at 2:00AM when the full db backup start ? will it run
the trx log backup first or the full db backup first ? will the
scheduler queue the request or it just simply ignore one of them ?
If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
and integrity check), what will happen if the db crash after the the
full db backup completed (after 2:59 AM), i only able to recover the db
up to 2:00AM ?
Please help.
Thanks
JCVoonWhat version of SQL Server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143183567.147117.216070@.e56g2000cwe.googlegroups.com...
> Hi:
> I need advice on creating the maintenance plan.
> 12:00 AM ~ 1:59 AM DTS / Archive Job
> 02:00 AM ~ 2:59 AM Full DB Backup
> 03:00 AM ~ 3:59 AM DB Integrity Check
> 04:00 AM ~ 5:59 AM Defrag Index
> 04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
> Can the trx log backup schedule to start from today 4:00 AM until next
> day 1:59 AM ? or i need to create two scheduler job to backup the trx
> log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
> 1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
> 11:59:59PM ?
> I schedule the log backup to start from 4:00 AM is because i need the
> trx log backup to truncate the log file during defrag index (04:00 AM ~
> 5:59 AM).
> I need to backup the trx log during the DTS/Archive Job (12:00 AM to
> 1:59 AM) before start the full db backup.
> If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
> what will happen at 2:00AM when the full db backup start ? will it run
> the trx log backup first or the full db backup first ? will the
> scheduler queue the request or it just simply ignore one of them ?
> If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
> and integrity check), what will happen if the db crash after the the
> full db backup completed (after 2:59 AM), i only able to recover the db
> up to 2:00AM ?
>
> Please help.
> Thanks
> JCVoon
>|||Tibor Karaszi:
Oops...sorry, forgot to mention. It is SQL Server 2000 ent
Regards
JCVoon|||Prior to 2005, a database backup will block a log backup. So you can have your log backups scheduled
if you wish, the log backup job will just sit a wait until the database has been performed. Also,
Agent will not start a job if it is already running.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143188648.197598.226630@.t31g2000cwb.googlegroups.com...
> Tibor Karaszi:
> Oops...sorry, forgot to mention. It is SQL Server 2000 ent
> Regards
> JCVoon
>|||Tibor Karaszi
Thanks.
>Prior to 2005, a database backup will block a log backup.
I'm using SQL 2000, block the log backup means the log backup will be
abort and generate error ? will the log backup block the full database
backup ?
Regards
JCVoon|||> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ?
No, it will wait until the database backup has finished.
> will the log backup block the full database
> backup ?
Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143253350.681426.250150@.t31g2000cwb.googlegroups.com...
> Tibor Karaszi
> Thanks.
>>Prior to 2005, a database backup will block a log backup.
> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ? will the log backup block the full database
> backup ?
> Regards
> JCVoon
>|||Thanks
JCVoon
Need advice on buiding a dynamic TSQL command to run against all DB's
Hi All,
I am putting together a series of TSQL statements to be used as part of a DB maintenance plan on SQL server 2005 on Windows Server 2003. I know that the statement itself works against a single database. The intention is to write one routine that will execute on all non-system DB's. Here are the system specs:
__
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324
1447)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
__
So I've actually tried two methods. The first is without using sp_MSforeachdb
__
-- Declare variables
Declare @.dbname sysname,
@.cmd varchar(4096)
-- Select DB Names
Select @.dbname = min(name) from master.dbo.sysdatabases
-- Loop through each database.
WHILE @.dbname is not null -- <-- (NEED TO NOT INCLUDE SYSTEM DB'S HERE)
Begin
Select @.cmd = 'use ' + @.dbname +
'
-- Delete data from table1 and related tables older than 2 Months
DELETE from table1 WHERE DATEDIFF(month, datecreated , GETDATE()) > 2
DELETE from table1a WHERE messageid NOT IN (SELECT messageid from table1)
DELETE from table1b WHERE messageid NOT IN (SELECT messageid from table1)
DELETE from table1c WHERE messageid NOT IN (SELECT messageid from table1)
-- Delete Selective Data from table2 older than 6 Months
DELETE from table2 WHERE DATEDIFF(month, AuditDate, GETDATE()) > 6 and tablename in
('TableX','TableY','TableZ')
DELETE from table2a WHERE auditid NOT IN (SELECT auditid from table2)
--
-- Delete Data From table3 older than 2 Years
DELETE from table3 WHERE DATEDIFF(Year, ReferralDate, GETDATE()) > 2
-- Shrink all Non System DB's
DBCC SHRINKDATABASE(' + @.dbname + ', 10, TRUNCATEONLY)
'
End -- (NOT SURE IF THIS IS IN THE RIGHT PLACE)
-- Execute the command
Exec (@.cmd)
__
The second attempted method was trying to use sp_MSforeachdb. I modified the following to successfully loop a simple print command to ensure that the looping occurs:
__
declare @.cmd1 varchar(4096)
set @.cmd1 =
'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''
exec sp_MSforeachdb @.command1=@.cmd1
-- THIS WORKS
__
However after many attempts, I haven't been able to get the syntax right to run my
statements abonve using this method. Seems very unforgiving with spaces and apostrophes.
Any advice is much appreciated. Thank you in advance.
Rick B
Hi Rick,
Try something like this:
--THIS CREATES ONE TEMP TABLE WITH ALL NON SYSTEM DATABASES
SELECT IDENTITY(INT, 1,1) AS ID, DBNAME
INTO TEMP_CURSOR
FROM from master.dbo.sysdatabases
where SID<>0x01;
DECLARE @.ID INT, @.DBNAME VARCHAR(250)
SELECT @.ID = MIN(ID)
FROM TEMP_CURSOR
WHERE ID > 0;
WHILE @.ID IS NOT NULL
BEGIN
SELECT @.DBNAME=DBNAME
FROM TEMP_CURSOR
WHERE ID = @.ID;
EXEC(' USE ' + @.DBNAME + '
-- Delete data from table1 and related tables older than 2 Months
DELETE from table1 WHERE DATEDIFF(month, datecreated , GETDATE()) > 2;
DELETE from table1a WHERE messageid NOT IN (SELECT messageid from table1);
DELETE from table1b WHERE messageid NOT IN (SELECT messageid from table1);
DELETE from table1c WHERE messageid NOT IN (SELECT messageid from table1);
-- Delete Selective Data from table2 older than 6 Months
DELETE from table2 WHERE DATEDIFF(month, AuditDate, GETDATE()) > 6 and tablename in ('TableX','TableY','TableZ')
DELETE from table2a WHERE auditid NOT IN (SELECT auditid from table2)
--
-- Delete Data From table3 older than 2 Years
DELETE from table3 WHERE DATEDIFF(Year, ReferralDate, GETDATE()) > 2
-- Shrink all Non System DB's
DBCC SHRINKDATABASE(' + @.dbname + ', 10, TRUNCATEONLY)
')
SELECT @.ID = MIN(ID)
FROM TEMP_CURSOR
WHERE ID > @.ID;
END
DROP TABLE TEMP_CURSOR;
I thinks this can do your trick
Regards,
|||One of the ways I use to test the syntax of dynamic sql is to print the SQL rather than run it. Then you can see the outputted SQL and see if its correct and if not, where you have gone wrong
hope this helps
|||Hey Luis
Upon execution against Master, I'm receiving the following error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'DBNAME'.
After a few tweaks, the following is the current syntax that I am using based off of Luis's feedback. Can anyone ID additional syntax errors? Thanks in advance. Rick B.
__
SELECT IDENTITY(INT, 1,1) AS ID, DBNAME
INTO TEMP_CURSOR
FROM master.dbo.sysdatabases
where SID<>0x01;
DECLARE @.ID INT, @.DBNAME VARCHAR(250)
SELECT @.ID = MIN(ID)
FROM TEMP_CURSOR
WHERE ID > 0;
WHILE @.ID IS NOT NULL
BEGIN
SELECT @.DBNAME=DBNAME
FROM TEMP_CURSOR
WHERE ID = @.ID;
EXEC(' USE ' + @.DBNAME + '
-- Delete data from table1 and related tables older than 2 Months
DELETE from table1 WHERE DATEDIFF(month, datecreated , GETDATE()) > 2;
DELETE from table1a WHERE messageid NOT IN (SELECT messageid from table1);
DELETE from table1b WHERE messageid NOT IN (SELECT messageid from table1);
DELETE from table1c WHERE messageid NOT IN (SELECT messageid from table1);
-- Delete Selective Data from table2 older than 6 Months
DELETE from table2 WHERE DATEDIFF(month, AuditDate, GETDATE()) > 6 and tablename in (TableX,TableY,TableZ)
DELETE from table2a WHERE auditid NOT IN (SELECT auditid from table2)
--
-- Delete Data From table3 older than 2 Years
DELETE from table3 WHERE DATEDIFF(Year, ReferralDate, GETDATE()) > 2
-- Shrink all Non System DBs
DBCC SHRINKDATABASE(' + @.dbname + ', 10, TRUNCATEONLY)
')
SELECT @.ID = MIN(ID)
FROM TEMP_CURSOR
WHERE ID > @.ID;
END
DROP TABLE TEMP_CURSOR;
__
|||Replace this statement:
SELECT IDENTITY(INT, 1,1) AS ID, DBNAME
INTO TEMP_CURSOR
FROM master.dbo.sysdatabases
where SID<>0x01;
with this one
SELECT IDENTITY(INT, 1,1) AS ID, NAME AS DBNAME
INTO TEMP_CURSOR
FROM master.dbo.sysdatabases
where SID<>0x01;
Hope it works
Subscribe to:
Posts (Atom)