(Newbie) Help with strange tables in my database "dbo.183" etc.

Question

(Newbie) Help with strange tables in my database "dbo.183" etc.

brj5144

SSC Enthusiast

Points: 125
More actions
June 30, 2021 at 8:49 pm
Go to Answer
#3902248

I don't know how to read about this or any other way to ask it because I don't know what they are called. But my sql server database is full of tables with strange names that I am not sure what is in them or what they are for.
Examples are
dbo.a183
dbo.a184
dbo.a185
Etc. What are these used for thank you.
July 1, 2021 at 6:19 am

This was removed by the editor as SPAM
frederico_fonseca

SSCoach

Points: 16003
More actions
July 1, 2021 at 12:33 pm
Answer
#3902456

it is very likely - look at https://desktop.arcgis.com/en/arcmap/10.3/manage-data/gdbs-in-sql-server/system-tables-sqlserver.htm
looks like this bit "The adds table (a_<registration_id>) maintains information about each inserted or updated record (feature) in a versioned business table and is queried to identify which records have been added or modified at a particular geodatabase state."

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

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

ScottPletcher SSC Guru Points: 100940 More actions · Answer 1

No idea. They're not system tables that are intrinsic to MS. Some other user/3rd party code/script must be creating them.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

brj5144 SSC Enthusiast Points: 125 More actions · Answer 2

Thank you so much.

That has put me a step in the right direction. I would assume they are created by the software that we use. ESRI ArcServer. It is a Geographical Information System for an electric utility. I will start looking at it now.

brj5144 SSC Enthusiast Points: 125 More actions · Answer 3

I'm pretty sure that is what they are. You guys are awesome. I have a bonus question. Nobody has to answer. This has nothing to do with SQL Server but someone out there has probably used Arc GIS software before.

"Other people that didn't really know what they were doing set this up and I have been left to sort through it."

Here is the scenario:

There is a SQL Server at City Hall it contains a GIS database with streets, houses, addresses etc. in it.
We have a SQL Server at the Electric Department. Someone has setup the CityHall Database to be replicated to the Electric Department SQL Server every night. (This is done with ESRI Scripts not through SQL Server)
So there is a copy of the CityHall database on the Electric Department SQL server.
In the CityHall database the dbo.Postal_Addresses table is 1 Gigabyte
The dbo.a12 table which is what we talked about above is 3.4 Gigabytes. (I assume it is the adds to the dbo.Postal_Addresses table.)

Why in the world is this adds table so large and what could I do to prevent this. It is 3 times larger than my largest table in the database?

(Just thought one of you smart guys might know this off the top of your head. Don't study too hard on that. I can contact ESRI.)

brj5144 SSC Enthusiast Points: 125 More actions · Answer 4

****UPDATE*****

I think we need to do something called reconcile versions to shrink this add table. I am looking into how to do that.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 5

In the future to help you with what are these are tables... in the past one thing that has helped me track that answer down is in Mgt Studio, click the Plus sign on the database, then click on the word tables and then hit F7. This will populate the list of tables with the create date/time on them on the right side. You can then sort the list ASC or DESC by table name or create date. You can then look for a pattern of when the tables in question were created versus other tables. This might help you narrow it down.