SQL Server – How to Best Remove Extra Temp. DB Data Files and Log Files. One of the Microsoft’s recommendation for optimizing the temp. DB performance is to make each tempdb data file the same size. Today, on one of our servers, I noticed there are 1. Sql Server 2005 Add Tempdb Files MinecraftMy target here is to configure tempdb with 8 equi sized data files and one log file. So, in this case I have to delete those 5 extra data files and re- size the remaining 8 files equally. To achieve this I followed the below simple three step process (The same procedure can be applied to any user databases as well)- - Step. First empty the data file. USE tempdb. DBCC SHRINKFILE (tempdev. EMPTYFILE); - - to empty "tempdev. Data file won’t gets deleted unless it is empty. So before going to Step. As a lot of line-of-business applications are being built with SQL Server 2005 Express Edition as their backend database, we need to make sure that we backup the. SQL Server Technical Article. Writers: Wei Xiao, Matt Hink, Mirek, Sunil Agarwal. Technical Reviewer: Wei Xiao. Applies To: SQL Server 2005. Summary: This. Step. 1 above. Note: If encountered this error Msg 2. Level 1. 6, State 1, Line 1 while performing the above operation, please refer to this post –> Msg 2. Level 1. 6, State 1, Line 1 – Error DBCC SHRINKFILE with EMPTYFILE option- -Step. Remove that extra data file from the database. ALTER DATABASE tempdb. REMOVE FILE tempdev. I repeated the above two steps to delete the other files as well. SQL Server 2016 is loaded with a lot of new enhancements and features. One of them is the default tempdb configuration chosen during setup. A properly configured. Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Enterprise Evaluation, Microsoft SQL Server 2008. Existing Recommendations. There are several different published suggestions for calculating the number of files used by tempdb for the best performance. Below is the screenshot after deleting the extra files- -Step. Re- size the data files to target file size.
Use ALTER DATABASE if the target file size is greater than the current file size. ALTER DATABASE [tempdb]. MODIFY FILE ( NAME = N'tempdev', SIZE = 3. KB ) - -grow to 3. MB. - -Use DBCC SHRINKFILE if the target file size is less than the current file size. DBCC SHRINKFILE (N'tempdev' , 3. MB. In my case, the target file size is 3. MB which is greater than the current file size, so I ran the ALTER DATABASE command for all the 8 files. And below is the screenshot after re- sizing the files. Note: The same procedure can be used for removing extra log files as well. Make sure the log file you are trying to empty do not have any active portion of the log. Run DBCC LOGINFO on your specified database, taking note of the File. Id and the Status indicator (2 showing that it is an active VLF).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
October 2016
Categories |