I am in the process of putting my notes into web pages, please keep checking in later for more contents
mssql   Change Server Name  Get Current Time  find missing index  numa nodes  estimated execution plan  Single User Mode  Shrink Database 
sp_who  sp_whoisactive  kill sessions for a db  kill sessions for a user 
Default Backup Location  Backup Using SSMS  Query Backup File Location  point_in_time_restore_and_recovery 
backup_restore_status  failed job error message  Generate Backup Script  Generate Recovery Script  check_log_shipping 
Database Mail  alter,operator,sqlagent,databasemail  sql profile and sql trace 
Rename Data File 
find_objects  show_databases  table_index_size  table_compression  index_rebuild  change_servername  create_user_extract_permission 
fix_orphaned_user  create_user_with_sysadmin  list_datafile  logfile_shrink  tempfile_shrink 


top   prev   next  

Find Current SQL Server Version

SELECT SERVERPROPERTY ('edition')      Edition, 
       SERVERPROPERTY ('productlevel') Service_Pack, 
       SERVERPROPERTY ('productversion') Numeric_Version;
GO

Find Windows Group Current Windows User Belongging To

# Find "AD group" current windows user belongging to 

EXEC xp_logininfo 'BUILTIN\Administrators';
GO
EXEC xp_logininfo 'domain\useraccount';
GO
EXEC xp_logininfo 'domain\useraccount','all';
GO

sp_who sp_who2

-- sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]
sp_who
go
exec sp_who
EXEC sp_who 'active';
EXEC sp_who 'sa';
EXEC sp_who 'interface';
EXEC sp_who '32' --specifies the process_id;

exec sp_helptext sp_who

sp_who @loginame='my_username'
drop table #temp_sp_who ;
create table #temp_sp_who 
                     (  spid            smallint,  
                        ecid            smallint,  
                        status          nchar(30),  
                        loginame        nchar(128),  
                        hostname        nchar(128),  
                        blk             char(5),  
                        dbname          nchar(128),  
                        cmd             nchar(16),
                        request_id      int);

INSERT INTO #temp_sp_who EXEC sp_who
set nocount on select * from #temp_sp_who;
select * from #temp_sp_who where dbname='amos';
set nocount on;
DECLARE @retTable TABLE 
                (  spid      smallint,  
                   ecid      smallint,  
                   status    nchar(30),  
                   loginname nchar(128),  
                   hostname nchar(128),  
                   blk char(5),  
                   dbname nchar(128),  
                   cmd nchar(16),  
                   request_id INT)INSERT INTO @retTable EXEC sp_who;
select * from @retTable where dbname='amos';
declare @ttTable table (SPID INT,
                        Status VARCHAR(255),
                        Login VARCHAR(255),
                        HostName VARCHAR(255),
                        BlkBy VARCHAR(255),
                        DBName VARCHAR(255),
                        Command VARCHAR(255),
                        CPUTime INT,
                        DiskIO INT,
                        LastBatch VARCHAR(255),
                        ProgramName VARCHAR(255),
                        SPID2 INT,
                        REQUESTID INT);

INSERT INTO @ttTable EXEC sp_who2;

select * from @ttTable where DBName is not null and DBName not in ( 'master' );

top
sp_who2
go

select count(*) from #temp_sp_who2;
go

DROP TABLE #temp_sp_who2;
go

CREATE TABLE #temp_sp_who2 
(
   SPID INT,  
   Status VARCHAR(1000) NULL,  
   Login SYSNAME NULL,  
   HostName SYSNAME NULL,  
   BlkBy SYSNAME NULL,  
   DBName SYSNAME NULL,  
   Command VARCHAR(1000) NULL,  
   CPUTime INT NULL,  
   DiskIO INT NULL,  
   LastBatch VARCHAR(1000) NULL,  
   ProgramName VARCHAR(1000) NULL,  
   SPID2 INT,
   REQUESTID INT
) 
GO

TRUNCATE TABLE #temp_sp_who2
GO

INSERT INTO #temp_sp_who2
EXEC sp_who2
GO

SELECT *
FROM #temp_sp_who2
where Status not in ( 'sleeping', 'BACKGROUND' )
order by status, Login
go

SELECT *
FROM #temp_sp_who2
where DBName not in ( 'master' )
order by status, Login
go


SELECT *
FROM #temp_sp_who2
order by Login
GO

SELECT *
FROM #temp_sp_who2
WHERE Login like '%SAN%'
GO

SELECT *
FROM #temp_sp_who2
WHERE upper(Login) like upper('%San%')
GO

TRUNCATE TABLE #temp_sp_who2
GO

DROP TABLE #temp_sp_who2
GO



top   prev   next  

sp_whoisactive with query_plan order by CPU desc

EXEC sp_WhoIsActive
    @filter = '',
    @filter_type = 'session',
    @not_filter = '',
    @not_filter_type = 'session',
    @show_own_spid = 0,
    @show_system_spids = 0,
    @show_sleeping_spids = 1,
    @get_full_inner_text = 0,
    @get_plans = 2,
    @get_outer_command = 0,
    @get_transaction_info = 1,
    @get_task_info = 1,
    @get_locks = 1,
    @get_avg_time = 0,
    @get_additional_info = 1,
    @find_block_leaders = 1,
    @delta_interval = 1,
    @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][query_plan][locks][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][[%]',
    @sort_order = '[CPU] DESC',
    @format_output = 1,
    @destination_table = '',
    @return_schema = 0,
    @schema = NULL,
    @help = 0


top   prev   next  

sp_whoisactive source code

sp_whoisactive source code


top   prev   next  

Find Missing Index

-- Find Missing Index Per Query
;WITH  
 XMLNAMESPACES 
     (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'   
             ,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)       
SELECT top 5 ECP.[usecounts]    AS [UsageCounts] 
      ,ECP.[refcounts]    AS [RefencedCounts] 
      ,ECP.[objtype]      AS [ObjectType] 
      ,ECP.[cacheobjtype] AS [CacheObjectType] 
      ,EST.[dbid]         AS [DatabaseID] 
      ,EST.[objectid]     AS [ObjectID] 
      ,EST.[text]         AS [Statement]       
      ,EQP.[query_plan]   AS [QueryPlan] 
FROM sys.dm_exec_cached_plans AS ECP 
     CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST 
     CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP 
WHERE ECP.[usecounts] > 1  -- Plan should be used more then one time (= no AdHoc queries) 
      AND EQP.[query_plan].exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0 
ORDER BY ECP.[usecounts] DESC
-- Find Missing Index Per Table
EXEC sp_MSforeachdb '
use [?]
SELECT 
TOP 25  @@servername,
db_name(dm_mid.database_id) AS DatabaseID, 
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact, 
dm_migs.last_user_seek AS Last_User_Seek, 
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName], 
''CREATE INDEX [IX_'' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) 
+ ''_''  + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''''),'', '',''_''),''['',''''),'']'','''') 
+ CASE  WHEN dm_mid.equality_columns IS NOT NULL  AND dm_mid.inequality_columns IS NOT NULL THEN ''_''  ELSE ''''  END 
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''''),'', '',''_''),''['',''''),'']'','''')  + ''_IDX_#MYCOMPANY'' + '']'' 
+ '' ON '' 
+ dm_mid.statement  + '' ('' + ISNULL (dm_mid.equality_columns,'''')  + 
CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns   IS NOT NULL THEN '','' ELSE  '''' END 
+ ISNULL (dm_mid.inequality_columns, '''')  + '')''   
+ ISNULL ('' INCLUDE ('' + dm_mid.included_columns + '')'', '''') AS Create_Statement  
FROM sys.dm_db_missing_index_groups dm_mig  
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs  
ON dm_migs.group_handle = dm_mig.index_group_handle  
INNER JOIN sys.dm_db_missing_index_details dm_mid  
ON dm_mig.index_handle = dm_mid.index_handle  
WHERE dm_mid.database_ID = DB_ID()  
ORDER BY Avg_Estimated_Impact DESC 
'
save xml content as myfile.sqlplan

<ShowPlanXML>

</ShowPlanXML>
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; select object_name(object_id) table_name, name, type_desc, is_disabled from sys.indexes where name like ''%#ncl'''
EXECUTE master.sys.sp_MSforeachdb "USE [?]; select  object_name(object_id) table_name, name, type_desc, is_disabled from sys.indexes where name like '%#ncl'"

select * from sys.indexes where name like '%#xxx';

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; select db_name(); select * from sys.indexes where name like ''%#xxx'''
EXECUTE master.sys.sp_MSforeachdb "USE [?]; select db_name(); select * from sys.indexes where name like '%#xxx'"
SET Showplan and Set Statistics 
-- new
SET SHOWPLAN_XML ON
SET STATISTICS XML ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
-- depreciated
SET SHOWPLAN_TEXT ON
SET STATISTICS PROFILE ON
SET SHOWPLAN_ALL ON



top   prev   next  

Estimated Execution Plan

-- need declare the type, don't need to supply the value.
(@P1 datetime,@P2 int,@P3 varchar(40),@P4 text)
(@1 varchar(8000)) 
SELECT *  
FROM [mytable]  
WHERE [mycol1]=@1;
-- SSMS: Display Estimated Execution Plan With Variables
declare @COL1 smallint;
declare @COL2 uniqueidentifier;

UPDATE MYTABLE  
   SET COL2 = @COL2
WHERE COL1 = @COL1;
SET STATISTICS IO ON
SET STATISTICS TIME ON; 
SET SHOWPLAN_XML ON
save the following as a .sqlplan file
<ShowPlanXML ...>
... ....
</ShowPlanXML>


top   prev   next  

Numa Nodes

-- one row per numa nodes
select *
from sys.dm_os_performance_counters
where object_name = 'SQLServer:Buffer Node'
and counter_name = 'Page life expectancy';



top   prev   next  

sp_whoisactive source code

sp_whoisactive source code


top   prev   next  

Kill Sessions For A Specific DB

-- sp_databases
-- sp_who2

declare @database_name sysname;
-- set @database_name='MYDB'
set @database_name='MYDB1'

SELECT  'kill ' + convert(varchar,spid) cmd FROM master..sysprocesses
WHERE   dbid = DB_ID(@database_name) AND spid != @@SPID order by spid;
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_RemoveActiveConnections]
    @database_name SYSNAME
AS 
    BEGIN
        DECLARE @spid INT
        DECLARE @query NVARCHAR(255)
        DECLARE processes CURSOR
        FOR
            SELECT  spid
            FROM    master..sysprocesses
            WHERE   dbid = DB_ID(@database_name)
                    AND spid != @@SPID
        -- DECLARE @database_name sysname;
        -- @database_name = 'mydbname';
        OPEN processes
        FETCH NEXT FROM processes INTO @spid
        WHILE @@FETCH_STATUS = 0 
            BEGIN
                PRINT 'KILLing ' + RTRIM(@spid)
                SET @query = 'KILL ' + RTRIM(@spid)
                EXEC(@query)
                FETCH NEXT FROM processes INTO @spid
            END
        CLOSE processes
        DEALLOCATE processes
    END
GO


top   prev   next  

Kill Sessions For A Specific User

-- sp_who2

declare  @login_name sysname;
-- set @login_name='MYLOGIN'
set @login_name=N'MYLOGIN1'

SELECT  N'kill ' + convert(nvarchar,spid) cmd FROM master..sysprocesses
WHERE   loginame = @login_name AND spid != @@SPID order by spid;
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_KillUserSessions] @login_name sysname
AS
    BEGIN
        DECLARE @spid INT;
        DECLARE @query NVARCHAR(255);
        DECLARE processes CURSOR
        FOR
            SELECT  spid
            FROM    master.sys.sysprocesses
            WHERE   loginame = @login_name
                    AND spid != @@SPID;
        -- DECLARE @login_name sysname;
        -- @login_name = 'myusername';
        OPEN processes;
        FETCH NEXT FROM processes INTO @spid;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                PRINT 'KILLing ' + RTRIM(@spid);
                SET @query = 'KILL ' + RTRIM(@spid);
                EXEC(@query);
                FETCH NEXT FROM processes INTO @spid;
            END;
        CLOSE processes;
        DEALLOCATE processes;
    END;
GO



top   prev   next  

SQL Server Agent Job Failed Job Error Message

SELECT top 10 
        sh.run_status,
        sh.step_id,
        sj.name + ' ' + sh.step_name,
        sh.run_date,
        STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  
          CAST(sh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
        STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + 
        CAST(sh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 
            'run_duration (DD:HH:MM:SS)  ',
	sh.message
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sh
ON sj.job_id = sh.job_id
where name like '%BK%' 
-- and sh.run_status <> 1 
order by sh.instance_id desc,sh.run_date desc, sh.run_time desc;
go
select	j.name
	,js.step_name
	,js.command
	,jh.message
	,jh.run_date
	,jh.run_time
	,jh.run_duration
from	msdb..sysjobs j
join	msdb..sysjobsteps js
		on js.job_id = j.job_id
join	msdb..sysjobhistory jh
		on jh.job_id = js.job_id
		and jh.step_id = js.step_id
where	
jh.run_status <> 1 
-- and j.[name] = N'!BK_USERDB_FULL'
; 
select @@version
go

select name from msdb..sysjobs 
where name like '%BK%' 
order by 1
go

select * from msdb..sysjobs 
where name like '%BK%' 
and enabled = 1
order by 1
go

select top 10 j.name, h.* from msdb..sysjobhistory h, msdb..sysjobs j 
where 
h.job_id = h.job_id and j.name = N'BK_DAILY_DATABASE.Subplan_1' 
order by h.instance_id;

select top 3	j.name
	,js.step_name
	,js.command
	,jh.message
	,jh.run_date
	,jh.run_time
	,jh.run_duration
from	msdb..sysjobs j
join	msdb..sysjobsteps js
		on js.job_id = j.job_id
join	msdb..sysjobhistory jh
		on jh.job_id = js.job_id
		and jh.step_id = js.step_id
where	
jh.run_status <> 1 and
-- and jh.run_date > dateadd(dd,-1,getdate()) 
-- and 
-- j.name = N'BK_DAILY_DATABASE.Subplan_1'
j.name like '%BK_TRANS%LOG_DATABASE%'
and jh.run_date > cast(convert(char(8),getdate(),112) as int) -1 
order by jh.run_date desc, jh.run_time desc;
go



top   prev   next  

SQL Server (Active) Session Eg: Backup Or Restore Status

-- Backup And Restoration Status
SELECT  r.session_id
      , r.command
      , CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete]
      , CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GETDATE()), 20) AS [ETA Completion Time]
      , CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
      , CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min]
      , CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [ETA Hours]
      , CONVERT(VARCHAR(1000), (SELECT  SUBSTRING(text, r.statement_start_offset / 2, CASE WHEN r.statement_end_offset = -1 THEN 1000
                                                                                           ELSE (r.statement_end_offset - r.statement_start_offset) / 2
                                                                                      END)
                                FROM    sys.dm_exec_sql_text(sql_handle)
                               ))
