SQL Server Database Corruption

I’m currently studying for the 70-450 exam to obtain my MCITP in SQL Server Database Administration. I wanted to get a better understanding for SQL Server’s transaction log. I wanted to see if I could corrupt a databases MDF and recover data even after the last t-log backup. This is where the tail backup comes in.

The tail log backup can recover your data even if the MDF is corrupted. This is because it stores all the transactions since the last full backup and t-log backup. If the MDF is corrupted your t-log should still be able  to be backed up, unless that is corrupted too. If the database is not truly corrupt, a backup log statement with the norecovery option will work just fine. This puts your database into recovery mode and prevents users from going into the database, preserving the state of the database so the restoration can be performed.

Unfortunately this does not work when the database is corrupted. You have to use the option continue_after_error, this forces the backup to occur.

You can then restore all data back to the database using your full and t-log backups.

Immediately after bringing the database online, be sure to make a full backup. This way you have a good restore point and if you routinely are backing up your t-logs, your next transactional backup will work just fine.

Advertisement

SQL Server Rebuild or Reoganize Your Indexes – It depends, but here is a script I use.

So if you are like me, chances are you have that one server where the reindexing or reorganizing is taking a long time to finish. I came up with a solution that has helped me reduce the time it takes to maintain my indexes.

Background

Rebuilding indexes basically recreates an index from scratch (retrieving the data from the table again) whereas reorganizing is a process that relocates the pages of an index.

Rebuilding an Index

Rebuilding an index is typically an offline process, unless you have the Enterprise edition of SQL Server with the online flag set (check BOL for your particular version). While non-clustered indexes are being rebuilt, the table is available. However, rebuilding clustered indexes results in the the table being offline.

Reorganizing an Index

Reorganizing is an online process. It can reduce your fragmentation, but doesn’t always. Remember it is only moving pages around and not actually recreating the pages from data in the table.

Best Practices

Based on what I have read from various experts of SQL Server, rebuilding is recommended for indexes over 30 percent fragmentation. Under 30 percent, the index should be reorganized.

It is also recommended to not do anything to the indexes that are under 100 pages in size as they will not gain anything from a rebuild or reorganize. Of course, sometimes you might need to rebuild a small index, if for example, one of the pages has data corruption.

What the Script Does

Based on the best practices I mention above, it scans the sys.indexes view of every database on your SQL Server instance and then creates (and executes) rebuild or reogranize command for each index that has more than 5% fragmentation and more than 100 pages in size.

It uses the server default fill factor, which you can set in the properties of server instance.It currently DOES rebuild clustered indexes, so if you are running this be sure to run it in your maintenance window.

I encourage you to read through and understand the script as well as test it in your test environment before running it on production to get a feel for its behavior.

Let me know if it helps you out, I know it has helped me get more done in my maintenance window.

DECLARE @rebuildThreshold FLOAT;
SET @rebuildThreshold = 30.0;

CREATE TABLE #indices (
dbname VARCHAR(300),
tablename VARCHAR(300),
indexname VARCHAR(300),
fragmentation FLOAT
)
DECLARE @db SYSNAME;
DECLARE @sql VARCHAR(2000);

DECLARE curs CURSOR
FOR
SELECT  name
FROM    sys.databases
WHERE   name<>’tempdb’  AND state_desc <> ‘OFFLINE’ AND is_read_only = 0

OPEN curs

FETCH NEXT FROM curs INTO @db;
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql = ‘SELECT ”’ + @db + ”’,
”[” + SCHEMA_NAME(schema_id) + ”].[” + OBJECT_NAME(i.object_id, DB_ID(”’ + @db + ”’)) + ”]”,
i.name AS indexname,
ips.avg_fragmentation_in_percent
FROM    sys.dm_db_index_physical_stats(DB_ID(”’ + @db + ”’), NULL, NULL, NULL, NULL) ips
INNER JOIN [‘ + @db + ‘].sys.indexes i ON ips.object_id=i.object_id AND
ips.index_id=i.index_id
INNER JOIN [‘ + @db + ‘].sys.objects o ON i.object_id = o.object_id
WHERE   page_count>=100 AND
avg_fragmentation_in_percent > 5’;

INSERT  INTO #indices
(
dbname,
tablename,
indexname,
fragmentation
)
EXEC (
@sql
);

FETCH NEXT FROM curs INTO @db;
END
CLOSE curs;
DEALLOCATE curs;

DECLARE curs2 CURSOR
FOR
SELECT  CASE WHEN fragmentation<@rebuildThreshold THEN ‘ALTER INDEX ‘ + indexname + ‘ ON [‘ + dbname + ‘].’ + tablename + ‘ REORGANIZE;’
ELSE ‘ALTER INDEX ‘ + indexname + ‘ ON [‘ + dbname + ‘].’ + tablename + ‘ REBUILD;’
END
FROM    #indices
WHERE   indexname IS NOT NULL;

OPEN curs2;

FETCH NEXT FROM curs2 INTO @sql;
WHILE @@FETCH_STATUS=0
BEGIN
EXEC (@sql);
PRINT @sql;

FETCH NEXT FROM curs2 INTO @sql;
END

CLOSE curs2;
DEALLOCATE curs2;

DROP TABLE #indices

SQL Server Delete From vs. Truncate Table

There are some very important distinctions between these that I feel I need to explain.

Delete From
First and foremost delete is used for deleting a limited number of rows from a table. This is not to be used for deleting all rows in a table.

Another important note is that this is a logged process meaning it writes this delete into the transaction log. Which is good if you ever need to recover those rows.

Also deleting rows does not reset or reseed, more technically, any identity columns. So deleting the last row will not decrement the identity column.

Truncate Table
So this function is to delete all rows in the table.

Unlike Delete, this operation is very efficient, mainly because it is not a logged process. Use this for very specific reasons, testing, repeating processes, etc.

Also it will reseed the identity column in your table. So for example, it will delete all the rows in your table and the identity will be reset to its initial value, by default this is the number 1.

Cannot fetch a row using a bookmark from OLE DB provider (Msg 7333, Level 16, State 2)

Linked Server Msg 7333

If you are getting this error and are trying to resolve it. Let me give you a little background information. A bookmark in this error is a left over from SQL Server 2005 SP1 and earlier. Bookmarks after 2005 SP1 is really a referred to as a key lookup, not to be confused with primary/foreign keys. From now on I am going to refer to bookmarks as key lookups so we are uniform and using the modern terminology.

Key lookups are objects within a query that refer to a data page (where the data is actually stored in the database) from a non-clustered index.

When SQL Server is executing DML (Insert, Update, Delete) against a linked server successfully for days, months, or even years and then fails with this error:

 Cannot fetch a row using a bookmark from OLE DB provider (Msg 7333, Level 16, State 2)

Something in the query execution plan is wrong. So far I have only run into this problem using linked servers and it probably has to do with resource management within SQL Server. Perhaps someone could elaborate on this that has more experience than I. But within the query’s execution plan, SQL Server decides to switch to a different index to run the query. The query then fails because it is using an index that is lacking information to complete the request.

Resolution

Ultimately, you have to do the following:

1. Look at your where criteria in your query that is dependent on the linked server.
2. Review indices on the table, that the where criteria is referring to.
3. Is your one of your criteria covered by more than one index?

If you answered yes to number 3, there is a good chance that the behavior I am describing is actually causing your problem.

To diagnose this, disable one of the indexes  that is in question.

NOTE: Never disable the clustered index, you will prevent data access to the table.

Run the query again, and it should work, if not rebuild the disabled index to bring it back online and then disable the other index. If the query works you are in business. Talk with your database administrator, developer, or vendor of the application to come up with a good solution. But this will temporarily get your query to work.

SQL Server Reporting Services ~ MultiValued Parameters

This is a really good article on multivalued parameters in a stored procedure. Very good read for SQL Server Reporting Services.

Tech Updates

In SSRS reports we can have multi valued paremeters, which means we can select multiple values for that parameter and based on all those selected values result in the report is shown. We can write the SQL query to pass those parameter’s values and populate the data set, but sometimes it is required or to optimize the performance we need to use the stored procedure instead of direct sql query. This stored procedure will be taking the report paremeter as input and returing the result set to populate the data set.

In case of sql query as well as stored procedure, all selected values of the multi-value parameter will be passed as ‘,’ (comma) separated. So we should use IN clause in WHERE to impose the desired condition.

Problem: If we use stored procedure passing an input parameter which is multi-value parameter, SSRS will take all those selected multiple values…

View original post 398 more words

Monitoring SQL Server with Profiler ~ 5 Things to Avoid

SQL Profiler

Everyone loves Profiler and for good reason, it provides you with good data to help you make your SQL Server run even better. To quote FDR, “great power involves great responsibility.” Here is a brief list of things to avoid.

Run Profiler on the same server as SQL Server.

This adds too much overhead to the server, please don’t do it, EVER! Ideally, run it on a test server that has some good system resources to be able to capture and analyse all that data.

Select too many events to trace.

For example, selecting Batch Begin and Batch Completed. Choose only what you need.

Don’t filter the results.

Filtering saves you time crawling through the results and saves SQL Server time. This falls back on number 3, choose only what you need.

Don’t join the Performance Monitoring data with Profiler data.

Why chase down a problem, that is not the real cause of your slow server? While you may see a small performance boost on the server, you may be missing the elephant in the room.

SQL Server Coalesce

Coalesce is one of those old functions that has been around for a very long time and lost some of it’s lime light. It is good for a number of things but it’s VERY important to know how it behaves.

Overview

So coalesce takes any two or more parameters and takes the first parameter which IS NOT NULL and returns that value. For example:

SELECT COALESCE(null,’  ‘, ‘value’);

This would return the second argument, ‘ ‘, not the value ‘value’. You need to be very careful with this! Sometimes programmers don’t recognize the difference between null and empty values. If that is you, I have a very good article discussing null values in SQL Server, listed below:

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

SQL Server CTE ~ Hooray for Common Table Expressions

What is a SQL Server CTE?

CTE stands for Common Table Expression.

A CTE is basically a subquery. So why is it special? This is why they are good:

• Separate a subquery from the body of the main query.
• Cleaner code – CTEs enable you to separate your logically congruent code segments.
• Can be used for improving the execution plan of your query.
• Can be used for recursive, hierarchical queries.

When aren’t they good? Almost never. Basically, if you don’t need a subquery don’t use a CTE. Also don’t bother using a CTE when you are performing a query with resources outside of SQL Server, there are negative performance issues in SQL Server 2005, but is fixed in SQL Server 2008. Here is everything you will need to know about CTEs.

CTE Basics

Books Online

Recursive CTE

Canceled By User ~ Debugging CLR Objects in SQL Server and Visual Studio

So you enabled CLR debugging on your TEST SQL Server (don’t ever do this on production!)

You are trying to debug but you get a message “Canceled by user” in the Output window in Visual Studio. Here is the answer, you have to enable the ports on BOTH your workstation and the SQL Server machine.

Add the following inbound exceptions to both machines:
TCP: 135
UDP: 400,500

On your WORKSTATION:
Add the program devenv.exe to your exceptions list in your firewall.

This will worked for me. Let me know if you had a different solution.

SQL Server ~ Creating a CLR User Defined Function Error 181

Cannot use the OUTPUT option in DECLARE or CREATE FUNCTION statement

So this may or may not trip you up. If you are like me, you want thing to run fast. One of those ways is to pass a reference to a variable as opposed to passing a copy of the value to the function. In .Net this works well, however SQL Server translates the byref to it’s equivalent which is OUTPUT option. Simply bite the bullet and change the function to have all of your parameters to byval and you are good to go.

Hopefully I saved you some time and agrivation.