Tuesday 18 August 2009

Rebuild Index Failed

"failed with the following error: "Online index operations can only be performed in Enterprise edition of SQL Server.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

Or, perhaps, the failure reason is that the SQL Server in question is Standard Edition, and that's pretty much it?

Other suggestions here (if you're not in Standard Edition!).

It would be nice, though, if SQL were clever enough to know when it's running Standard Edition, and not give you the option of an Online Index Operation when you're building the maintenance plan, now wouldn't it?! Or is that a Moon-on-a-Stick request?

Thursday 13 August 2009

OSQL sort of isn't...

'OSQL' is not recognized as an internal or external command, operable
program or batch file.

Oh. But it's where it should be! C:\Program Files\Microsoft SQL Server\80\Tools\Binn (not my installation).

Within CMD, you can type 'set path' to check the variables in the default path. If the actual location of osql.exe isn't in there (or, rather, its parent folder - and it should be there, because SQL is supposed to install it!)

Follow the instructions here.

Sorted. And so quickly.

Tuesday 11 August 2009

Because I am a doofus

And, because, primarily, this is my repository of Useful SQL Information.

Master
Contains serverwide information about SQL
  • logins
  • linked server info
  • configuration info
  • info about user databases - locations of db files, key properties etc

Vital: SQL cannot start without it.

Resource

AKA mssqlsystemreources.mdf in the file system. Rather completely invisible in SSMS. Contains all the system objects deployed in SQL: so all the system stored procedures and system views (although these appear logically in the db to which they belong). It makes life easier when performing upgrades or applying Service Packs to have all this information in one place. Do not go looking for it. You will not find it except as a file.

Cannot be backed up.

Model

Template database: all databases need a base set of objects called the system catalog. (I know, American Spelling). When you create a new database, it uses the model database as the basis for its structure. So, if you want a people table in every single database you create, create it in the model database.

MSDB

Stores information for SQL Server Agent, Service Broker, Database Mail, Log Shipping etc. SQL Server Agent job information gets stored here i.e. job parameters, schedule, execution history. MSDB also stores backups and maintenance plan information, as well as information about the servers and tables involved in log shipping.

If you're backing up the database logs every 15 minutes or so, and you have lots of databases in Full Recovery Mode, you need to make sure that you have a regular tidyup of MSDB with a cleanup task, to make sure it doesn't get too big.

Distribution

Used during replication, it stores metadata and history information and transaction information for transactional replication. Until replication is set up, the distribution database is invisible in SSMS, however, the data files are installed by default.

TempDB

It's all in the name for this one: temporary data and data objects. Temporary Tables. Temporary Stored Procedures. That sort of malarkey. Also used for sort operations for large queries, some indexing, and during dbcc checkdb (database_name, repair_rebuild). Thus tends to get a bit out of control on occasion.

Gets recreated every single time SQL Server Service is started (very handy if it's gotten a little Out Of Control recently).

Cannot be backed up.