#CertifyMe

http://borntolearn.mslearn.net/certchallenge/

SQL Server will not start. There is not enough disk space for tempdb.

With a large tempdb data file located on C:\ the service would not start. The following message was in the log file.

Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

Unfortunately there was no way to free up the required space and the only way to start SQL would be to move the tempdb file location. Catch 22, we need to start SQL to move the files.

The way to accomplish this is to start SQL using trace flag 3608. This trace flag tells SQL not to start up any databases except master. From a command prompt:

net start MSSQLSERVER /f /t3608

                /f: Minimal configuration mode

                /t: Start SQL Server with the specified trace flag

Once SQL Server is running you can connect with SQLCMD using a dedicated admin connection (DAC) from the command promt.

sqlcmd -A -S MyServer -q"ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', FILENAME = 'H:\MSSQL\DATA\tempdb.mdf', SIZE = 2048MB, FILEGROWTH = 2048MB);"

sqlcmd -A -S MyServer -q"ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = 'G:\MSSQL\DATA\templog.ldf', SIZE = 1024MB, FILEGROWTH = 1024MB);"

                -A: Use DAC

                -S: Server Name

                -q: Command

Now stop and restart SQL Server and Bob’s your aunty.

SQL Saturday #244 Johannesburg 2013

SQL Saturday #244 is fast approaching and I will be speaking about indexes. This will be a beginner level session aimed at helping you understand what indexes are, how to use them and what to look out for. Don't miss it. 

I am also looking forward to hearing Gail Shaw discussing database corruption. The schedule is online at http://www.sqlsaturday.com/244/schedule.aspx.

See you there!

Code Horrors

I just stumbled across a strange piece of code, albeit a small one. I thought I would create a blog post to start collecting these gems.
 
Item 1
 
SELECT
    @RowIndex = RowID
FROM
    CBT
WHERE
    RowID = 1;
 
which actually means
 
SELECT @RowIndex = 1;
 

Fixing Database Corruption in a VLDB

A client of mine phoned me last week with an error message that had appeared in their application. My worst fears were confirmed when I heard the words "logical consistency-based I/O error".

The error message reported was:
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x31b2521f; actual: 0x4bd50f4c). It occurred during a read of page (3:8191715) in database ID 17 at offset 0x00000f9fdc6000 in file 'C:\MSSQL\Data\Data2.ndf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Heaps Of Trouble

I recently came across a situation at a client which I thought I would share with you. I came across this while doing some routine maintenance and noticed a small table consuming a lot of memory. I mean this table, which is a smallish lookup table, was consuming almost 430 MB of buffer space. The thing about lookup tables and the way they are used is that the entire table is usually read into memory. This is by design but the problem here is that there should be no way that this table needs 430 MB.

SQL Server 2012: Restore Fails With Deadlock

We had an interesting situation at a client where we could not restore a SQL Server 2008 R2 backup onto SQL Server 2012. The restore would fail at 100% with a deadlock.