Monday, April 26, 2010

Learn SQL Server 2008 for free


Microsoft makes it easier than ever to attend SQL Server 2008 training, at little to no cost, without any travel necessary. 

It just needed a email account on hotmail or msn.

Subject matter includes:

· Database Administration

· Database architecture

· Database, programming and security. 

Click the courses below to see full descriptions or visit Microsoft Learning to browse the course catalog

DBA Track

Course 6339: Database Fundamentals in Microsoft SQL Server 2008

Clinic 10259: SQL Server 2008: Database Infrastructure and Scalability

Clinic 6188: What's New in Microsoft SQL Server 2008 for Enterprise Data Platform

Collection 6187: What’s New in Microsoft SQL Server 2008 


Developer Track

Clinic 6190: What's New in Microsoft SQL Server 2008 for Database Development

Clinic 10164: Essential SQL Server 2008 for Developers

Clinic 10258: SQL Server 2008: Upgrade and Application Compatibility


Reporting Service

Clinic 6258: New Features Of Microsoft SQL Server 2008 Reporting Services


Business Intelligence

Clinic 6189: What's New in Microsoft SQL Server 2008 for Business Intelligence

Sunday, April 25, 2010

DAC – Dedicated Administrator Connection


Use Command below to connect as DAC



It is on by Default for local server.

For remote(via network) enable it by running query below

'remote admin connections'


For Clusters, recommended to enable remote admin connections as no local DAC available


---- Query to Find if DAC - Dedicated Admin Connection used -----


select s1.session_id, s1.program_name,s1.host_name,


sys.tcp_endpoints t1

sys.dm_exec_sessions s1

on t1.endpoint_id = s1.endpoint_id

where =
'Dedicated Admin Connection'

--- End of the Query -----

--- Query to check the DAC ----

--- Scheduler_ID = 255 & Status = "VISIBLE ONLINE (DAC) ---




when SQL Server hangs, SQL Server does not respond, when SQL Server does not accept new connection

  • Use DAC sparingly for monitoring and Troubleshooting
  • Connect to master database first
  • Use lightweight queries.
  • Not advised for long running queries like ( DBCC CHECKDB, DBREINDEX, INDEXDEFRAG )

Friday, April 23, 2010

SQL Server Default Collation

Technorati Tags:


SQL Server Default CollationDefault_Collation

Thursday, April 22, 2010

SQL Server Latest Service Pack and Security Hot fixes


Applies to

Version Range



Target Version

Release Date

Security Bulletins




SQL Server 2000

Below 8.00.2039

Service Pack

SQL Server 2000 SP4






SQL Server 2000



SQL Server 2000 SP4 GDR






SQL Server 2000



SQL Server 2000 SP4 QFE






SQL Server 2005

Below 9.00.4035

Service Pack

SQL Server 2005 SP3






SQL Server 2005



SQL Server 2005 SP3 GDR






SQL Server 2005



SQL Server 2005 SP3 QFE






SQL Server 2008

Below 10.00.2531

Service Pack

SQL Server 2008 SP1





It lists only the latest SQL Server Service Pack and Security Hot fixes as on 22nd April 2010


Software Asset Management - SAM

Software Inventory Assistance

Whether we have just a few computers or a large network, a software inventory tool can make it easier to quickly know what we have and how it's being used.

The Microsoft Software Inventory Analyzer (MSIA) is a free tool available for download that can help us with our software inventory. It is built specifically to be a starting point to working with SAM.

We can use the MSIA to scan and inventory the Microsoft software that is installed on a single computer, or on multiple computers throughout a network. It generates a report that provides details of all installed Microsoft products, including the type and the number of licenses. The MSIA will work with networks that have 250 or fewer computers. The latest MSIA 5.1 features enable you to:

  • Identify and report all installed Microsoft products listed under Add or Remove Programs.
  • Consolidate the MSIA report with the Microsoft Licensing Statement (MLS) report (.xls only).

This information is useful for you, if you are taking care of the system administration, licensing and inventory of Microsoft software products in your organization.


BgInfo – Microsoft Windows SysInternal tool

How many times have you walked up to a system in your office and needed to click through several diagnostic windows to remind yourself of important aspects of its configuration, such as its name, IP address, or operating system version If you manage multiple computers you probably need BGInfo. It automatically displays relevant information about a Windows computer on the desktop's background, such as the computer name, IP address, service pack version, and more. You can edit any field as well as the font and background colors, and can place it in your startup folder so that it runs every boot, or even configure it to display as the background for the logon screen.

Wednesday, April 21, 2010

SQL Server 2005/ 2008 Query to find when the database last used

Below is the TSQL Query to find when the database last accessed for SQL Server 2005 / 2008

-- Query to find when the database last accessed on SQL Server 2005 / 2008 ---

select, x1 =

(select X1= max(bb.xx)

from (

    select xx = max(last_user_seek)

        where max(last_user_seek) is not null

    union all

    select xx = max(last_user_scan)

        where max(last_user_scan) is not null

    union all

    select xx = max(last_user_lookup)

        where max(last_user_lookup) is not null

    union all

        select xx = max(last_user_update)

        where max(last_user_update) is not null) bb)

FROM master.dbo.sysdatabases d

left outer join

sys.dm_db_index_usage_stats s

on d.dbid= s.database_id

group by




Differentiate your connections to SQL Instances by coloring it

What would be the case. have you ever connected to a server, thought it was a testing or development system, and only a split second after you pressed "F5" to run that command, realized that it was the production server? Yeah….me neither :).

To make sure that's never you, when you start SQL Server 2008 Management Studio (SSMS), stop for a moment on the connection dialog and press "Options". You'll see that you can select a color for the connection – I set mine to red for production servers, yellow for testing servers, and green for development servers.

Once inside SSMS, you can also set the default color for all connections – I do this and set them all to red, so that if I forget, at least I'll have that visual indicator to make sure the change I'm making is on the right server.

It helps on classifying the SQL Server Instances by Production, Development, Test or QA etc.

Tuesday, April 20, 2010

Format Date or Time without “/”

Format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement


select replace(convert(varchar, getdate(),111),'/','')


select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')

20100413 004426

Select cast(int, convert(char,getdate(),111))


Monday, April 19, 2010

Remote Desktop Error - The terminal server has exceeded the maximum number of allowed connections

When trying to connect using a remote desktop session you may get this Login Failure Message, because there are too many remote sessions

To resolve the exceeded connection issue execute "mstsc /admin" ( mstsc /console )command from the command prompt as shown below

If a terminal server has exceed the maximum number of sessions an "/admin" session can still be created and this will allow you to still connect to the server.

The /admin sessions don't count towards the session limit that may be configured on a terminal server to limit the number of remote sessions, so this will give you a back door into the server if you get the message "The terminal server has exceeded the maximum number of allows connections".

Monday, April 12, 2010

SQL Server - Patching : An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems


An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems


The SQL Server team is moving away from the current priority-driven hotfix release model to a scheduled delivery model. In the scheduled delivery model, a customer can receive a hotfix to address their most critical situations with a short turn-around time. Additionally, a customer can receive a fix that has undergone more testing and that is released on a schedule basis. Therefore, the SQL Server team has created the following delivery mechanisms.

Critical On Demand - COD

On Demand- OD

Cumulative Update - CU

General Distribution Release - GDR

The hotfix is released on or before a mutually agreed upon date based on the customer's need

The hotfix is released on or before a mutually agreed upon date based on the customer's need

The update is released every 2 months.

A GDR addresses an issue that has a broad customer impact, that has security implications, or that has both. A GDR is determined and issued by Microsoft as appropriate and when appropriate. GDRs are kept to a minimum.


It contains all previous critical on-demand hotfixes to date.



ISM eliminates the need for hotfix rollup releases. The first CU after a new release contains the hotfix rollup, plus any additional QFEs.

A hotfix package does not replace a service pack. A hotfix package is optional. A hotfix package can be installed or uninstalled at any time. Additionally, hotfix packages are cumulative. Therefore, the latest OD hotfix package or CU hotfix package includes all previously released hotfixes.

Quick Fix Engineering - QFEs are used for the majority of fixes where the effects of the problem are not widespread or severe enough to warrant a GDR.