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.
Friday, January 28, 2011
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.
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
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)
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)