November 9, 2010 at 1:32 am
Hi Everyone,
I am working in SQL Server 2008. My database is displaying "Suspect" in front of database name. Can anyone suggest me, How can I remove this, so that I can view Tables & Stored Procedure inside it?
Thanks
November 9, 2010 at 1:40 am
First things first. Do not, for any reason, no matter what anyone else tells you, detach that database. You will not be able to reattach it.
To offer any useful advice I need to know why SQL marked the database suspect. Please open the SQL error log and fine any and all messages relating to this database. Post them here.
Do not do anything else for the moment!
Do you have a backup of this database?
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 9, 2010 at 2:32 am
Thanks Gila for Replying. Even I couldn't make out why the database is marked as suspect. You had asked me to open the SQL error log. Sorry but how can I open that??? No, I don't have any recent backup of the same.
November 9, 2010 at 2:40 am
Is there a DBA there that you can speak with? Recovering a suspect database is not something for a novice.
Management studio. Object explorer. Management -> SQL Server logs
Without seeing what's in the error log there's no way to tell why the DB is suspect. You do realise that there's a good chance that, with no backup, you are going to lose data in fixing this.
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 9, 2010 at 2:42 am
In SSMS, Your server > Management > SQL Server Logs.
There you will find various error log files. pls look out for error messages in the log files. Also see application event logs for any errors.
November 9, 2010 at 2:42 am
These screenshots should help you to read the SQL Server Error Log.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 9, 2010 at 3:16 am
Hi
Here is the error :
"The log scan number[59:280:1] passed to log scan in database 'db_CrystalTec' is not valid. This error
may indicate data corruption or that the log file(.ldf) does not match the data file(.mdf). If this error
occur during replication, re-create the publication. Otherwise, restore from backup if the problem results in a
failure during startup. " as displayed in log file.
November 9, 2010 at 4:32 am
Have you done anything silly with the transaction log recently? (switching files around or similar?)
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 9, 2010 at 5:59 am
vgtestingg (11/9/2010)
Hi Everyone,I am working in SQL Server 2008. My database is displaying "Suspect" in front of database name. Can anyone suggest me, How can I remove this, so that I can view Tables & Stored Procedure inside it?
Thanks
Hi
You can use
sp_resetstatus [ @dbname = ] 'database'
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER DATABASE instead.
alternate way
EXEC sp_resetstatus 'DBname';
ALTER DATABASE DBname SET EMERGENCY
DBCC checkdb('DBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DBname SET MULTI_USER
To run this Permissions Required is membership in the sysadmin fixed server role.
Why it is happening because there might not enough space in server to have data's or to load data's
Thanks
Parthi
Thanks
Parthi
November 9, 2010 at 6:04 am
Why it is happening because there might not enough space in server to have data's or to load data's
Thanks
Parthi
Where did you get this from?, it does not say this in the error log
November 9, 2010 at 6:08 am
:w00t:
Not a great advice that i would follow before knowing what exactly caused this.
November 9, 2010 at 6:11 am
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)
Thanks
Parthi
You also have to be careful running this statement without knowing what data could possibly be lost
November 9, 2010 at 6:19 am
parthi-1705 (11/9/2010)
Terrible, generic advice omitted
Aaahhh!
Why it is happening because there might not enough space in server to have data's or to load data's
Absolutely not. Insufficient space will not send a database suspect
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 9, 2010 at 6:21 am
parthi-1705 (11/9/2010)
vgtestingg (11/9/2010)
Hi Everyone,I am working in SQL Server 2008. My database is displaying "Suspect" in front of database name. Can anyone suggest me, How can I remove this, so that I can view Tables & Stored Procedure inside it?
Thanks
Hi
You can use
sp_resetstatus [ @dbname = ] 'database'
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER DATABASE instead.
alternate way
EXEC sp_resetstatus 'DBname';
ALTER DATABASE DBname SET EMERGENCY
DBCC checkdb('DBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DBname SET MULTI_USER
To run this Permissions Required is membership in the sysadmin fixed server role.
Why it is happening because there might not enough space in server to have data's or to load data's
Thanks
Parthi
@parthi the above advise is not good at all.Keep in mind you are playing with data's.
Follow the Gail's advise what she's told in the pervious replays.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply