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?
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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
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
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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."
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.