I cannot and will not take credit for the following, but since my fellow senior engineer does not have a blog I figured I’d share this with everyone out there, because when searching for a solution, this one did not present itself.
Scenario : SQL Server, slow performance, and erratic behavior.
Solution : Reboot the server.
Outcome : Server Hung, for what seems a longer than usual amount of time.
Steps taken to rectify this behavior : Hard Power off the server (don’t ever do this, I mean sometimes you have to, but wait just a little bit longer before resorting to this) Things that could have been tried before, would be to run services.msc from another workstation or server, and possible you could connect and stop some services speeding up the process.
What ended up happening? The client shut the server off with thepower button.
Server boots back up and as you’d thing, SQL Server Service failed to start…
Customer reboots again.
SQL Server fails to start once again. Customer called EPIC Information Solutions (the company I work for)
A Programmer was on the task with the senior engineer mentioned above. We came back from a well deserved lunch and the VP of Managed Services asked us to help the programmer and the senior engineer. We tried pretty much everything, permissions on files, on services, hardware profile under services was disabled(which should be enabled, but that didn’t fix it) always got SQL Server Service failed to start “Access Denied” nothing seemed to fix it. Procmon.exe couldn’t identify which files it was failing on, there was no way to check to see if the databases were okay or not. Renaming Master.mdb and Masterldf.ldf netted the same results, nothing seemed to work. We used and abused Google like you wouldn’t believe… the backups were unreliable as they were years old. Sure there were backups of the SQL Databases, but how can you restore a SQL Backup when you cannot start the SQL Server Service and restore them with SQL Management Studio…. The worst came into view, rebuilding the master.mdb. We really didn’t want to do that.
Last ditch effort from my friend the senior engineer.
Rename the “Bin” folder under the “Microsoft SQL Server” folder and restored from a backup that ran last night.
Folder was restores, back to services.msc, highlight the SQL Server Service, and click start. Done… service started. SQL Server Management Studio started up and he was able to connect to the database. Our Programmer tested the app that required SQL and sure enough, it was working fine.
Thus as the title says, for the amount of space SQL Server directory takes… Back It UP!