Service Pack or Update for SQL Server 2008/R2/2012 fails without generating the setup log files!

I was recently working on an interesting setup issue with one of our Premier customers. The scenario was this – they were trying to patch their SQL Server 2008 R2 instance with SP1 (KB 2528583) and it was failing, but the log files were not getting generated fully. In fact, the path C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\log\ did not even have a folder with the latest timestamp. Interestingly, the log files were getting created on the D: drive location D:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\log\

From D:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\log\ folder we could only find Summary.txt and Detail.txt

Summary.txt

Overall summary:
  Final result:                  The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files.
  Exit code (Decimal):           1513770790
  Exit message:                  The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files.
  Start time:                    2012-11-18 00:54:46
  End time:                      2012-11-18 01:21:23
  Requested action:              Patch

Requested action:              Patch
Log with failure:              C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20121118_005431\MSSQLSERVER\SQLSysClrTypes_Cpu64_1.log
Exception help link:           http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.50.2500.0

Note the highlighted link and that log file were not present at all!

Detail.txt

Every feature that was trying get patched logs this error –> MSI Error 1622. 1622 stands for ERROR_INSTALL_LOG_FAILURE, which means the setup program is not able to log setup messages to the log file and hence failed.

Reference: http://msdn.microsoft.com/en-us/library/windows/desktop/aa368542(v=vs.85).aspx

2012-11-18 00:56:46 Slp: Target package: "g:\c6d70a3c67aacef9d9eff6019ffaf3\1033_ENU_LP\x64\setup\sqlsupport_msi\SqlSupport.msi"
2012-11-18 00:56:46 Slp: InstallPackage: MsiInstallProduct returned the result code 1622.
2012-11-18 00:56:46 Slp: Watson Bucket 1

2012-11-18 01:03:00 Slp: Target package: "g:\c6d70a3c67aacef9d9eff6019ffaf3\1033_ENU_LP\x64\setup\x64\sqlncli.msi"
2012-11-18 01:03:00 Slp: InstallPackage: MsiInstallProduct returned the result code 1622.
2012-11-18 01:03:00 Slp: Watson Bucket 5

I search for the keyword “at microsoft” to find exception stack and I see this error was raised at the end

2012-11-18 01:21:19 Slp: Result error code: 1622
2012-11-18 01:21:19 Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine
2012-11-18 01:21:19 Slp: Sco: Attempting to open registry subkey
2012-11-18 01:21:19 Slp: Sco: Attempting to open registry subkey Software\Microsoft\PCHealth\ErrorReporting\DW\Installed
2012-11-18 01:21:19 Slp: Sco: Attempting to get registry value DW0201
2012-11-18 01:21:21 Slp: Received request to add the following file to Watson reporting: C:\Users\EGP00\AppData\Local\Temp\2\tmpC720.tmp
2012-11-18 01:21:21 Slp: The following is an exception stack listing the exceptions in outermost to innermost order
2012-11-18 01:21:21 Slp: Inner exceptions are being indented
2012-11-18 01:21:21 Slp:
2012-11-18 01:21:21 Slp: Exception type: System.IO.DirectoryNotFoundException
2012-11-18 01:21:21 Slp:     Message:
2012-11-18 01:21:21 Slp:         Could not find a part of the path ‘C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20121118_005431\MSSQLSERVER’.
2012-11-18 01:21:21 Slp:     Stack:
2012-11-18 01:21:21 Slp:         at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
2012-11-18 01:21:21 Slp:         at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)
2012-11-18 01:21:21 Slp:         at System.IO.DirectoryInfo.GetFiles(String searchPattern, SearchOption searchOption)
2012-11-18 01:21:21 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.WatsonMsiBucket.ExecutePreCabTasks()
2012-11-18 01:21:21 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.WatsonService.ReportFailures()
2012-11-18 01:21:21 Slp:         at Microsoft.SqlServer.Chainer.Setup.Setup.Start()
2012-11-18 01:21:21 Slp:         at Microsoft.SqlServer.Chainer.Setup.Setup.Main()
2012-11-18 01:21:21 Slp: Watson Bucket 17

The above message is strange since the path clearly exists and some log files are getting created there, but no the one with the failure. From a admin perspective, while this error is being logged, if you look at the setup screen you will see it frozen at this stage (MsiTimingAction),

To make progress on this, I decided to troubleshoot the log file generation, and why the log files are created on the D: drive.

On a Windows system, the Program Files and Program Files (x86) paths are hard-coded and are set as part of the environment for each program. To open the current program files location, one can just go to Run and type in %programfiles% and hit enter. When I did this on my customer’s machine, it opened up the D:\Program Files path. Now, we’re getting somewhere.

These paths are also stored in the registry location HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion, as shown below.

On this machine it was pointing to the D: drive and hence the log files were going there. But some of the log files, were expecting this to always be the C:\ location and hence failing. This explains the log file location mystery.

The solution was fairly simple from here on, since the location of the ProgramFiles cannot be configured and changing it is not supported as per http://support.microsoft.com/kb/933700

I changed the location back to the default value by editing the registry under [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion]

ProgramFilesDir         –> C:\Program Files
ProgramFilesDir (x86) –> C:\Program Files (x86)

You can also make this change for a specific user profile by adding a User Environment Variable called “ProgramFiles” and setting its value to the path of the program files dir.

After changing the registry key value, I rebooted for good measure (though I think a logoff and logon should suffice). Now, when I go to Run and do %programfiles% it takes me to the C: drive as expected.

Re-ran the SP1 for SQL Server and it completed fine without issues and the log files were also getting created in the default path

Just thought I’d share this issue since its an example of how system misconfiguration or issues outside of SQL can have an impact of setup and patching.

SQL Server Complete Over View Details Query 

Query to find Daily backup Status, data,log,Total Size and other Useful information of all databases – SQL 2000/2005/2008/R2

Method :1Find out the database file,log file, total size and other useful information about a database on SQL Server 2000/2005/2008/R2 versions. The requirement is to use same query and it has to be executed across all version of SQL SERVER.  We can  also do this using dynamic SQL’s.

Here is the T-SQL which you can use to run on any version of SQL Server to fetch the result.

USE MASTER Go SELECT @@SERVERNAME Servername,@@VERSION SQLVersion, CONVERT(VARCHAR(25), DB.name) AS dbName, CONVERT(VARCHAR(25), suser_sname(sid)) AS dbOwner, filename AS FileName, CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status], (SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid !=0 ) AS DataFiles, (SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0) AS [Data MB], (SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS LogFiles, (SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS [Log MB], (SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)+(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB, convert(sysname,DatabasePropertyEx(name,'Updateability')) Updateability, convert(sysname,DatabasePropertyEx(name,'UserAccess')) UserAccess , convert(sysname,DatabasePropertyEx(name,'Recovery')) RecoveryModel , convert(sysname,DatabasePropertyEx(name,'Version')) Version , CASE cmptlevel WHEN 60 THEN '60 (SQL Server 6.0)' WHEN 65 THEN '65 (SQL Server 6.5)' WHEN 70 THEN '70 (SQL Server 7.0)' WHEN 80 THEN '80 (SQL Server 2000)' WHEN 90 THEN '90 (SQL Server 2005)' WHEN 100 THEN '100 (SQL Server 2008)' END AS [compatibility level], CONVERT(VARCHAR(20), crdate, 103) + ' ' + CONVERT(VARCHAR(20), crdate, 108) AS [Creation date], ISNULL((SELECT TOP 1 CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' + LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' + CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' + CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) + ' (' + CAST(DATEDIFF(second, BK.backup_start_date, BK.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)' FROM msdb.dbo.backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup] FROM sysdatabases DB ORDER BY dbName, [Last backup] DESC, NAME

OUTPUT:

Method :2

USE AdventureWorks GO -- Get Backup History for required database SELECT TOP 100 s.database_name, m.physical_device_name, CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken, s.backup_start_date, CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn, CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn, CASE s.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS BackupType, s.server_name, s.recovery_model FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id WHERE s.database_name = DB_NAME() -- Remove this line for all the database ORDER BY backup_start_date DESC, backup_finish_date GO

Method :3

Database Backup from different view points.To get the List/History/Log of all the Successful Backups:

SELECT    b.machine_name,   b.server_name,   b.database_name as DBName,   b.backup_start_date,   b.backup_finish_date,   CASE      WHEN b.[type] = 'D' THEN 'Database'     WHEN b.[type] = 'I' THEN 'Differential database'     WHEN b.[type] = 'L' THEN 'Log'     WHEN b.[type] = 'F' THEN 'File or filegroup'     WHEN b.[type] = 'G' THEN 'Differential file'     WHEN b.[type] = 'P' THEN 'Partial'     WHEN b.[type] = 'Q' THEN 'Differential partial'     ELSE b.[type]   END Backup_Type,       b.expiration_date,   b.[user_name],   DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,   b.recovery_model,   b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,   bf.physical_device_name as Location FROM    msdb.dbo.backupset AS b INNER JOIN msdb.dbo.backupmediafamily AS bf   ON b.media_set_id=bf.media_set_id ORDER BY    b.backup_start_date DESC GO

To get a list of all successful Backups taken till date for a particular Database:

DECLARE @DBName AS VARCHAR(100) = 'Your Database Name' SELECT    b.machine_name,   b.server_name,   b.database_name as DBName,   b.backup_start_date,   b.backup_finish_date,   CASE      WHEN b.[type] = 'D' THEN 'Database'     WHEN b.[type] = 'I' THEN 'Differential database'     WHEN b.[type] = 'L' THEN 'Log'     WHEN b.[type] = 'F' THEN 'File or filegroup'     WHEN b.[type] = 'G' THEN 'Differential file'     WHEN b.[type] = 'P' THEN 'Partial'     WHEN b.[type] = 'Q' THEN 'Differential partial'     ELSE b.[type]   END Backup_Type,   b.expiration_date,   b.[user_name],   DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,   b.recovery_model,   b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,   bf.physical_device_name as Location FROM    msdb.dbo.backupset AS b INNER JOIN msdb.dbo.backupmediafamily AS bf   ON b.media_set_id=bf.media_set_id WHERE   b.database_name = @DBName   ORDER BY    b.backup_start_date DESC GO

To get the List of all Databases which are not backed up till date:

SELECT   d.name [DB_Name] FROM   master.sys.databases d LEFT JOIN msdb.dbo.backupset b   ON b.database_name = d.name WHERE   d.database_id IS NULL To get the List of all Databases which are not backed up since last X days DECLARE @LastXDays AS INT = 1 ;WITH LatestBackupSet AS ( SELECT    b.database_name as DBName,   b.backup_start_date LastBackedUpOn,   b.[user_name],   ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk FROM    msdb.dbo.backupset AS b ) SELECT    lbs.DBName,   lbs.LastBackedUpOn,   lbs.[user_name] FROM    LatestBackupSet AS lbs WHERE   DATEDIFF(DAY,lbs.LastBackedUpOn ,CURRENT_TIMESTAMP) = @LastXDays     AND lbs.Rnk = 1 ORDER BY    lbs.DBName DESC GO

To get a list of the Latest successful backups of all Databases:

;WITH LatestBackupSet AS ( SELECT    b.machine_name,   b.server_name,   b.database_name as DBName,   b.backup_start_date,   b.backup_finish_date,   CASE      WHEN b.[type] = 'D' THEN 'Database'     WHEN b.[type] = 'I' THEN 'Differential database'     WHEN b.[type] = 'L' THEN 'Log'     WHEN b.[type] = 'F' THEN 'File or filegroup'     WHEN b.[type] = 'G' THEN 'Differential file'     WHEN b.[type] = 'P' THEN 'Partial'     WHEN b.[type] = 'Q' THEN 'Differential partial'     ELSE b.[type]   END Backup_Type,   b.expiration_date,   b.[user_name],   DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,   b.recovery_model,   b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,   bf.physical_device_name as Location,   ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk FROM    msdb.dbo.backupset AS b INNER JOIN msdb.dbo.backupmediafamily AS bf   ON b.media_set_id=bf.media_set_id ) SELECT    machine_name,   server_name,   DBName,   backup_start_date,   backup_finish_date,   Backup_Type,   expiration_date,   [user_name],   Total_Time_in_Minute,   recovery_model,   Total_Size_GB,   Location FROM    LatestBackupSet AS lbs WHERE   lbs.Rnk = 1 ORDER BY    lbs.DBName DESC GO

To get the Latest successful backup of a particular Database:

DECLARE @DBName AS VARCHAR(100) = 'Your Database Name' ;WITH LatestBackupSet AS ( SELECT    b.machine_name,   b.server_name,   b.database_name as DBName,   b.backup_start_date,   b.backup_finish_date,   CASE      WHEN b.[type] = 'D' THEN 'Database'     WHEN b.[type] = 'I' THEN 'Differential database'     WHEN b.[type] = 'L' THEN 'Log'     WHEN b.[type] = 'F' THEN 'File or filegroup'     WHEN b.[type] = 'G' THEN 'Differential file'     WHEN b.[type] = 'P' THEN 'Partial'     WHEN b.[type] = 'Q' THEN 'Differential partial'     ELSE b.[type]   END Backup_Type,   b.expiration_date,   b.[user_name],   DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,   b.recovery_model,   b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,   bf.physical_device_name as Location,   ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk FROM    msdb.dbo.backupset AS b INNER JOIN msdb.dbo.backupmediafamily AS bf   ON b.media_set_id=bf.media_set_id WHERE   b.database_name = @DBName   ) SELECT    machine_name,   server_name,   DBName,   backup_start_date,   backup_finish_date,   Backup_Type,   expiration_date,   [user_name],   Total_Time_in_Minute,   recovery_model,   Total_Size_GB,   Location FROM    LatestBackupSet AS lbs WHERE   lbs.Rnk = 1 ORDER BY    lbs.DBName DESC GO

To get a list of Databases that were backed-up and do not currently exist:

SELECT   DISTINCT b.database_name FROM   msdb.dbo.backupset b WHERE   DB_ID(b.database_name) IS NULL