October 19, 2011 at 8:52 am
HI,
We have a database that the status is set to Shutdown. How can I change the DB Status to Normal.
Thanks,
October 19, 2011 at 8:59 am
Where are you checking the status of the database. As per BOL, a database can have one of the following states.
online, offline, restoring, recovering, recovery pending, suspect and emergency.
what is the result of the below query.
select state_desc from sys.databases where name='<your database name>'
October 19, 2011 at 11:15 am
Hi,
After running the query you provided, the output is: RECOVERY_PENDING.
I'm seeing the Shutdown status at the General page of the DB Property section. In the Database subsection, the Status of the DB is Shutdown. In looking at other DBs, they have Normal Status.
October 19, 2011 at 11:18 am
Please open the SQL error log and find all errors relating to that database.
Got a backup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2011 at 12:27 pm
No, unfortunately, I don't have a backup file. Below is an error message that I can see in the log.
2011-10-10 15:00:50.03 spid27s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\log\dbFile_log.LDF'. Diagnose and correct the operating system error, and retry the operation.
2011-10-10 15:00:50.03 spid27s File activation failure. The physical file name "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\log\dbFile_log.LDF" may be incorrect.
October 19, 2011 at 12:33 pm
Another production database without a backup? Why?
Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\log\dbFile_log.LDF'
Is the file there?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2011 at 12:43 pm
EjSQLme (10/19/2011)
No, unfortunately, I don't have a backup file.
(facepalm) If this is an important database I hope you can fix it or your resume is up to date. I've seen businesses go under because of things like this and even if they don't the DBA is usually toast.. Sorry man..
And the ULTRA important question already asked, why no backup?
CEWII
October 19, 2011 at 1:01 pm
Elliott Whitlow (10/19/2011)
If this is an important database I hope you can fix it or your resume is up to date. I've seen businesses go under because of things like this and even if they don't the DBA is usually toast..
So far this month I'm 0-for-2 on corrupt databases without backups. Both the others were complete and total losses, no repair, no data could be extracted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2011 at 1:15 pm
Beyond the monitoring software I use, I have been configuring policies on all my SQL 2008 servers that check to be sure that all but tempdb have had a full backup within the last 25 hours. I haven't lost a database in a long time and even longer without a backup, I'd like to keep it that way.
I've seen a DBA get fired over stuff like this, the last case the DBA did have a backup, from a month ago which in most cases is as bad as not having one at all.. He got lax and even though the monitoring software was sending him "love notes" he didn't resolve the issue. I will give him some cover, he was getting a fair number of alerts but after so long you'd think he would have seen a no backups message..
Even with monitoring in place I will periodically go out to each server and run a script I have that tells me when every single database got its last full backup.
As a DBA a little paranoia goes a long way and some of my "paranoid code" has saved my bacon (and others) more than twice..
CEWII
October 19, 2011 at 1:45 pm
yes, the file is there. This is a Dev DB, we have been doing some work on it that would take us a while to recreate. So what is my next step?
October 19, 2011 at 1:57 pm
I'd probably go with a detach/re-attach, then you could specify all the file locations. Gail? Your thoughts?
CEWII
October 19, 2011 at 2:15 pm
No. Never ever detach a suspect or recovery_pending database as it will not always reattach (suspect will never reattach)
Are you absolutely sure that file is there? Exact path, exact name.
Was there a drive problem that the drive came online later than SQL? If so, try restarting SQL Server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2011 at 2:50 pm
Is the file there in the location showing in the error logs.
Try to detach and reattach providing the new path.
October 19, 2011 at 3:09 pm
R_S (10/19/2011)
Is the file there in the location showing in the error logs.Try to detach and reattach providing the new path.
Read Gail's post just above.
October 21, 2011 at 8:16 am
Yes, I'm sure that the file is there b/c I checked it to make sure it is there when I saw the error message that I posted. I believe there was an issue with the server and did crash, but we have other DBs on the same SQL Server, and this is the only DB with this issue.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply