SQL Server issues

[Former Member]

Hi All,

In SQL server mgmt studio I am finding this error

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo

Additional information:

Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space, See the SQL Server errorlog for details. (Microsoft SQL Server, Error : 945)

What might be the cause and How to solve this?

Regards,

Rupa Sarkar

[Former Member · Accepted solution]

Hi Rupa......

Is your DB in SQL showing suspect?

Else if you have MDF and LDF file of your DB then try to attach it.

This might solve your issue.....

Regards,

Rahul

[Former Member · Accepted solution]

Yes Rahul, It is showing suspect.

[Former Member · Accepted solution]

Yes Jimmy I tried whatever written in

http://blog.sqlauthority.com/2007/08/02/sql-server-fix-error-945-database-cannot-be-opened-due-to-in...

Still...no solve

[former_member186095 · Accepted solution]

you may use a solution from sap note 1466453. I quote some solutions:

Normally physical errors are displayed within the SQL Management Studio for example as 'suspect'. Logical errors are normally identified by aquery 'DBCC CheckDB'. Very often such errors are caused by an unexpected power outage or faulty hardware, e.g. RAID controllers.

Solution
First make sure that the root cause of the issue is resolved in order to avoid a similar scenario in the future.

Decide on one of the following options:

A) Locate a non-affected backup of the DB.

B) Repair the table with/without data loss.

.

You must first identify the table name, indicated in the DBCC check results.
- Run 'dbcc checktable ( XXX Table name, repair_allow_data_loss)'


Please note that the responsibility to execute the REPAIR_ALLOW_DATA_LOSS option is outside SAP support scope and we recommend to check carefully with application/business know-how. So we would like to suggest you to contact your database administrator to fix this issue. SAP is not responsible for fixing this database. This responsibility lies with the customer.

You can also find information about this on the Microsoft website:
http://msdn.microsoft.com/en-us/library/ms176064.aspx

JimM

[Former Member · Accepted solution]

Hi Rupa......

It might be because of sudden power failure. You should have good facility of Power Saver so that atleast server would be protected and get enough time to shut it down.

Now i guess its difficult to recover the suspected DB even if you try there might be damaged of few DB tables so can get lot oof errors.

I would suggest you to please restore most recent Back up of DB just before Db suspect. And continue with that DB.

And make some good provision as suggested for server protection....

Regards,

Rahul

[former_member1269712 · Accepted solution]

Hi Rupa,

You can recover your database from Suspect / Emergency mode.

Do following steps...

Step 1:

Open SQL Server Management Studio and connect to the SQL Server. Make sure that your current database is set to master

Step 2:

Clear the suspect mode of the database using sp_resetstatus DatabaseName. This will clear the suspect flag and make the database available online

Step 3:

Change the database status to Emergency using the following command. Emergency mode allows you to access the databases as normal but with no consistency guarantee. This option also allows us to export the table data so that we can minimize the damage.

ALTER DATABASE DatabaseName SET EMERGENCY;

Step 4:

Restrict database to single user by changing the access mode as mentioned below

ALTER DATABASE DatabaseName SET SINGLE_USER;

Step 5:

Run the CHECKDB command with u201CREPAIR_ALLOW_DATA_LOSSu201D option. This option should be tried as last option as it always behaves the way it is named. We are not sure of what data it removes.

DBCC CHECKDB (DatabaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

Even though the step 4 is expected to result in data loss while repairing the database, I was lucky to restore the database without any loss. My job would have been much easier if the admin of the database server followed few general guidelines that allow recovery of database in case of any unexpected failures. Here are the few of them

Backup your data frequently. (once in two days or daily or even more frequent depending on your needs)

Have multiple backups. Move the backups to external drives or tapes frequently

Validate that your backups are good by performing trial restores to alternate server

Run CHECKDB regularly if possibly in your case, to know how long it regularly takes

If above steps are failed then read more following links and follow steps as given in

http://www.gerixsoft.com/blog/mssql/recovering-mssql-suspect-mode-emergency-mode-error-1813

http://www.tek-tips.com/faqs.cfm?fid=4210

Thanks

Sachin

[Former Member · Accepted solution]

What is the functionality of <dbo.systargetservers> in msdb?