How to resolve setup errors when registry keys are missing (Failed to install and configure assemblies C:\Program Files (x86)\Microsoft SQL Server\90\NotificationServices\9.0.242\Bin\microsoft.sqlserver.notificationservices.dll in the COM+ catalog)

Hi Guys ,
A couple of weeks back we had a setup issue where the SP3 setup was failing on Notification services and Client components .I am sharing my experience with you because it took us a lot of time to figure out that a few registry keys were missing .This explanation has 3 parts viz.

First Part : Solution of the issue .
Second Part : Reproducing this issue and finding a solution in a better way so that the same type of process can be followed for other similar setup issues as well .
Third Part : 2 bugs (Unfortunately , I could not reproduce it on my machine, but repeatedly reproduced it on clients machine .Will still try on my machine and file them later.

First Part : Issue and its solution .
Issue
As per the Security Bulitin MS09-004 we rasied a change to patch the DEV (and later would be Prod Servers)server to SQL Sevrer 2005 SP3 .Since we were failing on the DEV Server , we were not able to initiate the setup on Prod until the setup on Dev is resolved . Till then the Prod was under potential threat of SQL injection. The issue was that we were not able to upgrade SQL Server 2005 RTM (32 bit) to SQL Server 2005 SP3 (32 bit) on Windows Server 2008 (64 bit) .

Error Messages
Setup Errors :
Failed to install and configure assemblies C:\Program Files (x86)\Microsoft SQL Server\90\NotificationServices\9.0.242\Bin\microsoft.sqlserver.notificationservices.dll in the COM+ catalog.
Error: 2148734209
Error message: Unknown error 0x80131501
Error descrition: MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)

Errors in Application log :
Unable to get the file name for the OLE Transactions Proxy DLL. Error Specifics: hr = 0x8004d027,
d:\rtm\com\complus\dtc\dtc\xolehlp\xolehlp.cpp:176, CmdLine: C:\Windows\syswow64\MsiExec.exe -Embedding 0E2EA3ADA5DC17201B521333814654B2 C, Pid: 3484

Failed to read the name of the default transaction manager from the registry. Error Specifics: hr = 0x00000002,
d:\rtm\com\complus\dtc\dtc\xolehlp\xolehlp.cpp:382, CmdLine: C:\Windows\syswow64\MsiExec.exe -Embedding 0E2EA3ADA5DC17201B521333814654B2 C, Pid: 3484

Errors in SQL Server :
QueryInterface failed for "ITransactionDispenser": 0x8004d027(XACT_E_UNABLE_TO_READ_DTC_CONFIG).

Client Environment Details
SQL Server 2005 RTM 32 bit Standard Edition (WOW mode)
Windows 2008 64 bit Standard

Troubleshooting done
-> The setup was failing only for Notification services and Client tools .Rest all components were successfully upgraded to SP3 .
-> Found the Both Notification services and Client tools were failing because of same error :

---------------------------------------------------------------------------Product : Client Components
Product Version (Previous): 1399
Product Version (Final) :
Status : Failure
Log File : C:\Program Files (x86)\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQLTools9_Hotfix_KB955706_sqlrun_tools.msp.log
Error Number : 29549
Error Description : MSP Error: 29549 Failed to install and configure assemblies c:\Program Files (x86)\Microsoft SQL Server\90\NotificationServices\9.0.242\Bin\microsoft.sqlserver.notificationservices.dll in the COM+ catalog. Error: -2146233087
Error message: Unknown error 0x80131501
Error description: MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)
---------------------------------------------------------------------------

---------------------------------------------------------------------------Product : Notification Services
Product Version (Previous): 1399
Product Version (Final) :
Status : Failure
Log File : C:\Program Files (x86)\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQLTools9_Hotfix_KB955706_sqlrun_tools.msp.log
Error Number : 29549
Error Description : MSP Error: 29549 Failed to install and configure assemblies c:\Program Files (x86)\Microsoft SQL Server\90\NotificationServices\9.0.242\Bin\microsoft.sqlserver.notificationservices.dll in the COM+ catalog. Error: -2146233087
Error message: Unknown error 0x80131501
Error description: MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)
---------------------------------------------------------------------------
-> Detailed MSP logs showed that the error comes when MSDTC is trying to register the notification services using regsvcs

Error: 2148734209
Error message: Unknown error 0x80131501
Error descrition: MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)
Error Code: -2146233087
Windows Error Text: Source File Name: sqlca\sqlassembly.cpp
Compiler Timestamp: Sat Oct 25 08:47:00 2008
Function Name: Do_sqlAssemblyRegSvcs
Source Line Number: 155


-> So the issue was with registering Notification Services DLL file which was common to both NS and Client components .As you can see RegSvcs was being called from inside the function Do_sqlAssemblyRegSvcs.

-> We then tried to manually register the microsoft.sqlserver.notificationservices.dll through command prompt :
%windir%\Microsoft.NET\Framework64\v2.0.50727\RegSvcs.exe /fc "C:\Program Files (x86)\Microsoft SQL Server\90\NotificationServices\9.0.242\Bin\microsoft.sqlserver.notificationservices.dll"

-> This did not work and we were getting some other error .

-> Since COM+ Catalog was also showing up in the error ,I ran the SQL Server RTM setup and found the the SCC ( System configuration check ) is failing on COM + Catalog requirements .
->So,I checked the Component Services (DCOMCNFG) and found that there are no issues there .Everything was working fine .We also noticed that there were no errors related to COM components in the Application logs as well except the error related to MSDTC .The 32 bit COM components were also running fine .This was strange but made me believe that this error might be misleading .

-> We checked the SQL Sevrer errorlogs and found the same entry related to MSDTC , but in a slightly different manner :
QueryInterface failed for "ITransactionDispenser": 0x8004d027XACT_E_UNABLE_TO_READ_DTC_CONFIG)

-> We also saw some errors related to MSDTC :
Unable to get the file name for the OLE Transactions Proxy DLL.
Error Specifics: hr =
0x8004d027,d:\rtm\com\complus\dtc\dtc\xolehlp\xolehlp.cpp:176, CmdLine: C:\Windows\syswow64\MsiExec.exe
-Embedding -E2EA3ADA5DC17201B521333814654B2 C, Pid: 3484

Failed to read the name of the default transaction manager from the registry. Error Specifics: hr = 0x00000002,
d:\rtm\com\complus\dtc\dtc\xolehlp\xolehlp.cpp:382,
CmdLine: C:\Windows\syswow64\MsiExec.exe
-Embedding 0E2EA3ADA5DC17201B521333814654B2 C, Pid: 3484

-> Now the picture was clear that MSDTC had issues for sure. Also the COM+ Catalog warning is related to MSDTC issue (I had resolved the same issue a few years back :http://ms-abhay.blogspot.com/2009/10/msdtc-was-unable-to-read-its.html. But it was on Win server 2003)

-> The first error code is same 0x8004d027 but the second error code , which is coming before 0x8004d027 is 0x00000002 is nothing but telling us that there is some registry key missing ( there might be more ) . error 2 means "System cannot find the file specified".

-> Since we were not sure of which key was missing we decided to uninstall and reinstall MSDTC . This will automatically recreate all the missing registry keys.

-> But there was some more twist left. On windows Server 2008 we cannot simply uninstall MSDTC by using command "msdtc -uninstall" .We have to remove the MSDTC server role >> reboot the server >> re-add MSDTC in the Server roles.

-> We tried that ,but even after removing MSDTC from the server role , it was still showing as running in the services console .We tried a few times without success.

-> In between we also tried to repair .net framework 3.5 SP1 which did not help .

-> I also went through this article which talks about making sure that all the MSDTC related keys are present in the registry hive:
http://msdn.microsoft.com/en-us/library/dd300421(v=WS.10).aspx(This was the 1st step towards solution , although it did not work.)

-> However , the keys mentioned in this article were present in both WOW mode and normal mode in the Registry .

-> Then on my machine I tried to find the registries with the value "OLETransactionManagers". The first Key hit was : HKEY_CLASSES_ROOT\OLETransactionManagers

-> Since SQL Server was running in WOW mode on client's server , we tried to find HKEY_CLASSES_ROOT\Wow6432Node\OLETransactionManagers key and got it in first attempt.

-> However , on my laptop this key had some values while there were no values on the Client side registry key (mentioned above).

-> We found that these 64 bit MSDTC registry keys were there but the 32 bit (WOW mode) registry keys related to MSDTC were missing .

-> We created these keys in WOW mode

HKEY_CLASSES_ROOT\Wow6432Node\OLETransactionManagers
String value name = DefaultTM and value data=MSDTC

HKEY_CLASSES_ROOT\Wow6432Node\OLETransactionManagers\MSDTC
String value name=DLL and value data=MSDTCPRX.DLL


->We restarted MSDTC and SQL Server . We checked the SQL Server errorlogs and found that MSDTC related error is no longer showing and also the SQL Server SCC check was not showing that error related to COM+ Catalog any more.

->This gave us some hope and with that hope we hoped that the setup will run successfully .This in turn did happen (",).

Second Part: Reproducing the error
I reproduced this on my machine by removing all the keys from HKEY_CLASSES_ROOT\OLETransactionManagers

When I ran the setup it gave me the same warnings on SCC :

I will still go ahead with the setup and select only the client components to install :

The setup will encounter this error:

Let’s check the Application logs:

At this stage First let me introduce a tool that can show you how to find the missing registry key or if there are any permission related issues .I have been using this tool since long now .This tool is Procmon from sysinternals http://technet.microsoft.com/en-us/sysinternals/bb896645".We will use this tool to find the missing registry keys .However this tool captures a lot of information of all the processes .So we need to first PAUSE it as soon as we launch it .

Now, we will find the process ID of MSIEXEC.exe from Task Manager since as per the Application logs its the msiexec command that is failing:

I will now filter it on those 3 PIDs

Once it’s done, uncheck the PAUSE icon (the magnifying glass) and click on the retry option on the setup window. Quickly after that ,again PAUSE the Procmon by clicking on the magnifying glass icon. Notice the output in the Procmon. You will see a lot of different keys there .We now need to delete not necessary entries:

Right click on SUCCESS and select:

Right click on RegOpenKey and select:

You are now left with only 6 keys to look at(You can follow the same steps for other missing Reg keys issues ) and all these keys are REGQueryValue:

Now ,notice the error copied above from application log:
*******

Now, double click on each of the 6 keys (start from the bottom most) and select the option >> PROCESS. You will notice the same command line string as showing in the application logs:

So the first key that is missing is HKCR\OLETransactionManagers\DefaultTM .The reason it’s not showing all the missing keys is because it’s failing on first key itself. If you create the first key and then click on retry button on the setup the Procmon will show you the next missing key.

But what should be its value .We can check on other servers (preferably same server version).Create the missing keys and click on retry button .The setup will be successful (in our case it was SP3 and not the initial setup .But the resolution is same).

Third part : The Bugs
1) The issue is , when the SP3 setup fails it should ideally rollback everything to normal .So the NS and client tools should work fine.But On the client's server we saw that the Management Studio stop working and throws an error ( I dont have the screenshot now . Will try and reproduce it ) . The only solution is to uninstall the client tools and reinstall again .During the setup you will again get this error .Click on ignore and the setup will complete .

2) On my laptop , I saw that it also corrupt other components of SQL Server 2008 like Books on Line ..Again I do not have that proof now but will reproduce it .The solution is to uninstall client tools and reinstall tools of SQL Server 2008.


Happy Learning !!

Understanding how SQL Server behaves in a non-preemptive mode while still running on the OS which is preemptive

Today , I was asked by someone how SQL Server behaves in a non-preemptive way while still running on the OS which is preemptive .Even though I explained this theoritically ,I was requested if there is a way we can see it practically . It wasn't difficult but it really proved to what I explained .I alos felt that if we add practicals to theory it will have much deeper impact ...

NOTE : I am doing all the testing on SQL Server 2008 RTM EVAL as what I am trying to explain can be done in 2008 onwards.On 2005 you will not see what is written here.So I would request to use SQL 2008 and above .

Windows Scheduling (Preemptive) :
Starting from Windows NT 3.1 (XP ,2000,2003 etc) Windows scheduling was priority driven i.e preemptive scheduling .So every thread will have a priority associated with it .Based on this priority the threads will get the time slice (Quantum) to run on the CPU .So , even if thread of lower priority is running and all of a sudden another thread of a higher
priority comes up , the low priority thread will be preempted interrupted) and the higher priority thread will be scheduled to run on the CPU.However , the scheduler is smart enough .It will keep the preempted thread on the top of the waiting threads by adjusting its priority (Lets not get too deep in to this at this point).

Prior to this OS scheduling was non-preemptive i.e. cooperative scheduling .Remember the days when Windows 98 use to hang and we use to reboot the server quite often ,to get rid of it.Cooperative scheduling is good if all the threads leave the CPU after some time and give chance to other threads (including kernal mode threads which are more
Important and get a chance to run whenever required) after a fixed interval of time . But that normally does not happen .some nasty application threads don't yield and hence blocks other threads.

SQL Server Scheduling (non-preemptive) :
SQL Server has its own scheduling mechanism and it does not follow OS scheduling (looks strange as it runs on the preemptive OS) .Its called as UMS (User Mode Scheduling) in 2000 and SOS (SQL OS) in 2005 and above .BUT :
1) Why SQL Server does not hang just like windows 98 use to ?
Answer: SQL Server does not hang because its threads yield every voluntarily.In case a thread does not yield in 60 seconds (unlike the faulty application where the threads does not yield)SQL Server throws non-yielding scheduler hung error and throws a mini dump with the stack information of all the threads in it .

2) How SQL Server manages to schedule in the non-preemptive way ?
Answer: Windows OS will not schedule any thread which is running in infinite wait loop and simply ignores it. SQL Server (actually UMS) takes advantage of this and cleverly puts all the threads which it does not want to schedule to infinitely sleep by calling WaitforsingleObject function in an infinite loop .When SQL Server wants the thread to run it simply signal the thread and it comes out of the sleep modes .Its the Windows which then schedules the thread .Its important to know that UMS schedules only ONE SQl Server thread per CPU .However , there is an exception to this . There are moments where to complete a task the thread leaves the SQL Server scheduler and goes to preemptive mode scheduling . For example using xp_cmdshell to open notepad or running an extended stored procedure that deals with filesystem (like reading a file) or a linked server query
etc.In that situation , you will see more than one thread on a single CPU in runnable status .That is because one thread is scheduled via UMS\SOS and the other one directly via OS scheduling.

Let me show you a Demo since my Laptop has only one dual core processor (Its SQL Server 2008 RTM):
Lets first run a simple query and find runnable and sleeping threads Select Status ,* from sysprocesses where status not in ('background')

you will notice that all the SPIDS will show you the status of sleeping and there is only one SPID that will be showing you the status of runnable .Its waittype will be PREEMPTIVE_os_WAITFORSINGLEOBJECT .Notice that only runnable SPID has a KPID associated with it .This KPID is nothing but the worker thread associated with the SPID.You can run it a few times but the output will not change except the KPID which means that one thread is yielding to another after the context switching .The reason why we see runnable state and not running ,because by the time we get the query output the thread again goes to runnable state.You might also see other runnable or suspended SPIDs but its because they are running in preemptive mode .

Now lets open another Query window and execute the same command there .
Select Status ,* from sysprocesses where status not in ('background'). This time its SPID 53 (current SPID on my machine) which is showing us the Runnable state while SPID 52 (the previous SPID)is now sleeping .

Lets do one more experiment.Open a new query window (SPID 51 in my case) and run select @@servicename around 1000 times . Come back to SPID 53 window and notice if the runnable state is showing for SPID 53 or SPID 51.You will notice that we have the SPID 51 doing its task .But why is it showing as sleeping while CPU value is still increasing ? The reason is that when we run the query via SPID 53 , during (and only) that time 51 shows as sleeping because 53 needs to run . So thread related to 51 yields voluntarily. when this query finishes , SPID 51 again picks up , but we cant see that since we have only one processor :) ...

Anyway , let me show you the small test when the SQL Server thread goes preemptive .We have 2 Query windows.One with sysprocesses query (SPID 53) and one with calling xp_readerrorlog 100 times (SPID 51).I further modified my sysprocesses query by filtering sleeping SPIDs.

Select Status ,* from sysprocesses where status not in ('background','sleeping') .Lets run the query through SPID 51 and then by SPID 53.Notice that you have 2 runnable SPIDs now . Thats because SPID 51 is scheduled by OS and not SQL OS \UMS .

Have a nice day and Happy Learning !!!

Finding optimal number of CPUs for a given long running CPU intensive queries (except OLAP queries)

Hi Guys ,

Hope this article will help you in some or the other way one day :) .....

Introduction:
This small article is applicable for finding optimal number of CPUs for long running CPU intensive queries/workload that doesn’t frequently wait for other resources and is not applicable if your queries/workload is often waiting for resources (like I/Os, Locks, Latches etc.) without consuming CPU in a stretch .it can also provide information on uneven CPU load across NUMA nodes and uneven CPU load within same NUMA node (load_factor effect).
It is recommended to analyze Windows Performance Monitor Counters for monitoring CPU pressure. Processor utilization greater then 75% to 80% indicates CPU pressure. Using Windows Performance Monitor should be the 1st step, the procedure suggested in this article should be considered as an additional step.
Further ,it is very important to find ways to optimize the queries/workload by tuning the database schema before attempting to add additional CPUs.

Description:
When a customer asks you: I am running a resource consuming SQL job and it takes x amount of time, how can I reduce the time so the SQL job completes sooner, can I add more CPUs ? if yes, how many ?
When you see CPU pressure, there are 2 options: you can either upgrade to faster CPUs or add additional CPUs [assuming that the queries are well tuned and normalized]. Upgrading to faster CPU will always help. Adding additional CPUs may not always help the SQL job to run faster unless that SQL job can take advantage of additional CPUs [read Max Degree of parallelism form BOL]. If the customer already has the fastest CPUs available in the market then they have to wait for the next release of faster CPUs. One more choice woiuld be to add additional CPUs and see if it helps, the below procedure will help you identify if this is the case.
This method calculates total user waits for CPU during the SQL workload and suggests additional CPUs if necessary. If CPU usage is at 100%, but no one waited for CPU during the workload, then adding additional CPU will not help; this is the basics of this calculation.
Current recommendations that are available on this topic calculates ‘signal wait time’ to ‘wait time’ ratio to suggest CPU pressure – but this cannot help one easily identify number of additional CPUs necessary.

Procedure:
When concurrent users apply simultaneous CPU intensive workload, there could be CPU pressure. We can conclude presence of CPU pressure when at any given moment during this time period at least one or more user tasks waited for CPU resource.
In this case one can run the below query to find out how many CPU on an average will help to scale(out) the workload better. It might be more informative to collect the below information in short time intervals (many samples) than just once to understand during which time of the workload application the CPU pressure was the most. Single sample will lead to average additional CPUs necessary for the entire workload duration.
1. Reset Wait Stats
dbcc sqlperf('sys.dm_os_wait_stats', clear)
2. Apply workload (you can find sample workload query at the end of this article, you need to execute the sample workload query simultaneously in many sessions to simulate concurrent user tasks).
3. Run the below query to find Additional CPUs Necessary – it is important to run the query right after the workload completes to get reliable information.

select round(((convert(float, ws.wait_time_ms) / ws.waiting_tasks_count) / (convert(float, si.os_quantum) / si.cpu_ticks_in_ms) * cpu_count), 2) as Additional_CPUs_Necessary,
round((((convert(float, ws.wait_time_ms) / ws.waiting_tasks_count) / (convert(float, si.os_quantum) / si.cpu_ticks_in_ms) * cpu_count) / hyperthread_ratio), 2) as Additional_Sockets_Necessary
from sys.dm_os_wait_stats ws cross apply sys.dm_os_sys_info si where ws.wait_type = 'SOS_SCHEDULER_YIELD'

Example:
When you have 2 CPUs and you run the sample workload with just 1 or 2 concurrent sessions – you will see no recommendation for addition additional CPUs – unless there is unbalanced user task distribution across CPUs. On the other hand if you run the workload with 4 concurrent sessions – you will notice the query suggests you to add 2 additional CPUs. If you run with 6 concurrent sessions – you will notice the query suggests you to add 4 additional CPUs.
If each workload runs in parallel (MAXDOP not 1), then you will notice additional CPU suggestion, you need to be careful in this case. For example with 2 CPUs when you run the workload (in parallel, MAXDOP 0/2) with 2 concurrent sessions, you will notice the suggestion to add 2 additional CPUs – this just indicates the workload is more scalable with more CPUs – parallel query execution as you can imagine can consume as many CPUs as you have and can consume more!!
The results are not reliable when other applications are running in the system. Also the results might be incorrect on a hyper threading enabled system.

Explanation:
When there are more user tasks concurrently needing CPU than available CPU, the excess user tasks will wait for CPU (there are exceptions when the workload is not evenly distributed across CPUs). In this case each user task uses its quantum, then goes into a wait state (waiting for CPU with wait_type SOS_SCHEDULER_YIELD. sys.dm_exec_requests doesn’t show this wait type, probably by design to avoid showing user tasks in wait state when they are waiting for CPU. But sys.dm_os_wait_stats will include these waits) until all other runnable user tasks have used their quantum. If one measures how many tasks went into this wait state and for how long while the workload was applied – it is possible to calculate the CPUs necessary to scale the workload better.
runnabkle_task_count from the dm_os_schedulers is also a indication for CPU pressure, but it is just a probe – one cannot reasonably conclude the number of CPUs necessary for a given workload.

Exception:
There is an exception(for OLTP like workload) where a user tasks doesn’t consume all of its quantum(goes into some other wait state before the quantum expires, waiting for I/Os, Locks, Latches etc.) in a stretch, but continues to run in a loop using CPU without using its full quantum(You know what quantum is ...right :D). The method mentioned here cannot calculate the necessary additional CPUs in this case.. Most common example is short transactions using part of its quantum and starts WRITELOG waits and continues in a loop – inserts using implicit transactions in a loop is a typical example.


Sample Workload:
Create the below table before running the query to generate CPU intensive workload.
Serial Workload:
select max(t1.c2 + t2.c2) from tab7 t1 cross join tab7 t2 option (maxdop 1)
Parallel Workload:
select max(t1.c2 + t2.c2) from tab7 t1 cross join tab7 t2 
Table:
create table tab7 (c1 int primary key clustered, c2 int, c3 char(2000))
go
begin tran
declare @i int
set @i = 1
while @i <= 5000
begin
insert into tab7 values (@i, @i, 'a')
set @i = @i + 1
end
commit tran
go



Happy Learning !!!

SQL Server Setup has encountered the following error:File format is not valid

Today , we faced an issue where SQL Server 2008 R2 setup was failing at the very beginning .This issue can also be reproduced on 2008 and also in 2005 (in a slightely different way) .Please find the RCA below :

Version : SQL Server 2008 R2
OS : Win Server 2008

Error :
TITLE: SQL Server Setup failure.
------------------------------
SQL Server Setup has encountered the following error:
File format is not valid..
------------------------------
BUTTONS:
OK
------------------------------

Resolution :
Its very clear that there is a file that does not have a correct format and SQL Server cannot read it .So we need to find which file is that .We need to first open the Setup logs .In SQL 2008 a folder is created with the timstamp and all the logs are created inside it .On my machine it was C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20101109_201205 .

I first opened Detail_ComponentUpdate.txt

2010-11-09 20:13:14 Slp: Running Action: GatherUserSettings
2010-11-09 20:13:21 Slp: -- PidPublicConfigObject : ValidateSettings is normalizing input pid=[PID value hidden]
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : NormalizePid is normalizing input pid=[PID value hidden]
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : NormalizePid found a pid containing dashes, assuming pid is normalized, output pid=[PID value hidden]
2010-11-09 20:13:21 Slp: -- PidPublicConfigObject : ValidateSettings proceeding with normalized pid=[PID value hidden]
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : Initialize is initializing using input pid=[PID value hidden]
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : NormalizePid is normalizing input pid=[PID value hidden]
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : NormalizePid found a pid containing dashes, assuming pid is normalized, output pid=[PID value hidden]
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : Initialize proceeding with normalized pid=[PID value hidden]
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : Initialize called ValidatePid, output is pid=[PID value hidden] validateSuccess=True output editionId=EVAL(0x2467BCA1)
2010-11-09 20:13:21 Slp: -- PidPublicConfigObject : ValidateSettings initialized private object, result is initializeResult=Success

2010-11-09 20:13:22 Slp: Detected localization resources folder: 1033
2010-11-09 20:13:22 Slp: License file: C:\Documents and Settings\Abhay\Desktop\2008 R2 X64\2008_R2_x86\x86\1033\License_EVAL.rtf
2010-11-09 20:13:22 Slp: Error: Action "GatherUserSettings" threw an exception during execution.

2010-11-09 20:13:22 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: Thread was being aborted. ---> System.Threading.ThreadAbortException: Thread was being aborted.
2010-11-09 20:13:22 Slp: at System.Threading.WaitHandle.WaitOneNative(SafeWaitHandle waitHandle, UInt32 millisecondsTimeout, Boolean hasThreadAffinity, Boolean exitContext)
2010-11-09 20:13:22 Slp: at System.Threading.WaitHandle.WaitOne(Int64 timeout, Boolean exitContext)
2010-11-09 20:13:22 Slp: at System.Threading.WaitHandle.WaitOne(Int32 millisecondsTimeout, Boolean exitContext)
2010-11-09 20:13:22 Slp: at System.Threading.WaitHandle.WaitOne()
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Configuration.UIExtension.Request.Wait()
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Configuration.UIExtension.UserInterfaceProxy.SubmitAndWait(Request request)
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Configuration.UIExtension.UserInterfaceProxy.NavigateToWaypoint(String moniker)
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Configuration.UIExtension.UserInterfaceService.Waypoint(String moniker)
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Configuration.UIExtension.WaypointAction.ExecuteAction(String actionId)
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.InvokeAction(WorkflowObject metabase, TextWriter statusStream)
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)
2010-11-09 20:13:22 Slp: --- End of inner exception stack trace ---
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)
2010-11-09 20:13:25 Slp: Received request to add the following file to Watson reporting: C:\Documents and Settings\Abhay\Local Settings\Temp\tmp11E.tmp
2010-11-09 20:13:25 Slp: The following is an exception stack listing the exceptions in outermost to innermost order
2010-11-09 20:13:25 Slp: Inner exceptions are being indented
2010-11-09 20:13:25 Slp:
2010-11-09 20:13:25 Slp: Exception type: System.ArgumentException
2010-11-09 20:13:25 Slp: Message:
2010-11-09 20:13:25 Slp: File format is not valid.
2010-11-09 20:13:25 Slp: Stack:
2010-11-09 20:13:25 Slp: at System.Windows.Forms.RichTextBox.StreamIn(Stream data, Int32 flags)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.RichTextBox.LoadFile(Stream data, RichTextBoxStreamType fileType)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.RichTextBox.LoadFile(String path, RichTextBoxStreamType fileType)
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.InstallWizard.EULAPidView.UpdateLicenseText(String filepath)
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.InstallWizard.EULAPidController.LoadData()
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.InstallWizardFramework.InstallWizardPageHost.PageEntered(PageChangeReason reason)
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.WizardFramework.UIHost.set_SelectedPageIndex(Int32 value)
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.WizardFramework.UIHost.GoNext()
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.WizardFramework.NavigationButtons.nextButton_Click(Object sender, EventArgs e)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Control.OnClick(EventArgs e)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Button.OnClick(EventArgs e)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Control.WndProc(Message& m)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.ButtonBase.WndProc(Message& m)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Button.WndProc(Message& m)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
2010-11-09 20:28:33 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Microsoft\Microsoft SQL Server to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20101109_201205\Registry_SOFTWARE_Microsoft_Microsoft SQL Server.reg_
2010-11-09 20:28:33 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20101109_201205\Registry_SOFTWARE_Microsoft_Windows_CurrentVersion_Uninstall.reg_
2010-11-09 20:28:33 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Microsoft\MSSQLServer to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20101109_201205\Registry_SOFTWARE_Microsoft_MSSQLServer.reg_
2010-11-09 20:28:36 Slp: File format is not valid.
2010-11-09 20:28:36 Slp: Watson bucket for exception based failure has been created
2010-11-09 20:28:36 Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine
2010-11-09 20:28:36 Slp: Sco: Attempting to open registry subkey Software\Microsoft\PCHealth\ErrorReporting\DW\Installed
2010-11-09 20:28:36 Slp: Sco: Attempting to get registry value DW0200
2010-11-09 20:29:01 Slp: Submitted 1 of 1 failures to the Watson data repository

Assuming that there is a file with incorrect format I took a chance to open this file as mentioned in the error above .C:\Documents and Settings\Abhay\Desktop\2008 R2 X64\2008_R2_x86\x86\1033\License_EVAL.rtf . Since this is an RTF file we can open it in WORDPAD ....

When opened , I found it unreadable ...Initially I thought it is suppose to be like that as there might be something encrypted .
However , there were other license files in the same folder which were absolutely readable ..

This made me curious and I checked the same file on my machine as I also had the same EVAL setup ...I was able to read it word by word .So it was clear that the file was corrupt ....We tried and swapped the file between my TP and the server ...The setup moved forward :) .

Hope this helps .

Error 1706. An installation package for the product Microsoft SQL Server 2005 cannot be found. Try the installation again using a valid copy of the installation package 'SqlRun_SQL.msi'.

adding a new post after a good gap ...
Recently we faced an issue where we lost physical files of Master database (master.mdf and mastlog.ldf).We had the backup files but we could not use them unless SQL Server is up and running .So we had no choice but to rebuild master .

We tried the step below via DOS prompt :
C:\Documents and Settings\Abhay\Desktop\softwares\SQLEVAL_2005\Servers>start /wait setup.exe /qn INSTANCENAME=CORRUPT REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=XXXXX

This had always worked for me and is also mentioned in BOL .However , this time this did not work .The errors I got in the log file were (to find the error we should search for Return Value 3):

Error 1706. An installation package for the product Microsoft SQL Server 2005 cannot be found. Try the installation again using a valid copy of the installation package 'SqlRun_SQL.msi'.
MSI (s) (2C:08) [14:07:26:265]: User policy value 'DisableRollback' is 0
MSI (s) (2C:08) [14:07:26:265]: Machine policy value 'DisableRollback' is 0
Action ended 14:07:26: InstallFinalize. Return value 3.

The setup files were valid and had been used many time in the past ..I ran SQLRun_SQL.msi manually and it was running fine .I also used 2 different setups and go the same error .
Also

MSI (s) (2C:08) [14:07:26:281]: File: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\modellog.ldf; To be installed; Won't patch; No existing file
MSI (s) (2C:08) [14:07:26:281]: Executing op: FileCopy(SourceName=C:\Config.Msi\3bf6ceb.rbf,,DestName=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\model.mdf,Attributes=32800,FileSize=1245184,PerTick=0,,VerifyMedia=0,ElevateFlags=3,,,,,,,InstallMode=4194308,,,,,,,)
MSI (s) (2C:08) [14:07:26:281]: File: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\model.mdf; To be installed; Won't patch; No existing file
MSI (s) (2C:08) [14:07:26:296]: Executing op: FileCopy(SourceName=C:\Config.Msi\3bf6cea.rbf,,DestName=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdblog.ldf,Attributes=32800,FileSize=786432,PerTick=0,,VerifyMedia=0,ElevateFlags=3,,,,,,,InstallMode=4194308,,,,,,,)
MSI (s) (2C:08) [14:07:26:296]: File: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdblog.ldf; To be installed; Won't patch; No existing file
MSI (s) (2C:08) [14:07:26:296]: Executing op: FileCopy(SourceName=C:\Config.Msi\3bf6ce9.rbf,,DestName=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdbdata.mdf,Attributes=32800,FileSize=12255232,PerTick=0,,VerifyMedia=0,ElevateFlags=3,,,,,,,InstallMode=4194308,,,,,,,)
MSI (s) (2C:08) [14:07:26:296]: File: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdbdata.mdf; To be installed; Won't patch; No existing file
MSI (s) (2C:08) [14:07:26:296]: Executing op: FileCopy(SourceName=C:\Config.Msi\3bf6ce8.rbf,,DestName=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf,Attributes=32800,FileSize=853016576,PerTick=0,,VerifyMedia=0,ElevateFlags=3,,,,,,,InstallMode=4194308,,,,,,,)
MSI (s) (2C:08) [14:07:26:296]: File: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf; To be installed; Won't patch; No existing file
MSI (s) (2C:08) [14:07:26:312]: Executing op: FileCopy(SourceName=C:\Config.Msi\3bf6ce7.rbf,,DestName=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf,Attributes=32800,FileSize=92602368,PerTick=0,,VerifyMedia=0,ElevateFlags=3,,,,,,,InstallMode=4194308,,,,,,,)
MSI (s) (2C:08) [14:07:26:312]: File: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf; To be installed; Won't patch; No existing file

This was strange as these files were existing ....

I ran the same command using \qb option and got the same error but in a form of a pop-up box.

I then checked and found that there is an option called as REINSTALLMODE .;REINSTALLMODE is used to repair installed components. The supported values are:
O – Reinstall if file is missing, or an older version is present.
M – Rewrite machine specific reg keys under HKLM
U – Rewrite user specific reg keys under HKCU
S – Reinstall all shortcuts

The Option O looked appropriate but I used all i.e.
C:\Documents and Settings\Abhay\Desktop\softwares\SQLEVAL_2005\Servers>start /wait setup.exe /qn INSTANCENAME=CORRUPT REINSTALL=SQL_Engine REINSTALLMODE=OMUS REBUILDDATABASE=1 SAPWD=XXXXX

This resolved the issue on my laptop but not on client server .
Finally I found that there is one more option which is never documented ...and this option is V
The issue was that the setup was copied for a different server and the original media location of where the RTM bits where installed in some cache file.That was the reason we were getting the error about the installation package not being found.To resolve this we had to use the option V to re-cache the media from the new location.

C:\Documents and Settings\Abhay\Desktop\softwares\SQLEVAL_2005\Servers>start /wait setup.exe /qn INSTANCENAME=CORRUPT REINSTALL=SQL_Engine REINSTALLMODE=V REBUILDDATABASE=1 SAPWD=XXXXX


This ran like a knife through butter ..

Hope it will help you in future ...

Error: 26049, Severity: 16, State: 1 :Server local connection provider failed to listen on [ \\.\pipe\SQLLocal\XXXXX ]. Error: 0x5

There might be many reasons and many solutions for this kind of error.But let me explain my situation :) ...For testing I installed a new default instance on one of the test servers.The setup was successful .However , later one of the other named instance did not come up after the restart.

The errors were :

2010-09-30 03:17:31.65 Server Error: 26049, Severity: 16, State: 1.
2010-09-30 03:17:31.65 Server Server local connection provider failed to listen on [ \\.\pipe\SQLLocal\XXXXX ]. Error: 0x5
2010-09-30 03:17:31.65 Server Error: 17182, Severity: 16, State: 1.
2010-09-30 03:17:31.65 Server TDSSNIClient initialization failed with error 0x5, status code 0x40.
2010-09-30 03:17:31.65 Server Error: 17182, Severity: 16, State: 1.
2010-09-30 03:17:31.65 Server TDSSNIClient initialization failed with error 0x5, status code 0x1.
2010-09-30 03:17:31.65 Server Error: 17826, Severity: 18, State: 3.
2010-09-30 03:17:31.65 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2010-09-30 03:17:31.65 Server Error: 17120, Severity: 16, State: 1.
2010-09-30 03:17:31.65 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

The issue is quite simple unlike it looks like .I tried everything like changing the named pipe , etc. etc.
Assuming, that 0x5 is the OS error which means Access Denied ,I gave the permission to the domain ID on the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer.
This resolved the issue .

status code 0x40 means that there is an issue with Shared memory listener
status code 0x50 means that there is an issue with Named pipe listener
status code 0x0A means that there is an issue with TCP/IP listener

Please go through this MSDN blog (which has one more link in it).
http://blogs.msdn.com/b/sql_protocols/archive/2006/03/09/546655.aspx?wa=wsignin1.0

Happy Learning !!!

A simple VB script to retain Errorlogs worth 90 days (or as you like)

So far ,I heard retaining X number of errlogs which is widely used (So I am not writing that script here)...But one of our clients asked us to retain errlogs worth only 90 days .The client was not ready to recycle the errorlogs and wanted us to keep them to the default ...

Finally , we could come out with a simple VB script that can do it .The script code is mentioned below .

Code :


sFolder = "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG"
iMaxAge = 90
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FolderExists(sFolder) Then
for each oFile in oFSO.GetFolder(sFolder).Files
If DateDiff("d", oFile.DateLastModified, Now) > iMaxAge and (oFile.name= "ERRORLOG.1" or oFile.name= "ERRORLOG.2" or oFile.name= "ERRORLOG.3" or oFile.name= "ERRORLOG.4" or oFile.name= "ERRORLOG.5" or oFile.name= "ERRORLOG.6") Then
wscript.echo "Deleting" &oFile.Name
oFile.Delete
End If
next
End If

You will need to create a scheduled task/Or SQL Server job using xp_cmdshell, to run at a specific time .Once its kicked off , if any of the files mentioned in the code (Note : errorlog will not be tried upon) have a timestamp greater than 90 days from the day you are executing the file , It will delete those files ...for example if I have 7 files below :

File Timestamp
Errorlog 12/9/2009
Errorlog.1 12/8/2009
Errorlog.2 12/7/2009
Errorlog.3 12/6/2009
Errorlog.4 12/5/2009
Errorlog.5 12/4/2009
Errorlog.6 12/3/2009

The files deleted will be : Errorlog.3,Errorlog.4,Errorlog.5 and Errorlog.6

You need to change the Path of sFolder variable ..
Happy learning ...

Abhay

checkODBCConnectError: sqlstate = 28000; native error = 4818; message = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'XXXXX\clusterservice

a little Background :
As per our security guidelines Builtin\Administrator login should be removed from all the SQL Server instances.It was implemented on all the SQL Server instances including those which are on MCSC (Windows Cluster).
After that, the nodes were rebooted due to patching requirements .The nodes came up , but SQL Server did not :D ...

Error in cluster logs (you will not find it in SQL Server logs) :

ERR SQL Server : [sqsrvres] checkODBCConnectError: sqlstate = 28000; native error = 4818; message = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'XXXXX\clusterservice'.
ERR SQL Server : [sqsrvres] ODBC sqldriverconnect failed

The error was clear .The cluster service was not able to login to SQL Server through user XXXXX\clusterservice but via a LOGIN ...That login is BUILTIN\Administrators.
But why it needs to login to SQL Server ?? Because it needs to run the isAlive check to make sure that the SQL Server is up and running .It also runs the looksalive (its a function)check but that does not need to query SQL Server .Is Alive check runs select @@servername and waits for the return message through ODBC client (in our case its SQL Server Native client).Thus the Isalive check was not able to create a trusted connection and we lost the access to Virtual server.

So, in a SQL Server 2005\2008 failover cluster installation, the cluster service account relies on membership in the BUILTIN\Administrators group to log on to SQL Server 2005\2008 to run the IsAlive check.If you remove the BUILTIN\Administrators group from a failover cluster, you must explicitly grant the MSCS service account permissions to log on to the SQL Server 2005 failover cluster.

The SQL Server 2005 resource starts an instance of the Sqlcmd.exe utility under the security context of the MSCS service account. Then, the SQL Server 2005 resource runs an SQL script over a dedicated administrator connection (DAC) that samples various dynamic management views (DMV). Because a DAC connection is used to collect some diagnostic data, the clustering service account must be provisioned in the SYSADMIN fixed server role. If later someone says that clustering service account cannot be provisioned in the SYSADMIN fixed server role, then we can create a login for cluster service account that is not given the SYSADMIN fixed server role .I have not tested it yet .So cannot confirm that this will work on not ...

Commands :
CREATE LOGIN [\] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
EXEC master.sp_addsrvrolemember @loginame = N'\ ', @rolename = N'sysadmin'

happly learning .....
Regards
Abhay

Msg 22004, Level 16, State 1, Line 0 :Failed to open loopback connection. Please see event log for more information.Failed to open loopback connection. Please see event log for more information.

I am back with one more solution :).The problem was simple but the error made me thinking ..I was trying to do xp_readerrorlog on a small file .But my SPID hanged ..after some time I got this error :

SQL Server error in QA :
Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found

I read somewhere that this error comes when SQL Server Agent failes to come up.Yes my agent was down.But I faied to understand what is the relation between running xp_readerrlog and SQL Agent not running .Still, I tried to run the agent and got the error ...So something is related to SQL Agent here and that something is that If SQL Agent is not running , I can run xp_readerrlog successfully (I will prove it wrong later).

I checked the application logs immediately and got these errors :

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 09/20/2010
Time: 18:24:22
User: N/A
Computer: abchaudh
Description:
Severity: 16 Error:10061, OS: 10061 [Microsoft][SQL Native Client]TCP Provider: No connection could be made because the target machine actively refused it.


For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 4d 27 00 00 0a 00 00 00 M'......
0008: 12 00 00 00 61 00 62 00 ....a.b.
0010: 63 00 68 00 61 00 75 00 c.h.a.u.
0018: 64 00 68 00 00 00 0e 00 d.h.....
0020: 00 00 6d 00 61 00 73 00 ..m.a.s.
0028: 74 00 65 00 72 00 00 00 t.e.r...


Event Type: Error
Event Source: SQLAgent$CORRUPT
Event Category: Service Control
Event ID: 103
Date: 09/20/2010
Time: 18:24:45
User: N/A
Computer: abchaudh
Description:
SQLServerAgent could not be started (reason: Unable to connect to server 'abchaudh\CORRUPT'; SQLServerAgent cannot start).

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Type: Information
Event Source: SQLAgent$CORRUPT
Event Category: Service Control
Event ID: 102
Date: 09/20/2010
Time: 18:24:52
User: N/A
Computer: abchaudh
Description:
SQLServerAgent service successfully stopped.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

I also checked the SQL Agent logs :

SQL Agent logs :

2010-09-20 18:25:36 - ! [298] SQLServer Error: 10061, TCP Provider: No connection could be made because the target machine actively refused it. [SQLSTATE 08001]
2010-09-20 18:25:36 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
2010-09-20 18:25:36 - ! [298] SQLServer Error: 10061, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
2010-09-20 18:25:36 - ! [000] Unable to connect to server 'abchaudh\CORRUPT'; SQLServerAgent cannot start
2010-09-20 18:25:42 - ! [298] SQLServer Error: 10061, TCP Provider: No connection could be made because the target machine actively refused it. [SQLSTATE 08001]
2010-09-20 18:25:42 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
2010-09-20 18:25:42 - ! [298] SQLServer Error: 10061, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
2010-09-20 18:25:42 - ! [382] Logon to server 'abchaudh\CORRUPT' failed (DisableAgentXPs)
2010-09-20 18:25:43 - ? [098] SQLServerAgent terminated (normally)

So, now I have 2 issues : SQL Agent is not running and xp_readerrorlog is timing out.

.If you see one of my older posts on "target machine actively refuses it" , you will have some information .

So I opened CLICONFG and found 3 incorrect aliases which were not using the right port ..

I removed them and SQL Agent came on line ...xp_read errorlog also started working ...

I stopped SQL Agent and stil everything was working ...
So the issue was that xp_readerrlog tries to connect to SQL Server but stucks due to the Alias pointing to incorrect port.

But this does not affect SQL Server service .To check why its not affecting SQL Server service , I disabled the sahred memory protocol and BANG....SQL Server connection failed ...

Happy learning ....

Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)

Hi Team ,
This issue was faced by someone outside IBM but my main intention is to explain the benefit of another nice tool : Dependency Walker (http://www.dependencywalker.com/)

Issue :
SQL Server Agent failed to come up after the service account password was reset at AD level .

Error(s) :
In the event log you will see these errors in sequence :

Description:
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).

Description:
Failed to retrieve SQLPath for syssubsystems population.

Description:
SQLServerAgent could not be started (reason: Failed to load any subsystems. Check errorlog for details.).

The first error is the main error and rest are the errors following the first error and we need not to think about them .

Troubleshooting and Resolution :
The error clearly says that either there is a problem with xpstar90.dll or the other dlls that this dll references .
This file is located in I first tried to re-register xpstar90.dll by using regsvr32 xrstar90.dll and got this message :

xpstar90.dll was loaded , But the dllRegisterServer entry point was not found.

I have heard that sometimes there is a different way of registering some DLLs , so by this error I did not come to the conclusion that this file is corrupt.
I was also thinking that there might be some other DDL that this DLL refers to , which got corrupted.

I decided to see the tree structure of xpstar90.dll in Dependency Walker . I opened the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\xpstar90.dll in it and go this output.



So, in this case XPSTAR90.DLL itself was corrupt .I found its version 2005.90.4035.0 and replaced it with another one that I had in another instance .
SQL Agent came online .
In case if it dos not , then we need to uninstall Native client from ADD Remove Programs and reinstall it .

Happy learning
Abhay

Error 1117 :The request could not be performed because of an I/O device error.

Our backups were failing under these conditions :

Scenario 1: The System databases plus few user databases are on local disk & few user databases are on LUNs.

Scenario 2: The System & user databases are completely on LUNs

The backups were running for some good amount of time but then use to fail with Error 1117.I know that taking backups on network is not suported but I was breaking my head on this ERROR (1117)to know the reason behind this error .After going through a few tests on my machine using external HDDs ,my understanding of this error is :


-> Error 1117 is ERROR_IO_DEVICE .Thats fine .But I was curious about knowing the situations under which this error might occur and what is the exact meaning on this Error .Does Error_IO_Device means that the Hardware is corrupt ? Found that this error occurs under the below situations and then found the reasons behind those situations as well :

STATUS_FT_MISSING_MEMBER
ERROR_IO_DEVICE

An attempt was made to explicitly access the secondary copy of information via a device control to the fault tolerance driver and the secondary copy is not present in the system.


STATUS_FT_ORPHANING
ERROR_IO_DEVICE
{FT Orphaning} A disk that is part of a fault-tolerant volume can no longer be accessed.


STATUS_DATA_OVERRUN
ERROR_IO_DEVICE
{Data Overrun} A data overrun error occurred.

STATUS_DATA_LATE_ERROR
ERROR_IO_DEVICE
{Data Late} A data late error occurred.


STATUS_IO_DEVICE_ERROR
ERROR_IO_DEVICE
The I/O device reported an I/O error

STATUS_DEVICE_PROTOCOL_ERROR
ERROR_IO_DEVICE
A protocol error was detected between the driver and the device.


STATUS_DRIVER_INTERNAL_ERROR
ERROR_IO_DEVICE
An error was detected between two drivers or within an I/O driver.


So this error mapping says that this error will be thrown out if anyof these conditions are met .In my situation we were falling in into STATUS_DATA_LATE_ERROR since we were also getting thses entries in the SQL serve errorlogs : "x I/O requests are pending for more then 15 secs ............filename.mdf"

If you are running backup jobs you might also get this error -1073548784 .
This is a common error and may come when the query you are running remotely is incorrect , or the table you are trying to drop does not exist .Try to export a table that already exists in another DB and you will recreate this OLEDB error.So we need not to worry about finding the message identifier for this number .


Action plan :
-----------------
--try to take backup of another database located remotely and of near about same size . I mean around 20GB.

--Run Chkdsk on this drive or ask someone to do that and see if the consistency errors come up .

--Create a similar database on another external drive like this one and take the backup .


Conclusion :
---------------
I am very much certain that the issue is with the drive and(OR)Network.The 15 sec IO delay messages in Errorlogs also suggests the same .But as you can see this error also comes when dataa gets late in reaching the destination (STATUS_DATA_LATE_ERROR) I am suspecting that the network might also be a bit slow and contributing to the backup failure .

Now the ball is in your court how you explain this to the client :) .

Hapy Learning

Msg 8914, Level 16, State 1, Line 1 -> Incorrect PFS free space information for page

Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:61991) in object ID 1993058136, index ID 1, partition ID 72057594955366400, alloc unit ID 71906736119218176 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.


This was the error we were getting in the Docs table of one of the Sharepoint database .The Compatibility level was 80 and Build was 1399 (2005 RTM).

I tried a lot of things on it like :
-> I Rebuilt the clustered index with and without LOB_COMPACTION option .
-> DBCC page shows is fill factor 100
-> Changed the fill factor to 100 explicitly
-> Ran dbcc updateusage
-> changed the compatibility level to 90
-> changed the fill factor to to 99 ,50 etc
Nothing helped .The profiler did not show much (my intention was to know what checkdb is doing internally).

Finally I took the backup of the database and restored it as a test database .It did not give any errors .This means that actually its not a corruption .

On the restored database Ran DBCC checkdb with repair_allow_data_loss .
It fixed the issue without harming the data .Finally Ran the same on the Sharepoint database and it resolved the issue .

Hope this gives you the confidence to run the repair_allow_data_loss for this issue .
But remember , almost every time if you run it with repair_allow_data_loss you will end up loosing the data .So be careful .

This situation was AN EXCEPTION and you can safely use this option of checkdb.

Root cause :
Microsoft says that
the engine (just like OS does which giving pages to processes ) pre-allocates a set of data pages (say X) to the SPID which needs it and marks them as 100% full in PFS assuming that those pages will eventually get filled very soon.It does this to avoid frequently updating PFS page and improving performance.But later when the SPID completes its work in less pages (say X-Y) , these remaining pages are released .However, the remaining pages should be marked again as empty (0_PCT_FULL) which it does not do and hence DBCC CheckDB reports those errors (SQL 2000 silently use to fix it ).Repair_allow_data_loss will fix it with no data loss actually.

Regards
Abhay

Finding the last date when the LOG/FULL/DIFF/FILEGROUP backup was taken for all the databases

Hi Guys ,
While creating a few scripts , a requirement came where I had to find the last backups (all types) taken for all the databases (except tempdb) .
Please find the script below .Hope it helps you in your daily activities .If you want to automate it for all the instances in your environment , please let me know and I can send you some more files.



/*
Script : Last_bckp.sql
Author : Abhay Chaudhary,
Date : 26th JUL, 2010
Purpose : Collecting SQL Server 2000/2005/2008 last backup taken information.
Requirements : Do a CTRL+F and change the to the DB where you want to
create the object.
Suggestions : hi_abhay78@yahoo.co.in
Version : 1.0
*/


USE
set nocount on
if not exists (select * from ..sysobjects where name ='bckp_types' and type ='S')
begin
create table ..bckp_types (num int identity(1,1),type varchar(1),bkp_name varchar(20))
insert into ..bckp_types (type,bkp_name) values ('D','Full backup')
insert into ..bckp_types (type,bkp_name) values ('L','Log Backup')
insert into ..bckp_types (type,bkp_name) values ('F','Filegroup backup')
insert into ..bckp_types (type,bkp_name) values ('I','Differential backup')
end
go

Declare @loop int
select @loop= max(num) from bckp_types
While (@loop !=0)
begin
Select 'last ' + bkp_name +' taken details.' from bckp_types where num=@loop
declare @bk_type varchar(1)
select @bk_type = type from bckp_types where num=@loop

SELECT s.name 'database Name',
b.backup_finish_date 'last backup date',
bmf.physical_device_name 'location of backup'
FROM master..sysdatabases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name
INNER JOIN msdb..backupmediafamily bmf ON b.media_set_id = bmf.media_set_id
WHERE s.name <> 'tempdb'
AND b.backup_finish_date = (SELECT MAX(backup_finish_date)
FROM msdb..backupset
WHERE database_name = b.database_name
AND type = @bk_type)
ORDER BY s.name

set @loop=@loop-1
end
go
Drop table ..bckp_types


Happy Learning ...
Abhay

SQLServer Error: 848, SQL Network Interfaces: The system detected a possible attempt to compromise security.

We faced a strange but simple issue yesterday and as usual I would like to share it with you .



Situation :
-------------
SQL server 2005 SP2
Windows Server 2003 SP2
Cluster : Yes 2 node A-P cluster

Service account of SQL Server Agent service and SQL Server service were same .SQL Server is Clustered .

While SQL Sevrer as well as agent were running fine the account under both the services are running ,got locked(we came to know this later as a rootcause of this issue).Still ,everything was fine and there was no issue since the account got locked after SQL Server and agent were started.

Then we found that all the jobs that were scheduled stopped working .In the job history we found that there is no JOB HISTORY created since the jobs stopped working .But there was not a single failure of the jobs .

Which means that the jobs were not scheduled by the Job schedular >> to the Threads >> to the SPIDs .So , we manually executed the jobs and all of them completed successfully .But again , there was no history being created and those jobs were not doing anything .For example , the backup job was running successfully when we ran it explicitly but no backups were taken .

To drill down further , we ran the commands under the jobs in QA and those were running fine .We created new jobs and there was no change at all in the situation .

Then we checked the SQL Agent logs and found this :

[298] SQLServer Error: 848, SQL Network Interfaces: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you. [SQLSTATE HY000]
[298] SQLServer Error: 848, Cannot generate SSPI context [SQLSTATE HY000]
[382] Logon to server '(local)' failed (ConnAttemptCachableOp)

This was strange to us as why the connectivity error is not being displayed when we were explicitely executing the job, which completes successfully and doing nothing.
But since it was the connectivity error by agent , we decided to run the jobs by logging on to the server using the account under which SQL Server and agent are running.

We then found out the the account was locked under which SQL Server and agent were still running.

Once the account got unlocked at the AD ,the jobs ACTUALLY started working .

To me it looks like a bug in design and i have logged it on the CONNECT :
https://connect.microsoft.com/SQLServer/feedback/details/575388/strange-behaviour-in-sql-agent-job-on-cluster-where-the-job-runs-but-does-not-do-anything

hope it helos you to resolve your issue .

Data auditing in SQL Server

There are 2 ways we can audit the SQL Server events to the tracefile (people call it audit log file).

- setting sp_configure parameter 'c2 audit mode' to 1.This will automatically capture all the audit events for all the databases and all the columns . You cannot modify it .Even if you try to , it will
not take the changes made manually .

- Creating our own trace for selected events and columns .Please check BOL for it .

In case you want to go through the second option and that is to create our own trace please see the demo below:

Step 1
In this step we are creating test_yasir trace in C: drive.Then we are setting the Events and columns adn settin gthem to ON .I have choosen a few events and columns .

declare @TraceIdOut int
exec sp_trace_create @TraceIdOut OUTPUT,6, N'c:\test_Yasir'
PRINT @TraceIdOut

declare @On bit
SET @On = 1
exec sp_trace_setevent @TraceIdOut, 14, 6, @On
exec sp_trace_setevent @TraceIdOut, 14, 7, @On
exec sp_trace_setevent @TraceIdOut, 14, 8, @On
exec sp_trace_setevent @TraceIdOut, 14, 9, @On
exec sp_trace_setevent @TraceIdOut, 14, 10, @On
exec sp_trace_setevent @TraceIdOut, 15, 6, @On
exec sp_trace_setevent @TraceIdOut, 15, 7, @On
exec sp_trace_setevent @TraceIdOut, 15, 8, @On
exec sp_trace_setevent @TraceIdOut, 15, 9, @On
exec sp_trace_setevent @TraceIdOut, 15, 10, @On
exec sp_trace_setevent @TraceIdOut, 20, 6, @On
exec sp_trace_setevent @TraceIdOut, 20, 7, @On
exec sp_trace_setevent @TraceIdOut, 20, 8, @On
exec sp_trace_setevent @TraceIdOut, 20, 9, @On
exec sp_trace_setevent @TraceIdOut, 20, 10, @On


exec sp_trace_setevent @TraceIdOut, 104, 1, @On
exec sp_trace_setevent @TraceIdOut, 104, 3, @On
exec sp_trace_setevent @TraceIdOut, 104, 6, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 10, @On
exec sp_trace_setevent @TraceIdOut, 104, 11, @On
exec sp_trace_setevent @TraceIdOut, 104, 14, @On
exec sp_trace_setevent @TraceIdOut, 104, 22, @On
exec sp_trace_setevent @TraceIdOut, 104, 26, @On
exec sp_trace_setevent @TraceIdOut, 104, 35, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On

exec sp_trace_setevent @TraceIdOut, 107, 1, @On
exec sp_trace_setevent @TraceIdOut, 107, 3, @On
exec sp_trace_setevent @TraceIdOut, 107, 6, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 10, @On
exec sp_trace_setevent @TraceIdOut, 107, 11, @On
exec sp_trace_setevent @TraceIdOut, 107, 14, @On
exec sp_trace_setevent @TraceIdOut, 107, 22, @On
exec sp_trace_setevent @TraceIdOut, 107, 26, @On
exec sp_trace_setevent @TraceIdOut, 107, 35, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On

exec sp_trace_setevent @TraceIdOut, 106, 1, @On
exec sp_trace_setevent @TraceIdOut, 106, 3, @On
exec sp_trace_setevent @TraceIdOut, 106, 6, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 10, @On
exec sp_trace_setevent @TraceIdOut, 106, 11, @On
exec sp_trace_setevent @TraceIdOut, 106, 14, @On
exec sp_trace_setevent @TraceIdOut, 106, 22, @On
exec sp_trace_setevent @TraceIdOut, 106, 26, @On
exec sp_trace_setevent @TraceIdOut, 106, 35, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On


