Instant File Initialization in SQL Server

Data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. Data and log files are first initialized by filling the files with zeros. This mean that every byte of new allocated space is overwritten with zero values (0×0). This behavior prevents the problem of accessing old data, that was previously stored physically in the same NTFS clusters. 

The zero-initialization takes place during the following SQL Server operations which causes these operations to take longer.:

          Create a database

          Add files, log or data, to an existing database

          Increase the size of an existing file (including autogrow operations)

         Restore a database or file group.

If you don’t want that SQL Server is doing the zero-initialization of data files then you can re-configure SQL Server. 

Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account 
has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.

After enabling the Instant file initialization SQL Server will skip the zero-initialization of the data files, but you need to restart SQL server afterwards.

We can Apply this setting only to data files  – log files are always zero-initialized in SQL Server! There is no way through which you can change it!!! Without the zero-initialization of the log file, the crash recovery process would have no idea where to stop, when the log file was wrapped around. Crash Recovery stops where it finds zero values in the header of the next log record to be processed.

How to Check if Instant File Initialization is Enabled or not:

You can check that instant file initialization is enabled or not by creating a Test database. If it's not enabled then you can see messages in the SQL Server Error Log for the zeroing out both data file and log file. If it’s enabled then the messages will only be for Log File. Before that you need to turn on the trace flags for 3004 and 3605. After creation of database you can check the messages in error log file.

--=========================================================
USE master;
--Enable Trace Flags 3004 and 3605 to On.
DBCC TRACEON(3004,-1);
DBCC TRACEON(3605,-1);
--Create a sample database to see what output is written to the SQL Server Error Log
CREATE DATABASE IntantFileInitialization ON  PRIMARY
(NAME = N'IFI', FILENAME = N'D:\DBROOT\IntantFileInitialization.mdf', SIZE = 3000MB)
 LOG ON
( NAME = N'IFI_log', FILENAME = N'D:\DBROOT\IntantFileInitialization_log.ldf', SIZE = 1MB)
 go
--Off the Trace Flags.
DBCC TRACEOFF(3004,3605,-1);
--Remove the Database
DROP DATABASE IntantFileInitialization;
--==========================================================

--Now see the output in the SQL Server Error Log File

From above screen shot it is clear that Instant file Initialization is not enabled.  If it’s enabled then the messages will only be for Log File as below:

In order for SQL Server to  perform instant file initialization the SQL Server service account must be granted the Perform Volume Maintenance Task security permission. This can be done by using the Local Security Policy Editor via:

Administrative Tools – Local Security Policy and then Local Policies – User Rights Management as below:

Then add the rights to the SQL Server service account:

So you can enable Instant File Initialization for your SQL Server instance when you are the SQL Server & Windows administrator, it will be good to grant that permission, because as a Windows admin, you will always have access permission to the file system.

Thanks For Reading This Post!!

Add Linked Server in SQL Server 2008!!!

Linked Servers allows you to connect to other database instances on the same server or on another machine or remote servers.

As per Microsoft BOL "A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed."

Let see how can we add a linked server using  SQL Server Management Studio (SSMS):

Step: 1
In SQL Server Management Studio, open Object Explorer, expand Server Objects, right-click Linked Servers, and then click New Linked Server.

Step: 2  
The “New Linked Server”  window will appears as below.

For “Server Type” make sure “Other Data Source” is selected.  If you will select the SQL Server option then  the name specified in Linked server must be the network name of the server in other words you can not give a friendly name to it.

Now I have selected Other data source so I can give a friendly name to it. I have given LINKED_SERVER1.

Provider – Select “Microsoft OLE DB Provider for SQL Server”

Product Name – SQLSERVER  

Datasource – Enter  the actual server name, and instance name  as :
SERVERNAME\INSTANCENAME

ProviderString: We can leave it Blank

Catalog : It is Optional. If given then it will be the default database you will be using. 

On the Security page, specify the security context that will be used when the original SQL Server connects to the linked server. In a domain environment where users are connecting by using their domain logins, selecting Be made using the login’s current security context is often the best choice. When users connect to the original SQL Server by using a SQL Server login, the best choice is often to select By using this security context, and then providing the necessary credentials to authenticate at the linked server.

I have selected Be made using the login’s current security context:

Step: 3 Click OK, and the new linked server is created. You can see it under linked server as below:

So this is how we can add linked Server in SQL Server 2008.

For more information about linked server components please go through below microsoft BOL link:
http://msdn.microsoft.com/en-IN/library/ff772782.aspx#SSMSProcedure

References:
Microsoft Online Books Sql Server 2008

Thanks For Reading this Post!!

How to Create password protected Backup of database In SQL Server 2008.

SQL Server supports password protection for backup media and backup sets. Beginning with SQL Server 2012 the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. You can still restore backups created with passwords.

Let see how to create a database backup with password:

Create A Sample database 'Secure_Bakup' using below Script:

USE master
GO
CREATE DATABASE SECURE_BAKUP
GO
USE SECURE_BAKUP
GO
CREATE TABLE T1(ID INT ,NAME CHAR(30));
GO
INSERT INTO T1 VALUES(1,'ABC'),(2,'BCD'),(3,'TYS')
GO

we can see the records from Table T1 as below:

SELECT * FROM T1

Create the database backup as below:

BACKUP DATABASE SECURE_BAKUP TO 
DISK='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\SECURE_BAKUP.BAK' 
WITH PASSWORD='ABC@123' 

Lets verify the backup set without password:

RESTORE VERIFYONLY FROM  
DISK='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\SECURE_BAKUP.BAK'

You will get authentication error as below:

Msg 3279, Level 16, State 2, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

Lets try to restore the backup set without password:

RESTORE DATABASE [SECURE_BACKUP2] FROM  
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\SECURE_BAKUP.BAK' 
WITH  FILE = 1,  
MOVE N'SECURE_BAKUP' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SECURE_BACKUP2.mdf',  
MOVE N'SECURE_BAKUP_log' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SECURE_BACKUP2_1.LDF',  
STATS = 10
GO

You will get authentication error as below:

Msg 3279, Level 16, State 2, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Now lets verify the same backup set with password:

RESTORE VERIFYONLY FROM  
DISK='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\SECURE_BAKUP.BAK' 
WITH PASSWORD='ABC@123'
GO

Its successful. "The backup set on file 1 is valid."

Now lets restore the backup set with password:

RESTORE DATABASE [SECURE_BACKUP2] FROM  
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\SECURE_BAKUP.BAK' 
WITH  FILE = 1,  
MOVE N'SECURE_BAKUP' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SECURE_BACKUP2.mdf',  
MOVE N'SECURE_BAKUP_log' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SECURE_BACKUP2_1.LDF',  
STATS = 10,
PASSWORD='ABC@123'
GO

And the database is successfully restored. Below is the output for same:

12 percent processed.
21 percent processed.
30 percent processed.
43 percent processed.
51 percent processed.
60 percent processed.
73 percent processed.
81 percent processed.
90 percent processed.
100 percent processed.
Processed 184 pages for database 'SECURE_BACKUP2', file 'SECURE_BAKUP' on file 1.
Processed 2 pages for database 'SECURE_BACKUP2', file 'SECURE_BAKUP_log' on file 1.
RESTORE DATABASE successfully processed 186 pages in 0.246 seconds (5.903 MB/sec).

So this is how we can create password protected backup in SQL Server 2008. 
Please make sure that you are using SQL Server 2008 as these features(Password and MEDIAPASSWORD) are discontinued from SQL Server 2012.

Thanks For Reading this Post!!! 

How to Recover Database in case you have accidentally deleted Transaction Log File(.ldf) In MS SQL Server???

The best way to do this is to use the backups, and to have a backup strategy that allows you to recover database in the smallest amount of time and without any data loss.

But what if you don’t have any backup for that database?????? 

Lets create the same scenario then we will see the solution for same:

Create A sample database TLOG and a table with few records:

CREATE DATABASE [TLOG];
GO
USE [TLOG];
GO

CREATE TABLE DATA (
    ID INT ,
    NAME CHAR(20))
GO

INSERT INTO DATA VALUES (1,'VINAKR' );
INSERT INTO DATA VALUES (2,'BAGHEL' );
INSERT INTO DATA VALUES (3,'CHAUBEY');
GO

I’m going to start an explicit transaction and I'll update a record in table as below: 

BEGIN TRANSACTION;
GO
UPDATE DATA SET NAME = 'SACHIN' WHERE ID = 3;
GO

Now I’m going to force the data page holding the updated row to be flushed to disk:

CHECKPOINT;
GO

So Now we have an active transaction(uncommitted) , and the table modification has been written to disk.  Now I'm going to shut-down the server from another session: 

SHUTDOWN WITH NOWAIT;
GO

Now go to physical location of log files and just delete the transaction log file of TLOG.

Now Let's  start up SQL Server again. The database will not be accessible as log file is missing. You will get error as below:

 We can see the following in the error log:
======================================================
2014-08-23 13:38:15.82 spid21s     Starting up database 'TLOG'
2014-08-23 13:38:16.08 spid21s     Error: 17207, Severity: 16, State: 1.
2014-08-23 13:38:16.08 spid21s     FileMgr::StartLogFiles: Operating system error 2(failed to retrieve text for this error. Reason: 15105) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TLOG_log.LDF'. Diagnose and correct the operating system error, and retry the operation.
2014-08-23 13:38:16.08 spid21s     File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TLOG_log.LDF" may be incorrect.

