November 29, 2010 at 10:24 am
Hello,
When I restart SQL Server, there is a good amount of space created on C drive (100 GB).
I have to do it every month or two when we run out of space. why is it so ?
I do know that the temp DB grows, however, when I check the temp DB space (before restart), it did not occupy so much space.
what can I look into or do to improve this, so that I do not have to do this restart on regular basis.
Thanks!
November 29, 2010 at 10:39 am
I guess the first thing you need to determine is which file or files are taking up the space. Probably worth going through making a note of the space used in various folders after a reboot, and again a month later.
If I had to take a guess though, have you checked the size of your log files and how quickly they are growing? While the logs rotate, and you can specify a maximum number of them that should be kept, they are only cycled when you restart SQL. Therefore, if you're generating 100GB of log files in a month or two, when you restart the server, it will start a new file, and the oldest one will be deleted, therefore clearly the space used by it.
If that is the case then you probably need to decide whether you need that many logs kept, or perhaps more importantly, look at moving them to another drive so they're not on your main system disk.
November 29, 2010 at 3:18 pm
Along the lines of what Keith mentioned, I would pay particular attention to the log file for TEMPDB
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
November 29, 2010 at 3:35 pm
Thank you very much Keith.
Thanks Chris.
November 29, 2010 at 3:43 pm
May I also know, if there is any other way to get the space back, without restarting the service ?
November 29, 2010 at 3:48 pm
Once you determine what file extension is taking up all of the drive space (if it turns out to be a SQL log file .ldf) then it is highly recommended that you set up transaction log file backups along with your full database backups. Take a look on SQL Server Central for the great article by Gail Shaw (Gila Monster) regarding Log Files
Good luck
Chris
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
November 29, 2010 at 4:12 pm
Thank you very much Chris.
November 30, 2010 at 2:27 am
By default the tempdb is set as Simple recovery model, so I doubt the tempdb transaction log file would be the cause.
If you do find it the SQL Server error logs at fault, you can force them to rotate by using sp_cycle_errorlog periodically, rather than having to restart the server.
If you check out http://www.sqlservercentral.com/Forums/Topic337468-149-1.aspx there's a bit of a discussion about it, including a script from Anthony Young that can be run regularly, and then cycle the log when it reaches a set size. I haven't used it myself so can't judge how well it works though.
November 30, 2010 at 11:20 am
Thanks Keith. I shall have a look at the article. I am new to DBA side, so it helps a lot.
December 1, 2010 at 8:19 am
Keith Langmead (11/30/2010)
By default the tempdb is set as Simple recovery model, so I doubt the tempdb transaction log file would be the cause.
Simple recovery simply means the tempdb log space gets re-used after it's cleared up; it does _not_ mean the tempdb log gets shrunk (nor should you shrink it; shrinking and growing causes fragmentation!). If restarting always saves a lot of space, then you need to increase the base size of tempdb; you might as well start the server with a reasonable size tempdb and log. Also, increase autogrowth; the default 1MB/10% is horrible (filesystem level fragmentation is often the result, called "external" fragmentation because it's "outside" of SQL Server)
Large transactions, explicit or implicit, can cause tempdb to grow; both the data and the log portions. This is a natural part of doing large transactions, and you simply need large amounts of space to do large amounts of work all at once.
Now, if some transactions are hanging open, that's a separate problem. If some process is creating permanent tables in tempdb and not cleaning them up, that's a separate problem as well. Watch the contents, use the Disk Usage by Top Tables report, and so on.
December 1, 2010 at 8:47 am
December 1, 2010 at 9:01 am
Not quite sure why everyone else is focusing on tempdb when touchmeknot said right from the start that "I do know that the temp DB grows, however, when I check the temp DB space (before restart), it did not occupy so much space." so they've already eliminated that as a cause.
The reason I wrote off the tempdb suggestion originally was that it was mentioned in the context of doing transaction log backups of it, which of course can't be done since it's simple.
In terms of transactions causing the log to grow you are of course correct, however, if the OP had said the space was being used up within a short space of time I might suspect that as a cause, however the time period discussed is every month or two. Now, unless I completely misunderstand how simple transactions work, the tempdb transaction log will grow while the transactions are open. Once they are completed that space is available for use again. Therefore, in order to grow to that kind of size over that period of time, the transactions would need to be stuck open for that entire time, and hopefully that's unlikely.
December 1, 2010 at 9:09 am
personaly i prefair seperate leave os alone in c into my organisation i'm instal alaway like that
c:\ for Os and SQL engins include master et model MSDB
D: SQL data DB
L: SQL logs DB
T tempDB
if you extra budjet on diff disk spindel or SAN lun
December 2, 2010 at 11:45 am
I'm not so sure whether this is a SQLServer related problem.
I have seen the same problem occure on other servers,
fileservers, exchange servers etc..
The bad thing is, you can't find a file thats growing big because its a system related issue in this case.
after restart you gain the space back, but i guess this is only true when you restart the host O.S.
Could it be that this server is performing some filedisk backup tasks in conjunction with "Volume Shadow Copy"
next time you get the need to reclaim space,
try the following first
services.msc
restart the service "Volume Shadow Copy"
if this is the case this will work rather fast, no downtime and you can keep your execution plans.
Wkr,
Eddy
December 2, 2010 at 3:25 pm
I would download and use a tool like WinDirStat to identify what files are using the space. Until you know what files are using the space - you really don't know if it is SQL related or not.
It could be tempdb growing - and, the only way to reduce that reliably is to restart SQL Server. To fix it permanently, you would have to identify which transactions are causing tempdb to grow and fix those processes.
Since the OP has already mentioned that it does not appear to be tempdb - then my guess is that they are running queries using SSMS on the server. And, returning very large resultsets to the client. SSMS uses temp files to hold that data and doesn't necessarily clean up after itself very well.
Again, find out what files are using all of the space - and then figure out what needs to be fixed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply