Administrator Guide · A12

Database tools: Check, Repair and Scripts

TechieCRM can check the database you work in, and the TechieCRM files on your own computer, for the problems that real support work has found again and again: records that point at something that is gone, record-number counters that have fallen behind, and similar. It can repair a few of them itself, safely, and it carries reviewed scripts for everything else, for whoever looks after your database. This chapter explains where the tools are, who can use them, what each answer means, and how to send the results on.

The checks only read. They change nothing and are safe to run at any time, though some take a few minutes on a large database. A repair changes the database, so it always shows what it will change first, keeps a backup, records who ran it and waits for you to confirm.

In this chapter

Opening Check Database
Which tabs you see
Running the checks
Reading the results
Saving or copying the report
The checks in this release
Repair Database
Removing TechieCRM from a database, and putting it back
Preparing a database before anyone signs in
Database Scripts
Changing how the windows look (for developers of other applications)
Not yet available

The Database group on the ribbon's Tools tab has three buttons:

Button	What it does	Who
Check Database	Read-only checks (this chapter, first half)	Everyone (administrators see the database tabs)
Repair Database	Previewed, backed-up, recorded repairs (Repair Database)	Administrators; greyed for everyone else, with the reason on hover
Database Scripts	Reviewed SQL Server scripts (Database Scripts)	Administrators; greyed for everyone else

Opening Check Database

Any of these opens the same window:

Ribbon ▸ Tools ▸ Database ▸ Check Database (in every module).
In the Administrator program: File ▸ Client Database Verification.

Point at the ribbon button to see which database it checks: "Check the SQL database you are signed in to and the files on this computer" when you signed in to a SQL database, or "Check TechieCRM's MongoDB database and the files on this computer" when you work in TechieCRM's own database. The window's title says the same: Check SQL Database or Check MongoDB Database, and the line under it names the database and server.

Only one Check Database window opens at a time. Choosing the button again brings the open window forward. You can keep working in TechieCRM while it runs.

Which tabs you see

The tabs follow how you signed in:

You signed in to	Tabs
A SQL database, with TechieCRM's own database set up on this computer	SQL database, SQL Server, Local MongoDB copy, This computer
A SQL database, without TechieCRM's own database on this computer	SQL database, SQL Server, This computer
TechieCRM's own (MongoDB) database	MongoDB database, This computer
No database	This computer, with the sentence "No SQL database is connected. Sign in to a database and run the check again."

SQL database: the client records and TechieCRM's own tables in the database you signed in to.
SQL Server: the server that database runs on (memory, backups, disk space and similar).
Local MongoDB copy: the copy of your SQL database that TechieCRM keeps on this computer. Anything found there carries the sentence "This is the local copy of your SQL database. The next refresh may correct it; if it does not, report it."
This computer: TechieCRM's own files on this computer (mail cache, outbox and similar), for whoever is signed in.

Who sees the database tabs. The SQL database, SQL Server and MongoDB tabs are for administrators: the MASTER login, or a login whose rights allow it to modify users (the same right Manage Users needs). Everyone else sees This computer only, with the sentence "The database checks are for administrators. You can check the TechieCRM files on this computer here." When TechieCRM cannot read your rights, it treats you as not an administrator.

A tab with no checks in this release says "There are no checks for this yet." (see The checks in this release).

Running the checks

Open Check Database.
Choose Run All Checks to run every check on every tab, except the slow ones.
To run particular checks, tick the box beside each one (on any tab) and choose Run Selected. Slow checks, marked (slow), run only this way.
While the checks run, the bar and the line under it show which check is running ("Checking: Record sequence counters (3 of 7)").
To stop early, choose Stop. The check that is running finishes first; the ones after it show Skipped with "Stopped before this check ran." Closing the window does the same.

The checks run in the background. TechieCRM stays usable while they run.

Reading the results

Each check has one line:

Column	What it shows
Check	What is checked. The tick box selects it for Run Selected.
Result	OK, Found, Note, Skipped or Could not run (below).
Found	How many items were found. Blank when the check did not run.
What was found	The first sentence of the answer. Point at it, or select the line, to read all of it.
Time	How long the check took.

What each result means:

Result	Meaning
OK	The check ran and found nothing wrong.
Found	The check ran and found something that needs attention.
Note	The check ran and found something worth knowing that is not a problem.
Skipped	The check did not run because what it looks at is not there: a table, a permission, a connection, or because you stopped the run. The answer says which.
Could not run	The check tried to run and failed (an error or a time-out). The answer gives the reason.

"Nothing found" and "could not look" are different answers, so Skipped and Could not run are never shown as OK. On the SQL Server tab many logins are not allowed to read the server's own information; those checks show Skipped with "Your SQL login cannot read ...", which is a normal answer, not an error.

Select a line to see, in the pane below the list, what the check looks for, its full answer and up to twenty examples you can look up by hand.

Two buttons work on the line you have selected:

Repair… is available when the check found something TechieCRM can repair itself. It opens Repair Database on that repair and shows what it would change; nothing changes until you confirm there. Administrators can repair the database; everyone can repair the TechieCRM files on their own computer.
Open Script… (administrators) is available when there is a reviewed database script for that check. It opens Database Scripts on that script.

The line above the buttons sums up the last run, for example "7 checks ran: 1 found something."

Saving or copying the report

Save Report… asks where to save and writes everything the window shows. Pick the file type: Text (to read or paste into an email), CSV (one line per check, for a spreadsheet) or JSON (for another program). If you type a name without an ending, the file type you picked decides it.
Copy puts the text report on the clipboard, ready to paste into an email or a support ticket.

The report lists the latest result of each check you have run in this window, with the time of the last run and the database it checked. Every text report ends with "These checks only read: nothing in the database was changed."

The checks in this release

The window lists every check this release of TechieCRM has. Later releases add checks to each tab and they appear without any change on your side.

SQL database

Check	What a finding means
Contacts with no company or individual	Those contacts do not appear under any client entry.
Notes with no client entry	Note history attached to an entry that has been deleted. Ticket and appointment notes are excluded (they legitimately have no client row), and so are History notes, which are kept on purpose as the permanent activity record when an entry is deleted.
Documents and emails with no client entry	The same, for filed documents and emails.
Users with no account row	Their login state cannot be read. TechieCRM lets an unreadable state sign in, so a user you meant to switch off may not be.
Unrecognised login states	The account holds a value TechieCRM does not know, and is treated as "could not determine".
Group memberships naming a missing group or user	Membership rows that are invisible in Security Groups and Teams but still counted by the source CRM.
Record sequence counters	A counter that has fallen behind its table means the next record the source CRM writes will collide with an existing one. This is the most important finding in the list. Only record numbers written by this database are counted. The field definitions counter is known to pass the 32-bit limit on large databases; that is a Note, not a finding.

The SQL database tab also checks these (October 2026):

Check	What a finding means
Notes blank in the list	Notes that open with text but show blank in the list. The examples show the text a refill would give.
Notes trigger	The source CRM's notes trigger is missing or switched off, or recursive triggers are on.
Ticket fields with no owner	Ticket field values with a blank, empty or SYSTEM creator (the cause of a false "insufficient access right" message when a ticket is saved). Rows owned by MASTER are counted separately.
Field values with no definition	Values for a user-defined field that no longer exists.
Empty field rows	Empty value rows for text, number and date fields.
Invisible characters in names	Name, Company or First Name with invisible characters at the start or end.
Computed columns	Standard computed columns that were turned into plain columns, or are missing.
Saved searches with empty values	Saved searches holding empty values the source CRM's own searches never have.
Empty column setups	Column setups with no columns.
Favorite List members that no longer exist	List members whose entry or ticket is gone.
Triggers on the main tables (Note)	Every trigger on the five main tables, with ones that belong to neither the source CRM nor TechieCRM listed first.
Shared appointment rows (Note)	Appointment rows compared with appointments, so shared rows are never mistaken for duplicates.
Stored file sizes (Note, slow)	The total size of stored documents and emails, and the 20 largest.
TechieCRM tables and columns	TechieCRM tables or columns that should exist but do not, or that your login cannot see.
Activity Log protection	The trigger that keeps the Activity Log append-only is missing or switched off.
TechieCRM table permissions	TechieCRM tables your login, or the web users' group, cannot read or change.
TechieCRM rows with nothing behind them	Client Status, partner, column setup and project rows whose entry, setup or ticket is gone; Client Status values not in the list; sign-ins left open for more than a day.

SQL Server (administrators): which server and edition this is; memory and parallelism settings; backup compression; database options (auto close, auto shrink, page checks, statistics, Query Store); the compatibility level, which must stay at 120 (it is only reported, never changed); file growth; log health; how recent the last backups and the last integrity check are; damaged pages; whether a maintenance job is scheduled; free disk space; blocking right now; which databases use the server; stale statistics; database users whose login is gone after a restore or a server move; and, when you tick it, index fragmentation (slow). A check your SQL login has no right to run shows as skipped with the reason, not as a failure.

MongoDB database / Local MongoDB copy (administrators): server version and disk space; indexes TechieCRM expects that are missing; values that should be unique but are not; records with no change date or with a generated id (which would make a second copy in the cloud); contacts with no company or individual; activities whose entry is gone; deleted records still present; the old custom-field layout; stuck background work; and, when you tick it, stored files without an owner (slow). A finding on the Local MongoDB copy is about TechieCRM's copy on this computer, not your SQL database.

This computer (everyone): the mail cache, the Email to Many outbox (emails marked "May have been sent" are listed), your stored email accounts (it confirms each stored password still unlocks and reports only the count, never a value), Activity Log entries waiting to be sent, and how recent your last local backup is. These checks only read your files; nothing is changed.

Repair Database

Opening it. Ribbon ▸ Tools ▸ Database ▸ Repair Database, the Administrator program's Utilities ▸ Repair Database, or Repair… on a finding in Check Database. Only one Repair Database window opens at a time.

The Repairs tab lists every repair in this release, where it works (SQL database, MongoDB database or This computer) and whether it can be undone. A repair that cannot run in your session says why when you select it (for example, the MongoDB repairs when you signed in to a SQL database without a local copy, or the database repairs when you are not an administrator).

How a repair runs

Select a repair and choose Preview. TechieCRM works out what it would change, reading only, and shows on the right: what will change and how many, up to twenty examples, every trigger on the tables it changes, what is backed up first, whether it can be undone, and how many other people are signed in to the database. If something stops it from running now (for example your SQL login cannot create tables), the preview says so and why.
Read the preview and tick I have read what will change. A repair that changes data also asks you to type the database name exactly as shown.
Choose Repair. Before anything changes, TechieCRM: - checks your rights again; - works the preview out again: if the database changed since you read it, nothing happens and you see "The database changed since the preview. Review the new counts and confirm again." Choose Preview again; - makes sure no other repair is running on this database (only one runs at a time, from any computer); - writes a backup of everything it is about to change to .techiecrm/db_repairs/<database>/<run>/ in your home folder, and reads it back to make sure it is complete; - records the repair in the Activity Log (A11) under your name. If the entry cannot be written, the repair does not run.
The repair then works in batches. Stop ends it after the batch that is running; what was done is kept and the result says Partly done.
The result says what changed, and the matching check's count before and after.

If a repair is refused, nothing in the database was changed, and the message says why.

Past repairs lists the repairs run from this computer, newest first: when, which repair, the result (Done, Partly done, Did not finish, Refused, Undone) and whether it can be undone. Select one and choose Undo… to undo it. Undo puts back only what is still exactly as the repair left it; anything changed since is left alone and listed.

Every repair is also recorded in the database itself (the TechieCRM table TCRM_Db_Repair_Run, for a SQL database), so anyone reading it can see what was repaired, when and by whom. That table is created the first time a repair runs, if your login may create tables.

The repairs in this release

Repair	Where	What it changes	Undo
Set up missing TechieCRM tables	SQL database	Runs TechieCRM's own setup scripts again for every feature with a missing table or column, and lets the source CRM's web users (CRMGroup) read and change TechieCRM tables where they cannot. Never touches the source CRM's own tables. Needs a login that can create tables.	No: new tables and columns are left in place, because removing them could lose what has been saved in them since.
Put back the activity log's protection	SQL database	Re-creates or switches on the trigger that stops Activity Log entries being changed or deleted.	No: TechieCRM never removes that protection.
Create missing MongoDB indexes	MongoDB database or Local MongoDB copy	Creates the indexes TechieCRM expects that the database lacks. A unique index whose values already repeat is left out (fix the repeats first), and so is one that exists without its rule. Documents are not touched.	Yes: drops only the indexes it created.
Set aside unusable mail cache files	This computer (everyone)	Renames a mail cache file that cannot be opened, is damaged, or has a layout this version does not know, to `<name>.<date>.old`, so the Email window builds a new one. Nothing is deleted. A file still holding email copies waiting to be saved is left alone. Close every Email window first.	Yes: renames it back, unless a new cache has been made since.
Add missing columns to the stored email accounts	This computer (everyone)	Adds the columns this version uses to the file that stores your email accounts. Each new column starts empty.	No: an empty column changes nothing.

Repairs to the source CRM's own tables (record-number counters, notes blank in the list, ticket field owners) are planned for a later release, each turned off until it has been proven on a copy of a database. Until then, use the matching database script.

Removing TechieCRM from a database, and putting it back

TechieCRM keeps its own features in tables whose names start with TCRM_, inside your CRM database. If you stop using TechieCRM on a database, or need to hand back a database without them, you can take them out.

Opening it. In Repair Database, under the buttons, choose Remove TechieCRM from this database…. The button is greyed unless you are an administrator signed in to a SQL database. The removal itself also needs the MASTER login, or a login that is db_owner of the database; anyone else is told so and nothing changes.

What is removed. Everything TechieCRM put in the database, found when you run it:

