SQL Server & Disk Space

Question

I created a database on a local SQL server that we use for development. I created the log and data files on a second hard drive (E:\MSSQL\DATA). I am using this database to do some speed tests so I created a lot of data (7 Million rows). I started running some pretty intensive queries and to get some test data I ran an update statement that updated all 7 million rows and now it has taken up all of the space on my C:\, which I don't understand since I put the data files on the E:\.

Is there some files on the C:\ that would be growing based on me running queries on this other database, if so how do I stop it? I am doing with this database but I need to get my C:\ back in order.

The database file group was PRIMARY, is this relevant?

score 4 · Accepted Answer · answered Sep 12 '11 at 15:56

4

It is probably the TEMPDB that is growing. Look here for some reference http://msdn.microsoft.com/en-us/library/ms190768.aspx

You should really read the following article

Capacity Planning for tempdb

answered Sep 12 '11 at 15:56

John Hartsock

234
1
6

How can I shrink this down? I skimmed the referenced article but didn't see anything that jumped out. – Dismissile Sep 12 '11 at 15:59
restarting the service will recreate it or you can shrink the individual files using the SSMS GUI. – Martin Smith Sep 12 '11 at 15:59
Looking at my tempdb it appears to be 1GB currently :p – Dismissile Sep 12 '11 at 16:00
@Martin - What task/command would I do to shrink it in SSMS. Like I said I'm not a DBA. – Dismissile Sep 12 '11 at 16:00
locate `tempdb` in object explorer, right click then "Tasks -> Shrink" – Martin Smith Sep 12 '11 at 16:01
@Dismissile... Just start and stop SQL Server Services. This will reset the TempDB. In addition you should read the article I posted above. – John Hartsock Sep 12 '11 at 16:02
Doing that now thanks for your help. I restarted SQL and my disk space went from 4mb to 1gb. Will accept your answer in 6 mins when it allows me to ugh. – Dismissile Sep 12 '11 at 16:03

score 2 · Answer 2 · answered Sep 12 '11 at 16:04

Sounds like you may have a system database located on C: drive, most likely to be the tempdb database as the space consuming culprit.

Use the following query to validate where your data and log files currently reside:

SELECT DB_NAME([database_id])AS [Database Name], 
       [file_id], name, physical_name, type_desc, state_desc, size
FROM sys.master_files

SQL Server & Disk Space

2 Answers2