December 12, 2008 at 9:32 am
Hi, all. We are running SQL Server 2008 on Windows Server 2008 (both 64 bit) and I am running into some space issues on the C drive. I have tried to find recommendations for hardware configurations and I found a BOL article listing the install space needed but it doesn't really talk about running space.
Here's what is happening. Whatever genius spec'ed out the server gave us a 11GB C: drive and a large D: drive. SQL Server is installed on the C: drive and all of the databases are installed on the D: drive.
I got an error message when I was running a bunch of large queries regarding a lack of disk space. When I looked, the C: drive was showing 0 space available. I closed one query window and saw that it now had 40 KB of space. When I closed all of my windows it went back to 2GB of free space on the C: drive. So obviously these queries are using temporary space on the C: drive.
Would it be tempdb? If all of the other databases, including the system databases, are on the D: drive would tempdb use the C: drive? If not tempdb, what is it? Is there anyway I can change whatever it is to use the D: drive instead?
I would also really appreciate it if anyone can point me to documentation regarding the above, and/or documentation recommending space needed on the C: drive for operating SQL Server 2008 (or 2005). I may need to take this further up the chain and get a larger C: drive (NOT an simple prospect).
Thank you!
- Cindy
December 12, 2008 at 10:17 am
use sp_helpfile to determine the filenames and locations of database and log files.
May be the tempdb is on your C:
or logfiles from your production database are kept on C:
To move your tempdb, use this..
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go
refer to the following links...
http://www.ss64.com/sql/database_a.html
http://msdn.microsoft.com/en-us/library/aa275464(SQL.80).aspx
December 12, 2008 at 10:19 am
i just copied/pasted that code.
use filepath of your choice
December 12, 2008 at 10:21 am
Thanks, Pradeep.
sp_helpfile was helpful (go figure!). I used it to examine tempdb and found out that it is not on the C: drive, so that can't be the issue.
Any other suggestions?
- Cindy
December 12, 2008 at 10:24 am
Where did you get the errors? From a client? It might be an issue there.
SQL doesn't report space issues, unless you add enough data that you run out of space in a database, and it is not set to autogrow. If you run a query that needs lots of tempdb space, and it's not set to autogrow, it can report space issues.
December 12, 2008 at 10:27 am
I had RDC'd to the server itself and was running some ad-hoc queries in SQL Server Management Studio.
And the exact error message was:
"An error occurred while executing batch. Error message is: There is not enough space on the disk."
- Cindy
December 12, 2008 at 10:30 am
Before canceling the query have you looked around the to locate the directory which is filling the C drive?
December 12, 2008 at 10:36 am
OK, there was a large temp file in C:\Documents and Settings\myusername\Local Settings\Temp\15. When I closed the query window that went down to 0.
Any idea where that comes from and how I can change that to another drive?
This is all very helpful. Thank you.
- Cindy
December 12, 2008 at 10:44 am
The temp file is probably holding the resultset that it wants to return to you, however is waiting for the entire query to complete.
You could change the environment variables to change the temp location to another location.
Right click My computer --> properties --> advanced -->environment variables
Only change the variables for your username. You made need to log off and back on again for the change to take effect.
December 12, 2008 at 10:53 am
Sweet! That did the trick. Thank you.
I need to find some documentation of this issue for our server admin folks. Specifically documentation regarding this as a database issue, not documentation of how to change Windows environment variables. Could anyone point the way, or suggest the proper search terms to use? I already spent a long time this morning searching and never found anything about this, so I'm obviously searching for the wrong thing.
- Cindy
December 12, 2008 at 11:53 am
Sorry, I haven't found anything that speaks to this as an issue, and couldn't tell you what to try as regards finding this on the web.
December 12, 2008 at 11:55 am
Well, thank you very much for your help.
- Cindy
December 12, 2008 at 12:09 pm
This brings up a best practice question...should you be logging on to the DB server and using SSMS there for routine queries, or using SSMS on a remote client machine?
The Redneck DBA
December 12, 2008 at 12:14 pm
Good question. I work from home, connecting to my company through a VPN, and there is a SIGNIFICANT speed difference when working on my laptop with the client vs. working on the server. Usually, if it's a quickie thing I'll work from the client on my laptop but in this case I knew I would be getting around 25 MB of data returned in these queries and I didn't want to have to pass that over the VPN, so it made much more sense to work on it on the server.
The server is kind of a development / sandbox for a data warehouse, not a truly production server, although there is a production Analysis Cube on the server. But the databases aren't production databases.
I think if it were a truly production environment we would need to put some standards in place around this type of thing. But in a sandbox environment we're a lot more relaxed
- Cindy
December 12, 2008 at 12:18 pm
In a sandbox, I don't think there's a big problem, but I might suggest setting up a desktop instead to handle this and RDP to that. If something happens on the server, you can impact others, or that's the idea of a server.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply