Big Troubles
-
Hello again.
My database has turned suspect and is in user mode. I have been trying everything I know to even see the database since it won't allow me in to see it because the SA account immediately attaches to it and won't let go. SQL server says I can't kill the job it is attempting to perform.
I have tried detaching, killing, restarting both the SQL server and the server and I am not getting anywhere.
I am close to attempting a restoration but I just started with this organization and they have never run a test on their backups. Restoring is my last resort.
What would you do?
-
Can you determine what the reason for suspect status is? Inspect Windows and SQL Server logs for error messages, also check there's enough free space on disks holding the database.
-
The reason was an out of control script that the vendor placed on my machine after I left Friday night. It made the transaction log jump to 100G and it consumed all of my disk space.
The problem was that while trying to fix the database, the SA account grabbed a hold of the database and would not let go. Being in Single User mode made it impossible to even see the databases. It would just freeze.
Here was my solution: After stopping the SQL services, I moved the database and log files to a new directory. I then brought the SQL services back online. I was now able to get into the databases. I detached my troubled database and then reattached from the new location. It actually worked. I am now in the process of checking and validating to make sure that everything is working correctly.
-
nice work! Way to stay calm and get the job done.
-
Good Job Steve.
As a rule of thumb - Make sure you place the log file on seperate disk or at least on a seperate partition. If that is the case and the log file disk/parition runs out of space, your Db will not be market suspect and all you would need to do is truncate the log and you should be fine. If the disk that has the data file runs out of space, its never happy and causes plenty of grief.
-
Believe me Vik, this would not have happened had I been the one to set it up. The temp, log, and database all sit on the same drive. Not to mention it hasn't had any maintainence in the year before I started. Ugh.
The new server I am migrating it to will have the ultimate setup. I think this little episode has made my managers understand the need for proper SQL configuration instead of just slamming then in willy-nilly.
One down, six more to go... (at least)
-
6 managers or 6 servers
.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply