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.
- Because this was a VM server, I asked networking if I could get another drive to use for now.
- 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.
- I setup the maintenance plans and executed the FULL backup immediately
- I executed the log backup.
- Using SHRINK FILE, I ”Released Unused space“ of the main log file
- DBCC SHRINKFILE (N‘DBname_log’ , 0, TRUNCATEONLY)
- Executed another FULL backup
- 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.
- 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. 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!