SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cannot access full database


Cannot access full database

Author
Message
aiki4ever-796329
aiki4ever-796329
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 95
I've got a database with a tran log that expanded and then filled up the disk. Unfortunately, I can't even execute "use database" without an error (cannot be opened due to insufficient disk). If you can't "use" the database, then you can't alter the thing to set it offline then online to fix the problem. And if you can't "use" the database then you can't truncate the log via

dbcc shrinkfile (databaseName_Log, 1) -- shrink truncated log file to 1 meg

Sometimes your sys admin can expand the disk the log is on. If that is not possible, how can I bring this database back online? There's always a restore from the latest backup, but I'm wondering if there is any other viable solution.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218909 Visits: 46279
And the exact error you're getting is???


p.s. truncating the log is the incorrect approach. Shrinking the log is the incorrect approach. Setting the database offline is the incorrect approach and is more likely to break things worse than fix anything.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


aiki4ever-796329
aiki4ever-796329
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 95
Here are my error messages interspersed with the SQL that caused them.



-- try just to use the database
use cfs_vehicle_backups

Msg 945, Level 14, State 2, Server CFSDB02, Line 3
Database 'CFS_Vehicle_Backups' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
use master

Changed database context to 'master'.

-- try to add space to the tran log
alter database cfs_vehicle_backups add log file (name=TranLog2, filename='E:\SqlData\Cfs_Vehicle_Backups.log2', size=20MB)

Msg 945, Level 14, State 2, Server CFSDB02, Line 3
Database 'CFS_Vehicle_Backups' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

-- try to set the database offline
alter database cfs_vehicle_backups set offline with rollback immediate

Msg 5069, Level 16, State 1, Server CFSDB02, Line 3
ALTER DATABASE statement failed.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218909 Visits: 46279
As I said, setting offline is completely the wrong solution. DO NOT try to take the DB offline, detach it or to restart SQL server.

What other messages are there in the log?
See the SQL Server errorlog for details.


This doesn't look like a full log file, it looks like a file is missing or inaccessible. What lead you to conclude that the log is full?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


aiki4ever-796329
aiki4ever-796329
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 95
Gail,

Thanks for the help! Bouncing the SQL Server was enough to make the database operational.

One of the error messages from SQL Server mentioned insufficient disk space. To check on that, I executed cygwin's df -h; this command reported that a disk was 100% full. Put the two together and a disk being full certainly seems like the cause. Now that I am able to check database properties, I see that my tran log file is written to the J: disk and that disk is 100 % full.

Anyway, the problem is solved and I've learned a little more about SQL Server.

PS In case you're interested, cygwin provides a suite of linux tools for the DOS command line. Being a linux guy from way back, I use cygwin, perl, and vim quite a bit.

David
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218909 Visits: 46279
aiki4ever-796329 (6/18/2013)
Thanks for the help! Bouncing the SQL Server was enough to make the database operational.


I seem to recall saying not to do that. Often in this kind of situation restarting SQL makes the problem way worse.

One of the error messages from SQL Server mentioned insufficient disk space.


The message you posted said 'inaccessible files or insufficient memory or disk space.'. It usually is actually inaccessible files. Lack of disk space alone will NOT make a database unavailable. It just makes it read only and makes data modifications throw errors.

I suspect what happened here was that the disk became full and something in the IO subsystem temporarily made the disk unavailable as a result. If SQL notices that a disk is not available, it will throw errors and it will not retry to see if that disk has come back until either SQL is restarted or the database is taken offline and brought online. Hence why the restart worked in this particular case and why you could see the disk at a slightly later point, SQL had already tried and failed and wouldn't retry.

I must emphasis, restarting SQL when you have error 945 is a dangerous thing to do and can make the situation worse. Hence it should not be done without guidance.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search