Vendors – Trust, but Verify


Well, between yesterday and today, I have learned a very valuable lesson when dealing with Vendor MS SQL installations.  And that is, “Trust, but Verify”.

I won’t call out the Vendor, I am not that mean. 

They setup 2 SQL boxes with replication between them and a Push Snapshot subscription to 12 different desktops supporting SQL Express.  Now, first off I thought nice system.  All data changed in a central database and then pushed out daily to the clients. 

However, when the vendor neglects to setup ANY maintenance plans this could cause problems.  I found myself dealing with an out of control Replication based Transaction Log that complete filled up a 100 GB drive.

Here’s how I solved it.

  1. Because this was a VM server, I asked networking if I could get another drive to use for now. 
  2. I added another log file to the database and located it on the new drive.  Now that the log was able to expand, I could fix the problem.
  3. I setup the maintenance plans and executed the FULL backup immediately
  4. I executed the log backup.
  5. Using SHRINK FILE, I ”Released Unused space“ of the main log file
  6. DBCC SHRINKFILE (N‘DBname_log’ , 0, TRUNCATEONLY)
  7. Executed another FULL backup
  8. Executed another Log Backup, now my logs were most empty and having returned almost 80GB of drive space back to the OS I need to undo the changes I did. 
  9. I repeatedly tried to drop the 2nd Log file and repeatedly got a “file not empty” message. I then noticed this little setting in the SHRINK FILE screen.ShrinkFile Scripting it out I discovered this. Wow, learn something new everyday.  Executing this statement moved what little transactions were left in the 2nd Log file to the Main log file so I could remove the file without any problems.

DBCC SHRINKFILE (N‘DBname_log’ , EMPTYFILE)

I am not 100% sure if this was the “best” method of doing this.  But it worked.  And it taught me a valuable lesson in working with Vendors, “Trust, but Verify”. 

Time to go back and double check all the other 50 or so SQL Servers that our wonderful vendors setup!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s