Archive for the ‘Files and FileGroups’ Category

Most of the DBAs at some point of time in their career should have faced at-least one of the below situation(s):

1. The disk/SAN where the database files are located is going to be replaced
2. Disk is full and no more free space available
3. Data files and/or log files are not located on standard drives

There can be even more secnarios like the above where we may need to move the database files from current location to a new location. Starting SQL Server 2005 this can be simply achieved by using ALTER DATABASE T-SQL command

Let us take the below scenario for step-by-step Demo:

Database: AdventureWorks2012
Current Files Location: C:\Disk1
New\Target Files Location: C:\Disk2

Step 1: Get the current database files Logical Name and Physical Location

USE master
SELECT name AS LogicalFileName, physical_name AS FileLocation
, state_desc AS Status 
FROM sys.master_files 
WHERE database_id = DB_ID('AdventureWorks2012');

Step 2: Take the Database offline

USE master


Note: In the above T-SQL query, I used WITH ROLLBACK IMMEDIATE option. Please be careful when using this option as it rolls back those incomplete transactions immediately. You may exclude this option, but have to wait till all the transactions are committed to take the database offline.

Step 3: Move the database files physically in the Windows OS to the new location



Step 4: Use ALTER DATABASE to modify the FILENAME to new location for every file moved

Only one file can be moved at a time using ALTER DATABASE.

USE master
ALTER DATABASE AdventureWorks2012
( NAME = AdventureWorks2012_Data, 
FILENAME = 'C:\Disk2\AdventureWorks2012_Data.mdf'); -- New file path

USE master
ALTER DATABASE AdventureWorks2012 
( NAME = AdventureWorks2012_Log, 
FILENAME = 'C:\Disk2\AdventureWorks2012_log.ldf'); -- New file path

Step 5: Set the database ONLINE

USE master

Step 6: Now, verify the database files Physical location

USE master
SELECT name AS FileName, physical_name AS CurrentFileLocation, state_desc AS Status 
FROM sys.master_files 
WHERE database_id = DB_ID('AdventureWorks2012');

Note: Same method can be used for moving files for any system or user defined database except for Resource database files


Read Full Post »

From a recent conversation with a customer, I noticed even some of the experienced DBAs are not very clear about the terms “unallocated space” and “unused space” and by doing a quick Bing search, found there are many SQL friends out there trying to get a clear correlation between these SQL Server terms: unallocated space, unused space, and reserved.

Today, let us try to get a clear picture (infact, a picture!) of these terms using sp_spaceused system stored procedure and at the same time understanding results for database size information.

Execute the below query on AdventureWorks2012 sample database

USE [AdventureWorks2012]
EXEC sp_spaceused



Now, let us understand the above result sets, lets check by the column names

  • database_size: database size (data files + log files) = 205.75 MB
  • unallocated space: space that is not reserved for use by database objects (Space Available) = 14.95 MB
  • reserved: space that is reserved for use by database objects = 190.05 MB
  • data: total space used by data = 97016 KB/1024 = 94.74 MB
  • index_size: total space used by indexes = 88048 KB/1024 = 85.99 MB
  • unused: portion of the reserved space, which is not yet used = 9544 KB/1024 = 9.32 MB
  • used:  let us coin this word to simplify things. say used = data + index_size = 94.74 + 85.99 = 180.73 MB

We now know what these columns are and the numbers they are returning. But, here is the real confusion part: How are they correlated with each other?

To answer this, below is the simple formulae I came up with to better interpret the results.  I haven’t found this simple explanation clearly anywhere else on SQL space (trust me, you won’t)

(color coded to read easy)

used = data + index_size
reserved = used unused
database_size = reserved + unallocated space + log space

To cross verify, we can substitute the result set we got from AdventureWorks2012 database in the above formula and confirm by checking the log file size. Alternatevely, we can calculate the log file size, which is not returned from the ‘sp_spaceused’ result set.

180.73 94.74 + 85.99 –>TRUE
190.05 180.43 9.32 –> TRUE
205.75 = 190.05 14.95 + log space

Hence log space = 205.75(190.05+14.95) = 0.75 MB (which is right!)

And, below is my small art in the same color coding as above to illustrate in a picture format. Of course, who doesn’t love pictures, and I am no exception -:)

Suresh Raavi - Copy Right

Read Full Post »

In SQL Serve 2012, we can get the default data file and log file locations using a simple T-SQL query as below:


Sample Output:


These parameters INSTANCEDEFAULTDATAPATH & INSTANCEDEFAULTLOGPATH are new in SQL Server 2012 and are not documented yet. Usually undocumented features like these aren’t tested rigorously which is why Microsoft says they are not intended to be used by customers.

Read Full Post »

One of the Microsoft’s recommendation for optimizing the tempDB performance is to make each tempdb data file the same size.

Today, on one of our servers, I noticed there are 13 data files with different sizes as shown in the below screenshot:


My target here is to configure tempdb with 8 equi sized data files and one log file. So, in this case I have to delete those 5 extra data files and re-size the remaining 8 files equally.

To achieve this I followed the below simple three step process (The same procedure can be applied to any user databases as well)

-- Step1: First empty the data file
USE tempdb
DBCC SHRINKFILE (tempdev12, EMPTYFILE); -- to empty "tempdev12" data file

Data file won’t gets deleted unless it is empty. So before going to Step2 to delete the data file, we should first empty the data file which can be done from the Step1 above.

Note: If encountered this error Msg 2555, Level 16, State 1, Line 1 while performing the above operation, please refer to this post –> Msg 2555, Level 16, State 1, Line 1 – Error DBCC SHRINKFILE with EMPTYFILE option

--Step2: Remove that extra data file from the database
REMOVE FILE tempdev12; --to delete "tempdev12" data file

I repeated the above two steps to delete the other files as well. Below is the screenshot after deleting the extra files


--Step3: Re-size the data files to target file size 
-- Use ALTER DATABASE if the target file size is greater than the current file size
USE [master]
MODIFY FILE ( NAME = N'tempdev', SIZE = 3072000KB ) --grow to 3000 MB
--Use DBCC SHRINKFILE if the target file size is less than the current file size
USE [tempdb]
DBCC SHRINKFILE (N'tempdev' , 3000) --shrink to 3000 MB

In my case, the target file size is 3000 MB which is greater than the current file size, so I ran the ALTER DATABASE command for all the 8 files. And below is the screenshot after re-sizing the files


Note: The same procedure can be used for removing extra log files as well. Make sure the log file you are trying to empty do not have any active portion of the log. Run DBCC LOGINFO on your specified database, taking note of the FileId and the Status indicator (2 showing that it is an active VLF)

Read Full Post »

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

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 »

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.

Can a Database have more than one mdf file 1

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.

Can a Database have more than one mdf file 2

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”

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 »