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!

Advertisements

Posted on October 17, 2013, in SQL. Bookmark the permalink. Leave a comment.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Humor Me a T-SQL Princess

DBAs really do have a sense of humor

Andrea Allred presents RoyalSQL

Bringing happy endings to all your data stories.

IDERA Community

A community site for users of all IDERA product and SQL community members.

Strate SQL

Data Adventures with a Architect

SQL Sanctum

A Join on SQL And Everything Else

Grant Fritchey

Intimidating Databases and Code

rhondastephens

To Catch A Falling Cactus

SQLSwimmer

Swimming through the Sea of SQL

SQL Studies

 Live, Learn, Share

Aunt Kathi's SQL Server Home

Learn SQL Server from your favorite aunt!

Jen's Blog: We Owls

...stuff. Also, things.

Journey to SQL Authority with Pinal Dave

SQL, SQL Server, MySQL, Big Data and NoSQL

Voice of the DBA

Writings from Steve Jones, the Voice of the DBA

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance

%d bloggers like this: