Solved

The best way to disbale the database

Posted on 2011-03-17
9
172 Views
Last Modified: 2012-05-11
What's the best way to disable the database so that it is still available to manually run querries on but so that if it is not used it does not consume server resources?
0
Comment
Question by:YZlat
  • 4
  • 3
  • 2
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
you can change the database to be READ_ONLY:
http://msdn.microsoft.com/en-us/library/aa275464%28v=sql.80%29.aspx
ALTER DATABASE your_db SET READ_ONLY

Open in new window


however, it will nevertheless need resources to run those queries, and the data queries will stay for some time in the cache.
0
 
LVL 35

Author Comment

by:YZlat
Comment Utility
any other ways? I need to make sure that no scheduled tasks for that database run. I disabled them but it still shows that the database takes up resources
0
 
LVL 14

Expert Comment

by:Daniel_PL
Comment Utility
ALTER DATABASE <dbname> SET OFFLINE
0
 
LVL 14

Expert Comment

by:Daniel_PL
Comment Utility
With forcing rollback:
ALTER DATABASE <dbname> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
you can indeed put the database offline, but then you cannot query "manually" unless you put it back online.

> but it still shows that the database takes up resources
how do you see that?

indeed, scheduled tasks are not "aware" of the source database being "offline", so they will still run (unless disabled), and run into error.

0
 
LVL 35

Author Comment

by:YZlat
Comment Utility
@angelIII what I meant by "scheduled tasks" is scheduled jobs

@Daniel_PL what exactly will set offline do for the database?
0
 
LVL 14

Expert Comment

by:Daniel_PL
Comment Utility
Database in oofline state is not available to any query against it - except of system views.
What do you mean by consume server resources?
0
 
LVL 35

Author Comment

by:YZlat
Comment Utility
meaning some programs (old ones that people didn't uninstall for some reason) are still running queries against that database. I would like to disable that but allow myself to run queriees occasionally using Management studio
0
 
LVL 14

Assisted Solution

by:Daniel_PL
Daniel_PL earned 250 total points
Comment Utility
Ok, I see, you have these options:

ALTER DATABASE your_db SET READ_ONLY  --database can be queried by all users, no changes are available

ALTER DATABASE <dbname> SET RESTRICTED_USER --database is available only to sysadmin or dbcreator server roles, or users with the db_owner database role in that db

ALTER DATABASE <dbname> SET SINGLE_USER --only one single connection can be established
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now