July 5, 2007 at 7:48 am
URL not working?http://www.sql-server-performance.com/ss_creating_backup_jobs.asp.
about recovery, I am confused with what u said, do I have to change it to simple before doing re-building indexing and full after it's completed
what practise should I follow for re-build indexing, do I need to check against the database if indexing rebuild or reorganize is required, how do I check? and than depending on the result i make decision and execute Maintenace plann
July 5, 2007 at 8:20 am
I also am confused as to why to change database mode from full to simple and back again. I keep some of my databases as simple recovery as the requirements are only for last full backup and the rest are on Full recovery.
I scheduled a job to run weekly for my sql 2005 servers to do the following routine for each database, with each database having it's own task that on fail or complete it falls over to the next task. This is because 2005 is bad at shinking the logs, and I am not willing to trust a shrinkdatabase routine on production databases.
Use <dbname>
go
backup log <dbname> with truncate_only
dbcc shrinkfile (<dbname>_log, 10)
Simple and it works very well. I run this about 15 minutes or so prior to a full backup. Rebuilding and reorganizations take time and space. Run those during a slow point in the system maybe once a week. Run the rebuild of the indexes before the reorganization of the indexes,make sure the rebuild is completed before scheduling the reorg and don't allow them to still be running when you do your truncates or backups.
July 5, 2007 at 8:45 am
Thanks for you input Lowry.
July 6, 2007 at 12:26 am
Bhavin -
Can you give us a little more information about your "critical" database?
How big is it?
What is your tolerance for data loss (e.g. 1 day, 1 hour, 15 minutes)?
Where are you currently dumping your backups to (same disk/c:?, seperate disk, tape)?
Right now, it sounds like you're doing a nightly full backup followed by a single tlog backup shortly after the full which means that your current exposure for data loss is ~24 hours...
Joe
July 6, 2007 at 7:15 am
How big is it?
5Gb
What is your tolerance for data loss (e.g. 1 day, 1 hour, 15 minutes)?
1Day
Where are you currently dumping your backups to (same disk/c:?, seperate disk, tape)?
Disk
I found one more error in my maintenance plan in sql 2005 sp2.
I use maintenance wizard for backing up transaction log and I selected list of databases (with full recovery) and saved the plan. When I open back that maintenance plan it unchecks all the databases and nothing is selected any more and not logs are backed up?
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply