I recently did a rollout in production of a new version of the EHR split over several deployments. The EHR uses a several shared databases for lookup values of CPT codes or drug names so that an update to those can be done once per server and not risk affecting patient data. Since we have all of our production databases on the same SQL cluster, and the structure of one of these tables was changing, we created a workaround. We created a copy of it, appended the version number to the name, then ran an update script on all databases to recreate all views and stored procedures that reference the shared databases to point to our new one. As they are upgraded to the new version, these views and stored procedures get recreated with the default database name.

Now that everyone is on the new version, querying the database I’ve found that not all these objects we changed got touched in the upgrade. I was using sp_helptext to display database objects to me, but it was limiting because it wasn’t searchable.

SELECT name, create_date, modify_date, type, type_desc
FROM SYS.OBJECTS
WHERE OBJECT_DEFINITION(object_id) LIKE '%ver323%' ORDER BY Name

This returned 153 records. 153 out of 158 were untouched in the upgrade. The column created_date in the query showed the date of the previous upgrade, so I’m deleting from update script the 5 objects that were updated, doing a Find Replace to delete “ver323″ from the script so these get recreated with the default database name. I’ve put this out in QA. I’ll put this in production once I’m convinced it won’t break anything.

Here’s my situation: the table [Person] stores demographic data and it has 20,000 rows. 16,000 of those records duplicated when an update file was processed (now there are 36,000 rows). At first I wanted to delete the entire contents of the table and rerun the file, but then I would be losing the 4,000 manually entered records. Then I thought to delete everything with a modified date since the file ran, but I would be losing any valid updates and anything manually entered since the file was run.

Best thing to do is delete the oldest instances of any duplicate records. In the table I have a unique person identifier and a last modified date. Time to dust off PARTITION BY. Instead of creating a temp table, I’m going to use WITH to create a CTE. I’m trying to keep it simple.

WITH [TmpDupes] AS (
SELECT
Row_Number() OVER(PARTITION BY PersonTableID ORDER BY LastModifiedDate Desc) AS RowNum,
PersonTableID
FROM [Person] )
DELETE FROM [Person]
WHERE PersonTableID IN
(SELECT PersonTableID FROM [TmpDupes] WHERE RowNum >= 2)

As much as a test environment should mirror the production environment, not everything ends up being the same. In Production it’s important to use FULL recovery model; in the test environment it’s much less important. Starting this weekend we got very close to running out of space on my favorite test server, so it was time to shrink files. To find the databases using FULL recovery mode:

SELECT name FROM sys.databases WHERE recovery_model_desc = ‘FULL’

Then from Object Explorer, select the database, right click, choose Properties, select Options, and change the field Recovery Model from FULL to SIMPLE and start shrinking log files and freeing up space.