November 21, 2012 at 9:56 am
I had a transaction Log become full (no disk space).
The database went into recovery.
I deleted files and no the database does not show that it is in recovery but there are no nodes to expand.
I tried to DUN DBCC CHECKDB but I get the following error:
Database 'MyDWdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 21, 2012 at 10:12 am
Currently I'm executing the step listed below to bring the Database Online after I took it offline. I'm waiting for the Database to come back online.
I don't know if it is going to work or what else that I can do short of restoring the Database.
STEP1: identify the db status:
use master
select databaseproperty(‘db_name’,'isShutdown’)
Most of them it would return 1 in this situation
STEP2: Clear up the internal db status:
use master
alter database db_name set offline
it would return with no error in most cases
STEP3: Get detail error message:
use master
alter database db_name set online
After step3, sql server will first verify the log file, if the log file is okay, it will verify the rest of the data file(s). Most of time it is because of the file location or file properties setting. For example if it is file location issue:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 21, 2012 at 11:32 am
First thing to do, before trying random stuff, would be to identify the specific cause of the DB not being able to be opened.
What does the errorlog say? (it'll say a lot more than just the error posted)
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
November 21, 2012 at 11:36 am
Welsh Corgi (11/21/2012)
I deleted files and no the database does not show that it is in recovery but there are no nodes to expand.
What files exactly did you delete?
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
November 21, 2012 at 11:54 am
I deleted some old backup files.
Ther was no space availible on the disk on which the data file resides.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 21, 2012 at 12:13 pm
And the error log....
And which drive filled? You initially said the log, now you say the data?
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
November 22, 2012 at 11:13 pm
Not much information in the Error Log that I did not already know.
It was in a recovery state and then it stopped around the time that I deleted the files and made the space available.
Several failed login attempts.
Recovery started after I took the Database offline and back online.
Recovery was successful.
That's about it.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 22, 2012 at 11:35 pm
So the DB is online and working?
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
November 23, 2012 at 12:51 am
Yes it is online and operational.
I thought that I posted that the database had recovered.
I said Data File when I meant the Log File.
I'm not sure why the database went out of recovery mode after I deleted some files to make rom for the Log File.
The Database did not go back into recovery mode until I took it offline and placed it back online.
It took 22 minutes for the Database (122 GB) to finish recovering.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy