June 18, 2013 at 10:52 am
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.
June 18, 2013 at 11:17 am
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
June 18, 2013 at 12:17 pm
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.
June 18, 2013 at 12:27 pm
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
June 18, 2013 at 12:58 pm
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
June 18, 2013 at 1:07 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply