Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using SQL Server 2008 R2 Resource Governor to limit CPU tap from running a FUll DB Backup

Posted on 2011-09-09
5
Medium Priority
?
961 Views
Last Modified: 2012-06-27
Hello -
We have a 1.1T DB that I need to run a backup of during the day but due to it's size, the CPU takes a hit and the end users report application performance issues.  This is unacceptable so I wish to use the Governor to limit how much the CPU is hit so I can run the backup.  

Is this possible?  I have reviewed BOL for R2 but not exactly seeing the help I need.

Suggestions please.

Thanks
0
Comment
Question by:SQLwatcher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36512135
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36512158
Here's a good read on using the governor for limiting resource consumption for backups:
http://programming4.us/database/3078.aspx
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36512169
Is this what you are looking for?

How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL)
http://msdn.microsoft.com/en-us/library/cc280384.aspx

Or maybe this can help -
How to: Configure Resource Governor Using a Template (SSMS)
http://msdn.microsoft.com/en-us/library/bb934150.aspx
0
 

Author Comment

by:SQLwatcher
ID: 36513353
Thank you for the input.  No, I am not using compression for the backups.  I have also reviewed MSDN pages offered regarding the templates etc and the article from programming4.us is similar in content to the MSDN sites.  

I have the template set up but wanted to know if any has had success with running a full DB backup using the governor to lower the performance impact on the end users.

I need to pull a file back up so the db will fit onto two secured drives as it is more that 1.1 T  I just did not want to have to stay up all night to do this task.  

Ideas welcome.

0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 2000 total points
ID: 36513452
Unfortunately, I don't have hands-on experience w/the governor on an actual backup.  Take a look at the whitepaper referenced at the bottom of this, the Appendix B - Resource Governor Overhead.

http://www.dbta.com/Articles/Columns/SQL-Server-Drill-Down/DBAs---Take-Control-with-Resource-Governor--74545.aspx
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question