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)

Azure SQL Database Firewall Issue (Connectivity Issue)

Hey everyone! Yes, I'm still around and yes, this is still SQL Something!

I'm sounding like a broken record now but, as usual, life has been pretty busy. Definitely going to try to finish the year right however, by having at least one post for each of the last couple of months.

Now! Let's get on with it.

Today we are looking at connecting to a Azure SQL Database. In a nutshell, an Azure SQL Database is a DB that exists on a logical DB server, not an actual DB Server VM. It takes away the hassle of having to do server related admin tasks and allows you to focus on the DB itself and the data it contains.

For the purpose of this exercise, let's assume we created a Azure SQL DB and when we attempted to connect to it via Management Studio, we received the following:

Fig. 1: Danger Will Robinson!

Now, we can take the following steps:

• Ensure that you are using the correct server name, username and password.

If the above doesn't work, we will have to verify our Azure SQL Database Firewall rules.

• Log on to portal.azure.com with your valid user.
• Navigate to your azure SQL Databases.
• Click the database in question.
• Select 'Set Server Firewall'.

Fig. 2: Firewall awesomeness.

• Click add Client IP.

Fig. 3: Add yo IP

• Click 'Save'.

Now we can retry our connection.

And that's all there is to it. :-)

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: SQL Server Agent Job History

Hey all, welcome back to SQL Something. Man, has it been a while. Too long.

Unfortunate to say however, this post will not be particularly epic to make up for the long absence. Like, not epic at all. But, you know what? Let's go into it. If it helps one person, then that's plenty. :-)

Today we are looking at SQL Server Agent Job history.

A SQL Server Job allows us to automate a process to run at particular times, either as a one time event or repeatedly for some specified duration (or indefinitely). Setting up jobs for performing backups is an example of a great way to use jobs.

A job's history can show us a number of things. It can show us if the job ran successfully and if not, it can give us an indicator as to why. It can also show us some useful info such as the creds used to invoke the job, as well as the duration of time taken for the job to run (this last I find pretty helpful).

SQL Server Fixed Roles And Related Stored Procedures

Good day everyone and welcome back to SQL Something!

Do you like Roles? Do you want to know a little more about SQL Server's built in Roles and what they do? Do you want to know some nifty built in sprocs that also give some extra info about the Roles?

Then this is a post for you!


Firstly let us look briefly at SQL Server's nine (9) built in Roles:

• SysAdmin - This is the big guy. Members of this Role have all the permissions across the instance. A SysAdmin is specified during installation.
• ServerAdmin - Members of this Role are allowed to perform instance configuration tasks and can also stop an instance.
• SecurityAdmin - Members can alter and elevate permissions for Logins. Can elevate permissions to SysAdmin.
• ProcessAdmin - Members can stop instance processes.
• SetupAdmin - Members can add/remove DBs to linked servers.
• BulkAdmin - Members can bulk insert.
• DiskAdmin - Members can perform tasks involving instance related files.
• DBCreator - Members can add/drop/alter/restore databases on an instance.
• Public - The little guy. All logins are considered part of the public Role group. Not a 'real' built in role.

Next, we will examine a couple stored procedures that add to the above info.