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 7:52 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
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!
Post #1447536
Posted Monday, April 29, 2013 7:58 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: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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 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 #1447543
Posted Monday, April 29, 2013 8:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 13,302, Visits: 12,161
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 Moden's 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)
Post #1447554
Posted Monday, April 29, 2013 8:14 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: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
Was TempDB out of space as well? Was there a logon trigger?


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 #1447555
Posted Monday, April 29, 2013 8:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 13,302, Visits: 12,161
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 Moden's 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)
Post #1447561
Posted Monday, April 29, 2013 8:31 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
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...)
Post #1447566
Posted Monday, April 29, 2013 12:47 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
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.
Post #1447705
Posted Monday, April 29, 2013 12:49 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
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!
Post #1447708
Posted Monday, April 29, 2013 12:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--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 #1447711
Posted Monday, April 29, 2013 12:54 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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 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 #1447715
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse