Friday, January 28, 2011

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.

Monday, January 24, 2011

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. 

Sunday, January 23, 2011

Contact Me

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

Thursday, January 20, 2011

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



 

Sunday, January 09, 2011

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)