How to install SQL Configuration Manager

When moving Sharepoint Foundation 2010 databases, the official advice is to set up a SQL Alias so that the Web Application Servers point at the new SQL server while using the old SQL Server name.

In order to do this, you need to have installed SQL Server Configuration Manager on the Web and Application Servers.

It's very easy to install SQL Server Configuration Manager without installing all the rest of SQL Server: just install the 'Client Connectivity Tools'. Ensure you install the version of the tools which matches the version of SQL Server installed on your database server.

• Choose Install -> New installation or add features to an existing installation.
• Choose all defaults until you get to the Feature Selection tab
• Select Shared Features -> Client Tools Connectivity
• Choose all remaining defaults and confirm installation

And that's it. No restart required. I did find that CPU on the server shot up to 100% for much of the install in our QA environment, so I don't recommend doing this in anger during office hours. Leave it to out of hours if you can.

Exporting employeeStartDates and employeeEndDates to SQL in FIM

Twice in the past six months, I've gone looking for this answer. The second time round, I came across my own post to Technet, which was a little embarrassing in a sitting-alone-at-my-desk manner.

Anyhow. Before I go looking a third time, this is how to get an employeeEndDate and an employeeStartDate out of the FIM Metaverse and into SQL date or date-time fields.

In VB.Net. Other languages exist. This is just the code for the Advanced Flows that you'd set up from FIM Synchronization Service: it relies on there being nothing more than the relevant dates. You may wish to build in more checks.

Select Case FlowRuleName
            Case "exportNonEmployeeEndDate"
                If (mventry.ObjectType.Equals("person")) AndAlso mventry("employeeEndDate").IsPresent Then
                    Dim NonEmpEndDateTime As DateTime = Convert.ToDateTime(mventry("employeeEndDate").StringValue)
                    Dim NonEmpEndDate As String = NonEmpEndDateTime.ToString("yyyy-MM-dd")
                    csentry("enddate").Value = NonEmpEndDate


                End If

            Case "exportNonEmployeeStartDate"
                If (mventry.ObjectType.Equals("person")) AndAlso mventry("employeeStartDate").IsPresent Then
                   Dim NonEmpEndDateTime As DateTime = Convert.ToDateTime(mventry("employeeStartDate").StringValue)
                    Dim NonEmpEndDate As String = NonEmpEndDateTime.ToString("yyyy-MM-dd")
                    csentry("startdate").Value = NonEmpEndDate


                End If

        End Select

As with all code on the internet, check it in the Development System before you throw it into the Live environment. And take backups before you get going. Just in case.

Also, has it really been a year? Sheesh. Sorry about that. 

Collation

Collation can be set at all different levels in SQL, from the server, down to the column level. And it's a pain when it does't match up. Fortunately, there is a fast workaround for T-SQL queries.

SELECT a.employeeid, a.name, b.employeeid, b.name
FROM employee a, employeedetails b
WHERE a.employeeid = b.employeeid

If the collation on employeeid in table a does not match the collation for employeeid in table b, there will be a nasty error, along the lines of "Cannot resolve collation conflict for equal to operation"

Easy enough to deal with - tell SQL to use what *should* be the default for the database(s) in question. This worked when I was dealing with two databases with totally different collations on the same server - the default server collation didn't trickle down to a database imported from another, older, server.

SELECT a.employeeid, a.name, b.employeeid, b.name
FROM employee a, employeedetails b
WHERE a.employeeid COLLATE DATABASE_DEFAULT = b.employeeid COLLATE DATABASE_DEFAULT

This is a lot simpler to implement than any solution that involves trying to re-collate one of the columns to the other table/database/column's collation. It's vastly more generic.

How to Estimate the size of a Nonclustered Index

Microsoft have a very long list of equations to work out, including the use of logarithms.

So I put it into a spreadsheet.

The spreadsheet only deals with non-unique non-clustered indexes at the moment, because that's what I was trying to work out. It should be reasonably simple - plug in the numbers in the cells adjacent to the bold text, make sure that you're calculating the correct number of non-leaf levels in cell B53, and make sure to use it in conjunction with the link above.

This spreadsheet is provided as-is, without warranty. It's for estimating the size of an index - actual size of indexes may vary.

Updated 30th June 2025 with a new link

Execute xp_cmdshell

Now: we don't enable xp_cmdshell lightly, as it allows a degree of control over the operating system that renders the SQL server vulnerable. However, when we do enable it, it can only be executed by members of the sysadmin role. Sometimes, this isn't quite what we want: we only want the account to be able to execute xp_cmdshell, not to be able to muck about with other SQL system settings.

So.

First, make sure that you have SA access on the server yourself. Then make sure the account that you want to use has access to the relevant databases on the SQL server. It can be a SQL user account or a Windows account: it doesn't matter. For the purpose of this snippet, I'm using a SQL account called 'user'. I'm going to go out on a limb, and guess that you already know how to create an SQL user account.

Then, grant the user execute rights on xp_cmdshell

GRANT EXEC ON xp_cmdshell TO [user];

Next, we need a proxy account which does have sysadmin access, and we'll use this account to run xp_cmdshell to run calls by users who are not members of the sysadmin role. This account must be a windows account. Give it access only to those drives and folders which it requires for the purposes of executing anticipated xp_cmdshell calls.

The proxy created is ##xp_cmdshell_proxy_account##, and in order to create it, you need both the Windows Login which has sysadmin access and its password.

EXEC sp_xp_cmdshell_proxy_account ‘domain\sysadminlogin’, ‘password’;

Check that this has created by querying the sys.credentials table

SELECT * FROM sys.credentials

Finally, verify that the SQL user has access to execute xp_cmdshell with the following code

EXECUTE AS LOGIN = 'user'
GO
EXEC xp_cmdshell 'whoami.exe'
REVERT

The result should be the Domain\Sysadmin account specified earlier.

How far has my backup or restore got?

I had a backup that was hanging: eventually I ended up restarting the server. It wasn't running through the GUI of SSMS, though. It was running from a process on the server, some 3rd party software. Stopping the software didn't stop the backup - it wouldn't. It's a file operation, and these things are pretty-much unkillable within SQL. Stopping the backup was possible solely by restarting the Full Text Search Engine on the Server (and, because there were two instances of SQL, both instances of FTE had to be restarted). However, I didn't quite twig this early enough in the process. It proved impossible to stop SQL because the backup was hanging. Eventually we powered down the server. However, I could tell how far the backup had got. A very nifty bit of script:

SELECT r.session_id,r.command,

CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],

CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],

CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],

CONVERT(VARCHAR(1000),(

SELECT SUBSTRING(text,r.statement_start_offset/2,

CASE WHEN r.statement_end_offset = -1 THEN 1000

ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)

FROM sys.dm_exec_sql_text(sql_handle)))

FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

It's based on the sys.dm_exec_requests view, so works for SQL 2005 and above. The code was here but had a slight typo which meant it wouldn't work for restores in the first instance.

It still doesn't solve the problem of SQL sitting on 100% for a restore for an age and a bit - this is due to the way SQL restores work. First, they restore the data to the files, in the data-copy phase: this is the bit that racks up to 100%. Then, any committed transactions that were present in the transaction log at backup time are rolled forward, and any incomplete transactions are rolled back. This is the redo and undo phase, and SQL doesn't record the progress of this. If the log file is particularly large, and the database is in SIMPLE recovery, this can take quite a while to work through...

Code Snippet

To execute sp_helprotect on all databases on a server, saving the results into a temporary table, and then displaying the results, including the database name. Makes use of sp_msforeachdb, an undocumented stored procedure which is very nifty for cycling through each database on a server.

It took me a while to work out how to do what I wanted to do...

This code is provided as-is, with no warranties. Test it before using it in earnest: and if you want to share the joy, please credit me. I wouldn't have managed it without the code here.

CREATE TABLE #temptable

(

[Owner] VARCHAR(50),

[Object] VARCHAR(100),

grantee VARCHAR(50),

grantor VARCHAR(50),

protecttype VARCHAR(50),

[Action] VARCHAR(50),

[Column] VARCHAR(50),

dbname VARCHAR(50)

)



DECLARE @STATEMENT VARCHAR(500)



SET @STATEMENT = 'Use [' + '?' + ']
INSERT INTO #temptable
([Owner],
[Object],
Grantee,
Grantor,
ProtectType,
[Action],
[Column]
)
EXEC (''sp_helprotect'')
UPDATE #temptable SET dbname = db_name()
WHERE dbname IS NULL'



EXEC MASTER.dbo.Sp_msforeachdb @command1=@STATEMENT



GO



SELECT *

FROM #temptable

ORDER BY dbname



GO



DROP TABLE #temptable
GO

SQL 2008 Setup SP1

If one gets the error message "The current SKU is invalid" while trying to add in another node to a SQL 2008 Cluster (and, perhaps significantly), one on which the other node(s) are running SP1, it is possible to get round this without slipstreaming.

Check, in Control Panel Add/Remove Programs, that the SQL 2008 Server Setup application is installed. If it is, back out of the node install, and run SQL 2008 SP1. It seems counter-intuitive, since SQL 2008 has not yet been installed. It does however work. I didn't stop the installer when I ran SP1, so I had to restart the node: I suspect that had I stopped the installer, I would not have needed to restart the node.

Once you've got the installation to work, you'll still need to service pack SQL Server itself, however, since you're on the passive node, it's pretty simple. And, since the installation is fresh, you won't need to restart again.

Microsoft error link.

Microsoft fix link.

Anything, but anything, to avoid slipstreaming the install.

Database Mail and Reporting Services: a high level how to

Preparation:
Set up SMTP Mail account, ensuring that the SQL Server's IP Address is whitelisted against the internal relay if required (this allows emails to be sent internally).
Ensure AWE addressing is enabled.

SP_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
SP_CONFIGURE 'awe enabled', 1
GO
SP_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO

If necessary, follow the instructions to enable the Lock Pages in Memory Option, according to company security policies, and restart SQL.

Configuration

Easiest to do this using the Database Mail Configuration Wizard. The following steps make more sense if you step through the wizard at the same time.

• Setup Database Mail
• Enable Database Mail
• Create a new profile for SQL Server Mail
• Use the add button to bring up the dialogue to input the account details.
• Add in the SMTP details: your port and SSL settings should be double checked with the sysadmin.
• Chose 'Basic Authentication' to log onto the SMTP server, which uses the account that you've just specified, rahter than the SQL Database Engine or Anonymous Authentication (Anonymous Authentication should be avoided).
• Make this the default mail profile, and either set the privacy for specific accounts, or make it public.

Enable the Database Mails XP Parameter:

SP_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
SP_CONFIGURE 'Database Mail XPs', 1
GO
SP_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO

Then test that the email is running OK, either by executing the following store procedure, or by right clicking on Database Mail and choosing 'Send Test Email'. Check the results in the Database Mail Log (it's an option in SSMS and can be found in the same spot as the SQL Agent Logs and the SQL Server Logs). Using the Database Mail Log is simpler than using the Database Mail Troubleshooting option (which didn't actually work for me).

USE msdb
GO
EXEC SP_SEND_DBMAIL @profilename = 'ProfileName'
@recipients = 'sweetsql@blogspot.com'
@subject = 'Test SQL Mail'
@body = 'Hurrah, this test has been successful!'
GO

Finally, it's necessary to configure Reporting Services to recognise the existence of the email, so that it can be used for an automatically generated scheduled emailed report. Use the Reporting Services Configuration Manager, add in the details of the mail profile and the smtp server, and save.

The reports that are emailed need to use a data source with saved credentials, rather than a data source which passes on the credentials of the user viewing the report. Happily, parameterised reports can be set up to use a specific set of parameters for emailing purposes: this dialogue can be found when you add an (email) subscription to the report.

Self-Blocking SPIDs

My nightly DBCC CheckDB job is failing on the CRM Server, when it checks the MSCRM database. It fails because the SPID is blocking itself: and there's no point in trying to kill the SPID, because that just hangs. The self-blocking SPID also blocks everything else, rendering it almost impossible to stop the Scheduled Job via SSMS, as SQL Agent doesn't load. The self-blocking SPID also renders it impossible to stop SQL via SSMS or SQL Configuration Manager: the only way to stop SQL seems to be by killing the process in Task Manager (and that can't possibly be healthy).

There is, if one checks the sys.dm_os_waiting_tasks DMV, a lot of action going on in tempdb while this process is failing: the blocked resource is blocked on a LCK_M_IX wait_type, which is an intent exclusive lock. The memory involved in the process seems to be rather intensive (the memory used by SQL suddenly skyrockets). The executing SQL that is involved is really quite interesting: and not many people seem to have come across it based on a quick google about the place.

DECLARE @BlobEater VARBINARY(8000)
SELECT @BlobEater = CheckIndex (ROWSET_COLUMN_FACT_BLOB)
FROM { IRowset 0xE093909D00000000 }
GROUP BY ROWSET_COLUMN_FACT_KEY
>> WITH ORDER BY
ROWSET_COLUMN_FACT_KEY,
ROWSET_COLUMN_SLOT_ID,
ROWSET_COLUMN_COMBINED_ID,
ROWSET_COLUMN_FACT_BLOB
OPTION (ORDER GROUP)

So far, I have drawn a complete blank. However, I've decided that trying to increase the size of TempDB might be a sensible thing to do, simply because so much that is being blocked involves tempdb, and tempdb itself was about 8MB in size.

SQL Configuration Manager Suddenly Unavailable

Last night, I installed SP1 onto a SQL 2008 Cluster. This morning, looking at another issue, I decided to mosey into the Configuration Manager, in order to double check which Ports SQL was operating on.

Imagine my horror when I got the following error, regardless of the account I used to try and open the tool:

"Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers or above with SQL Server Configuration Manager. Invalid class [0x80041010]"

Googling around suggested several solutions involving changing folder permissions, and also running something called mofcomp. This in particular seemed relevant to my situation, where an installation had recently been run on the server. It would seem that, occasionally, the .mof files get corrupted during an installation. They're Managed Object Format files, and used for storing system configuration information.

They can be found in the Shared folder for the 32 bit components of your SQL Server, for SQL 2005 and above. Thus, on a 64 bit cluster, they were here:

C:\Program Files (x86)\Microsoft SQL Server\100\Shared

And running this command in cmd fixed the problem. Phew.

C:\Program Files (x86)\Microsoft SQL Server\100\Shared>mofcomp "C:\Program Files (x86)\Microsoft SQL Server\10\Shared\sqlmgmproviderxpsp2up.mof"

Oooh! Squee! New Tool

Microsoft® SQL Server® 2008 R2 Best Practices Analyzer.

Tells you what could be improved in your installation, and how to go about it

• Gathers information about a Server and a Microsoft SQL Server 2008 or 2008 R2 instance installed on that Server
• Determines if the configurations are set according to the recommended best practices
• Reports on all configurations, indicating settings that differ from recommendations
• Indicates potential problems in the installed instance of SQL Server
• Recommends solutions to potential problems

Sweet. Note: there have been some problems with installing it owing to Workgroups problems and Kerberos issues. Solution here.

How to transfer logins and passwords

A couple of years ago, when I was a very junior DBA, we upgraded our main database (and everything else on the server), which contained all the information about everything at the University that employed me at the time, (students, faculty, staff, courses: you name it, it was in there) from SQL 2000 to SQL 2005, moving it across servers at the same time. This database was used by many members of staff on a daily basis, via a custom built Delphi interface. When it came to migrating all the usernames and passwords, we made use of this interface to log the users' keystrokes to find out their passwords, and input this all into a great script to recreate them on the new server. Where a user had not changed their password from the inital password given, or where a user's password did not fit the new server's security policies, or a user had not logged in during the window we were monitoring keystrokes, we reset the password. We then forced all users to change their passwords on their next login, and it worked pretty well. It wasn't my decision to use this method, and I'm not sure I'd chose it now: it all depends on the business needs.

At the moment, I'm setting up database mirroring. I need to transfer over the logins from my principal, source server to my mirror server. These are all system accounts, where I know the password (heck, it's in the password safe), but I don't want to create them all manually. Nor do I want to use IDERA's brilliant SQL Permissions tool - I don't need all the user account information - just to script out the logins themselves. After all, the user account information will be contained in the databases that I'm mirroring, once I've restored them to the server. Ideally, I'd like to script them all out in one go. Ideally, I'd like them to have the passwords in the script too.

Fortunately, I can: and without knowing the passwords. Microsoft have created a stored procedure, sp_help_revlogin, that can be used to transfer logins between instances of SQL 2005 and SQL 2008 on different servers (For SQL 2000 and SQL 7.0, check this KB article). It gets created in the master database (yes, I know, goes against all received wisdom about NOT making changes to the master database), along with a second stored procedure, sp_hexadecimal. Executing sp_help_revlogin results in a script that contains all the logins on the server, their default databases, and hexadecimal representations of the binary strings that hold login and password information. Run the script generated by sp_help_revlogin on the destination server, and the logins will be created. You will need to have databases with the correct names on the destination server, which need not be restored versions of the source databases, as the script will not run without the presence of the logins' default databases. They will not have access to connect to any of the databases, as user accounts have not been linked to the logins, but it's easy enough to do that (and there are many scripts available). If you can, it makes sense to restore the databases from the principal server to the mirror server, leaving them in a fully functional state, before running the logins script. And, naturally, if you're not setting up mirroring, then you would do this anyway. With mirroring, you need to make a call: create empty versions of the databases, and restore over them, or restore the databases twice over.

That I could, essentially, copy the logins and passwords across, without having to monitor anything, or know the passwords, was a bit of a revelation. I wish we had known this two years ago.

Credentials, Proxies, and SSIS SQL Agent Jobs

What I want to do:
Give one user access to see and execute one SSIS package as a SQL Agent Job, but none of the rest. Although the package exports data from one database on Server A to a new database on Server B, this does not count as a multi-server job in SQL terms. I've assumed that the package itself has been created and works as a SQL Agent Job under a sysadmin login, but I don't want to give this guy sysadmin access. He's a developer.

1. Create the credential, based on an existing login, on Server B. If you use a Windows login, it will want the correct password for that login, so don't get clever and make one up (can you spot the mistake I made?).





















2. Make sure that the credential's login has the correct access on Server B
3. Create the proxy, point it at the credential.
4. Add principals to the Proxy i.e. the accounts that you want to have the rights of the proxy when executing the SQL Agent Job.



















5. Create the job, and make the user's Login the owner of the job.
6. Give the user's Login the following rights in MSDB: db_ssisoperator and SQLAgentUserRole
7. Test.

I found it very helpful to test this process out using my non-sysadmin Windows account: it meant I could test out the process while being able to see all error messages on my own machine.

Doubtless, there are better explanations of how to do this out there: and I am not sure what I would do if I needed to allow multiple users to execute the same package under such constraints. I am sure someone will let me know...

But what if the FTS won't?

When building a Full Text Search Catalog and Index, it is a Good Idea to check that the search works properly at the end of it, by using something along the following lines:

SELECT *

FROM tablename

WHERE Freetext (COLUMN, 'some text')

Now, I knew the create script worked, as I'd used it to create my Very First Full Text Catalog and Index only a week ago. I knew the select would work, because, again, I'd tested it. SQL 2008 SP1 was about to have a bit of a disagreement with me about what was, and what wasn't, going to work.

Not so.

SQL Server encountered error 0x80070005 while communicating with full-textfilter daemon host (FDHost) process. Make sure that the FDHost process isrunning. To re-start the FDHost process, run the sp_fulltext_service 'restart_all_fdhosts' command or restart the SQL Server instance.


and in the Logs:


SQL Server failed to communicate with filter daemon launch service (Windows error: Windows Error: hr = 0x80070005(failed to retrieve text for this error)). Full-Text filter daemon process failed to start. Full-text search functionality will not be available.

Running sp_fulltext_service 'restart_all_fdhosts' did not improve matters: the command ran happily, but the error persisted. Restarting SQL didn't help either (heck, it's a DEV box, and not yet ready for the Developers).

Searching for 0x80070005 told me that the problem was to do with permissions: and the solution turned out to be setting the SQL Full Text Filter Daemon Launcher Service to run under the same account as the SQL Server Service.

The SELECT statement then deigned to execute: but did not return any results. Rebuilding the index did not help matters: however, dropping the FULLTEXT index, the index, and the Full Text catalog did work. T-SQL here for posterity for next time I have this problem.



DROP FULLTEXT CATALOG CatalogName

DROP FULLTEXT INDEX ON dbo.TableName

DROP INDEX IndexName ON dbo.TableName

When the autoshrink/autogrow won't show in SQL Reports

...Try the script below, from this discussion on MSDN. It worked beautifully for me when it turned out that there were well over a million rows of autogrow/autoshrink data, and thus the autogrow/autoshrink data from SQL 2008's built in reports wasn't going to show correctly.

I then changed the data file size, so that the thing grew in one BIG chunk, gave it lots of elbow room for more data to fill up the file, and left it well alone.

DECLARE @filename VARCHAR(100), @filenum int

SELECT @filename = CAST(value AS VARCHAR(100))

FROM fn_trace_getinfo(DEFAULT)

WHERE property = 2

  AND traceid = 1

  AND value IS NOT NULL



-- Go back 4 files since default trace only keeps the last 5 and start from there.

SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'



--Check if you have any events

SELECT gt.HostName,

       gt.ApplicationName,

       gt.NTUserName,

       gt.NTDomainName,

       gt.LoginName,

       gt.SPID,

       gt.EventClass,

       te.Name AS EventName,

       gt.EventSubClass,

       gt.TEXTData,

       gt.StartTime,

       gt.EndTime,

       gt.ObjectName,

       gt.DatabaseName,

       gt.FileName

FROM [fn_trace_gettable](@filename, DEFAULT) gt

JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id

WHERE EventClass in (92, 93, 94, 95)

and gt.DatabaseName = 'tempdb'  --Change the DB Name here

ORDER BY StartTime;

Taking it futher

The awesome SQLRockStar came out with a nifty tip about fragmented Virtual Log Files. SQL has this tendency to split the transaction log file out into different chunks. It might be physically contiguous, but, logically, it is fragmented, and this affects performance.



The tip teaches us how to use Policy Based Management in SQL 2008 to work out which databases have extensive Virtual Log File fragmentation. So far, so good. But hark! I hear you cry! I only have one SQL 2008 Server. Everything else is still SQL 2000 or SQL 2005. There is no Policy Based Management tool there.


Panic not. If you have set up a Central Management Server in SQL 2008, and registered your servers within it, you can evaluate the Policy against all your servers. Just right click on the group you want to check, pick 'Evaluate Policies', choose your source (i.e. the SQL 2008 box on which you set up the initial policy), and let it run. It tends to look as though it's stuck if one of the servers in the group you've chosen isn't running SQL or is turned off, but leave it to run, and, eventually it'll stop trying to connect to that box, and move onto the next one.

Once you know what's up, you can follow the instructions here. I did make up a handy script to make life a little easier, and I cannot emphasise enough that you must back everything up properly before you get going. It will save a vast amount of heartache in the long run. Honest truly.

USE [databasename]
-- Backup the Log File

BACKUP LOG [databasename] TO devicename
GO

-- Set recovery to simple mode, otherwise the log file refuses to shrink

ALTER DATABASE [databasename]
SET recovery simple
GO

-- Shrink the log file

DBCC shrinkfile ( transactionloglogicalfilename , truncateonly )
GO

-- Grow it in ONE BIG STEP
ALTER DATABASE [databasename]

MODIFY FILE

(

NAME = transactionloglogicalfilename

, SIZE = newtotalsize --MB

)
GO

-- Set recovery back to full mode

ALTER DATABASE databasename
SET recovery FULL
GO

Finally, run a full backup, so that all future transaction log backups have something to work from.

(I used the instant SQL formatter to format the SQL for this post. It looks, um, interesting....)

Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

