Incremental Servicing Model for SQL Server 2005

The Release Services team for SQL Server introduced a new servicing policy for SQL Server 2005 where in Cumulative Updates for the product are released every 2 months or 8 weeks cycle. Even though there is a KB Article and multiple blog posts surrounding the same, I have found the common question:

This fix is available in the list of available fixes in CU3 but I don’t see the bug/KB# in the Service Pack 3 KB Article.

Read on to find out why!! 🙂

So, if you are experiencing an issue mentioned in a KB Article, then you will find that the Bug# or the KB# for that issue mentioned in the list of available fixes in the next available Cumulative Update for SQL Server or Service Pack which ever is earlier. So, if you were looking for a bug# that you found mentioned in the KB Article for a Cumulative Update, then that KB Article/Bug# will not be repeated in the next available Cumulative Update or Service Pack. The reason for this is that all fixes that are released for Cumulative Update include all the fixes released in the prior Cumulative Updates.

For example, Service Pack 3 contains all the fixes that were released between time period of Service Pack 3 and Service Pack 1.

So, this is the reason why the Service Pack/Cumulative Update Pack sizes are getting progressively larger. So, another GOTCHA for all those folks who have been cribbing about the update package sizes. 🙂

Related Links:

An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

http://support.microsoft.com/kb/935897

Another blog post iterating the above points in a more detailed manner:

http://blogs.technet.com/beatrice/archive/2007/07/11/incremental-servicing-model.aspx

How to troubleshoot connectivity issues for SQL

The first option should be to perform a UDL test. The following blog post has information on the same:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!939.entry

If this fails, you can then use the steps mentioned in the blog post by the SQL Dev team to troubleshoot your connectivity issue:

http://blogs.msdn.com/sql_protocols/archive/2008/04/30/steps-to-troubleshoot-connectivity-issues.aspx

How to import data from an Excel File using T-SQL

If you have an Excel File named ExcelFile.xls, then you can perform SELECTs on that Excel file using the Excel Sheet name and the Jet Provider.

SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\ExcelFile.xls;Extended Properties="Excel 8.0"')...[Data_Load$];

In case, you use the same Excel file over and over again, then you can use OPENQUERY using a Linked Server defined to your Excel File.
Using OPENQUERY:

SELECT *
FROM OPENQUERY (DataLoad, 'select * from [Data_Load$]')

If you find that the data being imported for non-Text columns is NULL, then refer the following blog entry:

How to track exceptions using Ring Buffers output

SQL Server 2005 added a new feature called Dynamic Management Views (DMVs) to help DBAs monitor the server. However, people like me who were comfortable using the system tables available in SQL Sever 2000 (read: averse to change 😉 ) took quite a bit of time to switch over to using DMVs. One such DMV that has caught on with making live troubleshooting much easier is the sys.dm_os_ring_buffers DMV. The ring buffers hold historical information about events occurring on the server. Recently, I was working on a lock timeout issue. Based on a profiler trace capture, it was found that the server was experiencing multiple occurrences of the following message: 

Lock request time out period exceeded.  

We identified the statements and made the changes to prevent the Lock Timeout from occurring. Now that the server was being monitored, I didn’t want to a run a SQL Server profiler trace to track the occurrence of this message. So, I thought of using the Ring Buffers DMV to monitor if any further Lock Timeouts were occurring the server. This made monitoring the instance a piece of cake! 

The script below will give you the time range between which the exceptions are stored in the Ring Buffers along with the list of exception occurrences that you want to monitor. 

SQL Server 2005 

------------- Script Start -----------------------
DECLARE @ts_now BIGINT,@dt_max BIGINT, @dt_min BIGINT
SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_info
select @dt_max = MAX(timestamp), @dt_min = MIN(timestamp) from sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION'
select DATEADD(ms, -1 * (@ts_now - @dt_max), GETDATE()) AS MaxTime,
DATEADD(ms, -1 * (@ts_now - @dt_min), GETDATE()) AS MinTime
SELECT record_id,
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,y.Error,UserDefined,b.description as NormalizedText
FROM (
SELECT
                        record.value('(./Record/@id)[1]', 'int') AS record_id,
                        record.value('(./Record/Exception/Error)[1]', 'int') AS Error,
                        record.value('(./Record/Exception/UserDefined)[1]', 'int') AS UserDefined,TIMESTAMP
FROM (
SELECT TIMESTAMP, CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION'
AND record LIKE '% %') AS x) AS y
INNER JOIN sys.sysmessages b
on y.Error = b.error
WHERE b.msglangid = 1033 and  y.Error = 1222 -- Change the message number to the message number that you want to monitor
ORDER BY record_id DESC
------------- Script End -----------------------

 

SQL Server 2008 

------------- Script Start -----------------------
DECLARE @ts_now BIGINT,@dt_max BIGINT, @dt_min BIGINT
SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, ms_ticks) FROM sys.dm_os_sys_info
select @dt_max = MAX(timestamp), @dt_min = MIN(timestamp) from sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION'
select DATEADD(ms, -1 * (@ts_now - @dt_max), GETDATE()) AS MaxTime,
DATEADD(ms, -1 * (@ts_now - @dt_min), GETDATE()) AS MinTime
SELECT record_id,
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,Error,UserDefined,text as NormalizedText
FROM (
SELECT
                        record.value('(./Record/@id)[1]', 'int') AS record_id,
                        record.value('(./Record/Exception/Error)[1]', 'int') AS Error,
                        record.value('(./Record/Exception/UserDefined)[1]', 'int') AS UserDefined,TIMESTAMP
FROM (
SELECT TIMESTAMP, CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION'
AND record LIKE '% %') AS x) AS y
INNER JOIN sys.messages b
on y.Error = b.message_id
WHERE b.language_id = 1033 and  y.Error = 1222 -- Change the message number to the message number that you want to monitor
ORDER BY record_id DESC
------------- Script End -----------------------

 

Technorati Tags: ,,,

Check Integrity task in Maint Plan fails on SQL Server 2000

When a SQL Server 2000 Database Maintenance Plan is executed, you find that the following message in printed in the Maint Plan .OUT file (Found in the SQL Server LOG folder):
 
The backup was not performed since data verification errors were found
 
OR
 
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC SQL Server Driver][SQL Server]Option ‘SINGLE_USER’ cannot be set in database ‘MASTER’. [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed. [1] Database master: Check Data and Index Linkage… [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
 

 
This happens because you had selected the option of "Attempt to repair any minor problems" (refer above pic). This option needs CHECKDB to be executed with the database in SINGLE USER mode. If you have users connected to the database during that time, the database cannot be put into SINGLE USER mode due to which the Integrity Check task will fail. This will prevent backups from occurring on the database and you would see the above messages in the log files.
 
DBCC CHECKDB (<database name>, repair_fast) executed by <account name> found 0 errors and repaired 0 errors.  Elapsed time: 0 hours 0 minutes 50 seconds.
 
If you look in the SQL Server ERRORLOG, you will find that the CHECKDB is executed with REPAIR_FAST flag. This requires Single User mode. Another note to keep in mind is if you check the Integrity Check option and "Attempt to repair any minor problems", then exclude MASTER, MSDB and MODEL databases from the Plan.
 
Reference Article:
290622 BUG: Database maintenance plan on system databases fails on integrity check if "Attempt to repair minor problems" is selected
http://support.microsoft.com/default.aspx?scid=kb;EN-US;290622