Verify Default Trace Status in SQL Server

Below is the process to verify default trace status

To get the name and location of the default trace that is currently runningSELECT * FROM :: fn_trace_getinfo(default)

To verify whether default trace option is enabled or not
select * from sys.configurations where configuration_id = 1568


Displays information about traces that are running
  select * from sys.traces

To enable default trace in SQL Server

sp_configure 'show advanced options',1
reconfigure

 sp_configure 'default trace enabled', 1
reconfigure

To restart default trace in SQL Server

sp_configure 'show advanced options',1
reconfigure

 sp_configure 'default trace enabled', 0
reconfigure

 sp_configure 'default trace enabled', 1
reconfigure

Partitioning Example to split, merge and switch options

In our previous example we have defined below partition function and scheme as below

CREATE PARTITION FUNCTION SalesData_Function (DATETIME)  AS RANGE LEFT FOR VALUES ('12/31/2010','12/31/2011','12/31/2012')

CREATE PARTITION SCHEME SalesData_Scheme AS PARTITION SalesData_Function TO (Sales_2010, Sales_2011, Sales_2012, [PRIMARY])

According to above partition scheme if you insert a record of 2013 year it will insert in Primary partition.  If you want to insert 2013 records in a new partition with file group then
we need to Split Partition, Below is the example :
--- Adding a new filegroup
ALTER DATABASE SalesDB ADD FILEGROUP Sales_2013

--- Adding a physical file to filegroup
ALTER DATABASE SalesDB ADD FILE (NAME = 'Sales2013', FILENAME= 'C:\Sales2013.ndf', SIZE =1000KB, MAXSIZE=UNLIMITED, FILEGROWTH =5%) TO FILEGROUP Sales_2013

--- Modify partition scheme to make use of new filegroup
ALTER PARTITION SCHEME SalesData_Scheme NEXT USED Sales_2013

-- Modify parition function to split the data range
ALTER PARTITION FUNCTION SalesData_Function() SPLIT RANGE('12/31/2013')

The above partition function splits the data of specified range and moves into new file group, in our case it will move all the 2013 records into Sales_2013 filegroup.

--- Verify whether records are moved into new filegroup are not
insert into Sales_Data values(1015,'order15','Bangalore','12-Mar-2013', 100)
insert into Sales_Data values(1016,'order16','Chennai','23-Apr-2013', 100)

SELECT DISTINCT OBJECT_NAME(si.object_id) Table_Name, p.partition_number,fg.name,
p.rows, (SELECT value FROM sys.partition_range_values prv WHERE prv.boundary_id = p.partition_number) Partition_Value
 FROM sys.partitions p, sys.indexes si, sys.partition_schemes ps,
sys.destination_data_spaces dds, sys.filegroups fg, sys.partition_functions pf
 WHERE p.object_id = si.object_id and p.index_id = si.index_id AND si.data_space_id = ps.data_space_id AND dds.partition_scheme_id = ps.data_space_id
AND fg.data_space_id = dds.data_space_id AND p.partition_number = dds.destination_id AND pf.function_id = ps.function_id

Merge Partition -- After couple of years we decided we need to keep last three year records in three different filegroups and need to merge rest of the data into single partition.
That functionality can be done using merge partition.

In our Sales_data table we have 4 years of data and 5 partitions including primary. we will merge first two years ie., 2010, 2011 into one partition.

-- Verify number of records in each year
SELECT YEAR(order_date), COUNT(*) AS "OrderYear" FROM sales_data GROUP BY YEAR(order_date)

-- Modifying partition function to merge 2009, 2010 records into next available partition.
ALTER PARTITION FUNCTION SalesData_Function() MERGE RANGE ('12/31/2010') -- It merges all 2009, 2010 records into Sales_2011 partition and count of Sales_2011FG records will get increased

Note : Merge will not remove physical filegroup from the database structure, it just do merging of two different sets into one.

-- To verify 2009, 2010 records are in which partition
SELECT $Partition.SalesData_Function(Order_Date) AS [Partition],* from Sales_Data ORDER BY [Partition] -- Verify date by partitioned column

Partition Switching -- Switching of partition helps to archive historical data in a faster way because it is a metadata operation
Note : Switch partition has certain limitations
a) It needs an empty table with same schema as original table.
b) source and target table must share same filegroup to swith partition

1. Create a temporary table for archival process (same as original schema)
2. Invoke alter partition switch command to switch the partition
3. Insert data into permanent archive table
4. drop the temporary table

--- Creating a temporary table for parition switching
SELECT * INTO sales_data_temp FROM sales_data WHERE YEAR(order_date)=2000

ALTER TABLE sales_data SWITCH PARTITION 1 TO sales_data_temp
The above partition switch will throw the below error

ALTER TABLE SWITCH statement failed. table 'SalesDB.dbo.sales_data_temp' is in filegroup 'PRIMARY' and partition 1 of table 'SalesDB.dbo.sales_data' is in filegroup 'Sales_2012'.
To do partition switching both tables must share same filegroup

DROP TABLE sales_data_temp
CREATE TABLE [dbo].[Sales_Data_temp](
 [Order_ID] [int] NULL,
 [OrderName] [varchar](15) NULL,
 [Order_City] [varchar](30) NULL,
 [Order_Date] [datetime] NULL,
 [Order_Amt] [int] NULL
) ON Sales_2012
GO

ALTER TABLE sales_data SWITCH PARTITION 1 TO sales_data_temp -- This will switch partition 1 into newly created temporary table and removes records from original table.

SELECT * FROM sales_data_temp
SELECT * FROM sales_data

Partitioning Example Part III (With Filegroups)

--- Creating a demo database with filegroups to store associated data on respective filegroups
CREATE DATABASE SalesDB ON PRIMARY
( NAME = Primary_Data, FILENAME = 'C:\Sales_Data.mdf',
 SIZE = 100, MAXSIZE = 150, FILEGROWTH = 5 ),
 FileGroup Sales_2010 ( NAME = Sales2010, FILENAME = 'D:\Sales2010.ndf',
    SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ),
 FileGroup Sales_2011   ( NAME = Sales2011,   FILENAME = 'E:\Sales2011.ndf',
    SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ),
 FileGroup Sales_2012 ( NAME = Sales2012, FILENAME = 'F:\Sales2012.ndf',
    SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )

--- Verify file groups are created properly
USE SalesDB
SELECT * FROM SalesDB..sysfiles

--- Creating Partition function to store 3 years of data on it
CREATE PARTITION FUNCTION SalesData_Function (DATETIME)
AS RANGE LEFT FOR VALUES ('12/31/2010','12/31/2011','12/31/2012')  -- FOUR PARTITIONS

--- Creating a partition scheme to store data on respective filegroups
CREATE PARTITION SCHEME SalesData_Scheme AS PARTITION SalesData_Function TO (Sales_2010, Sales_2011, Sales_2012, [PRIMARY])

-- Creating a sales demo partitioned table on order_date column
Create table Sales_Data (Order_ID int, OrderName varchar(15), Order_City varchar(30), Order_Date DATETIME, Order_Amt INT) ON SalesData_Scheme(Order_Date)

--- Insert demo data
insert into Sales_Data values(1015,'order01','Bangalore','20-Jul-2009', 100)
insert into Sales_Data values(1001,'order1','Hyderabad','12-Jan-2010', 100)
insert into Sales_Data values(1002,'order2','Chennai','06-Sep-2010', 100)
insert into Sales_Data values(1003,'order3','Bangalore','11-Nov-2010', 100)
insert into Sales_Data values(1004,'order4','Hyderabad','11-Feb-2011', 100)
insert into Sales_Data values(1005,'order5','Chennai','04-Mar-2011', 100)
insert into Sales_Data values(1006,'order6','Bangalore','09-Jul-2011', 100)
insert into Sales_Data values(1008,'order7','Chennai','15-Aug-2011', 100)
insert into Sales_Data values(1009,'order8','Hyderabad','20-Dec-2011', 100)
insert into Sales_Data values(1010,'order9','Bangalore','19-Jan-2012', 100)
insert into Sales_Data values(1011,'order10','Bangalore','14-Apr-2012', 100)
insert into Sales_Data values(1012,'order11','Hyderabad','24-Dec-2012', 100)
insert into Sales_Data values(1013,'order12','Chennai','1-Jan-2013', 100)
insert into Sales_Data values(1014,'order13','Hyderabad','09-Jun-2013', 100)
insert into Sales_Data values(1015,'order14','Bangalore','20-Jul-2012', 100)

SELECT * FROM Sales_Data  -- Verify records

SELECT $Partition.SalesData_Function(Order_Date) AS [Partition],* from Sales_Data ORDER BY [Partition] -- Verify date by partitioned column

--- Verify number of rows on each partitions along with filegroup name and partition max value
SELECT DISTINCT OBJECT_NAME(si.object_id) Table_Name, p.partition_number,fg.name,
p.rows, (SELECT value FROM sys.partition_range_values prv WHERE prv.boundary_id = p.partition_number) Partition_Value
FROM sys.partitions p, sys.indexes si, sys.partition_schemes ps,
sys.destination_data_spaces dds, sys.filegroups fg, sys.partition_functions pf
WHERE p.object_id = si.object_id and p.index_id = si.index_id AND si.data_space_id = ps.data_space_id AND dds.partition_scheme_id = ps.data_space_id
AND fg.data_space_id = dds.data_space_id AND p.partition_number = dds.destination_id AND pf.function_id = ps.function_id
 

Partitioning Example Part II (Basic Example)

Below example demonstrates the below items
1. Creating Partition Function
2. Creating Partition Scheme
3. Creating a table and assigning partition to that table
4. Verifying partitions using metadata functions


CREATE DATABASE PARTITION_TEST

SELECT * FROM PARTITION_TEST..sysfiles

USE PARTITION_TEST

-- Creating a Partition Function
CREATE PARTITION FUNCTION partitionfunction1(int) AS RANGE RIGHT FOR VALUES (1000,2000,3000,4000,5000) ; -- SIX partitions

-- DROP PARTITION FUNCTION partitionfunction1  -- To drop the partition function

SELECT * FROM sys.partition_functions
/*
PARTITION 1 = UPTO 1000  
PARTITION 2 = 1001 TO 2000
PARTITION 3 = 2001 TO 3000
PARTITION 4 = 3001 TO 4000
PARTITION 5 = 4001 TO 5000
PARTITION 6 = 5000 and above

CREATE PARTITION FUNCTION partitionfunction1(int)
AS RANGE  LEFT
FOR VALUES (1000,2000,3000,4000,5000)

PARTITION 1 = UPTO 999 
PARTITION 2 = 1000 TO 1999
PARTITION 3 = 1999 TO 2999
PARTITION 4 = 2999 TO 3999
PARTITION 5 = 3999 TO 4999
PARTITION 6 = 4999 and above
*/
-- Creating a Partition Scheme
CREATE PARTITION SCHEME partitionscheme1 AS PARTITION partitionfunction1 ALL TO ('PRIMARY');

-- Create the table and defining a parition on specified column
CREATE TABLE partitioned_table (col1 int, col2 int, col3 INT ) ON partitionscheme1(col1)

insert into partitioned_table values (10,10,10)
insert into partitioned_table values (90,10,10)
insert into partitioned_table values (100,10,10)
insert into partitioned_table values (600,10,10)
insert into partitioned_table values (900,10,10)
insert into partitioned_table values (1000,10,10)
insert into partitioned_table values (2000,10,10)
insert into partitioned_table values (2500,10,10)
insert into partitioned_table values (3000,10,10)
insert into partitioned_table values (3500,10,10)
insert into partitioned_table values (4000,10,10)
insert into partitioned_table values (4500,10,10)
insert into partitioned_table values (5000,10,10)
insert into partitioned_table values (5500,10,10)
insert into partitioned_table values (6000,10,10)
insert into partitioned_table values (6500,10,10)
insert into partitioned_table values (7000,10,10)
insert into partitioned_table values (7500,10,10)
insert into partitioned_table values (8000,10,10)
insert into partitioned_table values (8500,10,10)
insert into partitioned_table values (9000,10,10)
insert into partitioned_table values (9500,10,10)
insert into partitioned_table values (9900,10,10)
insert into partitioned_table values (9901,10,10)

SELECT OBJECT_ID('partitioned_table')

SELECT * FROM sys.PARTITIONS WHERE OBJECT_ID = 2121058592

SELECT * FROM partitioned_table

--- Below command is to view data from each partition
SELECT $PARTITION.partitionfunction1(COL1) AS [PARTITION], col1 AS [DATA] FROM partitioned_table

-- Below command is to view partition boundaries and row count in each partition
SELECT $PARTITION.partitionfunction1(COL1) AS Parition_Name,
  MIN(Col1) AS Min_Value_InPartition,
  MAX(Col1) AS Max_Value_InPartition,
  COUNT(*) Partition_Record_Count
FROM partitioned_table
GROUP BY $PARTITION.partitionfunction1(Col1)
 

Querying XML Column Values in SQL Server Part II

--Querying XML datatype using T-SQL

In the previous example we read XML Tag values and this example it shows how to read values of individual tags.

DECLARE @XMLTest TABLE (xmlString XML)

INSERT INTO @XMLTest VALUES ('
<SELECT>
<FIELD Display="InvoiceNumber">SalesData.InvoiceNumber</FIELD>
<FIELD Display="InvoiceDate">SalesData.InvoiceDate</FIELD>
<FIELD Display="InvoiceDescription">SalesData.InvoiceDescription</FIELD>
<FIELD Display="OrderedDate">SalesData.OrderedDate</FIELD>
<FIELD Display="OrderSummary">SalesData.OrderSummary</FIELD>
<FIELD Display="OrderDeliveryDate">SalesData.OrderDeliveryDate</FIELD>
<FIELD Display="OrderQuantity">SalesData.OrderQuantity</FIELD>
<FIELD Display="OrderAmount">SalesData.OrderAmount</FIELD>
<FROM>SalesData</FROM>
<ORDERBY>Invoice Number ASC</ORDERBY>
</SELECT>')

SELECT x.value('@Display[1]', 'VARCHAR(20)') AS Name
FROM @XMLTest t CROSS APPLY XMLSTRING.nodes('/SELECT/FIELD') a(x)

Querying XML Column Values in SQL Server Part I

--Querying XML datatype using T-SQL

DECLARE @XMLTest TABLE (xmlString XML)

INSERT INTO @XMLTest VALUES ('
<SELECT>
<FIELD Display="InvoiceNumber">SalesData.InvoiceNumber</FIELD>
<FIELD Display="InvoiceDate">SalesData.InvoiceDate</FIELD>
<FIELD Display="InvoiceDescription">SalesData.InvoiceDescription</FIELD>
<FIELD Display="OrderedDate">SalesData.OrderedDate</FIELD>
<FIELD Display="OrderSummary">SalesData.OrderSummary</FIELD>
<FIELD Display="OrderDeliveryDate">SalesData.OrderDeliveryDate</FIELD>
<FIELD Display="OrderQuantity">SalesData.OrderQuantity</FIELD>
<FIELD Display="OrderAmount">SalesData.OrderAmount</FIELD>
<FROM>SalesData</FROM>
<ORDERBY>Invoice Number ASC</ORDERBY>
</SELECT>')

SELECT xmlString FROM @XMLTest

SELECT
    XMLSTRING.value('(/SELECT/FROM)[1]', 'CHAR(200)') FROM_VALUE,
    XMLSTRING.value('(/SELECT/ORDERBY)[1]', 'CHAR(200)') ORDERBY_VALUE
FROM @XMLTest

/SELECT/FROM = (RootNode / SubNode)
[1]  = Indicates field Number


SELECT
    XMLSTRING.value('(/SELECT/FROM)[1]', 'CHAR(200)') FROM_VALUE,
    XMLSTRING.value('(/SELECT/ORDERBY)[1]', 'CHAR(200)') ORDERBY_VALUE,
    XMLSTRING.value('(/SELECT/FIELD)[1]', 'CHAR(200)') FIELD_VALUE1,
    XMLSTRING.value('(/SELECT/FIELD)[2]', 'CHAR(200)') FIELD_VALUE2
FROM @XMLTest

 

How to verify alerted or created or deleted objects on database

fn_trace_gettable returns trace file information in a tabular format. It helps to query the default or user-defined trace files.

fn_trace_gettable([@filename],[@numfiles])

@filename  -- which file to be read
@numfiles  -- Rollover files

To know the default system trace file.

SELECT * FROM fn_trace_getinfo(default);   -- Displays default trace file name

Below query returns the rows for created / altered / dropped objects from the current database

Select ObjectName, DatabaseName, StartTime,      
EventClass, ObjectType,ServerName, LoginName,      
NTUserName, ApplicationName
from ::fn_trace_gettable( 'C:\Program Files\Microsoft SQL Server\LOG\log_123.trc', default )           
where EventClass in (46,47,164) and EventSubclass = 0  and DatabaseID = db_id();

 Below query returns event class along with object_name

 Select ObjectName, DatabaseName, StartTime, te.name,EventClass, ObjectType,ServerName,   
 LoginName,NTUserName, ApplicationName
 from fn_trace_gettable( 'C:\Program Files\Microsoft \LOG\log_491.trc', default )           
 INNER JOIN sys.trace_events te  ON EventClass = te.trace_event_id
 where EventClass in (46,47,164) and EventSubclass = 0  and DatabaseID = db_id();

Below query returns about Stored procedure execution details from a particular application

Select ApplicationName, HostName, TextData, RowCounts, Reads, writes, CPU, Duration
from fn_trace_gettable( 'C:\program files\microsoft sql server\log\log_490.trc', default )           
where EventClass in (10,12)  -- Batch and RFC Completed
AND applicationname = '.Net SqlClient Data Provider'

 

Connect To SQL Server Using VBA

1. Open Excel  -- Developer Tab -- Select Visual Basic -- Double click on Sheet1 (or require sheet)

Tools - Reference -  Microsoft ActiveX Data Objects 2.6 Library

Sub FetchFromSQLServer()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim constring As String

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
constring = "provider=SQLOLEDB.1;Data Source=SQLServer2005,1433;Initial Catalog=kalyandb;User id='user1';password='admin"
con.Open constring

rs.ActiveConnection = con
rs.Open "Select * from Colors"
Sheet1.Range("A1").CopyFromRecordset rs

rs.Close
Set rs = Nothing
Set con = Nothing

End Sub