SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server outof space question.


SQL Server outof space question.

Author
Message
haiao2000
haiao2000
Right there with Babe
Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)

Group: General Forum Members
Points: 740 Visits: 438
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!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220321 Visits: 46279
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
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61499 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220321 Visits: 46279
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
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61499 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
sqlnaive
sqlnaive
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6335 Visits: 2774
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
haiao2000
Right there with Babe
Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)

Group: General Forum Members
Points: 740 Visits: 438
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
haiao2000
Right there with Babe
Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)

Group: General Forum Members
Points: 740 Visits: 438
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
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70372 Visits: 40924
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!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220321 Visits: 46279
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search