Developed by Data Equity LLC, SharpShooter is an analytics and troubleshooting tool for Time Matters. The following article is the first in a new series of topics taken from "SharpShooter's Field Guide". The Field Guide is designed to explain and offer repair tips for the test results provided by SharpShooter.

The following is an excerpt from the SharpShooter Field Guide on the topic of Orphaned Specified Relationships. Orphaned Specified Relationships are a long standing issue with Time Matters and are also closely related to a recently highlighted "linked updates" regression in the retracted SR3 of Time Matters 9 (and SR2C of TM8).

Orphaned Specified Relationships can be a common problem and their occurrence should give cause for moderate data integrity concerns. Detecting and correcting ongoing orphaned relationships will improve application performance and data integrity. To prevent these issues from occurring it is best to identify subordinate relationships prior to deleting Contacts and Matters.

There are two types of Specified Relationships; manual and linked field. Manual relationships are created when an end user explicity relates two records together in the Time Matters User Interface. This type of relationship is normally associated with a Relationship Code. The two most common ways to initiate this operation are by adding a relationship from the Related Records Tab or a Drag and Drop (between any two records).

The Linked Field relationship is a result of a form style defining a field as a lookup to another record. The lookup must be defined to receive updates when the "looked up record" is changed. As you can imagine, this can be (and is) the underlying cause of data integrity issues in Time Matters. 

 

I, as most would, consider the Time Matters database to be very “dynamic”. Every day thousands of rows are changed by the “Follow Flag”, an astounding amount of database updating. Considering the “Date” is changed by the “Follow” process and that “Dates” are found in many indexes, this creates a lot of transactional activity on a large database.

The heightened use of the Database Maintenance/Re-indexing Main Database utility is also increasing the database activity. This is an “if all else fails, try this” utility, used to correct hard to find issues in the Related Records and TM Synch code. Unfortunately, I am seeing and hearing of firms running the program’s database Maintenance utility all too frequently, creating a significant amount of database transaction activity.

Importing data is also a significant contributor to database activity. This is normally a one-time operation for Time Matters (during initial setup). Some firms use the various external links to Time Matters that may, depending on the link, contribute to database activity.

Highly dynamic databases create a lot of activity in the database transaction log and can also create fragmented indexes. This blog will cover the former issue regarding database transaction logs. We’ll discuss index fragmentation in my next blog. 

In SQL Server, each database has at least one data file and one transaction log file. The data is physically stored in the data file, and the details of all modifications to your database and the details of the transactions that performed each modification are stored in the transaction log. Due to the aforementioned issues, many Time Matters databases have much larger transaction log files than should be required. 

According to the SQL Server documentation:

“…when a transaction log file grows until the log file uses all the available disk space and cannot expand any more, you can no longer perform any data modification operations on your database. Additionally, SQL Server may mark your database as suspect because of the lack of space for the transaction log expansion.”

We were doing a Webinar for MobileTM this week when I realized there is feature in TM Enterprise that is not well known.

This feature has to do with viewing and copying the current filter/search criteria in SQL Syntax. I was explaining one of MobileTM's advanced features, being able to use an inline SQL filter in any datalist. To demonstrate I opened the Contact list in Time Matters (you may want to follow along here), any list will do but the Contact list is easy to follow.

1) Make sure you are displaying the filter droplist in the toolbar (this was removed in 9.0 for some reason).

2) Select a filter from the drop-down or create any search with one of the search options.

3) Right click on the filter drop-list, select show filter.

4) In the bottom portion of the display you now have the specific SQL syntax needed to reproduce the filter Time Matters is using to display the list.

I noticed a pinned thread on the Time Matters forum regarding SQL Express backup. Here's an extremely simple yet effective process I suggest to clients using SQL Express. 

All flavors of SQL Server, including SQL Express, ship with a utility program that will execute SQL script from the Windows command line. This allows sites running SQL Express a very simple, cost-free opportunity to perform automated backups.

sqlcmd -S .\sqlexpress -Q "exec timematters9.tm9user.tm_backup timematters9, 'auto'" -o sqlbackuplog.txt

This command can be executed in an automated task. The result will be the same as running the backup from inside Time Matters. If you have not modified your TM backup procedure, a backup will be created in the default SQL Server backup folder with the name of the current day of the week.

WHERE
.\sqlexpress = the name of the Sql Server Instance
timematters9 = name of desired database to be backed up
tm9user = name of the time matters database owner

Just add this command to the Windows scheduler and you will have a simple to implement, centrally managed automation system for performing backups and any other SQL server maintenance you would like to perform at a regular interval.