How to: Delete Data or Log Files from a Database

Deleting Files

Deleting a data or transaction log file removes the file from the database. You cannot remove a file from the database unless the file has no existing data or transaction log information; the file must be completely empty before it can be removed. To empty a data file by moving data from the data file to other files in the same filegroup, use the DBCC SHRINKFILE statement and specify the EMPTYFILE clause. Because the Database Engine no longer allows data to be placed on the file after the shrink operation, the empty file can be removed by using the ALTER DATABASE statement or SQL Server Management Studio.

You cannot move transaction log data from one log file to another to empty a transaction log file. To remove inactive transactions from a transaction log file, the transaction log must be truncated or backed up. When the transaction log file no longer contains any active or inactive transactions, the log file can be removed from the database. For more information, see Managing the Transaction Log.

Steps to Remove Data File

Step 1

-- Empty the data file
DBCC SHRINKFILE (Test1data, EMPTYFILE)

Step 2

-- Backup the Database / Transaction Log so the file can be cleared out completely
BACKUP DATABASE AdventureWorks2008R2 TO DISK = '\\backupshare\AdventureWorks2008R2.bak'

or

-- Backup the Database / Transaction Log so the file can be cleared out completely
BACKUP LOG AdventureWorks2008R2 TO DISK = '\\backupshare\LOG_AdventureWorks2008R2_0600.bak'

Step 3

-- Remove the data file from the database
ALTER DATABASE AdventureWorks2008R2 REMOVE FILE Test1data

References:

How to: Delete Data or Log Files from a Database
How to: Delete Data or Log Files from a Database (SQL Server 2005)
Adding and Deleting Data and Transaction Log Files

TEMPDB Should Always Have One Data File Per Processor Core

According to Microsoft’s Storage Top 10 Best Practices, it is recommended to have .25 to 1 data files (per filegroup) for each CPU on the host server (dual core counts as 2 CPUs). This is especially true for TEMPDB where the recommendation is 1 data file per CPU.

It’s also recommended to move TEMPDB to adequate storage and pre-size after installing SQL Server. Performance may benefit if TEMPDB is placed on RAID 1+0(dependent on TEMPDB usage). The data files should be of eaqual size as SQL Server uses a proportional fill algorithm that favors allocations in files with more space.

On the another hand, sometimes the recommendation might not always correct. Paul shared a very good example in his blog post,

I heard just last week of a customer who’s tempdb workload was so high that they had to use 64 tempdb data files on a system with 32 processor cores – and that was the only way for them to alleviate contention. Does this mean it’s a best practice? Absolutely not!

So, why is one-to-one not always a good idea? Too many tempdb data files can cause performance problems for another reason. If you have a workload that uses query plan operators that require lots of memory (e.g. sorts), the odds are that there won’t be enough memory on the server to accomodate the operation, and it will spill out to tempdb. If there are too many tempdb data files, then the writing out of the temporarily-spilled data can be really slowed down while the allocation system does round-robin allocation. The same thing can happen with very large temp tables in tempdb too.

In summary,

  • It is recommended to have .25 to 1 data files (per filegroup) for each CPU on the host server (dual core counts as 2 CPUs).
  • Sometime too many data files may also cause performance problems for another reason