SQL Server Performance Forum – Threads Archive
Database offline
What 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, CanadaDBAFrom 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 OFFLINE
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 "…
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ec132fef-5e1a-4b33-8ef1-b9b64ef5444f.htm
MohammedU.
Moderator
SQL-Server-Performance.com
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?
CanadaDBA
From BOL..
OFFLINE
Sets the file offline and makes all objects in the filegroup inaccessible.
Caution:
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….
use <DBNAME>
GO
checkpoint
GO
USE master;
ALTER DATABASE <DBNAME> SET SINGLE_USER;
GO
EXEC SP_DETACH_DB <DBNAME>
go –copy the files (mdf and ldf) to new location
exec sp_attach_db…..
MohammedU.
Moderator
SQL-Server-Performance.com
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.
Moderator
SQL-Server-Performance.com
]]>