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!!

I am running the script to de-fragment indexes, but my index is still fragmented. Why??

I don't know about you guys but I have seen this type of situation so many times where no matter how many times you run your script.At the end, You will see that fragmentation has not been reduced. The one situation could be the obvious one that "the table is a HEAP", means there is no clustered index on that table.

The second reason could be your index is very small. In general, Fragmentation on small tables is often not controllable. As the pages of small indexes are stored on mixed extents. As we all knew that mixed extents are shared by up to eight objects so the fragmentation in a small index might not be reduced after re-organizing or rebuilding the indexes.


I have copied the below lines from Brent Ozar Blog which explains the case in better way:

In the case of our index fragmentation numbers, one of the source data fields is Page Count – the number of pages that an object has.  Size matters with fragmentation: small objects with only a handful of pages may appear to have very high fragmentation numbers, but they can’t actually be de-fragmented.  There’s only so much de-fragmentation you can do when a table only has three pages.  I’ve actually been on support escalation calls where customers demand to know why a defrag job doesn’t reduce all types of fragmentation to absolute zero, even for tables with just one page.

Microsoft’s best practices on SQL Server 2000 index defragmentation notes that:

“Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages).”

With that in mind, I added a Page Count Group column and calculated it with a formula:

=IF(Table1[[#This Row],[page_count]]>50000,"Large",(IF(Table1[[#This Row],[page_count]]<10000,"Small","Medium")))

Thanks!!

Error "Page could not be moved because it is a work table page Cannot move all contents of file " During DBCC Shrink.

Today morning, I was assigned a task to remove data files from temp db.As On one of my Server having four datafiles in TempDB. We were doing some R&D so we needed to remove three datafiles from TempDB. I thought its the easiest job to do. All we have to do is Shrink and then remove the file.

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev3' , EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev3]
GO

Before this, every time it was working for me.But today when I was doing this, I got a surprising error. The Error was like "

=================================================================
DBCC SHRINKFILE: Page 4:209 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 1"

Cannot move all contents of file "tempdev3" to other places to complete the emptyfile operation.
====================================================================
As I got this error first time so, I searched the same on google and found some useful links. Which says that If there are some persisted tables in Temp DB and reside on the file you are trying to remove, it will not be able to Shrink or Remove. 

So, all we can do is that remove those tables from temp database and shrink it again. But now the problem is how we can see how many tables are there in my temporary database? On technet I got a useful query which will Find out all the tables which are in tempDB;

use tempdb
declare @id int
declare @dt smalldatetime
create table #spt_space_all
(
id int,
name varchar(500),
rows varchar(200) null,
reserved varchar(200) null,
data varchar(200) null,
index_size varchar(200)null,
unused varchar(200) null,
create_date smalldatetime null,
)
declare TMP_ITEMS CURSOR LOCAL FAST_FORWARD for
select id from sysobjects
where xtype='U'
open TMP_ITEMS
fetch next from TMP_ITEMS into @id
declare @pages int
WHILE @@FETCH_STATUS = 0
begin
create table #spt_space
(
id int,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null,
create_date smalldatetime null,
)
set nocount on
if @id is not null
set @dt = (select crdate from sysobjects where id=@id )
begin
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
update #spt_space set create_date=@dt 
end
insert into #spt_space_all
select name = @id,object_name(@id),
rows = convert(char(11), rows),
reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'KB'),create_date
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
drop table #spt_space
FETCH NEXT FROM TMP_ITEMS
INTO @id
end
CLOSE TMP_ITEMS
DEALLOCATE TMP_ITEMS
select 'DROP TABLE '+[name]+'
GO' from #spt_space_all where [name] not like '%#spt_space_all%'
drop table #spt_space_all
GO


Copy the result of query and run the same in Temp database. It will drop all those tables.

Now you can run below DBCC commands. Of-course it will slowdown the performance of other databases. So, Please think about it if you running it on production servers.

DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ( 'ALL')
GO

Now Run same query to remove the temp db data file:

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev3' , EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev3]
GO

It worked in my case. If not then repeat the steps you will be get what you want at the end :)

Thanks!!! 

Inconsistent Line Endings while opening .sql files in SSMS 2012.

Last month when I was trying to open a .sql file in SQL Server Management studio 2012 (SSMS) I was getting a warning window with message says "The line endings of the following file are not consistent. Do you want to normalize the line endings?" 

Actually this warning window appears when we load SQL script to SQL Server . It occurs when we load a file containing a mix of Windows standard and non-standard line-endings. Windows line-endings take the form of a pair of characters (Carriage Return plus Linefeed- CR  LF) whereas Unix line-endings use just a single (Linefeed) character. 

We can easily tell the difference by loading a file into Windows Notepad. Those files which use Unix-style line-endings will wrap in Notepad; those using Windows line-endings won’t.


To disable “Inconsistent line Endings”  warning window in SSMS 2012 please follow below steps:

1: Open SSMS 2012
2: Got to Tools > Options
3: Expand the Environment Tab
4: Select Documents> Uncheck the option for consistent line ending on load.

Below is the screen shot for same:

Thanks!!!

Error Unable to show XML. The following error happened. There is an unclosed literal string. Line 1, position 2097154.

Recently when I was working on optimization of a store procedure. I tried to see the execution plan of that procedure using DMV sys.dm_exec_query_plan by passing the Plan_handle of that store procedure. When I clicked on the query_plan hyperlink to display the graphical execution plan I got below error window saying that:

"Unable to show XML The following error happened. 
There is an unclosed literal string. Line 1, position 2097154."

I have faced this problem earlier on my system so I was aware that how to fix it. Actually this error comes in picture because of limitation set by default of 2 MB for XML data.  The XML Data for my procedure was more than 8 MB. We can check the size of our cached plan by using DMV sys.dm_exec_cached_plans. So, To see the graphical execution plan we have to increase the size of XML Data. We can follow  below steps to increase the XML data size:

From SSMS Go to Tools > Options >Query Results> SQL Server > Results to Grid 

Here we can see that only 2 MB data size is specified for XML data. Just increase it to 5 MB or unlimited according to your situation. Click OK.

Go back and execute the query. BINGO!!!!! Problem Solved. :)

Thanks!!

Stop Successful Backup Messages in Error Log File using Trace 3226.

The SQL Server error log captures information about what is occurring on our database server. It logs all the failures/errors that have occurred on our server. This error log file store information since the last start of SQL Server or since the last time we ran sp_cycle_errorlog(Closes the current error log file and cycles the error log extension numbers just like a server restart).

When we take backup of database in Sql Server, By default the successful Backup messages are get logged into SQL server error log file which can also be useful but if we have a lot of databases on our SQL server instance with frequent backups then we'll surely want to suppress all successful backup messages.

From SQL Server 2008, Microsoft converted an undocumented command (DBCC Trace(3226)) to documented category through which we can turn off the successful backup message.

Below is new trace flag is 3226 with its description which I have taken from Microsoft BOL:

Now lets create a sample database and then we will see how to stop successful backup messages from Error Log file:

Use Master
Go
IF NOT EXISTS ( SELECT name FROM sys.databases WHERE name = 'NewTraceDB' )
Create Database NewTraceDB
Go

Now lets take the backup of database:

Use NewTraceDB
Go
Backup database NewTraceDB to disk ='C:\vimal\NewTraceDB1.bak'
Go

Now open error log file. We can see that the backup messaged has been logged into this file.

Now lets turn on this New trace flag 3226. We are going to turn ON this trace only for this session. 
If we want to turn ON globally then we can use "-1" as DBCC TraceOn(3226,-1);

Now execute:

DBCC TRACEON (3226)
Go

Let take the backup again:

Use NewTraceDB
Go
Backup database NewTraceDB to disk ='C:\vimal\NewTraceDB2.bak'
Go

Now open open error log file once again and check for backup messages. No successful backup message are there after enabling trace 3226.

So, by using Trace Flag 3226 we can stop Stop Successful Backup Messages in Error Log File. Now lets off the trace flag.

DBCC TraceOff(3226)
Go

Now lets Clean the test environment

Use master
Go
Drop database NewTraceDB
Go

Trace flag 3226 works fine with SQL 2005/2008/2012/2014.

Thanks!!!!

What happens when we drop a database that is offline?

If you are thinking that dropping an offline database will give you an error then you are wrong. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. Those files have to be deleted manually from the respective windows folder. 

Lets create a sample database and then we'll see the same.

Use Master
Go
IF NOT EXISTS ( SELECT name FROM sys.databases WHERE name = 'TestOffline' )
Create Database TestOffline
Go

Now put the database in Offline Mode by executing below T-Sql Statement:

Alter Database TestOffline Set Offline
Go

We can Verify from below screen shot that this database is now offline

Now Lets drop this database:

Drop Database TestOffline
Go

"Command(s) completed successfully." Now lets open the location where mdf and ldf are stored on disk. We can see that files are still present there.

So, It's clear that if we drop a database that is offline then the files(.mdf and .ldf) will not be deleted from disk. 

Thanks!!!

How to get SQL server and instance name??

If you want to know the SQL Server server name and instance name then just execute the 
below query:

SELECT @@servername

If you want to know only the instance name of SQl server then execute:

SELECT SERVERPROPERTY ('InstanceName')      

                        

Thanks!!!                                                                                                                                     

How To Start SQL Server Instance in Single User Mode??

Under certain circumstances, we may have to start an instance of SQL Server in single-user mode by using the start up option -m. 

