September 16, 2010 at 2:43 pm
Maybe a stupid question:
Would it help to detach the other DB's (assuming that option is available) and move the related mdf and ldf files to a different drive/server to free some space on the drive?
September 16, 2010 at 2:46 pm
Here is what was in the errorlog
08/31/2010 09:02:13,spid52,Unknown,The transaction log for database 'E_Y' is full. To find out why space in the log cannot be reused see the log_reuse_wait_desc column in sys.databases,
08/31/2010 09:02:13,spid52,Unknown,Error: 9002 Severity: 17 State: 2.,
08/31/2010 09:02:13,spid52,Unknown,D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\E_Y_log.ldf: Operating system error 112(There is not enough space on the disk.) encountered.,
08/31/2010 09:02:13,spid52,Unknown,Error: 17053 Severity: 16 State: 1.,
The D: drive is local to the server, not SAN. The D: drive contains all the databases which are very tiny (< 50 MB) except tempdb which is 2.5GB and this stupid database which is 96GB data and now 263 GB log. This is why I thought of shrinking tempdb temporarily, which would allow the logfile to grow. Then I could shrink the log and get things back on track. Then put tempdb back to its original size.
September 16, 2010 at 2:47 pm
LutzM (9/16/2010)
Maybe a stupid question:Would it help to detach the other DB's (assuming that option is available) and move the related mdf and ldf files to a different drive/server to free some space on the drive?
Yup, here's a quick recap.
GET SOME FREAKING FREE SPACE ON THAT DRIVE.
There's no point talking about this anymore untill that's done and the server has been restarted and the real problem, if different, exposed...
I'm off too. G night.
September 16, 2010 at 2:51 pm
GilaMonster (9/16/2010)
WayneS (9/16/2010)
@Gail - out of curiosity, would adding a drive and configuring it as a "mount point"(?) that expands the D drive be an alternative for freeing up space?Don't think so. From what I know of mount points, you mount a drive as a directory. It doesn't make the drive larger, just makes a directory redirect to a separate drive (symlink from Unix days). It won't let a single file spread across the drives.
There is a way to extend a drive, think it requires dynamic drives, can't recall what else it needs. Getting outa my area here.
Of course, if this is a SAN LUN, it should be possible for the storage admin to extend it trivially.
The only way I know to really grow a drive is with virtualization (or maybe partitioning, but that would ahve been explored by now)... turn off the server, add space, turn on, go to the hd properties and add the extra allocated space (sorry but I forgot the exact property name).
September 16, 2010 at 2:52 pm
hallhome (9/16/2010)
Here is what was in the errorlog08/31/2010 09:02:13,spid52,Unknown,The transaction log for database 'E_Y' is full. To find out why space in the log cannot be reused see the log_reuse_wait_desc column in sys.databases,
08/31/2010 09:02:13,spid52,Unknown,Error: 9002 Severity: 17 State: 2.,
08/31/2010 09:02:13,spid52,Unknown,D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\E_Y_log.ldf: Operating system error 112(There is not enough space on the disk.) encountered.,
08/31/2010 09:02:13,spid52,Unknown,Error: 17053 Severity: 16 State: 1.,
The D: drive is local to the server, not SAN. The D: drive contains all the databases which are very tiny (< 50 MB) except tempdb which is 2.5GB and this stupid database which is 96GB data and now 263 GB log. This is why I thought of shrinking tempdb temporarily, which would allow the logfile to grow. Then I could shrink the log and get things back on track. Then put tempdb back to its original size.
That wouldn't work. Tempdb will be shrunk. Then you'll need to restart the server. At which point temdb will grow back to it's original size.
You need to both shrink tempdb AND change the filesize. Then you'll be able to restart the server and hopefully get back to online state on all dbs.
September 16, 2010 at 2:53 pm
Do you have another drive with 2.5 GB on it? (The C drive even, or a flash drive, anything!)
If so, move TempDB completely (ALTER DATABASE TempDB ...), then restart SQL and see if the DB comes online. If so, immediately switch to Simple recovery and run a checkpoint. After that, there'll be time to fix stuff.
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
September 16, 2010 at 2:55 pm
GilaMonster (9/16/2010)
Do you have another drive with 2.5 GB on it? (The C drive even, or a flash drive, anything!)If so, move TempDB completely (ALTER DATABASE TempDB ...), then restart SQL and see if the DB comes online. If so, immediately switch to Simple recovery and run a checkpoint. After that, there'll be time to fix stuff.
I'm suddenly picturing the frazzled tech running out of the back room, and into the secretary pool. "Your IPOD! I NEED AN IPOD! Who's got one?! GIMME!" then tearing arse back into the server room.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 16, 2010 at 2:57 pm
hallhome (9/16/2010)
Here is what was in the errorlog08/31/2010 09:02:13,spid52,Unknown,The transaction log for database 'E_Y' is full. To find out why space in the log cannot be reused see the log_reuse_wait_desc column in sys.databases,
08/31/2010 09:02:13,spid52,Unknown,Error: 9002 Severity: 17 State: 2.,
08/31/2010 09:02:13,spid52,Unknown,D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\E_Y_log.ldf: Operating system error 112(There is not enough space on the disk.) encountered.,
08/31/2010 09:02:13,spid52,Unknown,Error: 17053 Severity: 16 State: 1.,
The D: drive is local to the server, not SAN. The D: drive contains all the databases which are very tiny (< 50 MB) except tempdb which is 2.5GB and this stupid database which is 96GB data and now 263 GB log. This is why I thought of shrinking tempdb temporarily, which would allow the logfile to grow. Then I could shrink the log and get things back on track. Then put tempdb back to its original size.
Yup but even 50 MB may be all you need.
If you "could" alter the growth of the file to 1 MB (assuming it can be done from pending mode), then it would work. If it's at 10% (7+ GB) and can only be changed if the db is online then you're pretty much screwed with that option.
What was the verdict with the last available backup?
September 17, 2010 at 5:51 am
GilaMonster (9/16/2010)
Carl Federl (9/16/2010)
Will that work if the current drives are 100% full?
There are some constraints with the solution:
1. The second transaction log needs to be on a different drive that has at least 1MB free.
2. The database primary file group needs to have enough space to add the definition of the second transaction log. So if the primary file is full and cannot grow, adding the second log file will fail.
3. The database needs to be Online
ISTR that the space for the new trans log shouldn't be 1 MB, but the amount of space that MODEL claims for its trans log. Or am I wrong on that?
Meaning, if MODEL requires 50MB for it's minimum transaction log size, then that's how much space you need.
September 17, 2010 at 5:53 am
sqlbuddy123 (9/16/2010)
1. Free up some space on D drive and see that if the DB recovers. This may include shrinking other databases to free up some space.
Someone may have already responded to this comment, so I apologize if I'm repeating...
You can't shrink other databases while the disk is full. SQL Server requires space in which to do the shrinking, in which to add markers or throw data into tempdb or whatever it's doing. I know this much from experience.
September 17, 2010 at 6:02 am
GilaMonster (9/16/2010)
hallhome, please for now switch any user databases on that server into simple recovery model if they're not already.
Dumb question time. Please forgive.
If the disk is full (and it sounds pretty 100% full), will he be able to save changes to the other databases? Will he even be able to back up?
My experience, when our disk got full, was that we had to manually delete things to make room because nothing we tried on SQL would actually commit, save or finish because there wasn't enough "space" to process the request. We never had a problem doing work arounds when there was free space. Just when the disk was so full, you could barely squeak a byte or bit out of it.
I'm glad I read this thread. I'm going to look up Paul R now.
September 17, 2010 at 6:23 am
you could also add another log file on another drive. not ideal, but it will get you going until you can get the mess cleaned up, then remove it. As the others have already stated, depending on where you are in the recovery cycle, you could truncate the log, if dumping it isn't working. make sure you have a clean backup before and after.
September 17, 2010 at 6:25 am
What about this:
Shut down sql service,
move the other (small) dbs' mdf and ldf somewhere else (even another computer).
Resart sql service, ignore error about missing db files and suspect dbs.
Set your big db to simple recovery and shrink the log.
Copy the mdf and ldf back to their original location and recover the dbs (restore database xxxx with recovery should be enough.) or do another service stop/start.
September 17, 2010 at 7:02 am
lrutkowski (9/17/2010)
you could also add another log file on another drive. not ideal, but it will get you going until you can get the mess cleaned up, then remove it.
If you read through the suggestions already made, you'll see that's been suggested before, however you can only add a log file if the database is online, which this one is not.
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
September 17, 2010 at 7:05 am
Brandie Tarvin (9/17/2010)
If the disk is full (and it sounds pretty 100% full), will he be able to save changes to the other databases? Will he even be able to back up?
Maybe and maybe. Depends if there's a little bit of free space in the log files or not.
I'm glad I read this thread. I'm going to look up Paul R now.
Paul R as in Paul Randal
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 15 posts - 46 through 60 (of 86 total)
You must be logged in to reply to this topic. Login to reply