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 ««12

SQL Server outof space question. Expand / Collapse
Author
Message
Posted Monday, April 29, 2013 12:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
Lowell (4/29/2013)
hmmm; i'd say there is always files that can be moved or deleted.

files in temp folders on the operating system, log files, , the already suggested moving of backup files, so many other possibilities to address this quickly.


I agree, unfortunately I didn't even get a chance to lay my hand on the server, another engineer in the company, who work at a client site in another country asked for assistance and I am shooting in the dark.
Post #1447717
Posted Monday, April 29, 2013 12:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:47 PM
Points: 12,910, Visits: 32,020
ouch; remote-second hand assistance is tough,

I feel for you and what headaches are involved in getting your information once-removed from the client.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1447720
Posted Tuesday, April 30, 2013 8:49 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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.
Post #1448081
Posted Tuesday, April 30, 2013 8:52 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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.
Post #1448084
Posted Tuesday, April 30, 2013 9:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
The fellow coworker that asked the question has been quiet since my last suggestion, so I am guessing he was able to manage the sittuation. 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 =)

Thanks everyone for your input.
Post #1448096
Posted Tuesday, April 30, 2013 9:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
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..








************************************
Every Dog has a Tail !!!!!
Post #1448105
Posted Tuesday, April 30, 2013 9:26 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: Yesterday @ 10:05 AM
Points: 42,829, Visits: 35,961
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 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 #1448110
Posted Tuesday, April 30, 2013 9:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
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..



In my case the fellow coworked stated that I couldnt truncate log even. so not sure what the deal there.
Post #1448114
Posted Tuesday, April 30, 2013 9:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
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..



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.
Post #1448115
Posted Tuesday, May 7, 2013 1:36 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 5:20 AM
Points: 3,546, Visits: 2,651
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.
Post #1450013
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse