Database Versions

As DBAs, these days we get databases of different types. Say, databases that have been created with the current version or databases that belong to old applications that have gone through a few upgradations (from 7 to 2000 to 2005 etc). The question is how can we find out what was the original version of SQL Server when the database was created. There is an undocumented command to find this out - DBCC DBINFO. You can see two entries in the output - dbi_version and dbi_createversion. The value for dbi_version denotes the current version of the database while dbi_createversion denotes the version of SQL Server when the database was originally created. A value of 515 stands for SQL Server 7.0, A value of 539 stands for SQL Server 2000, a value of 611 stands for SQL Server 2005 and a value of 612 stands for SQL Server 2005 with vardecimal option enabled. For more information on DBCC DBINFO command, see Paul's post at http://www.sqlskills.com/blogs/paul/2008/07/11/SearchEngineQA20BootPagesAndBootPageCorruption

Tools to avoid SQL Injection

Owing to the recent mass SQL injection attacks, Microsoft has worked to come up with a few tools to reduce the chances of attacks of the same type. Find the link here -
http://blogs.technet.com/swi/archive/2008/06/24/new-tools-to-block-and-eradicate-sql-injection.aspx

Tracking FileGrowth

I was always of the opinion that we should avoid using the “autogrow” option ON for production databases - whether small applications or large applications), simply because of the lot of overhead it creates in the expansion of the file, connection issues while the expansion happens, and most importantly, the amount of fragmentation it creates. Recently while going through a production issue, I heard this question from a fellow administrator that how we can find out when a file growth happened (This is a database where the autogrow is set to ON). I dug through some resources, but accidentally found out that is getting tracked in the default trace. We can just0 query the trace file to obtain the growth events. Joining the result with sys.Trace_Events will give us more clear results. Run the below given query and ensure that the path to the trace file is correct -
SELECT TrcEv.Name, Trc.DatabaseName, Trc.FileName, Trc.StartTime FROM fn_trace_gettable(E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_6.trc', NULL) AS Trc INNER JOIN sys.trace_events TrcEv ON Trc.EventClass = TrcEv.trace_event_id WHERE TrcEv.Name LIKE '%Auto Grow'
ORDER BY StartTime.
If you want to see, how the initial size of the file and the growth factor affects the file growth (and the index fragmentation of course), try the small example given below-

CREATE DATABASE GrowthTest
GO
USE GrowthTest
GO
CREATE TABLE dbo.Employee
(EmployeeName VARCHAR(3000))
GO

DECLARE @i INT
SET @i =1
WHILE @i < 10000
BEGIN
INSERT INTO Employee VALUES(REPLICATE('A', 3000))
END
(I assume that the filegrowth for your model database is the default settings) After this, run the above given query once more to see how many times the autogrowth happened.

Sql2k8 on August pricelist

Microsoft officially announcing that SQL Server 2008 will be released in August, and that too with no price increase!
http://www.eweek.com/c/a/Application-Development/Microsoft-to-Deliver-SQL-Server-2008-in-August/
http://blogs.technet.com/dataplatforminsider/archive/2008/07/09/sql-server-2008-available-on-august-price-list.aspx

Backups with COPY_ONLY option

We have heard a lot that having regular backups is a must in any database environment. But, there are cases where having even regular full, differential and log backups will not ensure that the database is secure, and you can lose the work for hours or days. This happens due to backup chain break when someone takes a full backup apart from the regular scheduled backups and the subsequent differential backups will have a different backup_lsn. The remedy is using the COPY_ONLY option with full backups which doesn’t break the chain. Paul Randal has illustrated the importance of this with a post which I noticed recently only http://www.sqlskills.com/blogs/paul/2007/10/15/BACKUPWITHCOPYONLYHowToAvoidBreakingTheBackupChain.aspx. So as a DBA, next time when your developers ask you for a backupset of a production database, either restore it from a regular backupset or take a backup with the COPY_ONLY option.

Reorganization of Non-Clustered Indexes

We all have seen people getting confused on the indexes reorganization processes. One of doubts that often popup is that whether the clustered index reorganization of a table will automatically reorganize all the non-clustered indexes on the table. Well, the answer is that it depends on the version and on the type of the clustered index column.In case of SQL server 2005, the answer is straight forward – the non-clustered indexes will NOT be reorganized at all. If the version is SQL Server 2000, it depends on the clustered index. Let us discuss SQL Server 2000 first. In SQL Server 2000, if the table is a heap, then the non-clustered indexes will locate the row corresponding to a key, based on the physical recordid which consists of the pageid and the record slot. This is a physical value. So in a heap, the lookup reference is a physical location. In case we create a clustered index on the heap or remove the clustered index on a table, these physical locations may get changed due to the rearrangements. Hence, the lookup references for the non-clustered indexes will become invalid which means the non-clustered indexes need to be reorganized.
Now, let us consider that the table is not a heap and has a clustered index. In case the table has a clustered index, the lookup is NOT based on the physical recordid. This is because if there is a clustered index, the lookup can uniquely identify the record using the clustered index key. This is logical reference, because the reference is towards a clustered index key and not to any physical location. However, if we drop the clustered index from the table, the logical lookup references based on the clustered index will become invalid, and hence the non-clustered indexes have to be reorganized.
Now, let’s see what will happen when we reorganize an existing clustered index. We know that a clustered index can be unique or non-unique. To ensure the uniqueness of non-unique clustered index keys, internally SQL Server automatically adds a uniquifier to every key of the clustered index (This does not happen with a unique clustered key). Now, if the non-unique clustered index is reorganized, the uniquifier is recreated, which will in turn make the lookup references invalid. So, the non-clustered indexes have to be recreated to ensure the integrity. But, if the clustered index is a unique clustered index, there is no uniquifier and therefore no uniquifier recreation is required during reorganization. Hence, the non-clustered indexes need not be recreated.
All the above things are the same in SQL Server 2005 except that the uniquifiers are not recreated for a non-unique clustered index during a index reorganization. Instead of recreating the uniquifiers, it will reuse the old uniquifiers. This means, even if the non-unique clustered index is recreated, there is no chance of the lookup references becoming invalid. So, there is no need of recreating the non-clustered indexes neither for a unique clustered index nor for a non-unique clustered index. Hence we can summarize as follows –
(Click on the image for a larger view)
Now, you can go back and see whether your index maintenance plans and scripts require any modification!

SQL Server 2008 RC0

SQL Server 2008 RC0 is now available for download; find it at http://download.microsoft.com/download/2/0/c/20cf6e18-0448-4e7b-8d8c-60a3c4802671/Download_Instructions_ENU.htm