But then one day I got a message to say the job had failed
Executing the query "ALTER INDEX [IX_Events_eventId] ON [dbo].[Events] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF )
" failed with the following error: "Could not allocate a new page for database ‘Gandalf01' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Insufficient disk space? Thinking that 2 terabytes ought to be enough disk space to turn round in for most DBs, I right clicked on the database and asked for properties - it told me that the property size is not available.
One of the joys of SQL Server is that there are almost always at least two ways to skin a cat. I ran this code:
SELECT SUM(size)*1.0/128 AS [size in MB] FROM [Gandalf01].sys.database_files
It worked and told me the database size (and as I suspected, it wasn't much).
I tried the GUI again and this time the property dialog came up fine and showed me the size, as expected.
I tried my re-index again and it worked.
I've seen something like this before - the GUI refuses to tell me the database properties because the database owner has somehow got set to NULL.
EXEC sp_helpdb --reveals that the database owner is NULL and the GUI refuses to work
EXEC sp_changedbowner [SA] -- changes the owner to SA and the GUI now works.
Conceivably the owner is someone who has now left - given that Fred has left and his access removed, it doesn't seem unreasonable that his database might no longer have an owner. But how can it no longer have a size? I would be interested to know if anyone has an explanation (other than "It's a bug, Jack")