Script level upgrade for database master failed because upgrade step SSIS_hotfix_install.sql

After update windows including SQL 2012 SP2 unable to start SQL service/instance

We have SQL 2012 Ent 64 bit SP1

Window 2012 R2 Datacenter

After update windows including SQL 2012 SP2 unable to start SQL service/instance and following event log

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 945, state 2, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Solution:

Start SQL Server service with Trace Flag 902: 

Right click on the command prompt and run as Administrator

When named instance

>Net Start MSSQL$InstanceName /T902

Or when default instance:

>Net Start MSSQLSEVER /T902

Open SQL Server Management Studio, go to Availability Group and remove SSISDB from the availability databases

Stop SQL Server services:  

>Net Stop MSSQL$InstanceName or >Net Stop MSSQLSERVER when default instance

Start SQL server service from SQL Server configuration manager

Add SSISDB back to Availability Group 

SSRS not sorting correctly

SSRS not sorting correctly.

When you have issues and try modifying group properties and tablix sort property, and still the report doesn't take the sorting you select on the groups or the query.  You just need to right click on the report name on the Solution Explorer window and select View Code. 

This option will show you the xml version of the report where you can find the property SortExpressions

This entry will show up multiple times you just need to find the one that the report is using and change it to the field you need, save the xml version then close it to go back to the Designer mode.

I found the solution on the following blog which I recommend in case you're still having issues.

http://stackoverflow.com/questions/29515934/ssrs-not-sorting-correctly

Thanks.

How to upload/import multiple .trc files to a trace table in SQL Server

1. Bring each one of the trc files to the database 
SELECT * INTO trace_folder_file1 
FROM ::fn_trace_gettable('c:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQL\Log\log_1.trc', 5) 
SELECT * INTO trace_folder_file2 
FROM ::fn_trace_gettable('c:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQL\Log\log_2.trc', 5) 

2. Query the table to identify queries based on the information you need.
In this case it will bring all the queries that have something in the duration column 

SELECT * FROM ( 
SELECT * FROM [dbo].[trace_folder_file1] 
UNION ALL 
SELECT * FROM [dbo].[trace_folder_file1] ) 
AS DT 
WHERE [Duration] > 0

Database does not exist when configuring Log Shipping for a database in SQL Server 2005

When you are trying to configure a Log Shipping for a database in SQL Server 2005 and you get the error: 

Database does not exist as log shipping primary.

1. Validate the server name on both primary and secondary instance:

SELECT @@servername
EXEC sp_helpserver

2. If you are getting NULL as a result of the SELECT then add the server to the instance:

sp_addserver '\', local

3. If you get the error:

Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89
The server '\' already exists.

4. You should need to drop the server first and then add it again:

sp_dropserver '\'

5. And then add the server name:

sp_addserver '\', local

Finally try again running the Log Shipping configuration script or run it from the wizard. 

SQLCMD.EXE on SQL 2008 doesn’t work – HResult 0×2, Level 16, State 1

When you are using SQL Server Express 2008 w/ Advanced Services and get the following error trying to execute sqlcmd:

HResult 0×2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing
a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured
to allow remote connections. For more information see SQL Server Books Online.
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

1. Enable named pipes and TCP/IP on Network protocols
2. Restart the service
3. If you are still having problemas, change the named pipe to the following.
\\.\pipe\sql\query

SQL Server 2008 - SQL Server Management Studio

When you are using the table designer and trying to set a column as primary key you get an error saying "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made change to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created"

To solve the problem go to:
1. Tools menu option in SQL Server Management Studio
2. Options
3. Designers
4. Un-check the Prevent saving changes that require table re-creation

SQLServer 2005 Drop database before removing log shipping

When you have removed a database before cleaning up the log shipping configuration and getting the following error on the Application Event Viewer:

The log shipping primary database . has backup threshold of 60 minutes and has not performed a backup log operation for minutes. Check agent log and logshipping monitor information.

1. Get the primary and secondary database names and secondary servers from the following tables:

select * from msdb.dbo.log_shipping_primary_databases
select * from msdb.dbo.log_shipping_primary_secondaries

2. Run the following store procedures on the primary server

USE master
EXEC sp_delete_log_shipping_primary_database @database= ''
EXEC sp_delete_log_shipping_primary_secondary @primary_database='',@secondary_server='',@secondary_database ='secondarydbname'

3. If the secondary server and database are still active execute:
USE master
EXEC sp_delete_log_shipping_secondary_database @secondary_database=''
EXEC sp_delete_log_shipping_secondary_primary @primary_database='',@primary_server=''

4. Make sure it got deleted from the monitoring database

select * from msdb.dbo.log_shipping_monitor_primary