March 31, 2012 at 12:03 pm
Hi Jeff,
Thanks for your reply.
Yes. I am learning a lot after this issue. Infact my db was having FULL recovery model. I advised my boss to go for DB Full backup and every 1 hours transaction log backup. Due to space issue, he was keen to go with weekly full backup and 1 incremental backup on daily basis at 5 am. On the other hand, he is not interested to provide space for Transaction log. Since it is our Monitoring tools, it keeps inserting, updating and deleting data into our database after collecting data from various probes. It is having 300 transactions/sec, 90 write transactions/sec and 250 bulk transactions/sec. You can imagine the amount of intensive queries/pressure on the sql server. The db is growing around 15 to 20 gb in a day.
After explaining the recovery models in sql server to bosses, they agreed with one day data loss since it is our monitoring tool for our overall organisations. i have changed the db recovery model from FULL to SIMPLE.
Eventually it is scheduled with daily FULL backup. Our db size is currently 575 gb. Also need to understand one more thing. While it is backing up, it is using around 150 gb of transaction log. Can you please explain the reason?
Thanks for your help in advance.
Regards
Govind
March 31, 2012 at 2:49 pm
govindarajan69 (3/31/2012)
Eventually it is scheduled with daily FULL backup. Our db size is currently 575 gb. Also need to understand one more thing. While it is backing up, it is using around 150 gb of transaction log. Can you please explain the reason?
Sure, it's explained in here, mainly under the section 'full backups': http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2012 at 4:56 am
Thanks Gail for this article.
Since this db size is around 575 gb now, i need to know how long the Full backup should take in general? Currently it is taking around 6.0 hours. The db recovery model is SIMPLE. it is writing to tape directly using Legato Agent. Observed that the backup is utilising around 175 gb every day.
Your suggestion please.
Regards
Govind
April 2, 2012 at 5:05 am
No way to tell. It's a function of IO throughput, CPU, data size.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply