June 23, 2010 at 8:42 am
Hi,
Iam new to SQL Server DBA and here iam trying to bring the database suspect mode and tried as below...
i have an database with 2 data files and 1 log file , the 2 data files are fulled and i created the one more data file on the external device.........
and give the size,maxsize,filegrowth and etc., and before inserting the data i removed the external device and trying to insert the data in the database.......with out any error sqlserver as ascepted the data with any error.........with out the data file how the sqlserver is accepting the data .
i have given
size :3mb
max size :6mb
filegrowth:1mb
at certain task i got an error data file is full errorNo:1105
when i queired the dbcc showfilestats()
it has taken total extext:48 and used extents:48
but i mentioned the maxsize :6mb
as my knowledge if the data file is missing the database should go to suspect mode..............
if iam wrong please suggestion me
June 23, 2010 at 8:51 am
I'm not sure that I completely understand you, given what you've written. Did you say that you added a data file to the database on an external drive and then removed that drive? I would think that the database would be set as Suspect, but if there was no data there, perhaps not. Are there any errors in the error log?
June 23, 2010 at 8:59 am
Not sure I understand, are you trying to make a database suspect or are you trying to fix a suspect database?
A missing data file won't send a DB suspect, it'll send it recovery pending possibly, depending on a couple factors
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
June 23, 2010 at 9:39 am
Iam trying to bring the database into suspect mode? but it not happne even thought the external dirver is removed from the system of which the one of the data file existed........
June 23, 2010 at 9:41 am
Why are you trying to force suspect mode?
June 23, 2010 at 10:42 am
i want get aware to trouble shoot the database if it is suspect mode...........to bring the normal mode.........
for this iam trying to bring the suspect mode and do trouble shoot and bring the normal mode......
please give me suggestions or any other way to bring the database to suspect
June 23, 2010 at 11:12 am
Here are some instructions.
http://www.sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-backing-up-the-tail-of-the-log.aspx
Suspect mode is not necessarily corruption. You'd want to remove the a file and then startup the server to move it into suspect mode.
June 23, 2010 at 12:10 pm
Steve Jones - Editor (6/23/2010)
You'd want to remove the a file and then startup the server to move it into suspect mode.
That won't send a DB suspect. Maybe recovery pending, depending what file, but not suspect.
Suspect requires that rollback/rollforward started and failed. Typically corruption in log or corruption in data file.
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
June 24, 2010 at 5:21 am
Here's an article by Paul Randal on recovering a suspect database, it includes steps on how to force a database into suspect mode:
June 24, 2010 at 10:54 am
Sorry, I was thinking that Paul had deleted the MDF to bring about a suspect state.
Sure enough, deleting a file doesn't work for me. The database comes up on the server in "shutdown" state.
June 24, 2010 at 5:14 pm
iirc it used to on SQL 2000. Can't remember that far back and I don't have a 2000 instance to play with any longer.
I do have a partially written blog post on missing files and suspect databases, is something that gets confused a fair bit.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply