We help IT Professionals succeed at work.

SQL Server 2005 database messed up. Can it be fixed?

Mark
Mark asked
on
164 Views
Last Modified: 2017-03-27
I had an ungraceful shutdown on my SQL Server 2005 host. Now I am having problems accessing the tables. I can connect to the database with user sa in Management Studio, but not with a regular user. With sa I cannot access the important database. I get the error:

"Database 'osh2011' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details. (Microsoft SQL Server Error: 945)"

There is plenty of disk space. In Management Studio / Object Explorer that database and others do no have '+' signs beside them and therefore cannot be expanded to see the tables. Read-Only databases can be expanded and tables accessed vial I cannot select 'osh2011' in the database drop-down.

When trying to access the 'osh2011.dbo.tblPaEmpGenInfo table as user sa the error log has:

Logon - Error: 19456: Severity: 14, State 16>
Logon - Login failed for user 'sa'/ [CLIENT: <named pipe>

SQL Server Config Manager > Protocols has named piles enabled.

What can I do to fix this?
Comment
Watch Question

Jason clarkDBA Freelancer

Commented:
Due to missing of some files, or other resource the SQL database cannot be recovered hence induced error code 945. You may choose to rebuild your database. Look msdn blog: https://msdn.microsoft.com/en-us/library/dd207003.aspx

also have a look on this to solve with other option: http://www.sqlrecoverysoftware.net/blog/sql-error-926-and-945.html

hope this will help you.
Software Engineer
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
james snow: Leapin' Lizards! That appeared to work! I've just done it, so I'll have to give it a day or two in production to confirm, but I was able to run queries and the Access application which uses this database. Your procedure needed a couple of corrections:

alter database osh2011 set emergency

DBCC checkDB (osh2011)  -- You had an extra blank: check DB. This command probably should have included the REPAIR_ALLOW_DATA_LOSS parameter as other web examples showed, but apparently I didn't need that in this case.

ALTER DATABASE osh2011 SET SINGLE_USER with ROLLBACK IMMEDIATE

-- DBCC DATABASE (osh2011, REPAIR_ALLOW_DATA_LOSS)  -- this command didn't work at all. No such "DBCC DATABASE" command; at least not in 2005.

ALTER DATABASE osh2011 SET MULTI_USER

alter database osh2011 set online -- you omitted this one.

And your blog link gives "Server not Found."

Another link that had good info on this: https://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/

Jason clark: I started looking at your suggestion, but I needed the original SQL Server 2005 DVD, and it ultimately needed to restore the latest database backups. As this is mostly a legacy system whose "tables" are mostly views selecting from production system tables running on a separate SQL Server 2014 (if that all makes sense), I don't do a routine backup of the database. Instead, I do a daily image backup (Acronis) of the whole server which is normally very quick and error-free. I was planning on doing that restore tomorrow, but then saw james snow's post and thought "what the heck, nothing to lose."

Author

Commented:
That fix seemed to work. Thanks

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions