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.

 

Advertisement

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.

 

SQL Server Distributed Queries!!!

Distributed queries in SQL Server are a blessing and a curse. What can I say, they are fascinating. For those of you who are not familiar with distributed queries, here is the crash course.

What is a distributed query?

So here is the deal, a distributed query is query that relies on data that lives on another data source. The classic example of distributed queries is the Linked Servers in SQL Server. You can also use the OpenQuery and OpenRowset stored procedures to get to the remote data. The difference between OpenQuery and OpenRowset is that the OpenQuery uses linked servers (linked servers store the connection string data); whereas OpenRowset requires the entire connection string to be passed.

For sanity purposes I like the OpenQuery it is cleaner and is more abstract.

Example:

—What not to do!

SELECT RTRIM(name), id FROM OPENQUERY(linkedserver, ‘SELECT name,id FROM foo’) WHERE id = 24234

–Much better!

SELECT name, id FROM OPENQUERY(linkedserver, ‘SELECT RTRIM(name),id FROM foo WHERE id = 24234’)

In the above example something might be popping in your head, and you’re right, it is a nested query! The theory that I use often is this: All you are doing is creating a temporary table from one server to use on another. All of the processing inside the OpenQuery occurs on the linked server first and then fills in a temporary table on the SQL Server from whence you are executing the distributed query. SQL Server then takes that temporary table (results of the OpenQuery) and runs the remainder of the query.

Here is the order of execution, prepare to have your mind blown:

1. OPENQUERY
2. FROM
3. WHERE
4. SELECT

Here is the mind blowing part, this also relates to performance. Let me explain. The more criteria (restricting the number of rows and fields) you impose in step 1, makes all the remaining steps faster. Likewise any data manipulation you do in step 1 will speed up the entire query. If you have more than one OPENQUERY linking to eachother then join them if possible (they have to be from the same data source), see the example below. Try to get to one OPENQUERY. Remember what I said from earlier, it downloads all that data and puts it into temporary tables. You could take 5 Mb of data transferred from a couple OPENQUERY’s and combine them to be under 1 Mb, this has a huge difference in network traffic and computing time on the SQL Server.

–This is really bad.

SELECT * FROM OPENQUERY(linkedserver1,’SELECT name, id from foo’) AS foo INNER JOIN OPENQUERY(linkedserver1,’SELECT name2, id from goo’) AS goo

ON foo.id = goo.id

–This is much better.

SELECT * FROM OPENQUERY(linkedserver1,’SELECT name, name2 FROM foo INNER JOIN goo ON foo.id = goo.id’)

Gotcha’s with Eloquence and the SQL/R ODBC

Only inner joins are accepted in the very old style: FROM foo, goo WHERE foo.id = goo.id

You can only link 5 tables per OPENQUERY

If you have any questions, just post a comment.