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?


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

| improve this answer | |
  • 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

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

Further reading:

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.