Let me start by saying that I am not a DBA, nor would I ever pretend to be one. But with that said, all of us at some time or another will need to assist someone with SQL. If you have ever landed a client with a SQL server based application, you have probably been put in a situation where you have had to learn right then and there. That is pretty standard when you are playing the part of the out-sourced know-it-all. Hopefully a few of these will help you along the way.
*Note*: Use common sense when working with anyone’s data, especially databases. I always backup before running any queries that may compromise the data in anyway.
1. Consistency checking. You may be receiving various Backup Exec errors indicating that a consistency check has failed in database x or maybe you just want to check just to be sure. Select the database that you want and then click on New Query. On the right hand side/pane, enter in DBCC CHECKDB and click Execute.
This will run a consistency check on your database and report any problems that may be found.
2. Allocations errors. After reading through the results of your DBCC CHECKDB query, you may find allocations errors. You can try nullifying those nasty little bastards with another easy
query: DBCC CHECKALLOC.
3. Table errors. Once again you may run into these and you have a few options. Start by creating a new query containing: DBCC CHECKTABLE ('theNameofYourTableHere', REPAIR_FAST);. From there you can run a full repair by executing another query containing: DBCC CHECKTABLE ('billheaders', REPAIR_REBUILD);. It is worth noting that these two queries/commands do not harm data, that is to say, they do not allow or consider data loss to be acceptable.
4. A sledge hammer to crack a nut. Sometimes when all hope is lost, and you are CERTAIN that you have a backup of the database (damaged or not) that you are working on, you can try a repair with data loss. Im not sure about the exact rules of when you should and should not use this query, but I am fairly certain that the SQL God’s of ole’ role over in their graves everytime you execute this query. Anyways, here is it: DBCC checkdb ('yourDatabaseNameHere', repair_allow_data_loss);
5. Exclusive lock. Some of these queries require that you have SQL in Single User Mode. I spent longer that I wanted searching for a way to do this. Most of my Google searches turned up solutions for starting the entire SQL server or instances in single user mode. But I didn’t want to do that. In fact, I could not do that to the production SQL server while it was in use. Use this query to put a single database into single user mode: alter database yourDatabaseNameHere set SINGLE_USER. Once you are done with your checks and queries in single user mode, you need to get the DB back up so everyone can access it. Use this query to send the DB back into multi user mode: alter database yourDatabaseNameHere set MULTI_USER
6. SQL version. You may want to determine the version and update level of your SQL installation. Run the following query to find out: SELECT @@VERSION
I am certain that there are hundreds of other useful queries, but these are the ones that I found myself using the most. Hopefully they will help you out as well.
–Himuraken