MSSQL: SQL Server Commands

I am in the process of putting my notes into web pages, please keep checking in later for more contents

quick link

home   find objects   find objects in All Dbs   find sql in db cache   startup_time   system functions   sp_helpdb   table_index_size   msdb purging   ssms usage   sql server version   fix orphaned user   create user with sysadmin   user permission  
transaction log   transaction   index rebuild   table_compression  
70-461     70-462     70-463     70-464     70-465     mssql     mssql_01     mssql_02     mssql_03    
top
top   prev   next  

sql agent subsystem

msdb..sp_enum_sqlagent_subsystems
go

-- merge replication
sp_addmergepublication 
sp_addpublication_snapshot 
sp_addmergearticle 
sp_mergearticlecolum 
sp_addmergefilter

--  distriubtion 
select * from master..MSreplication_options;

select * from distribution..MSpublications;

select * from distribution..MSmerge_sessions;

select * from [distribution].[dbo].[MSmerge_subscriptions];


top   prev   next  

find objects

use Master;
go
declare @myobjectname sysname

set @myobjectname = 'msforeach'

SELECT top 100
db_name() db_name,s.name as schema_name,
-- ISNULL(po.name, ps.name) AS object_owner,
-- ISNULL(po.type_desc, ps.type_desc) as owner_type,
o.name,
-- o.type,
o.type_desc,
o.is_ms_shipped 
FROM sys.all_objects o
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
    LEFT OUTER JOIN sys.database_principals po ON o.principal_id = po.principal_id
    LEFT OUTER JOIN sys.database_principals ps ON s.principal_id = ps.principal_ID
where 
-- o.name like '%sp_database%'
-- o.name = @myobjectname
o.name like '%' + @myobjectname + '%' 
order by o.name, s.name;
go


top   prev   next  

find objects in all dbs


eg: EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'
-- For All DBs
EXECUTE master.sys.sp_MSforeachdb '
use [?];
declare @myobjectname sysname

set @myobjectname = ''sp_help_operator''

SELECT top 100
db_name() db_name,s.name as schema_name,
-- ISNULL(po.name, ps.name) AS object_owner,
-- ISNULL(po.type_desc, ps.type_desc) as owner_type,
o.name,
-- o.type,
o.type_desc,
o.is_ms_shipped 
FROM sys.all_objects o
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
    LEFT OUTER JOIN sys.database_principals po ON o.principal_id = po.principal_id
    LEFT OUTER JOIN sys.database_principals ps ON s.principal_id = ps.principal_ID
where 
-- o.name like ''%sp_database%''
-- o.name = @myobjectname
o.name like ''%'' + @myobjectname + ''%'' 
order by o.name, s.name;
'

use Master;
go
-- use msdb;
-- go
select db_name() + '.' + 
schema_name(schema_id) + '.' +  
o.name, o.type,  o.type_desc from sys.objects o 
where 
-- name like 'sp_whoIsActive' 
type  not in ( 'PK', 'AAA' ) 
and is_ms_shipped = 0
order by schema_id,type,name;
go

SELECT *
FROM   [sys].[objects]
WHERE  SCHEMA_NAME([schema_id]) != 'sys'
   AND [is_ms_shipped] = 0
   AND [parent_object_id] NOT IN (SELECT [object_id]
                                   FROM   [sys].[objects]
                                    WHERE  SCHEMA_NAME([schema_id]) = 'sys'
                                           OR [is_ms_shipped] = 1);

set ansi_nulls off --> column_name = null will return rows
set ansi_nulls on  --> column_name = null will return no rows

top   prev   next  

Server Start Time And DB Instance Start Time

-- server start time
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
GO

SELECT DATEADD(SECOND, (ms_ticks/1000)*(-1), GETDATE()) 
FROM sys.dm_os_sys_info;
GO

SELECT DATEADD(ms,-sample_ms,GETDATE())AS StartTime 
FROM sys.dm_io_virtual_file_stats(1,1);
GO

SELECT
  DATEADD(MILLISECOND, (sample_ms * -1), GETDATE()) AS BOOT_TIME_MACHINE
FROM sys.dm_io_virtual_file_stats(DB_ID(N'tempdb'), 2);
GO
 
-- database instance start time
sp_readerrorlog 0,1,'Copyright (c)'
GO

SELECT create_date FROM sys.databases WHERE name = 'tempdb';
GO

SELECT create_date AS START_TIME_INSTANCE FROM sys.databases WHERE name = 'tempdb';
GO

SELECT getdate() curr_time, 
login_time db_start_time, 
DATEDIFF(minute, login_time,getdate()) mydiff_minute 
from sysprocesses WHERE spid = 1;
GO

top   prev   next  

Find SQL Statement In DB Cache

SELECT total_worker_time/execution_count AS [Avg CPU Time],  
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
        ((CASE qs.statement_end_offset  
          WHEN -1 THEN DATALENGTH(st.text)  
         ELSE qs.statement_end_offset  
         END - qs.statement_start_offset)/2) + 1) AS statement_text  
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st 
where st.text like 'SELECT%CREATE_STATEMENT%'  
ORDER BY total_worker_time/execution_count DESC;  
top
top   prev   next  

system functions

system function: 

APP_NAME  NONE  Returns the program name for the current connection if one has been set by the program before logging on. 
COALESCE  (expression1, expression2, ... expressionN)  A specialized form of the CASE statement. Returns the first non-null expression.  
COL_LENGTH  ('table_name', 'column_name')  The defined (maximum) storage length of a column. 
COL_NAME  (table_id, column_id)  The name of the column. 
DATALENGTH  ('expression')  The length of an expression of any datatype. 
DB_ID  (['database_name'])  The database identification number.  
DB_NAME  ([database_id])  The database name. 
GETANSINULL  (['database_name'])  The default nullability for the database. Returns 1 when the nullability is the ANSI NULL default.  
HOST_ID  NONE  The workstation identification number. 
HOST_NAME  NONE  The workstation name. 
IDENT_INCR  ('table_or_view')  The increment value specified during creation of an identity column of a table or view that includes an identity column. 
IDENT_SEED ('table_or_view') The seed value specified during creation of an identity column of a table or view that includes an identity column. 
INDEX_COL  ('table_name', index_id, key_id) The indexed column name(s). 
ISDATE  (expression_of_possible_date)  Checks whether an expression is a datetime datatype or a string in a recognizable datetime format. Returns 1 when the expression is compatible with the datetime type; otherwise, returns 0. 
ISNUMERIC  (expression_of_possible_number)  Checks whether an expression is a numeric datatype or a string in a recognizable number format. Returns 1 when the expression is compatible with arithmetic operations; otherwise, returns 0. 
ISNULL  (expression, value)  Replaces NULL entries with the specified value. 
NULLIF  (expression1, expression2)  A specialized form of CASE. The resulting expression is NULL when expression1 is equivalent to expression2. 
OBJECT_ID  ('object_name')  The database object identification number. 
OBJECT_NAME  (object_id)  The database object name. 
STATS_DATE  (table_id,index_id)  The date when the statistics for the specified index (index_id) were last updated. 
SUSER_ID  (['login_name'])  The user's login ID number. 
SUSER_NAME  ([server_user_id])  The user's login name. 
SUSER_SID  (['login_name'])  The security identification number (SID) for the user's login name. 
SUSER_SNAME  ([server_user_sid])  The login identification name from a user's security identification number (SID). 
USER_ID  (['user_name'])  The user's database ID number. 
USER_NAME  ([user_id])  The user's database username. 

-- --------
use @@SERVERNAME, or that query the server name from sys.servers.

-- SELECT DEFAULT_DOMAIN()
-- SELECT DEFAULT_DOMAIN()[DomainName] --> DomainName is the alias

top
top   prev   next  

sp_helpdb

exec sp_helpdb 
go
Use tempdb
exec sp_helpfile
go
EXEC sp_helpindex customer
go
EXEC sp_helpconstraint customer_table_name
GO
select @@ROWCOUNT

sp_configure 
sp_who 
sp_who2
sp_lock 
xp_msver 
sp_helpextendedproc 

sp_executesql 'insert mytable values(@p)','@p float',1.0 
top
top   prev   next  

table, index size

use mydb;
go

SELECT  sc.name + '.' + t.NAME AS TableName,
        p.[Rows],
        ( SUM(a.total_pages) * 8 ) / 1024 AS TotalReservedSpaceMB, -- Number of total pages * 8KB size of each page in SQL Server
        ( SUM(a.used_pages) * 8 ) / 1024 AS UsedDataSpaceMB,
        ( SUM(a.data_pages) * 8 ) / 1024 AS FreeUnusedSpaceMB
FROM    sys.tables t
        INNER JOIN sys.schemas sc ON sc.schema_id = t.schema_id
        INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
        INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID
                                            AND i.index_id = p.index_id
        INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE   t.type_desc = 'USER_TABLE'
        AND i.index_id <= 1  --- Heap\ CLUSTERED
GROUP BY sc.name + '.' + t.NAME,
        i.[object_id],i.index_id, i.name, p.[Rows]
ORDER BY ( SUM(a.total_pages) * 8 ) / 1024 DESC;

use mydb;
go
SELECT [Size in MB] = SUM(IDX.reserved)/128,
[Object Name] = OBJ.name
FROM dbo.sysindexes IDX
JOIN dbo.sysobjects OBJ
ON OBJ.id = IDX.id
WHERE IDX.indid IN (0, 1, 255)
GROUP BY IDX.id, OBJ.name
ORDER BY 1 DESC
IF OBJECT_ID('tempdb..#t', 'U') IS NOT NULL
DROP TABLE #t
 
CREATE TABLE #t (
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18))
 
-- get the space used for each table in this database
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
SELECT * FROM (
SELECT [name] as "Table",
CONVERT(int, [rows]) as "Rows",
CONVERT(int, LEFT([reserved],LEN([reserved])-3)) / 1024 as "Reserved MB",
CONVERT(int, LEFT([data],LEN([data])-3)) / 1024 as "Data MB",
CONVERT(int, LEFT([index_size],LEN([index_size])-3)) / 1024 as "Index MB",
CONVERT(int, LEFT([unused],LEN([unused])-3)) / 1024 as "Unused MB"
FROM #t WHERE [name] like 'CONSPT%') t
ORDER BY [Reserved MB] DESC
 
DROP TABLE #t
top
top   prev   next  

msdb purging

msdb purging
use msdb
go
exec sp_delete_backuphistory '2009-04-02' 
go

sp_delete_backuphistory @oldest_date='4/28/2009'
sp_delete_backuphistory @oldest_date = '08-19-2008'

sp_purge_jobhistory
sysmail_delete_mailitems
sysmail_delete_log_sp

DECLARE @cutoff DATE;
SET @cutoff = DATEADD(MONTH, -6, GETDATE());
EXEC msdb..sysmail_delete_mailitems_sp @sent_before = @cutoff;
EXEC msdb..sysmail_delete_log_sp @logged_before = @cutoff;


DECLARE @MinBackupDate DATETIME;
SET @MinBackupDate = DATEADD(month,-6, GETDATE());
DECLARE @SQL VARCHAR(MAX);
 
SET @SQL = 'EXEC msdb..sysmail_delete_mailitems_sp @sent_before= ''' + CONVERT(VARCHAR(20),@MinBackupDate,101) + ''' ;' ;
--PRINT @SQL;
EXEC( @SQL );
 
SET @SQL = 'EXEC msdb..sysmail_delete_log_sp @logged_before= ''' + CONVERT(VARCHAR(20),@MinBackupDate,101) + ''' ;' ;
--PRINT @SQL;
EXEC( @SQL );

-- delete all backup history for a specific database 
use msdb
go
exec sp_delete_database_backuphistory 'test1' 
go
USE MSDB
GO

select count(start_time) from sysmaintplan_logdetail with (nolock) where start_time < '4/28/2009.';

sp_maintplan_delete_log @oldest_time='4/28/2009'

1 

Exec SP_SpaceUsed log_shipping_monitor_history_detail
Exec SP_SpaceUsed log_shipping_monitor_error_detail
Exec SP_SpaceUsed sysjobhistory
Exec SP_SpaceUsed msdb.dbo.sysmaintplan_logdetail


sp_cleanup_log_shipping_history '7CED0C18-6B90-44D2-AAB6-B18A123B7B29',0
sp_cleanup_log_shipping_history
which requires the agent_id parameter which i found using : 
select top 10 * from log_shipping_monitor_history_detail with (nolock)

the sp_cleanup_log_shipping_history deleted a huge amount of data in
the [sysmaintplan_log] and [sysmaintplan_logdetail] tables

DBCC SHRINKFILE(MSDBLog, 100)
GO

. SHRINK THE MSDB Data File
USE MSDB
GO

DBCC SHRINKFILE(MSDBData, 100)
GO

top
top   prev   next  

SQL Server Version

ssms --> help --$gt; about
Object Explorer -> serever instance --> the version information 
C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG
Select @@version
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

SELECT
SERVERPROPERTY('ServerName') 'ServerName'
,@@VERSION '@@version'
,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 'ComputerNamePhysicalNetBIOS'
,SERVERPROPERTY('MachineName') 'MachineName'
,SERVERPROPERTY('InstanceName') 'InstanceName'
,SERVERPROPERTY('IsClustered') 'IsClustered'
,SERVERPROPERTY('BuildClrVersion') 'BuildClrVersion'
,SERVERPROPERTY('Collation') 'Collation'
,SERVERPROPERTY('CollationID') 'CollationID'
,SERVERPROPERTY('ComparisonStyle') 'ComparisonStyle'
,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 'ComputerNamePhysicalNetBIOS'
,SERVERPROPERTY('Edition') 'Edition'
,SERVERPROPERTY('EditionID') 'EditionID'
,SERVERPROPERTY('EngineEdition') 'EngineEdition'
,SERVERPROPERTY('IsFullTextInstalled') 'IsFullTextInstalled'
,SERVERPROPERTY('IsIntegratedSecurityOnly') 'IsIntegratedSecurityOnly'
,SERVERPROPERTY('IsSingleUser') 'IsSingleUser'
,SERVERPROPERTY('LCID') 'LCID'
,SERVERPROPERTY('LicenseType') 'LicenseType'
,SERVERPROPERTY('NumLicenses') 'NumLicenses'
,SERVERPROPERTY('ProcessID') 'ProcessID'
,SERVERPROPERTY('ProductVersion') 'ProductVersion'
,SERVERPROPERTY('ProductLevel') 'ProductLevel'
,SERVERPROPERTY('ResourceLastUpdateDateTime') 'ResourceLastUpdateDateTime'
,SERVERPROPERTY('ResourceVersion') 'ResourceVersion'
,SERVERPROPERTY('ServerName') 'ServerName'
,SERVERPROPERTY('SqlCharSet') 'SqlCharSet'
,SERVERPROPERTY('SqlCharSetName') 'SqlCharSetName'
,SERVERPROPERTY('SqlSortOrder') 'SqlSortOrder'
,SERVERPROPERTY('SqlSortOrderName') 'SqlSortOrderName'
,SERVERPROPERTY('FilestreamShareName') 'FilestreamShareName'
,SERVERPROPERTY('FilestreamConfiguredLevel') 'FilestreamConfiguredLevel'
,SERVERPROPERTY('FilestreamEffectiveLevel') 'FilestreamEffectiveLevel'

cf:sql server discovery 
SQL Server Installation Center --> Tools --> Installed SQL Server features discovery report
%ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\

Run "Setup.exe /Action=RunDiscovery" from a command prompt
If you add "/q" to the command line above no UI will be shown, 
but the report will still be created in 
%ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\20091112_082147. 
top
top   prev   next  

fix orphaned user at database level

sp_change_users_login 
-- change sid in user database EXECUTE master.sys.sp_MSforeachdb 'USE [?]; select db_name(); EXEC sp_change_users_login ''Report'''; GO USE my_db GO EXEC sp_change_users_login 'Report' --> db username, login name, password must not presended GO -- just use alter user to fix it USE my_db GO ALTER USER [my_db_username] with LOGIN=[my_login_name] GO ALTER USER UserName WITH LOGIN = UserName; GO EXEC sp_change_users_login 'Update_One', 'my_db_username', 'my_login_name' --> password must not presented GO EXEC sp_change_users_login 'Auto_Fix', 'my_db_username' GO EXEC sp_change_users_login 'Auto_Fix', 'my_db_username', NULL, 'my_password' GO

top
top   prev   next  

password hash

-- get password hash
SELECT LOGINPROPERTY('MyAppUser','PASSWORDHASH');

-- alter login
SELECT 'ALTER LOGIN ' + QUOTENAME(sp.name) + ' WITH PASSWORD = 0x' +
CONVERT (VARCHAR(514), (SELECT CAST (LOGINPROPERTY(sp.name, 'PasswordHash') 
                                   AS varbinary(256))), 2) + ' HASHED;' cmd 
FROM sys.server_principals sp
WHERE sp.type_desc = 'SQL_LOGIN'
ORDER BY sp.name;

-- create server login
CREATE LOGIN [MyAppUser] WITH 
PASSWORD = 0x0100FEFCB7E9DF1FD04B3DB6E048A92F07DE060C4B75C97DC267 HASHED, 
SID = 0x8AE08F0D255CE34C9164AD21DF960A16, 
DEFAULT_DATABASE = [master], 
CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;

top
top   prev   next  

Get User Sid

-- get sid
USE AppDB 
GO 
SELECT name, sid FROM sys.sysusers WHERE name = 'MyAppUser' 
GO 
USE MASTER 
GO 
SELECT name, sid FROM sys.sql_logins WHERE name = 'MyAppUser' 
GO
select * from sys.server_principals where name like 'MyAppUser%'
GO
top
top   prev   next  

Create User With SYSADMIN Permission


USE [master]
GO
CREATE LOGIN [MYDOMAINNAME\MYUSERNAME] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [MYDOMAINNAME\MYUSERNAME]
GO
EXEC sp_addsrvrolemember 'MYDOMAINNAME\MYUSERNAME', 'sysadmin';
GO
top
top   prev   next  

user permission

-- list all server sys permissions
SELECT SP.[name] AS 'Login' , SPerm.state_desc + ' ' + SPerm.permission_name AS 'ServerPermission'
FROM sys.server_principals SP
  JOIN sys.server_permissions SPerm
    ON SP.principal_id = SPerm.grantee_principal_id
ORDER BY [Login], [ServerPermission];

-- list all server member role pairs
select c.name, p.name from sys.server_role_members rm, sys.server_principals p, sys.server_principals c
where rm.role_principal_id=p.principal_id and rm.member_principal_id=c.principal_id order by 1,2;

-- list all db member role pairs
EXECUTE master.sys.sp_MSforeachdb '
use [?];
select @@servername servername, db_name() db_name, c.name,p.name from sys.database_role_members rm, sys.database_principals p,sys.database_principals c
 where rm.member_principal_id in ( select principal_id from sys.database_principals )
 and rm.role_principal_id = p.principal_id
 and rm.member_principal_id = c.principal_id
 ;
'

-- db sys permission and db obj permission
SELECT 
    USER_NAME(grantee_principal_id) AS 'User'
  , state_desc AS 'Permission'
  , permission_name AS 'Action'
  , CASE class
      WHEN 0 THEN 'Database::' + DB_NAME()
      WHEN 1 THEN OBJECT_NAME(major_id)
      WHEN 3 THEN 'Schema::' + SCHEMA_NAME(major_id) END AS 'Securable'
FROM sys.database_permissions dp
WHERE class IN (0, 1, 3)
AND minor_id = 0
AND user_name(grantee_principal_id) not in ( 'public' )
AND permission_name not in ( 'CONNECT' ) 
order by 1,2,3,4
; 

-- db sys permission and db obj permission per db
USE MyDB;
GO
SELECT 
    USER_NAME(grantee_principal_id) AS 'User'
  , state_desc AS 'Permission'
  , permission_name AS 'Action'
  , CASE class
      WHEN 0 THEN 'Database::' + DB_NAME()
      WHEN 1 THEN OBJECT_NAME(major_id)
      WHEN 3 THEN 'Schema::' + SCHEMA_NAME(major_id) END AS 'Securable'
FROM sys.database_permissions dp
WHERE class IN (0, 1, 3)
AND minor_id = 0
AND user_name(grantee_principal_id) not in ( 'public' )
AND permission_name not in ( 'CONNECT' ) 
order by 1
; 

-- db sys permission and db obj permission for all db
EXECUTE master.sys.sp_MSforeachdb '
use [?];
SELECT
    @@ServerName ServerName
  , db_name() db_name
  , USER_NAME(grantee_principal_id) AS ''User''
  , state_desc AS ''Permission''
  , permission_name AS ''Action''
  , CASE class
      WHEN 0 THEN ''Database::'' + DB_NAME()
      WHEN 1 THEN OBJECT_NAME(major_id)
      WHEN 3 THEN ''Schema::'' + SCHEMA_NAME(major_id) END AS ''Securable''
FROM sys.database_permissions dp
WHERE class IN (0, 1, 3)
AND minor_id = 0
AND user_name(grantee_principal_id) not in ( ''public'' )
AND permission_name not in ( ''CONNECT'' ) 
order by 1,2,3,4,5
;
'

-- list single user server and db permission

-- username, pull server info

-- declare @myusername sysname
declare @myusername nvarchar 

set @myusername = 'Ignite'
select @@servername servername,
name username, type_desc,is_disabled,default_database_name 
from sys.server_principals where name = @myusername;

SELECT @@servername servername,
SP.[name] AS 'Login' , SPerm.state_desc + ' ' + SPerm.permission_name AS 'ServerPermission'
FROM sys.server_principals SP
  JOIN sys.server_permissions SPerm
    ON SP.principal_id = SPerm.grantee_principal_id
where SP.[name]=@myusername;

SELECT 
@@servername servername,
sp2.name
FROM sys.server_principals sp2
left outer JOIN sys.server_role_members srm2 ON srm2.role_principal_id = sp2.principal_id
where srm2.member_principal_id in (select sid from sys.server_principals where name = @myusername );

-- username, pull database info
EXECUTE master.sys.sp_MSforeachdb '
declare @myusername sysname
set @myusername = ''Ignite''
use [?];
SELECT
    @@ServerName ServerName
  , db_name() db_name
  , USER_NAME(grantee_principal_id) AS ''User''
  , state_desc AS ''Permission''
  , permission_name AS ''Action''
  , CASE class
      WHEN 0 THEN ''Database::'' + DB_NAME()
      WHEN 1 THEN OBJECT_NAME(major_id)
      WHEN 3 THEN ''Schema::'' + SCHEMA_NAME(major_id) END AS ''Securable''
FROM sys.database_permissions dp
WHERE class IN (0, 1, 3)
AND minor_id = 0
AND user_name(grantee_principal_id) not in ( ''public'' )
AND permission_name not in ( ''CONNECT'' ) 
AND USER_NAME(grantee_principal_id) COLLATE DATABASE_DEFAULT =@myusername COLLATE DATABASE_DEFAULT
order by 1,2,3,4,5
;
'

EXECUTE master.sys.sp_MSforeachdb '
use [?];
declare @myusername sysname
set @myusername = ''Ignite''
select @@servername servername, db_name() db_name, * from sys.database_principals where name=@myusername;
select @@servername servername, db_name() db_name, * from sys.sql_logins where name=@myusername;
select @@servername servername, db_name() db_name, * from sys.sysusers where name=@myusername;
select @@servername servername, db_name() db_name, c.name,p.name 
from sys.database_role_members rm, sys.database_principals p,sys.database_principals c
 where rm.member_principal_id in 
( select principal_id from sys.database_principals 
  where name COLLATE DATABASE_DEFAULT = @myusername COLLATE DATABASE_DEFAULT 
)
 and rm.role_principal_id = p.principal_id
 and rm.member_principal_id = c.principal_id
 ;
'

start sql server with -m, then add sysadmin

EXEC sp_addsrvrolemember 'MYDOMAINNAME\MYUSERNAME', 'sysadmin';  
GO  

top
top   prev   next  
draft
sp_helprotect [ [ @name = ] 'object_statement' ] 
     [ , [ @username = ] 'security_account' ] 
     [ , [ @grantorname = ] 'grantor' ] 
     [ , [ @permissionarea = ] 'type' ]

EXEC sp_helprotect 'titles'; --> lists the permissions for the titles table
EXEC sp_helprotect NULL, 'Judy';  --> lists all permissions that user Judy has in the current database.

EXEC sp_helprotect NULL, NULL, 'Judy';
EXEC sp_helprotect NULL, NULL, dbo;
EXEC sp_helprotect @grantorname = 'dbo';

EXEC sp_helprotect NULL, NULL, NULL, 's'; 
EXEC sp_helprotect @name = 'CREATE TABLE';

top
top   prev   next  
-- at server instance level
EXEC xp_logininfo 'mywindowdomain\mywindowuser'
select * from sys.sql_logins;
select * from sys.server_principals;

declare @myusername sysname
set @myusername= N'nclmiami\shpnt_DB_RO'
select 'CREATE LOGIN [' + name + '] ' + 
case when [TYPE] = 'G' then 'FROM WINDOWS ' 
     when [TYPE] = 'U' then 'FROM WINDOWS ' 
     when [TYPE] = 'S' then 'password ' END +
'WITH DEFAULT_DATABASE=[' + default_database_name + '],' +
'DEFAULT_LANGUAGE=[' + default_language_name + ']' cmd 
 from sys.server_principals pp where name = @myusername  
union all 
select 
convert(varchar(255),pm.state_desc) + ' ' + 
convert( varchar(255),pm.permission_name) + ' TO ' + 
pp.name collate DATABASE_DEFAULT + ';' cmd  
from 
sys.server_permissions pm, 
sys.server_principals pp 
where 
    pp.name = @myusername 
and ( not (pm.state = 'G' and pm.type = 'COSQ' ))
and pm.grantee_principal_id = pp.principal_id
union all 
select 'grant ' + ppr.name + ' to ' + pp.name + ';' cmd 
from sys.server_role_members rm, sys.server_principals pp,
sys.server_principals ppr
where 
    pp.name = @myusername
and rm.member_principal_id = pp.principal_id
and rm.role_principal_id = ppr.principal_id;

-- database level
sp_helpuser N'nclmiami\shpnt_DB_RO'

declare @myusername sysname
set @myusername= N'nclmiami\shpnt_DB_RO'
SELECT	'use ' + db_name() + CHAR(13) + CHAR(10) + 'GO' + + CHAR(13) + CHAR(10) 
        + 'exec sp_addrolemember ' + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') 
	+ ',' + SPACE(1) +  QUOTENAME(USER_NAME(rm.member_principal_id), '''') 
        + CHAR(13) + CHAR(10) + 'GO' + + CHAR(13) + CHAR(10)  cmd 
FROM	sys.database_role_members AS rm
WHERE	USER_NAME(rm.member_principal_id) = @myusername
ORDER BY 1 ASC;

EXEC sp_MSforeachdb '\
USE ? \
declare @myusername sysname \
set @myusername= N''nclmiami\shpnt_DB_RO'' \
SELECT	''use '' + db_name() + CHAR(13) + CHAR(10) + ''GO'' + + CHAR(13) + CHAR(10) + ''exec sp_addrolemember '' + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''''''') \
	+ '','' + SPACE(1) +  QUOTENAME(USER_NAME(rm.member_principal_id), '''''''') \
	+ CHAR(13) + CHAR(10) + ''GO'' + + CHAR(13) + CHAR(10)  cmd \
FROM	sys.database_role_members AS rm \
WHERE	USER_NAME(rm.member_principal_id) = @myusername \
ORDER BY 1 ASC'


top
top   prev   next  

Get Server Role

-- server role
SELECT 'grant ' + SP2.[name] + ' TO [' + SP1.[name] + '];' AS 'CMD'
FROM sys.server_principals SP1
  JOIN sys.server_role_members SRM
    ON SP1.principal_id = SRM.member_principal_id
  JOIN sys.server_principals SP2
    ON SRM.role_principal_id = SP2.principal_id
ORDER BY SP1.[name], SP2.[name]; 

-- server permission
SELECT convert(varchar(255),  
       SPerm.state_desc + ' ' 
	   + SPerm.permission_name ) collate DATABASE_DEFAULT + ' TO [' 
	   +  convert(varchar(255),SP.[name]) collate DATABASE_DEFAULT + '];' AS CMD
FROM sys.server_principals SP
  JOIN sys.server_permissions SPerm
    ON SP.principal_id = SPerm.grantee_principal_id
where 
not (SPerm.state = 'G' and SPerm.type = 'COSQ') 
ORDER BY 1
;
top
top   prev   next  

transaction log

Transaction log
DBCC SQLPERF

DBCC SQLPERF(LOGSPACE);
GO

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
DBCC SQLPERF("sys.dm_os_latch_stats",CLEAR);

DBCC LOGINFO
DBCC LOGINFO(N'logfilename');
GO
-- -------------------
# Without Log Truncation
BACKUP WITH COPY_ONLY
BACKUP LOG database_name TO  WITH COPY_ONLY
BACKUP DATABASE database_name TO  WITH COPY_ONLY 

# log truncation
BACKUP LOG WITH NO_LOG
BACKUP LOG WITH TRUNCATE_ONLY
BACKUP LOG DBUtil WITH NO_LOG

Shrink Log File (BACKUP LOG WITH TRUNCATE_ONLY; DBCC SHRINKFILE (db_name ,0,TRUNCATEONLY)

Backup Log (BACKUP WITH NO_LOG)
-- -----------------
DBCC SHRINKFILE (nologtest_log, 2);
GO

SELECT log_reuse_wait_desc FROM sys.databases WHERE NAME = 'my_db_name';
GO
top
top   prev   next  

Transaction

DBCC OPENTRAN
top
top   prev   next  

Index and Table Rebuild And Reorganization

-- 30 percentage framentation, rebuild, between 5 percent and 30 percent reorganiztion
select ' print ''begin'''
union all
select ' print ''end'''
union all
SELECT 
-- '-- ' xxx
-- --                     ps.database_id AS 'databaseID'
-- --                   , QUOTENAME(DB_NAME(ps.database_id)) AS 'databaseName'
-- --                   , ps.[object_id] AS 'objectID'
                     -- ,object_name(ps.[object_id]) AS 'objectName'--
-- --                  , ps.index_id AS 'indexID'
--                    , i.name AS 'indexName'
-- --                    , ps.partition_number AS 'partitionNumber'
                    --, SUM(ps.avg_fragmentation_in_percent) AS 'fragmentation'
                    -- , SUM(ps.page_count) AS 'page_count'
                    -- , os.range_scan_count
                    -- , GETDATE() AS 'scanDate',
'alter index ' + i.name + ' on ' + object_schema_name(ps.object_id) + '.' + object_name(ps.[object_id]) + ' rebuild;' cmd 
                FROM sys.dm_db_index_physical_stats(DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT) AS ps
                JOIN sys.dm_db_index_operational_stats(DEFAULT,DEFAULT,DEFAULT,DEFAULT) AS os
                    ON ps.database_id = os.database_id
                    AND ps.[object_id] = os.[object_id]
                    AND ps.index_id = os.index_id
                    AND ps.partition_number = os.partition_number
                    LEFT OUTER JOIN sys.indexes AS i ON i.object_id = ps.object_id
                                            AND i.index_id = ps.index_id
                WHERE avg_fragmentation_in_percent >= 30 
                    AND ps.index_id > 0 -- ignore heaps
                    AND ps.page_count > 0
                    AND ps.index_level = 0 -- leaf-level nodes only, supports @scanMode
					AND DB_NAME(ps.database_id)='napa_log'
                GROUP BY ps.database_id 
                    , QUOTENAME(DB_NAME(ps.database_id)) 
                    , ps.[object_id]
                    , ps.index_id 
					, i.name
                    , ps.partition_number 
                    , os.range_scan_count
                OPTION (MAXDOP 2);
select 'print ''end''';

top
top   prev   next  

index reorganization

-- reorganization
select ' print ''begin'''
union all
select ' print ''end'''
union all
SELECT 
-- '-- ' xxx
-- --                     ps.database_id AS 'databaseID'
-- --                   , QUOTENAME(DB_NAME(ps.database_id)) AS 'databaseName'
-- --                   , ps.[object_id] AS 'objectID'
                     -- ,object_name(ps.[object_id]) AS 'objectName'--
-- --                  , ps.index_id AS 'indexID'
--                    , i.name AS 'indexName'
-- --                    , ps.partition_number AS 'partitionNumber'
                    --, SUM(ps.avg_fragmentation_in_percent) AS 'fragmentation'
                    -- , SUM(ps.page_count) AS 'page_count'
                    -- , os.range_scan_count
                    -- , GETDATE() AS 'scanDate',
'alter index ' + i.name + ' on ' + object_schema_name(ps.object_id) + '.' + object_name(ps.[object_id]) + ' REORGANIZE;' cmd 
                FROM sys.dm_db_index_physical_stats(DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT) AS ps
                JOIN sys.dm_db_index_operational_stats(DEFAULT,DEFAULT,DEFAULT,DEFAULT) AS os
                    ON ps.database_id = os.database_id
                    AND ps.[object_id] = os.[object_id]
                    AND ps.index_id = os.index_id
                    AND ps.partition_number = os.partition_number
                    LEFT OUTER JOIN sys.indexes AS i ON i.object_id = ps.object_id
                                            AND i.index_id = ps.index_id
                WHERE avg_fragmentation_in_percent < 30 
                    AND avg_fragmentation_in_percent >= 5
                    AND ps.index_id > 0 -- ignore heaps
                    AND ps.page_count > 0
                    AND ps.index_level = 0 -- leaf-level nodes only, supports @scanMode
					AND DB_NAME(ps.database_id)='napa_log'
                GROUP BY ps.database_id 
                    , QUOTENAME(DB_NAME(ps.database_id)) 
                    , ps.[object_id]
                    , ps.index_id 
					, i.name
                    , ps.partition_number 
                    , os.range_scan_count
                OPTION (MAXDOP 2);
select 'print ''end''';

top
top   prev   next  
sys.dm_db_index_physical_stats;

ALTER TABLE table1 rebuild;

ALTER INDEX index_name ON table_name REORGANIZE

Reorganize Index
ALTER INDEX index_name ON table_name REORGANIZE WITH ( LOB_COMPACTION = ON );
ALTER INDEX index_name ON table_name REORGANIZE WITH ( LOB_COMPACTION = OFF );

USE [AdventureWorks]
GO
ALTER INDEX [PK_AWBuildVersion_SystemInformationID] ON [dbo].[AWBuildVersion] REORGANIZE WITH ( LOB_COMPACTION = ON )
GO

ALTER INDEX index_name ON table_name REORGANIZE WITH ( LOB_COMPACTION = ON )

ALTER INDEX index_name ON table_name REORGANIZE WITH ( LOB_COMPACTION = OFF )

ALTER  INDEX  ALL ON table_name  REBUILD ;

ALTER  INDEX  index_name  ON  table_name  REBUILD  PARTITION  =  ALL  
WITH ( PAD_INDEX              =  OFF,  
       STATISTICS_NORECOMPUTE =  OFF,  
       ALLOW_ROW_ LOCKS       =  ON,  
       ALLOW_PAGE_LOCKS       =  ON,  
       ONLINE                 =  OFF,  
       SORT_IN_TEMPDB         =  OFF  )

ALTER INDEX index_name ON table_name REBUILD PARTITION = ALL 
WITH ( 
PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON, 
ONLINE = OFF, 
SORT_IN_TEMPDB= OFF );
go

ALTER INDEX [MyIdxName] ON [dbo].[MyTabNme] 
REBUILD 
PARTITION=ALL 
WITH 
(
PAD_INDEX              =OFF,
STATISTICS_NORECOMPUTE =OFF,
ALLOW_ROW_LOCKS        =ON,
ALLOW_PAGE_LOCKS       =ON,
ONLINE                 =OFF,
SORT_IN_TEMPDB         =OFF
)
GO
top
top   prev   next  
USE [AdventureWorks2012] ALTER INDEX [PK_Product_ProductID] ON [Production].[Product] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = ROW)
top
top   prev   next  

Index and Table compression

table and index compression

EXEC sp_estimate_data_compression_savings 'dbo','table1',null,null,'row'
EXEC sp_estimate_data_compression_savings 'dbo','table1',null,null,'page'

EXEC sp_estimate_data_compression_savings 'Production','Product',1,null,'ROW'
ALTER TABLE table1 REBUILD WITH (data_compression = row)
ALTER TABLE table1 REBUILD WITH (data_compression = page)
ALTER TABLE TestCompression REBUILD WITH (DATA_COMPRESSION = NONE);

top
top   prev   next  

SQL Trace Server Side and SQL Profiler (Client Side)

1 

2 

3 

4 

5 

top


top   prev   next  
Running a Server-Side Trace on a SQL Server DB Instance

Writing scripts to create a server-side trace can be complex and is beyond the scope of this document. This section contains sample scripts that you can use as examples. As with a client-side trace, the goal is to create a workload file or trace table that you can open using the Database Engine Tuning Advisor.

The following is an abridged example script that starts a server-side trace and captures details to a workload file. The trace initially saves to the file RDSTrace.trc in the D:\RDSDBDATA\Log directory and rolls-over every 100 MB so subsequent trace files are named RDSTrace_1.trc, RDSTrace_2.trc, etc.

DECLARE @file_name NVARCHAR(245) = 'D:\RDSDBDATA\Log\RDSTrace';
DECLARE @max_file_size BIGINT = 100;
DECLARE @on BIT = 1
DECLARE @rc INT
DECLARE @traceid INT

EXEC @rc = sp_trace_create @traceid OUTPUT, 2, @file_name, @max_file_size
IF (@rc != 0) BEGIN
   EXEC sp_trace_setevent @traceid, 10, 1, @on
   EXEC sp_trace_setevent @traceid, 10, 2, @on
   EXEC sp_trace_setevent @traceid, 10, 3, @on
  .
   EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler'
   EXEC sp_trace_setstatus @traceid, 1
   END

The following example is a script that stops a trace. 
Note that a trace created by the previous script continues 
to run until you explicitly stop the trace or the process runs out of disk space.

DECLARE @traceid INT
SELECT @traceid = traceid FROM ::fn_trace_getinfo(default) 
WHERE property = 5 AND value = 1 AND traceid <> 1 

IF @traceid IS NOT NULL BEGIN
   EXEC sp_trace_setstatus @traceid, 0
   EXEC sp_trace_setstatus @traceid, 2
END
            

You can save server-side trace results to a database table and 
use the database table as the workload for the Tuning Advisor 
by using the fn_trace_gettable function. 
The following commands load the results of all files named RDSTrace.trc 
in the D:\rdsdbdata\Log directory, 
including all rollover files like RDSTrace_1.trc, 
into a table named RDSTrace in the current database:

SELECT * INTO RDSTrace
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace.trc', default);                    
                

To save a specific rollover file to a table, for example the RDSTrace_1.trc file, 
specify the name of the rollover file and substitute 1 instead of default as the 
last parameter to fn_trace_gettable.

SELECT * INTO RDSTrace_1
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace_1.trc', 1);                    
                    

Running Tuning Advisor with a Trace

Once you create a trace, either as a local file or as a database table, 
you can then run Tuning Advisor against your RDS instance. 
Microsoft includes documentation on using the Database Engine 
Tuning Advisor in MSDN. Using Tuning Advisor with RDS is 
the same process as when working with a standalone, 
remote SQL Server instance. 
You can either use the Tuning Advisor UI on your client machine 
or use the dta.exe utility from the command line. 
In both cases, you must connect to the RDS DB Instance using 
the endpoint for the DB Instance and provide your master user 
name and master user password when using Tuning Advisor.

The following code example demonstrates using the dta.exe command line utility 
against an RDS DB Instance with an endpoint of 
dta.cnazcmklsdei.us-east-1.rds.amazonaws.com. 
The example includes the master user name admin and the master user password test, 
the example database to tune is named RDSDTA and the input workload is a trace file 
on the local machine named C:\RDSTrace.trc. The example command line code also 
specifies a trace session named RDSTrace1 and specifies output files to the 
local machine named RDSTrace.sql for the SQL output script, RDSTrace.txt for a result file, 
and RDSTrace.xml for an XML file of the analysis. There is also 
an error table specified on the RDSDTA database named RDSTraceErrors.

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -if C:\RDSTrace.trc -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors

top
top   prev   next  
To run a client-side trace on a SQL Server DB instance

    Start SQL Server Profiler. It is installed in the Performance Tools folder of your SQL Server instance folder. You must load or define a trace definition template to start a client-side trace.

    In the SQL Server Profiler File menu, click New Trace. In the Connect to Server dialog box, enter the DB Instance endpoint, port, master user name, and password of the database you would like to run a trace on.

    In the Trace Properties dialog box, enter a trace name and choose a trace definition template. A default template, TSQL_Replay, ships with the application. You can edit this template to define your trace. Edit events and event information under the Events Selection tab of the Trace Properties dialog box. For more information about trace definition templates and using the SQL Server Profiler to specify a client-side trace see the documentation in MSDN.

    Start the client-side trace and watch SQL queries in real-time as they execute against your DB Instance.

    Select Stop Trace from the File menu when you have completed the trace. Save the results as a file or as a trace table on you DB Instance.


top   prev   next  

read a text file or windows command result in sql server

CREATE TABLE #cmd_result (output varchar(8000))
INSERT #cmd_result
EXEC master.dbo.xp_cmdshell 'TYPE C:\BOOT.INI'
SELECT * FROM #cmd_result
DROP TABLE #cmd_result

top   prev   next  

sp_spaceused at database level

USE Northwind
EXEC dbo.sp_spaceused


# exec sp_spaceused all databases
EXEC sp_msforeachdb @command1="use [?]  exec sp_spaceused"
EXEC sp_msforeachdb @command1="use [?]; exec sp_spaceused"


USE pubs
EXEC Northwind..sp_spaceused

top   prev   next  

sp_spaceused at table/object level

EXEC sys.sp_spaceused N'dbo.mytable_name', @updateusage = N'TRUE';
GO


create table #t
(
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50)
);

EXEC sp_MSforeachtable @command1="insert into #t EXEC sp_spaceused '?'"

select * from #t order by convert(int, substring(data, 1, len(data)-3)) desc;

select name, rows, reserved,DATA,index_size,unused 
from #t a 
order by 
convert(int, substring(a.data, 1, len(a.data)-3)) + 
convert(int, substring(a.index_size, 1, len(a.index_size)-3)) desc;

drop table #t



top
top   prev   next  

Statistics

stats, rebuild, reorg

DBCC SHOW_STATISTICS("my_tablename", my_indexname);
GO
DBCC SHOW_STATISTICS ("my_schemaname.my_tablename", my_indexname) WITH HISTOGRAM;
GO
1 

2 

sp_updatestats

USE mydb;
GO
EXEC sp_updatestats; 
GO

sp_autostats
USE AdventureWorks2012;
GO
EXEC sp_autostats 'Production.Product';  # show autostats setting
GO
USE AdventureWorks2012;
GO
EXEC sp_autostats 'Production.Product', 'ON'; # set stats
GO

USE AdventureWorks2012;
GO
EXEC sp_autostats 'Production.Product', 'OFF', AK_Product_Name; #disable autostats for an index
GO

1 

DROP STATISTICS 
-- Create the statistics groups.
USE AdventureWorks2012;
GO
CREATE STATISTICS VendorCredit
    ON Purchasing.Vendor (Name, CreditRating)
    WITH SAMPLE 50 PERCENT
CREATE STATISTICS CustomerTotal
    ON Sales.SalesOrderHeader (CustomerID, TotalDue)
    WITH FULLSCAN;
GO
DROP STATISTICS Purchasing.Vendor.VendorCredit, Sales.SalesOrderHeader.CustomerTotal;

top   prev   next  

SQL Server Transaction Programming Technique

USE tempdb
GO
drop proc testp
GO
CREATE PROC testp @var int
AS
CREATE TABLE #temp (k1 int identity)
IF @var=1
 ALTER TABLE #temp ADD c1 int
ELSE
 ALTER TABLE #temp ADD c1 varchar(2)
INSERT #temp DEFAULT VALUES
EXEC dbo.sp_executesql N'SELECT c1 FROM #temp'
GO
exec testp 2

top
top   prev   next  
USE master
IF OBJECT_ID('dbo.sp_created') IS NOT NULL
 DROP PROC dbo.sp_created
GO
CREATE PROC dbo.sp_created @objname sysname=NULL
/*
Object: sp_created
Description: Lists the creation date(s) for the specified object(s)
Usage: sp_created @objname="Object name or mask you want to display"
Returns: (None)
$Author: Khen $. Email: khen@khen.com
$Revision: 2 $
Example: sp_created @objname="myprocs%"
Created: 1999-08-01. $Modtime: 1/04/01 12:16a $.
*/
AS
IF (@objname IS NULL) or (@objname='/?') GOTO Help
SELECT name, crdate FROM sysobjects
WHERE name like @objname
RETURN 0
Help:
EXEC dbo.sp_usage @objectname='sp_created',
    @desc='Lists the creation date(s) for the specified object(s)',
    @parameters='@objname="Object name or mask you want to display"',
    @example='sp_created @objname="myprocs%"',
    @author='Ken Henderson',
    @email='khen@khen.com',
    @version='1', @revision='0',
    @datecreated='19990801', @datelastchanged='19990815'
RETURN -1
GO
USE Northwind
EXEC dbo.sp_created 'Order%'

top   prev   next  

Marked As system objects

USE master
GO
IF OBJECT_ID('dbo.sp_test') IS NOT NULL
 DROP PROC dbo.sp_test
GO
CREATE PROC dbo.sp_test AS
select 1
GO
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.sp_test'),'IsMSShipped') AS 'System Object?', status, status & 0xC0000000
FROM sysobjects WHERE NAME = 'sp_test'
GO
EXEC sp_MS_marksystemobject 'sp_test'
GO
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.sp_test'),'IsMSShipped') AS 'System Object?', status, status & 0xC0000000
FROM sysobjects WHERE NAME = 'sp_test'

top
top   prev   next  
John Kauffman
1 
2 

John.Kauffman@microsoft.com

1 
1 

1 

1 

1 

1 

1 

top   prev   next  

Change DB Owner

sp_changedbowner
1 
1 

EXEC sp_changedbowner 'Albert';
ALTER AUTHORIZATION ON DATABASE::ReplaceThisWithYourDatabaseName to sa;

table, view, schema, 
1 

top   prev   next  

Change DB to single User Mode

1 
1 
1 
1 

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where d.name = 'mydb';

Set a Database to Single-user Mode
database/properties/Options/Restrict Access/single/(Kill)Open Connections/Yes
USE master;
GO
ALTER DATABASE AdventureWorks2012 SET SINGLE_USER WITH ROLLBACK After 30;
GO
ALTER DATABASE AdventureWorks2012 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012 SET SINGLE_USER WITH NO_WAIT;
GO
ALTER DATABASE AdventureWorks2012 SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks2012 SET MULTI_USER;
GO
ALTER DATABASE 'DB NAME' SET MULTI_USER WITH ROLLBACK IMMEDIAT


