Finding version of service pack installed on SQL server

There are times when we have to look for service pack installed on SQL Server Database Servers. SQL server provides us with many different ways to check service pack installed. Some of most commonly used methods are given below

1). @@version

Using @@version we can see information about SQL server installed on machine

The syntext are

Print @@version

The result set comes as something like this

Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86)

Feb 26 2008 18:15:01

Copyright (c) 1988-2005 Microsoft Corporation

Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

The informations shows that the machine is running SQL server 2005 on 32 bit processor (Intel X86) with windows xp as operating system (windows NT 5.1) and service pack 2 installed.

2). serverproperty (’productlevel’)

This is the best way to find service pack installed because it gives to the point information for what we have enquired. The syntax is

print cast(serverproperty(’productlevel’) as nvarchar)

Productlevel attribute of serverproperty construct actully is used to find the service pack installed we have just cast the result tinto nvarchar so that we can print it to result window.

3).Through SQL Server Configuration Manager

This is my favorite one , because through this way one should not have o write any line of code to get the service pack installed . I like it because some times I have to tell this to system administrators also and they don’t like working inside the management studio. They need a way through which they can find the service pack without making any connection to sql server ot we can say with out writing any code. The steps to find service pack through configuration manager is as below

• Open sql server configuration manager
• Select sql server 2005 services
• On right side select the sql server service
• Right click on SQL service and select properties
• From dialog box open, select advanced tab
• And look for “service pack level” in right pane of grid control.

Also there is internal version numbers which are associated with each release of SQL server

Some of them are given below

	RTM	SP1	SP2	SP3	SP4	SP5	SP5A
Ver 6.00	6.00.121	6.00.124	6.00.139	6.00.151
Ver 6.5	6.50.201	6.50.213	6.50.240	6.50.258	6.50.281	6.50.415	6.50.416
Ver 7.00	7.00.623	7.00.699	7.00.842	7.00.961	7.00.1063
Ver 2000	8.00.194	8.00.384	8.00.534	8.00.760	8.00.2039
Ver  2005	9.00.1399.06		9.00.3068.00

OLAP Databases with SQL Server 2005

With the arrival of SQL server 2005. it become the no 1 solution for OLAP databases(for windows server system). It is often consider that one database model can be used for OLAP and OLTP database. OLAP stands for Online Analytical Processing, this kind of database are used for a analysis services and have its own special requirement. OLAP database tools (E.g. SQL server analyses services) enable users to analyze different dimensions of multidimensional data. For example, it provides time series and trend analysis views. OLAP often used in data mining operations. The data structure that OLAP create from the relational data is called OLAP cube. OLAP cubes can be thought of as multi dimensional array. A business might want to analyze its sales data by product, by product category, by sales manager, or something else. These different analyzing criterions are the OLAP cube dimensions. On other hand OLTP (Online Transaction Processing) databases, as the name implies, handle real time transactions which inherently have some special requirements.. OLTP databases must be atomic in nature (an entire transaction either succeeds or fails, there is no middle ground), be consistent (each transaction leaves the affected data in a consistent and correct state), be isolated (no transaction affects the states of other transactions), and be durable (changes resulting from committed transactions are persistent). All of this can be a fairly tall order but is essential to running a successful OLTP database.

 

 

Some of the major differences are

 

OLTP	OLAP
This stores current data	This stores History data for analysis
Optimizes update performance by minimizing the number of indexes	Optimizes adhoc queries by including lots of indexes
This is fully normalized	Possibly partially denormalized for performance reasons. As this is used for reporting
Data stored revolves around business functions	Data stored revolves around information topics.
Stores typically coded data.	Stores descriptive data
Scattered among different databases or DBMS and using different value coding schemes	Centralized in data warehouse. Or in a collection of subject oriented data marts
Transaction recovery is necessary	Transaction recovery is not necessary
Online update/insert/delete	Batch update/insert/delete

 

 

 

SQL Server support for OLAP database systems

 

SQL Server 2005 Analysis Services (SSAS) for SQL server provides unified and integrated view of all your business data as the foundation for all of traditional reporting, online analytical processing (OLAP) analysis, Key Performance Indicator (KPI) scorecards, and data mining.

 

Advanced features includes

 

• Translations, Translations provide a simple and centrally managed mechanism for storing and presenting analytic data to users in their preferred languages.
• MDX Scripts, Multidimensional Expressions (MDX) scripts are the new mechanism for defining calculated members, named sets, and cell calculations.

• Business Intelligence Wizards, A set of easy-to-use wizards can help even the most novice user model some of the more complex business intelligence problems.
• Semi additive Measures, This new measure aggregation type for advanced data modeling includes last-nonempty, last-child, first-child, average-of-children, and even by-account-type.

• Data Mining, Analysis Services provides tools for data mining with which you can identify rules and patterns in your data, so that you can determine why things happen and predict what will happen in the future-giving you powerful insight that will help your company make better business decisions.

New features includes

• Microsoft SQL Server 2005 - Interoperability with the 2007 Microsoft Office System
• Microsoft SQL Server Data Mining Add-Ins for Office 2007
• Microsoft SQL Server 2005 Analysis Services Performance Guide

 

Related Links :-

Microsoft Analyses Services

Moving tempdb to new location in SQL server 2005

As a sql server administrator, many time i have seen “tempdb out of space” problem. the best solution to solve this problem is to move the temp db to new location so that it has enough space to expand

given below is the procedure to move tempdb database to newer location

1. Open SQL Server Management Studio.

2. Connect to the desired server.

3. Click the New Query button.

4. Copy and paste the following into the query pane:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘{new location}\tempdb.mdf’);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘{new location}\templog.ldf’);
GO

5. Change {new location} in the pasted code (for both the tempdb.mdf and templog.ldf files) to the path of the new location.

6. Click Execute.

7. Go to the Control Panel and then Administrative Tools. Select Services.

8. Stop and Start SQL Server (MSSQLSERVER).

9. Go back to SQL Server Management Studio and open a new query pane.

10. Copy and paste the following to verify that tempdb has moved to the new location:

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(’tempdb’);

11. Click Execute.

12. In the physical_name column, you should see the path to the new location.

hope this will solve tempdb space issue’s