TempDB files Recommendations for SQLSERVER

•             Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents tempdb from expanding too frequently, which can affect performance. 

•             The tempdb database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions. 

•             Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs.

•             Make each data file the same size; this allows for optimal proportional-fill performance.

•             Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many 

directly attached disks.

•             Put the tempdb database on disks that differ from those that are used by user databases.

•             No. Of CPU v/s Data Files Recommendation

No. of Cores       Data files recommended

<8           = No. of Cores

>=8 to <32           = No. of Cores/2

>=32      = No. of Cores/4

How to recover a Database when the below issues occurs.

In this Scenario, the database will be unavailable  and not in suspect mode

1) when the log file is full and log file drive does not have any free space
2) No option to expand the existing storage
3) No impact on Data loss
4)Log file is not corrupted
 

Solution

1) Change the database Status to Emergency State

ALTER DATABASE [DBName]SET EMERGENCY ;

2)In this Mode , you will able to truncate database by changing the  recovery model to Simple.

In order to enable recovery you need to put the Database in Single User mode

3) ALTER DATABASE [DBName]SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

4)DBCC CHECKDB (DBName, REPAIR_ALLOW_DATA_LOSS);

Once DBCC CheckDb completed , Database will be online and accessible in single user mode.

5)ALTER DATABASE [DBName] SET  MULTI_USER;

Now Database will be online and accessible for further operation. 

So you can shrink the log file and release the space to system.

Hope this works.

 to enable recovery you need to put the database into SINGLE_USER
mode as well. To do so, execute - See more at: http://solutioncenter.apexsql.com/recover-data-from-detached-corrupt-sql-server-database/#sthash.Czk29S3a.dpuf

In order to enable recovery you need to put the database into SINGLE_USER
mode as well. To do so, execute - See more at: http://solutioncenter.apexsql.com/recover-data-from-detached-corrupt-sql-server-database/#sthash.Czk29S3a.dpuf

In 

WELCOME TO SQL SERVER MANTRA

This Blog is Fully dedicated for SQL SERVER to create ,share and explore the  ideas about the SQL Server in the relational database management system world.