So, having decided to use the built-in Standard Reports on SQL 2005 to check how one of my databases had been auto-growing and shrinking, as the free space looked a little suspect (as did the fact that the database had shrunk between Friday morning and Monday morning for no apparent reason other than a reindex...), I got the error in the title:

Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

Fair enough. According to SQLSkills, this is a compatibility issue. I was using SQL 2008 Management Studio, and was checking a SQL 2005 database. Usually, this doesn't cause a problem, as the reporting tools were first introduced in SQL 2005.

Unfortunately, this database turned out to have a compatibility level of 80 - SQL 2000. Running the report in SSMS 2005 gave the following error:

Unabel to display the report because the database has a compatibility level of 80. To view this report, you need to use the Database Properties dialog to change the compatibility level to SQL 2005 (90).

This has the advantage of being rather more explicit, but none the less irritating...

Sometimes, it ought to be obvious....

Over the Christmas break, we moved our Raiser's Edge database files, so they weren't sitting on the c:\ drive (where they installed as default), but on the data drive for that server. One of my colleagues used the BlackBaud Manager for the system, rather than me doing anything clever in the back end.

At which point, the backup job, created via BlackBaud, failed.

First it failed owing to permissions. It decided that it couldn't connect to the (local) server. Easy enough to fix.

Then it failed owing to that hoary old chestnut "[SQLSTATE 42000] (Error 22029)". You know. That really useful error message that has you looking in six different places to find out why it failed: diskspace, file locked, unable to access backup file location etc etc etc. No SQL maintenance plan that I could right click and 'View History' - the thing's done by a T-SQL string invoking xp_sqlmaint. Well, the string *looked* OK: same as the version that did execute successfully when run manually.

Yup. String looked fine. Beautiful syntax. Until the second cup of coffee kicked in.

It helps if you're trying to backup a database that exists! In the process of moving the database files about, the database got renamed. The user connections got updated to reflect this new database name, but not the backup job.

Backups are now running.

Beware Third Party Software: it can do odd things

DBCC CHECKDB on (naturally) the largest database we have, the one which supports the mainwebsite and all the forums, failed.... PANIC! PANIC!

DBCC results for 'MyDB'.Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:576726) with latch type SH. 38
(failed to retrieve text for this error. Reason: 15105) failed.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object
.CHECKDB found 0 allocation errors and 1 consistency errors in database 'MyDB'.
is the minimum repair level for the errors found by DBCC CHECKDB (MyDB).

Msg 5269, Level 16, State 1, Line 1
Check terminated. The transient database snapshot for database 'MyDB' (database ID 6) has been marked suspect due to an IO operation failure. Refer to the SQL Server error log for details.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

The SQL Error Logs showed a sad tale:

A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a4000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a8000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a6000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199aa000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a0000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a2000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199ae000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

The operating system returned error 1784(failed to retrieve text for this error. Reason: 15105) to SQL Server during a write at offset 0x00000074b48000 in file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Error: 17053, Severity: 16, State: 1.O:\DATA\MyDB.mdf:MSSQL_DBCC24: Operating system error 1784(failed to retrieve text for this error. Reason: 15105) encountered.

Enough to give anyone the screaming habdabs, right? SQL 2008, on the SAN, a really important database, and it can't complete a DBCC CHECKDB owing to I/O issues.

Oddly, every other DBCC CHECKDB (bar one, which showed page errors) completed successfully. The SAN guy couldn't find any problems with the SAN I/O, and there hadn't been any changes to the switches.

What else? Well, we use Diskeeper 2010 to keep the database drives defragged (it works rather well), and it would seem that the new Intelliwriter Option causes DBCC CHECKDB to react badly, as the file expands to allow for non-fragmented writing, and DBCC CHECKDB doesn't expect this. I am not the only DBA who has had the problem. Diskeeper have produced a fix, which is available here, but in the short term, disabling Intelliwriter corrects the issue - and DBCC CHECKDB will complete happily and there is no need to panic.

Still, my next move will be to write an SSIS package to connect to all the servers and run DBCC CHECKDB on each database in turn....