Analyzing database schema takes hours

Question

Using a new SQL2005 database project in VS2010. I did import objects and settings, and built the project. It seems to be stuck at 'Analyzing database schema. Your database projects will be ready after 3267 operations are completed'.

This seems to take hours. Is there any way to speed this up?

I read somewhere that this may happen if my .dbmdl file is too big, but it's just 2.6MB.

Hope this Helps @ http://stackoverflow.com/questions/1255416/database-project-takes-a-long-time-to-open — arun.passioniway, Mar 23 '12 at 11:53
I have read that - Unfortunately it did not help. For that person, it was 150MB, and it took 20 minutes. Mine is just 2% of that size, and takes about 4 - 6 hours. — xt_20, Mar 26 '12 at 01:47
@xt_20 Did you ever solve this? After the March 2013 updates, I've had no issues, but I realize that it has been a year between your problem and possible fix. — krembanan, Apr 26 '13 at 10:18
Because VS was freezing while displaying that "Analyzing database schema" message I assumed it was an issue with SSDT/Database projects. But it turned out to be with another plugin entirely. — nateirvin, May 01 '19 at 19:57

score 45 · Answer 1 · edited Jul 13 '18 at 03:55

45

Go to Project Settings
click on the Database Settings button
Under the Operational tab

3.1 uncheck Auto create statictics and

3.2 Uncheck Auto update statistics checkbox.

This will reduce your numbers of operations by about 90% and the analysis should finish instantly.

edited Jul 13 '18 at 03:55

answered Jan 14 '13 at 18:02

Rushui Guan

3,023
1
24
26

2

My IDE stop responding and I have no chance to uncheck these checkboxe – Will Wu Mar 23 '16 at 07:33
10

