Mini Post: The Visual Studio Component Cache Is Out Of Date

Good day all and welcome back to SQL Something!

Quick post: "The Visual Studio component cache is out of date. Please restart Visual Studio.". Got this error when I tried to run a query on a DB in SQL Server Management Studio 2016. There is discussion on possible causes here, but that's about it.

There are a couple fixes for it.

Choose one of the following:

Solution 1: Restart Management Studio.

• Close SQL Server Management Studio.
• This should automatically clear temp files related to Management Studio.
• Optional: Open Task Manager and stop all Visual Studio tasks.
• Start SQL Server Management Studio.
• Attempt query again.

Solution 2: Delete the Management Studio temp folder.

• Close SQL Server Management Studio.
• Browse to C:\Users\YourUserName\AppData\Local\Temp\
• Browse to SSMS folder.
• Delete folder and contents.
• Start SQL Server Management Studio.
• Attempt query again.

Solution 3: Lastly, you can try running the Disk Cleanup Utility.

• Close SQL Server Management Studio.
• Search for Disk Cleanup Utility.
• Open Disk Cleanup Utility.
• Select Temporary Files only.
• Click OK, Delete files.
• Start SQL Server Management Studio.
• Attempt query again.

One of the above should work for you. :-)

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. :-)

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. :-)

Mini Post: Find All Tables That Have A Given Column

Hey everyone! Welcome back to SQL Something!

Really quick post today. Today we look at finding a table(s) that a column belongs to. Had to find a solution to do this recently and thought it would helpful to share.

The answers we seek can be found in the following SQL Server system views:

• sys.columns
• sys.tables

We will retrieve the related column names from sys.columns and the table names from sys.tables.

Putting it together we get the following:

SELECT       c.name AS ColumnName, t.name AS TableName
FROM          sys.columns c
                     JOIN sys.tables t
                     ON c.object_id = t.object_id
WHERE       c.name LIKE '%YourColumn%'
ORDER BY ColumnName, TableName;

The above will list all tables that have a column name like the given column name ('YourColumn').

And there we go. :-)

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. :-)

Original Reference:  https://stackoverflow.com/questions/26293085/find-all-table-names-with-column-name

Syncing Logins/Users Across SQL Server Instances (For Availability Groups and Mirroring Failovers)

Hey Everyone! Guess who's still around!

Welcome back to SQL Something (with a post dedicated to my friend Greg who guilt-ed me into finally starting back posts). In all seriousness, it's really been far too long.

Today's post briefly touches on SQL Server Always On Availability Groups in SQL Server 2016. You can find a pretty good tutorial on how to set up Always On Availability Groups in an Azure VM via the links here and here. They are pretty thorough (great job team Microsoft!), but they do have a couple very, very small errors/typos that I might point out in a subsequent post.

Today, however, we are merely focusing on a simple login/user issue in order to help streamline failing over to your secondary. The fix is about syncing logins between availability groups. This fix can also be applied to Mirroring (a deprecated feature after 2016)