FROM    master.sys.dm_exec_requests r
WHERE   command IN ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE HEADERON', 'RESTORE HEADERONLY')



top   prev   next  

Default Backup Location

Using SSMS to Change Default Backup Location:
Database Settings --> Data and Log locations 

Default Backup Location
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer 

EXEC  master.dbo.xp_instance_regread 
 N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer',N'BackupDirectory'

-- Query Default Backup Location
DECLARE @BackupDirectory VARCHAR(100) 
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer', 
@value_name='BackupDirectory', 
@BackupDirectory=@BackupDirectory OUTPUT 
SELECT @BackupDirectory 

-- Change Default Backup Location
mssqltips:Change Default Backup Location
EXEC master..xp_regwrite 
@rootkey='HKEY_LOCAL_MACHINE', 
@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer', 
@value_name='BackupDirectory', 
@type='REG_SZ', 
@value='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup' 



top   prev   next  

Backup SSMS and Script

mssqltips:BACKUP DB Using SSMS and Scripting out TSQL
BACKUP DATABASE [YourDB] TO  DISK = N'SomePath\YourDB.bak' 
WITH FORMAT, INIT,  NAME = N'YourDB Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [AdventureWorks] 
   TO  DISK = N'C:\SQL_Backup\AdventureWorks_full_20080522.BAK' 
   WITH  DESCRIPTION = N'Full backup of the AdventureWorks database', 
   NOFORMAT, 
   INIT,  
   NAME = N'AdventureWorks-Full Database Backup', 
   SKIP, 
   NOREWIND, 
   NOUNLOAD,  
   STATS = 10, 
   CHECKSUM
GO
DECLARE @backupSetId AS INT
SELECT @backupSetId = position 
FROM msdb..backupset 
WHERE database_name=N'AdventureWorks' 
   AND backup_set_id=(SELECT MAX(backup_set_id) 
               FROM msdb..backupset 
               WHERE database_name=N'AdventureWorks' )
IF @backupSetId IS NULL 
BEGIN 
   RAISERROR(N'Verify failed. Backup information for database ''AdventureWorks'' not found.', 16, 1) 
END
RESTORE VERIFYONLY 
   FROM  DISK = N'C:\SQL_Backup\AdventureWorks_full_20080522.BAK' 
   WITH  FILE = @backupSetId,  
   NOUNLOAD,  
   NOREWIND
GO


top   prev   next  

Query SQL Server Backupset File Location

select  top 50 a.server_name, a.database_name, backup_finish_date, a.backup_size,
CASE a.[type] -- Let's decode the three main types of backup here
 WHEN 'D' THEN 'Full'
 WHEN 'I' THEN 'Differential'
 WHEN 'L' THEN 'Transaction Log'
 ELSE a.[type]
END as BackupType
 ,b.physical_device_name
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b
  on a.media_set_id = b.media_set_id
where backup_finish_date !> GETDATE()
-- and a.database_name Like 'master%'
-- and a.type <> 'L' 
order by a.backup_finish_date desc;
go

------------------------------------------------------------------------------------------- 
--Most Recent Database Backup for Each Database 
------------------------------------------------------------------------------------------- 
SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset 
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  msdb..backupset.type = 'D' 
GROUP BY 
   msdb.dbo.backupset.database_name  
ORDER BY  
   msdb.dbo.backupset.database_name;
GO

-- Database Backups for all databases For Previous Week  GETDATE() - 7
SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset 
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date;
go

------------------------------------------------------------------------------------------- 
--Most Recent Database Backup for Each Database - Detailed 
------------------------------------------------------------------------------------------- 

SELECT  
   A.[Server],  
   A.last_db_backup_date,  
   B.backup_start_date,  
   B.expiration_date, 
   B.backup_size,  
   B.logical_device_name,  
   B.physical_device_name,   
   B.backupset_name, 
   B.description 
FROM 
   ( 
   SELECT   
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
       msdb.dbo.backupset.database_name,  
       MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
   FROM    msdb.dbo.backupmediafamily  
       INNER JOIN msdb.dbo.backupset 
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
   WHERE   msdb..backupset.type = 'D' 
   GROUP BY 
       msdb.dbo.backupset.database_name  
   ) AS A    
   LEFT JOIN  
   ( 
   SELECT   
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset 
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  msdb..backupset.type = 'D' 
   ) AS B 
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] 
  AND A.[last_db_backup_date] = B.[backup_finish_date] 
ORDER BY  
   A.database_name;
GO

------------------------------------------------------------------------------------------- 
--Databases Missing a Data (aka Full) Back-Up Within Past 24 Hours 
------------------------------------------------------------------------------------------- 
--Databases with data backup over 24 hours old 
SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name, 
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date, 
   DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)] 
FROM    msdb.dbo.backupset 
WHERE     msdb.dbo.backupset.type = 'D'  
GROUP BY msdb.dbo.backupset.database_name 
HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))  
UNION  
--Databases without any backup history 
SELECT      
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,  
   master.dbo.sysdatabases.NAME AS database_name,  
   NULL AS [Last Data Backup Date],  
   9999 AS [Backup Age (Hours)]  
FROM 
   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset 
       ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name 
WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb' 
ORDER BY  
   msdb.dbo.backupset.database_name;
GO

-- Full Back-Up Within Past 24 Hours 
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'D'
ORDER BY backup_set_id DESC
GO

-- Transaction Log Back-Up Within Past 24 Hours 
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'L'
ORDER BY backup_set_id DESC
GO

-- Differential Back-Up Within Past 24 Hours 
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'I'
ORDER BY backup_set_id DESC
GO

-- File\File Group Backups
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'F'
ORDER BY backup_set_id DESC
GO

select a.backup_set_id, a.server_name, a.database_name, 
a.name, a.user_name, a.position, a.software_major_version, 
a.backup_start_date, backup_finish_date, a.backup_size, 
a.recovery_model, b.physical_device_name
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b
  on a.media_set_id = b.media_set_id
-- where a.database_name = 'DatabaseName'
order by a.backup_finish_date desc
Go

SELECT  DatabaseName = x.database_name,
        LastBackupFileName = x.physical_device_name,
        LastBackupDatetime = x.backup_start_date
FROM (  SELECT  bs.database_name,
                bs.backup_start_date,
                bmf.physical_device_name,
                  Ordinal = ROW_NUMBER() OVER( PARTITION BY bs.database_name 
                                               ORDER BY bs.backup_start_date DESC )
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.[type] = 'D'
                  AND bs.is_copy_only = 0 ) x
WHERE x.Ordinal = 1
ORDER BY DatabaseName;
Go

SELECT          database_name,
                physical_device_name,
                backup_start_date,
                backup_finish_date,
                backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
-- WHERE database_name = 'YourDB'
ORDER BY backup_finish_date DESC
GO

wraithnath



top   prev   next  

point in time restore and recovery:

ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO

Back up the tail of the log
BACKUP LOG <database_name> TO <backup_device>   
   WITH NORECOVERY, NO_TRUNCATE; 

RESTORE DATABASE AdventureWorks  FROM AdventureWorksBackups  WITH FILE=3, NORECOVERY;  
-- or RESTORE DATABASE AdventureWorks  FROM DISK = N'D:\xxx.bak', NORECOVERY;  

RESTORE LOG AdventureWorks  FROM AdventureWorksBackups  WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';  
-- or RESTORE LOG AdventureWorks  FROM DISK = N'D:\yyy.trn', NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';  
  
RESTORE LOG AdventureWorks  FROM AdventureWorksBackups  WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';  

RESTORE LOG [AdventureWorks] 
   FROM DISK = N'D:\zzz.trn'
   WITH STOPAT = '2016-12-21 11:12:18.797', -- Insert Your Time
   STANDBY = N'D:\yyy.trn'
GO

rem RESTORE LOG [test] 
rem FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Test_Transaction' 
rem WITH FILE = 3, 
rem NOUNLOAD , 
rem STATS = 10, RECOVERY , 
rem STOPAT = N'8/21/2005 3:20:00 PM'

rem RESTORE LOG AdventureWorks FROM DISK = 'c:\adventureworks_log.bak'   
rem WITH STOPATMARK = 'lsn:15000000040000037'  
rem GO

rem RESTORE LOG AdventureWorks FROM DISK = 'c:\adventureworks_log.bak'   
rem WITH STOPBEFOREMARK = 'lsn:15000000040000037'  
rem GO

RESTORE DATABASE AdventureWorks WITH RECOVERY;   
GO  

Restore a SQL Server Database to a Point in Time (Full Recovery Model)
1 
2 

Recover to a Log Sequence Number 
1 

msdbgmarkhistory
1 

restore headeronly
1 

RESTORE HEADERONLY   
FROM DISK = N'C:\AdventureWorks-FullBackup.bak'   
WITH NOUNLOAD;  
GO 

restore filelist only
1 
RESTORE FILELISTONLY FROM AdventureWorksBackups   
--   WITH FILE=2;  
GO

1 
RESTORE VERIFYONLY

Generate a point-in-time restore automatically SQL 2005 version
1 
2 
3 



top   prev   next  

Generate Backup Script

-- backup everyday

backup

DECLARE @LFCR AS CHAR(2) = CHAR(13) + CHAR(10)
select 
'backup database [' + name + ']' + @LFCR + 
'TO DISK = N''D:\DBBACKUPS\Backup\' + name +  '.bak'' WITH COMPRESSION,FORMAT, INIT,'+ @LFCR + 
'NAME = N'''+ name + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'+ @LFCR + 
'GO' + @LFCR
as cmd 
 from sys.databases where name not in ( 'model', 'tempdb' ) order by database_id;

