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

Mark
Mark used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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:
Hello,
try the below steps, it may help You

Open Microsoft SQL Server Management Studio > Click new Query Button> Write SQL Scripts>
( ALTER DATABASE database_name SET EMERGENCY
DBCC check DB (database_name)
ALTER DATABASE database_name SET SINGLE_USER with ROLLBACK IMMEDIATE
DBCC DATABASE (database_name, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE database_name SET MULTI_USER
 )
Then, click Execute tab.
For detailed Information to repair it manually, refer this blog

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial