One common question that often arises when working with SQL Server is: What is the initial size of TempDB? While it may seem like a simple question, the answer is actually more complicated than it appears.

TempDB is a special system database in SQL Server that is recreated every time the SQL Server process is started. This means that the size of TempDB can grow based on its usage, but when the SQL process restarts, the size will revert back to its original size. However, if any modifications to the TempDB files are made by a database administrator (DBA), those modifications will be retained.

Let’s perform a small test to better understand this concept. For this example, we will be using SQL Server 2016, but please note that your results may vary if you are using an older version of SQL Server.

After restarting the SQL Server, the initial size of TempDB is shown as 72 MB. However, this is actually the current size and not the initial size. If we were to restart SQL Server again, the initial size would be 8 MB, as shown in the earlier snapshot.

So, how can we determine the size of TempDB after a restart? Fortunately, there are some queries that we can use to find both the initial size and the current size of TempDB files.

Below is a query that will show the current size of TempDB files:

SELECT name, size * 8.0 / 1024 AS 'Current Size in MB'
FROM tempdb.sys.database_files;

And here is a query that will show the initial size of TempDB files:

SELECT name, size * 8.0 / 1024 AS 'Initial Size in MB'
FROM master.sys.sysaltfiles
WHERE dbid = 2;

By using these queries, you can easily identify the size of TempDB after a restart. This simple yet powerful solution can help you better manage your TempDB and optimize its performance.

Understanding the initial size of TempDB is crucial for SQL Server administrators and developers alike. By knowing how TempDB behaves and how to determine its size, you can ensure that your SQL Server environment is running efficiently and effectively.