Error ".msi is missing" While Doing SQL Server Upgrade!!

As per MSDN
 "When you try to install a Microsoft SQL Server service pack or a cumulative update, you may encounter the following error messages, and these may indicate Windows Installer Cache problems. The Windows Installer Cache, located in c:\windows\installer folder, stores important files for applications installed using the Windows Installer technology and should not be deleted. If the installer cache has been compromised, you may not immediately see problems until you perform an action such as uninstall, repair, or update SQL Server. "

Below is the screen shot of error which appears on the screen when problem occurs.

There are KB articles available on MSDN sites for this error. The amount of time it takes to fix the error can vary from case to case. If only one or two files ae missing then you can fixed it quickly but what if there are more than 100 files are missing in that case it can be very time consuming.


Microsoft has a good KB article http://support.microsoft.com/kb/969052 which will explain the entire process of taking corrective actions to fix this problem, and there is a VB script which will tell you the details of files which are missing and it will provide you commands to take corrective action.

If you are in hurry and you don't want to read the whole article then below are the steps which you need to perform 

1. Copy the FindSQLInstalls.vbs script from the KB and place it under C:\Script

2. Open a command window(As Admin) to the directory to which you saved the script, and run the following command: 

Cscript FindSQLInstalls.vbs %computername%_sql_install_details.txt

This will create a .txt file,and it will have all the missing MSI errors.

3. Open the txt file to see the details it has on the missing msi files.To do this, search the text file for string patterns such as the following:

does not

!!!  

Near the above string you will see copy command with the msi details.Something like –

Copy “H:\SQLINSTALL\SQL2008StdRTM\x64\setup\sql_bids.msi” C:\windows\Installer\df2ee59.msi

You need to run the command(As Admin) and it will copy the files to the cached folder. you need to scroll down the txt file to see if there are multiple MSI files which are missing,and in that case you need to run the copy command multiple times.

4. Once you have completed running all the copy commands which were available in the txt file, try to re-run SP setup again and it will be successful.

Thanks For Reading This Blog Post!!

Change SQL Server Collation without rebuilding system databases In SQL Server

Last week we had received a change request where we need to change the collation level of System databases as one job was getting failed with error 

"Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation.”

We tried to change the collation level of database with 

"ALTER DATABASE [DBxxx] COLLATE New_Collation" 

But it was getting failed with error that you can not change the collation of system databases.

We did some google and found that we need to rebuild the system databases. But just before performing this activity we got to know that there is an option through which we can changed the collation level of database without rebuilding system databases. We did the same and the change was successful. 

It is undocumented startup parameter so please do it at your own risk.


Here is the steps to do the same:

1: Make backup of all the database and server objects
2: Detach all your user databases from the server.
3: Now Stop the SQL Server Service
4: Start the SQL Server Service using command Prompt. Go to the directory as below(Path can be different in you case).
If you have more than one instance of sql server then you need to specify the -s <instancename> parameter. But in case of single sql server instance you can use the below script.

For Single SQL Instance:
sqlservr -m -T4022 -T3659 -q"<New Collation Name>"

For more than one SQL Instance:
sqlservr -s instancename -m -T4022 -T3659 -q"collation-name" 

Now I am going to test the same on my local system server:

The default collation level on my server is "SQL_Latin1_General_CP1_CI_AS" . See the below screen shot for same. 

When we will try to change the collation level with alter command . It give throw below error.

I have taken backups of all system and user database. Also detached the user databases on this server. Now going to stop the SQL server service.

Now open cmd and run the below command:

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER1\MSSQL\Binn>> sqlservr -s mssqlserver1 -m -T4022 -T3659 -q"Japanese_BIN"

You will see below message:

Press ctrl+C to stop.

Now start the SQL server service and check the collation level again as below: 

It has been changed now. Bingoooo :)

Thanks!!

The index “[IndexName]” on table “[TableName]” cannot be reorganized because page level locking is disabled.

While looking at my laptop screen, I got new a email with subject "a maintenance job has been failed on a production server. Please look into it". When I opened that email the error description was like:

"The index “[IndexName]” on table “[TableName]” cannot be reorganized because page level locking is disabled."

The error description was more than enough to understand the actual issue. The error was due to the index reorganization job, while it was trying to de-fragment the index. But it was not able to do so because the index has the page level locking option disabled. So, the job failed.

By default, page level locking is enabled for indexes.As I did not face this kind of issue previously so I don't have idea how it got disabled. But will share soon. 

To fix the Job, I have enabled the page level locking option and the job ran successfully.

To find all the indexes in the database that have page level locking disabled, you can run the following query:


--List of Indexes that have page level locking disabled
SELECT OBJECT_NAME(i.object_id) as Table_Name
, name as IndexName
, allow_page_locks
FROM sys.indexes as i
WHERE ALLOW_PAGE_LOCKS = 0

Thanks!!

Query To Find Out Allocated SQL Server Memory & Total OS Memory Installed On Machine

In this short blog post, you will find a simple Query which will give you the Total OS memory of the box as well as the allocated SQL server memory. Although you can see the same with GUI option, by just right click on the instance and then properties. But, someday you will definitely need this query to get the result. As GUI is best in the case of one or two servers, but what if you want to see the result for more than 2500 servers. You can simply run the below query on your repository server to get the result.

SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB],

(SELECT value_in_use FROM sys.configurations WHERE name like '%max server memory (MB)%') as [SQL Allocated Memory_In_MB]

FROM [master].[sys].[dm_os_sys_memory]

Thanks!!