sp_send_dbmail XML attachment line break issue

I was initially working on creating an event notification that sends me an email alert for our dba team making it work under in a tsql procedure

so once they receive email alert they can open dead lock graph directly from the email account only if ssms is installed on the machine

 

First I tried some thing like this below then I noticed


EXEC msdb.dbo.sp_send_dbmail @recipients = ‘sqljunkieshare@gmail.com’
,@subject = ‘test’ — Subject defined above
,@body = ‘test’
,@query_result_header =0
,@query_attachment_filename=‘tst.xdl’
,@query = ‘SET NOCOUNT ON select convert(NVARCHAR(MAX),deadlock_graph) from testdb.dbo.DeadlocksReports where deadlock_id = 20’
, @attach_query_result_as_file = 1

Then I realized that output text is getting truncated to 256 charecters then I used @query_no_truncate = 1 to avoid the truncation


EXEC msdb.dbo.sp_send_dbmail @recipients = ‘sqljunkieshare@gmail.com’
,@subject = ‘test’ — Subject defined above
,@body = ‘test’
,@query_result_header =0
,@query_attachment_filename=‘tst.xdl’
,@query = ‘SET NOCOUNT ON select convert(NVARCHAR(MAX),deadlock_graph) from testdb.dbo.DeadlocksReports where deadlock_id = 20’
, @attach_query_result_as_file = 1
,@query_no_truncate = 1

Once I opened the xml attachment file I noticed that there are some weird line breaks for about every 256 characters then I realized that I might need to use @query_result_width with default of 256 with max of 32767


EXEC msdb.dbo.sp_send_dbmail @recipients = ‘sqljunkieshare@gmail.com’
,@subject = ‘test’ — Subject defined above
,@body = ‘test’
,@query_result_header =0
,@query_attachment_filename=‘tst.xdl’
,@query = ‘SET NOCOUNT ON select convert(NVARCHAR(MAX),deadlock_graph) from testdb.dbo.DeadlocksReports where deadlock_id = 20’
, @attach_query_result_as_file = 1
,@query_result_width = 32767
,@query_no_truncate = 1

seems to work as my xml output doesnt seem to exceed 32767 characters

 

dead lock graph email attachment

 

actual dead lock graph

One of the best SQL Server Index Internals: A Deep Dive

its from Tim Chapman, one of the best index internal presentation

I will try to highlight the things he talk through this presentation

Indexes

  • Clustered vs Non clustered and filtered indexes
  • Non clustered indexes and include column effect on index structure
  • Heap
  • Heap vs Clustered
  • Various DMV utilization to understand index internals
  • How to detect fragmentation
  • Logical fragmentation and effects on query perfromance
  • Page splits , Page density, page fullness

Statistics

  • How to view statistics
  • Effect of statistics on query performance
  • effect of stats from index rebuild and reorg

Log usage

  • Index rebuild vs reorg
  • Log usage for Index rebuilds and reorganize

Over all must watch

 

How to resolve suspect Database issue (Emergency Repair) SQL Server

There are several reasons why database goes into suspect mode, but best way to resond that I found very helpfull

1) Set the database to emergencey mode

ALTER DATABASE ‘db name’ SET EMERGENCY

2) Set the database to Single user mode so to do dbcc repair

ALTER DATABASE DAXBI SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

3) Run dbcc repair command

DBCC CHECKDB (N’db name’, REPAIR_REBUILD ) WITH ALL_ERROMSGS, NO_INFOMSGS;

that will take care of any consistency errors with indexes and partitions

if you want the database to be back online not worried about the data loss

DBCC CHECKDB (‘db name’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERROMSGS, NO_INFOMSGS;

4) Set databsae to multi user mode

ALTER DATABASE ‘db name’ SET MULTI_USER

DMV : sys.dm_exec_connections

Returns information about the connections established to this instance of SQL Server and the details of each connection.

Column name Data type Description
session_id int Identifies the session associated with this connection. Is nullable.
connect_time datetime Timestamp when connection was established. Is not nullable.
num_reads int Number of packet reads that have occurred over this connection. Is nullable.
num_writes int Number of data packet writes that have occurred over this connection. Is nullable.
last_read datetime Timestamp when last read occurred over this connection. Is nullable.
last_write datetime Timestamp when last write occurred over this connection. Not Is nullable.
client_net_address varchar(48) Host address of the client connecting to this server. Is nullable.

sys.dm_exec_connections

–Client wich has more than one connection

SELECT  client_net_address ,COUNT(*) number_of_connections

FROMsys.dm_exec_connections

GROUP BY client_net_address

HAVING  COUNT(*)> 1

ORDER  BY number_of_connections desc

–Oldest connection to server

SELECT  session_id,connection_id,connect_time FROMsys.dm_exec_connections

ORDER  BY connect_time asc

–Connection with highest network utilization

SELECT  num_reads,num_writes FROMsys.dm_exec_connections

ORDER  BY num_reads DESC,num_writes DESC

Sql server datacollector issue specified @collector_type_uid is not valid in this data warehouse

agent error collector upload error

Executing the query “{call [core].[sp_create_snapshot](?, ?, ?, ?, ?, ?…” failed with the following error: “The specified @collector_type_uid (14AF3C12-38E6-4155-BD29-F33E7966BA23) is not valid in this data warehouse.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

if we look sp_create_snapshot procedure in Management_dataware_house  it accepts six parameters  @collection_set_uid   ,@collector_type_uid   ,@machine_name  , @named_instance  ,  @log_id   ,  @snapshot_id

this procedure creates a snapshot in the management datawarehouse for a collection instance one parameter is Collecter_type_id

Note: Sql Server Data collector can collect data from Dynamic management views , SQL trace and Windows performance counters using TSQL and WMI query’s

We can get list of availanle data collector types by querying

SELECT

[collector_type_uid],[name]

,[parameter_schema]

,[parameter_formatter]

,[schema_collection]

,[collection_package_name]

,[collection_package_folderid]

,[upload_package_name]

,[upload_package_folderid]

,[is_system]

FROM [msdb].[dbo].[syscollector_collector_types_internal]

Collector types

insertinto [core].[supported_collector_types_internal](collector_type_uid)

values (’14AF3C12-38E6-4155-BD29-F33E7966BA23′)

 

collector suscces full

Decrypting encrypted stored procedures, views, functions in SQL server 2005,2008 &R2

Any stored procedure or view or function can be encrypted when stored in the sql server , so a user can not see the code inside even if the user has SYSADMIN role and it is also not recommended to use this encryption option to hide the code because once you use the encryption option Microsoft sql server does not provide the option or the feature to reverse the setting or decrypt the object , so when you use the encryption option , save your code in a separate instance































TITLE: Microsoft SQL Server Management Studio
——————————

Script failed for StoredProcedure ‘dbo.test_encrp’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+StoredProcedure&LinkId=20476

——————————
ADDITIONAL INFORMATION:

Property TextHeader is not available for StoredProcedure ‘[dbo].[test_encrp]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=TextHeader&LinkId=20476

First we will see how it looks in the sql_modules catalog views, in the process we will also list all the encrypted

stored procedures in the databases

to find all the encrypted objects in the sql server database


here is the query used above

select o.name,s.definition,o.type_desc,

case

when definition is null then ‘yes’
else ‘no’
end as ‘is_encrypted’

from sys.sql_modules s inner join

sys.objects o on s.object_id = o.object_id

where type in (‘p’,’tr’,’FN’,’tf’,’v’)

now we got know how it is encrypted , and where to find the encrypted objects now we will look in to how to decrypt them

There are two ways you can do

  1. Using a script/stored procedure using a DAC connection
  2. Using third party tools

first we will look at the script method

first you need to have SYSADMIN role assigned to you , then you have to setup a DAC connection to the sql server instance that you

want to decrypt the objects

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

here is the code to decrypt

 

/****** Object:  StoredProcedure [dbo].[sp__procedure]    Script Date: 03/06/2012 19:56:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp__procedure]
(@procedure sysname = NULL)
AS
SET NOCOUNT ON

DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@intEncrypted
tinyint,@procNameLength int
select @maxColID = max(subobjid)
–,@intEncrypted = encrypted
FROM
sys.sysobjvalues WHERE objid = object_id(@procedure)
–GROUP BY encrypted

–select @maxColID as ‘Rows in sys.sysobjvalues’
select @procNameLength = datalength(@procedure) + 29

DECLARE @real_01 nvarchar(max)

DECLARE @fake_01 nvarchar(max)

DECLARE @fake_encrypt_01 nvarchar(max)

DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max)

select @real_decrypt_01a = ”

— extract the encrypted imageval rows from sys.sysobjvalues
SET @real_01=(SELECT imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 and subobjid = 1 )

— create this table for later use
create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL ,
[real_decrypt] NVARCHAR(MAX) )

— We’ll begin the transaction and roll it back later
BEGIN TRAN
— alter the original procedure, replacing with dashes
SET @fake_01=’ALTER PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
‘+REPLICATE(‘-‘, 40003 – @procNameLength)

EXECUTE (@fake_01)

— extract the encrypted fake imageval rows from sys.sysobjvalues
SET @fake_encrypt_01=(SELECT imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 and subobjid = 1)

SET @fake_01=’CREATE PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
‘+REPLICATE(‘-‘, 40003 – @procNameLength)
–start counter
SET @intProcSpace=1
–fill temporary variable with with a filler character
SET @real_decrypt_01 = replicate(N’A’, (datalength(@real_01) /2 ))

–loop through each of the variables sets of variables, building the real variable
–one byte at a time.
SET @intProcSpace=1

— Go through each @real_xx variable and decrypt it, as necessary
WHILE @intProcSpace<=(datalength(@real_01)/2)
BEGIN
–xor real & fake & fake encrypted
SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1,
NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^
(UNICODE(substring(@fake_01, @intProcSpace, 1)) ^
UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))
SET @intProcSpace=@intProcSpace+1
END

— Load the variables into #output for handling by sp_helptext logic

insert #output (real_decrypt) select @real_decrypt_01
— select real_decrypt AS ‘#output chek’ from #output — Testing

— ————————————-
— Beginning of extract from sp_helptext
— ————————————-
declare @dbname sysname
,@BlankSpaceAdded int
,@BasePos int
,@CurrentPos int
,@TextLength int
,@LineId int
,@AddOnLen int
,@LFCR int –lengths of line feed carriage return
,@DefinedLength int
,@SyscomText nvarchar(4000)
,@Line nvarchar(255)

Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0
–Keeps track of blank spaces at end of lines. Note Len function ignores trailing blank spaces
CREATE TABLE #CommentText
(LineId int
,Text nvarchar(255) collate database_default)

— use #output instead of sys.sysobjvalues
DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT real_decrypt from #output
ORDER BY ident
FOR READ ONLY

— Else get the text.

SELECT @LFCR = 2
SELECT @LineId = 1

OPEN ms_crs_syscom

FETCH NEXT FROM ms_crs_syscom into @SyscomText

WHILE @@fetch_status >= 0
BEGIN

SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)

WHILE @CurrentPos != 0
BEGIN
–Looking for end of line followed by carriage return
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,
@BasePos)

–If carriage return found
IF @CurrentPos != 0
BEGIN
–If new value for @Lines length will be > then the
–set length then insert current contents of @line
–and proceed.

While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@CurrentPos-@BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +
@BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N”) +
isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N”)
SELECT @BasePos = @CurrentPos+2
INSERT #CommentText VALUES( @LineId, @Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
–else carriage return not found
BEGIN
IF @BasePos <= @TextLength
BEGIN
–If new value for @Lines length will be > then the
–defined length

While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@TextLength-@BasePos+1 ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength –
(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =
0
END
SELECT @Line = isnull(@Line, N”) +
isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N”)
if LEN(@Line) < @DefinedLength and charindex(‘ ‘,
@SyscomText, @TextLength+1 ) > 0
BEGIN
SELECT @Line = @Line + ‘ ‘, @BlankSpaceAdded = 1
END
END
END
END

FETCH NEXT FROM ms_crs_syscom into @SyscomText
END

IF @Line is NOT NULL
INSERT #CommentText VALUES( @LineId, @Line )

select Text from #CommentText order by LineId

CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom

DROP TABLE #CommentText

— ————————————-
— End of extract from sp_helptext
— ————————————-

— Drop the procedure that was setup with dashes and rebuild it with the good stuff
— Version 1.1 mod; makes rebuilding hte proc unnecessary
ROLLBACK TRAN

DROP TABLE #output
GO

the  second way is to use the third party tools , there many third party tools that are available to download

one of the best is the Optillect sql decrypter which is a free ware, good thing about it is it does not need SYSADMIN role as it does not

use DAC , rather it uses DBCC ind, and DBCC PAGE for the required information for the SYS.OBJVALUES table

http://download.cnet.com/Optillect-SQL-Decryptor/3640-10254_4-75300271-1.html

 

download and install it

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Wait stats BACKUPIO, and BACKUPBUFFER

At in order to understand how we accumulate these waits over time first we need to know how  SQL server backup works

Backup and restore work very similar so I will only cover the backup example in this post.

When a backup starts it creates a series of buffers, allocated from the memory outside the buffer pool.  The target is commonly 4MB for each buffer resulting in approximately 4 to 8 buffers.  Details about the calculation are located in: http://support.microsoft.com/kb/904804/en-us

The buffers are transitioned between the free and data queues.   The reader pulls a free buffer, fills it with data and places it on the data queue.   The writer(s) pull filled data buffers from the data queue, process the buffer and return it to the free list.

You get a writer per backup device, each retrieving from the data queue.   So a a backup command with four(4) to disk specifications will have four writers and a reader.   The reader uses async I/O so it can keep up with the writers.

image

For more details about the buffer sizes and counts refer to: http://msdn2.microsoft.com/en-us/library/ms186865.aspx

BUFFERCOUNT = { buffercount | @buffercount_variable }
Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause “out of memory” errors because of inadequate virtual address space in the Sqlservr.exe process.The total space used by the buffers is determined by: buffercount * maxtransfersize.

MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
Specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB)so there are lot of parameters considered here when we have buffer and io waits first and important thing is the backup media

everything depends on the backup media some times it is very slow when they use tape devices which accumulates the ASYNC_IO_COMPLETION , so the data buffer is waiting to become free buffer but it can not as backup device is slow so the writer thread has to wait for the IO operation to complete on the backup device

Always try to avoid the network backup,

Always test different test scenario using different buffer counters

This is not considered a best practical solution in case of VLDB proper testing should be performed

Refer

http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx

Technical Case Study-Backup VLDB Over Network_Final