SQL Server Performance Forum – Threads Archive
Database offlineWhat exactly is database offline? I tried to google it but didn’t get what I was looking for. One of my environments has few users. They attach databases and run an application to produce the results. Sometimes they forget to detach databases after their use and these databases remain in SQL Server. Sometimes they leave the database to use it in future. At the moment there are about 900 databases attached. I believe these attached databases are using the servers resources and therefore, decrease performance. Is this correct? I was thinking to force the users to detach databases that they do not need and attach when they need. Then thought why shouldn’t just set the database to offline and take it back online when they need. Does this increase performance? Does this save server’s resources? Thanks, CanadaDBA
From BOL… SQL Server 2005 Books Online Database States
Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed. I don’t think it uses any resources when no one is using them… Check BOL topic "Memory Used by SQL Server Objects Specifications "…
I took one of the databases off-line and then moved the mdf and ldf files to another folder. Trying to bring the database on-line failed. It is obvious because the files were not where they must be. The question is why BOL says "the database may be taken offline in order to move a file to a new disk"? How can I use this feature to move a database? What should I do in order to move a database without detaching the database?
Sets the file offline and makes all objects in the filegroup inaccessible.
Use this option only when the file is corrupted and can be restored. A file set to OFFLINE can only be set online by restoring the file from backup. For more information about restoring a single file, see RESTORE (Transact-SQL).
Keep the files where there were and bring the database online…. run the following and copy the files (mdf and ldf) to new location and run sp_attach_db command….
ALTER DATABASE <DBNAME> SET SINGLE_USER;
EXEC SP_DETACH_DB <DBNAME>
go –copy the files (mdf and ldf) to new location
Mohammed, still I didn’t get an answer. In your second reply from the last, you brought an explanation from BOL saying "the database may be taken offline in order to move a file to a new disk". I want to find out how I can move a database by Off-line and On-line? CanadaDBA
That is the definition of database OFFLINE…from book online…
Check the following url in BOL
SQL Server 2005 Books Online Database States
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b7f1f111-ca73-4a89-b567-a98d64d6ecb3.htm As I mentioned in my previous post use sp_detach_db and sp_attach_db procedures to move the database…or you can use backup and restore method.. Check BOL topic "Detaching and Attaching Databases" for details… . MohammedU.