Debugging the SQL Connection

Unable to connect to remote server or AssetTrack has lost connection to SQL.

Using your AssetTrack Configuration Worksheet navigate to your AssetTrack Web Server.  Open the web.config file located in the AssetTrack Server folder.  Find the <datasources> section and verify the UID and PWD match what is in your AssetTrack Configuration Worksheet.  If they don't update the web.config file and refresh the AssetTrack application pool in IIS.

If they do match, continue to the SQL server and verify you can login with the UID and PWD found in the web.config file.  If you can not log into the SQL server using the credentials then you will need to log in using an admin user to check if the User is setup correctly in SQL. 

 

Unable to re-index the database. The db user may not be dbo or the db was restored and the restoring user is not the dbo

Over time, as you perform inserts updates and deletes on the AssetTrack databse, your indexes will become fragmented both internally and externally. Internal fragmentation is when you have a high percentage of free space on your index pages, meaning that SQL Server needs to read more pages when scanning the index. External fragmentation is when the pages of the index are not in order any more, so SQL Server has to do more work, especially in IO terms to read the index.

What this means is that if your indexes become too fragmented, at best, your queries will be less efficient but at worst, SQL Server will just stop using the indexes all together, meaning virtually all queries would have to perform a table scan or clustered index scan. In the end, this will drastically hurt your performance.

The AssetTrack Importer is configured to reduce fragmentation when possible. We do this by calling a system stored procedure (sp_updatestats) to rebuild the index of all tables. This is essentially "defragging" your database. The problem with us calling the procedure directly is that Microsoft has a "Won't Fix" bug that can cause issues when your DBO and actual database owner are not the same user. This usually is a result of restoring a backed up database as a different user than the one who initially created it. This is why you are seeing the message displayed at every import, AssetTrack Importer is unable to re-index your tables for you. It is not a critical problem and with your data volume, probably will never become a problem. But it can be fixed.

The Solution

Change the database owner to match the DBO login name.

Image 1 shows where to find the database owner. Right clicking on the db and choosing properties will bring up this window. This owner needs to match the DBO Login name as shown in Image 2.

Running the following query can change the database owner.

EXEC sp_changedbowner 'usernamehere';

Have more questions? Submit a request

Comments

Please sign in to leave a comment.