Start SSMS (SQL Server Management Studio) in few minutes

Hi SQL Server Folks,

Here’s something interesting about Management Studio 2005 (SSMS)

  • When you launch SQL Server Management Studio 2005 on the server, it takes a couple of minutes (more than 5 mins in some case) before it  starts up and another few minutes to connect.
  • And, you observe from Task Manager that, there are sufficient (Memory and CPU) resources available.

Here’s few Tips to improve the SSMS ‘Start-up’ time:

Step #1. Un-Check Certificate Revocation

- Go to “Start” -> “Programs” -> “Internet Explorer” –> “Internet Options” –> “Advanced” -> “Security”

Step #2. Disable SQL Server Error and Usage Reporting

- Go to “Start” -> “Programs” -> “Microsoft SQL Server 2005” –> “Configuration Tools” –> “SQL Server Error and Usage Reporting” –> Uncheck following:

Step # 3. Disable Online Help Content for SQL Management Studio

- Go to “SQL Management Studio” –> “Tools” –> “Options” –> “Environment” –> “Help” –> “Online” –> “When loading help content” –> Select “Try local first, then online” or “Try local only”

#4. Enable Shared Memory and TCP/IP in SQL Server Configuration Manager

- Go to “Start” -> “Programs” -> “Microsoft SQL Server 2005” -> “SQL Server Configuration Manager” –> “SQL Native Client Configuration” -> “Client Protocols

 

I Hope it will help someone.

Thanks.

Windows Instant File Initialization and SQL Server

Hi SQL Server Folks,

Recently I was working on very huge database, approximately size was ~2 T B for that db. I faced a problem that when the data file was growing for a db then it was taking more time. The reason is when we we create any data file OR when data files grows then operating system do not assign the space directly to that data file. First it will allocate desired space then that space is filled by Zero’s and then this data file can be used by any operation that user is performing.

But we can reduce that time through a feature called Windows Instant File Initialization. On a Windows Server 2003 or newer operating system, using SQL Server 2005 or newer, you can take advantage of this feature to dramaticallyreduce the amount of time required to create or grow a SQL Server data file. This only works on SQL Server data files, not on SQL Server log files.

Normally when you create a SQL Server data file, the operating system goes through and “zeros out” the entire file after the file is allocated. This can take quite a bit of time for a large data file, and this comes into play in several situations, including:

  1. Creating a new database.
  2. Adding a new file to an existing database.
  3. Growing an existing file to a larger database.
  4. Restoring a database from full database backup.
  5. Restoring a database from full database backup to intilize a database mirror.

Windows Instant File Initialization allows the operating system to skip the zeroing out process (for authorized Windows users), which makes the file allocation process nearly instantaneous even for very large files. This can have a huge effect on how long it takes to restore a database, which could be very important in a disaster recovery situation.

You have to grant this right to the Windows account that the SQL Server Service is using. This would normally be a Windows domain account. You can do this by using the Local Group Policy Editor on the machine where SQL Server will be running. You can just type GPEDIT.MSC in a Run window, which will bring up the Local Group Policy Editor as shown in below Figure.

Now go on to the left hand side of this screen and go to Computer Configuration –> Windows Settings –>  Security Settings –>  Local Policies –>  User Rights Assignment. Next, in the right hand portion of the dialog window, you simply right-click on “Perform volume maintenance tasks” and select Properties, and click on the Add User or Group button. Then you need to add the name of the SQL Server Service account, and click Ok. After you make this change, you need to restart the SQL Server service for the change to go into effect.

After done this definitely your SQL Server Performance will be better for larger db.

Hope it will help some one. Thanks all to stay tune here.

Useful Short Cuts in SQL Server

Hi Guys, 

Please find the useful short cuts in SSMS.

Query Analyzer is a great tool to have and here are a list of all shortcut keys you can use to speed up your keyboard capabilities to use Query Analyzer.

