SQL Connection Timeout

Is this on a local instance of Acumatica? I‘m guessing so because you’re able to run the DBCC commands.

One thing that I do to my local SQL is to set the model database’s recovery model to Simple and then I don’t have to worry about logs and backing up logs for any databases that I create. I also have restricted the amount of RAM that the SQL engine can access so that it doesn’t just take everything.

It’s possible that your database indexes have become fragmented and so I’d recommend the SQL Maintenance script at https://ola.hallengren.com/. It will do a bunch of things but the main one is around index de-fragmentation. You can run a stored procedure that it creates to have it rebuild indexes that are fragmented on one or multiple databases and it will only rebuild those based on thresholds so you’re not rebuilding all of them.

​@Django Thanks for the info. Do I have to change anything in the maintenence script, or can it run as is? And where do I then find the index rebuild to run? I am not very well versed in sql.

Because this is all my machine, I run the script on the master database so remember where it is. You can add it to each database if you want but that seems like extra unnecessary effort.

The main parameters for when you first run the script (and you can re-run it) is around backups. If you’re just using it for index rebuilds you can use examples from the website but here’s one I use:

EXECUTE dbo.IndexOptimize
@Databases = MyDatabaseName,
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

You’ll see the progress in the Messages tab (assuming that you’re running this in SSMS).  Every index on every table in the database (or databases if you give it multiples). That’s about it for what you need to do. The idea of the fragmentation levels is to not waste time on indexes that aren’t heavily fragmented.

Hopefully that helps with your timeout. It seems odd that you’re getting this error locally. However, there’s also no real downside to having your indexes optimized on your databases.

On my old under powered laptop with a hard drive vs. SSD, I would add queryTimeout=”10000” to the database section in the web,config file before the />

        <add name="PXSqlDatabaseProvider" type="PX.Data.PXSqlDatabaseProvider, PX.Data" connectionStringName="ProjectX" companyID="" secureCompanyID="False" />