SQL – What version of SQL 2005 server am I running?

You can check the version of SQL server running on a machine using the following command in MSQL server management studio:

select @@version

To determine the product version (for example, 9.00.1399.06), the product level (for example, RTM) and the edition (for example, Enterprise Edition) run the following:

SELECT  SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

Advertisements

Moving TEMPDB in SQL Sever 2005

 

As advised by Microsoft I needed to move the TEMPDB and logs away from the C: drive, in this case I have moved onto a folder called TEMPDB on F:

The following code shows the current location of TEMPDB and logs;

USE TempDB
GO
EXEC
sp_helpfile
GO;

you should see something like this

tempdev 1
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf
PRIMARY
6608000 KB
Unlimited
10%
data only

templog 2
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf
NULL
29504 KB
Unlimited
10%
log only

The following code creates new Tempdb and logs in the new location provided;

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
    (NAME = tempdev, FILENAME = ‘F:\TEMPDB\tempdb.mdf’)
GO
ALTER DATABASE TempDB MODIFY FILE
    (NAME = templog, FILENAME = ‘F:\TEMPDB\templog.ldf’)
GO 
 

The new current location of TEMPDB and logs is;

tempdev 1
F:\TEMPDB\tempdb.mdf
PRIMARY
145792 KB
Unlimited
10%
data only

templog 2
F:\TEMPDB\templog.ldf
NULL
768 KB
Unlimited
10%
log only

 

 

SQL 2005 SERVER SET UP

I have set up a new server this week to host SQL server 2005 to manage my count engine.

Spec:

 – Antec Sonata 3 Super Case with 500W aPFC PSU
 – Abit AB9 WiFi iP965, S775, PCI-E (x16),
    DDR2 533/667/800, SATA II, SATA RAID, ATX
 – 4GB (2x2GB) Corsair TwinX XMS2, DDR2 PC2-6400 (800),
    240 Pins, Non-ECC Unbuffered, CAS 5-5-5-18
 – Intel Core 2 Quad Q6700, Kentsfield Core, Socket 775,
    2.66 GHz 1066MHz 8MB Cache, Retail
 – Pioneer DVR-215DBK 20x DVD±R, 10x DVD±DL,
    DVD + RW x8/-RW x6, SATA, Black, OEM
 – 1000 GB Hitachi HDS721010KLA330 Deskstar 7K1000,
    SATA II, 7200 rpm, 32MB Cache, 8.5 ms, NCQ
 – 500 GB Hitachi 0A33437 Deskstar 7K500, SATA II,
    7200 rpm, 16MB Cache, 8.5 ms, NCQ
 – Edimax 9270TX 64Bit 10/1000 Gigabit (GbE) PCI
    Network Card

I’ve done a lot of research into getting the most out of my hardware. After some time I concluded the following set up to be the best;

Windows Server 2003 OS
SQL Server 2005 32Bit.

To get the most out of the RAM using the 32Bit installation of SQL Server 2005 I needed to to tweak the boot sequence using the /3GB switch – to allow 3 og the 4 GIG of RAM available for processing.

Start –> Right-click My Computer –> Properies.
Advanced Tab –> Startup and Recovery –> Settings –> Click the Edit button to edit BOOT.ini

Boot.ini;

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS=”Windows Server 2003, Enterprise” /noexecute=optout /fastdetect /3GB

the /3GB switch applies to all 32-bit applications running on the box (not just SQL Server).
It reduce the kernel mode memory to just 1GB, thereby allowing the user mode portion 3 of the 4GB in the virtual address space.

The next key setup process was to set up the HDD to spread the work load. The following is how Microsoft suggest setting up HDD in SQL server;

C:  (RAID1 mirror for redundancy)
O/S
SQL Installation

D:  (RAID1 mirror for improved read and redundancy, or RAID10 for improved read/write and redundancy)
MDF Files

E: (RAID1 for redundancy if you are not backing up database or log files to a NAS, otherwise just a standard drive is ok)
Log Files
 
F: (RAID0  for fast read/write, no redundancy required as it’s just temp working, or a standard drive)
TempDB

I only had 2 HDD in my server so I did the following;

c:
Winders Server 2003
SQL Server 2005

F:
Data Files (MDF)
LOG Files
TEMPDB

which works fine for my purposes. If I need to increase processing speed I’ll add further HDD in the future.