Bookmarks: Clear all bookmarks. CTRL-SHIFT-F2
Bookmarks: Insert or remove a bookmark (toggle). CTRL+F2
Bookmarks: Move to next bookmark. F2
Bookmarks: Move to previous bookmark. SHIFT+F2
Cancel a query. ALT+BREAK
Connections: Connect. CTRL+O
Connections: Disconnect. CTRL+F4
Connections: Disconnect and close child window. CTRL+F4
Database object information. ALT+F1
Editing: Clear the active Editor pane. CTRL+SHIFT+ DEL
Editing: Comment out code. CTRL+SHIFT+C
Editing: Copy. You can also use CTRL+INSERT. CTRL+C
Editing: Cut. You can also use SHIFT+DEL. CTRL+X
Editing: Decrease indent. SHIFT+TAB
Editing: Delete through the end of a line in the Editor pane. CTRL+DEL
Editing: Find. CTRL+F
Editing: Go to a line number. CTRL+G
Editing: Increase indent. TAB
Editing: Make selection lowercase. CTRL+SHIFT+L
Editing: Make selection uppercase. CTRL+SHIFT+U
Editing: Paste. You can also use SHIFT+INSERT. CTRL+V
Editing: Remove comments. CTRL+SHIFT+R
Editing: Repeat last search or find next. F3
Editing: Replace. CTRL+H
Editing: Select all. CTRL+A
Editing: Undo. CTRL+Z
Execute a query. You can also use CTRL+E (for backward compatibility). F5
Help for SQL Query Analyzer. F1
Help for the selected Transact-SQL statement. SHIFT+F1
Navigation: Switch between query and result panes. F6
Navigation: Switch panes. Shift+F6
Navigation: Window Selector. CTRL+W
New Query window. CTRL+N
Object Browser (show/hide). F8
Object Search. F4
Parse the query and check syntax. CTRL+F5
Print. CTRL+P
Results: Display results in grid format. CTRL+D
Results: Display results in text format. CTRL+T
Results: Move the splitter. CTRL+B
Results: Save results to file. CTRL+SHIFT+F
Results: Show Results pane (toggle). CTRL+R
Save. CTRL+S
Templates: Insert a template. CTRL+SHIFT+INSERT
Templates: Replace template parameters. CTRL+SHIFT+M
Tuning: Display estimated execution plan. CTRL+L
Tuning: Display execution plan (toggle ON/OFF). CTRL+K
Tuning: Index Tuning Wizard. CTRL+I
Tuning: Show client statistics CTRL+SHIFT+S
Tuning: Show server trace. CTRL+SHIFT+T
Use database. CTRL+U

So let’s get started ….

List of Undocumented DBCC commands

 

DBCC activecursors [(spid)]

DBCC addextendedproc (function_name, dll_name)

DBCC addinstance (objectname, instancename)

DBCC adduserobject (name)

DBCC auditevent (eventclass, eventsubclass, success, loginname, rolename, dbusername, loginid)

DBCC autopilot (typeid, dbid, tabid, indid, pages [,flag])

DBCC balancefactor (variance_percent)

DBCC bufcount [(number_of_buffers)]

DBCC buffer ( {‘dbname’ | dbid} [, objid [, number [, printopt={0|1|2} ][, dirty | io | kept | rlock | ioerr | hashed ]]])

DBCC bytes ( startaddress, length )

DBCC cachestats

DBCC callfulltext

DBCC checkalloc [(‘database_name’[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, ESTIMATEONLY]]

DBCC checkcatalog [('database_name')] [WITH NO_INFOMSGS]

DBCC checkconstraints [( 'tab_name' | tab_id | 'constraint_name' | constraint_id )] [WITH ALL_CONSTRAINTS | ALL_ERRORMSGS]

DBCC checkdb [(‘database_name’[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, DBCC TABLOCK]

DBCC checkdbts (dbid, newTimestamp)]

DBCC checkfilegroup [( [ {'filegroup_name' | filegroup_id} ] [, NOINDEX] )] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]

DBCC checkident (‘table_name’[, { NORESEED | {RESEED [, new_reseed_value] } } ] )

DBCC checkprimaryfile ( {‘FileName’} [, opt={0|1|2|3} ])

DBCC checktable (‘table_name’[, {NOINDEX | index_id | REPAIR}]) [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]

DBCC cleantable (‘database_name’|database_id, ‘table_name’|table_id, [batch_size])

DBCC cacheprofile [( {actionid} [, bucketid])

DBCC clearspacecaches (‘database_name’|database_id, ‘table_name’|table_id, ‘index_name’|index_id)

DBCC collectstats (on | off)

DBCC concurrencyviolation (reset | display | startlog | stoplog)

DBCC config

DBCC cursorstats ([spid [,'clear']])

DBCC dbinfo [('dbname')]

DBCC dbrecover (dbname [, IgnoreErrors])

DBCC dbreindex (‘table_name’ [, index_name [, fillfactor ]]) [WITH NO_INFOMSGS]

DBCC dbreindexall (db_name/db_id, type_bitmap)

DBCC dbrepair (‘dbname’, DROPDB [, NOINIT])

DBCC dbtable [({'dbname' | dbid})]

DBCC debugbreak

DBCC deleteinstance (objectname, instancename)

DBCC des [( {‘dbname’ | dbid} [, {'objname' | objid} ])]

DBCC detachdb [( 'dbname' )]

DBCC dropcleanbuffers

DBCC dropextendedproc (function_name)

DBCC dropuserobject (‘object_name’)

DBCC dumptrigger ({‘BREAK’, {0 | 1}} | ‘DISPLAY’ | {‘SET’, exception_number} | {‘CLEAR’, exception_number})

DBCC errorlog

DBCC extentinfo [({‘database_name’| dbid | 0} [, {‘table_name’ | table_id} [, {'index_name' | index_id | -1}]])]

DBCC fileheader [( {‘dbname’ | dbid} [, fileid])

DBCC fixallocation [({‘ADD’ | ‘REMOVE’}, {‘PAGE’ | ‘SINGLEPAGE’ | ‘EXTENT’ | ‘MIXEDEXTENT’}, filenum, pagenum [, objectid, indid])

DBCC flush (‘data’ | ‘log’, dbid)

DBCC flushprocindb (database)

DBCC free dll_name (FREE)

DBCC freeproccache

DBCC freeze_io (db)

DBCC getvalue (name)

DBCC help (‘dbcc_command’ | ‘?’)

DBCC icecapquery (‘dbname’, stored_proc_name [, #_times_to_icecap  (-1 infinite, 0 turns off)]) Use ‘dbcc icecapquery (printlist)’ to see list of SP’s to profile. Use ‘dbcc icecapquery (icecapall)’ to profile all SP’s.

DBCC incrementinstance (objectname, countername, instancename, value)

DBCC ind ( { ‘dbname’ | dbid }, { ‘objname’ | objid }, { indid | 0 | -1 | -2 } )

DBCC indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid | indname})

DBCC inputbuffer (spid)

DBCC invalidate_textptr (textptr)

DBCC invalidate_textptr_objid (objid)

DBCC iotrace ( { ‘dbname’ | dbid | 0 | -1 }, { fileid | 0 }, bufsize, [ { numIOs | -1 } [, { timeout (sec) | -1 } [, printopt={ 0 | 1 }]]] )

DBCC latch ( address [, 'owners'] [, 'stackdumps'])

DBCC lock ([{'DUMPTABLE' | 'DUMPSTATS' | 'RESETSTATS' | 'HASH'}]|[{'STALLREPORTTHESHOLD', stallthreshold}])

DBCC lockobjectschema (‘object_name’)

DBCC log ([dbid[,{0|1|2|3|4}[,[‘lsn’,’[0x]x:y:z’]|['numrecs',num]|['xdesid','x:y']|['extent','x:y']|['pageid','x:y']|['objid',{x,'y'}]|['logrecs',{'lop'|op}...]|[‘output’,x,['filename','x']]…]]])

DBCC loginfo [({'database_name' | dbid})]

DBCC matview ({‘PERSIST’ | ‘ENDPERSIST’ | ‘FREE’ | ‘USE’ | ‘ENDUSE’})

DBCC memobjlist [(memory object)]

DBCC memorymap

DBCC memorystatus

DBCC memospy

DBCC memusage ([IDS | NAMES], [Number of rows to output])

DBCC monitorevents (‘sink’ [, 'filter-expression'])

DBCC newalloc – please use checkalloc instead

DBCC no_textptr (table_id , max_inline)

DBCC opentran [({'dbname'| dbid})] [WITH TABLERESULTS[,NO_INFOMSGS]]

DBCC outputbuffer (spid)

DBCC page ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ][, cache={0|1} ])

DBCC perflog

DBCC perfmon

DBCC pglinkage (dbid, startfile, startpg, number, printopt={0|1|2}, targetfile, targetpg, order={1|0})

DBCC pintable (database_id, table_id)

DBCC procbuf [({‘dbname’ | dbid}[, {‘objname’ | objid}[, nbufs[, printopt = { 0 | 1 } ]]] )]

DBCC proccache

DBCC prtipage (dbid, objid, indexid [, [{{level, 0} | {filenum, pagenum}}] [,printopt]])

DBCC pss [(uid[, spid[, printopt = { 1 | 0 }]] )]

DBCC readpage ({ dbid, ‘dbname’ }, fileid, pageid, formatstr [, printopt = { 0 | 1} ])

DBCC rebuild_log (dbname [, filename])

DBCC renamecolumn (object_name, old_name, new_name)

DBCC resource

DBCC row_lock (dbid, tableid, set) – Not Needed

DBCC ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+)

DBCC ruleon (  rulenum | rulestring } [, { rulenum | rulestring } ]+)

DBCC setcpuweight (weight)

DBCC setinstance (objectname, countername, instancename, value)

DBCC setioweight (weight)

DBCC show_statistics (‘table_name’, ‘target_name’)

DBCC showcontig (table_id | table_name [, index_id | index_name] [WITH FAST, ALL_INDEXES, TABLERESULTS [,ALL_LEVELS]])

DBCC showdbaffinity

DBCC showfilestats [(file_num)]

DBCC showoffrules

DBCC showonrules

DBCC showtableaffinity (table)

DBCC showtext (‘dbname’, {textpointer | {fileid, pageid, slotid [,option]}})

DBCC showweights

DBCC shrinkdatabase ({dbid | ‘dbname’}, [freespace_percentage [, {NOTRUNCATE | TRUNCATEONLY}]])

DBCC shrinkdb  is no longer supported. Please use shrinkdatabase instead

DBCC shrinkfile ({fileid | ‘filename’}, [compress_size [, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]])

DBCC sqlmgrstats

DBCC sqlperf (LOGSPACE)({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]} | {THREADS} | {LOGSPACE})

DBCC stackdump [( {uid[, spid[, ecid]} | {threadId, ‘THREADID’}] )]

DBCC tab ( dbid, objid )

DBCC tape_control {‘query’ | ‘release’}[,('\\.\tape<n>')]

DBCC tec [( uid[, spid[, ecid]] )]

DBCC textall [({‘database_name’|database_id}[, 'FULL' | FAST] )]

DBCC textalloc ({‘table_name’|table_id}[, 'FULL' | FAST])

DBCC thaw_io (db)

DBCC traceoff [( tracenum [, tracenum ... ] )]

DBCC traceon [( tracenum [, tracenum ... ] )]

DBCC tracestatus (trace# [, ...trace#])

DBCC unpintable (dbid, table_id)

DBCC updateusage ({‘database_name’| 0} [, ‘table_name’ [, index_id]]) [WITH [NO_INFOMSGS] [,] COUNT_ROWS]

DBCC upgradedb (db)

DBCC usagegovernor (command, value)

DBCC useplan [(number_of_plan)]

DBCC useroptions

DBCC wakeup (spid)

DBCC writepage ({ dbid, ‘dbname’ }, fileid, pageid, offset, length, data)

[Part 2] Analysis Service is not accessible Error : A connection could not be made to redirector. Ensure that ‘SQL Browser’ is running

Hi SQL Server Folks,

In the Part 1 of the same issue I discussed about the permission of NETWORK SERVICE account on path “C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin” . I gave full permission to NETWORK SERVICE account on mentioned path and problem got resolved. It’s the path where AS is installed, In your case this path might be different.

In this Part 2 even I tried all the options of Part 1 but none of these options were working. I will summarize the issue again here:-

I installed a SQL Server 2008 R2 EE Cluster named instance on machine Windows 2003 Server R2 SP2 (64-bit) and install was successfull i.e. all SQL Server components like Database engine, AS, IS , RS & Shared Components was successfully installed. All components are able to connect through SSMS except of AS. While I was gaving a try to connect AS with instance name it was throughing the below error: 

 

But when I didn’t specified the instance name i.e. only specified SQL Server virtual name then AS Was able to connect. This was a starnge behaviour but there is a reason for that. when we are specifying only SQL Server virtual name the request will be listen on default port 2383 for AS. In any case of Clustered SQL Server 2008 the port will be use by AS is 2383. But when we are specifying SQL Server virtual name then it will rely on Browser Service.

I checked all the things as described in Part1 and found all were set. I additionally opened the ports for SQL Server Analysis service on both the node and then again gave a try to connect AS but no luck.  Then I Reviewed, msmdsrv.ini file, looks like the port information was set to default:

<Port>0</Port>

Then I reviewed msmdsrv.log file, one error message is there but not much helpful:

Message: Failed to initialize SQLSQM timer. One of the following can be the source of the problem: registry doesn’t contain all necessary information, server instance name can not be retrieved correctly, failure during timer creation, etc. (Source: \\?\F:\OLAP\Log\msmdsrv.log, Type: 3, Category: 289, Event ID: 0xC121001B)

Research on this issue, and it seems like we are having some issues with browser service. Tested with UDL and BIDS (Visual Studio), the error message is consistent, this is related to Browser Service. Looked for the browser service file: msmdredir.ini and it was available in the following location: “C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig”. At first this confused us as it seemed like browser service is 2005 service because of the path but we confirmed that on a machine, where we have only 2008 SQL Server Service. But I found that for SQL 2008 OR SQL 2008 R2 browser service will also be located at: C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ folder structure, this is consistent, i.e. same browser service will be use for all SQL Server versions (SQL 2005, SQL 2008 & SQL 2008 R2). Then I went in to services.msc and stopped the SQL Server Browser Service and started the Processs Monitor tool to capture activity related to Browser service. I started Browser Service, it came online. But I looked in to the process monitor and I found below error:- 

 It gave me hint to check the permission of AS service account on path and as expected it was missing then I gave full permission to Analysis Service account on that pecuilar path and changed the account for SQL Server Browser Service from Local Service to Local System and re-started the browser service. Now I gave a try to connect SSAS with instance name and I was able to connect.

Hope it will Help Someone.

SQL Server 2008 R1 Client Tools

Hi SQL Server Folks,

I got a requiremet from one of my customer to install client tools but for SQL Server 2008 R1. It was very simple in SQL Server 2005 i.e. in the binaries for SQL Server 2005 you have two folders name as “Server” & “Tool” so if you want to install only client tools then simply open the folder name as “Tool” and run the setup.exe and your task is done. But in the binaries of SQL Server 2008 there is nothing like that i.e. all servers and client related components are mixed. Below is the script that I wrote to make SQL Server 2008 R1 client tool.

Open the command prompt and go in to the path where your SQL Server binaries are there for ex. C:\Gaurav\SQL Server 2008 R2 

 set source=C:\Gaurav\SQL Server 2008 R2

set dest=C:\SQLFULL_ENU_ToolsOnly

 REM Copy Root ONLY

robocopy %source% %dest%

REM Copy Root ONLY

robocopy %source%\x86 %dest%\x86 /XF fixsqlregistrykey_ia64.exe

robocopy %source%\x64 %dest%\x64 /XF fixsqlregistrykey_ia64.exe

 robocopy %source%\x86\1033 %dest%\x86\1033

robocopy %source%\x64\1033 %dest%\x64\1033

 robocopy %source%\x86\help %dest%\x86\help /S

robocopy %source%\x64\help %dest%\x64\help /S

 REM Copy redists

 robocopy %source%\x86\redist\DotNetFrameworks %dest%\x86\redist\DotNetFrameworks /S /XF *ia64*

robocopy %source%\x64\redist\DotNetFrameworks %dest%\x64\redist\DotNetFrameworks /S /XF *ia64*

 robocopy %source%\x86\redist\Powershell %dest%\x86\redist\Powershell /S /XD ia64

robocopy %source%\x64\redist\Powershell %dest%\x64\redist\Powershell /S /XD ia64

 robocopy %source%\x86\redist\Watson %dest%\x86\redist\Watson /S

robocopy %source%\x64\redist\Watson %dest%\x64\redist\Watson /S

 robocopy “%source%\x86\redist\Windows Installer” “%dest%\x86\redist\Windows Installer” /S /XD ia64

robocopy “%source%\x64\redist\Windows Installer” “%dest%\x64\redist\Windows Installer” /S /XD ia64

 REM Setup Folder and exclude folders and files that aren’t needed

robocopy %source%\x86\Setup %dest%\x86\Setup /S /XD sql_engine_core_inst_loc_msi sql_engine_core_inst_msi sql_engine_core_shared_loc_msi sql_engine_core_shared_msi /XF rsfx.msi rsSharePoint.msi sql_as.msi sql_fulltext.msi sql_is.msi sql_rs.msi sqlbrowser.msi Synchronization.msi SyncServicesADO.msi sql_as_loc.msi sql_is_loc.msi sql_rs_loc.msi sqlserver2005_bc.msi sqlwriter.msi

 robocopy %source%\x64\Setup %dest%\x64\Setup /S /XD sql_engine_core_inst_loc_msi sql_engine_core_inst_msi sql_engine_core_shared_loc_msi sql_engine_core_shared_msi /XF rsfx.msi rsSharePoint.msi sql_as.msi sql_fulltext.msi sql_is.msi sql_rs.msi sqlbrowser.msi Synchronization.msi SyncServicesADO.msi sql_as_loc.msi sql_is_loc.msi sql_rs_loc.msi sqlserver2005_bc.msi sqlwriter.msi

After running this above script your SQL Server 2008 R1 client tools are reday and it will be save in your destination location that you defined above in script, in our case this location is C:\SQLFULL_ENU_ToolsOnly .

Instructions to use SQL 2008 R1 Client Tools

  1. Copy the whole folder “SQL 2008 Client Tools”  on machine where you want to install it.
  2. For 32-bit machine open “SQL 2008 Client Tools”  folder  and open the file name as “MyConfigurationFile.ini” in Notepad. After opening it search the text as “MEDIASOURCE” and in that field specify the location where you copied “SQL 2008 Client Tools” folder and save the changes in this “MyConfigurationFile.ini” . Now search the text name as “INSTALLSHAREDWOWDIR=”C:\Program Files (x86)\Microsoft SQL Server” and delete this text from this file and save the changes in this “MyConfigurationFile.ini” .
  3.  For 64-bit machine open “SQL 2008 Client Tools”  folder  and open the file name as “MyConfigurationFile.ini” in Notepad. After opening it search the text as “MEDIASOURCE” and in that field specify the location where you copied “SQL 2008 Client Tools” folder.
  4.  Now Open the command prompt and go in to “SQL 2008 Client Tools”  folder  location and type the parameter as “Setup.exe /q /ACTION=Install /FEATURES=Tools /ConfigurationFile=MyConfigurationFile.INI” and wait to install SQL 2008 client tools.
  5.  After finishing this task go in to Start à Programs and check for “Microsoft SQL Server 2008”.

Hope it will help someone. Stay tune for more interesting SQL Server stuff’s.

Thanks.

SQL Server 2008 R2 Installation Issue : ” The configuration registry key is invalid”

Hi Folks,

Before some time I was installing SQL Server 2008 R2 EE 32-bit on Windows Server 2003 SP2 SE (32-bit) but it failed on final installlation wizard with error ” The configuration registry key is invalid”.  I had all the pre-requisite i.e. s/w and h/w level. I looked in to the “detail.txt” file of the installation to look  deep in this issue. I found below info in that file:-

2011-12-21 10:42:45 Slp: PerfCounter calling lodctr: ‘C:\Program Files\Microsoft SQL Server\100\DTS\Binn\perf-MsDtsServer100DTSSVCPERF.INI’
2011-12-21 10:42:45 Slp: Prompting user if they want to retry this action due to the following failure:
2011-12-21 10:42:45 Slp: —————————————-
2011-12-21 10:42:45 Slp: The following is an exception stack listing the exceptions in outermost to innermost order
2011-12-21 10:42:45 Slp: Inner exceptions are being indented
2011-12-21 10:42:45 Slp:
2011-12-21 10:42:45 Slp: Exception type: Microsoft.SqlServer.Configuration.Sco.ScoException
2011-12-21 10:42:45 Slp:     Message:
2011-12-21 10:42:45 Slp:         The configuration registry key is invalid.
2011-12-21 10:42:45 Slp:        
2011-12-21 10:42:45 Slp:     Data:
2011-12-21 10:42:45 Slp:       WatsonData = netfxperf.dll@OpenPerformanceData@CollectPerformanceData@ClosePerformanceData
2011-12-21 10:42:45 Slp:       DisableRetry = true
2011-12-21 10:42:45 Slp:     Inner exception type: System.ComponentModel.Win32Exception
2011-12-21 10:42:45 Slp:         Message:
2011-12-21 10:42:45 Slp:                 The configuration registry key is invalid.
2011-12-21 10:42:45 Slp:                
2011-12-21 10:42:45 Slp:         Stack:
2011-12-21 10:42:45 Slp:                 at Microsoft.SqlServer.Configuration.Sco.PerformanceCounter.LoadPerformanceCounter(String symbolInstallPath, String counterPrefixServiceName)
2011-12-21 10:42:45 Slp:                 at Microsoft.SqlServer.Configuration.Sco.PerformanceCounter.RegisterPerformanceCounterCore(String libraryFileName, String openFunction, String collectionFunction, String closeFunction, String symbolInstallPath, String counterPrefixServiceName)
2011-12-21 10:42:45 Slp:                 at Microsoft.SqlServer.Configuration.Sco.PerformanceCounter.RegisterPerformanceCounter(String libraryFileName, String openFunction, String collectionFunction, String closeFunction, String symbolInstallPath)
2011-12-21 10:42:45 Slp: —————————————-
2011-12-21 10:57:33 Slp: User has chosen to retry this action

 To resolve this issue there was a need to rebuild the performance counters in windows server. To rebuild all Performance counters including extensible and third-party counters in Windows Server 2003, type the following commands at a command prompt. Press ENTER after each command.
cd\windows\system32
lodctr /R

 Notes: 
/R is uppercase. You must have administrative rights on the computer to successfully perform this command.
On a computer that is running a 32-bit edition of Windows XP, the Lodctr /R:<filename> command is the standard method to restore performance counter registry strings and information by using a file name. 

After Rebuild of Performance Counters, uninstalled the failed Default instance and Installed SQL server 2008 R2 and installation completed successfully.

Please refer below link for more inofrmation to rebuild Performance Counter Library values:-

http://support.microsoft.com/kb/300956 

Hope it will help someone.

SQL Server 2008 R2 Installation issue : Condition is false because the required feature MSXMLSYS_Msxml6_Cpu32 failed in result Result

Hi Guys,

Before sometime I was trying to install SQL Server 2008 R2 EE 32-bit on machine Windows Server 2003 SP2 (32-bit) and i was not able to install it. I had all the pre-requisite to install SQL Server 2008 R2 i.e. at H\W & S\W level requirements; even though setup was failing. My setup started fine and didn’t gave me any error during installation but on the final window of installation “database engine” and other components too was failing to install.

To look in to that issue I openend “Detail.txt” file that was in path “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120106_065559″ in my case. In your case it will be the location where you are installing your SQL Server. I read that file and found too many failed components and the error was below:

1. 2012-01-06 07:15:40 Slp: Condition “Feature dependency condition for action:  Install_sql_engine_core_shared_Cpu32_Action The condition tests feature: SQL_Engine_Core_Shared_sql_engine_core_shared_Cpu32. There are 7 dependant features. The feature is tested for results: ValidateResult, Result.” did not pass as it returned false and true was expected.

 2. 2012-01-06 07:15:40 Slp: Condition is false because the required feature MSXMLSYS_Msxml6_Cpu32 failed in result Result

But everywere in the “Detail.txt” file 2nd eror was repeating. Then I started to found the error related to MSXML. 

I got an idea to check if SQL Server 2008 R2 32-bit EE is supported on Windows Server 2003 EE SP2 32-bit and I found it was supported but only on Pentium3  and onwards procesors. In My case I checked the processor type and it was AMD.

The AMD Opteron(tm) Processor 285 doesn’t support MSXML6 component , whereas in other processors the issue is handled by the sql server 2008 R2 install seamlessly , here we need to apply certain patch manually. That patch I downloaded from below link:-

 

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=24032

After downloading and applied the patch I was able to install SQL Server 2008 R2 w/o any problem.

I Hope it will be helpfull for some one. Stay tune for more stuff”s. :)

[Part1] Analysis Service is not accessible : A Connection can not be made to redirector. Ensure that “SQL Browser” Service is running

Hi Guys,

Before some time I installed SQL Server 2005 32-bit cluster on Windows server 2003 SP2 (32-bit) machine. All SQL Server related components were installed successfully. I patched both the nodes and it was successfull too. So i thought to give a try to connect all the components (i.e. Database Engine, Analysis Service, Integartion Service & Reporting Service) of SQL Server via SSMS.  I was able to connect all the components except Analysis Service. When I gave a try to connect it then it throwed me below error:-

Then I saw in services.msc to search for “SQL Browser” Service , it was running fine. Then I saw in cluster manager for “Analysis Service” , it was online too.

 

Then I saw in event viewer and found in Application below error:-

 

This gave me a clue so I checked the permission on F: drive for Analysis service account. Because whenever SQL Server tries to start AS it creates some files in path “F:\Microsoft SQL Server\MSSQL.2\OLAP\Data” .

So  I follow the below mention steps to overcome this situation:- 

1.       Stop the service (if the service is starting)

2.       Rename the current “Data” folder to something else such as “Data_old” at the shared drive

3.       Create a new “Data”. Make sure the SSAS service account ” has “Full control” of the new folder. (compare the new data folder and the old data folder permission setting)

4.       Restart the service with the new empty data folder. 

After following all this I gave a try to re-connect AS but no luck again. 

Then I downloaded the Process Monitor tool to look deep in this issue. You can download this tool from below link:-

 http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx

I stopped the AS and then started the Process Monitor, then I gave a try to re-start of AS and it came online. I went in to ProcMon and there searched for text “Denied” then I got below error:-

 

It gave me enough hint to troubleshoot this issue. I saw the permission for AS group account, it was already there but missing was “Network service” account. I gave full control to “Network Service” on path “C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin” and then reboot the node. Now I gave a try to connect AS via SSMS and it was successful.

 Hope it will help someone.

 Thanks.

In Depth Of SQL Server

Follow

Get every new post delivered to your Inbox.