Bug in SQL Server 2012 Management Studio

Today I noticed a bug in SQL Server 2012 Management Studio. Shows information about file access that points to the now files being used on the fly after executing a “ALTER DATABASE XXX MODIFY FILE…” statement Let me explain how to reproduce it. First, run:

USE tempdb;

go

exec sp_helpfile

The Output looks like this on my laptop:

name fileid filename filegroup size maxsize growth usage

tempdev 1 C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATAtempdb.mdf PRIMARY 8192 KB Unlimited 10% data only

templog 2 C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATAtemplog.ldf NULL 512 KB Unlimited 10% log only

Then run the following commands:

USE MASTER;

GO

select * into #tmp from sys.objects

And verify usage of tempdb files in SSMS Activity Monitor

Run:

USE master;

GO

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘C:temptempdb.mdf’);

GO

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ‘C:temptemplog.ldf’);

GO

Your output should look like this :

The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.

The file “templog” has been modified in the system catalog. The new path will be used the next time the database is started.

then run

USE tempdb;

GO

EXEC sp_helpfile

Your output should look like this :

name fileid filename filegroup size maxsize growth usage

tempdev 1 C:temptempdb.mdf PRIMARY 2547520 KB Unlimited 10% data only

templog 2 C:temptemplog.ldf NULL 427392 KB Unlimited 10% log only

Run the following command

select * into #tmp2 from sys.objects

And verify usage of data file in Activity Monitor

Now it looks like we are using the new file location already, without restarting SQL Server. But in fact if we look in the new folder, the files are not there. And Windows Resource Manager confirms that the old files are still in use.