-- Rename a database
1 
ALTER DATABASE AdventureWorks2012
Modify Name = Northwind ;
GO

Database Features And Task:
1 

top
top   prev   next  
sqlservercentral 

SELECT @@VERSION
go
SELECT @@version;
go

sys.dm_os_waiting_tasks;
sys.dm_os_wait_stats;

set statistics io on;



sqlcmd

-E: windows authentication

-- --------------------------------------
create endpoint

-- --------------------------------------
set showplan_all on
go

set showplan_all off
go

set statistics profile on
go

set statistics profile off
go
-- --------------------------------------

select top 10 ... ...

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

dtswizard --> Import and Export Data

-- ---------------------------------------
sqlcmd -s  -q "exec sp_databases"
sqlcmd -s localhost -q "exec sp_databases"
sqlcmd -s dbserver -u loginname -p password -q "exec sp_databases"
sqlcmd -s ./MSSQLSERVER -u loginname -p password -q "exec sp_databases" 



SELECT name FROM sys.databases;

exec sp_databases;
go

exec xp_logininfo;
go

EXEC xp_logininfo 'BUILTIN\Administrators'
go

SELECT * FROM sysfiles;
go

top   prev   next  

sp_who3

;WITH  
 XMLNAMESPACES 
     (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'   
             ,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan) 
SELECT
    SPID                = er.session_id
    ,Status             = ses.status
    ,[Login]            = ses.login_name
    ,Host               = ses.host_name
    ,BlkBy              = er.blocking_session_id
    ,DBName             = DB_Name(er.database_id)
    ,CommandType        = er.command
    ,SQLStatement       =
        SUBSTRING
        (
            qt.text,
            er.statement_start_offset/2,
            (CASE WHEN er.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset)/2
        )
    ,QueryPlan          = qp.query_plan 
    ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,qt.dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
    ,ElapsedMS          = er.total_elapsed_time
    ,CPUTime            = er.cpu_time
    ,IOReads            = er.logical_reads + er.reads
    ,IOWrites           = er.writes
    ,LastWaitType       = er.last_wait_type
    ,StartTime          = er.start_time
    ,Protocol           = con.net_transport
    ,transaction_isolation =
        CASE ses.transaction_isolation_level
            WHEN 0 THEN 'Unspecified'
            WHEN 1 THEN 'Read Uncommitted'
            WHEN 2 THEN 'Read Committed'
            WHEN 3 THEN 'Repeatable'
            WHEN 4 THEN 'Serializable'
            WHEN 5 THEN 'Snapshot'
        END
    ,ConnectionWrites   = con.num_writes
    ,ConnectionReads    = con.num_reads
    ,ClientAddress      = con.client_net_address
    ,Authentication     = con.auth_scheme
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) as qp
WHERE  er.session_id > 50
ORDER BY
    er.blocking_session_id DESC
    ,er.session_id

top   prev   next  

current running sql

;WITH  
 XMLNAMESPACES 
     (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'   
             ,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)
SELECT s.loginame, db_name(s.dbid) name, s.hostname, s.program_name, 
t.text Statement, p.query_plan QueryPlan, s.stmt_start, s.stmt_end, s.spid, 
CONVERT(smallint, s.waittype) waittype, s.lastwaittype, s.ecid, s.waittime, 
CONVERT(varchar(64), s.context_info) context_info, RTRIM(r.wait_resource) waitresource, 
s.blocked, r.statement_start_offset, r.statement_end_offset, 
r.start_time, q.plan_generation_num  
FROM 
master..sysprocesses AS s WITH(NOLOCK) LEFT OUTER JOIN 
sys.dm_exec_requests r ON r.session_id = s.spid LEFT OUTER JOIN 
sys.dm_exec_query_stats q ON q.plan_handle = r.plan_handle AND 
q.statement_start_offset = r.statement_start_offset AND 
q.statement_end_offset = r.statement_end_offset 
CROSS APPLY sys.dm_exec_sql_text(r.[plan_handle]) AS t 
CROSS APPLY sys.dm_exec_query_plan(r.[plan_handle]) AS p
WHERE (s.dbid<>0 AND s.cmd<>'AWAITING COMMAND' 
AND s.cmd NOT LIKE '%BACKUP%' 
AND s.cmd NOT LIKE '%RESTORE%' 
AND s.cmd NOT LIKE 'FG MONITOR%' 
AND s.hostprocess > '' 
AND s.spid>50 AND s.spid<>@@SPID)  
AND lastwaittype NOT IN ('SLEEP_TASK')

top   prev   next  

Missing Index

;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

top
top   prev   next  
DMV:
Clearing the wait statistics on a server.
DBCC SQLPERF('sys.dm_os_wait_stats', clear);
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

top
top   prev   next  
sys.dm_io_virtual_file_stats
sys.dm_exec_query_stats
sys.dm_os_wait_stats
sys.dm_os_waiting_tasks
sys.dm_exec_requests

sys.dm_exec_query_stats 
sys.dm_exec_sql_text

top
top   prev   next  
SELECT DISTINCT
wt.wait_type
FROM sys.dm_os_waiting_tasks AS wt
JOIN sys.dm_exec_sessions AS s ON wt.session_id = s.session_id
WHERE s.is_user_process = 0;

top
top   prev   next  
SELECT TOP 10
wait_type ,
max_wait_time_ms wait_time_ms ,
signal_wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0 -- remove zero wait_time
AND wait_type NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC;

top
top   prev   next  
SELECT DB_NAME(vfs.database_id) AS database_name ,
vfs.database_id ,
vfs.FILE_ID ,
io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency ,
io_stall_write_ms / NULLIF(num_of_writes, 0)
AS avg_write_latency ,
io_stall / NULLIF(num_of_reads + num_of_writes, 0)
AS avg_total_latency ,
num_of_bytes_read / NULLIF(num_of_reads, 0)
AS avg_bytes_per_read ,
num_of_bytes_written / NULLIF(num_of_writes, 0)
AS avg_bytes_per_write ,
vfs.io_stall ,
vfs.num_of_reads ,
vfs.num_of_bytes_read ,
vfs.io_stall_read_ms ,
vfs.num_of_writes ,
vfs.num_of_bytes_written ,
vfs.io_stall_write_ms ,
size_on_disk_bytes / 1024 / 1024. AS size_on_disk_mbytes ,
physical_name
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id
AND vfs.FILE_ID = mf.FILE_ID
ORDER BY avg_total_latency DESC
;

top
top   prev   next  
SELECT TOP 10
execution_count ,
statement_start_offset AS stmt_start_offset ,
sql_handle ,
plan_handle ,
total_logical_reads / execution_count AS avg_logical_reads ,
total_logical_writes / execution_count AS avg_logical_writes ,
total_physical_reads / execution_count AS avg_physical_reads ,
t.text
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
ORDER BY avg_physical_reads DESC
;

top
top   prev   next  
SELECT SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,
( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))
/ SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 )
AS PercentageSignalWaitsOfTotalTime
FROM sys.dm_os_wait_stats;

top
top   prev   next  
SELECT TOP ( 10 )
wait_type ,
waiting_tasks_count ,
( wait_time_ms - signal_wait_time_ms ) AS resource_wait_time ,
max_wait_time_ms ,
CASE waiting_tasks_count
WHEN 0 THEN 0
ELSE wait_time_ms / waiting_tasks_count
END AS avg_wait_time
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' -- remove eg. SLEEP_TASK and
-- LAZYWRITER_SLEEP waits
AND wait_type NOT LIKE 'XE%'
AND wait_type NOT IN -- remove system waits
( 'KSOURCE_WAKEUP', 'BROKER_TASK_STOP', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',
'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE',
'DBMIRROR_EVENTS_QUEUE', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE',
'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',
'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH' )
ORDER BY wait_time_ms DESC;

top
top   prev   next  
SELECT scheduler_id ,
current_tasks_count ,
runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255;

top
top   prev   next  
SP_CONFIGURE 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
-- Blocked Process Report event will fire repeatedly, every 10 seconds
SP_CONFIGURE 'blocked process threshold', 10 ;
GO
RECONFIGURE ;
GO
SP_CONFIGURE 'blocked process threshold', 0 ;
GO
RECONFIGURE ;
GO

top
top   prev   next  
The AUTO_UPDATE_STATISTICS database option
AUTO_UPDATE_STATISTICS_ASYNCHRONOUSLY database option to TRUE.

top
top   prev   next  
sp_configure 'default trace enabled', 0

top
top   prev   next  
SELECT name FROM sys.objects WHERE object_id = 1836025772;

SELECT CAST(name AS char(20)) AS Name, is_disabled
FROM sys.foreign_keys WHERE name = 'FK_Items_Boxes' ;

SELECT OBJECT_NAME(t.parent_id), te.type_desc
FROM sys.triggers AS t INNER JOIN sys.trigger_events AS te 
ON t.OBJECT_ID = te.OBJECT_ID
GROUP BY OBJECT_NAME(t.parent_id),te.type_descHAVING COUNT(*) > 1 ;

SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = 'Codes' AND TABLE_SCHEMA = 'dbo' );

top
top   prev   next  
SET NOCOUNT ON;

SET STATISTICS TIME ON ;

SET LANGUAGE 'us_english' ;

SET XACT_ABORT ON;
SET XACT_ABORT OFF ;

SET DEADLOCK_PRIORITY LOW ;

BEGIN CATCH ; 
PRINT 'Entering CATCH block' ; 
IF XACT_STATE () = 1 
    BEGIN ; 
       PRINT 'Transaction is committable' ; 
       COMMIT ; 
    END ; 
IF XACT_STATE () = -1 
     BEGIN ; 
      PRINT 'Transaction is not committable' ; 
      ROLLBACK ; 
    END ;
END CATCH ;
PRINT 'Ending batch';


top
top   prev   next  
SET ROWCOUNT 1;
SET ROWCOUNT 0;

top
top   prev   next  
CREATE DATABASE DW
WITH (
AUTOGROW = ON,
REPLICATED_SIZE = 50,
DISTRIBUTED_SIZE = 10000,
LOG_SIZE = 25
);

top
top   prev   next  
ALTER DATABASE Test SET READ_COMMITTED_SNAPSHOT ON;
GO
ALTER DATABASE Test SET READ_COMMITTED_SNAPSHOT OFF ;
GO
ALTER DATABASE Test SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
Go
SET TRANSACTION ISOLATION LEVEL SNAPSHOT ;
Go
SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;
Go

SET DEADLOCK_PRIORITY HIGH ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

top
top   prev   next  
EXEC msdb.dbo.sp_start_job @job_id=N'cb73ea96-9a96-49fe-ada9-a70a941f9fb9';
go
SELECT * FROM msdb.dbo.sysjobs_view;
go

declare 
@dt datetime 
select @dt = cast(N'2009-07-22T14:19:13' asdatetime)
EXEC msdb.dbo.sp_delete_backuphistory @dt
GO
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2009-07-22T14:19:13'
GO
EXECUTE msdb..sp_maintplan_delete_log null,null,'2009-07-22T14:19:13'

EXECUTE master.dbo.xp_delete_file 1,N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log',N'txt',N'2009-07-23T12:55:05'

sp_delete_backuphistory
sp_purge_jobhistory
sp_maintplan_delete_log

-- remove old data from msdb:
sp_delete_backuphistory
sp_purge_jobhistory
sp_maintplan_delete_log


top
top   prev   next  
BACKUP DATABASE [AdventureWorks] 
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorks_backup_2009_08_19_145336_3160000.bak'
WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks_backup_2009_08_19_145336_3150000', 
SKIP, REWIND, NOUNLOAD, STATS = 10;
go
BACKUP LOG [AdventureWorks] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorks_backup_2009_08_20_111623_3462370.trn' 
WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks_backup_2009_08_20_111623_3462370', 
SKIP, REWIND, NOUNLOAD, STATS = 10
go

top
top   prev   next  
-- Check Database Integrity
-- check the physical integrity of their databases
DBCC CHECKDB

DBCC CHECKDB('database_name')WITH NO_INFOMSGS
DBCC CHECKDB ('DATABASE_NAME') WITH NO_INFOMSGS, ALL_ERRORMSGS

DBCC CHECKDB('database_name', NOINDEX)

DBCC CHECKDB('database_name') WITH NO_INFOMSGS;
go
DBCC CHECKDB ('DATABASE_NAME') WITH NO_INFOMSGS, ALL_ERRORMSGS;
go
DBCC CHECKDB('database_name', NOINDEX)
go

USE [AdventureWorks]
GO
DBCC CHECKDB(N''AdventureWorks'') WITH NO_INFOMSGS
GO
-- USE [AdventureWorks]
-- GO
-- DBCC CHECKDB(N''AdventureWorks'') WITH NO_INFOMSGS
-- GO

-- Shrink Database

-- Check Database Integrity
1 

1 


top
top   prev   next  
AUTO_CREATE_STATISTICS
AUTO_UPDATE_STATISTICS

UPDATE STATISTICS 
UPDATE STATISTICS table_name WITH FULLSCAN;

UPDATE STATISTICS 
or sp_updatestats commands

UPDATE STATISTICS table_name WITH FULLSCAN;

use [AdventureWorks]
GO
UPDATE STATISTICS [dbo].[AWBuildVersion]
WITH FULLSCAN
GO
use [AdventureWorks]|
GO
UPDATE STATISTICS [dbo].[DatabaseLog]
WITH FULLSCAN
GO

sp_updatestats 
top
top   prev   next  
How to: Add Data or Log Files to a Database (SQL Server Management Studio)