every table whose name starts with TCRM_ (the tables TechieCRM sets up at sign-in, and the ones added by hand or by other TechieCRM tools, such as TCRM_Sync_Control or TCRM_Payable);
every view, procedure, function and trigger whose name starts with TCRM_ or usp_TCRM_ (for example TCRM_Projects_View, the Activity Log's protection trigger and usp_TCRM_SyncFromMerge_Run);
the links (foreign keys) between those tables, and their indexes.

Nothing else. The CRM's own tables, views, procedures and triggers are never touched, even when a column on them has "TCRM" in its name (those are listed as left alone). Removing an object also removes the permissions that were granted on it, such as the web users' (CRMGroup) permissions on TechieCRM tables.

How it runs

Dry run. TechieCRM reads the database and lists every object it would remove, with its type and how many rows each table holds, the server folder the backup will go to, anything outside TechieCRM that refers to a TechieCRM object (it will stop working), and anything that stops the removal. The dry run changes nothing.
Choose Choose… and pick a folder on this computer for the copy of TechieCRM's tables, type the database name exactly as shown, and tick the box.
Choose Remove TechieCRM. Before anything is removed, TechieCRM: - checks your rights again, and runs the dry run again: if anything changed, nothing happens and it asks you to run the dry run again; - saves a copy: a new folder TechieCRM_removal_<database>_<date>_<time> inside the folder you chose, holding one CSV file per table (UTF-8, with a header row; an empty cell is an empty value), manifest.json (the database, server, time, login, every object with its row count and the text that creates it), the setup scripts that create them, and cleanup_log.txt. Every file is read back and counted; - takes a full backup of the database on the database server, in the server's own default backup folder, named <database>_before_TechieCRM_cleanup_<date>_<time>.bak. It is a copy-only backup, so your own backup routine is not disturbed. TechieCRM then checks the backup can be read.

If the copy or the backup fails, nothing is removed and the window says why. 4. The objects are then removed one at a time: triggers, views, procedures and functions, the links between tables, then the tables. If one cannot be removed, the rest still are, it is tried once more at the end, and the result says Partly done and lists what is left. Run the dry run again to see it.

The result says what was removed and where the copy, the backup and the log are. The log (cleanup_log.txt) and result.json in the copy folder record what was found, copied, backed up and removed, and what failed. The Activity Log is one of the tables removed, so the log file is the record of the removal.

After the removal, signing in does not set the tables up again. TechieCRM writes a note on the database itself (not in any table) saying it was removed, when and by whom. While the note is there, TechieCRM's sign-in setup and its features create none of its tables, and the desktop shows one line at the bottom of the window: "TechieCRM's tables were removed from this database on purpose, so TechieCRM did not create them again." Set up missing TechieCRM tables in Repair Database is also refused while the note is there.

Three features that used to set up their own table when first used now check the note too, and while it is there they create nothing and say they are not available, with the same reason ("TechieCRM's tables were removed from this database on purpose ... an administrator opens Tools > Database > Repair Database and chooses Restore TechieCRM to this database"):

The office sign-in registry (offices without cloud sync; see Signing in from more than one place). The one-place check does not run on that database; signing in works as it did before the check existed.
The Scheduled email list. Opening Scheduled says "Scheduled emails are not available on this database." and the reason; Send Later cannot hold an email there.
Repair Database. Each repair keeps its record in a TechieCRM table, so repairs say "Repairs cannot run on this database ..." and the reason. Remove and Restore do not use that record and still work.

If the note cannot be read (for example the login may not read the database's properties), these features behave as they did before the note existed.

Putting it back. In Repair Database choose Restore TechieCRM to this database…, tick Set TechieCRM's tables up in this database again and choose Restore TechieCRM. It removes the note and runs TechieCRM's normal setup, so its tables come back empty. To get the old rows back, restore the backup the removal took, or ask your database administrator to load the CSV copy. Restore needs the same rights as the removal.

On a local portal. The same removal and restore run from the portal's install folder with manage.py remove_techiecrm and manage.py restore_techiecrm, on the office database in the portal's .env, with the same safeguards in the same order. See A13 Local portal.

Preparing a database before anyone signs in

TechieCRM sets its tables up by itself when a login that may create tables signs in. You can also do it before anyone signs in, from the sign-in window: press the gear, and on the Configuration window's Database page fill in the server, the MASTER login (or a login with db_owner on the database), its password and the database, then press Prepare (Prepare again once the tables are there). See A14 The Configuration window, Preparing a database.

Prepare does the same setup as sign-in, in this order, and lists each step:

It reads the note left by Remove TechieCRM from this database (above). While the note is there it creates nothing, shows the same line as sign-in, and the Prepare button is greyed with that line as its reason. Restore TechieCRM to this database is still the way back.
It checks that the login may create tables before anything else runs. A login that may not is told Only a SQL login that may create tables can prepare this database: the MASTER login, or a login with db_owner on it., and nothing changes.
It creates the missing TCRM_ tables, columns and indexes, and grants the web users (CRMGroup) their permissions, exactly as at sign-in.
It gives the database its workspace ID when it has none (one row in TCRM_Workspace; two computers preparing at the same moment end with one ID).

The local portal's Save and prepare runs the same preparation (A13).

Database Scripts

Opening it. Ribbon ▸ Tools ▸ Database ▸ Database Scripts (administrators), or Open Script… on a check in Check Database.

The window lists reviewed SQL Server scripts that come with TechieCRM, written from real maintenance work. Scripts that only read come first. Select one to see what it does, the rights it needs, how long it takes and, on the Script tab, its full text.

Scripts that only read (Run): Pre-maintenance health report, Maintenance health report for this database and Blocking and load snapshot. Run runs the script on the SQL database you are signed in to, one section at a time, the same safe way the checks read. A section your SQL login is not allowed to read says so and the others still run. Stop ends the run after the section that is running. The results appear on the Results tab; Save Results… (text or CSV) and Copy Results send them on.
Scripts that change the database have no Run button. TechieCRM never runs them. Use Copy Script or Save Script As… and give the script to your database administrator, who runs it in SQL Server Management Studio. Every one of them starts with what it changes, what must be true first, the backup to take, how to run it and how to roll it back, and does nothing until @Execute = 1 is set in it: run as it is, it only shows what it would do.

Script	What it is for
Copy-only full backup with a space check	A full backup that does not disturb the nightly backups, verified. Take one before any other script that changes the database.
Integrity check (DBCC CHECKDB), never a repair	Reads every page and reports damage. If it reports any, stop and send the output to your support contact.
Adaptive index maintenance and statistics update	Reorganizes and rebuilds fragmented indexes and refreshes statistics, after hours.
Standard SQL Server and database settings for the CRM	Memory, parallelism, backup compression, file growth, Query Store. Leaves the compatibility level alone.
Right-size the transaction log	Shrinks only the log, optionally moving a database whose log is never backed up to SIMPLE recovery.
Turn on read committed snapshot	Readers stop blocking writers; test on a copy first.
Relink database users to their server logins	After a restore or a move to another server.
Put linked database users back in the CRMGroup role	The role the web portal's users need.
Restore computed columns flattened by a rebuild	The cause of notes that save without an error but never appear.
Turn the note text trigger back on	The cause of notes that show blank in the list.
Let the web users reach the TechieCRM tables	Grants for the CRMGroup role.
Remove empty user-defined field value rows	After the program writing them is fixed; backs the rows up first.
Remove invisible characters from the ends of client names	One name column at a time; backs the old values up first.
Saved searches with empty (NULL) values	Sets them to empty values; backs the rows up first.
Bring record sequence counters up past the numbers in use	The fix for "duplicate" errors after an import, for a database administrator without TechieCRM.

Each script's text is checked against the reviewed copy every time it is shown or run; a script that has been changed is left out of the list.

Changing how the windows look (for developers of other applications)

The three windows take their colours and fonts from your TechieCRM theme and change with it the moment you change Theme, Primary or Accent colour, or the font, in Settings. The result colours keep their meaning in every theme: OK, Found, Note, Skipped and Could not run each keep their own status colour.

An application that embeds a window can pass its own look as a DbToolsStyle (in src/db_tools_ui/style.py): window, text, border and field colours, the accent colour, the five result colours, font family and sizes, icon size and the smallest window size. Every field defaults to the TechieCRM theme. A window given its own look keeps it when the TechieCRM theme changes. CheckWindow, RepairWindow and ScriptsWindow all take style=.

from src.db_tools_ui.check_window import CheckWindow
from src.db_tools_ui.style import DbToolsStyle

CheckWindow(parent, style=DbToolsStyle(accent="#3366CC", font_size_pt=11)).show()

To run the Check window on its own: python -m src.db_tools_ui.

Not yet available

Repairs to the source CRM's own tables (record-number counters, notes blank in the list, ticket field owners): a later release, each proven on a copy first. Use the database scripts meanwhile.
Data repairs to TechieCRM tables and the MongoDB database (removing rows that point at nothing, closing stale sign-ins, stamping change dates, re-keying ids): a later release.
Since the last check: comparing a run with the previous one.
Database Scripts from the command line and a MongoDB validate script.

← The Activity Log

The TechieCRM Local Portal →