Mini Post: DBCC SHRINKFILE Error: Msg 8985, Could not locate file in sys.database_files.

*Slides by*

Hey everyone, sliding in a post on the last day of the month of August. I already missed July :'-(

Anyways, today we will be looking at the following error(s) I got when attempting to shrink a database file via DBCC SHRINKFILE:

• Msg 8985, Level 16, State 1, Line #
  Could not locate file 'FileName_1' for database 'DatabaseName' in sys.database_files. The file either does not exist, or was dropped.
• 
  Msg 5041, Level 16, State 1, Line #
  MODIFY FILE failed. File 'FileName_1' does not exist.

These errors have to do with incorrect filenames you might be using for the DBCC SHRINKFILE parameters. And by incorrect, I mean it is possible that you, like me, were not using the logical filename.

One way to double check the logical filename would be to run the following on the database in question to get the info from sys.database_files:

SELECT 
   file_id AS FileID,
   name AS LogicalFileName, 
   size/128.0 AS FileSizeMB,
   size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
                   AS EmptySpaceMB
FROM 
   sys.database_files;

The query gives you some additional information such as file_id (which you can use instead of the logical name) as well as file size and 'empty' space to better determine your file reduction planning.

Hope this helps. :-)

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

How to Get the Last (Latest/Newest) Record in a One-to-Many Relationship/Join

Good day everyone and welcome back to SQL Something!

Today we're looking at an issue that was presented to me today: How to get the last record in a one to many relationship.

Some digging online found this original post here, which I then had to deconstruct to understand it. To that end, I made a couple test tables and then went through the query, while making small modifications to suit my needs.

Let's go through it.

Recreate a 2008 DB on a 2005 Instance

Hey everyone, just a quick note on one way to get data from a 2008 DB to a 2005 DB. (EDIT: This initial "Mini Post" turn out a bit longer than I expected...)

As most of you would know you cannot restore a backup from a newer version of SQL Server on an older instance, so this limits the options you have if you do end up having to this for whatever reason.

Your best option is to generate a script of the DB on the newer instance and execute the script on the older instance.

You can choose to either script the data as well, or you can export the data to either a text file or Excel document and import the data into the 2005 DB afterwards.