XML Import

CREATE PROCEDURE [TestingSP].[prc_Testing_INSERT_XML]
@intClientID INT,
@CurrencyXML XML
AS
DECLARE @TempTable TABLE (FirstName NVARCHAR (256), LastName NVARCHAR (256), Email NVARCHAR(256));
BEGIN


INSERT INTO @TempTable(FirstName, LastName, Email)
SELECT MliFirstName = T.Item.query('firstname').value('.', 'NVARCHAR(256)'),
MliLastName = T.Item.query('lastname').value('.', 'NVARCHAR(256)'),
MliEmail = T.Item.query('email').value('.', 'NVARCHAR(256)')
FROM @CurrencyXML.nodes('shq-mailinglist/person') AS T(Item)

BEGIN TRY
IF EXISTS (SELECT * FROM @TempTable)
BEGIN

INSERT INTO Testing.ImpMailingList(ClID, ImlFirstName,
ImlLastName, ImlEmail)
SELECT @intClientID, tt.FirstName, tt.LastName, tt.Email
FROM @TempTable tt;
END

RETURN 1;
END TRY
BEGIN CATCH

INSERT INTO Testing.ApplicationError(ClID, AerENo, AerESeverity, AerEState, AerEProcedure, AerELine, AerEMessage)
VALUES(NULL, ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE());

RETURN -1;
END CATCH

END

Passing Multiple Records to a Stored Procedure in SQL Server

CREATE PROCEDURE [ImportJobMaterials]
@JobMaterialsXml AS VARCHAR(MAX),
@JobID AS INT,
@ChangeOrderNumber AS VARCHAR(10) = NULL,
@ChangeOrderDescription AS VARCHAR(100) = NULL
AS
BEGIN
DECLARE @XML AS XML

DECLARE @MaterialsTable TABLE
(
ID INT IDENTITY(1,1),
Area VARCHAR(250),
Phase VARCHAR(250),
WorkCodeID INT,
WorkCodeTitle VARCHAR(250),
MaterialTitle VARCHAR(250),
Quantity DECIMAL(18,2),
TotalHours DECIMAL(18,2)
)

SELECT @XML = @JobMaterialsXml

INSERT INTO @MaterialsTable (Area, Phase, WorkCodeID, WorkCodeTitle, MaterialTitle, Quantity, TotalHours)
SELECT M.Item.query('./Area').value('.','VARCHAR(250)') Area,
M.Item.query('./Phase').value('.','VARCHAR(250)') WorkCode,
M.Item.query('./WorkCodeID').value('.','INT') WorkCodeID,
M.Item.query('./WorkCodeTitle').value('.','VARCHAR(250)') WorkCodeTitle,
M.Item.query('./MaterialTitle').value('.','VARCHAR(250)') MaterialTitle,
M.Item.query('./Quantity').value('.','DECIMAL(18,2)') Quantity,
M.Item.query('./TotalHours').value('.','DECIMAL(18,2)') TotalHours
FROM @XML.nodes('/ArrayOfJobMaterialImport/JobMaterialImport') AS M(Item)

--Process the data
END

Page Life Expectancy (SQL Server)

SELECT [object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

Find Unused Indexes of Current Database

DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO

SQL SERVER – Find Row Count in Table – Find Largest Table in Database

SELECT sc.name +'.'+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC

Daily Activities- SQL Server BackUP & Restore

The following is the query for recent 100 records for database backups.
you get results by changing backup type below

D= Full backup
L= T-Log backup
I=Incremental or Differential

SELECT TOP 100 bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bm.physical_device_name,
bm.logical_device_name
FROM msdb..backupset bs
JOIN msdb..backupmediafamily bm
ON bs.media_set_id = bm.media_set_id
WHERE bs.TYPE = 'D'
ORDER BY bs.backup_start_date DESC,
bs.database_name

///////////////////////////////////////////////////////////////////////////////////////

Another query to get Backup start and end time with total backup size.

SELECT
a.database_name AS dbname,
CONVERT(CHAR(20),a.backup_start_date,100)
AS start_time,
CONVERT(CHAR(20),a.backup_finish_date,100)
AS end_time,
a.backup_size,
a.backup_size/1024/1024 AS [backup_Size_MB]
FROM
msdb.dbo.backupset AS a
LEFT JOIN
msdb.dbo.backupset AS b
ON
a.database_name = b.database_name
AND a.backup_start_date >
(SELECT MAX(backup_start_date) FROM
msdb.dbo.backupset) - 1
AND b.backup_start_date = DATEADD(week, -1,
(SELECT MAX(backup_start_date)
FROM msdb.dbo.backupset) - 1)
ORDER BY
a.database_name,
a.backup_start_date

/////////////////////////////////////////////////////////// RESTORE Query

Another beautiful query here to result restore history

DECLARE @dbname SYSNAME
SET @dbname = 'db_name' /* Replace with your dbname*/
SELECT
destination_database_name AS 'Database Name',
[user_name] AS 'Username',
CASE restore_type
WHEN NULL THEN 'NULL'
WHEN 'D' THEN 'Database'
WHEN 'F' THEN 'File'
WHEN 'G' THEN 'Filegroup'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log File'
WHEN 'V' THEN 'Verifyonly'
WHEN 'R' THEN 'Revert'
END AS 'Restore Type',
CASE [replace]
WHEN NULL THEN 'NULL'
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
END AS 'Database Replaced',
restore_date AS 'Date Restored'
FROM msdb..restorehistory
WHERE destination_database_name = CASE
WHEN @dbname IS NOT NULL THEN @dbname
ELSE destination_database_name END
ORDER BY restore_date DESC

Get Database Backup History

SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
--WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

Identify blocking

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO