SQL Server Error 823 Troubleshooting and Resolution

SQL Server Error 823

If you are getting this error, chances are you are having a hardware failure on your server, or perhaps someone deleted one of the database files.

Assuming you took care of that situation:

Do not Detach the Database

The very easy fix to do is to simply restore the database using your backups. But if you don’t have a good set of backups, Paul Randal ( he and his wife Kim, are SQL GODS) has a great set of steps to try in his article here.

Here is the short story of how to get your database up and running if your transaction log was damaged.

Error 823 Resolution

Switch the database into the emergency/single-user mode:

alter database <dbname> set emergency;
go
alter database <dbname> set single_user;
go
dbcc checkdb(‘<dbname>’,REPAIR_ALLOW_DATA_LOSS) with all_errormsgs, no_infomsgs;
go
alter database <dbname> set multi_user;
go
alter database <dbname> set online;
go

The main thing to realize here is that if the transaction log is damaged or missing that the transactions are not going to be found in the transaction log. You are going to lose any active transactions that have not been commited to the database. The real area of interest is the REPAIR_ALLOW_DATA_LOSS. This particular arguement allows you to recreate the transaction log. Do not take this command lightly as it will lose data, so please use it only as a last resort.

 

DBA Test/Development Server Best Practices

These are things I learned from a hardware failure of a test server (you might also call it a staging area). So what’s the big deal? Developers should have all their code checked into source control and nothing critical should be on there right?

Sadly no. These are things you should do on your test server to limit your exposure as a DBA.

1. Treat the server as a production server. This includes transaction log backups, backups of user accounts, SSIS, SSRS, SQL Agent jobs, and Windows Tasks.

2. Trust, but verify. Trust the developers to be following best practices, after all your job is to make the company money and so is theirs. Periodically issue correspondence with the developers to verify that nothing is running in a production manner.

3. Get a good snapshot/system image from time to time. If someone or something destroys your server, you have something to fall back upon. This is especially true when developers are creating lots of dependencies in their code to OS level libraries.

SQL Server Null Values ~ Into the Void (pun intended)

Null values can get some people rather confused writing queries. I hope I can shed some light on that. By default, SQL Server uses ANSI Nulls. This behavior says that any value compared to null is not equal to null.

if ‘a’=null
print ‘true’
else
print ‘false’

Returns false

Here is something interesting and if you are slightly devious, what about null compared to null?

if null=null
print ‘true’
else
print ‘false’

Returns false

if null is null
print ‘true’
else
print ‘false’

Returns true

Lets turn off ANSI_Null:

SET ANSI_NULL OFF;

if null=null
print ‘true’
else
print ‘false’ 

RETURNS TRUE!!!!!

if null is null
print ‘true’
else
print ‘false’ 

Returns true

According to MSDN, if you are trying to search for or compare null values using “is null” is recommended and will return consistent results.

If you are trying to create a query and want to include nulls in the result set you have to do it explicitly like this:
WHERE name = ‘Smith’ OR name IS NULL
Or if you want to exclude nulls:
WHEREname = ‘Smith’ OR name IS NOT NULL

If you want to be tricky you could do something like:

WHERE ISNULL (name,) = ‘Smith’

In London, they have signs that say “Mind the gap”, I say MIND THE NULLS!

One handy function to deal with nulls is Coalesce, which I describe here.

MS Access Error: Too few parameters. Expected x

If you are getting this error it means you are probably working. Here is the deal: this error happens when you try to access an Access Database through ODBC or OLEDB. Chances are you are trying to use Access’s standard of using double quotes (“), replace them with single quotes(‘) and you should be off and running.

If that still didn’t fix your problem the error is probably quite literal in that you forgot to add a parameter to your query or that you are missing some search criteria.

Let me know if this doesn’t work for you and I can help you get up and running.

Altering a Table – “Saving Changes is Not Permitted”

So you may or may not know this. SQL Server 2008 and up have a restrictive setting for altering tables (to prevent users from shooting themselves in the foot). To get around this message, go to Tools>Options>Designers; then un-check “Prevent saving changes that require table re-creation”.

That is well and good but has lead me to ask the question, what changes will require table re-creation? Here is what I found:

  • Changing column sizes.
  • Setting a column to allow nulls or to not allow nulls.
  • Changing a column type to a type that does not convert well. Int to bigint is fine but nvarchar to text requires table re-creation.
  • Changing foreign key constraints.
  • Modifying identity properties of columns.

Here is something that you can do though:

  • Add  a column and it’s properties.
  • Delete a column, kind of strange since this would be destroying data. What gives Microsoft?

More on SQL Server Denali!

This guy has a great blog article on high availability on the latest version of SQL Server. I highly recommend reading it. Unlike previous versions, you can query all the nodes and you aren’t limited to a two node configuration like before! I have another article, a very terse one, here: http://wp.me/p175D9-3o

SQL Server Denali: HADRON ROCKS. | Brent Ozar – Too Much Information | Brent Ozar – Too Much Information.

SQL Server – Restore Master Database

I have been working toward a SQL Server certification (70-432). This has resulted in me getting quite a good knowledge of how things work. Here is something interesting I have learned:

Steps (this only works if you have a good backup of Master)

1. Stop the SQL Server instance.

2. From a command prompt (must be running the command prompt as administrator), go into the Binn directory of the SQL Server instance and run “sqlservr -m”. This will start up a special single user mode.

3. Start another command prompt as administrator. Run sqlcmd and then perform the restore, something to the effect of: restore database master from backupdevice if you are using preprogrammed backup devices (something that is recommended).

4. Start SQL Server up normally.

5. Restore MSDB, then Model if necessary.

If you don’t have a valid backup, shame on you! But you can run setup again which will rebuild Master but you have to reattach all databases.