Error : “The file ‘tempdev2’ cannot be removed because it is not empty.”

I have multiple data files created for Tempdb database let’s say tempdev2. Now when I try to remove additional mdf files from filegroup, I got below issue. SQL Server says..

Msg 5042, Level 16, State 1, Line 1
The file 'tempdev2' cannot be removed because it is not empty.
Cause : The reason for such behavior is there are some activities going on that is using files created your TempDB database. The general behavior is to restart the SQL server that will create your TempDB files and reset it. But the only problem with it is it will down the other environments / databases.
Fix :
USE [tempdb]
GO
DBCC SHRINKFILE('tempdev2', EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev2]
GO

I hope this will help you!

Like this post and post comments if you like it.

Download SQL Server 2012 Developer Training Kit

The SQL Server 2012 Developer Training Kit includes technical content designed to help you learn how to develop SQL Server 2012 database and BI solutions. The SQL Server 2012 Developer Training Kit is a great resource for developers, trainers, consultants and evangelists who need to understand the key improvements introduced in SQL Server 2012 from a developer perspective…. download and read more on…http://www.microsoft.com/en-us/download/details.aspx?id=27721&WT.mc_id=rss_alldownloads_all

Microsoft Training Courses on SQL Server 2012

Microsoft has launched new courses to sharpen your knife in SQL Server. These courses will help experienced professionals to upgrade their skills with course Updating your Database Skills to Microsoft SQL Server 2012 or course Updating your Business Intelligence Skills to Microsoft SQL Server 2012.

For SQL Server these are categorized in below steps:

  • Step 1 – Course 10774 Querying Microsoft SQL Server 2012 (exam code – 461)
  • Step 2 – Course 10775 Administering Microsoft SQL Server 2012 Databases (exam code – 462)
  • Step 3 – Course 10777 Implementing a Data Warehouse with Microsoft SQL Server 2012 (exam code – 463)
  • Step 4 – MCSA: SQL Server

For more details log on to – http://www.microsoft.com/learning/en/us/training/sql-server.aspx

“Index was outside the bounds of the array” while connecting to SQL Server 2012 instance using lower version SSMS

When you try to connect SQL server 2012 instance using SQL server 2008 or 2008 R2 management studio you receive below errors…

Cause:

This is because  SQL Server does not support backward compatibility. We cannot use lower version of SSMS to connect higher version of SQL, Server which means we cannot connect to SQL Server 2012 using SQL Server 2008 or 2008 R2 SSMS.

reference :

FIX:

There are multiple workarounds with some limitations as mentioned below…

1. Install Microsoft® SQL Server® 2012 Express – you can download it from here http://www.microsoft.com/en-us/download/details.aspx?id=29062. The only limitation is supported operating systems: Windows 7, Windows Server 2008 R2, Windows Server 2008 Service Pack 2, Windows Vista Service Pack 2.

2. If you have SQL Server 2008 or 2008 R2 install you may choose for this fix –http://support.microsoft.com/kb/2459027

Like this post and reply with your comments if this works for you!