Error Msg 5042 : I cannot delete a data file from tempdb

• Esckay

  SSC Veteran

  Points: 254

  More actions

  March 7, 2006 at 11:23 pm

  #112220

  Hi,

  I am having problems to delete a data file from tempdb. I ran the following:

   

  use

  tempdb

  go

  dbcc

  shrinkfile (tempdev4, truncateonly)

  go

  dbcc

  shrinkfile (tempdev4, emptyfile)

  go

  alter

  database tempdb

  remove

  file tempdev4

  go

   

  The output of the above TSQL is the following:

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  File 'O:\MSSQL\Data\tempdev4.NDF' modified in sysaltfiles. Delete old file after restarting SQL Server.

  Msg 5042, Level 16, State 1, Line 1

  The file 'tempdev4' cannot be removed because it is not empty.

   

  Autogrow is disabled on the file and there are other files in the filegroup. When I try to do a dbcc shrinkfile (tempdev4, 1000) , then it completes successfully, but it doen't shrink the size at all. The file is also not in master..sysaltfiles.

  Is there a way to delete this file without restarting SQL ? and why does this happen ? I successfully removed another file from tempdb, with the same technique without any problems.

   

  Cheers,

  Esckay

• March 10, 2006 at 8:00 am

  This was removed by the editor as SPAM

• M. S. Senthil Kumar

  Valued Member

  Points: 71

  More actions

  March 15, 2006 at 3:25 am

  #626708

  Have you checked the number of users connected in the tempdb during that time.This is to check whether any body else is connected with tempdb?

  Senthil.