Have you ever tried this and seen the speed of file allocation process? Though I had heard about it, I had not tried (I might have but I cannot remember :)). The default file allocation of SQL Server is zero initialization that takes long time when you try to create a database with a large sized data file or when you try to restore a fairly large database that is not exist. These two processes (not only these two, other processes like adding a new file...) can be speeded up by enabling Instant Initialization. You can enable the Instant Initialization by giving proper permission to the server that runs the SQL Server instance. Let me show you the simple test I did;

My SQL Server service runs with a account called SQLServerAccount that has just few user rights. It is not in the administrators group. I ran the code below and it took 13 seconds to create the "Test" database.

CREATE DATABASE [Test] ON  PRIMARY
( NAME = N'Test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Test.mdf' , SIZE = 200 , FILEGROWTH = 1000 )
 LOG ON
( NAME = N'Test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

I dropped the database and added the SQLServerAccount user account to "Perform volume maintenance tasks" policy. it is located under Administrative tasks -> Local security settings -> User right assignment. After restarting the SQL Server service, I ran the script again. Now SQL Server avoids zero initialization. It took only 1 second to create the database. This is a just a database with 200MB, so, think about a database with 10GB, or think about a restore process of 10GB database. Hope you can see the advantage.

This is not enabled by default because of security reason. Enabling this allows SQL Server administrator to see the recently deleted files that are not supposed to be seen by the SQL Server administrator.

This feature only available with SQL Server 2005 and it needs to be run with Windows 2003, XP or later. If the service account is already the local administrator, you already have the advantage. Note that this is not applicable to the log file.

Since the 20th is a Poyaday that is a mercantile holiday in Sri Lanka, we decided to have SS SLUG meeting on 21st, Thursday. Fiqri, ASP.NET MVP is going to join with us this time, showing his knowledge on SQL Server mobile edition. I am sure that Fiqri is going to deliver very valuable info for us because we have not discussed this topic before.

Gogula will be doing a session too; "Inmon vs. Kimbal - The great debate". This is a very interesting topic, and I am sure that people involve with BI know about this debate. Let's see what he is going to deliver us.

And I will be doing a session too; I will be showing another greatest redgate tool, SQL Multi Script.

Hope we have added enough things to the meeting. As usual, we have arranged few things for give-away items, including T-shirts, USB drives, Mugs, Pens. Come and join with us. Listen and grab both knowledge and prizes.

For more info, visit SQL Server Universe .Com

Another note of mine talks about "Schema Comparison with Data Dude" has been published at www.sql-server-performance.com. This shows the way of doing the schema comparison and how Data Dude addresses the changes.

Read it from here and let me know whether it is good enough for readers.

We, Sri Lankans celebrated 60th National Independence day today. It held on a grand scale at Gall Face, Colombo and all the media channels showed the program live. I am proud of being Sri Lankan because of the greatest history we have and wonders our ancestors had done.

Though we celebrate our independence like this, the LTTE terrorists are slowly sweeping it away from us. They keep on killing innocents and destroying the country. They targeted some innocents people today too, at Colombo Railway Station, killing 12 including two school children. When we can see the peace? When we can live happily? Will they let our children live happily in this country?