April 29, 2013 at 7:52 am
Hello Experts,
One of our clients server ran into this issue. The server has both application and SQL Server installed on it. The hard drive is completely full and we don’t know what is causing it to be full but because it is full, we can’t login to the SQL Server because there is not enough space to write to the transaction log.
We can’t stop the MSSQLSERVER service because there’s no space and there’s not anything I know of clearing to be able to clear enough space so we can delete the transaction log. What else could I do other than attaching another HDD and move/add data/log file to the new volume?
Thanks in advance!
April 29, 2013 at 7:58 am
Do Not delete the log file.
Add more space and/or remove files that aren't necessary (old backups, logs, etc)
What error do you get when you try to connect? Logging in doesn't require log space, so shouldn't be a problem.
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
April 29, 2013 at 8:13 am
GilaMonster (4/29/2013)
Logging in doesn't require log space, so shouldn't be a problem.
Actually I have run into this in the past. We had a DB setup with restricted growth on the log file and once it got full nobody could connect to it. I know in theory it shouldn't take any log space but there was obviously something trying to write to it because the error I was receiving indicated the disk was full. In my case the actual disk was not full but the log had consumed all of the allocated space. As soon as I expanded the log allocation the users were able to connect instantly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 29, 2013 at 8:14 am
Was TempDB out of space as well? Was there a logon trigger?
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
April 29, 2013 at 8:25 am
GilaMonster (4/29/2013)
Was TempDB out of space as well? Was there a logon trigger?
In my case tempdb had plenty of room and there was no logon trigger. I suspect that for this thread your idea of clearing a little of drive space will get them up and running long enough to clear out some more drive space.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 29, 2013 at 8:31 am
Haiao, I experienced one such issue as well with one of my friend's client. There the issue was very basic. All the DBs were configured with Full recovery but the backups were were never taken afterwards. It was very small scale organization with very basic IT infrastructure and entire new setup so they didn't give much concentration on DB. But after certain time, the log files grew to the size where it was impossible to log in. I don't remember actually how I dealt at that time but the final thing solution was to take backups and the DBs came to very normal size thereafter. I configured the scheduled backups on the server and thereafter they never faced this issue.
I don't know if this is same scenario (as my case my rarest of rare where clients have not much idea and they don't want to spend much on resources), but just wanted to share my experience (as you never know...) 🙂
April 29, 2013 at 12:47 pm
sqlnaive (4/29/2013)
Haiao, I experienced one such issue as well with one of my friend's client. There the issue was very basic. All the DBs were configured with Full recovery but the backups were were never taken afterwards. It was very small scale organization with very basic IT infrastructure and entire new setup so they didn't give much concentration on DB. But after certain time, the log files grew to the size where it was impossible to log in. I don't remember actually how I dealt at that time but the final thing solution was to take backups and the DBs came to very normal size thereafter. I configured the scheduled backups on the server and thereafter they never faced this issue.I don't know if this is same scenario (as my case my rarest of rare where clients have not much idea and they don't want to spend much on resources), but just wanted to share my experience (as you never know...) 🙂
Thank you, I suggested client to do this, but in the mean time, I told them to add new drive to deal with a unresponsive DB server.
April 29, 2013 at 12:49 pm
GilaMonster (4/29/2013)
Do Not delete the log file.Add more space and/or remove files that aren't necessary (old backups, logs, etc)
What error do you get when you try to connect? Logging in doesn't require log space, so shouldn't be a problem.
Yeah I told them not to delete log file. client told me there is nothing on the system can be removed =)..ok. so I suggested them to add new drive so that they can at least shrink the log.
Thank you!
April 29, 2013 at 12:51 pm
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.
Lowell
April 29, 2013 at 12:54 pm
haiao2000 (4/29/2013)
GilaMonster (4/29/2013)
Do Not delete the log file.Add more space and/or remove files that aren't necessary (old backups, logs, etc)
What error do you get when you try to connect? Logging in doesn't require log space, so shouldn't be a problem.
Yeah I told them not to delete log file. client told me there is nothing on the system can be removed =)
So the drive in question contains the data file and the log file and absolutely nothing else?
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
April 29, 2013 at 12:56 pm
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.
April 29, 2013 at 12:59 pm
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
April 30, 2013 at 8:49 am
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.
April 30, 2013 at 8:52 am
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.
April 30, 2013 at 9:05 am
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.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply