Problem
As a DBA, I have heard about (and experienced) numerous horror stories over the years. One thing’s for sure: you always learn something…what to do and what not to do!
Solution
Here are a few DBA nightmares-inducing, real-life scenarios (and lessons learned) from long ago for your enjoyment.
Missing Logins
Back in the day when I was a Jr DBA, I was asked to clean up unused user accounts on a few SQL Servers. Sounds easy. So, I started the task, but soon I made a fatal mistake. I accidentally dropped the SA account from a Production SQL Server. When I realized I made a mistake, I watched in horror as multiple accounts began to ‘disappear’ from the Logins folder. When I told my boss, he tried to log in, but it wasn’t happening. I was only supposed to remove old, unused SQL accounts, but I made a BIG mistake. My boss had to restore the Master database.
Lesson Learned:
- Check multiple times before making a Production change or have someone look over your shoulder to verify you are making the right decision. Two sets of eyes are always better than one.
Disappearing Backup Files
One of my first DBA jobs was with a small company that handled financial trading for customers. The big system at that time was Outlook Mail. Customer trading information was stored within emails, so the company was required to keep several years of data. Since it was the most important application for the company, the Systems Team thought they would be the sole supporter of that server. So, the DBAs were asked to set up SQL backups to the local server, but we were not granted permanent rights. They removed our accounts after we took care of setting up database backups. Now, it was on the Systems Team to make sure backups were running. At that time, SQL backups were first taken to the local disk, next a third-party app was used to back up the files to tape, and then that tape was shipped off to cold storage.
A few years later, they desperately needed our help. They couldn’t figure out why the SQL backups had stopped getting backed up and copied to tape. It seems that they were being audited and were asked to provide previous emails going back about six months.
When they tried to retrieve those emails, the SQL backups that should have been on tape were missing. Something happened, and the backup jobs were failing. They had no clue what happened, let alone when it happened. Unfortunately for the company, they were later fined six-digit figures for failure to maintain several years of data. And, on top of that, their story was posted in the local newspaper. Ouch!
Lesson Learned:
- DBAs are supposed to be the primary caretakers of SQL database backups. Beyond that, if disaster recovery testing had been performed, it would have shown that there were backup/restore issues.
Suspect Databases
After several years of working at my first DBA job, I felt that I was ready for a new adventure. I was excited because I wanted to keep my career moving forward so when the time came, I put in my resignation. My boss was sad but supportive, and he prepared for my departure. As part of his plan, he wanted to change the SA passwords, but also change the SQL Service account passwords. Since my last day was on a Friday, he planned to make the password changes the following Saturday.
Saturday arrives. He changed the SQL Services account passwords and SA passwords and rebooted all the SQL Servers. The following Monday, he discovered there were many SQL Servers with multiple User databases in Suspect Mode. He looked at the SQL jobs scheduled for that weekend and realized that the maintenance jobs were running a DBCC Reindex when he made those password changes and reboots. He had forgotten to verify that SQL jobs were not running before starting the updates. Ultimately, he ended up performing many restores on his own since he was the only DBA on staff.
Lesson Learned:
- Create a well-thought-out checklist in advance before making production changes. Missing a crucial step caused a seasoned DBA many hours of restoring several databases.
Power On
In one of my previous jobs, the company I worked for used a RAID 5 storage for Production SQL Servers. The IT group was small, as was the DBA team. My manager at the time was troubleshooting an issue with one of the Production SQL Servers and needed to reboot it. She went into the Server room, walked up to the server, and started pushing the power button to restart. She suddenly realized that she was restarting the wrong SQL Server! Panicking, she kept her finger on the power button and didn’t release it until I got into the office, which was about 10 minutes later. She was terrified of letting the wrong server reboot. She wanted to make sure that I could contact the users to let them know of the impending reboot.
Lesson Learned:
- Be very cautious when working directly on a Production SQL server. Sometimes it’s better to double- or triple-check before you start your task. This will prevent costly mistakes.
Superpower
In a previous job, my boss and I were responsible for maintaining a financial application as well as 10 production SQL Servers. Clients would connect to the SQL Server directly by way of ODBC connections to the SQL database from their workstation. One day, my boss said that we needed to set up a new SQL Server. So, I copied over a database from an existing SQL Server and added the user logins.
Sounds simple enough. But we also had to get some specific clients pointing from the old SQL Server to the new one. That’s where our method of changing users’ ODBC connections was a bit unconventional for a DBA. We created a batch file that would include a list of workstations that needed to have the ODBC updated to point to the new SQL Server. To do that, we looked up the workstation names. Since the Systems Team named workstations based on the machine type and location, it was easy to locate specific workstations. For example, the machine name would be named W_CLE_2345. This would represent a workstation in Cleveland along with the serial number. So, the naming convention was most helpful but not exactly confidential.
After updating the batch file with a list of workstations to update, we would run that batch file from our local machine. Apparently, we both had domain rights to update hundreds of workstations, so we could run that batch file whenever we needed to. Fortunately, I never made a mistake. That surely would have been disastrous.
Lesson Learned:
- DBAs should not have domain administrative rights. Sysadmin rights to SQL Servers should be the highest level of permissions that a DBA needs.
Best Practices
- Work with your Systems team to come up with a disaster recovery plan for your production SQL Servers. This is necessary to ensure that your SQL Servers are available to clients in case of an emergency. Failure to ensure availability of the SQL Server will result in a monetary loss to the company.
- When preparing for Production changes, be sure to follow Change Management procedures. This will ensure that all required parties involved are notified of upcoming changes and you will have an implementation plan checklist to work from to ensure that no job steps are missed.
Next Steps
- Share your DBA horror stories in the comments section.
- Be sure to read a few of my other articles:
Terri Hurley is a SQL Server professional working at a leading financial company providing SQL Server support. Terri has been involved in IT for over 20 years, providing SQL Server and Oracle expertise. Her skills include SQL infrastructure, database design, performance tuning, security, high availability, disaster recovery, and replication. In addition, she is knowledgeable in Change Management procedures and implementation.
- MSSQLTips Awards: Rookie of the Year Contender – 2024
I have never seen any documentation, official MS or otherwise, that explains how to drop/delete the “SA” account on SQL Server. I’d be seriously interested in how someone accidentally (or otherwise) “dropped the SA account from a Production SQL Server”.
Many years ago, I was working on a system that contained a workflow that was driven by a table. To determine the next step the workflow would proceed to, a stored procedure using a TOP (1) was implemented. This ran fine for roughly two years when it started to become very sluggish due to increased data volume. The problem was easily identified as a missing index, which was approved and promptly applied to the table. Now, the new index was working spankingly well and query times dropped significantly. Unfortunately, the developer had failed to insert an ORDER BY into the query implementing the TOP and the past success had been dumb luck as it had been following the sort order of the clustered index. This was, of course, no longer the case and data was now “spraying everywhere” because of the new index’s sort order. We had to take the system offline and restore a backup from 2 hours prior. Mercifully, other than some disgruntled users, everything ended well. If the problem would not have been noticed as quickly as it was, the results would have been far more disastrous.
Being a junior DBA currently, for one we support client we usually get request for executing queries for which they will be providing queries, apart from that we even get some delete, rename tables views reuqests as well. One fine day when I was in on-call support and we recieved a request for deleting multiple tables in a non-prod server and I accidentally deleted one table which was not on the list. I was quick to notice it, but not sure how to retireve or undo what I did.
Lesson Learnt:
Always inform about the situation to a senior member of the team (which I did) before the client or requester finds us out themselves and always double check names when executing deleting requests.