SQL Server outof space question.

Question

SQL Server outof space question.

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 1

SQL Show (4/30/2013)
I had the same problem few years back, no space in ldf drive. But users were able to logon. We received error only when try to run dml statements.

The same problem happened last week & it was so urgent that i only had sfew minutes to make it right & finnaly i tyhought of & did a Truncate Log... Shrinking option..:w00t:

************************************
Every Dog has a Tail !!!!! :-D

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

haiao2000 (4/30/2013)
The last question he asked was if he can delete the LDF file and still be able to reattach the database. I told him he could, but extremely cautious and back it up before delete it permanently. I sent him query to reattach db without LDF file and that was last time I heard from him =)

I do hope he had a backup, because if you detach a DB that's got a completely full log file and then delete the transaction log, the chances are better than average that you won't be able to reattach (there wasn't space in the log for SQL to do a clean shut down) and that you'll need to fetch out a database backup and restore it.

That's why the first thing I said in this thread was "Do Not Delete the log file"

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

haiao2000 Hall of Fame Points: 3734 More actions · Answer 3

OnlyOneRJ (4/30/2013)
SQL Show (4/30/2013)
I had the same problem few years back, no space in ldf drive. But users were able to logon. We received error only when try to run dml statements.
The same problem happened last week & it was so urgent that i only had sfew minutes to make it right & finnaly i tyhought of & did a Truncate Log... Shrinking option..:w00t:

In my case the fellow coworked stated that I couldnt truncate log even. so not sure what the deal there.

haiao2000 Hall of Fame Points: 3734 More actions · Answer 4

haiao2000 (4/30/2013)
OnlyOneRJ (4/30/2013)
SQL Show (4/30/2013)
I had the same problem few years back, no space in ldf drive. But users were able to logon. We received error only when try to run dml statements.
The same problem happened last week & it was so urgent that i only had sfew minutes to make it right & finnaly i tyhought of & did a Truncate Log... Shrinking option..:w00t:
In my case the fellow coworked stated that I couldnt truncate log even. so not sure what the deal there.

Oh Ok I remember you mentioned that.

sqlnaive SSCoach Points: 17437 More actions · Answer 5

SQL Show (4/30/2013)
sqlnaive (4/29/2013)
All the DBs were configured with Full recovery but the backups were were never taken afterwards
Correct me if am wrong. If backup never taken, then db will go into auto-truncate option, even in full recovery model.So it could not be your problem.

Nope. if you are not taking backups then log file will start growing to an extent where you will face disk issue. So once your DB has been set to bulk-logged ot full reovery, you MUST schedule log backups. Auto-truncate does not automatically gets enabled in full recovery. It is the feature in simple recovery model.

I had the same problem few years back, no space in ldf drive. But users were able to logon. We received error only when try to run dml statements.

This was because logging in is not an event that is getting written in log. In case you fire any DML, then there is the data change and that's when there is necessity to write in log file which is showing disk issue.