EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'Database Name' GO USE [master] GO DROP DATABASE [Database Name] GO
Display sys procedures in SQL Server
SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
FROM sys.all_objects
WHERE type = 'P'
AND is_ms_shipped = 1
Check SQL Server Backups
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'
WHEN 'I' THEN 'Differential Database'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential Partial'
ELSE msdb..backupset.type
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() - 2)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date desc
Oracle hash values for Passwords
set long 150 set linesize 150 set longchunksize 150 select 'alter user '||username||' identified by values '||REGEXP_SUBSTR(DBMS_METADATA.get_ddl
('USER',USERNAME), '''[^'']+''')||';' from dba_users
SQL Server Performance Troubleshooting Methodology
Display the SQL statement that a Linux Process is Executing
-- get the process id from Linux (use top or ps) -- the process id is spid in v$process select sql_id from v$session where paddr in (select addr from v$process where spid in (4567)) -- put the sql_id from v$session into v$sqlarea/v$sql_text/v$sql select sql_text from v$sqlarea where sql_id = 'ap3qms77rt67k' -- this will display the sql that the process is executing -- In one query select sql_text from v$sqlarea where sql_id = (select sql_id from v$session where paddr in (select addr from v$process where spid = 1820) );
Powershell – Recursive Search for Strings in Files
To search for a string (grep) in files recursively with Powershell (example):
gci -recurse -include *.sql|%{gc $_} | %{$_|select-string "dba"}
To get the filename(s):
gci -recurse -include *.txt|%{$Filename=$_;gc $_} | %{$res=$_|select-string "dba";if ($res) {Write-Host "$Filename"}}
SQL Server – Translate object_ids/object_names
-- Get object_id from object name
select OBJECT_ID('[dbo].[testobject]')
-- get object_name from object id
select OBJECT_NAME(213575799)
SQL Server – Display Running Tasks
Use this query in conjunction with sp_who to display running tasks and determine whether there are sessions blocking other sessions, and who is executing the query (sp_who)
SELECT command,
r.session_id, r.blocking_session_id,
s.text,
start_time,
percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
--WHERE r.command like ('%DBCC%')