How to reduce the allocation resource contention for tempdb

Concurrency enhancements for the tempdb database

To reduce the allocation resource contention for tempdb that is experiencing heavy usage

Solution:
1. In that SQL Server Configuration Server Propertise Add trace flag    ;-T1118
2.Increase the number of tempdb data files to be at least equal to the number of processors. Also, create the files with equal sizing( ( Microsoft Best practice guide line says, It may be good for SAN), You need carefully plan this one if your sql server really want it)

Always try to test before move on to production.

The transaction log for Principal database 'My_DB' is full

The database mirror environment Principal server database 'My_DB' Transaction log is full.

Principal database 'mydb' (Principal, Suspended.) is not Synchronized with mirror database.

Error: 9002, Severity: 17, State: 5


The transaction log for database 'My_DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases


Solution:
1. Run DBCC SQLPERF ('logspace')
2. Check the SQL Server error log
3. ALTER DATABASE My_DB to increase log file size
4. Run DBCC SQLPERF ('logspace')

5. Resume Database sync

Slowly the transaction log spaced used will be decreasing. 

Contact Me

If you need a SQL Server consulting services for your ogranisation please email me  ponnu at hotmail.co.uk  directly.

How do you find SQL Server Backup File Version?

When you have a different backup files form various SQL Servers. How do you find a version of  SQL Server backup ? ( SQL Server 2008, 2005, 2000)

Restore Headeronly from disk='E:\backup\MyDb.bak'

SoftwareVersionMajor
10  for SQL Server 2008
9    for SQL Server 2005

8    for SQL Server 2000



 

The transaction log for database tempdb is full

If you get the error message for Tempdb Transaction log is full.

Msg 9002, Level 17, State 4, Procedure sp_helpdb, Line 19

The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Solution: 1

-- First check Tempdb Tran log file size using  dbcc sqlperf(logspace)

-- tempdb 999.9922 99.99628 0

USE MASTER
GO
ALTER DATABASE TEMPDB MODIFY FILE (NAME='templog', SIZE=1500MB)

Solution:2

ALTER DATABASE Tempdb
ADD LOG FILE
( NAME = tempdblog2,
FILENAME = 'E:\MSSQL.1\MSSQL\DATA\tempdblog2.ldf',
SIZE = 10MB,
MAXSIZE = 100MB,
FILEGROWTH = 10MB)

Error message 14421 that occur when you use log shipping in SQL Server

Error: 14421, Severity: 16, State: 1


The log shipping secondary database NY-SQLSRV001. AdventureWorks has restore threshold of 45 minutes and is out of sync. No restore was performed for 150 minutes. Restored latency is 1154 minutes. Check agent log and logshipping monitor information.
 
Troubleshooting Error Message 14421


By definition, message 14421 does not necessarily indicate a problem with Log Shipping. This message indicates that the difference between the last backed up file and last restored file is greater than the time selected for the Out of Sync Alert threshold.

Solution:
1. Disable LogShipping Settings on user Database AdventureWorks from Primary Server.
2. Take the Full backup of user database AdventureWorks from Primary Server
3. Copy the backup file to Secondary server Shared Network folder
4. On Secondary server open the Query Report to run the following statement
-- Recover a Database from a Backup Without Restoring Data
-- Restore database using WITH RECOVERY.

RESTORE DATABASE AdventureWorks WITH RECOVERY
5. Now the User database AdventureWorks allow you to restore with NORECOVERY mode
6. Restored the AdventureWorks on Secondary Server from Backup file
7.Setting up Logshipping on Primary Server to Secondary server

If you use the linked server feature with SQL Server 2005 and 2008

If you use the linked server feature with SQL Server 2005 and 2008, please read through this carefully.
Microsoft Customer Support team discovered several problems that can result in memory leak(s).

http://blogs.msdn.com/b/psssql/archive/2009/09/22/if-you-use-linked-server-queries-you-need-to-read-this.aspx