"Project Settings" can be found by right-clicking the project in Solution Explorer, opening "Properties", and then selecting the "Project Settings" tab on the left menu of the properties page ([source](https://msdn.microsoft.com/en-us/library/hh272681(v=vs.103).aspx)). – Jake Oct 12 '16 at 09:19
17

What are the consequences of setting this? Any lost functionality? – user230910 Apr 30 '17 at 11:44
3

As upvoted by a number of other users, does anyone know what the consequences of enabling this are? – Oren Hizkiya Sep 18 '18 at 14:50
3

This could have potentially catastrophic consequences for query performance against your database. If your tables don't have statistics, or the statistics are out of date, the query optimiser isn't going to be able to determine the optimal plan, and sooner or later the plans that it selects will no longer be appropriate. Queries which should take seconds could easily begin taking hours. – UberDoodles Jan 02 '19 at 15:39
@WillWu you can edit these settings directly in the SQLPROJ file by adding `False` and `False` to the first `` node. – nateirvin May 01 '19 at 19:38
Im still confused as to WHY microsoft team decided it was wise to put this in project load. I mean wtf, why they dont just try to compile any txt I open up? This is entirely a build/publish/calculate stat process. – eocron Apr 20 '22 at 10:21

gabnaim · Answer 2 · 2013-04-25T18:59:24.343

12

This question is a bit old but this comes up first when you search for a solution, so I will add my 2 cents.

If you still have this problem after installing the fix, try this, this worked for me:

Open the database project folder in Windows Explorer. Delete the following files/folders:

in the rootdirectory, the .dbmdl file
the content of the "sql" directory
the obj directory.

Close and re-open Visual Studio. Click Build.

The project built fine for me.

edited Apr 25 '13 at 18:59

answered Apr 25 '13 at 18:50

gabnaim

1,103
9
13

10

**Googlers** of **VS2017**. This had no effect – Jul 13 '18 at 03:53

score 8 · Answer 3 · answered Aug 16 '18 at 02:29

8

Old question, but looks like still not solved.

My dacpac has over 10000 objects and the build got stuck for over 40 mins on the Validating stage. Tried the options suggested in other comments and answers but no joy.

Apparently, the Validation of the model also does a "case search". I unchecked the option and build time dropped to under a minute.

Validate Casing on identifies in SQL Server project in VS2017

answered Aug 16 '18 at 02:29

Ceemah Four

458
4
11

1

+1 it worked for me, by un-checkinf that option, the loading time of sql.proj I'm working on reduced, enough for let me continue. Thanks. – Mauricio Arias Olave Jun 23 '20 at 20:33

score 5 · Answer 4 · answered Mar 22 '13 at 12:08

5

According to the release notes of the March 2013 update of SQL Server Data-Tier Application Framework, this bug should be fixed now.

Database Project in Visual Studio hangs on “Analyzing Database Schema…” This release addresses an issue that can occur when loading or building large Database Projects. In some cases, the operation would never finish. This is a functional fix, not a performance enhancement - even with the fix, the time to complete this operation depends on the size and complexity of your project

http://blogs.msdn.com/b/ssdt/archive/2013/03/06/sql-server-data-tier-application-framework-march-2013-available.aspx

http://www.microsoft.com/en-us/download/details.aspx?id=36842

answered Mar 22 '13 at 12:08

krembanan

1,408
12
28

8

**Googlers** : 5 years later and **several** VS versions later this is still an issue so the answer above is probably no longer useful – Jul 13 '18 at 02:36
2

We see this is our DB project and has been the same from at least VS2012 through to VS2017 (version we're currently using). If anything it's gotten worse, but that may be due to changes in our db project. It certainly hasn't improved. – redcalx Feb 06 '19 at 14:28

score 2 · Answer 5 · answered Aug 03 '17 at 15:13

If the SSDT project is not something that you use all the time (like in my case), you can Unload the project, and leaved it unloaded. This way opening the solution in Visual Studio takes no time.

When you need to do something in the SSDT project, at that time you can Reload the project.

score 1 · Answer 6 · answered Sep 16 '16 at 11:45

Analyzing database objects (in VS 2015 Update 3) was taking more time than usual, and was occurring after repeated attempts at restarting VS. I suspect things had hung when the number of objects hadn't changed for 15 minutes. For me, the following got me going again;

Close VS
Make a backup copy of the solution file (*.sln)
Edit the solution file, removing the database projects Restart VS. I found that I was able to now open the solution and build
Close the solution and replace your edited solution file with the original backup
Restart VS. I found that the solution opened this time, and I was able to build successfully

Hopefully this helps someone in the same spot that I was in.

score 1 · Answer 7 · answered Oct 08 '19 at 17:21

1

I even had this issue with Visual Studio 2019 enterprise version 16.3.2 I solved it by just deleting my local database project and fetched the latest version of the project recursively from my DevOps code repository

answered Oct 08 '19 at 17:21

Bertulleke

11
1

I had this issue in VS 2022. Fix: Deleted the database folder and restored from git. – John H Mar 29 '23 at 16:33

score 0 · Answer 8 · answered Oct 04 '18 at 10:17

For this case, if you wouldn't include it (sql files) in project build, choose option "Not In Build" when creating file(s) or change sql file property Build = "None". That will make changes against .sqlproj file and visual studio restart required.

That fixed for me and saved my life!

score 0 · Answer 9 · answered Oct 15 '20 at 23:45

0

Try closing Visual Studio and deleting the .vs folder in the root of your solution.

(VS just recreates it next time you run it, though you may need to set your Startup Project again).

answered Oct 15 '20 at 23:45

MGOwen

6,562
13
58
67

score 0 · Answer 10 · answered Apr 19 '23 at 16:44

I have been running into this issue recently with VS Enterprise 2022 v17.5.X and noticed something I thought I would share, in case it helps someone else. The short version is an invalid FK reference appeared to be causing the issue (resolving this typo/build error resulted in the analysis completing immediately).

Details:

Usually just giving it a few minutes or restarting VS would resolve the problem and let me get back to work. However, this time I was in the middle of changes when it happened and nothing seemed to help.

In a last ditch effort, I pushed my changes through our build server just to validate the build and hopefully be able to deploy these latest changes without the help of VS. The deployment revealed a typo in the name of one of the foreign key constraints (defined in a separate FK script file in the project). This invalid column reference caused the build to fail. The interesting thing is after fixing this issue, the issue where VS was stuck analyzing the database schema cleared almost immediately and I could once again build.

It could have been a coincidence, but given the amount of time I had spent on the issue until then, it doesn't seem like it. If you are encountering this issue in newer VS versions and have recently made/are making changes, it may be worth validating there are no schema errors.

score 0 · Answer 11 · edited May 23 '17 at 12:34

0

Here is a possible fix. (I have had to recreate the schema on SQL databases before, due to speed issues) but this article might be worth looking at. - sounds closer to your issue. Can't open my dbml file in Visual Studio 2010 anymore

edited May 23 '17 at 12:34

Community

1
1

answered Mar 26 '12 at 02:23

Yosem

4,685
3
22
29

I haven't had a chance to try that yet, but since the bounty expires in 2 hours and you are the only person who replied, it's yours. – xt_20 Mar 28 '12 at 01:36

Analyzing database schema takes hours

11 Answers11

Linked