-- -----------------------------------------------------------------------------------

DECLARE @LFCR AS CHAR(2) = CHAR(13) + CHAR(10)
select 
'backup database [' + name + ']' + @LFCR + 
'TO DISK = N''D:\DBBACKUPS\Backup\' + name +  N'_D' + substring (convert (varchar (10), getdate(),112),7,2) + 
'.bak'' WITH COMPRESSION,FORMAT, INIT,'+ @LFCR + 
'NAME = N'''+ name + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'+ @LFCR + 
'GO' + @LFCR
as cmd 
 from sys.databases where name not in ( 'model', 'tempdb' ) order by database_id;

-- -----------------------------------------------------------------------------------


-- cf1: select name from sys.databases where name not in ( 'model' );

d:
cd dbbackups

BACKUP DATABASE [MyDB1] 
TO  DISK = N'D:\DBBACKUPS\Backup\MyDB1.bak' WITH NOFORMAT, INIT,  
NAME = N'MyDB1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO



top   prev   next  

Generate Recovery Script

/*
Generate Point-in-time restore script, given only DBName and Point-in-time timestamp
The script WILL NOT do any changes to your databases, it will only generate sql code 
that you need to manually execute.
  
Created by Henning Frettem, www.thefirstsql.com
*/
SET NOCOUNT ON
GO
  
DECLARE
  @DBName varchar(200),
  @PointInTime datetime,
  @cPointInTime varchar(19),
  @Filename varchar(200),
  @tab varchar(1) = char(9),
  @cr varchar(2) = char(13)+char(10),
  @Full_BackupStartDate datetime,
  @Diff_BackupStartDate datetime,
  @Log_BackupStartDate datetime,
  @SQL varchar(max) = ''

  
-- SET @DBName  = 'MyDatabase'
-- SET @PointInTime = '2017-01-01 17:10:00'