exec sp_trace_setevent @TraceIdOut, 105, 1, @On
exec sp_trace_setevent @TraceIdOut, 105, 3, @On
exec sp_trace_setevent @TraceIdOut, 105, 6, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 10, @On
exec sp_trace_setevent @TraceIdOut, 105, 11, @On
exec sp_trace_setevent @TraceIdOut, 105, 14, @On
exec sp_trace_setevent @TraceIdOut, 105, 22, @On
exec sp_trace_setevent @TraceIdOut, 105, 26, @On
exec sp_trace_setevent @TraceIdOut, 105, 35, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On


Step 2 :

In this step we wil apply the filter since you said you need to audit only a user database.We will achieve it using sp_trace_setfilter

sp_trace_setfilter 3,3,0,0,1

In this example I have set the filter on databaseid (to 1 which is master) in traceid 3

Step 3:
In this step we will first confirm if our trace is showing up in the metadata.Do a select * from sys.traces and check the trace you created as well its trace id .
Then start the trace (which is 1) using sp_trace_setstatus

example :
sp_trace_setstatus 3,1

Here traceid is 3 and staus is 1
Further , if you want to add or remove any event use sp_trace_setevent after stopping the trace using sp_trace_setstatus


But in this method there is a problem .The problem is that , if you restart the instance the trace metadata will be washed from the sys.traces DMV.
So you will have to manually run it again .Further the physical trace file (log file) still exist.So you will get the error while creaing the trace .To over come this :

1) I have added the datetime in the file name .So it will create a unique file each minute.
2) I have encapsulated the query into an SP and pinned it to SQL Server startup.

So now

step 1 would be

create proc audit_trace as
declare @TraceIdOut int
Declare @D1 nvarchar(30)
Declare @D2 nvarchar(30)
Declare @D3 nvarchar(30)
Declare @D4 nvarchar(30)
Declare @D5 nvarchar(30)
Declare @trace_name nvarchar(256)

SELECT @D1=DATENAME(Day, GETDATE())
SELECT @D2=DATENAME(month, GETDATE())
SELECT @D3=DATENAME(year, GETDATE())
SELECT @D4=DATENAME(hour, GETDATE())
SELECT @D5=DATENAME(minute, GETDATE())

set @trace_name='c:\trace_'+@d1+'_'+@d2+'_'+@d3+'_'+@d4+'_'+@d5+'_'
print @trace_name

--set @trace_name = 'c:\trace_'+@trace_date+'.trc'
--print @trace_name
exec sp_trace_create @TraceIdOut OUTPUT,6, @trace_name
PRINT @TraceIdOut

declare @On bit
SET @On = 1
exec sp_trace_setevent @TraceIdOut, 14, 6, @On
exec sp_trace_setevent @TraceIdOut, 14, 7, @On
exec sp_trace_setevent @TraceIdOut, 14, 8, @On
exec sp_trace_setevent @TraceIdOut, 14, 9, @On
exec sp_trace_setevent @TraceIdOut, 14, 10, @On
exec sp_trace_setevent @TraceIdOut, 15, 6, @On
exec sp_trace_setevent @TraceIdOut, 15, 7, @On
exec sp_trace_setevent @TraceIdOut, 15, 8, @On
exec sp_trace_setevent @TraceIdOut, 15, 9, @On
exec sp_trace_setevent @TraceIdOut, 15, 10, @On
exec sp_trace_setevent @TraceIdOut, 20, 6, @On
exec sp_trace_setevent @TraceIdOut, 20, 7, @On
exec sp_trace_setevent @TraceIdOut, 20, 8, @On
exec sp_trace_setevent @TraceIdOut, 20, 9, @On
exec sp_trace_setevent @TraceIdOut, 20, 10, @On


exec sp_trace_setevent @TraceIdOut, 104, 1, @On
exec sp_trace_setevent @TraceIdOut, 104, 3, @On
exec sp_trace_setevent @TraceIdOut, 104, 6, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 10, @On
exec sp_trace_setevent @TraceIdOut, 104, 11, @On
exec sp_trace_setevent @TraceIdOut, 104, 14, @On
exec sp_trace_setevent @TraceIdOut, 104, 22, @On
exec sp_trace_setevent @TraceIdOut, 104, 26, @On
exec sp_trace_setevent @TraceIdOut, 104, 35, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On

exec sp_trace_setevent @TraceIdOut, 107, 1, @On
exec sp_trace_setevent @TraceIdOut, 107, 3, @On
exec sp_trace_setevent @TraceIdOut, 107, 6, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 10, @On
exec sp_trace_setevent @TraceIdOut, 107, 11, @On
exec sp_trace_setevent @TraceIdOut, 107, 14, @On
exec sp_trace_setevent @TraceIdOut, 107, 22, @On
exec sp_trace_setevent @TraceIdOut, 107, 26, @On
exec sp_trace_setevent @TraceIdOut, 107, 35, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On

exec sp_trace_setevent @TraceIdOut, 106, 1, @On
exec sp_trace_setevent @TraceIdOut, 106, 3, @On
exec sp_trace_setevent @TraceIdOut, 106, 6, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 10, @On
exec sp_trace_setevent @TraceIdOut, 106, 11, @On
exec sp_trace_setevent @TraceIdOut, 106, 14, @On
exec sp_trace_setevent @TraceIdOut, 106, 22, @On
exec sp_trace_setevent @TraceIdOut, 106, 26, @On
exec sp_trace_setevent @TraceIdOut, 106, 35, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On


exec sp_trace_setevent @TraceIdOut, 105, 1, @On
exec sp_trace_setevent @TraceIdOut, 105, 3, @On
exec sp_trace_setevent @TraceIdOut, 105, 6, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 10, @On
exec sp_trace_setevent @TraceIdOut, 105, 11, @On
exec sp_trace_setevent @TraceIdOut, 105, 14, @On
exec sp_trace_setevent @TraceIdOut, 105, 22, @On
exec sp_trace_setevent @TraceIdOut, 105, 26, @On
exec sp_trace_setevent @TraceIdOut, 105, 35, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On

/*adding the SP to execute at SQl Server startup */
exec sp_procoption N'audit_trace', 'startup', 'on'

Step 2 and 3 will be same as mentioned above in the begening .

Disadvantage
-------------
Simple ..Its resource consuming .Do not add a lot of columns in the trace .Do specifically what you want to audit.
It entirely depends what all columns you are auditing.
You need to keep the instance under testing phase and monitor the resource consumption due to tracing .
Clear the client that we need to make sure that we have fast disks ,more/faster CPUs, IO processing capabbilities and enough RAM in case they need to do extensive auditing (if there are performance issues).

Using WMI and SQL Agent to fire low memory threshold alert ...

This will work perfectly ....The only thing I wanted to add to the table was when it alerts you it should also fill the column with available MBytes so that you know how much memory was available .......But after trying it for 2 days , I realized that the class through which I am checking another class (Perfmon >> memory >> Available Mbytes) does not have a column for this.I am using "_instance modification" class .May be its due to this that the alert is fired but the job fails when it inserts the availableMbytes ...because this column is not in _instancemodificationevent class...the error number also suggests that .

By the way this one will only alert if your RAM is > 256 every 10 seconds ....this is because I wanted to test it ....you need to modify it to < 256 and every 300 seconds ...so that you get alert every 5 mins or whatever you decide ....


/*******************************************************************************************
* This script will create an Alert to Monitor Physical RAM reaching a low threshold.
* The alert will run a job and the job will enter data in a table.
*******************************************************************************************/

/* Step 1: creating the table to capture the Event information */

USE Master
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[memory]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[memory]
GO

