Data, Log and tempdb File Placement: Checking Where Everything Lives

The next full drive is already listed in your SQL Server catalog. Reviewing file placement shows where data, logs, and tempdb compete for capacity. Start with the actual files and the storage behind their paths.

Match File Placement to the Storage You Actually Have

Data files store pages and indexes. Transaction log files record changes needed for recovery. Their I/O patterns differ, but modern storage does not obey every old rule about dedicated physical disks.

Ask the storage team what the volumes actually represent. Two drive letters on the same shared array are not physical separation.

I inspect service-level goals and capacity before recommending paths. The right layout depends on throughput, latency, failure domains, backup staging, and operational ownership. A diagram of drive letters alone is too shallow.

What happens if one volume fills or becomes unavailable? That answer matters more than whether a path looks tidy.

Read the Instance Default Data and Log Paths

SQL Server setup stores default locations for new data and log files. Review them before the first application database is created. A default that points to a system volume can create a capacity surprise later.

Inspect tempdb and backup locations too. They can compete with data files despite careful application file placement.

The query below shows default data and log paths reported by the instance. Verify the actual folder and volume permissions on Windows. A path property does not prove the directory exists or has enough space.

SELECT SERVERPROPERTY(N'InstanceDefaultDataPath') AS default_data_path,
       SERVERPROPERTY(N'InstanceDefaultLogPath') AS default_log_path;

List Every Database File and Its Growth Setting

sys.master_files lists physical paths and allocated sizes across databases. Use it to find files on unintended volumes, identify growth settings, and plan migrations. Include system databases and tempdb in the review.

A database can have several log files. Read type_desc from the catalog instead of relying on an .ldf extension. The growth column counts 8 KB pages unless is_percent_growth is 1, so a value of 8192 means 64 MB.

I group the results by volume with infrastructure monitoring data. Allocated size is not the same as used space, and a volume hosts more than SQL Server files. Keep the actual free-space measure outside this query.

The result maps SQL allocations. Add volume free space to complete the file placement review.

SELECT DB_NAME(database_id) AS database_name,
       name AS logical_name,
       type_desc,
       physical_name,
       size * 8.0 / 1024 AS allocated_mb,
       growth,
       is_percent_growth
FROM sys.master_files
ORDER BY physical_name;

Split Data and Log Volumes Only for a Reason

Separate storage can help data and logs. The architecture must actually separate failure risk or I/O contention. On pooled or virtualized storage, different letters can still share the same devices and bottlenecks.

Ask for the storage design and observed latency before prescribing a split. If the platform uses independent volumes with different protection, document those choices.

I have seen teams spend days moving files between letters that mapped to the same storage pool. The map looked improved. The workload did not notice.

Start with a measured problem or a clear recovery requirement. Then choose placement that addresses it. A rule inherited from spinning disks should not overrule current evidence.

Plan File Placement With Growth Headroom

Plan room for data growth, log spikes, index maintenance, backups, and emergency diagnostics. A log volume needs enough headroom for long transactions and failed log backups until the alert is handled. A data volume can need temporary room during maintenance. Set file growth increments deliberately and monitor the host volume, not only database allocation.

I ask who receives the low-space alert and how much lead time they need. A perfectly chosen path with no capacity process still fails. Keep a documented owner for volume expansion. Storage requests made after the volume is full are both urgent and avoidable.

Move a User Data File Without Guesswork

A file move needs a plan for availability, backup, exact logical name, target permissions, and rollback. Change the catalog path under the supported procedure for that database. Take it offline or stop the service as required.

Move the file with Windows tools, then bring it online and verify. System databases and tempdb have different procedures. Follow the supported method for the specific file and deployment.

One move script can't fit every database role. A copy to the new volume without a verified startup path can leave the database unavailable. Test the procedure on a restore or nonproduction copy and keep a current backup. Record the old path and exact file name before touching the filesystem.

Test File Placement Against a Real Restore

