Rebuilding Indexes using the SSMS Database Maintenance Wizard

Usually I do all 3 job under one single task but with separate schedule for each of them:

 

Reorganize_Index

Rebuild_Index

Update_Statistics

 

Details can be found here:

https://www.red-gate.com/simple-talk/sql/database-administration/rebuilding-indexes-using-the-ssms-database-maintenance-wizard/

 

 

Advertisements

Update Statistics Manually. Re-indexing manually

DECLARE @tablename varchar(80),@shemaname varchar(80)
DECLARE @SQL AS NVARCHAR(200)
DECLARE TblName_cursor CURSOR FOR
SELECT t.name,s.name FROM sys.tables t join sys.schemas s
on s.schema_id=t.schema_id


OPEN TblName_cursor

FETCH NEXT FROM TblName_cursor
INTO @tablename,@shemaname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'UPDATE STATISTICS '+@shemaname+'.[' + @TableName + '] WITH FULLSCAN ' ---+ CONVERT(varchar(3), @sample) + ' PERCENT'
PRINT @SQL

EXEC sp_executesql @statement = @SQL

 FETCH NEXT FROM TblName_cursor
 INTO @tablename,@shemaname
END

CLOSE TblName_cursor
DEALLOCATE TblName_cursor

MS SQL add Update Statistics job (Schedule) to get better performance ( Reindexing )

/****** Object: Job [Update CRM Statistics] Script Date: 05/01/2017 09:43:42 ******/
 BEGIN TRANSACTION
 DECLARE @ReturnCode INT
 SELECT @ReturnCode = 0
 /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 05/01/2017 09:43:43 ******/
 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
 BEGIN
 EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
 EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Update CRM Statistics',
 @enabled=1,
 @notify_level_eventlog=3,
 @notify_level_email=0,
 @notify_level_netsend=0,
 @notify_level_page=0,
 @delete_level=0,
 @description=N'No description available.',
 @category_name=N'[Uncategorized (Local)]',
 @owner_login_name=N'xxxx', @job_id = @jobId OUTPUT

/*xxxx = login username for the database which has dbaadmin permission*/
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 /****** Object: Step [Update Statistics] Script Date: 05/01/2017 09:43:43 ******/
 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update Statistics',
 @step_id=1,
 @cmdexec_success_code=0,
 @on_success_action=1,
 @on_success_step_id=0,
 @on_fail_action=2,
 @on_fail_step_id=0,
 @retry_attempts=0,
 @retry_interval=0,
 @os_run_priority=0, @subsystem=N'TSQL',
 @command=N'DECLARE @tablename varchar(80),@shemaname varchar(80)
 DECLARE @SQL AS NVARCHAR(200)
 DECLARE TblName_cursor CURSOR FOR
 SELECT t.name,s.name FROM sys.tables t join sys.schemas s
 on s.schema_id=t.schema_id
 OPEN TblName_cursor

FETCH NEXT FROM TblName_cursor
 INTO @tablename,@shemaname

WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @SQL = ''UPDATE STATISTICS ''+@shemaname+''.['' + @TableName + ''] WITH FULLSCAN '' ---+ CONVERT(varchar(3), @sample) + '' PERCENT''
 PRINT @SQL

EXEC sp_executesql @statement = @SQL

FETCH NEXT FROM TblName_cursor
 INTO @tablename,@shemaname
 END

CLOSE TblName_cursor
 DEALLOCATE TblName_cursor
 ',
 @database_name=N'CRM',

/*CRM is the database NAME*/
 @flags=0
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Reindexing',
 @enabled=1,
 @freq_type=4,
 @freq_interval=1,
 @freq_subday_type=1,
 @freq_subday_interval=0,
 @freq_relative_interval=0,
 @freq_recurrence_factor=0,
 @active_start_date=20141124,
 @active_end_date=99991231,
 @active_start_time=193000,
 @active_end_time=235959,
 @schedule_uid=N'b5ec9450-ee63-43bc-926f-ab5e36d5dc9e'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 COMMIT TRANSACTION
 GOTO EndSave
 QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
 EndSave:

GO

sql drop stored procedure if exists

Drop and re-create procedure if it exists in T-SQL not working

 

IF EXISTS(SELECT 1

          FROM   INFORMATION_SCHEMA.ROUTINES

          WHERE  ROUTINE_NAME = 'YOUR_STORED_PROCEDURE_NAME'

                 AND SPECIFIC_SCHEMA = 'dbo')

  BEGIN

      DROP PROCEDURE YOUR_STORED_PROCEDURE_NAME

  END

GO




CREATE PROCEDURE dbo.YOUR_STORED_PROCEDURE_NAME

AS-- Logic Comes Here

GO

DataSet panel in SSRS designer (BIDS or SQL Server Data Tools for SQL 2012) is gone

Dataset not showing in the left side ?

If you are using BIDS with SQL 2008 R2 you can only get the “Report Data” menu by clicking inside the actual report layout itself.

  1. Click inside the actual report layout.
  2. Now select “View” from the main menu bar.
  3. Now select “Report Data” which is the last item.

 

Restore database from network drive

Configuring Security & Sharing on the Remote Server (ServerName)

On the remote server called “ServerName” which has the “DB.bak” file on its file system, browse to the folder including the file, right-click, properties and then follow the screenshots below.

 

 

 

Creating Mapped Network Drive on the SQL Server

Log on the SQL server machine to create a mapped network drive which is mapped to the shared path you already created in the previous step. To do this, follow the screenshot below.

Registering the Mapped Network Path on SQL Server Management Studio

You, you need to register the mapped network path on the SQL Server Management Studio in order to be able to access the mapped drive from SQL Server Management Studio File Explorer. To do so, open SQL Server Management Studio and execute the query below.

1
2
3
4
5
6
7
8
9
10
11
12
EXEC sp_configure 'show advanced options', 1 
GO 
RECONFIGURE 
GO 
EXEC sp_configure 'xp_cmdshell', 1 
GO 
RECONFIGURE 
GO  
EXEC XP_CMDSHELL 'net use N: /delete'
EXEC XP_CMDSHELL 'net use N: \\ServerName\DB-Backup-ShareName'
EXEC XP_CMDSHELL 'Dir N:'

Read more: http://developmentsimplyput.blogspot.com/2012/11/restorebackup-sql-database-fromto.html#ixzz2lDfAWJeL

sql days difference

To find the days difference between 2 date:

DATEDIFF(day, From_Date, To_Date) AS DAYS_DIFF

Some more example from MSDN:

SELECT DATEDIFF(year, ‘2005-12-31 23:59:59.9999999’

, ‘2006-01-01 00:00:00.0000000’);

SELECT DATEDIFF(quarter, ‘2005-12-31 23:59:59.9999999’

, ‘2006-01-01 00:00:00.0000000’);

SELECT DATEDIFF(month, ‘2005-12-31 23:59:59.9999999’

, ‘2006-01-01 00:00:00.0000000’);

SELECT DATEDIFF(dayofyear, ‘2005-12-31 23:59:59.9999999’

, ‘2006-01-01 00:00:00.0000000’);

SELECT DATEDIFF(day, ‘2005-12-31 23:59:59.9999999’

, ‘2006-01-01 00:00:00.0000000’);

SELECT DATEDIFF(week, ‘2005-12-31 23:59:59.9999999’

, ‘2006-01-01 00:00:00.0000000’);

SELECT DATEDIFF(hour, ‘2005-12-31 23:59:59.9999999’

, ‘2006-01-01 00:00:00.0000000’);

SELECT DATEDIFF(minute, ‘2005-12-31 23:59:59.9999999’

, ‘2006-01-01 00:00:00.0000000’);

SELECT DATEDIFF(second, ‘2005-12-31 23:59:59.9999999’

, ‘2006-01-01 00:00:00.0000000’);

SELECT DATEDIFF(millisecond, ‘2005-12-31 23:59:59.9999999’

, ‘2006-01-01 00:00:00.0000000’);