SQL Server 2008 Internals

I have been reading Kalen Delaney articles and books so many years. She is one of the world best SQL Server Specialist.

SQL Server running on Windows Server 2008 R2 Enterprise (x64)

It is amazing to work one of the high-end SQL Server 2005(64-bit) running on Windows Server 2008 R2 (x64-bit) Enterprise edition with 130GB of RAM. The Intel X7560 processor has 24Logical processors for the entire server.

Move the Tempdb database to new location

Move the tempdb database to new location to speed up the system

-- Find out the current default location of the tempdb database when you installed in SQL 2005
sp_helpdb tempdb

tempdev 1 E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf
templog 2 E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf


-- Move to new location for fastest disk
USE master
go

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\MSSQL\Data\tempdb.mdf')
go

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\MSSQL\Data\templog.ldf')
go

Database Recovery Model and Compatibility Level

Find the Recovery model and compatibility level for all databases on instance.

SELECT [name], recovery_model_desc, [compatibility_level]
FROM sys.databases;