Log Errors in SQL Server(Management\SQL Server Log)

Log Errors in SQL Server(Management\SQL Server Log)

                                          In SQL Server if you need to log all errors ( Or any given errors ) then there is no easy way to do it using graphical interface. Following SQL script will help you to log all errors in SQL Server log file. This script is MSSQL configuration script installation level. After execute this it will configure all errors log to MSSQL log files. Note: This is not recommended to execute in Production server or very busy low performance test db servers.

(B’cos after install this script it will log all errors, it cost extra CPU and disk space)

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 10
GO
--EXEC sp_altermessage 8134, 'WITH_LOG', 'true';
GO
DECLARE @message_id nvarchar(20);
DECLARE SYS_messages CURSOR FOR 
SELECT CAST(message_id AS nvarchar(20)) FROM sys.messages WHERE language_id=1033 ORDER BY message_id; 
OPEN SYS_messages; 
FETCH NEXT FROM SYS_messages INTO @message_id; 
WHILE @@FETCH_STATUS = 0  BEGIN
PRINT @message_id
EXEC sp_altermessage @message_id, 'WITH_LOG', 'true';
    FETCH NEXT FROM SYS_messages INTO @message_id; 
END
CLOSE SYS_messages; 
DEALLOCATE SYS_messages; 
GO 

SQL Server Trace

SQL Server Trace

In SQL Server you need to find information, then most of DBA's using SQL Server Error Log.But in SQL Server Error Log Bit difficult to filter information and it does not display all information.

SELECT TG.*,TE.name FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_993.trc', default) TG INNER JOINsys.trace_events TE ON TG.EventClass=TE.trace_event_idWHERE DatabaseName='<YOUR DB NAME>';GO

Some information you can find table name start with "sys.trace_" but some pieces of information do not contain "trace"  tables. examples like "ObjectType" columns.  "ObjectType Trace Event Column" Microsoft Docs will help you to find more details.

CREATE SCHEMA PL/SQL(Oracle)

CREATE SCHEMA PL/SQL(Oracle)

• First Need to create table space for located all object belong to schema,

CREATE TABLESPACE TBS_DATABASE_USER_GROUP DATAFILE 'C:\ORACLE\tbs_database_user_group_01.dbf' SIZE 256M REUSE AUTOEXTEND OFF; 

• Next Create  "TEMPORARY TABLESPACE" as follows,

CREATE TEMPORARY TABLESPACE TBS_TEMP_DATABASE_USER_GROUP TEMPFILE 'tbs_temp_database_user_group.dbf' SIZE 5M AUTOEXTEND ON;

• Next Create User(or Schema)

CREATE USER Tharindu  IDENTIFIED BY password123
  DEFAULT TABLESPACE TBS_DATABASE_USER_GROUP
  TEMPORARY TABLESPACE TBS_TEMP_DATABASE_USER_GROUP
  QUOTA 20M on TBS_DATABASE_USER_GROUP;

• Finally Grant for above created user 

GRANT create session TO Tharindu;
GRANT create table TO Tharindu;
GRANT create view TO Tharindu;
GRANT create any trigger TO Tharindu;
GRANT create any procedure TO Tharindu;
GRANT create sequence TO Tharindu;
GRANT create synonym TO Tharindu;

With using above steps, allow you to create oracle user!  

char Vs varchar

char Vs varchar

Most common rule for any database systems if you have fixed length column use "char" data type else, (if you have variations) then use "varchar". but after read some forum post, noticed this is not common for less variation columns(less than 10 digits columns). I have created sample script to validate this as follows,

USE tempdb

GO

DECLARE

  @char CHAR(5) = 'ABCD',

  @nchar NCHAR(5) = N'ABCD',

  @varchar VARCHAR(10) = 'ABCD',

  @nvarchar NVARCHAR(10) = N'ABCD';

SELECT

  DATALENGTH(@char) AS CharDataLength,

  DATALENGTH(@nchar) AS NcharDataLength,

  DATALENGTH(@varchar) AS VarcharDataLength,

  DATALENGTH(@nvarchar) AS NvarcharDataLength

GO

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

CREATE TABLE #char(strchar char(8));

CREATE TABLE #varchar(strvarchar varchar(8));

GO

INSERT INTO #char VALUES('A');

INSERT INTO #char VALUES('AB');

INSERT INTO #char VALUES('ABC');

INSERT INTO #char VALUES('ABCD');

INSERT INTO #char VALUES('ABCDE');

INSERT INTO #char VALUES('ABCDEF');

INSERT INTO #char VALUES('ABCDEFG');

INSERT INTO #char VALUES('ABCDEFGH');

INSERT INTO #varchar VALUES('A');

INSERT INTO #varchar VALUES('AB');

INSERT INTO #varchar VALUES('ABC');

INSERT INTO #varchar VALUES('ABCD');

INSERT INTO #varchar VALUES('ABCDE');

INSERT INTO #varchar VALUES('ABCDEF');

INSERT INTO #varchar VALUES('ABCDEFG');

INSERT INTO #varchar VALUES('ABCDEFGH');

GO 10000

SET STATISTICS IO ON

SELECT * FROM #char;

PRINT '------------------------------------'

SELECT * FROM #varchar;

SET STATISTICS IO OFF

EXEC sp_spaceused '#char';

GO

