Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cannot access full database Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 10:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:35 PM
Points: 9, Visits: 90
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.

Post #1464790
Posted Tuesday, June 18, 2013 11:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1464800
Posted Tuesday, June 18, 2013 12:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:35 PM
Points: 9, Visits: 90
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.
Post #1464829
Posted Tuesday, June 18, 2013 12:27 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1464830
Posted Tuesday, June 18, 2013 12:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:35 PM
Points: 9, Visits: 90
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
Post #1464841
Posted Tuesday, June 18, 2013 1:07 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1464846
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse