Detect torn pages in SQL Server

Use below query to find the torn pages in SQL Server 2005 or later versions. 
Suspect_pages table is located in MSDB database.

SELECT db_name(database_id) DatabaseName, file_id, page_id, last_update_date
FROM msdb.dbo.suspect_pages WHERE event_type = 3

The above query result will display torn page and that page located in which file and when torn page issues happened that date will show.

Disable all SQL Server Jobs

Use below script to disable all active Jobs in SQL Server:

BEGIN
  SET NoCount On
  DECLARE @JobName nVarChar(128), @Enabled int  
  SELECT Name, [Enabled] INTO #Temp_Jobs FROM msdb.dbo.sysjobs WHERE [Enabled] = 1  
  WHILE Exists(SELECT TOP 1 Name FROM #Temp_Jobs) 
  BEGIN
     SELECT TOP 1 @JobName = Name, @Enabled = [Enabled] FROM #Temp_Jobs
     PRINT ('Job Name:  ' + Cast(@JobName AS nVarChar) + ' , Status: ' + Cast(@Enabled AS VarChar))
     EXEC msdb.dbo.sp_update_job @Job_Name = @JobName , @Enabled = 0
DELETE FROM #Temp_Jobs WHERE Name = @JobName     
  END
  DROP TABLE #Temp_Jobs
  SET NoCount OFF
END

Stop and Resume Log Shipping in SQL Server

Method 1:

Follow below steps to stop the Log Shipping in SQL Server 2008:

1) Right click on LogShipping Primary Server Database.
2) Select the Transaction Log shipping option.
3) Click ... option under secondary server instances and databases Restore Tab from the window.
4) Select the General. 
5) Uncheck the Enable option.
6) Select the OK button.

Follow below steps to resume the Log Shipping in SQL Server 2008:

1) Right click on LogShipping Primary Server Database.
2) Select the Transaction Log shipping Option.
3) Click ... option under secondary server instances and databases Restore Tab from the window.
4) Selct the General. 
5) Check the Enable option.
6) Select the OK button.

SQL performance slowing down

I was working on a database since last couple of month. Suddenly it was slowing down day by day. I had not changes anything in the database. 

Then I checked the database properties and found that recovery model was set to Full. 
Full recovery model was not needed as I do not use incremental backup. 
So I set recovery model to Simple and my problem is solved.

Change Database compatibility level

You can change the database compatibility level through Management Studio. (Go to Database Properties - Option).

Here is the script to change the database compatibility level through SQL:

ALTER DATABASE <DB_Name> SET SINGLE_USER 
GO 
EXEC sp_dbcmptlevel <DB_Name>, 100; 
GO 
ALTER DATABASE <DB_Name> SET MULTI_USER 
GO

In above script change <DB_Name> with Database Name (you want to change the compatibility level).

Compatibility Level :

SQL Server 2008 : 100
SQL Server 2005 : 90
SQL Server 2000 : 80
SQL Server 7.0 : 70