2014-08-23 13:38:16.08 spid21s     The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

========================================================

The database wasn't cleanly shut down and the transaction log isn't available so recovery was not able to run.
Let see the status of our database using sys.databases as below:

SELECT [state_desc] FROM [sys].[databases] WHERE [name] = N'TLOG';
GO

It will return  RECOVERY PENDING. Below is the screen shot for same:

If the transaction was committed and SQL Server was cleanly SHUTDOWN in that case you can simply attached the MDF file and can rebuild your log  but as our database is in inconsistent mode we can not do that. Same is clearly mentioned in error log file as "The log cannot be rebuilt because the database was not cleanly shut down."

So, Now to recover our database first we will put it in EMERGENCY mode as below:

ALTER DATABASE TLOG SET EMERGENCY;
GO

You can see the database in SSMS as:

Now put the database in sigle_user mode and run the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS:

ALTER DATABASE TLOG SET SINGLE_USER;
GO
DBCC CHECKDB (N'TLOG', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO

It will executed successfully with below warning:

File activation failure. The physical file name 
"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TLOG_log.LDF" may be incorrect.The log cannot be rebuilt because there were open transactions/users when the database was shutdown,no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Warning: The log for database 'TLOG' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use,you will need to reset database options and delete any extra log files.

Now check the database state again as below:

SELECT [state_desc] FROM sys.databases WHERE [name] = N'TLOG';
GO

state_desc
-----------
ONLINE

So the database is back online again because everything worked. Let’s see what happened to our table "DATA":

USE TLOG;
GO
SELECT * FROM DATA;
GO
==========================
ID NAME
1 VINAKR              
2 BAGHEL              
3 SACHIN               

(3 row(s) affected)

And the data is still corrupt – because even though the transaction log was rebuilt and repaired, the original transaction that changed the name to SACHIN never got a chance to rollback because I deleted the transaction log. 

So, This is how We can recover it but make sure this should be your last option to do the same.

Thanks For Reading this Post!!!

FUNCTION IDENT_CURRENT(),IDENT_seed(),IDENT_INCR(),SCOPE_IDENTITY() AND GLOBAL VARIABLE @@IDENTITY

IDENT_CURRENT() returns the last identity value generated for specified table or view. This last identity value generated can be for any session and any scope. In case if you have newly created table without any records or truncated the table then it will show you the identity seed value.

Syntax: IDENT_CURRENT() ( 'table_or_view' )

IDENT_SEED():
Returns the original seed value that was specified when an identity column in a table or a view was created. Changing the current value of an identity column by using DBCC CHECKIDENT() does not change the value returned by this function.

Syntax: IDENT_SEED ( 'table_or_view' )

IDENT_INCR():
Returns the increment value specified during the creation of an identity column in a table or view that has an identity column.

We can test the same using below script:

USE master
GO
CREATE DATABASE IDENT
GO
USE IDENT
GO
create table B1 (id int identity(5,1),name varchar(12))
Go
insert into B1 VALUES ('P1'),('P2'),('P3')
GO
select IDENT_CURRENT('B1')as Identity_current_value_Before_Truncate
GO
TRUNCATE TABLE B1
GO
select IDENT_CURRENT('B1') as Identity_current_value_After_Truncate
select IDENT_seed('b1') as Seed_Value
select IDENT_Incr('b1') as Increment_Value
GO

From output we can see that after inserting records to table, the function IDENT_CURRENT ('B1') returns the last identity value generated and after truncation of table it returns the seed value. Along with this IDENT_SEED and IDENT_INCR returns the seed and increment value for
table B1 i.e. 5 and 1 respectively.

--DROP DATABASE
Use master
GO
DROP DATABASE IDENT
GO

SCOPE_IDENTITY():
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

@@IDENTITY:
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

For SCOPE_IDENTITY() and @@IDENTITY use below script to see the outputs:

USE master
GO
CREATE DATABASE IDENT
GO
USE IDENT
GO
create table B1 (id int identity(5,1),name varchar(12))
insert into B1 VALUES ('P1'),('P2'),('P3'),('P4')
select SCOPE_IDENTITY() as Table_B1
Go
create table D1 (id int identity(4,1),name varchar(12))
insert into D1 VALUES ('P1'),('P2'),('P3')
select SCOPE_IDENTITY()  as Table_D1
go
select @@IDENTITY as Last_generated_ID
GO

--DROP DATABASE
Use master
GO
DROP DATABASE IDENT
GO

Thanks For Reading This Blog!!!

Misleading Message Regarding “Transaction Rollback” While Running DBCC CHECKDB...

We all know the importance of DBCC CHECKDB in MS Sql server. The command “DBCC CHECKDB(database name)” checks the logical and physical integrity of all the objects in the specified database. If corruption has occurred for any reason, the DBCC CHECKDB command will find it, and will display exactly where the problem is. 

I got very interesting thing while running DBCC CHECKDB command in error log file and it was "transactions rolled back in database".

Lets create the same scenario using below script:

USE master
GO
CREATE DATABASE DTEST
GO
USE DTEST
GO
CREATE TABLE T(ID INT,NAME VARCHAR(12))
GO

--NOW WE WILL WRITE INSERT QUERY WITH MISSING COMMIT/ROLLBACK TRAN

BEGIN TRAN
INSERT INTO T VALUES(1,'MICROSOFT')
GO

Now open second query window and execute the below:

USE DTEST
GO
DBCC CHECKDB(DTEST)WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO 

Now open error log file and you will see below message:

2014-08-12 12:39:02.99 spid59      1 transactions rolled back in database 'DTEST' (25). 
This is an informational message only. No user action is required.

If you know little bit about how DBCC CHECKDB works internally it will be easier to understand why this message appears. From SQL Server 2005, DBCC CHECKDB uses a database snapshot to check the objects of database.When a database snapshot is created on a database, the transaction log of that database is examined, and crash-recovery is run on it, but into the database snapshot. The source database is totally unaffected. The message in the error log from DBCC CHECKDB is for hidden database snapshot not for actual database.

To verify the same we can run DBCC OPENTRAN(). It will list out the open transactions. In our case we will get the message as below:

Transaction information for database 'DTEST'.

Oldest active transaction:
    SPID (server process ID): 56
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (23:69:1)
    Start time    : Aug 12 2014 12:38:16:340PM
    SID           : 0x01

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Thanks For Reading This Blog!!!

HOW TO IDENTIFY AN OPEN TRANSACTION IN A DATABASE??

The fastest way to identify long-running transaction is to use DBCC OPENTRAN. This command can accept the database name as an input parameter in the format DBCC OPENTRAN(DatabaseName) where DatabaseName is the name of the database to check for open transactions.

If an active transaction exists in the database, this command will output information similar to the following:
========================================================================
Transaction information for database 'Test'.
Oldest active transaction:
    SPID (server process ID): 66
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (1348:2852:1)
    Start time    : Aug 11 2014  3:24:25:487PM
    SID           : 0x01
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
========================================================================

We can also get the information about open transaction from sys.sysprocesses as follows:

select * from sys.sysprocesses where open_tran > 0

Let see the same with help of a sample database "Test".

In this "TEST" database, We have a table as T with column ID and name, Now We'll use the below code which will take time to get execute as We have mentioned "WAITFOR DELAY '00:50:50'. 

BEGIN TRAN
SELECT 'TEST'
INSERT INTO A VALUES(13,'VI')
WAITFOR DELAY  '00:50:50'
COMMIT TRAN

Now we will execute DBCC OPENTRAN() Command to see the information about open transactions.

When we will use sys.sysprocesses we will get the information as below:

Now, We'll use below script to find the actual query which is taking time:

SELECT      r.start_time [Start Time],session_ID [SPID],
            DB_NAME(database_id) [Database],
            SUBSTRING(t.text,(r.statement_start_offset/2)+1,
            CASE WHEN statement_end_offset=-1 OR statement_end_offset=0 
            THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1 
            ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
            END) [Executing SQL], 
            Status,command,wait_type,wait_time,wait_resource, 
            last_wait_type
FROM        sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE       session_id =66 --Passed the session Id which We have got from DBCC opentran()
ORDER BY    r.start_time

After execution we will get the output as below:

From here we can see the current executing query is "WAITFOR DELAY".

If you want to see the whole batch of code then simply execute the below code:

SELECT s.session_id ,
s.status ,
s.host_name ,
s.program_name ,
s.login_name ,
s.login_time ,
s.last_request_start_time ,
s.last_request_end_time ,
t.text
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE s.session_id = 66 

We will get output as Below:

See the "text" column in results..It is having all TSQL statements. So, this is how we can get the information about all open transactions in a database.

Thanks For Reading This Post!!!!

HOW WILL YOU VERIFY THAT A BACKUP SET IS VALID WITHOUT RETORING IT??

In Database, Backups are the starting point for any serious disaster recovery strategy. Taking SQL database backups on a regular basis is just the first step of recovery. It is also important to make sure that they are reliable and restorable. This is the only way to avoid unpleasant surprises in case of a disaster.

We can check the validation of a database backups without restring it. So, Let see how to do the same.

I have a backup file "Test.Bak" of test database which is available at below location:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup

To see the that this backup set is usable or not we will have to execute the below T-SQL code:

RESTORE VERIFYONLY FROM 

DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\TEST.BAK'

So, this is how we can validate the Backup set.

Thanks For Reading this Post!!!