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!
Padre's SQL Resort 