Do Not Set Maximum Ram To 0 Ever - Mistakes We Make

I'm going down memory lane here and remembering one of the first things I did as a DBA. I was told by one of our other DBA's that if you have 40GB of RAM for a database and it's not working very well, the fix is to modify the max RAM from 40,960 to 1,024 then back to 40,960 after it cleared everything out. I understand now that This isn't the best way to handle it. Queries may fail, everything that was cached will have to cache again, and a slew of other performance issues and possible failed reports.

Here comes the bad part. I had the bright idea at the time that if setting it to 1GB fixed the issue... why not set it to zero? Well SQL doesn't accept zero with any grace. It modifies it to 16 MB of ram. SQL just will not run with that much RAM. SSMS wouldn't load, the website went down, nothing was working. We had to stop all services and login through SQLCMD after starting SQL up in single user mode with the minimal configuration switch. 

Now we have downtime in the middle of production hours. I will say there are better ways to fix this, but the way we fixed it was restoring master from earlier that day that still had the correct setting. I wasn't really familiar with working in a DOS or PowerShell window with SQL at the time. This obviously caused down time and didn't help my reputation any. 

We All Make Mistakes

I now know what not to do. I know to verify things I do not completely understand and cannot logically pick apart. I know how to research more efficiently and I have a better Disaster Recovery plan. We all make mistakes, really we do. No one is perfect. The thing is though, what are we doing to get better? 

This is my request to all of you. Post a story about a mistake you've made and what you did to overcome it. Tell me what you've learned and how you plan on preventing it in the future. I think we could all simply learn from our mistakes or we can help others by letting them learn from ours.

I'm sure someone out there has a good story about deleting a table because they forgot the where clause and the transaction to wrap it in.

No comments:

Post a Comment

All opinions welcome! Please comment with any changes thoughts or discussion points.