May 29, 2009 at 8:19 pm
Hi Everybody
Acutually we have implemented Transactional log backup every four hours a day,differential backup every eight hours a day and a full backup once per day plan in the server for the particular database.
After implementing this my database size is goes arround 60gb from 30gb within one day. After that I have shrinked the database.Then i got the old database size 30gb.now the database size is 30gb and space available is 1gb.
Is it advisable to have above plan?
The tool always showing full database capacity.
How come i can avoid full database capacity (i.e) redmark in database capacity monitoring tool as i have attached in the database capacity.xls file.
Also suggest how can i avoid the dataloss from database.
please share your ideas.
Thanks in Advance
Vijay.s
June 1, 2009 at 4:42 am
Without knowing what your system does and the amount of transaction your process it is hard to advise.
But I will try with what I have implemented.
Every evening (11pm) I run a full backup, and then I do a log backup every 15 mins (from 0300 to 2245). Doing the full backup and the subsequent log covers for data loss up to 15 mins.
Depending what time you run your logs and diff's, I would remove the diff and increase the log backup frequency.
I personally don’t shrink data/log files, UNLESS we have to perform a large data import. We increase the log size manually and once the import and Trans backup is complete I shrink the log at an appropriate time. Obviously if your capacity is low then "needs must".
When you say "After implementing this my database size is goes arround 60gb from 30gb within one day", do you mean the total size of the MDF and LDF, or just the MDF?
Rgds
JL
June 1, 2009 at 2:00 pm
sql_lock (6/1/2009)
Every evening (11pm) I run a full backup, and then I do a log backup every 15 mins (from 0300 to 2245). Doing the full backup and the subsequent log covers for data loss up to 15 mins.
sql_lock - I would recommend rethinking the above timings for your transaction log backups. You have opened yourself up to a possible loss of more than 4 hours in the event of a disaster.
Just think what would happen if your full backup was running and has not completed when you run into a problem. All data from the last transaction log backup, up to the point in time of the disaster would be lost.
That might not be a problem if you know for sure that there is absolutely no activity during that time frame, but I would not trust that myself.
There is no reason not to continue running transaction log backups 24/7 since they no longer block in SQL Server 2005. In other words, you can run a full backup and a transaction log backup at the same time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 2, 2009 at 2:12 am
Jeff
Thanks for the advice. These 4 hours are our maint window where there is no activity other than the backup and index routine.
Before I started here (2 weeks ago) the backups and index routine was taking 2-3 hours. Since I have started here I have optimized this and from your info I have check the job(s) duration and I can now move the log backup to 0100.
I have notice with SQL 2005 that if you are backing up a large amount of transaction (no trans backups for 2 days) then the agent job running the backup does cause locking.
Thanks Jeff
JL
June 2, 2009 at 9:24 am
sql_lock (6/2/2009)
I have notice with SQL 2005 that if you are backing up a large amount of transaction (no trans backups for 2 days) then the agent job running the backup does cause locking.
Are you talking about locking - or blocking. In SQL Server 2000 you could not run full backup and a log backup at the same time. One would be blocked until the other completed.
Locking is going to happen no matter what - since SQL Server needs the locks on the data to rebuild the indexes. If you are on Enterprise Edition, you could perform your index rebuilds online which will reduce the locking, and keep the index available. In your case, not sure it would make a difference because you can guarantee no activity on the system.
I would still recommend running your transaction log backups 24/7 just to be safe. This gives you the ability to restore the system to any point in time - even to a time between the start and finish of your full backup.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply