SQL Server Blogs

Voices from the UK SQL Server Community
Welcome to SQL Server Blogs Sign in | Help
in Search

My two cents

Tips, opinions, tests (and misspellings) from a Brazilian DBA in UK

November 2007 - Posts

  • Data purity

    Reviewing DBCC CHECKDB syntax for SQL Server 2005, I found a [DATA_PURITY] option added to its syntax.

    This option enable the following checks on each column value of those datatypes:

    Unicode character - The data length should be a multiple of 2.

    Datetime - The days field should be between Jan 1 1753 and Dec 31 9999. The time field must be earlier than '11:59:59:999PM' .

    Real and Float - Check for existence of invalid floating point values like SNAN, QNAN, NINF, ND, PD, PINF.

    For a database created on SQL Server 2005, these checks are always performed, but upgraded databases won't do it until you explicitly call using that option. If it reports no errors, then all subsequent CHECKDB in that database will perform those checks automatically.

    Add this in your migration checklist.

    KB923247 has all the details about it. The guys at mssqltips.com also have a good article about it.

  • Losing your Transaction log

    Technorati Tags: ,

    Some days ago, my boss got on my desk asking if I could take a look at one database that was not opening. That DB was in a virtual machine used for our sales presentations.

    For some reason, several files got corrupted in that VM, and between those was our beloved transaction log file.

    It was a perfect opportunity to sharpen my database recovery skills, there was quite some time that I didn’t played with those tools and to be honest I had forgot almost everything.

    So, as I will probably forget about it soon (thankfully, SQL Server does not crash that often), and as there is quite some time that I don’t post anything here, I think it will be good to blog about it.

    The error recorded in the log was:

    Error: 9004
    An error occurred while processing the log for database AnyDB.

    I love clear messages like that. This helps a lot to identify what caused the problem, right? No other meaningful error on SQL Server Errorlog nor in EventViewer.

    A quick look at google confirmed my suspicion:

    Time to restore from our backups.

    To be honest, this is what I would do in any critical database, but where is the fun it that? :-)

    So I went for the not recommended neither supported procedure in SQL Server 2000, which is:

    1 - Enable updates to system tables

    EXEC sp_configure 'allow updates',1

    RECONFIGURE WITH OVERRIDE

     

    2 - Save the current status for your database

    SELECT dbid, name, status

    FROM sysdatabases

    WHERE dbname = 'test'

     

    3 - Set it to “Emergency” mode (Status = 32768)

    UPDATE sysdatabases

    SET status = 32768

    WHERE dbname = 'test'

     

    4 - Recreate an empty log

    DBCC REBUILD_LOG (test,'<PATH>\test.ldf')

     

    5 - Check if your database is physically ok

    ALTER DATABASE test SET SINGLE_USER

    DBCC CHECKDB (test)

     

    6 - Change your DB status to something “neutral”

    UPDATE sysdatabases

    SET status = 28

    WHERE dbname = 'test'

     

    7 - Disable updates to system tables

    EXEC sp_configure 'allow updates',0

    RECONFIGURE WITH OVERRIDE

     

    8 - Restart SQL Server

     

    In SQL Server 2005, the procedure is much easier, and tough obviously still not recommended, it appears to use only supported commands:

    ALTER DATABASE test SET SINGLE_USER, EMERGENCY

    DBCC CHECKDB (test, REPAIR_ALLOW_DATA_LOSS)

    ALTER DATABASE test SET MULTI_USER, ONLINE

    Nice, huh? Obviously, both procedures will leave your database in a not-so-good shape because losing your transaction log, means losing the ability to undo uncommitted work and redo committed work that was not yet copied to your data file. So you should not trust anymore the integrity of you data.

    Even so, I believe that there are times when this is worthwhile. If you don’t have a good backup and/or you are dealing with development databases this might be a useful last resort.

    For more details, see Paul Randal's blog (always worth reading).

Powered by Community Server (Commercial Edition), by Telligent Systems