Search

Monday, November 25, 2013

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.

Monday, November 11, 2013

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

Monday, November 4, 2013

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.