SQL Server FAQs (Frequently Asked Questions)
I don't work with databases, and my knowledge of all aspects of database design and maintenance is really, really slim. On the rare occasions when I do need to do something, I frequently realize that even though I've done it before, it was so long ago that I've already forgotten how. Thus the birth of this slender, uninformative page: when I have to do something, I'm gonna write down how here, so next time I know where to find the answer. In no sensible order, here are a few things relating primarily to Microsoft SQL Server and SQL Query Analyzer:
What is the comment character for SQL Query Analyzer?
There are two choices: the easiest way to do it is preface the line with a pair of hyphens:
-- this line is commented out
Alternatively, you can sandwich the code you want to comment out between forward-slash and asterisk pairs:
/* both of these meaningless lines
have been commented out */
How can I determine what recovery model a database is using?
In SQL Query Analyzer, use this command:
SP_HELPDB [database name]
This stored procedure will tell you a bunch of stuff, most of which you won't care about. The recovery model, however, will be displayed in the "Status" column, in an entry that will say one of three things: RECOVERY=FULL, RECOVERY=BULK_LOGGED, or RECOVERY=SIMPLE. There you go.
To change the mode, you'd use Query Analyzer to issue an "ALTER" command, specifying the mode you want to use. For example:
ALTER DATABASE [name of database]
SET RECOVERY SIMPLE
How do I install SQL Server Analysis Services for SQL Server 2000?
You have to launch autorun.exe from the SQL Server installation CD. That will bring up a splash screen with several options. Click on "SQL Server 2000 Components." One of the options there is "Install Analysis Services." (You wouldn't want to be able to install Analysis Services by running setup, would you? That would be to easy to find.
My database name has a hyphen in it, and SQL queries are failing. How can I fix the query?
Enclose the server name in square brackets: [my-poorly-chosen-dbname]
My hard disk is filling up with giant error logs that I don't need. How do I delete them?
By default, SQL Server 2005 will keep seven log files, named ERRORLOG, ERRORLOG.1, ERRORLOG.2, ERRORLOG.3, ERRORLOG.4, ERRORLOG.5, and ERRORLOG.6. Let's aside the question of why they're filling up, and whether or not you really need them. I'm assuming that you've determined that you definitely DON'T need them. You can delete the .1 - .6 files in Windows Explorer, or from a command prompt. You can't, however, delete ERRORLOG while the SQL Server is running. However, cycling the log will create a new file, delete the oldest file (ERRORLOG.6) and renumber the others. You can cycle the logs by rebooting. Alternatively, you can execute this SQL query:
If you wanted to clear all the files from within SQL Management Studio, you could just execute the "dbcc ERRORLOG" query seven times, thus giving you a set of very small logs, containing only a few minutes worth of data.