SQL Server outof space question.

Question

Post reply

SQL Server outof space question.

haiao2000

Hall of Fame

Points: 3734
More actions
April 29, 2013 at 7:52 am

#293219

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!

Viewing 15 posts - 1 through 15 (of 19 total)

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

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

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

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/

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

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

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/

sqlnaive SSCoach Points: 17437 More actions · Answer 5

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...) 🙂

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

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.

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

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!

Lowell SSC Guru Points: 323504 More actions · Answer 8

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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

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 10

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.

Lowell SSC Guru Points: 323504 More actions · Answer 11

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL Show SSCertifiable Points: 6143 More actions · Answer 12

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.

SQL Show SSCertifiable Points: 6143 More actions · Answer 13

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.

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

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.