EXEC sp_spaceused '#varchar';

GO

DROP TABLE #char;

DROP TABLE #varchar;

with above script statistics you will noticed less space used (and less pages) for "char" column apart from "varchar" column. 

sql_variant Vs varchar

sql_variant Vs varchar

I have done small comparison between  sql_variant Vs varchar data types. not seen big different than I have expected, but it's interesting information,

use [tempdb]

GO

IF OBJECT_ID('tblVarChar') IS NOT NULL BEGIN

DROP TABLE tblVarChar

END

IF OBJECT_ID('tblsql_variant') IS NOT NULL BEGIN

DROP TABLE tblsql_variant

END

GO

CREATE TABLE tblVarChar(name varchar(200));

CREATE NONCLUSTERED INDEX IX_tblVarChar ON tblVarChar (name);

CREATE TABLE tblsql_variant(name sql_variant);

CREATE NONCLUSTERED INDEX tblsql_variant ON tblsql_variant (name);

GO

INSERT INTO tblVarChar VALUES('http://www.databaseusergroup.com');

INSERT INTO tblsql_variant VALUES('http://www.databaseusergroup.com');

GO 100

INSERT INTO tblVarChar VALUES('255');

INSERT INTO tblsql_variant VALUES(CAST(255 AS tinyint));

GO 1000

INSERT INTO tblVarChar VALUES('32767');

INSERT INTO tblsql_variant VALUES(CAST(32767 AS smallint));

GO 1000

INSERT INTO tblVarChar VALUES('9223372036854775807');

INSERT INTO tblsql_variant VALUES(CAST(9223372036854775807 AS bigint));

GO 1000

INSERT INTO tblVarChar VALUES('2016-02-23 12:46:07');

INSERT INTO tblsql_variant VALUES(CAST('2016-02-23 12:46:07.347' AS smalldatetime));

GO 1000

PRINT 'tinyint'

SET STATISTICS IO ON

SELECT * FROM tblVarChar WHERE name='255';

GO

SELECT * FROM tblsql_variant WHERE name=255;

SET STATISTICS IO OFF

GO

PRINT '----------------------------------------------------------------------------------------'

PRINT 'smallint'

SET STATISTICS IO ON

SELECT * FROM tblVarChar WHERE name='32767';

GO

SELECT * FROM tblsql_variant WHERE name=32767;

SET STATISTICS IO OFF

GO

PRINT '----------------------------------------------------------------------------------------'

PRINT 'bigint'

SET STATISTICS IO ON

SELECT * FROM tblVarChar WHERE name='9223372036854775807';

GO

SELECT * FROM tblsql_variant WHERE name=9223372036854775807;

SET STATISTICS IO OFF

GO

PRINT '----------------------------------------------------------------------------------------'

PRINT 'smalldatetime'

SET STATISTICS IO ON

SELECT * FROM tblVarChar WHERE name='2016-02-23 12:46:07';

GO

SELECT * FROM tblsql_variant WHERE name='2016-02-23 12:46:07';

SET STATISTICS IO OFF

GO

PRINT '----------------------------------------------------------------------------------------'

PRINT 'varchar'

SET STATISTICS IO ON

SELECT * FROM tblVarChar WHERE name='http://www.databaseusergroup.com';

GO

SELECT * FROM tblsql_variant WHERE name='http://www.databaseusergroup.com';

SET STATISTICS IO OFF

GO

sp_spaceused 'tblVarChar';

GO

sp_spaceused 'tblsql_variant';

GO

SELECT DISTINCT SQL_VARIANT_PROPERTY(name,'BaseType') BaseType,

SQL_VARIANT_PROPERTY(name,'Precision') Precisions,

SQL_VARIANT_PROPERTY(name,'Scale') Scale,

SQL_VARIANT_PROPERTY(name,'TotalBytes') TotalBytes,

SQL_VARIANT_PROPERTY(name,'Collation') Collation,

SQL_VARIANT_PROPERTY(name,'MaxLength') MaxLengths

FROM      tblsql_variant;

GO

with above SQL example you will notice less page read for "smalldatetime" and "varchar" others almost same. but i'm bit wondering "tinyint" has more pages than "varchar". not really sure why this has allocated extra page. any way it's good if you are using "sql_variant" data type then ignore staring high allocated pages data types. 

Hierarchyid DataType in SQL Server

Hierarchyid DataType in SQL Server

SQL Server 2012 will introduced new data type call hierarchyid. This data type makes it easier to store and query hierarchical data.This does not automatically represent a tree you have to used hierarchyid function as follows, 

use tempdb
GO
IF OBJECT_ID('EmployeeWithHierarchyID') IS NOT NULL BEGIN
DROP TABLE EmployeeWithHierarchyID;
END
GO
CREATE TABLE EmployeeWithHierarchyID
(
    [Id] hierarchyid not null primary key,
    [Name] nvarchar(50) not null
)
GO
INSERT INTO EmployeeWithHierarchyID ([Id], [Name]) VALUES
    (hierarchyid::GetRoot(), 'General Manager'),
    ('/1/', 'Manager'),
    ('/1/1/', 'Assistant Manager')
GO
SELECT
    Id,
    Id.ToString() AS [Path],
    Id.GetLevel() AS [Level],
    Name
FROM EmployeeWithHierarchyID
GO