July 20, 2005 at 8:02 am
I am running Win2000 with SQL2000. My backup has been working fine for a couple of days, until this morning. Here's the error message I got from my nightly backup job:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 945: [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'IEX39' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.
1. I have plenty of backup space - over 1.6 terabytes free on the backup drive
2. No users are logged into the database - test database and server was rebooted after the failed backup
3. I can query the database fine and view a list of tables in Enterprise manager
4. The SQL Server log pretty much says the same thing as the error message, not much help
July 20, 2005 at 8:06 am
I also tried to create a new maintence job and just backup the problem database. Same error.
I also tried to create a new maintence job to optimize the database. Same Error.
I also tried to create a new maintence job to reorganize the database. Same Error.
July 20, 2005 at 8:24 am
Does look like a space problem - perhaps with the db itself. Have you checked to see whether the 'Automatically grow file' property has been set (check the data file and the log file). If not, you may have your solution.
If you can successfully backup other databases, that also indicates a problem with this one specifically.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 20, 2005 at 8:26 am
I always turn off the automatically grow and shrink options on the data and transaction files.
July 20, 2005 at 8:33 am
OK - so have you run out of growing space?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 20, 2005 at 9:08 am
I have plenty of 'growing' space. I run a system that needs all of the processing power that it can have. I don't want to take the hit of the database growing and shrinking without me controling when it happens.
July 20, 2005 at 9:21 am
OK, no offence intended.
This link may be of interest - they seem to have the same symptoms as you.
http://www.talkaboutdatabases.com/group/comp.databases.ms-sqlserver/messages/123008.html
Other than that, I'm running out of ideas.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 20, 2005 at 1:17 pm
Ok, I had to do some research on what other people were doing, but here's the conclusion:
1. Permissions were changed on the drive where the database resides and not rebooted
2. Everyone still had access to the databases on that drive
3. Machine was rebooted a couple days later and when SQL Server was restarted, it was unable to get access to the drive and put the databases into suspect status.
I put the permissions back on the drive and rebooted SQL Server and now everything works fine.
Thanks for any responses to this post.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply