Posts Tagged ‘Uncategorized’

Undocumented CHECKDB options May 28th, 2010

Vinod Kumar

Many of my previous posts do talk a lot about how CHECKDB works within SQL Server with various interesting combination of scenarios. Though the use of CHECKDB and understanding of this topic is quite complex, in this post let me talk about the undocumented option of TABLERESULTS. It is quite handy and I used it in one of the demo at TechEd India 2010.

WITH TABLERESULTS

This option will allow CHECKDB to return a TDS based result set for all messages (informational and errors) returned as part of executing CHECKDB. This option maybe undocumented for multiple reasons:

* Maybe the code is not yet tested or there can be bugs that can disrupt the behavior end-users want.

The option is interesting as the result set if implemented fully could help produce a report of true errors encountered, specific repair actions needed for each error, etc.

The command is beneficial to analyze corrupt databases when brought in-house for troubleshooting. Esp for cases when there is corrupt index pages, you can quickly look at the INDID and figure out is they are only non-clustered indexes that have got damaged or if there are Clustered indexes also which have to be examined …

Microsoft is not under any obligation to provide information or support these commands or tools as these are undocumented. My previous posts around CHECKDB are –

CHECKDB and TempDB

CHECKDB and DATA_PURITY

CHECKDB is ONLINE now

CHECKDB and ResourceDB

CHECKSUM and SQL Server

CHECKSUM and TempDB

Continue reading...


 

CHECKSUM and SQL Server May 9th, 2010

Vinod Kumar

Checksum is a new feature introduced in SQL Server 2005+ to aid in data integrity and IO consistency issue detection. Checksum is a way to detect problems and not necessarily take any action. I wrote about CHECKSUM in my previous posts around CHECKSUM Vs Torn Page and many more. The basic functionality is as follows:

  • When the unit of storage is prepared to be written to disk, the checksum is calculated and stamped in the page header
  • Whenever this page is read back, the checksum on the storage unit is re-calculated and compared against the checksum stamped on the page header
  • If there is a difference then appropriate error is raised

A unit of Storage for which checksum is calculated:

  • Database Page
  • Log Block
  • MTF Backup Stream

Checksum is enabled at the database level for Pages and LogBlocks. For backups, you will have to request checksums using the WITH CHECKSUM option. I will discuss in detail on a later blog post along with other Backup related issues.

As soon as the database option for page_verification is turned ON, not all pages are immediately setup with the checksum. Only pages being written after this point will be tagged with the checksum. All databases created in SQL Server 2005+ will have a PAGE_VERIFICATION option set to CHECKSUM. This includes all the system databases: master and model. The tempdb database has PAGE_VERIFICATION option set, as I outlined on my previous post.

A database upgraded from SQL Server 2000 retains the PAGE_VERIFICATION option of TORN_PAGE_DETECTION it had in SQL Server 2000. The upgrade could happen due to restore, attach or setup.

There are a couple of important points that needs to be understood about the page verification. When a page is read into buffer pool a decision is made whether to perform Page Verification based on the database setting. This is clearly explained in the SQL Server IO whitepaper by Robert Dorr.

You will need to use the ALTER DATABASE statement to change the page verification settings on a database. The details are documented in the Books Online topic under the ALTER DATABASE statement TSQL topic.

SELECT name , page_verify_option_desc FROM sys.databases

You can use the DMVs method as above to obtain the page verification option set on a database. There are so many more interesting things to discuss on this topic which I will reserve  for future posts :). Welcome your comments.

Continue reading...


 

End of Service Pack Support for SQL Server 2005 SP2 and SQL Server 2008 RTM January 8th, 2010

Vinod Kumar

I would like to remind all users that Service Pack Support for SQL Server 2005 Service Pack 2 (SP2) will end on January 12, 2010 and support for SQL Server 2008 RTM will end on April 13, 2010.  Microsoft is ending support for these products as part of their Service Pack support policy, found in http://support.microsoft.com/lifecycle

Both SQL Server 2005 SP2 and SQL Server 2008 RTM will no longer receive assisted support or security updates from Microsoft after their respective end of support dates.  Self-Help online support will continue to be available for a minimum of 12 months after the product reaches the end of support.  Self-Help options include public knowledge base articles, FAQs, troubleshooting tools which are typically available from http://support.microsoft.com and the Microsoft Download Center.

http://blogs.msdn.com/sqlreleaseservices/archive/2009/10/08/end-of-service-pack-support-for-sql-server-2005-sp2-and-sql-server-2008-rtm.aspx

Continue reading...


 

Office Demo – Post August 19th, 2009

Vinod Kumar

Test onto my Blog from Office 2010. If this works I am most likely to be regular with posting into my blog !!!

If you are reading this – Dont forget to catch my Fun with Office 2010 at VirtualTechdays.com. 30 mins of fun with Office 2010 … Dont miss it !!!

Continue reading...


 

Excel Calculation Fix October 15th, 2007

Vinod Kumar

I know there was a lot of noise on this bug. But here is the post from the dev team on the fix. Check it out here.

Continue reading...