For example, if we want to change server configuration options or recover a damaged master database or other system database. Both actions require starting an instance of SQL Server in single-user mode.

When we start an instance of SQL Server in single-user mode, note the following:
Only one user can connect to the server.
The CHECKPOINT process is not executed. By default, it is executed automatically at startup.

Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it.

To open the SQL Server instance in SIngle user mode just Go to SQL Server Configuration Manager and click on  SQL Server Services i.e. By default it is selected.

Now Click on desired SQL Server instance to which you want to open in single user mode. Then right click go to properties. 

Click on Startup Parameter. (In sql server 2005/2008 click On the Advance tab enter param ‘-m;‘ before existing params in Startup Parameters box. Make sure that you entered semi-comma after -m.) 

Put -m in text box and then click on Add. Click on OK.

A warning window will appear. Click ok then restart the service.

Now lets connect it through SSMS. You can also connect it through command line.

Ooops!!! We got an error. Saying Sql server is in single user mode and only one administrator can connect at this time.

Ohhh yesss We just forgot to stop SQL Server Agent service. Now stop this service and try again to connect it through new query editor it will be successful. If you will connect through explorer then after that you will not be able to open a query window.

Once done remove -m from startup parameter and restart the service.

Thanks!!!

References: Microsoft BOL

Script to Get Database Backup Time and Backup location..

We can use below T-SQL script to find the Database Backup Time and Backup location:

SELECT B.database_name,B.user_name As [User],
F.physical_device_name,B.backup_start_date as Backup_time,
CASE B.[Type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType FROM msdb.dbo.backupset B
INNER JOIN msdb.dbo.backupmediafamily F ON B.media_set_id = F.media_set_id
--WHERE B.database_name = DB_NAME() --uncomment this line for a signle Database
ORDER BY backup_start_date DESC
GO

Thanks!!!

T-SQL Script to get SQL Server Start time and total Up time.

There may be a situation where you want to know the SQL Server service start date, time and total up time. I have written a simple T-Sql script using DMV to find the same, which is as below:

--======================================================================
SELECT 'Your Microsoft SQL server started On ' + 
CAST( (DATEname (WEEKDAY , sqlserver_start_time ))as varchar(12)) 
+' '+  CONVERT( varchar(20), sqlserver_start_time, 107)+' ' +'At'+ CONVERT( varchar(20), sqlserver_start_time, 108)
+' and is UP and Running From '+
CAST((DATEDIFF(MINUTE,sqlserver_start_time,GETDATE()))/60 AS VARCHAR(5)) + ' Hours and ' 
+RIGHT('0' + CAST(((DATEDIFF(MINUTE,sqlserver_start_time,GETDATE()))%60) AS VARCHAR(2)),2) + ' Minutes. '  
AS [SQl Server Start Time & Total UpTime] FROM sys.dm_os_sys_info
--==================================================================

You can also check temp DB Creation time for same as every time when we start sql server, temp db is created. 

Thanks!!!

When was SQL Server database Last used??

If we want to remove the databases from the server which are not getting used from a long time. In this case first we need the name of databases which are not used from a long time. We can use below script to find the same.  

SELECT DBName, MAX(LastUsedDate) LastUsedDate

FROM

    (SELECT

        DB_NAME(database_id) DBName

        , last_user_seek

        , last_user_scan

        , last_user_lookup

        , last_user_update

    FROM sys.dm_db_index_usage_stats) AS PvtTable

UNPIVOT 

    (LastUsedDate FOR last_user_access IN

        (last_user_seek

        , last_user_scan

        , last_user_lookup

        , last_user_update)

    ) AS UnpvtTable

GROUP BY DBName

ORDER BY LastUsedDate

Thanks!!

SOUNDEX and DIFFERENCE Function in SQL Server.

The SOUNDEX function converts a character string to a four-digit code for use in a comparison. Vowels are ignored in the comparison. Non-alphabetic characters are used to end the comparison. This function always returns some value.

Syntax:

SOUNDEX ( character_expression )

The following example displays the results of the SOUNDEX function for the similar character strings of smith and smythe. When character strings are similar, both strings have the same SOUNDEX codes.

SELECT SOUNDEX ('Kumar'), SOUNDEX ('Kumara');
GO

Output:
----- -----
K560  K560

(1 row(s) affected)

DIFFERENCE ()

The DIFFERENCE function compares the SOUNDEX values of two strings and evaluates the similarity between them, returning a value from 0 through 4, where 4 is the best match. 

Syntax:

DIFFERENCE ( character_expression , character_expression )

See the following examples:

SELECT DIFFERENCE('Vimal', 'Vimal');
GO
SELECT DIFFERENCE('Vimal', 'Kamal');
GO
SELECT DIFFERENCE('Roy', 'John');
GO

Thanks!!