August 18, 2009 at 10:44 am
Hi Folks
has anyone see this issue where you taka a database offline and SSMS shows it as offline but when you look at the status in master.sys.master_files it still shows it as being online no matter how long you wait ?
Thanks
Jim
--check database status
use master;
go
select
CAST(name as varchar(30)) logical_name,
database_id,
CAST (state_desc as varchar(10)) status
from
master.sys.master_files
where
name LIKE 'Adventure%'
order by
name;
go
=== output ===
logical_name database_id status
------------------------------ ----------- -------
AdventureWorks_Data 9 ONLINE
AdventureWorks_Log 9 ONLINE
AdventureWorksDW_Data 8 ONLINE
AdventureWorksDW_Log 8 ONLINE
AdventureWorksLT_Data 10 ONLINE
AdventureWorksLT_Log 10 ONLINE
(6 rows affected)
--set databases off-line
ALTER DATABASE AdventureWorks SET OFFLINE;
GO
ALTER DATABASE AdventureWorksDW SET OFFLINE;
GO
ALTER DATABASE AdventureWorksLT SET OFFLINE;
GO
--check databases status again
select
CAST(name as varchar(30)) logical_name,
database_id,
CAST (state_desc as varchar(10)) status
from
master.sys.master_files
where
name LIKE 'Adventure%'
order by
name;
go
=== output ===
logical_name database_id status
------------------------------ ----------- -------
AdventureWorks_Data 9 ONLINE
AdventureWorks_Log 9 ONLINE
AdventureWorksDW_Data 8 ONLINE
AdventureWorksDW_Log 8 ONLINE
AdventureWorksLT_Data 10 ONLINE
AdventureWorksLT_Log 10 ONLINE
(6 rows affected)
August 18, 2009 at 11:28 am
That's the file status, not the database status. Database status is in sys.databases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 18, 2009 at 11:36 am
Thanks GSquared
appreaciate the assist
Jim
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply