Could not redo log record - DB is SUSPECT Mode

We recently faced this issue after the maintenance window. The user database went to suspect mode and it was unable to bring online due to below error.
This case we rebuilt the logfile, to bring the DB online.

ERROR
Could not redo log record (1327929:29486:115), for transaction ID (1:1223281085), on page (1:1333024), database 'TestDB2 ' (database ID 8). Page: LSN = (1327558:113348:307), type = 2. Log: OpCode = 5, context 29, PrevPageLSN: (1327929:29486:18). Restore from a backup of the database, or repair the database.

Msg 3313, Level 21, State 2, Line 1

This case has only below options:

1. Restore the DB from the backup strategy.
2. Try DBCC REPAIR with REPAIR_FAST | REPAIR_REBUILD (No Data Loss)
3.  Final is REPAIR_ALLOW_DATA_LOSS

Alternate Solution:
===================

ALTER DATABASE TestDB2 REBUILD LOG ON (NAME=TestDB2_Log, FILENAME='D:\SQLLog\TestDB2_Log.ldf')
dbcc checkdb (testdb2)
ALTER DATABASE testdb2 SET MULTI_USER

Note: This approach might lead to data loss. Make sure we have enough backups in place.

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

Solution :

1. Go to  C:\Windows\System32\drivers\etc
2. Open HOSTS File in notepad (Run as Admin)
3. Add below lines:

127.0.0.1 localhost
127.0.0.1 mycomputername -- this will be computer name

4. Save and close the notepad.
5. Connect SQL Server


Source:

http://blog.sqlauthority.com/2012/09/06/sql-server-fix-error-cannot-connect-to-login-failed-the-login-is-from-an-untrusted-domain-and-cannot-be-used-with-windows-authentication-microsoft-sql-server-error-18452/

 

How to find PID

Here in the TSQL Script to find PID Key:

USE master
GO
EXEC xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft
SQL Server\80\Registration','CD_KEY'
GO

References:

http://blog.sqlauthority.com/2007/02/28/sql-server-t-sql-script-to-find-the-cd-key-from-registry/

https://gallery.technet.microsoft.com/scriptcenter/Get-SQL-Server-Product-4b5bf4f8

Upgrades from Earlier Versions to SQL Server 2014

We can find the supported upgrades to SQL Server 2014 from the below link:

https://msdn.microsoft.com/en-us/library/ms143393.aspx

SSRS Upgrade: 

• Upgrade: You upgrade the Reporting Services components on the servers and instances where they are currently installed. This is commonly called an “in place” upgrade. In-place upgrade is not supported from one mode of Reporting Services server to another. For example, you cannot upgrade a Native Mode report server to a SharePoint mode report server. You can migrate your report items from one mode to another. For more information, see the ‘Native to SharePoint Migration’ section later in this document.
  
• Migrate: You install and configure a new SharePoint environment, copy your report items and resources to the new environment, and configure the new environment to use existing content. A lower level form of migration is to copy the Reporting Services databases, configuration files, and if you are using SharePoint mode, the SharePoint content databases.

https://msdn.microsoft.com/en-us/library/ms143747%28v=sql.120%29.aspx

Well defined steps of SSRS to new Server:

http://www.mssqltips.com/sqlservertip/2692/migrating-sql-reporting-services-to-a-new-server-by-moving-the-reporting-services-databases/

In Place Upgrade Of SQL Server:

http://sqlmag.com/sql-server-2014/migrating-sql-server-2014

Known Issues:

http://www.sanssql.com/2013/11/upgrade-error-valid-database.html

Grant execute permission to all SP's in the DB

/*
Below code will grant execute permission to all SP's in the DB.
Using below script we can grant permission to all other objects/other permission
 in the DB to diffrent users.


*/
select name into #temp_sp_list from sys.objects where type ='P' order by 1

declare @sp_count int,@sp_name varchar(50)

--select top 1 @sp_name=name from #temp_sp_list
--print @sp_name

select @sp_count=count(*) from #temp_sp_list

while(@sp_count>0)
begin
select top 1 @sp_name=name from #temp_sp_list

EXEC ('grant execute on [' + @sp_name + '] to SBUReportStaging') -- Change the user name/permission;

delete from #temp_sp_list where name=@sp_name

select @sp_count=count(*) from #temp_sp_list

print @sp_count;
end

drop table #temp_sp_list


-- Thanks to Rakesh Rao :)

To stop multiple traces on server

	Here in the steps to stop the traces:_ Step 1: Find out what are all running select * FROM :: fn_trace_getinfo(default) Step 2: Stop them by passing trace id and status   sp_trace_setstatus 3, 0 go sp_trace_setstatus 3, 2
	Syntax sp_trace_setstatus [ @traceid = ] trace_id     , [ @status = ] status
	0 Stops the specified trace. 1 Starts the specified trace. 2 Closes the specified trace and deletes its definition from the server.

Replication _ IMP

Merge publication on a 64-bit SQL Server 2012 instance supports a maximum of 256 articles. Snapshot and transactional replication support up to 32,767 articles.

Merge publication on a 64-bit SQL Server 2012 instance supports a maximum of 246 columns in a table. Snapshot and transactional replication support up to 1,000 columns in a table.

Merge publication supports up to 1,024 bytes for a column used in a row filter. Snapshot and transactional replication support up to 8,000 bytes for a column used in a row filter.

Merge publication supports more than 30 unique indexes in a table. The unique index limit is determined by the number of articles and columns.