Saturday, December 18, 2010

Download Microsoft SQL Server 2005 Service Pack 4 RTM


Service Pack 4 (SP4) for Microsoft SQL Server 2005 is now available for download.

SQL Server 2005 SP4 includes SQL Server 2005 SP3 cumulative update 1 to 11, customer requested fixes, along with instances of the SQL Server 2005 SP4 database Engine support for DAC operations.

It can be downloaded from

Microsoft SQL Server 2005 Service Pack 4 RTM

Thursday, December 16, 2010

SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.


SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

When we take the database OFFLINE this error raised.


Wednesday, December 15, 2010

The server principal is not able to access the database under the current security context.


I reproduced the error below;

Issue 1:

Msg 916, Level 14, State 1, Line 1
The server principal "Test_Login" is not able to access the database "testdb" under the current security context.

1. Created database ‘testdb’

2. Created the login ‘Test_Login’. The login not granted access on the database ‘testdb’

3. On logging on ‘Test_Login’, tried accesing the database ‘testdb’ and got the error.


From the error message, when the server principal ( login) does not have access on the database the error occurs.

Resolution 1:
Grant the database access for the login. For example;

Use testdb

Create User Test_Login for Login Test_Login

Issue 2:

The database is in “Restricted Mode”


Try the query below, to access the database in different user context

Use testdb
execute as user='test_login'

Msg 916, Level 14, State 1, Line 1
The server principal "Test_Login" is not able to access the database "testdb" under the current security context.

Reference : 
"The server principal LoginName is not able to access the database DatabaseName under the current security context"

Sunday, December 05, 2010

TSM TDPSQL Backup failed


Recently when i tried TSM TDPSQL backup with the new server on SQL Server 2008 failed with the error below.

“ TSM TDP SQL Error: Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. Process Exit Code 1914. The step failed. “


After investigation, installed the TSM TDP SQL version from 5.5.0. This resolved the issue.

Saturday, December 04, 2010

Uninstall SQL Server 2008 Cluster


Here goes the SQL Server 2008 un-installation screenshots.










Thursday, November 18, 2010

TSQL Query to find the DB Maintenance status


Are you curious to find the Database Backup / Restore status?
Do you have to find out the status the DBCC CHECKDB ?
Do you have to report when the ROLLBACK would finish ?
Do you want judge how long the Database Shrink will take?

Here is the answer!

The query listed below can be used to find the Percentage of work completed for the following commands:


We have to replace the where condition filter based on the requirement.

SELECT command,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests Req
CROSS APPLY sys.dm_exec_sql_text(Req.sql_handle) sqltext
WHERE req.command in ('DBCC CHECKDB')

Wednesday, October 20, 2010

On SQL Server Management Studio, How to list only the database, which user has access ?


The default behavior of SSMS when we login, it will show all the databases available on the SQL Server Instance. Irrespective of access on the databases user has.

The reason behind is, to load the SSMS faster. Otherwise, while loading the SSMS SQL Server has to verify for each databases if the user has access or not. Also if any of the databases or offline or closed, it has to figure out if that database has to be listed or not. All this takes time.
Hence by default the SSMS will show all the databases on SSMS.

If we really required to show only the database user has access, please use the command below to restrict the show all databases on SSMS,


When VIEW ANY DATABASE is revoked, a user can only see master, tempdb, any database he owns, and the user’s current database context.

Thursday, October 14, 2010

Impact of renaming “sa” account on SQL Server 2008


Error: 912, Severity: 21, State: 2

Microsoft has confirmed that this is a problem in the Microsoft products that are listed 

  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Standard
  • This problem was first corrected in SQL Server 2008 Service Pack 1


    The upgrade process includes a script to create the SP_Syspolicy_Create_Purge_job stored procedure. The procedure creates the syspolicy_purge_history job and hardcodes the owner of the job to be the sa account. Because the sa account is renamed, the job creation fails.

    Workaround:  recreate the sa account

    Reference:  FIX: Error message when you try to upgrade an instance of SQL Server 2005 to SQL Server 2008: "Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes"

    SQL Server 2008 Service Pack 2 - Installation


    Here Let me show the step by step on SQL Server 2008 SP 2 Installation











    SQL Server Job fails with error “Unable to open step output file. The step failed”


    Are you getting the error message below on a SQL Server Job?



    See if the SQL Agent Service account have sufficient privileges.

    I have encountered this error when I change the SQL Server Agent account.
    Resolved giving proper rights on the folder.

    Saturday, October 09, 2010

    Processing order of SELECT Statement


    The following steps show the processing order for a SELECT statement.

    1. FROM

    2. ON

    3. JOIN

    4. WHERE

    5. GROUP BY


    7. HAVING

    8. SELECT


    10. ORDER BY

    11. TOP

    This is the reason where we can not use column alias on Where clause, however we can use it on Select / Order by.

    If interested on the logical order of the query processing poster, download it from here.


    Thursday, October 07, 2010

    SQL Server 2008 Service account Privileges


    Please refer the 
    on setting up Windows Service Accounts.

    It discuss about the

    • Domain User Account
    • Local User Account
    • Local Service Account
    • Network Service Account
    • Local System Account
    • Windows NT Rights and Privileges Granted for SQL Server Service Accounts
    • Access Control Lists Created for SQL Server Service Accounts
    • Windows Permissions for SQL Server Services

    Wednesday, October 06, 2010

    Can we have SQL Server 2000 default Instance and SQL Server 2005 default Instance on the same server?

    The default instance could be an installation of

    SQL Server 2000,
    SQL Server 2005,
    SQL Server 2008, or
    SQL Server 2008 R2.

    Only one installation of SQL Server, regardless of version, can be the default instance at one time

    Wednesday, September 29, 2010

    SQL Server 2008 Service Pack 2 released


    SQL Server 2008 SP 2 released on 29 Sep 2010.

    To download the SQL Server 2008 SP2 release please select from the links below:

    Tuesday, September 14, 2010

    Oracle Linked Server issue

    Today I got the error below on the SQL Server job it runs a SSIS Package.

    Msg 7302, Level 16, State 1, Server USIVA007, Line 1
    Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server

      Strange but recreating of the Linked Server connection, resolved the issue.

    Techniques to Debug Connectivity Issues to an Oracle Server Using the ODBC Driver and OLE DB Provider

    Tuesday, August 31, 2010

    SQL Server Performance Optimization Seminar in Chennai by Sep 24 – 15, 2010


    This seminar series targeting the audience across the world. It is begin from Chennai, India.

    What is unique about the seminar & why i am interested in it

    • Presenter Pinal Dave, well known on SQL Server community for his presentation skill
    • Solid Quality Mentors differ from usual training / mentoring by giving real time & practical sessions
    • It’s worth the time spent since learning the tips may take years
    • More of interactive session with presenter rather dull theoretical sessions
    • Real time experience on the issues faced by customers
    • Unique in design
    • Immerse in deep on the concept of SQL Server Performance optimization
    • Follow up sessions to keep engaged on what we learned

    What can we get out of the two day session;

    Day 1:  Sep 24th 2010 Day 2: Sep 25th 2010
    Resolution to popular misconception on SQL Server features Evils of Index and workarounds
    The unknown Tricks and Tips of sub queries The Intelligence of Statistics
    The Relation between External and Internal Joins Query Optimization for unknown values
    Triggers and Views Query Optimization techniques
    Question hour Question hour

     Registration Form

    Contacts : For clarifications, please call +91 98202 24429

    Further details:

    About the Solid Quality Mentor:

    • Solid Quality Mentors is the only company in the world with the largest number of the top experts on Microsoft’s Data Platform.
    • A Microsoft Gold Certified Partner


    About the seminar presenter – Pinal Dave:

    Pinal Dave is a Database Architect and Corporate Trainer specializing in SQL Server Query Optimization and Performance Tuning having 7 years of rich, hands-on experience. He has been awarded as the Microsoft Most Valuable Professional (MVP) in SQL Server technology and is also a Microsoft Certified Trainer (MCT)


    Tuesday, August 10, 2010

    MOSS – Microsoft Office Sharepoint Server 2007 SQL Server 2005 / 2008 Database Maintenance


    There is a whitepaper in detail SQL Server 2005 /2008 Maintenance for MOSS ( Sharepoint ).

    Database Maintenance for SharePoint

    Sunday, August 08, 2010

    SQL Server 2008 R2 Update for Developers Training Kit


    The SQL Server 2008 R2 Update for Developers Training Kit  is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in their applications, as well as for developers who are new to SQL Server. The training kit is brought to you by Microsoft Developer and Platform Evangelism.

    Download it from here, it sizes 216.5 MB.

    It contains PPT Slides, Video, Demo and Hand on Lab

    Sunday, August 01, 2010

    Come and Play on SQL Server 2008 R2


    Register or login to experience the SQL Server 2008 R2 virtual lab.

    The environment also host the SharePoint 2010 and MS Office 2010.


    Wednesday, July 28, 2010

    Me in SQL Data Camp in Chennai


    I attended the SQL Data Camp in Chennai and received a RedGate book as a token of one the most active participant of the event.


    Thursday, July 15, 2010

    Wednesday, July 14, 2010

    How to find who dropped the database? Is that logged on SQL Server?


    Are you one looking to find answer for questions like below.,

    · How to find who dropped a database?

    · How to find who created the database?

    · How to find who altered the database?

    · How to find who altered the database configurations?

    · How to find who dropped the schema?

    · How to find who altered the schema?

    · How to find who altered the server configuration?

    · How to find who modified the login?

    · How to find who modified the table?

    If it is then,  are you wondering is that possible on SQL Server?

    Yes, SQL Server can beginning from version SQL Server 2005 onwards.

    That is where SQL Server Default trace come into picture.

    What is SQL Server Default Trace?

    Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur.

    To get the default trace options

    SELECT * FROM ::fn_trace_getinfo(default)

    To know what is audited on default trace

    SELECT Trc.EventID, Trc.ColumnID, as Event_Description, as Column_Description

      FROM ::fn_trace_geteventinfo(1) Trc

        JOIN sys.trace_events Evt ON Trc.eventID = Evt.trace_event_id

        JOIN sys.trace_columns Col ON Trc.columnid = Col.trace_column_id



    Below is the screenshot  “Schema Changes History” of Standard Reports at Server Level.

    See that “it shows the event of Drop and Alter of the database


    The same can be queried from SQL Server Default Trace like;

    select * from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log\log.trc',DEFAULT)
    where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2

    Default trace can be queried to find answers on

    1. To finmd the Create / Alter / Drop i.e., DDL changes at Database,Schema and Object level
    2. To find the configuration changes at SQL Server Instance, Database, Schema and Object Level
    3. To find Login creation , modifications and failures
    4. To find performance issues like missing of statistics, join predicate, hash or sort warnings
    5. Database file growth or shrinkage
    6. DBCC operations


    Points to ponder

    1. Default trace is a Server Side trace and it is enable by default.
    2. Default trace properties can not be modified, other than enabling and disabling of this trace.
    3. Default trace by default enabled and stored on the “Log” folder

    4. Default trace file will be created newly on start of the SQL Server service.
    5. A single trace file can store  up to 20 MB, when it is full new file will be created.
    6. SQL Server would maintain 5 default trace file, where the file with highest number is the latest.
    7. Default trace is light weight and it is not replacement for DDL Trigger. Default trace log only basic information, when detailed information needed DDL trigger is the option.

    Monday, July 12, 2010

    Using Spatial Data type to draw circle in SQL Server 2008


    --- Draw Circle in SQL Server 2008
    declare @g Geometry
    set @g = 'Point(10 5)'
    select @g.STBuffer(5)


    Saturday, June 19, 2010

    SQL 2000 SP4 Installation Error sp_vupgrade_replication(1)



    • Msg 170, Level 15, State 1, Server MS, Line 1

    Line 1: Incorrect syntax near '-'.

    The sqlsp.log file indicates:

    Process Exit Code: (0)

    Error running script: sp_vupgrade_replication (1)

    Action CleanUpInstall:

    Installation Failed.


    1. Check if distribution database exist though the Replication is dropped
    2. If so drop the distribution database
    3. To drop the distribution database by over - restoring of some user database
    4. Perform the patching / SQL SP again.

    Wednesday, June 16, 2010

    SQL2000 SP4 Installation Error sp_vupgrade_replication(1)


    SQL Server 2005 Patching fails CREATE DATABASE failed.


    Error Number : 29537

    Error Description : MSP Error: 29537 SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]CREATE DATABASE failed.
    Some file names listed could not be created. Check related errors.. To continue, correct the problem, and then run SQL Server Setup again.

    By default the setup creates the Temp_MS_AgentSigningCertificate_database Database on specified default database location,
    If the location doesn't exist, it fails to create the database and the setup fails.

    1. In SSMS, go to Server Properties > Database Settings > Database Default Locations ,check default location for Data and Log .
    2. Try to access locations specified at Data and Log, Make sure files are available there.
    3. If any of the files missing from above location , we hit the above error.
    4. In that case, change the path to point to reall location of mdf/ldf files .
    5. Setup should now succeed.

    1. Reference: a recently applied update, kb955706, failed to install

    Thursday, June 10, 2010

    Delete files using DOS command FORFILES


    To delete te

    --Delete all .bak files in the named directory and its subfolders where the file modified date is more than 2 days old.


    EXEC xp_cmdshell 'FORFILES /p "L:\MSSQL$PRODSQL2KWEB\Backup" /s /m *.bak /d -2 /c "CMD /C echo Deleting the file : @file del /Q /F @FILE && del /Q /F @FILE " '


    FORFILES Parameters

    Parameter Name Description

    /p Path

    /m Search Mask (default is *.*)

    /s Subdirectories will be searched recursively if this parameter is included

    /c <command> Command to be executed against each file in the result set, commands must be enclosed in double-quotes, default is "cmd c/ echo @file"

    /d Date range for file selection, using Last Modified Date as the criterion for the file. When the /d parameter is in the form of MM/DD/YYYY, file meeting the criteria of +/- the specified date are included. When in the format of a smallint (-32,768 - 32,768) the files +/- the files with a modified date +/- that number of days from the current date are included in the file result set.


    Variable Name Description

    @FILE File name

    @FNAME File name without extension

    @EXT File extension

    @PATH Full path of the file

    @RELPATH Relative path of the file

    @ISDIR Evaluates as TRUE if the file type is a directory

    @FSIZE File size in bytes

    @FDATE Last modified date stamp on the file

    @FTIME Last modified timestamp on the file


    Tuesday, June 08, 2010

    Privileges needed to run SQL Server Profiler without sysadmin privileges


    In starting with SQL Server 2005 and above a SQL Server user or login does not need to have SYSADMIN privileges to use the SQL Server Profiler.

    It can be accomplished by,

    GRANT ALTER TRACE to [Login]

    BOL explanation:

    By default, running SQL Server Profiler requires the same user permissions as the Transact-SQL stored procedures that are used to create traces. To run SQL Server Profiler, users must be granted the ALTER TRACE permission

    References: Permissions Required to Run SQL Server Profiler

    Saturday, May 29, 2010

    Query to find when the table last accessed on SQL Server 2005 and above


    ------ Query to find when the User Table Last accessed ---------

    WITH LastActivity (ObjectID, LastAction)
    SELECT object_id AS TableName, Last_User_Seek as LastAction
    FROM sys.dm_db_index_usage_stats u
    WHERE database_id = db_id(db_name())
    SELECT object_id AS TableName,last_user_scan as LastAction
    FROM sys.dm_db_index_usage_stats u
    WHERE database_id = db_id(db_name())
    SELECT object_id AS TableName,last_user_lookup as LastAction
    FROM sys.dm_db_index_usage_stats u
    WHERE database_id = db_id(db_name())
    SELECT OBJECT_NAME(so.object_id)AS TableName,
        MAX(la.LastAction)as LastSelect
    sys.objects so
    LEFT JOIN LastActivity la
    ON so.object_id = la.ObjectID
    WHERE so.type = 'U'
    AND so.object_id > 100
    GROUP BY OBJECT_NAME(so.object_id)
    ORDER BY OBJECT_NAME(so.object_id)

    ------ Query to find when the Database Last accessed ---------

    SELECT name, last_access =(
                    select X1= max(bb.xx)
                    from ( select xx =
                            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

    Deny specific user from Remote Login to SQL Server

    SQL Server Query to deny user on remote login feature, let user connect to database engine only from localhost (or

    This query below applies to SQL Server 2005 with SP2 and above higher version. It uses the SQL Server Logon Trigger


    CREATE TRIGGER Deny_Remote_Login_Login1_trigger






    (SELECT * FROM sys.dm_exec_sessions

    WHERE is_user_process = 1 AND

    original_login_name = 'Login1' --- Checking the given login

    and Host_Name not in ( 'SQLServerHostName') --- Connecting Remotely

    and Session_ID = @@SPID )


    PRINT 'RemoteLogin Attempt attempt of ' + ORIGINAL_LOGIN()+' denied, please login locally'





    Tuesday, May 18, 2010

    SQL Server 2005 Uninstall Error: The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine Error: 2147749896 (0x80041008).


    The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine Error:2147749896 (0×80041008).



    Resolution: Copy and run the DOS batch file below

    @echo on
    cd /d c:\temp
    if not exist %windir%\system32\wbem goto TryInstall
    cd /d %windir%\system32\wbem
    net stop winmgmt
    winmgmt /kill
    if exist Rep_bak rd Rep_bak /s /q
    rename Repository Rep_bak
    for %%i in (*.dll) do RegSvr32 -s %%i
    for %%i in (*.exe) do call :FixSrv %%i
    for %%i in (*.mof,*.mfl) do Mofcomp %%i
    net start winmgmt
    goto End
    if /I (%1) == (wbemcntl.exe) goto SkipSrv
    if /I (%1) == (wbemtest.exe) goto SkipSrv
    if /I (%1) == (mofcomp.exe) goto SkipSrv
    %1 /RegServer
    goto End
    if not exist wmicore.exe goto End
    wmicore /s
    net start winmgmt

    Thanks to:

    Monday, May 17, 2010

    How to remove the subscription from Publication in SQL Server Replication


    1. On SQL Server 2000 connect to Enterprise Manager
    On SQL Server 2005 / 2008 connect to SQL Server Management studio

    2. Expand Replication à Publication, select the Publication from which the subscribers to be removed.
    On SQL Server 2005 / 2008 from the Replication à Local Publication select the Publication

    3. Right click on the Publication

    4. Select the Subscription tab


    5. Select the Subscriber and click Delete button, that’s it

    6. If you hold the “Shift” key you can delete more than one subscriber. However you have to execute the sp_subscription_cleanup each subscribers manually.

    7. Rather if you delete one subscriber at a time, it is simple and no need to execute the stored procedures at subscriber.


    Tuesday, May 04, 2010

    How to apply Service pack on SQL Server 2005 Cluster


    1. Backup of all the System + User Database

    2.Windows Login account with "Local Administrator" privileges on all the nodes involved

    3. Ensure all the recourses part of the SQL Server Cluster resource group are online.
       I recommend to reboot both nodes before installation. (to avoid pending reboot, hanging installers, terminal sessions etc. problems.)

    4. Cross check if the cluster failover on the nodes involved successfully.

    5. shut down all applications that use SQLServer (because you know the instance(s) will get offline at some point(s) during the upgrade)

    6. Disable if any DDL Triggers

    7. Verify if following services online on the nodes involved.

    • Task Scheduler
    • Com+ System Application Service
    • Cryptographic Services
    • Remote Registry Service 

    8. Pause Database Mirroring

    Log on the active node, apply the Service pack. ( It is opposite in case of SQL Server 2008 ! ). You cannot install SP3 from any passive nodes in the failover cluster.

    Please note SP will not update the client tools on the passive node. you need to do that manually; though it is cluster aware.


    How to install SQL Server 2005 SP3

    SQL Server 2005 SP3 Release Notes

    SQL Server 2008 failover cluster rolling patch and service pack process

    Saturday, May 01, 2010

    SQL Server 2005 Service Pack 4

    SQL Server 2005 Service Pack 4 ( SQL Server 2005 SP4 ) will be released by end of the year 2010 ( Quarter 4 of 2010 ). It will be the last Service pack for the SQL Server 2005.

    SQL Server 2008 Service Pack 1 ( SQL Server 2008 SP1 ) will be released by third quarter of the year 2010 ( Quarter 3 of 2010 ). It will be the last Service pack for the SQL Server 2005.

    Source: Shared by the Microsoft SQL Server release services group manager.

    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.