SQL Interview Questions and Answers

Materialized views are also logical view of our data driven by select query but the result of the query will get stored in the table or disk, also definition of the query will also store in the database .When we see the performance of Materialized view it is better than normal View because the data of materialized view will stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.

1) In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table. When we create view using any table, rowid of view is same as original table but in case of Materialized view rowid is different. In case of View we always get latest data but in case of Materialized view. we need to refresh the view for getting latest data. Performance of View is less than Materialized view. In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table. In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.

-to_many_rows

-zero_divid_errors

-no_data_found

-invalid_cursor

-dup_value_on_index

SELECT *

FROM EMP

WHERE ROWNUM <= 10

MINUS

SELECT *

FROM EMP

WHERE ROWNUM < 10;

DELETE

FROM EMP

WHERE ENAME= (SELECT ENAME

FROM EMP

WHERE ROWNUM<=10

MINUS SELECT ENAME

FROM EMP

WHERE ROWNUM<10);

Schema objects is Structures of database’s data.  tables, views, sequences, synonyms, indexes are schema objects.

Data is collection of information or collection of attribute, example Ravi it’s a data. The information of Ravi is height, weight, education, age, etc..

A table is collection of data to storage in any database.  Table data is stored in rows and columns.

A View is not a original table It’s called virtual table. It’s based on original table

There are three type of tables

1. Simple view
2. Complex view

1. Inline view

An index is direct access to rows, used to increase the performance of data retrieval.  Index can be created on one or more columns of a table.

UNIQUE constraints allowed NULLs values. PRIMARY KEY constraints cannot contain NULLs.

Truncate is after deleting entire table, Cannot be rolled back options. Delete allows the single row and multiple rows. Deleted records can be rolled back or committed

Join is retrieves data from related columns or rows from multiple tables.

1. Cross Join
2. Inner join
3. equi join
4. non equi join
5. Outer join
6. left outer join
7. right outer join
8. full outer join
9. Self join

Subquery is a query  embedded with another query called subquery its return values from mainquery

INSTR(str1, str2 [,starting,[howmuch]])

INSTR is returns the position of the character.

SUBSTR(stri1, starting, howmuch)

SUBSTR returns a single or multiple character string

UNION returns all unique rows .

UNION ALL returns all rows included all duplicates.

INTERSECT returns all common row and unique rows selected by both queries.

MINUS returns all unique rows selected by the first query but not by the second.

ROWID is a pseudo column attached to each row of a table. Its create table database assign the rowed in each rows

Primary key is a column or a combination of columns of a table which cab be used to uniquely identify a row of the table. PRIMARY KEY cannot be null.

UNIQUE KEY is a column or a combination of columns of a table, which can be used to identify a row of the table uniquely. UNIQUE KEY can be null.

FOREIGN KEY is a column or a combination of column which is the primary key or unique key of the referenced table. FOREIGN KEY can be null.

Database maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.

• 1
• 22
• 0
• NULL

• >=
• <=
• in

Ans Intersect

A database is a collection of data. Representing some aspect of real world.

select decode(mod(2004,4),0,’leap year’, ‘not a leap year’) from dual

Date is used to store date and time values including month, day, year, century, hours, minutes and seconds. It fails to provide granularity and order of execution when finding difference between 2 instances (events) having a difference of less than a second between them. Time Stamp datatype stores everything that Date stores and additionally stores fractional seconds.

Maximum 254  in single table

To be created when table is queried for less in the table rows and need fastest Retrival

Foreign key is which refers to another table primary key. Reference key is the primary key of table referred by another table.

SELECT NAME FROM SYS.V_$DATAFILE;
SELECT NAME FROM SYS.V_$CONTROLFILE;
SELECT MEMBER FROM SYS.V_$LOGFILE;

SQL*Loader is a loader utility used for moving data from external files into the Oracle database in bulk. It is used for high-performance data loads.

SQL*Loader, External Tables

You new invoke the insert_location procedure using the followingcommand:

Subqueries, or nested queries, are used to bring back a set of rows to be used by the parent query. Depending on how the subquery is written, it can be executed once for the parent query or it can be executed once for each row returned by the parent query. If the subquery is executed for each row of the parent, this is called a correlated subquery.

SQL>select deptno, count(deptno) from emp group by deptno having

