MS SQL Server 2005 SP4 Installation failure // Error #1406

While performing SQL Server 2005 SP4 patching, I had recently faced the following error message and it immediately
prompted me to verify the permissions (Account already had full privileges on data/system drives) granted to the account used for this patching purpose.

Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB2463332_sqlrun_sql.msp.log
Error Number              : 1406
Error Description         : MSP Error: 1406  Could not write value  to key \Software\Classes\.pre.   Verify that you have sufficient access to that key, or contact your support personnel

While I had ensured that all of the below per-requisites were covered prior to the start of this patching activity, I continued to receive errors (Could not write value  to key \….\.   Verify that you have sufficient access to that key, or contact your support personnel)
1) Presence of Built-in Administrator in SQL Server.
2) Complete access on windows file directory, where system DB’s are hosted.
3) Administrator privileges on Windows account.

In order to set the registry key permission to allow full control, I followed this-
1.  On the Windows Task bar, click Start…Run.
2.  Type “regedit” (without the quotes) and click OK.
3.  Browse to the specified registry key.
4.  Right click the key and select “Permissions”.
5.  In the Group or User names field, select the user installing the program.  Be sure the user has administrative rights.
6.  Under the Permissions field, make sure “Full Control” and “Read” are both set to “Allow”.
7.  Click OK and attempt the operation again.

It was all in vain. So, what was preventing it from progressing?
Answer was right in front of my screen:  Anti-Virus 🙂

I tried to disable the first 3 items on the screen below and then disabled the “On-Access Scanner”.
Started the installation process once again, and boom… It went absolutely fine and finally, the server is now patched with 2005 SP4.
Anti-Virus

The problem was the anti-virus was blocking the process of writing into the classes.
Disabling it made my work done in a swift manner!

Hope this helps.

Cheers!
Aman Kharbanda

 

 

Advertisements

Error attaching a database (.mdf file) to SQL Server

In midst of new DB server build and application testing, lately, we had received a request from application folks to fall back to last night backup set which had already been moved to Tape.

Seeking this as an urgent request, Wintel Support guy accidentally copied the datafiles (.mdf/.ldf) instead of the required backup set and we were asked to create a new testing DB using these datafiles.

Now, to start with the DB process, always ensure that the service account used to run SQL Server services has full access/control on the newly copied data files (There is a strong possibility that these files are copied to a new location, wherein your domain authenticated ID doesn’t have complete privileges and it may result in an error).

Having covered this prospect, we started with the attach DB process and encountered the following error-
2013-09-24 12:18:40.840 spid61       Error: 3415, Severity: 16, State: 3.
2013-09-24 12:18:40.840 spid61       Database ‘*******’ cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.

As per the current setup and scenario, both Source and Target Servers were running with same version/edition/service packs, so the error message looked a bit strange (Database cannot be upgraded).

Having ensured that permissions were in place and files were accessible, I tried to rename the physical files and it worked smoothly!

Quite strange, isn’t it? 🙂

Trick was to rename the physical files, and ensure that SQL Server service account had full control/access on the data files.

Hope this helps.

Cheers!!
Aman Kharbanda

Diagnosing Database Backup and Restore Issues

Image

Taking about the issues in DB backup and restoration tasks, we have several ways to get the errors/alerts and warning messages by either looking at SQL Server Error log or Job Output or in the event viewer logs. At times, we might not get complete information or details, in order to troubleshoot the actual cause of problem and we are left in a dizzy without any real solution.

There are a few Trace flags in SQL Server, which basically gives a detailed information regarding File Creation, Padding and much more related Info while you are taking a Backup of your Database.

Now lets see, what all details are logged in the SQL Server error log while I start a transaction log backup after enabling required trace flags-

— How to enable the trace flags globally (No need to restart SQL Server services here) —
dbcc traceon (3004,3014,3605,-1)

Excerpts from SQL Server error log-
2013-09-13 15:00:03.030 spid74       BackupLog: database Test
2013-09-13 15:00:03.070 spid74       BackupLog: SafeCheckpoint done
2013-09-13 15:00:03.160 spid74       Backup: Streams open
2013-09-13 15:00:03.160 spid74       BackupLog: Work estimate done
2013-09-13 15:00:03.290 spid74       BackupLog: Configuration section done
2013-09-13 15:00:03.290 spid74       BackupLog: Family(0) StreamSize=0x0
2013-09-13 15:00:03.310 spid74       BackupLog: Family(0) StreamSize=0x0
2013-09-13 15:00:03.320 spid74       BackupLog: Family(0) StreamSize=0x0
2013-09-13 15:00:03.330 spid74       BackupLog: Family(0) StreamSize=0x0
2013-09-13 15:00:03.340 spid74       BackupLog: Log files done
2013-09-13 15:00:03.380 spid74       BackupLog: Final configuration done
2013-09-13 15:00:03.410 spid74       BackupLog: MBC done
2013-09-13 15:00:03.470 spid74       BackupLog: Writing history records
2013-09-13 15:00:03.470 Backup       Log was backed up. Database: Test, creation date(time): 2013/07/05(18:13:35), first LSN: 87:6107:1, last LSN: 87:6107:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘D:\DB_Backup\Test\Test_backup_201309131500.trn
2013-09-13 15:00:03.480 spid74       Writing backup history records
2013-09-13 15:00:03.640 spid74       BackupLog: Finished

This will surely help you to get more detailed messages whenever DB backup runs, but it can prove to be an overhead, especially when excessive messages/events will be logged.
Say you have 20 odd databases on the server, and with blessing of these trace flags your entire error log will be filled by successful backup completion events.

You’ve to define all kinds of filters in your logs to exclude the junk and look what exactly you are looking for.

Trace 3226 will come handy in such situations.

All the failed backup events will be successfully logged in your SQL Server error log file.

Hope this helps.

Cheers!
Aman Kharbanda
(https://sqlserverposts.wordpress.com)

 

Not able to send mails via SQL Server DB Mail Utility

Recently, we faced a issue where mails were not getting delivered to the recipients using the SQL Server DB Mail Utility. Relevant parameters were cross-checked and everything was in place.
Mailing profile, account was rectified; SMTP Server IP, Port and recipient’s mail address was correct and sysmail_server table was also checked to be fine.

DB Mail

So, what was the problem?

In event viewer, following error messages were getting logged –
1) The read on the database failed. Reason: The EXECUTE permission was denied on the object ‘sp_readrequest’, database ‘msdb’, schema ‘dbo’.
Procedure: sp_readrequest
Server: *************
State: 5
Source: .Net SqlClient Data Provider
ErrorCode: -2146232060
Message: The EXECUTE permission was denied on the object ‘sp_readrequest’, database ‘msdb’, schema ‘dbo’.
2) The EXECUTE permission was denied on the object ‘sysmail_logmailevent_sp’, database ‘msdb’, schema ‘dbo’.

Suspecting it to be a permission issue (with Error 5 – Access Denied), the domain authenticated ID which was used to login to SQL Server was assigned full permission on all the mentioned objects within the MSDB database.

But, the issue was not yet solved and we continued to receive the above mentioned errors.

Then, we checked the Service Account configured for SQL Server services.
Here was the catch to this problem.

SQL Server was running with Local System, while Agent service was configured with a domain authenticated ID, which was not having sufficient permissions on MSDB database.

Changing the service account of SQL Server Agent to ‘Local System’ resolved the problem and mails were now successfully getting delivered to the recipients and Group Distribution Lists.

Hope this helps.

Cheers!
Aman Kharbanda