Monday, August 4, 2014

SQL Server - How to resolve TempDB get FULL issue.

Please find the complete details to troubleshooting and resolving If the TempDB database get full:-

 Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL.

 Reason for temp DB full.
 1. Heavy transaction activity
 2. Due to maintenance job (Index fragmentation etc)
 3. Due to inventory closing or any other such type activity
 4. Mount Drive\ Volume does not have sufficient space to grow temp db log files.
 5. Auto growth is not enable in tempdb
 6. Bulk Operation

 How to check if TempDB database get FULL :-

--Log space usage
dbcc sqlperf(logspace)
 --Open Tran
dbcc opentran(tempdb)
-- VLF (Virtual Log File)
  use tempdb
  dbcc loginfo()

 Status - 2 (active log)
 Status - 0 (Inactive Log)
  •  To truncate or shrink the log file there should be continuous inactive log.
1. Simple and effective solution is to re-start the SQL server but in production environment we do not have privilege to restart the SQL services.
 2. We can use below quires to shrink the log file -
    dbcc shrinkfile (templog, 0)

 3. Create new log file in some other volume and cab the existing one to stop the auto growth.
 4. Perform failover if it’s in cluster if shrinking will not resolve the issue and we have necessary approvals from business.

Like and Share to SQL Integrity Blog

SQL Server- How many IP Addresses used in SQL Server Clustering?

  • 2 IP for heartbeat network (1 per node)

  • 2 IP for public network (1 per node)

  • 1 IP for Windows Cluster.

  • 2 IP for SQL Server Virtual Network Name (SQL instance on each node)

  • 1 IP for MSDTC (this is optional however required if you use distributed transaction like linked server)

For Single Instance single cluster ( active/passive)
In above instead of two you will only need one IP address for SQL Server virtual network name.

Like and Share to SQL Integrity Blog