SQL Server 2005 | DataGinger.com

Posts Tagged ‘SQL Server 2005’

SQL Server – Msg 2555, Level 16, State 1, Line 1 – Error DBCC SHRINKFILE with EMPTYFILE option

Posted in Files and FileGroups, Shrink, tagged SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 on October 7, 2012| 4 Comments »

When tried to empty one of the tempdb data file using the below T-SQL command:

(My goal here is to REMOVE the file using ALTER DATABASE command followed by. For more information on removing data/log files, please refer to this post How to Best Remove Extra TempDB Data Files and Log Files)

--To empty 'tempdev12' data file
USE tempdb
GO
DBCC SHRINKFILE (tempdev12, EMPTYFILE); 
GO

I got the below error message:

DBCC SHRINKFILE: Page 9:44720 could not be moved because it is a work table page. Msg 2555, Level 16, State 1, Line 1 Cannot move all contents of file “tempdev12” to other places to complete the emptyfile operation. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I figured out this is due to the existing connections and existing temp tables. To avoid this we can either

Kill all the existing connections to the database OR
Re-start the SQL Server

In may case it is not a critical production instance, so I just restarted the server and it worked.

For more information on how to use SHRINKFILE command please visit my previous post Transaction Log is Full – How to Shrink the Transaction Log

Read Full Post »

SQL Server – Can a Database Have More Than One .mdf File?

Posted in Files and FileGroups, tagged SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 on November 23, 2011| 2 Comments »

Does a database allows to create multiple mdf files? Let’s check here

Until recently I am on the assumption that a database can only have one data file with the *.mdf extension.

While checking the tempDB configuration on one of our production servers, I noticed tempDB having multiple data files with .mdf extension as shown in the screenshot below.

However, I expected the tempDB to be having only one .mdf file and multiple .ndf files, which is the way I have been seeing all these years.

To test it by myself (which I always prefer) I tried creating more than one data file with *.mdf extension on a database, and ofcourse YES, it accepted without any issues.

Reason: The primary data file shouldn’t necessarily have to be a .mdf extension

A primary data file is the data file associated when first created the database, and YES this can only be one

Does the .mdf/.ndf/.ldf file extensions really matter?

NO, SQL Server does not enforce the .mdf, .ndf, and .ldf file name extensions, these extensions help us identify the different kinds of files and their use.

Microsoft’s recommended file name extension for primary data files is .mdf. Similarly for secondary data files is .ndf and for transaction log file is .ldf. However, I can have any extension (something like .xyz) for any file (primary/secondary/log). Please refer to the screenshot below for an example of database created with different FileName extensions.

So, how does SQL Server know the file we are adding is a data file or log file ?

If using GUI, in the File Type we mention either “Rows Data” or “Log”
If using T-SQL, ALTER DATABASE DbName ADD FILE… or ALTER DATABASE DbName ADD LOG FILE…

In Conclusion: Yes, we can create multiple data files with .mdf extension for a database either on an already existing filegroup or by creating a new filegroup. Generally speaking secondary files have a .ndf extension (not necessarily though!), but you can give it whatever extension you want, and it will work correctly.

Read Full Post »

« Newer Posts