asked on
SQL Server 2005 database messed up. Can it be fixed?
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.tblPaEmpGenIn
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?
"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.tblPaEmpGenIn
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?
Microsoft SQL Server 2005
Last Comment
Mark
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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."
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."
ASKER
That fix seemed to work. Thanks
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.