Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have installed Microsoft SQL Server Express 2012 on a desktop under Windows 8.1 to teach myself SQL (and eventually work with some data). I am also using SQL Server Management Studio.

SQL Server is constantly hitting my disk, for no apparent reason. There's so much disk activity that I am not comfortable running the program because I don't want to wear out my drive.

Here's what I know for sure:

1.The disk activity stops if I stop SQL Server from the command line (net stop mssqlserver).

2.The disk activity does NOT resume if I restart SQL Server from the command line (net start mssqlserver) and I am not in SQL Server Management Studio.

3.The disk activity does NOT resume if I open SQL Server Management Studio, connect to the server (which is the desktop), and do nothing else.

4.The disk activity begins as soon as I open a query window (with the "New Query") button, before I do anything else. And if I close the window without doing anything, the disk activity keeps going.

5.In Activity Monitor in SSMS, I see one process running: a SELECT on tempdb. But I didn't run a single query.

Any idea what might be going on? I don't want to be putting constant, pointless wear-and-tear on my hard drive just to run SQL Server Express while doing nothing.

UPDATE: Apart from "System Databases" shown in SSMS, I have only three databases. One is the AdventureWorks2012 database from Microsoft for training. Another is a database with almost no data that is also based on a book. The third is two text files that I loaded into a database.

I understand why this info is ordinarily relevant, and I understand that SQL Server is active whenever there's database activity (update, select, insert).

What I'm trying to say is that SQL Server is hitting my disk constantly when I am doing NOTHING. I haven't run a single query. All I've done is fire up SQL Server, fire up SSMS, and open a new query window. Constant disk activity then occurs.

Activity monitor shows some "Recent Expensive Queries" that must be system-initiated, because I didn't initiate any, but I can't post a screenshot because I'm new.

My hope is that there's some kind of global setting that I can change that relates to SQL Server's background processes. Given that I am not running any queries at all, I don't think that the constant disk activity is expected behavior.

share|improve this question

closed as off-topic by xeon, joeqwerty, Ward, Falcon Momot, mdpc Jan 21 at 3:23

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions must be relevant to professional system administration. Server Fault is dedicated to professional system and network administrators. End user and enthusiast questions are off-topic (contact your system administrator or hire a professional to help you out). Please see the Help Center for more information." – xeon, joeqwerty, Ward, Falcon Momot, mdpc
If this question can be reworded to fit the rules in the help center, please edit the question.

1  
You forgot to provide the most important information: how many databases do you have, and how are they configured? –  Massimo Jan 18 at 5:09
    
run Resource Monitor and look which file the SQL server accesses: techrepublic.com/blog/the-enterprise-cloud/… –  magicandre1981 Jan 18 at 7:01

1 Answer 1

Without knowing how many databases you have, etc., it's hard for me to be specific, but I will say that SQL Server is both memory and disk intensive.

Memory: SQL likes to load as much data as it can into memory for better performance, because memory is faster than hitting your disks even more than it already does.

Disk IO: SQL is pretty much constantly writing to the transaction logs. A list of things logged includes:

  • The start and end of each transaction.
  • Every data modification (insert, update, or delete). This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.
  • Every extent and page allocation or deallocation.
  • Creating or dropping a table or index.

Brent Ozar has a really good article on how SQL Server stores data here.

As for SQL Server Management Studio, when you connect to a SQL Server through SSMS it does query the server to generate the list of databases, etc.

In short, without more information, I'm not surprised that SQL is using your hard drive. To get more information about what's running, sp_WhoIsActive is awesome. If there really is something odd going on, that might help you find it.

share|improve this answer
    
Thanks for the comment. I updated my question. I have virtually no databases, and the constant disk activity begins simply when I open up a query window, without even executing a query. I do not believe that this can be expected behavior. Any other thoughts?\ –  user3208862 Jan 19 at 0:07
    
"Recent expensive queries" hits a lot of DMVs, but they're not expensive in themselves. You haven't turned on trace or anything like that, have you? Also, you might want to exempt SQL files and directories from antivirus scanning as described here: support.microsoft.com/kb/309422 –  Katherine Villyard Jan 19 at 1:16
    
I have a vanilla installation; if "trace" is on by default, then it's on, but I haven't changed any defaults. The disk activity is definitely not virus scanning. –  user3208862 Jan 20 at 20:08

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