Oracle archivelog full

-- Measure DB_RECOVERY_USAGE

SELECT name

, ceil( space_limit / 1024 / 1024) SIZE_M

, ceil( space_used  / 1024 / 1024) USED_M

, decode( nvl( space_used, 0),

 0, 0

 , ceil ( ( space_used / space_limit) * 100) ) PCT_USED

FROM v$recovery_file_dest

ORDER BY name

/

SELECT * FROM V$RECOVERY_FILE_DEST;

SELECT * FROM V$RECOVERY_AREA_USAGE;

 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 150G SCOPE=BOTH

  ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 50G SCOPE=BOTH

  ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 100G SCOPE=BOTH

--$ rman target=/

RMAN> CrossCheck backup ;

RMAN> Delete Obsolete ;

RMAN>delete expired archivelog all;

RMAN> crosscheck archivelog all; 

SQL Quick count of tables

select a.name tablename , b.rows 

from sysobjects as a inner join sysindexes as b on a.id = b.id

where (a.xtype = 'u') and (b.indid in (0,1))

  and a.name in ('drop_T_Shippment',

                             'drop_T_TDHeader',

                             'drop_T_TDItem',

                             'drop_T_SFCompTrace')

order by b.rows desc;

--

SELECT

      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]

      , SUM(sPTN.Rows) AS [RowCount]

FROM 

      sys.objects AS sOBJ

      INNER JOIN sys.partitions AS sPTN

            ON sOBJ.object_id = sPTN.object_id

WHERE

      sOBJ.type = 'U'

      AND sOBJ.is_ms_shipped = 0x0

      AND index_id < 2 -- 0:Heap, 1:Clustered

GROUP BY 

      sOBJ.schema_id

      , sOBJ.name

ORDER BY [TableName]

GO

SQL SERVER LIST FOREIGN KEYS

 SELECT

    f.name constraint_name
   ,OBJECT_NAME(f.parent_object_id) referencing_table_name
   ,COL_NAME(fc.parent_object_id, fc.parent_column_id) referencing_column_name
   ,OBJECT_NAME (f.referenced_object_id) referenced_table_name
   ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) referenced_column_name
   ,delete_referential_action_desc
   ,update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.object_id = fc.constraint_object_id
ORDER BY f.name

SQL SERVER RESTORE ERROR Invalid object name 'MSreplication_subscriptions'.

 --Fix

Go back to the original and then disable the subscription

right click and create script

--DISABLE

/****** Begin: Script to be run at Subscriber ******/

use [Worklog]

exec sp_dropmergepullsubscription @publisher = N'PHAILORTSQL05', @publisher_db = N'Worklog', @publication = N'Worklog'

GO

/****** End: Script to be run at Subscriber ******/

/****** Begin: Script to be run at Publisher ******/

/*use [Worklog]

exec sp_dropmergesubscription @subscription_type = N'pull', @publication = N'Worklog', @subscriber = N'HKACPKWUSQL01', @subscriber_db = N'Worklog'

*/

/****** End: Script to be run at Publisher ******/

--ENABLE

-- Adding the merge pull subscription

/****** Begin: Script to be run at Subscriber ******/

use [Worklog]

exec sp_addmergepullsubscription @publisher = N'PHAILORTSQL05', @publication = N'Worklog', @publisher_db = N'Worklog', @subscriber_type = N'Local', @subscription_priority = 0, @description = N'', @sync_type = N'Automatic'

exec sp_addmergepullsubscription_agent @publisher = N'PHAILORTSQL05', @publisher_db = N'Worklog', @publication = N'Worklog', @distributor = N'PHAILORTSQL05', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = N'', @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = N'ecp\reynaldoglennangeles', @job_password = null, @publisher_security_mode = 1, @publisher_login = N'', @publisher_password = N'', @use_interactive_resolver = N'False', @dynamic_snapshot_location = N'', @use_web_sync = 0

GO

/****** End: Script to be run at Subscriber ******/

/****** Begin: Script to be run at Publisher ******/

/*use [Worklog]

exec sp_addmergesubscription @publication = N'Worklog', @subscriber = N'HKACPKWUSQL01', @subscriber_db = N'Worklog', @subscription_type = N'pull', @subscriber_type = N'local', @subscription_priority = 0, @sync_type = N'Automatic'

*/

/****** End: Script to be run at Publisher ******/

sql server display all database

 sp_databases

SQL server list database backup

 -Database Backups for all databases For Previous Week 

--------------------------------------------------------------------------------- 
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 

SQL SERVER EMAIL

 EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Kosmos Mailer'

   ,@recipients = 'dexter.velasco@aei.com'

   ,@subject = 'Email from SQL Server'

   ,@body = 'This is my First Email sent from SQL Server :)'

   ,@importance ='HIGH'

GO

SELECT * FROM msdb.dbo.sysmail_unsentitems

SELECT * FROM msdb.dbo.sysmail_sentitems

SELECT * FROM msdb.dbo.sysmail_faileditems

SQL SERVER - list all tables

 SELECT

  *
FROM
  INFORMATION_SCHEMA.TABLES;
GO

ITAMS refresh

 EXEC sp_change_users_login 'Report'

EXEC sp_change_users_login 'Auto_Fix', 'itamsuser';

EXEC sp_change_users_login 'Auto_Fix', 'sa_web';