set @cPointInTime=convert(char(19),getdate(),20) 
set @DBName=db_name()
set @PointInTime=@cPointInTime
print '-- ' + @cPointInTime
print '-- ' + @DBName

  
BEGIN TRY
  --> Performing some checks
  IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset 
      WHERE database_name = @DBName)
       RAISERROR(N'The database has never been backed up', 16, 1)
  IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset 
          WHERE database_name = @DBName AND type = 'D' AND backup_start_date <= @PointInTime)
    RAISERROR(N'No full backup exists prior to the specified PointInTime', 16, 1)
  ELSE IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset 
         WHERE database_name = @DBName AND type = 'L' AND backup_start_date > @PointInTime)
    RAISERROR(N'No backup of the log exist after the specified PointInTime, do a tail log backup first', 16, 1)
  
  --> Getting the filename and the date of the last full backup prior to the PointInTime
  SELECT TOP 1 
      @Filename = b.physical_device_name,
      @Full_BackupStartDate = backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'D'
    AND a.backup_start_date <= @PointInTime
  ORDER BY a.backup_start_date DESC
  
  SET @SQL = 'USE master' + @cr + 
    'GO' + @cr + @cr +
    'ALTER DATABASE ' + QUOTENAME(@DBName) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE' + @cr + 
    'GO' + @cr + @cr
   
  PRINT @SQL
  SET @SQL = ''
  
  --> Restore the last full backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', REPLACE, STATS=5, NORECOVERY' + @cr + 'GO' + @cr,
    @Diff_BackupStartDate = a.backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'D'
    AND a.backup_start_date = @Full_BackupStartDate
  ORDER BY a.backup_start_date DESC  
 
  PRINT @SQL
  SET @SQL = ''
    
  --> Restore the last differential backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', NORECOVERY' + @cr + 'GO',
    @Diff_BackupStartDate = a.backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'I'
    AND a.backup_start_date > @Full_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date DESC
   
  PRINT @SQL
  SET @SQL = ''
    
  IF @Diff_BackupStartDate IS NULL
    SET @Diff_BackupStartDate = @Full_BackupStartDate
      
  --> Generate all log restores except the last one
  SELECT
    @SQL = @SQL + 'RESTORE LOG ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', NORECOVERY' + @cr + 'GO' + @cr,
    @Log_BackupStartDate = a.backup_start_date   
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @Diff_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date
  
  PRINT @SQL
  SET @SQL = ''
 
  --> Generate last log restore script with the stopat command and recovery
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE LOG ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', RECOVERY, STOPAT = ''' + CONVERT(varchar(20), 
            @PointInTime, 120) + '''' + @cr + 'GO' + @cr
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @PointInTime
  ORDER BY a.backup_start_date ASC
  PRINT @SQL
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE()
END CATCH
GO




top   prev   next  

Single User Mode

-- EXEC sp_RemoveActiveConnections @database_name='mydb'
-- kill 9999
USE MASTER
GO
ALTER DATABASE [mydb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
-- DROP DATABASE [mydb]
-- GO
ALTER DATABASE [mydb] SET MULTI_USER;
Go


top   prev   next  

Get Current Time and Format

-- Quick Delay after Truncate
WAITFOR DELAY '00:00:01'
GO

-- GET DATE TIME FORMAT
SELECT GETDATE() 
GO

-- GET DATE TIME FORMAT
SELECT SYSDATETIME(),SYSDATETIMEOFFSET(),SYSUTCDATETIME(),CURRENT_TIMESTAMP,GETDATE(),GETUTCDATE();  
date and time

-- Yesterday
select cast(convert(char(8),getdate(),112) as int) -1 as yesterday;

-- for a day dd
select substring (convert (varchar (10), getdate(),112),7,2)

-- for yyyymmdd
select substring (convert (varchar (10), getdate(),112),1,8)

select substring (convert (varchar (10), getdate(),112),1,10)
go
-- for yyyymmdd hhmmss
select substring (convert (varchar (19), getdate(),120),1,19)

select substring (convert (varchar (20), getdate(),120),1,20)
go

BEGIN
	declare @ts Datetime
	declare @time_str1 varchar(30)
	declare @time_str2 varchar(30)
	declare @time_str varchar(30)
	declare @filename varchar(100)
	-- get current_timestamp : Apr 20 2017  5:31PM
	SELECT @ts=CURRENT_TIMESTAMP
	-- after 120 convert 2017-04-20 17:31:22
	-- after left 20, 2017-04-20 17:31:22
	set @time_str1 = LEFT(CONVERT(VARCHAR, @ts, 120), 20)
	-- after replace _ and :  2017-04-20_173122
	set @time_str = replace( replace( @time_str1, ' ', '_'), ':', '')
	-- filename format: T:\dbbackups\mydb_2017-04-20_173122.bak
	set @filename = 'D:\dbbackups\mydb_'+@time_str+'.bak'
	print @ts
	set @time_str2 = CONVERT(VARCHAR, @ts, 120)
	print @time_str2 
	print @time_str1
	print @time_str
    print @filename
END


top

top   prev   next  

Change Server Name

-- query server name
use master
go
select @@servername;
go
sp_helpserver;
go

-- change server name
use master
go
begin 
declare @my_servername nvarchar(256); 
select @my_servername=@@servername;
exec sp_dropserver @my_servername;
declare @my_hostname nvarchar(256);
select @my_hostname=host_name();
exec sp_addserver @my_hostname, local;
END;
go





top   prev   next  

Rename Data File

-- cf: select @@VERSION
-- cf: select name, physical_name from sys.master_files
-- cf: select * from sys.master_files

-- -- Changing logical names
-- ALTER DATABASE DB1 MODIFY FILE (NAME = DB1, NEWNAME = NEW_DB1);
-- ALTER DATABASE DB1 MODIFY FILE (NAME = DB1_log, NEWNAME = NEW_DB1_log);
declare @MYDB as nvarchar(255) = N'MYDB1'
select 'alter database ' + d.name + ' modify file ( name = ''' + f.name + ''', newname = ''new_'+ f.name + ''');' 
from sys.master_files f, sys.databases d
where d.name = @MYDB and d.database_id = f.database_id;
-- -- Changing file location or file name
declare @MYDB as nvarchar(255) = N'MYDB1'
select 'alter database ' + d.name + ' modify file ( name = ''' + f.name + ''', filename = '''+ f.physical_name + ''');' 
from sys.master_files f, sys.databases d
where d.name = @MYDB and d.database_id = f.database_id;

-- alter database DB1 modify file ( name = 'DB1', filename = 'D:\MSSQL\DATA\DB1_01.mdf');
-- alter database DB1 modify file ( name = 'DB1_log', filename = 'E:\MSSQL\Data\DB1_log_01.LDF');

-- take database off line
-- move OS files
-- take database on line



top   prev   next  

logshipping Applying Check

select primary_server, primary_database, last_backup_date, last_backup_file from msdb.dbo.log_shipping_monitor_primary;

select secondary_server, secondary_database, last_copied_date, last_copied_file from msdb.dbo.log_shipping_monitor_secondary;

select secondary_server, secondary_database, last_restored_date, last_restored_file from msdb.dbo.log_shipping_monitor_secondary;

sp_help_log_shipping_monitor_secondary 
sp_change_log_shipping_secondary_database 
sp_add_log_shipping_secondary_database 



top   prev   next  

dba(database) mail

GUI
SSMS --> Management --> Database Mail --> Configure Database Mail,Profile,Account

Script
1): Enable Database Mail Stored Procedure
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure 
go

2): Add email account and mail server
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'MyMailAccount',
    @description = 'Mail account for Database Mail',
    @email_address = 'support_db1@quickdbasupport.com',
    @display_name = 'MyAccount',
    @username='support@@quickdbasupport.com',
    @password='support_pass1',
    @mailserver_name = 'mail.quickdbasupport.com'

3): Add email profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
       @profile_name = 'MyMailProfile',
       @description = 'Profile used for database mail'

4): Add mail account to email profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'MyMailProfile',
    @account_name = 'MyMailAccount',
    @sequence_number = 1

5): to grant the Database Mail profile access to the msdb public database role 
and to make the profile the default Database Mail profile.
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'MyMailProfile',
    @principal_name = 'public',
    @is_default = 1 ;

6): Test This Email
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='support@quickdbasupport.com',
    @subject = 'My Mail Test',
    @body = @body1,
    @body_format = 'HTML' ;

7): Verify through gui
SSMS --> Management --> Database Mail --> Configure Database Mail,Profile,Account

8): view Database Mail Logs
SELECT * FROM msdb.dbo.sysmail_event_log


top


top   prev   next  

Alert,Operator,sqlagent,DatabaseMail

 sqlmail,dbamail operator,alert,dbamail (profile),sqlagent
restarting SQL Agent
Warning: Restarting SQL Agent will cancel any executing jobs. 

ssms --> Right Click/select "SQL Server Agent" --> (Select ) Properties --> Alert System

Add New Operator: Sql Server Agent --> Operator --> New Operator

Add New Alert: Sql Server Agent --> Alerts --> New Alert



top   prev   next  

Shrink Database

DBCC SHRINKDATABASE ( database_name [ , target_percent ][ , { NOTRUNCATE | TRUNCATEONLY } ])
GO
DBCC SHRINKDATABASE (mydb, 10);
GO
-- light but may not working
USE [mydb]
GO
DBCC SHRINKFILE (N'my_db_log' , 0, TRUNCATEONLY)
GO

-- fileid=2 trying log file
EXEC sp_MSforeachdb 'DECLARE @ln varchar(100) SET @ln=(SELECT name FROM [?].dbo.sysfiles WHERE fileid=2) USE [?] DBCC SHRINKFILE (@ln, 0, TRUNCATEONLY)'

-- heavy but working
USE [mydb]
GO
DBCC SHRINKFILE (N'my_db_log' , 0)
GO

-- fileid=2 trying log file
EXEC sp_MSforeachdb 'DECLARE @ln varchar(100) SET @ln=(SELECT name FROM [?].dbo.sysfiles WHERE fileid=2) USE [?] DBCC SHRINKFILE (@ln, 0)'





top   prev   next  

sql profiler and sql trace

https://www.google.com/search?q=sql+profiler+trace&ie=utf-8&oe=utf-8

https://www.mssqltips.com/sql-server-tip-category/83/profiler-and-trace/

http://blog.sqlauthority.com/2009/08/03/sql-server-introduction-sql-server-2008-profiler-complete/

http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step

https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/

http://sqlconcept.com/tools/default-trace-audit-documentation-and-faq/

http://sqlconcept.com/tools/default-trace-audit-documentation-and-faq/screenshots/

Introducing SQL Trace
https://technet.microsoft.com/en-us/library/ms191006(v=sql.105).aspx

How to: Set a Trace Filter
https://technet.microsoft.com/en-us/library/ms188627(v=sql.105).aspx



top   prev   next  

CREATE LOGIN with sysadmin

USE [master]
GO
CREATE LOGIN [MYDOMAIN\MYUSER] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [MYDOMAIN\MYUSER]
GO
EXEC sp_addsrvrolemember 'MYDOMAIN\MYUSER', 'sysadmin';
GO



top   prev   next  

Rebuild And Reorganize Indexes

ALTER INDEX myindex ON mytable REORGANIZE ;   
ALTER INDEX ALL     ON myschema.mytable REORGANIZE ;     

alter index myindex on mytable rebuild;
alter index all on mytable rebuild;
ALTER INDEX ALL ON myschema.mytable REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

SELECT a.index_id, name, avg_fragmentation_in_percent  
FROM sys.dm_db_index_physical_stats (DB_ID(N'mydb'), 
      OBJECT_ID(N'mytable'), NULL, NULL) AS a  
    JOIN sys.indexes AS b 
      ON a.object_id = b.object_id AND a.index_id = b.index_id;   
GO  
# cluster index and non-cluster index conversion
option 1):
CREATE UNIQUE CLUSTERED INDEX [PK_Customer] on Customer(CustomerID) WITH DROP_EXISTING;

option 2):
ALTER TABLE dbo.YourTable DROP CONSTRAINT PK_YourTable;
ALTER TABLE dbo.YourTable ADD CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED (YourPKField)



top   prev   next  

sp_configure and Configuration

sp_configure

sp_configure 'show advanced options', 1

reconfigure

select * from sys.configurations;



top   prev   next  

Create Operator

USE [msdb]
GO

EXEC msdb.dbo.sp_add_operator @name=N'JobAlerts', 
		@enabled=1, 
		@weekday_pager_start_time=0, 
		@weekday_pager_end_time=235900, 
		@saturday_pager_start_time=0, 
		@saturday_pager_end_time=235900, 
		@sunday_pager_start_time=0, 
		@sunday_pager_end_time=235900, 
		@pager_days=127, 
		@email_address=N'mydbateam@xxx.com;myithelpdesk@xxx.com', 
		@category_name=N'[Uncategorized]'
GO


Home  
top