Visual Studio Schema Compare

Question

Visual Studio Schema Compare

swade 69823

SSC Enthusiast

Points: 190
More actions
November 6, 2023 at 4:22 pm

#4322364

I use the SQL Schema Compare feature in Visual Studio. I use it to help with syncing DB projects in DevOps. But also use it to compare PROD to DEV before I refresh DEV with a copy of the PROD database. This has been working great for me for years. Just recently, the Schema Compare is not working for one of my larger databases. The database is SQL 2019. Here are the error messages.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

That looks like an Azure security error to me, not related to Schema Compare.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

swade 69823 SSC Enthusiast Points: 190 More actions · Answer 2

These databases are onsite. I'm using the same SA username and passwords for other databases on that SQL instance. I don't think it's related to security.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

Fair enough. I thought I had that error not long ago, and the reason was I had 'auto-pause' turned on for a particular Azure SQL DB and it had dozed off. That reverse engineering nonsense is a red herring, I reckon.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

swade 69823 SSC Enthusiast Points: 190 More actions · Answer 4

You said something about Azure made me think of Azure Data Studio. We don't have any PROD databases in the Cloud but I was playing around Azure Data Studio recently and I forgot it has an Extension that does SQL compare. I just ran it and it failed too but I didn't see any error messages so I looked in the Event Viewer on my machine.

Doing Schema Compares in both Azure Data Studio and Visual Studio throw this error in the Event Viewer.

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 330 seconds. Working set (KB): 261056, committed (KB): 563776, memory utilization: 46%%.

I'm not a DBA so I'm going to have to do some research. First thought is thinking our SQL server might need to be rebooted. Besides, running SQL maintenance tasks, I'm not sure how to improve SQL server performance.

frederico_fonseca SSCoach Points: 16157 More actions · Answer 5

that is a SQL issue - not VS or Schema compare issue.

basically server needs more memory or needs to have less memory allocated to SQL Server - other processes running on the server MAY also require moving to their own servers (for example if you have IIS on a SQL Server box move one of them to another server).

if you are using VS and schema compare ON the server then DON'T - VS will eat a lot of memory, and can therefore cause that issue on the SQL instance.

swade 69823 SSC Enthusiast Points: 190 More actions · Answer 6

Yeah. Our DB server is separate than our App Server (IIS). DB server has 32 GB of memory.

We use our own desktops for development with Visual Studio.

I'll try running after hours to see if that helps. Also will see if that DB server has been rebooted in awhile. I know it gets rebooted after Window Updates are applied.

Thanks for the feedback folks.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

This isn't actually a SQL error - something has changed on the network or server such that larger databases end up timing out when generating the schema.

See: https://stackoverflow.com/questions/2962927/a-transport-level-error-has-occurred-when-receiving-results-from-the-server

Look at the 'netsh interface tcp set autotuning=disabled' part of the thread. Run that on the system where the script is running and see if that helps.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 8

Share a little history, the compare engine in Visual Studio was licensed from Redgate years ago. Now, we've been tuning our engine since then. I don't think Microsoft has shown theirs the same kind of love. It could be that some of the queries it's running are negatively impacting your server. Not saying that's the case, but it looks like your servers are under some stress, and it's evidenced in the compare engine. Are you seeing the errors elsewhere?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning