Skip to content

Instantly share code, notes, and snippets.

@jonsagara
Created January 18, 2014 21:00
Show Gist options
  • Save jonsagara/8496385 to your computer and use it in GitHub Desktop.
Save jonsagara/8496385 to your computer and use it in GitHub Desktop.
Add more files to tempdb, make them the same size, and use a fixed size for autogrowth. This is especially important when READ_COMMITTED_SNAPSHOT, which causes SQL Server to the sh*t out of tempdb. Be sure to restart SQL Server after making this configuration change.
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 102400KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'D:\Path\To\Your\DATA\tempdb2.mdf' , SIZE = 102400KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'D:\Path\To\Your\DATA\tempdb3.mdf' , SIZE = 102400KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'D:\Path\To\Your\DATA\tempdb4.mdf' , SIZE = 102400KB , FILEGROWTH = 102400KB )
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment