what is Instant File Initialization? how to enable it ?

what is Instant File Initialization? how to enable ?

Do you have instant file initialization turned on?  lets find its uses and how to enable ..for better performance ..

What is Instant File Initialization

It means when ever we create a new db  or grow a data file or when we restore a db, this option will be very helpful to allocate the required data pages at earliest. new pages allocated to the database first need to be filled with binary zeroes.  The process of writing binary zeroes takes time.  Therefore, when instant file initialization is not turned it takes SQL Server longer to create or expand a database.  The more space added to a database the longer it takes.

Why does SQL Server Not Perform Instant File Initialization by Default?

By writing binary zeroes to newly allocated disk space SQL Server makes sure pages allocated to the database don’t contain old disk images.  Without overwriting those data pages, a person might be able to peek into the internal structure of a data page and see those bits and bytes of old data stored on the disk.  By not zeroing out those data pages there is a security risk that old data can be viewed by outputting raw database pages.

How to Turn on Instant File Initialization

In order to turn on instant file initialization you need use the following steps:

1. Logon to your SQL Server with an account that is a member of the local Windows Administrator group. 
2. Open the Local Security Policy application by running secpol.msc from a command prompt.
3. In the left pane, click the “Local Policies”:
   
4. In the left pane double-click on the “User Rights Assignment”
   
5. Double-click on “Perform volume maintenance” item.
   
6. Click the “Add User or Group” button
   
7. Add the account name for which the SQL Server is running under in the dialog below:
   
8. Click the “Apply” button, and then close the “Local Security Policy” dialog box.

Finding the Deleted table and user info in SQL Server ?

 I showed you how to use a transaction log backup file to identify who deleted an object from a database.   In this tip, I will show you how to find out the actual table name for the object deleted.    

I ran the following code to identify who delete tables from my database in my last tip:

SELECT [Current LSN], [Operation], [Transaction Name], [Transaction ID], SUSER_SNAME ([Transaction SID]) AS DBUser 
FROM fn_dump_dblog (
            NULL, NULL, N'DISK', 1, N'C:\temp\Read_Backup_Logfile_DELETEDemo_Log.bak',
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
    WHERE
        [Transaction Name] LIKE ('DROPOBJ');

When I ran this code, I got the following output:

 Current LSN             Operation                Transaction Name                  Transaction ID DBUser
 ----------------------- ------------------------ --------------------------------- -------------- ------

 00000023:00000100:0001  LOP_BEGIN_XACT           DROPOBJ                           0000:0000032d  TIRU-PC\TIRU

To identify the object_id for the object I drop, I will need to review all the transaction log backup records associated with the DROP TABLE statement, so I can find the object lock transaction.  To do that I need to find all the transaction log backup records with the same Transaction ID value as the DROPOBJ transaction.  From the output above you can see that the Transaction ID value for the DROP TABLE command was “0000:0000032d”.  To find the schema lock information I can run the following code:

SELECT [Transaction ID], [Lock Information]
FROM fn_dump_dblog (
            NULL, NULL, N'DISK', 1, N'C:\temp\Read_Backup_Logfile_DELETEDemo_Log.bak',
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
    WHERE
        [Transaction ID] = '0000:0000032d'
              and [Lock Information] like '%ACQUIRE_LOCK_SCH_M OBJECT%';

Transaction ID Lock InformationHere you can see that I used the Transaction ID value I found for the DROPOBJ transaction in the WHERE clause above.   Additionally, this code will constrain the rows returned to only those rows that have text “ACQUIRE_LOCK_SHM_M OBJECT”, in the Lock Information column.    When I run this code, I get the following output:

-------------- -------------------------------------------------------------------------
0000:0000032d  HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 0:565577053:0 

In this output, the object_id for the object deleted can be found within the Lock Informationcolumn value.  In this example that object_id value is “565577053”.
In order to find out the object name for object_id “565577053”  I will have to restore my database prior to when I actually dropped the table.   I will do this by running the following code:

ALTER DATABASE [Read_Backup_Logfile_Demo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [Read_Backup_Logfile_Demo] 
FROM DISK = N'C:\Temp\Read_Backup_Logfile_DELETE_Demo.bak'
WITH REPLACE;

In this code, I restored my Read_Backup_LogFile_Demo database from the full backup I took of my database just prior to the DROP TABLE command.  For simplicity, I restored my database over the top of my existing database.  In reality you might want to perform this restore to a dummy database, if you want to retain the database with the deleted table.
Once my database is restored, I can find the name of the object that was deleted by running the following code:

USE [Read_Backup_Logfile_Demo];
GO
SELECT name, object_id from sys.objects
WHERE object_id = '565577053';

When I run this code, I get the following output:

name                                          object_id
--------------------------------------------- -----------
Read_Backup_Log                               565577053

As you can see the object I deleted was “Read_Backup_Log”.
By using my last tip and this tip you can find who dropped objects in my database, and which objects were dropped.   Next time you need to look at transactions in a backup log file you can use the fn_dump_dblog function.