The file layout should be recoverable on the target server or have a documented WITH MOVE restore plan. A disaster recovery host can use different drive letters or mount points. Test a restore with the intended locations. Backup files themselves need capacity and access separate from database files where the architecture requires it.

I include path assumptions in the recovery runbook. Check the recovery destination before an outage. A missing target volume shouldn't be discovered by the first failed restore command.

Keep volume names, owners, and required capacity current. The best layout is one you can recreate under pressure.

Give the Service Account the Right Folders

Grant the SQL Server service account the permissions needed on file directories. Avoid broad access for convenience. Monitor antivirus exclusions and backup agents according to supported guidance.

Log growth, file initialization behavior, and storage snapshots can affect operations. Coordinate those choices with infrastructure teams rather than changing only the SQL configuration.

I check that monitoring labels volumes by purpose, not just letter. A DBA can then tell whether an alert concerns data, log, tempdb, or backups. Clear labels reduce response time. A small amount of documentation prevents the next person from treating every full disk as the same problem.

Recheck the Layout When the Workload Changes

A new reporting workload, retention rule, or availability design can change the best placement. Review file paths and growth trends after major application releases and storage migrations. Keep the default paths aligned with the current plan so new databases do not quietly return to the old volume.

Which file will grow first under the next large load? Your layout and alerts should answer that. A path is a decision about performance, capacity, and recovery. Make it visible, testable, and easy for the next DBA to maintain.

Run the volume query with the required server-state permission. Several files on one volume repeat its free-space value, so don't sum those repeated values. Group data and log paths by volume to spot shared capacity. Check user databases on the Windows system drive first, then compare tempdb files for equal sizes and sensible growth increments.

SELECT DB_NAME(f.database_id) AS DatabaseName, f.type_desc, f.physical_name,
       v.volume_mount_point, v.total_bytes, v.available_bytes
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id,f.file_id) AS v;
SELECT name, physical_name, size * 8.0 / 1024 AS AllocatedMB, growth, is_percent_growth
FROM tempdb.sys.database_files
ORDER BY type, file_id;

For a planned user data-file move, replace every name and path below with verified values. Take a current backup first. Create the target folder and grant the service account access.

Change the catalog path, take the database offline, move the existing file with Windows tools, then bring the database online. Don't run the final command before the move finishes.

I rehearsed this on a throwaway database. MODIFY FILE refused a target folder that didn't exist yet, with error 5121. SET ONLINE before the file was moved failed with error 5120, and the database stayed offline until the path was corrected.

ALTER DATABASE [YourDatabase] MODIFY FILE
(NAME = N'YourLogicalDataFile', FILENAME = N'D:\SqlData\YourDatabase.mdf');
ALTER DATABASE [YourDatabase] SET OFFLINE;
-- Move the file to the verified target using Windows tools before continuing.
ALTER DATABASE [YourDatabase] SET ONLINE;

Verify the file paths after startup and confirm application reads. Move the file creating actual capacity or latency risk first.

Tempdb follows a different route. SQL Server rebuilds it at every startup, so you never take it offline or copy its files. Point each tempdb file at the new folder with MODIFY FILE, then restart the SQL Server service.

At startup, SQL Server creates fresh tempdb files in the new location. Delete the old files only after the restart succeeds. Repeat the MODIFY FILE statement for every tempdb file the first query lists, and run it only in a planned change window.

SELECT name, physical_name
FROM tempdb.sys.database_files;

ALTER DATABASE tempdb MODIFY FILE
(NAME = N'tempdev', FILENAME = N'T:\TempDB\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE
(NAME = N'templog', FILENAME = N'T:\TempDB\templog.ldf');

A tidy drive-letter diagram doesn't buy another disk. Keep the old location recorded until the new layout passes the agreed checks.

Related reading on this blog: Move TempDB for Performance: SQL in Sixty Seconds #107 and Available Free Space in Data and Log File.

File placement is not a drive-letter convention, it is a capacity and recovery decision.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.