SQL Server Default Trace

To check the default trace is enabled or not:

SELECT * FROM sys.configurations WHERE configuration_id = 1568

If not enabled then execute the following code to enable:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO

To find the path of trace file:

SELECT * FROM ::fn_trace_getinfo(0)

Ref: http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

Configure alert for 80% log full

Job name from sp_who2

select * from msdb..sysjobs where

job_id = convert(uniqueidentifier,0x20981069DD3DE5F4598433E36C60CF26)

Orphaned users

DECLARE @SQLString VARCHAR(6300)
SELECT @SQLString = ''
SELECT @SQLString = @SQLString + 'EXEC [dbo].[sp_Change_Users_Login] ''Update_One'','''+[Name]+''','''+[Name]+''' '
FROM [dbo].[SysUsers]WHERE [IsSqlUser] = 1 AND (SId IS NOT NULL AND SId <> 0x0) AND SUSER_SNAME(SId) IS NULL
IF LEN(LTRIM(RTRIM(@SQLString)))>0
BEGIN
PRINT (@SQLString)
END
GO