Tag Archives: SQL Server 2008

“The SQL Server registry keys from a prior installation cannot be modified. To continue, see SQL Server Setup documentation about how to fix registry keys.”

Its been a long time that i wanted to blog about this error for the benefit of the DBA group and finally found time to blog this. 🙂

Error: “The SQL Server registry keys from a prior installation cannot be modified. To continue, see SQL Server Setup documentation about how to fix registry keys.”

error1

Synopsis:We received the above mentioned error while we were patching SQL server 2008 to SP4 on a server running Windows 2003. I tried out solutions provided in various posts on the internet i.e. gave explicit full permissions on registry keys, copied /replaced”FixSqlRegistryKey_x86.exe” (Path:C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Sqlserver2008\x64) from the SQL server setup but none of them helped to resolve the issue.

I referred the ‘Detail.txt’ log file which was generated in “setup bootstrap\log\” folder and found the following error:

error2

Solution: We followed the following procedure to resolve the issue and successfully patched the SQL Server 2008. Thanks to experts who suggested the workaround

a) Double click on the Service pack executable file and note down the temp path to which the service pack contents extracts to. In our case it was extracting to E drive[ Refer below screenshot]

SP_extract

b) The installation failed again with the above mentioned error and don’t close the window so that the above mentioned [refer step a)] temp location would still be accessible. [Refer below screenshot]

rules_validation

c) Go to the temp location [refer step a)] and rename “FixSqlRegistryKey_x86.exe” say something like “FixSqlRegistryKey_x86_old.exe” [Refer below screenshot]

3

d) Copy  “FixSqlRegistryKey_x64.exe” from the above mentioned temp location and paste it in same location. Rename “copy of FixSqlRegistryKey_x64.exe” to “FixSqlRegistryKey_x86.exe” . Click on “re-run” button on SQL server patching window and now the rules check validation would complete successfully [Refer below screenshot]  and continue with patching.

4

Happy Patching!!!

Advertisements

Leave a comment

Filed under SQL Server Patching

How to find Failed or Hung SQL Server Agent Jobs in the last 24 Hours

Run the below query to find failed/hung jobs in your SQL Server Instance:

 

 

SELECT sjh.server as 'Server Name' ,sj.name as 'Failed Job Name', sjh.message as 'Error Description', sjh.run_date, sjh.run_time as 'Job Run Time/Job Hung Time'
FROM msdb..sysjobhistory sjh
JOIN msdb..sysjobs sj on sjh.job_id = sj.job_id
JOIN (SELECT job_id, max(instance_id) maxinstanceid
FROM msdb..sysjobhistory
WHERE run_status NOT IN (1,4)
GROUP BY job_id) a ON sjh.job_id = a.job_id AND sjh.instance_id = a.maxinstanceid
WHERE    DATEDIFF(hh, CONVERT(datetime,CONVERT(VARCHAR(8),sjh.run_date),12), GETDATE()) <= 24
UNION ALL
select job.originating_server as 'Server Name', job.Name as 'Failed Job Name', 'Warning: Job Hung for More than 1 Day.' as 'Error Description',
CONVERT(char(10), activity.run_requested_Date,112) as run_date,
datediff(minute, activity.run_requested_Date, getdate()) as Elapsed
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity on (job.job_id = activity.job_id)
where run_Requested_date is not null
and stop_execution_date is null
and datediff(hour, activity.run_requested_Date, getdate()) >= 24

 

Output:

SQL-Job-Hung-Failure

NOTE: Ideally, you would want to know if a job is hung as soon as possible.

Hence, you can replace the underlined code above with the one below to reduce the time-interval for Hung Jobs from 1 day to 1 Hour:

datediff(minute, activity.run_requested_Date, getdate()) >= 60

Use a Power Shell script/Windows Scheduled Task to have this scheduled & run on multiple SQL Server Instances in a jiffy!

Leave a comment

Filed under sql server 2008, sql server 2012

Difference between a Primary Key and a Unique Constraint – DBA Perspective

I was preparing for an interview (hope my manager isn’t reading this yet) & found this wonderful link SQL Server DBA Interview Questions which had a great question on the difference between a Primary Key and a Unique Constraint.

That question brought in a thought about how this difference between a Primary Key and a Unique Constraint matters to a DBA? (Developer’s headache – not mine was my first opinion, however that quickly changed when I started exploring it.)

For DBAs like me who might NOT remember all the differences, this simple post by Shailendra Chauhan captures the essence of the Difference between Primary Key and Unique Key

Here is the DBA perspective that DBAs would hopefully benefit from {or pay the price if ignored 😉 }

Primary Key creates a Clustered Index internally, where Unique Constraint creates a “Unique, Non-Clustered” Index.

This means you can actually have multiple Unique Constraints in a single table. Simple yet useful to maintain Data Consistency.

For all those Science fans who live by “Seeing is believing”, here’s the screen preview:

Unique_1

Notice that I have created a Unique Constraint “IX_ID” on a Column “ID”. SQL Server has created a “Unique, Non-Clustered” Index with the same name.

Key Takeaway here would be to consider the above difference in how SQL server perceives your Primary key/Unique Constraint. Based on this solid understanding, you can make your database design better!

Cheerio! Hope this article was useful 🙂

PS: Please feel free to add onto your inputs/experience in the below comments section.

Leave a comment

Filed under Keys and Constraint, sql server 2005, sql server 2008, sql server 2012, Uncategorized