CREATE TABLE [dbo].[memory] (
[PostTime] [datetime] NOT NULL default (getdate()) ,
[computerName] sql_variant Not Null ,
[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
[Flag] [int] NOT NULL CONSTRAINT [DF_MEMORY_Flag] DEFAULT ((0)),
) ON [PRIMARY]
GO

CREATE INDEX [Memory_IDX01] ON [dbo].[memory]([recordid]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO

/*Step 2 : Creating the Job that will enter values into the table we created above*/
/*Service account and sql operator option are optional*/

USE [msdb]
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture memory Event')
EXEC msdb.dbo.sp_delete_job @job_name = N'Capture Memory Event', @delete_unused_schedule=1

GO
--DECLARE @ServiceAccount varchar(128)
--SET @ServiceAccount = N''
--DECLARE @SQLOperator varchar(128)
--SET @SQLOperator = N''

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Capture Memory Event',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job for responding to memory events',
@category_name=N'[Uncategorized (Local)]',
--@owner_login_name=@ServiceAccount,
--@notify_email_operator_name=@SQLOperator,
@job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/*Step 3: Insert graph into LogEvents*/


EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert data into LogEvents',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
declare @@server sql_variant
select @@server =serverproperty (''machinename'')

INSERT INTO memory (
PostTime,
Computername
)

VALUES (
GETDATE(),
@@server)
',
@database_name=N'master',
@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

/*Creating the alert and associating it with the Job to be fired */

USE [msdb]
GO

IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to Memory_event')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to memory_event'

GO

DECLARE @server_namespace varchar(255)
SET @server_namespace = N'\\.\root\Cimv2\'

EXEC msdb.dbo.sp_add_alert @name=N'Respond to memory_event',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'[Uncategorized]',
@wmi_namespace=N'\\.\root\Cimv2',
@wmi_query=N'SELECT * FROM __InstanceModificationEvent WITHIN 10 WHERE TargetInstance ISA ''Win32_PerfFormattedData_PerfOS_Memory'' AND TargetInstance.AvailableBytes > 256',
@job_name='Capture memory Event' ;

--EXEC msdb.dbo.sp_add_notification @alert_name=N'Respond to memory_event', @operator_name=N'Test', @notification_method = 1
--GO

--/* Step 5: Create a stored proc for sending the [Create_user] information as .CSV file */

--Create proc [dbo].[Deadlock_rpt]
--as
--DECLARE @SQL varchar(Respond to memory_event2000)
--DECLARE @date varchar (2000)
--DECLARE @File varchar(1000)
--select @date= convert(date,GETDATE())
--SET @SQL = 'select * from [Create_user] where flag = 0'
--SET @File = '[Create_user] report'+@date+'.csv'

--EXECUTE msdb.dbo.sp_send_dbmail
--@profile_name = 'test',
--@recipients = 'your email.com',
--@subject = 'low memory threshold reached...',
--@body = '***URGENT***Attached please find the low memory threshold report',
--@query =@SQL ,
--@attach_query_result_as_file = 1,
--@query_attachment_filename = @file,
--@query_result_header = 1,
--@query_result_separator = ' ',
--@query_result_no_padding = 1,
--@query_result_width = 32767


--/* Step 6: Changing the flag to 1 so that next time this information is not sent*/
--update dbo.[Create_user] set flag = 1 where flag = 0
--go

Printing DBCC inputbuffer for any SPID > 50

It helps in many cases .For example if SPIDS are involved in blocking .and we are doing inputbuffer one by one.
For blocking you can just add : and blocked !=0 in the sysprocesses query
Similarly you can modify it for waittype,waittime, CPU etc (as per your need)

--Input buffers
PRINT 'Input buffer SPIDs'

declare @spid smallint
declare @i_buff_string char(30)
set nocount on
declare bufCursor CURSOR FOR SELECT spid from master.dbo.sysprocesses where spid > 50
FOR READ ONLY
open bufCursor
fetch next from bufCursor into @spid
while (@@fetch_status <> -1)
begin
SET @i_buff_string = ('DBCC INPUTBUFFER (' + convert(char(6),@spid) +')')
PRINT '-> '+@i_buff_string
exec (@i_buff_string)
PRINT ''
fetch next from bufCursor into @spid
end
close bufCursor
deallocate bufCursor

Why do we need the Non-clustered Index

I was asked this question by an SME , So I thought let me pass on if it makes sense .

Lets create a table test which has a few columns from Person.address table (Adventureworks database)
select addressid,addressline1,addressline2,postalcode,stateprovinceid into test from Person.Address

On this table I created clustered index on AddressID and stateprovinceID
CREATE UNIQUE CLUSTERED INDEX [combined index] ON [dbo].[testo]
(
[addressid] ASC,
[stateprovinceid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Another non-clustered index was created with include option
CREATE NONCLUSTERED INDEX [provinceID] ON [dbo].[testo]
(
[postalcode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


*** BUT I WILL DISABLE THE INDEX ********

Now I ran a few queries:
-----------------------------------
select * from test where postalcode >'98501'



select * from test where postalcode >'98501' order by stateprovinceid



select postalcode from testo where postalcode >'98501' order by stateprovinceid




select addressline1,addressline2 from testo where postalcode >'98501' order by stateprovinceid



Notice that In all the cases above we have the clustered index Scan and not a simple SCAN .
So the clustered index is being used .And since Its visible that a clustered index is being used there is no need of any other index .Why ............because the data itself lies in the clustered index and we can not improve the plan anymore.

Lets see why a scan would be used
Your Data is sorted on the bases of 2 columns in the clustered index .So any query will use that Index because the data lies there .So in worst to worst case you will have a Clustered Index Scan (Better than a Index scan [heap scan]).

But why Scan and not Seek
Because the columns you are using in the where clause or the columns you are looking for as an output have no index or you can say they are not indexed (indirectly , working like a
heap).So , You will do a scan for those Rows .But since its ordered already , So it will be a clustered Scan.

Now you enable the non-clustered index and run few queries

select postalcode from testo where postalcode >'98501'


It did an index seek .remember it is not a Clustered index seek like below

select addressid from testo where addressid < 100



The reason is that In the earlier case a non-cluseterd index was used to find out the postalcode very quickly but since the data was in the clustered index it needed to point to that Leaf and hence some more efforts .But these efforts were far less than Clustered index scan .

That is the reason we need to Non-clustered index .TO reduce the ROW Lookups or Bookmark lookups.which are the pointers to the clustered index.

Happy learning!!

xp_sendmail: failed with mail error 0x80040111

Hi guys,
Its been long since i posted any new stuff ...so far I've posted nothing related SQL 2000 but i thought this one is needed .

We recently faced a SQL Mail issue on WIN 2k Server .Anyone who has worked on SQL Mail issue in the past would be knowing how irritating this issue can be sometimes.But its actually our dis-interest that makes this issue difficult.Somehow we don't like such issues :) ...Since we resolved this issue and went through different permutations and combinations I would like to discuss this with you all so that in future we can save some time and resources on the same issue.This includes

-> Some very basic understanding on SQL mail .
-> What you should do/do not do when SQL Mail issue comes up.

Brief understanding on SQL Mail
--------------------------------
There are two ways you can send mails in SQL Server 2000 (Not talking about 2005 or 2008).

-> SQL Agent Mail
-> SQL Mail .

Yes , you heard it right .SQL Agent uses its own Extended M(ail)API session where as SQL Mail uses its own .However,Both SQL Mail and SQL Agent Mail connect with Microsoft Exchange Server, Microsoft Windows NT® Mail, or a Post Office Protocol 3 (POP3) server using Mail Profile. It is possible to use different profiles for SQL Mail and SQL Agent Mail at the same time.
Extended MAPI is nothing but a better version over Simple MAPI .Anyway, we don't have to go in this direction .

The SPs that we use in SQL Mail are :

xp_startmail <-- used to start the mail session
xp_sendmail <-- To send mail .This invokes sp_processmail.
xp_findnextmsg <--sp_processmail uses this SP
xp_readmail <-- used by sp_processmail
xp_deletemail<--deletes the message.
sp_processmail <-- processses the mail
xp_stopmail<-- Stops the mail session

We will concentrate on First two and the last one in the list above.You can read about these SPs under : http://msdn.microsoft.com/en-us/library/ms186841.aspx?PHPSESSID=lm71lj7i6gj5fjtebtb2srl4n3
This link is for sp_findnextmsg but has link to all other SPs and XPs.


The Issue that we faced.
-------------------------
One of the job was not sending the mail after completion .SQL Mail was connecting to Exchange server successfully before .
Outlook 2000 client was installed on the Win 2K server (and was working fine under SQL Server service account).
Although we concentrated mostly troubleshooting SQLMail it was SQL Agent mail issue actually .
But Since troubleshooting steps are same for both , we resolved the issue .

TroubleShooting done

-> Logged in to the Server > opened the QA and ran sp_stopmail .Success
-> Sp_startmail ....Took 10 mins to start and with error.
-> The error was :
Server: Msg 18025, Level 16, State 1, Line 0
xp_startmail: failed with mail error 0x80040111
-> We tried to send the test mail using xp_sendmail
xp_sendmail 'abhay.chaudhary@in.ibm.com','test'
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80040111
-> Assuming that the Profile might be corrupt we created nwe profile through control panel >> Mail option .
-> To our surprise we could not see that profile in the drop down list of both SQL Mail and Agent properties.
-> So we failed there as well .We have the new profile , but we cant use it .Another Big question ? WHY ?
-> Agent logs were showing this :[260] Unable to start mail session (reason: Unable to logon (reason: Unable to get the default MAPI Message Store due to MAPI
error 273: The information store could not be opened); check the mail profile and/or the SQLSERVERAGENT service startup account)
-> After wasting time on 18025 we concentrated on Error 0x80040111
-> Found KB 238119 (List of Extended MAPI numeric result codes)
-> found that the error means : MAPI_E_LOGON_FAILED
-> Now we were clear that this is an authentication issue.
-> SQL Agent mail was not able to talk to Exchange Server.
-> The exchange Engineer cleared that since both the servers can ping each other and we can use outlook web using the same account under Exchange is running , there is no isues with
Exchange Mail box .
-> He was pretty convincing.
-> Then we found that (Actually we already knew that )in the SQL Mail configuration it was mentioned that we need to make sure that in we are logged in to the server using the SQL Server
service account.
-> That is what we have not done and were not able to do actually (Since begening) because of some permission issues on that account while logging on to the Win 2k Server.
-> Since rest all trials were failing , we decided to force ourselves for one last time in resolving this issue after loggin gon tho the server using SQL Server domain account .
-> The Intel team helped us in doing that .
-> We logged on to the server >> created a new profile >> Changed the SQL Agent setting to point to that profile (which was now visible..wow).
-> Started the mail session using xp_startmail.
SQL Mail session started.
-> Sent a test mail : xp_sendmail 'abhay.chaudhary@in.ibm.com','test'
Mail sent.
-> That resolved the issue.

Do's to solve this issue
--------------------------
-> Ping the exchange server or the POP3 Server and make sure its a success.
-> Make sure that the Domain account for SQL Server(including agent) , Exchange and OS are same .
-> Stop the mail session first as it might be Stuck.
-> Start the mail session .If it works , fine .
-> If not then read the MAPI error and match it with the KB 238199 to understand the root cause .
-> In one of the situations when I was testing it on Lotus Notes to POP 3 account on Gmail I got 8004011d which means MAPI_E_FAILONEPROVIDER (due to slow network)
-> Check the Agent logs and make sure MAPI32.dll exists (Using MAPI32.DLL from C:\WINDOWS\SYSTEM32 (version 1.0.2536.0))
-> Make sure the mapistub.dll exists in C:\WINDOWS\SYSTEM32 folder
-> You can also test you MAPI profile : xp_test_mapi_profile 'hello'
-> Create the new profile and see if that helps .If it does it means that the previous profile was corrupt(We did not test this in our case and created a new profile straightaway).

Don'ts
---------
Never try to resolve this issue without logging on to the server with SQL Server service account .

Hope this helps .
let me know your comments ..