The best way to disbale the database

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?
LVL 35
YZlatAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
YZlatAuthor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Daniel_PLDB Expert/ArchitectCommented:
ALTER DATABASE <dbname> SET OFFLINE
0
 
Daniel_PLDB Expert/ArchitectCommented:
With forcing rollback:
ALTER DATABASE <dbname> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE
0
 
YZlatAuthor Commented:
@angelIII what I meant by "scheduled tasks" is scheduled jobs

@Daniel_PL what exactly will set offline do for the database?
0
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
YZlatAuthor Commented:
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
 
Daniel_PLConnect With a Mentor DB Expert/ArchitectCommented:
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
All Courses

From novice to tech pro — start learning today.