通过

SQL Server tempdb database and disk space issues

  • 项目

As SQL administrator you are responsible not only for SQL Server part but also you are part responsible for the resources used by SQL Server and also how much resources are used and why.

Who knows the activity on the server and have an idea of best practices on SQL Server can still be surprised by the tempdb space usage at a specific time.

First lets start with some basic information about the system databases and what does the tempdb database and for what type of operations is used.

SQL Server comes with the following system databases:

- master Database: Records all the system-level information for an instance of SQL Server.

- msdb Database: Is used by SQL Server Agent for scheduling alerts and jobs.

- model Database: Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.

- Resource Database: Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

- tempdb Database: Is a workspace for holding temporary objects or intermediate result sets.

For more information about each database you can click on the database name or you can access the link: System Databases

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

-       Temporary user objects that are explicitly created, such as: global o local temporary tables, temporary stored procedures, table variables, or cursors.

-       Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

-       Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.

-       Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another.

The disk space is a common issue that could be encountered by anyone. Even if you monitor the activity on the server and frequently you analyze the reports concerning the normal space usage by the databases you can still be surprised.

In case you encounter the disk space issue I recommend to read and test, the examples you have in the following article: Troubleshooting Insufficient Disk Space in tempdb

In the article you have:

  • information about  error messages that indicate insufficient disk space in the tempdb database.
  • examples that you can use to monitor the disk space and space used by queries.

Most of the issues occur because of the long running queries or reports you are executing in the SQL Server Instance and that have to use different temporary data stored in the tempdb database.

The size and physical placement of the tempdb database can affect the performance of a system. This is one of the reasons you have to first create a baseline concerning the activity on the server.

If you are a beginner I recommend to first plan the space the tempdb can use and  how you can avoid performance issues or disk space issues, for this you can take a look at the article: Capacity Planning for tempdb .

Once you have planned the space the tempdb can use I suggest you take a look at the article Optimizing tempdb Performance so you can learn how to avoid performance issue caused by lack of free disk space.

I recommend you analyze and investigate closely all the queries and procedures that are executed on the server and to verify how much space is used in all possible situations. Also it is very important the number of users that are connected so if you do all the tests on an test environment you have to take this parameter (the number of users connected) into consideration.

Comments