HEAP Table / Clustered Table

A Table without clustered index is called HEAP Table.

When you define a clustered Index on heap table, then it gets a structure then that table can be called as Clustered Table.

Clustered Index - 

What is virtual log file ?

SQL Server internally divides the transactional log file into smaller chunks, called virtual log files.

What is LSN ( Log Sequence Number) ?
Each log record written into log file is stamped with a number called LSN.

Logshipping - FAQ

What recovery models will support Logshipping
FULL & BULK-LOGGED

What will happen if we change the recovery model of a logshipping database?
If we change it from Full to Bulk Logged, nothing will happen. The T-log files will generate as usual.
If we change it back from Bulk Logged to Full also no issues.
If we change it from Full to Simple and simple to full then it wont generate logbackups from that point onwards.

Example Scenario:
backup log mydatabase to disk='c:\temp\mydatabase_log1.trn' log mydatabase to disk='c:\temp\mydatabase_log1.trn'
alter database mydatabase Set recovery bulk_logged
select name, recovery_model from sys.databases where name ='mydatabase'
backup log mydatabase to disk='c:\temp\mydatabase_log2.trn'
alter database mydatabase Set recovery Full
select name, recovery_model from sys.databases where name ='mydatabase'
backup log mydatabase to disk='c:\temp\mydatabase_log3.trn'
alter database mydatabase Set recovery Simple
select name, recovery_model from sys.databases where name ='mydatabase'
alter database mydatabase Set recovery Full
select name, recovery_model from sys.databases where name ='mydatabase'
backup log mydatabase to disk='c:\temp\mydatabase_log4.trn'

Then we will get the below error message

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

To Restart the logshipping what needs to be done ?

1) Need to generate full backup to generate logs  -- It will take huge time based ont the size of the database.
2) Simple way is generate a differential backup -- Which allows to generate logs again, no need to generate full backup.

backup database mydatabase to disk='c:\temp\mydatabase_differential1.dif' with differential database mydatabase to disk='c:\temp\mydatabase_differential1.dif' with differential

backup log mydatabase to disk='c:\temp\mydatabase_log5.trn'

Interview Questions

What is the default file group name in SQL Server ?
 -- PRIMARY

To which filegroup Transaction log file is associated with ?
-- LDF file is not associated with any of file group

Is it possible to configure DB Mirroring to multiple Mirror Sites ?
-- The Answer is NO since 2008 R2, It is possible from 2012 onwards.

Is it possible to configure Mirroring and Logshipping on the same database ?
-- The Answer is YES

Is it possible to generate backup from the Mirror Server database?
-- The Answer is NO, but it is possible using SQL Server 2012 Always on availability replicas.
What is the code name for SQL Server 2012
Denali for 2012, Katmai for 2008, Kilimanjaro for 2008R2

There are 3 SQL Instances on one physical server. How many SQL Browser services will be installed on that server ?
-- The Answer is ONE, because sql browser is a shared service, Inadditon to SQL browser we have different shared services like Active Directory Helper.

How can you enable Instant File Initialization ?
By adding SQL Service account into perform volume maintenance tasks we can do that.

Is MSDTC is must to install SQL Server 2008 Failover cluster ?
-- The Answer is NO, it gives a warning message if MSDTC is not selected. But if you want to install SSIS,
or distributed transactions, then surely we need to install MSDTC.

What is the maximum limit of non-clustered indexes can be created on a table ?
-- The Answer is 999

How many IP addresses are required to configure 2 node cluster
1. Two public network addresses for node1 and node2
2. Two private network addresses for node1 and node2
3. Windows Cluster IP
4. MSDTC IP
5. Virtual Server IP

Will Attach_Rebuild_Log function builds a new log file incase of abnormal database shutdown?
The Answer is NO,that option ATTACH_REBUILD_LOG function will only works whenever database is cleanly shutdown if Log file is missing.

How differential backup is generated ?
Differential backup is generated based on the changed extents. But to have a differential backup initially we need to generate a full backup.

Backup Compression available in which versions of SQL Server ?
Initially it is available only in 2008 R2 Enterprise Edition, later it was added to SQL Server 2008 R2 Standard Edition.


What is default SQL Server port number ?

The Answer is 1433.







Keep watch this workspace for more question and answers............