count(*)>3;

The output should contain only those jobs with more than three employees.

SQL>select job,count(empno) from emp group by job having count(job)>3

SQL>select ename from emp where job=’CLERK’ and sal>(select min(sal)

from emp

where job=’SALESMAN’);

SQL>select ename from emp where deptno=10 and sal>any(select sal from

emp where deptno not in 10).

1. Rename t1 to t1_base;

Create view t1 <column list with new name> as select * from t1_base;

2. create table t2 <column list with new name> as select * from t1;

Drop table t1;

Rename t2 to t1;

1. SELECT DISTINCT SAL FROM EMP1 WHERE SAL NOT IN (SELECT SAL FROM EMP1 WHERE SAL < ANY (SELECT SAL FROM EMP1))

2.SELECT SAL FROM EMP WHERE SAL >= ALL (SELECT SAL FROM EMP)

“NULL”, CONCAT (DATA_TYPE, DATA_LENGTH) TYPE FROM USER_TAB_COLUMNS

WHERE TABLE_NAME = ‘DEPT’

SELECT EMPNO, LPAD (‘ ‘, 6*(LEVEL – 1)) || ENAME “EMPLOYEE NAME” FROM EMP START

WITH ENAME=’KING’ CONNECT BY PRIOR EMPNO = MGR

Entity – Relational diagram

Could be (a) one to one

(b) One to many (crowfoot style)

(c) Many to many

A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns matches a value in a column of a related table.  It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.

UPDATE and DELETE Restrict – A referential integrity rule that disallows the update or deletion of referenced data.

DELETE CASCADE        –  when a referenced row is deleted, all associated dependent rows are deleted.

If a foreign key reference a parent key of the same table it is called self-referential integrity constraint.

The condition must be a Boolean expression evaluated using the values in the row being inserted or updated, and cannot contain subqueries, sequences, the SYSDATE, UID, USER, or USERENV SQL functions, or the pseudocolumns LEVEL or ROWNUM.

A. SELECT ROUND(enroll_date, ‘DAY’) FROM student;
B. SELECT ROUND(enroll_date, ‘YEAR’) FROM student;
C. SELECT ROUND(enroll_date, ‘MONTH’) FROM student;
D. SELECT ROUND(TO_CHAR(enroll_date, ‘YYYY’)) FROM student;

A. The statement will achieve the desired results.
B. The statement will execute, but will NOT enable the PRIMARY KEY constraint.
C. The statement will execute, but will NOT verify that values in the ID column do NOT violate the constraint.
D. The statement will return a syntax error.

The synonym is another name of the table used for multiple links of a database. A view is created with many tables, and virtual columns and with conditions.

SQL>select ename,sal from emp where sal>(select sal from emp where ename=’JONES’)and sal> (select sal from emp where ename=’SCOTT’);

1. The locations, departments, and employees tables are empty.
2. The departments table has one row. The locations and the employees tables are empty.
3. The location table has one row. The departments and the employees tables are empty.
4. The locations table and the departments table both have one row. The employees table is empty.

Answer(s) D

Auto close is a database property : if this is turned ON, the SQL server database engine will automatically close the connectivity to user database if its not being used. When new user tried to access the database, the database will come online.

When Auto create stats is enabled the Query Optimizer creates statistics on individual columns used in a predicate, if these statistics are not already available. These statistics are necessary to generate the query plan. They are created on columns that do not have a histogram in an existing statistics object. The name of the auto-created statistics includes the column name and the object ID in hexadecimal format. Its going to increase the database performance instead of waiting for the maintenance plan and manual creation of stats.
Auto update stats is going to update the stats every time whenever a new stats are creating, which in turn leads optimizer to select the best execution plan and increases the query performance.

SQLOS is thin layer which allows the communication between the SQL server database engine and Operating system
Main Objective of SQL OS ::::
Scheduler and IO completion. The SQLOS is responsible for scheduling threads for CPU consumption. Most threads in SQL Server are run in cooperative mode, which means the thread is responsible for yielding so that other threads can obtain CPU time. Most IO is asynchronous. The SQLOS is responsible for signalling threads when IO is completed.
Synchronization primitives: SQL server is a multi-threaded application, so SQLOS is responsible for managing thread synchronizations.
Memory management: Different components within SQL Server, example plan cache, CLR, lock manager etc request memory from the SQLOS. Therefore, the SQLOS can control how much memory a component within SQL Server is consuming.
Deadlock detection and management of the same.
Exception handling framework.
Hosting services for external components such as CLR and MDAC. SQL Server will run threads that are associated with external component in pre-emptive mode. Pre-emptive mode allows the SQLOS to prevents runaway threads (threads which will not yield and allow other threads to get CPU execution). Also the SQLOS can keep track of the memory these external components consume. For example, for CLR the SQLOS can invoke garbage collection if the CLR process is taking up too much memory.

Limiting the memory of the SQL server, means assigning the maximum and minimum memory to SQL server. Depending on the Memory allocated to the OS we can assign/allocate memory to SQL server. As per the recommendation we need to assign approx. 80% of total allocated memory to SQL server.

Maximum memory is the amount of memory a SQL server instance can pull from the physical memory allocation at OS level, when ever the SQL server is required. Depending on the memory utilization SQL server demands the memory from OS.
Minimum memory is the amount of memory SQL server needed to start, generally SQL server database engine required only 20MB of memory to start the services. But once will allocate minimum memory in GB then what will happen is SQL server will not release that memory once it will approach that value.

SQL server setup.exe file is corrupted.
Hardware and softwareís are not compatible.
MSI and MSP files are missing.
Donít have enough permission to perform write operation on installation path.
AgentSigning Certificate is not creating.
SQL service account is not having enough permission or it has not been configured in the proper way.

We need to read the details.txt.
SQL server error logs.
Eventviewer at OS level.
Temp folder.

ACID (an acronym for Atomicity, Consistency Isolation, Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures.

Atomicity is an all-or-none proposition means its going to treat all the transaction as one or none.
Consistency guarantees that a transaction never leaves your database in a half-finished state, what all transactions are got committed that transaction will be available and if not the transaction will be rollback.
Isolation keeps transactions separated from each other until theyíre finished, again its depends on which isolation level the transaction is running.
Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination, its going to calculate the recovery phase based on last checkpoint.

A transaction consists of a single command or a group of commands that execute as a package. Transactions allow you to combine multiple operations into a single unit of work. If a failure occurs at one point in the transaction, all of the updates can be rolled back to their pre-transaction state.
A transaction must conform to the ACID propertiesóatomicity, consistency, isolation, and durabilityóin order to guarantee data consistency. Most relational database systems, such as Microsoft SQL Server, support transactions by providing locking, logging, and transaction management facilities whenever a client application performs an update, insert, or delete operation.

Concurrency is the ability of multiple users to access data at the same time. When the number of simultaneous operations that the database engine can support is large, the database concurrency is increased. In Microsoft SQL Server Compact, concurrency control is achieved by using locks to help protect data. The locks control how multiple users can access and change shared data at the same time without conflicting with each other.

Optimistic concurrency: Optimistic concurrency control works on the assumption that resource conflicts between multiple users are unlikely, and it permits transactions to execute without locking any resources. The resources are checked only when transactions are trying to change data. This determines whether any conflict has occurred (for example, by checking a version number). If a conflict occurs, the application must read the data and try the change again. Optimistic concurrency control is not provided with the product, but you can build it into your application manually by tracking database access.
Pessimistic concurrency: Pessimistic concurrency control locks resources as needed, for the duration of a transaction. SQL Server Compact supports pessimistic concurrency control that locks resources as needed for the duration of a transaction.

Uncommitted Read: Uncommitted Read, or dirty read, lets a transaction read any data currently on a data page, whether or not that data has been committed. For example, although another user might have a transaction in progress that has updated data, and that transaction is holding exclusive locks on the data, your transaction can read the data anyway, and possibly take further actions based on the values you read. The other user might then decide to roll back his or her transaction, so logically, those changes never occurred. Although this scenario isn’t desirable, with Uncommitted Read you won’t get stuck waiting for a lock, nor will your reads acquire share locks that might affect others.
Committed Read: Committed Read is SQL Server’s default isolation level. It ensures that an operation will never read data another application has changed but not yet committed. Because you can never read uncommitted data, if a transaction running with Committed Read isolation revisits data, that data might have changed, or new rows might appear that meet the criteria of the original query. Rows that appear in this way are called phantoms.
Repeatable Read: If you want the read operations to be repeatable, choose the third repeatable Read. The Repeatable Read isolation level adds to the properties of Committed Read by ensuring that if a transaction revisits data or if a query is reissued, the data will not have changed. In other words, issuing the same query twice within a transaction won’t pick up any changes to data values that another user’s transaction has made. No other user can modify the data that your transaction visits as long as you have not yet committed or rolled back your transaction.
Serializable: The Serializable isolation level ensures that if a query is reissued, no data will have changed and no new rows will appear in the interim. In other words, you won’t see phantoms if the same query is issued twice within a transaction. Rerun the example from the Repeatable Reads section, inserting a row with a col1 value of 35. But this time, set your isolation level to SERIALIZABLE. The second connection will block when you try to do the INSERT, and the first connection will read exactly the same rows each time.
SNAPSHOT: Specifies that data read by any statement in a transaction will be the transaction ally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transactions.

No, it not possible to restore the database in point of time in simple recovery mode.
Cause in simple recovery mode, we are not having the transaction log backup.

TUF File: Its Transaction Undo File. It Generated only when you Have Configured Log Shipping with Stand by Option. Since in Stand by Log Shipping Secondary Database is Available to User. So TUF Keep Pending Transaction Which are in Log File Came from Primary So That when Next Log Backup Will Come from Primary They Can Be Synchronized at Secondary.
WRK: This Extension Is Given to A File Which is Being Copied from Primary Backup Location to Secondary and Once Copy Process has been completed these files are renamed with .trn file.

Yes, we can configure the multiple secondaries in log shipping. As the log shipping is a database level configuration. We can have the appropriate permission to the network path where the log backup is getting generated.

RTO stands for Recovery Time Objective: In simple terms, you can think of RTO as a measure of how much downtime is acceptable, or how quickly must the data be made accessible again. RTO is often talked about in terms of the number of nines of desirable up time or accessibility for the data/database/system.
RPO stands for Recovery Point Objective: Again, in simple terms, you can think of RPO as a measure of how much data or work itís acceptable to lose. Itís relatively easy to achieve very minimal or even zero data/work loss using backups but depending on the amount of damage the database suffered when the disaster hit, recovering might take a lot of time. For instance, if an entire database is destroyed, depending on the architecture of the database and the backups that exist, it may take a significant amount of time to recover the database up to the point of the disaster. Most RPOs are defined as the amount of time for which work may be lost.
Both RTO and RPO together called as SLA.

Generally: We will schedule a Full backup on every weekend followed by differential backup every day post business hours like 10 PM, followed by log backup every one hour.
If the database is highly critical, we will schedule a full backup every day post business hour like 10PM followed by log backup every 1 hour or 30 mins.
Depending on the Recovery model and transactions will configure the log backup in range of 15mins to 60 mins.

Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In an SQL Server, the smallest unit is a page, which is made of page with size 8K. Every page can store one or more rows based on the size of the row. The default value of the Fill Factor is 100, which is same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. There will be no or very little empty space left in the page, when the fill factor is 100.
Fill factor will play a significant role in database performance by minimising the affect of page split which in turns leads to the optimal performance at IO level.

Yes we can configure the mirroring between two different SQL server versions.
But this is not recommended, as its not going to support the backward compatibility during mirroring failover.

Lock escalation is the process of converting many fine-grained locks (such as row or page locks) into table locks. Microsoft SQL Server dynamically determines when to perform lock escalation. When making this decision, SQL Server takes into account the number of locks that are held on a particular scan, the number of locks that are held by the whole transaction, and the memory that is being used for locks in the system as a whole.

wait types represent the discrete steps in query processing, where a query waits for resources as the instance completes the request.
To get the information for wait type we will use below mentioned DMV.
Sys.dm_os_wait_stats
Sys.dm_os_waiting_tasks

Page Latch ñ These are waits that occur when a worker needs to wait for a page to become available. This typically occurs on a page is already available in memory.
Page IO Latch ñ These are waits that occur when a needs to wait for a page due to physical I/O. Such as when a page needs to be made available in the buffer pool for reading or writing and SQL Server needs to retrieve it from disk or mount points.

Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Heartbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk, and MSDTC Disk.

We canít read the mirror database, as the database is in restoring mode always. So read and write operations cannot be performed on the database.
To read a mirror database we need to create a snapshot of that database.

Yes, we can migrate a 500GB or of any size database without downtime or minimal downtime of few mins like 5mins.
By configuring a high availability concepts like log shipping or mirroring.

Yes, we can add the new data file on primary database in log shipping.
If the primary and secondary servers is having the same disk configuration i.e. the data file locations on both the servers are exactly same in that case there will be no affect on the log shipping behaviour and if not then the changes made on the primary will not reflect on the secondary.

No, its not possible, cause the blank database will take the template from model database and the size of model database is 16 MB (8 MB for data and 8 MB for log). So, we must have to provide size either equal to model or more than model database.

Sp_replcounters is a system stored procedure that returns information about the transaction rate, latency, and first and last log sequence number (LSN) for each publication on a server. This is run on the publishing server. Running this stored procedure on a server that is acting as the distributor or subscribing to publications from another server will not return any data.

sys.dm_repl_articles – Contains information about each article being published. It returns data from the database being published and returns a row for each object being published in each article.
sys.dm_repl_schemas – Contains information about each table and column being published. It returns data from the database being published and returns one row for each column in each object being published
sys.dm_repl_traninfo – Contains information about each transaction in a transactional replication.

In this case we need to pull out the dependency report first. After that we need to see on which all factors the SQL server service is dependent and which factor is right now creating issue while SQL services is trying to come online.
Like : SQL server service is dependent on shared disks, ip addresses, storage. May be the dependency is either OR or AND.

No, it supports only on Full recovery mode.

No, we cannot take a T-Log backup of a newly created database. Until and unless we will not take the full backup of database in spite of database recovery model, the database will behave as its being configured on simple recovery mode.

No, itís not possible.

No, but we can allow SQL server to automatically create statistics on read only secondary replicas.

There are two options to configure secondary replica for running read workload. The first option ëRead-intent-onlyí is used to provide a directive to AlwaysOn secondary replica to accept connections that have the property ApplicationIntent=ReadOnly set. The word ëintentí is important here as there is no application check made to guarantee that there are no DDL/DML operations in the application connecting with ëReadOnlyí but an assumption is made that customer will only connect read workloads.

Yes. If a corrupt page is detected, SQL Server will attempt to repair the page by getting it from another replica.

SQL Server 2014ís biggest improvement is that the replicaís databases stay visible when the primary drops offline ñ as long as the underlying cluster is still up and running. If we have one primary and four secondary replicas, and we lose just my primary, the secondaries are still online servicing read-only queries. (Now, you may have difficulties connecting to them unless youíre using the secondaryís name, but thatís another story.) Back in SQL 2012, when the primary dropped offline, all of the secondariesí copies immediately dropped offline ñ breaking all read-only reporting queries.

When SQL Server runs on a computer with more than one processor or CPU, it detects the best degree of parallelism, that is the number of processors employed to run a single statement, for each query that has a parallel execution plan. You can use the max degree of parallelism option to limit the number of processors to use for parallel plan execution and to prevent run-away queries from impacting SQL Server performance by using all available CPUs.
The default value for MAXDOP is 0 (zero) and can be set or viewed using (sp_configure). A value of 0 means that SQL Server will use all processors if a query runs in parallel.

It will tell us why the database log file isnít clearing out. By reading the column we will get to know what action needs to be performed on log file in order to truncate the log file.

No.
The purpose of a checkpoint is to bring the pages in the data files up-to-date with whatís in the transaction log. When a checkpoint ends, thereís a guarantee that as of the LSN of the LOP_BEGIN_CKPT log record, all changes from log records before that point are persisted in the data files on disk. Thereís no guarantee about logged changes after that point, only before it. In other words, all the log records before the LSN of the LOP_BEGIN_CKPT log record are no longer required for crash recovery, unless thereís a long running transaction that started before that LSN.

The answer is that log clearing/truncation of a VLF containing an LOP_BEGIN_CKPT log record cannot happen until another complete checkpoint has occurred. In other words, a complete checkpoint has to occur since the last log clearing/truncation before the next one can happen. If a checkpoint hasnít occurred, the log_reuse_wait_desc for the database in sys.databases will return CHECKPOINT. Itís not common to see this occur, but you might see it if thereís a very long running checkpoint (e.g. a very large update on a system with a slow I/O subsystem so the flushing of data file pages takes a long time) and very frequent log backups, so two log backups occur over the time taken by a single checkpoint operation. It could also happen if youíve messed with the sp_configure recovery interval and set it higher than the default.

No.
When snapshot isolation is enabled, deletes from a heap are ghosted, as part of the overall versioning process, which can lead to some interesting side-effects. A versioned record has an extra 14-bytes tagged on the end, so a heap record that suddenly becomes versioned is 14-bytes longer ñ which may mean it doesnít fit on the page any longer. This could lead to it being moved, resulting in a forwarding/forwarded record pair ñ just because the record was deleted! Now, the page has to be full for this time happen, and the Storage Engine will take steps to avoid this happening for rows less than 32 bytes long.

All the pages and extents are locked. The table doesnít show them as allocated any more but because theyíre exclusively locked, the allocation subsystem canít really deallocate them until the locks are dropped (when the transaction commits).
They canít be reused until theyíre really deallocated. If a transaction rollback happens, the pages are just marked as allocated again.
Use the DMV sys.dm_tran_locks.

If there was a clean shut down, SQL Server marks it in database boot page, and so knows that it does not need to run recovery, during restart, and therefore does not need the original transaction log. In such cases, we can attach the database without the log, and SQL Server will simply create a new log.
However, if there is no mark indicating a clean shutdown, and this is never guaranteed, then SQL Server must run recovery during restart, which it canít do without the original log, and which is the reason why youíve ended up with a RECOVERY_PENDING database. It simply means there wasnít a clean shutdown and SQL Server canít start the recovery process because you whacked the log.
The recovery process uses the contents of the log to re-establish transactional consistency. During recovery SQL Server will redo the effects of any transaction that was not included in the last CHECKPOINT to run before the database went offline, and undo the effects of any uncommitted transaction that was included in the last CHECKPOINT.

It could be a problem, yes. Generally, it depends on the overall size of the log. Itís common, and not really an issue, to see a very large transaction log with several hundred VLFs, but several thousand is a concern regardless of overall log size.
Always try to shrink the log file, which will help us in reducing the VLF numbers.

The TRUNCATE command is not a non-logged operation. In fact, it is fully logged. With very few exceptions, namely one or two operations in tempdb such as operations on the version store, there is no such thing as a non-logged operation in SQL Server. Operations such as TRUNCATE TABLE and DROP TABLE are fully logged, but SQL Server uses an efficient, deferred de-allocation mechanism that means the commands seem to be instantaneous, regardless of the size of the table. This fact, coupled with some misleading terminology, including in several Microsoft articles, lead to the mistaken belief that these operations are not logged at all.

When operating a database in FULL or BULKLOGGED recovery model, all log records must remain in the log, as part of the active log, until they have been captured in a log backup. Otherwise, the log backups could not guarantee to capture the complete, unbroken chain of LSNs that are required for database restore and recovery.
In these recovery models, only a log backup will cause SQL Server to run the ìlog clearingî process, also referred to as log truncation. During log truncation, SQL Server will mark as inactive any VLFs that contain no part of the active log, i.e. VLFs that contain no log records that SQL Server still requires for recovery, or for log backup, or for any other process.
SQL Server will run log truncation after every log backup and, each time, any inactive VLFs that result will be available for reuse to store new log records, overwriting the existing log records that are no longer required. Therefore, when SQL Server reaches the end of the log.
However, itís possible that there will be periods where successive log truncations produce zero inactive VLFS, because there are other factors that can prevent SQL Server reusing space in the log. If there are no inactive VLFs available, then SQL Server has no other choice but to grow the log, adding more VLFs.
This is likely what is happening in this case; some other process is forcing SQL Server to retain log records in the active log, and so successive executions of the log truncation process arenít producing any more inactive VLFs. Attempts to shrink the log will have no effect in this situation as there is no free space to remove! DBCCSHRINKFILE can only physically remove unused or inactive VLFs that are at the end of the log file.

We must have data files equivalent to number of CPU processors or if CPU is greater than 8, then at least we have 8 data files for TempDB and the size of data files must be same.
As well always mount the TempDB on separate disk for better performance.