database-wiki

Don't reinvent the wheel, just realign it!

Generation Insert with data from a table. (Where clause included)

Posted by database-wiki on May 6, 2015

=> Found this code online but it did not work as expected. This is a fixed code.

USE DATABASE_NAME
GO
begin tran t1

DECLARE @TableName SYSNAME
DECLARE @WhereClause VARCHAR(1024)
DECLARE @IdentityInsert INT
DECLARE @ColName SYSNAME
DECLARE @ColType TINYINT
DECLARE @ColStatus TINYINT
DECLARE @DebugMode BIT
DECLARE @ColList NVARCHAR(4000)
DECLARE @ValList NVARCHAR(4000)
DECLARE @SQL1 NVARCHAR(1000)
DECLARE @SQL2 NCHAR(10)
DECLARE @SQL3 NCHAR(4000)

–Your Table Name
SET @TableName = ‘TABLE_NAME’

— limit scope of inserts, this will be hard coded thing to narrow down the set
SET @WhereClause = ‘where CONDITION =VALUE’

— set to 1 if you only want a script
SET @DebugMode = 0

— set to 1 if you want to force IDENTITY_INSERT statements
SET @IdentityInsert = 0

SET @ColList = ”
SET @ValList = ”
SET @SQL1 = ‘SELECT REPLACE(”INSERT INTO ‘ + @TableName + ‘ (‘
SET @SQL2 = ‘) VALUES (‘
SET @SQL3 = ‘)” COLLATE DATABASE_DEFAULT, ”””NULL”””, ”NULL”) FROM ‘
+ @TableName

IF @DebugMode = 1
PRINT ‘– StmtShell: ‘ + @sql1 + @sql2 + @sql3

DECLARE csrColumns CURSOR LOCAL fast_forward
FOR SELECT
c.name,
c.xtype,
c.STATUS
FROM
syscolumns c
INNER JOIN sysobjects o
ON o.id = c.id
WHERE
o.name = @TableName
AND o.xtype IN (‘U’, ‘S’)
AND c.name <> ‘IsLocReallyValid’
ORDER BY
ColID

open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

while @@fetch_status = 0
begin
Print @ColName
if @ColName <> ‘IsLocReallyValid’
BEGIN
if not ((@ColType = 56 AND @ColStatus = 128) AND (@IdentityInsert =0))
set @ColList = @ColList + ‘ ‘ + @ColName

if @ColType in (127,173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165) — numeric types (nulls not supported yet)
if(@ColType = 56 AND @ColStatus = 128) AND (@IdentityInsert =0)
set @ValList = @ValList
else
set @ValList = @ValList + ‘ ”””+isnull(convert(varchar(200),[‘ + @ColName + ‘]),”null”)+”””’
else if @ColType in (175, 239, 231, 231, 167) — uid and string types
set @ValList = @ValList + ‘ ”””+isnull(replace([‘ + @ColName + ‘],””””,””””””),”null”)+”””’
else if @ColType in (36,40,42,58, 61) — dates , uniqueidentifiers(nulls not supported yet)
set @ValList = @ValList + ‘ ”””+isnull(convert(varchar(200),[‘ + @ColName + ‘]),”null”)+”””’

if @DebugMode = 1 begin print ‘– @ValList: ‘ + rtrim(@ValList) end
if (@ColStatus & 0x80) = 0x80 begin set @IdentityInsert = 1 end — Check if column has Identity attribute
END
fetch next from csrColumns into @ColName, @ColType, @ColStatus
end
CLOSE csrColumns
DEALLOCATE csrColumns

SET @ColList = REPLACE(LTRIM(@ColList), ‘ ‘, ‘, ‘)
SET @ValList = REPLACE(LTRIM(@ValList), ‘ ‘, ‘, ‘)

IF @IdentityInsert = 1
AND @DebugMode = 1
PRINT ‘SET IDENTITY_INSERT ‘ + @TableName + ‘ ON’
IF @IdentityInsert = 1
AND @DebugMode = 0
SELECT ‘SET IDENTITY_INSERT ‘ + @TableName + ‘ ON’

IF @DebugMode = 1
PRINT @SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ‘ ‘ + @WhereClause
ELSE
EXEC
(
@SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ‘ ‘
+ @WhereClause
)

IF @IdentityInsert = 1
AND @DebugMode = 1
PRINT ‘SET IDENTITY_INSERT ‘ + @TableName + ‘ ON’
IF @IdentityInsert = 1
AND @DebugMode = 0
SELECT ‘SET IDENTITY_INSERT ‘ + @TableName + ‘ ON’

rollback tran t1

Posted in TSQL PROGRAMMING | Leave a Comment »

Basics of MongoDB and NoSQL.

Posted by database-wiki on April 11, 2014

 

Example Data:

Create a text file and copy the below content later rename it to Products.json

Products.json

{ “_id” : “ac3″, “name” : “AC3 Phone”, “brand” : “ACME”, “type” : “phone”, “price” : 200, “warranty_years” : 1, “available” : true }

{ “_id” : “ac7″, “name” : “AC7 Phone”, “brand” : “ACME”, “type” : “phone”, “price” : 320, “warranty_years” : 1, “available” : false }
{ “_id” : { “$oid” : “507d95d5719dbef170f15bf9″ }, “name” : “AC3 Series Charger”, “type” : [ “accessory”, “charger” ], “price” : 19, “warranty_years” : 0.25, “for” : [ “ac3″, “ac7″, “ac9″ ] }
{ “_id” : { “$oid” : “507d95d5719dbef170f15bfa” }, “name” : “AC3 Case Green”, “type” : [ “accessory”, “case” ], “color” : “green”, “price” : 12, “warranty_years” : 0 }
{ “_id” : { “$oid” : “507d95d5719dbef170f15bfb” }, “name” : “Phone Extended Warranty”, “type” : “warranty”, “price” : 38, “warranty_years” : 2, “for” : [ “ac3″, “ac7″, “ac9″, “qp7″, “qp8″, “qp9″ ] }
{ “_id” : { “$oid” : “507d95d5719dbef170f15bfc” }, “name” : “AC3 Case Black”, “type” : [ “accessory”, “case” ], “color” : “black”, “price” : 12.5, “warranty_years” : 0.25, “available” : false, “for” : “ac3″ }
{ “_id” : { “$oid” : “507d95d5719dbef170f15bfd” }, “name” : “AC3 Case Red”, “type” : [ “accessory”, “case” ], “color” : “red”, “price” : 12, “warranty_years” : 0.25, “available” : true, “for” : “ac3″ }
{ “_id” : { “$oid” : “507d95d5719dbef170f15bfe” }, “name” : “Phone Service Basic Plan”, “type” : “service”, “monthly_price” : 40, “limits” : { “voice” : { “units” : “minutes”, “n” : 400, “over_rate” : 0.05 }, “data” : { “units” : “gigabytes”, “n” : 20, “over_rate” : 1 }, “sms” : { “units” : “texts sent”, “n” : 100, “over_rate” : 0.001 } }, “term_years” : 2 }
{ “_id” : { “$oid” : “507d95d5719dbef170f15bff” }, “name” : “Phone Service Core Plan”, “type” : “service”, “monthly_price” : 60, “limits” : { “voice” : { “units” : “minutes”, “n” : 1000, “over_rate” : 0.05 }, “data” : { “n” : “unlimited”, “over_rate” : 0 }, “sms” : { “n” : “unlimited”, “over_rate” : 0 } }, “term_years” : 1 }
{ “_id” : { “$oid” : “507d95d5719dbef170f15c00″ }, “name” : “Phone Service Family Plan”, “type” : “service”, “monthly_price” : 90, “limits” : { “voice” : { “units” : “minutes”, “n” : 1200, “over_rate” : 0.05 }, “data” : { “n” : “unlimited”, “over_rate” : 0 }, “sms” : { “n” : “unlimited”, “over_rate” : 0 } }, “sales_tax” : true, “term_years” : 2 }
{ “_id” : { “$oid” : “507d95d5719dbef170f15c01″ }, “name” : “Cable TV Basic Service Package”, “type” : “tv”, “monthly_price” : 50, “term_years” : 2, “cancel_penalty” : 25, “sales_tax” : true, “additional_tarriffs” : [ { “kind” : “federal tarriff”, “amount” : { “percent_of_service” : 0.06 } }, { “kind” : “misc tarriff”, “amount” : 2.25 } ] }

Posted in MONGODB | Leave a Comment »

The fulltext filter daemon host (FDHost.exe) process has stopped abnormally.

Posted by database-wiki on November 23, 2013

From SQL Server Error Logs here’s what we have:-

spid56s The fulltext filter daemon host (FDHost) process has stopped abnormally. This can occur if an incorrectly configured or malfunctioning linguistic component, such as a wordbreaker, stemmer or filter has caused an irrecoverable error during full-text indexing or query processing. The process will be restarted automatically.
spid42s Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 65536

Memory Manager KB
—————————————- ———-
VM Reserved 8451272
VM Committed 253496
Locked Pages Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0

MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
—————————————- ———-
VM Reserved 8396800
VM Committed 200960
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 400

Memory node Id = 0 KB
—————————————- ———-
VM Reserved 8447624
VM Committed 249960
Locked Pages Allocated 0
MultiPage Allocator 26488
SinglePage Allocator 85696
2011-10-07 13:09:34.65 spid42s
Memory node Id = 64 KB
—————————————- ———-
VM Reserved 2560
VM Committed 2504
Locked Pages Allocated 0
MultiPage Allocator 2416
SinglePage Allocator 85696

=> It is quite evident that none of the Clerks were consuming memory, perfmon proves that as well.

Perfmon
=======

Counter Name Avg Max Min
————————————- ——————– ——————– ——————–
Memory\Available MBytes 6164.592 6322.000 6069.000
Memory\Cache Bytes 37049968.095 42672128.000 22970368.000
Process(fdhost)\Private Bytes 72012309.207 126963712.000 18948096.000
Process(fdhost)\Virtual Bytes 27535638746.130 55433195520.000 496201728.000
Process(fdlauncher)\Private Bytes 1773737.657 1810432.000 1728512.000
Process(fdlauncher)\Virtual Bytes 26888616.142 27893760.000 25796608.000
Process(sqlservr)\Private Bytes 273409090.651 279138304.000 257171456.000
Process(sqlservr)\Virtual Bytes 8928812910.580 8935145472.000 8918106112.000

=> So here’s a snapshot from Perfmon which indicates that fdhost.exe consumes all virtual memory (RAM+Page file) on the machine until the machine runs out of memory and fdhost dies.

virtualbytes-fdhost

Further from the crawl logs which was collected yesterday, here’s what we notice:

From SQLFT0000700005.LOG:-
{
spid18s Error ‘0x80070005′ occurred during full-text index population for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’), full-text key value ‘105135’. Attempt will be made to reindex it.
spid18s Informational: Full-text Auto population completed for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’). Number of documents processed: 1. Number of documents failed: 1. Number of documents that will be retried: 1.
spid33s A full-text retry pass of Auto population started for table or indexed view ‘[Super7].[ram5].[TEXT]’. Table or indexed view ID is ‘405576483’. Database ID is ‘7’.
spid32s Error ‘0x80070005′ occurred during full-text index population for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’), full-text key value ‘105135’. Attempt will be made to reindex it.
}

From SQLFT0000700005.1 :-
{
spid45s Error ‘0x80070005′ occurred during full-text index population for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’), full-text key value ‘16779605’. Attempt will be made to reindex it.
spid45s Error ‘0x80070005′ occurred during full-text index population for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’), full-text key value ‘16779606’. Attempt will be made to reindex it.


spid20s Error ‘0x80070005′ occurred during full-text index population for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’), full-text key value ‘16782193’. Attempt will be made to reindex it.
spid33s Error ‘0x80070005′ occurred during full-text index population for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’), full-text key value ‘16782194’. Attempt will be made to reindex it.

spid67s Informational: Full-text retry pass of Manual population completed for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’). Number of retry documents processed: 2343. Number of documents failed: 2343.

Followed by the below :-
{
spid28s Error ‘0x80070005′ occurred during full-text index population for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’), full-text key value ‘114045’. Attempt will be made to reindex it.
spid28s Informational: Full-text Auto population completed for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’). Number of documents processed: 1. Number of documents failed: 1. Number of documents that will be retried: 1.
spid47s A full-text retry pass of Auto population started for table or indexed view ‘[Super7].[ram5].[TEXT]’. Table or indexed view ID is ‘405576483’. Database ID is ‘7’.
spid43s Error ‘0x80070005′ occurred during full-text index population for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’), full-text key value ‘114045’. Attempt will be made to reindex it.
}

=> Error Code “0x80070005” implies “Access is Denied”. This would makes SQL server service unable to open and release the shared memory sections created by FDhost.exe.

Further to this From SQLFT0000700005.2 :-
{
spid83s Error ‘0x800705af’ occurred during full-text index population for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’), full-text key value ‘108279’. Attempt will be made to reindex it.
spid76s Error ‘0x800705af’ occurred during full-text index population for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’), full-text key value ‘108280’. Attempt will be made to reindex it.
spid73s Informational: Full-text retry pass of Manual population completed for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’). Number of retry documents processed: 15003. Number of documents failed: 15003.


spid44s Error ‘0x800705af’ occurred during full-text index population for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’), full-text key value ‘114045’. Attempt will be made to reindex it.
spid44s Informational: Full-text Auto population completed for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’). Number of documents processed: 1. Number of documents failed: 1. Number of documents that will be retried: 1.
spid30s A full-text retry pass of Auto population started for table or indexed view ‘[Super7].[ram5].[TEXT]’. Table or indexed view ID is ‘405576483’. Database ID is ‘7’.
spid36s Error ‘0x800705af’ occurred during full-text index population for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’), full-text key value ‘108281’. Attempt will be made to reindex it.
spid36s Error ‘0x800705af’ occurred during full-text index population for table or indexed view ‘[Super7].[ram5].[TEXT]’ (table or indexed view ID ‘405576483’, database ID ‘7’), full-text key value ‘108282’. Attempt will be made to reindex it.
}

=> Error Hex Code 0x800705af implies “The paging file is too small for this operation to complete.” which most likely is a result of the fact that Fdhost.exe has eaten up all the memory.

Now this is a known issue with Fdhost.exe which is documented under:
A memory leak occurs in FDHOST.exe process in SQL Server 2008 on a Windows Server 2003 Domain Controller
http://support.microsoft.com/kb/2014053

Please note that even though this talks about DC’s but upon further research I can confirm that the issue does not just occur on SQL Server instances running on a DC, it can even occur on any server where the account under which the FDHOST Launcher service is started does not have the correct permissions. We have in fact introduced a new rule in the SQL Server 2008 R2 Best Practice Analyser to check that the account used for the FDHOST Launcher service has the correct permissions or not.

CONCLUSION:-

Looks like the service account doesn’t have enough permission for which it is unable to open and release the shared memory sections created by FDhost.exe.

Please change the service account to a Domain account which has sufficient permission to get rid of the issue.

REFERENCES:-

2159124: How to setup the FDHOST Launcher service to work properly
http://support.microsoft.com/default.aspx?scid=kb;EN-US;2159124

How to: Set the FDHOST Launcher (MSSQLFDLauncher) Service Account for Full-Text Search (SQL Server Configuration Manager)
http://msdn.microsoft.com/en-us/library/ms345189.aspx

Posted in CORE ISSUES, FULL-TEXT SEARCH | Leave a Comment »

Best way to Delete from Large table using date range or row count!

Posted by database-wiki on August 27, 2013

=> From my analysis, I found using Top clause in conjunction with delete statement – significantly quicker than other methods.

=> Modify the database name and delete statement with correct table name also i have assumed the table has got a date column.

=> We can use the SP in report mode to know the rows deleted for date range and date range for a given rows deleted.

USE [TEST_DATABASE]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create Procedure [dbo].[sp_LargeTableCleanup]

@RowCount BIGINT,

@DateMin DateTime,

@DateMax DateTime,

@Report char

AS

BEGIN

SET NOCOUNT ON

DECLARE @maxOfAType bigint = 100000,

@delayLength char(14) = ’00:00:00.010′,@TempDelRows INT =0,

@rowsDeleted bigint =0;

SET @rowsDeleted = @maxOfAType;

SET @TempDelRows = 0;

DECLARE @Terminator INT  =0

If (@RowCount is null AND  @DateMin is not null AND @DateMax is not null AND @DateMin <= @DateMax and @Report=’Y’)
BEGIN
SET @Terminator =999
                  Select @rowsDeleted=COUNT(*) from test_database..LargeTable  
                  And InsertUTC >= @DateMin and InsertUTC < @DateMax+1
                  Print cast(@rowsDeleted as varchar(10)) + ‘ rows will deleted.’
            END

If (@RowCount >= 0 AND @DateMin is null AND @DateMax is null AND @Report=’Y’)
BEGIN
SET @Terminator =999
      SET @maxOfAType = @RowCount;
            select top (@maxOfAType) @DateMin= MIN(InsertUTC),@DateMax=MAX(InsertUTC)
            from test_database..LargeTable 
            SET @rowcount = @@ROWCOUNT;  
            SET @TempDelRows = @TempDelRows + @rowcount    
            Print ‘Date range between ‘ + cast(@Datemin as varchar(12)) + ‘and ‘ + cast(@Datemax as varchar(12)) + ‘ will be deleted.’
      END

If @RowCount IS NULL AND  @DateMin is not null AND @DateMax is not null AND @DateMin <= @DateMax

BEGIN

SET @Terminator =999

WHILE @rowsDeleted > 0

Begin

delete top (@maxOfAType)  from test_database..LargeTable

Where InsertUTC >= @DateMin and InsertUTC < @DateMax+1

SET @rowsDeleted = @@ROWCOUNT;

SET @TempDelRows = @TempDelRows + @rowsDeleted

WAITFOR DELAY @delayLength;

END

IF @TempDelRows >=  0

BEGIN

Print cast(@TempDelRows as varchar(10)) + ‘ rows deleted’

END

END

If (@RowCount >= 0 AND @DateMin is null and @DateMax is null )

BEGIN

SET @TempDelRows  = 0

SET @Terminator =999

SET @maxOfAType = @RowCount;

WHILE @RowCount > 0

Begin

delete top (@maxOfAType)

from test_database..LargeTable

SET @rowcount = @@ROWCOUNT;

SET @TempDelRows = @TempDelRows + @rowcount

END

IF @TempDelRows >=0

BEGIN

Print cast(@TempDelRows as varchar(10)) + ‘ rows deleted’

END

END

if @Terminator=0

BEGIN

Print ‘Please Enter Valid RowCount or Valid DateRange’

END

END

GO

Testing:

Reporting:

Exec [dbo].[uspPub_DimAddressCleanup] 50,NULL, NULL,’Y’
Exec [dbo].[uspPub_DimAddressCleanup] NULL,’2012/12/01′,’2013/03/29′ ,’Y’

Actual Delete:

Exec [dbo].[uspPub_DimAddressCleanup] 50,NULL, NULL,’N’
Exec [dbo].[uspPub_DimAddressCleanup] NULL,’2012/12/01′,’2013/03/29′ ,’N’

Exec [dbo].[uspPub_DimAddressCleanup] 50,’2013/01/02′,’2013/01/01′ — should not execute

Erroneous input:

Exec [dbo].[uspPub_DimAddressCleanup] NULL,’2013/01/02′,’2013/01/01′ ,’Y’– should not execute

Exec [dbo].[uspPub_DimAddressCleanup] 50,’2013/01/01′,’2013/01/02′,’Y’ — should not execute

Exec [dbo].[uspPub_DimAddressCleanup] 50,’2013/01/01′, NULL,’N’ — should not execute

Exec [dbo].[uspPub_DimAddressCleanup] 50,NULL,’2013/01/02′,’N’ — should not execute

Posted in TSQL PROGRAMMING | Leave a Comment »

With Bob Ward… (Principal Escalation Engineer at Microsoft)

Posted by database-wiki on August 24, 2013

IMG_0756

Posted in PERSONAL | Leave a Comment »

Using the SQL Server fill factor effectively!

Posted by database-wiki on August 23, 2013

=> Firstly i would thank my colleague (Naveen Kumar Gopinath) for deep dive on fill factor and the credit goes to him for this utility.

=> The idea behind this utility is to use the fill factor on indexes efficiently.

=> How to monitor fill factor with respect to the growth of data on an indexes and

recommend fill factor value depending on the data size.

=> How effective does the fill factor improves performance with INSERT/UPDATES/DELETE and SELECT.

The Approach followed:

===================

=> Capture actual fill factor values on every indexes daily.

=> Derive the recommended fill factor using the data capture for a week.

Tables Used:

===========

=> Table FillFactorTable contains the table names and the indexes for which the information will be capture on a daily basis.

Why this table? We don’t want to capture information on all the tables exists in the database.

We can go ahead and add or remove the tables which we would like to capture the information.

=> Table FillFactorInformation will capture the fill factor and related information to derive the recommended Fill factor.

Few of the Key Factors to be consider for fill factor

=========================================

Key entry – this is the value of the key, it is also called a filled index row.

Empty slot – an empty index row.

Page – this is the smallest physical container for a group of keys, there are 8 pages in an extent.

Extents can be mixed, for example, one extent may contain eight different small indexes.

Defragmentation- this may occur because of either a reorganization or rebuild of indexes.

Max_record_size_in_Bytes- the maximum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the maximum record size in the IN_ROW_DATA allocation unit.

For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the maximum record size in the complete allocation unit.

Avg_record_size_in_bytes- Average record size in bytes

Record_count- total number of records applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the total number of records in the IN_ROW_DATA allocation unit.

Avg_fragmentation_in_percent- Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.

The value is measured as a percentage and takes into account multiple files

Room for improvement.

=====================

The fillfactor required information are captured using the DMV

sys.dm_db_index_physical_stats(DB_ID(@DatabaseName) ,  OBJECT_ID(@TableName) , NULL , NULL , ‘DETAILED’ )

Which is time consuming process to capture the information, this fetches the required information. The time taken to fetch the information also depends on the size of the database, bigger the db size the more the time to fetch the information.

For more details

http://msdn.microsoft.com/en-us/library/ms188917.aspx

Script 1:

USE May10

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FillFactorInformation]’) AND type in (N’U’))

BEGIN

— drop table [FillFactorInformation]

CREATE TABLE [dbo].[FillFactorInformation](

ID                                    BIGINT IDENTITY(1,1),

[DatabaseName]                  NVARCHAR(128)   NOT NULL,

[TableName]                     NVARCHAR(100)   NOT NULL,

[SchemaName]                    NVARCHAR(10)    NOT NULL,

[IndexName]                     NVARCHAR(100)     NOT NULL,

[IndexKey]                      NVARCHAR(50)    NULL,

[KeySize]                       INT               NOT NULL,

[IndexBytes]                    FLOAT                   NOT NULL,

[OrginalFillFactor]             INT               NOT NULL,

[GrowthPercentage]        FLOAT                   NULL,

[FragmentationPercentage] FLOAT                   NOT NULL,

[AlterStatement]          NVARCHAR(4000)  NULL,

[RecommendedFillFactor]  INT               NULL,

[InsertUTC]                     DATETIME          NOT NULL,

CONSTRAINT [PK_FillFactorInformation] PRIMARY KEY CLUSTERED

(

[DatabaseName] ASC,

[TableName] ASC,

[SchemaName] ASC,

[IndexName] ASC,

[InsertUTC] ASC

))

END

GO

Script 2:

USE May10

GO

IF NOT EXISTS ( Select 1 From sys.sysobjects WHERE Name = ‘FillFactorTable’)

BEGIN

CREATE TABLE

FillFactorTable

(

[ID]              BIGINT IDENTITY(1,1),

[DatabaseId]      INT,

[DatabaseName]    VARCHAR(400),

[TableId]         INT,

[TableName]       VARCHAR(400),

[IndexKey]        VARCHAR(100)  NOT NULL,

[IndexName]       VARCHAR(1000) NOT NULL,

OrderID               INT

)

END

GO

Script 3:

Output:

USE may10

GO

DECLARE @Cnt INT = 0

Select @Cnt = Count(1) FROM [may10].dbo.[FillFactorTable]

IF @Cnt = 0

BEGIN

INSERT INTO [may10].dbo.[FillFactorTable]  (OrderID , DatabaseID , DatabaseName , TableId , TableName , IndexKey , IndexName )

Select DISTINCT

DENSE_RANK() OVER(  ORDER BY s.id) as OrderID,

23                                                 as DatabaseID,

‘may10’                                      as DatabaseName,

s.Id                                         as TableID,

s.name                                             as TableName,

cast(i.object_id AS VARCHAR(11))+’_’+cast(i.index_id AS VARCHAR(11)) as IndexKey,

i.name                                                                                                as IndexName

FROM

sys.indexes i

INNER JOIN

sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id

INNER JOIN

sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id

INNER JOIN

sys.sysobjects s ON c.object_id = s.ID

INNER JOIN sys.dm_db_partition_stats st ON  st.OBJECT_ID  = c.object_id

WHERE st.index_id = 1

and st.row_count > 8000000

END

GO

OUTPUT:

select * from may10.dbo.FillFactorTable

 

ID    DatabaseId  DatabaseName      TableId     TableName         IndexKey            IndexName                           OrderID

===============================================================================================================================

1     23                may10       373576369   tblStatements      373576369_1       tblStatements_StartTime             1

2     23                may10       373576369   tblStatements      373576369_10      tblStatements_BatchEndSeq           1

3     23                may10       373576369   tblStatements      373576369_11      tblStatements_BatchStmtStart        1

4     23                may10       373576369   tblStatements      373576369_2       tblStatements_EndTime               1

5     23                may10       373576369   tblStatements      373576369_3       tblStatements_HashId                1

6     23                may10       373576369   tblStatements      373576369_4       tblStatements_SessionRequestBatch   1

7     23                may10       373576369   tblStatements      373576369_5       tblStatements_StmtSeq               1

8     23                may10       373576369   tblStatements      373576369_6       tblStatements_StartEndSeq           1

9     23                may10       373576369   tblStatements      373576369_7       tblStatements_EndSeq                1

10    23                may10       373576369   tblStatements      373576369_8       tblStatements_ConnId                1

11    23                may10       373576369   tblStatements      373576369_9       tblStatements_BatchStartSeq         1

12    23                may10       405576483   tblPlanRows       405576483_1       PK_tblPlanRows                      2

Script 4:

USE may10

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tvf_GetFillFactor]’) AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’))

BEGIN

DROP FUNCTION [dbo].[tvf_GetFillFactor]

END

GO

CREATE FUNCTION [dbo].[tvf_GetFillFactor]

(

@KeyBytes           FLOAT,

@GrowthPercentage FLOAT

)

RETURNS INT

AS

BEGIN

DECLARE @FillFactor FLOAT

DECLARE @Rate           FLOAT

DECLARE @Offset         FLOAT

IF @KeyBytes < 2

SET @KeyBytes=2

IF @GrowthPercentage > 0.06

SET @GrowthPercentage = 0.06

IF @GrowthPercentage < 0.001

SET @GrowthPercentage = 0.001

SET @Rate=- 5.2312 * Power(@keybytes,-0.244) — R=0.95

SET @Offset=1 – 0.2193 * Power(@keybytes, – 0.462) — R = 0.99

SET @FillFactor= CEILING(100 * (@Rate * @GrowthPercentage + @Offset))

IF @FillFactor < 50

SET @FillFactor = 50

IF @FillFactor > 99

SET @FillFactor = 99

RETURN @FillFactor

END

GO

Script 5:

USE may10

GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspPub_GenerateFillFactor]’) AND type in (N’P’, N’PC’))

BEGIN

DROP PROCEDURE [dbo].[uspPub_GenerateFillFactor]

END

GO

CREATE PROCEDURE [dbo].[uspPub_GenerateFillFactor]

(@CurrentDate DateTime = null)

AS

DECLARE @RecordedAt       DATETIME

IF @CurrentDate  IS NULL

SET @RecordedAt = CONVERT(DATE , GETUTCDATE());

ELSE

SET @RecordedAt = CONVERT(DATE , @CurrentDate);

DECLARE @DatabaseId   BIGINT

DECLARE @DatabaseName NVARCHAR(100)

DECLARE @TableId      BIGINT

DECLARE @TableName    NVARCHAR(100)

DECLARE @IndexName        VARCHAR(1000)

DECLARE @FillFactorValue VARCHAR(3)

DECLARE @MySql            NVARCHAR(MAX)

DECLARE @intFlag INT

DECLARE @iRowCnt INT

DECLARE @ID INT

Select @iRowCnt = MAX(OrderID)  , @intFlag = MIN(OrderID) from [Utility].[dbo].[FillFactorTable]

WHILE (@intFlag <=@iRowCnt)

BEGIN

Select DISTINCT @DatabaseId = DatabaseId , @DatabaseName = DatabaseName , @TableId = TableId , @TableName = TableName   From [Utility].[dbo].[FillFactorTable] WHERE OrderID = @intFlag

IF @TableId  <> 0

BEGIN

INSERT INTO [Utility].[dbo].[FillFactorInformation]

([DatabaseName]

,[TableName]

,[IndexName]

,[IndexKey]

,[KeySize]

,[IndexBytes]

,[OrginalFillFactor]

,[FragmentationPercentage]

,SchemaName

,InsertUTC)

SELECT

db_name([database_id])                                                                       AS [DatabaseName],

CAST(OBJECT_NAME(S.[Object_Id]) AS VARCHAR(100))                                 AS [TableName],

i.[Name]                                                                                           AS [IndexName],

CAST(S.object_id AS VARCHAR(11))+’_’ + CAST(S.index_id AS VARCHAR(11)) AS [IndexKey],

S.[KeySize]                                                                                        AS KeySize,

S.[IndexBytes]                                                                                     AS IndexBytes,

i.fill_factor                                                                                    AS FillFactorValue,

S.[FragmentationPercentage]                                                                  AS FragmentationPercentage,

‘dbo’                                                                                              AS SchemaName,

CurrentDate                                                                                        AS InsertUTC

FROM (Select

[database_id],

[object_id],

[index_id],

max(max_record_size_in_Bytes)                                              as KeySize,

sum(avg_record_size_in_bytes * record_count)                         as IndexBytes,

Sum(avg_fragmentation_in_percent * page_count)/Sum(page_count) as FragmentationPercentage,

@RecordedAt                                                                            as CurrentDate

FROM sys.dm_db_index_physical_stats(DB_ID(@DatabaseName) ,  OBJECT_ID(@TableName) , NULL , NULL , ‘DETAILED’ ) as dd

WHERE index_type_desc IN (‘CLUSTERED INDEX’,’NONCLUSTERED INDEX’)

AND alloc_unit_type_desc=’IN_ROW_DATA’

GROUP BY [database_id],[Object_Id] ,[index_id]

HAVING SUM(page_count) > 0

) S

JOIN Sys.Indexes i

ON  i.[Object_id] = S.[Object_Id]

AND i.[Index_id]  = S.[Index_id]

END

SET @intFlag = @intFlag + 1

END

UPDATE [Utility].[dbo].[FillFactorInformation]

SET GrowthPercentage = CASE WHEN [EstStDev]*2 + [EstAvg] < 0.001 THEN

0.001

ELSE

[EstStDev]*2+[EstAvg]

END

From [Utility].[dbo].[FillFactorInformation] TT

JOIN (

SELECT

ITT.[IndexKey]                                                                                              AS IndexKey,

Stdev([IndexBytes])/Max([IndexBytes])                                                           AS [EstStDev],

(Max([IndexBytes])- Min([IndexBytes]))/Min([IndexBytes])/Count([IndexBytes]) AS [EstAvg]

FROM [Utility].[dbo].[FillFactorInformation] ITT INNER JOIN [Utility].[dbo].[FillFactorTable] AFF

ON ITT.[IndexKey]=AFF.[IndexKey]

WHERE [InsertUTC] > GetUTCDate()-7

and [IndexBytes] IS NOT NULL

and [IndexBytes] > 0

GROUP BY ITT.[IndexKey]  ) Estimates

ON [Estimates].[IndexKey]= TT.[IndexKey]

And InsertUTC = @RecordedAt

And Convert(varchar(10), [InsertUTC] ,103)  = Convert(varchar(10), @RecordedAt,103)

— ———————

CREATE TABLE #temp (TableName VARCHAR(255), IndexName VARCHAR(255), FillFactorValue VARCHAR(3) , ID BIGINT)

INSERT INTO  #temp (TableName , IndexName , FillFactorValue , ID )

SELECT

‘[dbo].[‘+TableName+’]’,

[IndexName],

ISNULL(Cast([dbo].[tvf_GetFillFactor](KeySize,GrowthPercentage)as varchar(3)) , 0 ) as FF ,

ID as ID

FROM  [Utility].[dbo].[FillFactorInformation]

WHERE [InsertUTC]= @RecordedAt

DECLARE Index_cursor CURSOR FOR

SELECT TableName ,IndexName ,FillFactorValue, ID

FROM #temp

OPEN Index_cursor

FETCH NEXT FROM Index_cursor INTO @TableName, @IndexName, @FillFactorValue , @ID

WHILE @@FETCH_STATUS = 0

BEGIN

SET @MySql = ‘ALTER INDEX [‘ + @IndexName + ‘] ON ‘ + @TableName +’ REBUILD

WITH (PAD_INDEX = ON, FILLFACTOR = ‘ + @FillFactorValue +  ‘)’

Update [Utility].[dbo].[FillFactorInformation] SET AlterStatement = @MySql , RecommendedFillFactor = @FillFactorValue    WHERE ID = @ID

FETCH NEXT FROM Index_cursor INTO @TableName, @IndexName, @FillFactorValue , @ID

END

CLOSE Index_cursor

DEALLOCATE Index_cursor

Drop table #temp

GO

Output:

Use may10

go

Exec uspPub_GenerateFillFactor

Posted in GENERAL PERFORMANCE | Leave a Comment »

Avoid auto-growth and automatically grow files every month based on previous month growth pattern.

Posted by database-wiki on April 22, 2013

Scenario:

======

—  Your Data file growth increments need to be tuned based on trends of database growth.  Tuning the file growth increments can reduce database engine overhead by limiting the frequency of file growth.  Database performance is being impacted if the database engine is increasing the file size during data insertion.

—  Your Data file growth increments have not been adjusted in a very long time.  We have grown more than double in size (possibly triple) since data file growth increments have been adjusted.

—  Your Data file growth increments should be adjusted so that data file sizes are not extended more than once every other week.

—  Data file growth controlled by either percentage of growth or by a specific size in Mb.   Is it preferred to grow data files by specific size? Yes it is

Resolution:

========

— I have used the logic of determining the data file and log file auto growth using SQL Server default trace.

— One good thing about SQL Server default trace is that we can determine when the data file and log file has grown using event class. EventClass = 92 is Data File Auto-growth including indexes And EventClass = 93 is Log File Auto-growth.

— We can also use this trace to determine who had done what in the SQL Server instance if we enough .trc files to trace back to the date the event has happened. Example, if anybody had dropped an object or database etc. J

  1. First, I have created a logging table DataAndLogFileGrowthLog.

USE [Utility]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DataAndLogFileGrowthLog]’) AND type in (N’U’))

BEGIN

CREATE TABLE [dbo].[DataAndLogFileGrowthLog](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[Spid] [varchar](5) NOT NULL,

[DatabaseID] [bigint] NOT NULL,

[DatabaseName] [varchar](255) NOT NULL,

[FileId] [bigint] NOT NULL,

[FileName] [varchar](255) NOT NULL,

[PhysicalName] [varchar](1000) NOT NULL,

[Drive] [varchar](10) NOT NULL,

[FreeSpaceMB] [bigint] NOT NULL,

[ApplicationName] [varchar](500) NULL,

[EventClass] [bigint] NOT NULL,

[GrowthMB] [bigint] NOT NULL,

[GrowthCount] [bigint] NOT NULL,

[GrowthDurationMS] [bigint] NOT NULL,

[GrowthDate] [date] NOT NULL,

[GrowthStartTime] [time](7) NOT NULL,

[GrowthEndTime] [time](7) NOT NULL,

[IsLogFile] [bit] NULL,

[AlterAndReport] [bit] NULL,

[CanGrow] [bit] NULL,

[AlterStatement] [varchar](1000) NULL,

PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_PADDING OFF

GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_DataAndLogFileGrowthLog_IsLogFile]’) AND type = ‘D’)

BEGIN

ALTER TABLE [dbo].[DataAndLogFileGrowthLog] ADD  CONSTRAINT DF_DataAndLogFileGrowthLog_IsLogFile DEFAULT ((0)) FOR [IsLogFile]

END

GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_DataAndLogFileGrowthLog_AlterAndReport]’) AND type = ‘D’)

BEGIN

ALTER TABLE [dbo].[DataAndLogFileGrowthLog] ADD  CONSTRAINT DF_DataAndLogFileGrowthLog_AlterAndReport DEFAULT ((0)) FOR [AlterAndReport]

END

GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_DataAndLogFileGrowthLog_CanGrow]’) AND type = ‘D’)

BEGIN

ALTER TABLE [dbo].[DataAndLogFileGrowthLog] ADD  CONSTRAINT DF_DataAndLogFileGrowthLog_CanGrow DEFAULT ((0)) FOR [CanGrow]

END

GO

2. DataAndLogFileGrowthLogging stored procedure to scan the default SQL trace on daily basis and to log the details in DataAndLogFileGrowthLog table.

USE [Utility]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF EXISTS (SELECT 1     FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspPub_DataAndLogFileGrowthLogging]’) AND type IN (N’P’,N’PC’))

DROP PROCEDURE [dbo].[uspPub_DataAndLogFileGrowthLogging]

GO

CREATE PROCEDURE [dbo].[uspPub_DataAndLogFileGrowthLogging]

AS

IF OBJECT_ID(‘tempdb..#FreeSpaceDrives’, ‘u’) IS NOT NULL

BEGIN

DROP TABLE #FreeSpaceDrives;

END

CREATE TABLE #FreeSpaceDrives (DRIVE CHAR(1) PRIMARY KEY , FREESPACE BIGINT NOT NULL )

INSERT INTO #FreeSpaceDrives

EXECUTE master.dbo.xp_fixeddrives;

DECLARE @filename NVARCHAR(1000);

DECLARE @ReportHTML NVARCHAR(MAX);

DECLARE @Subject NVARCHAR(250);

DECLARE @12MonthsAgo DATETIME2(7) = DATEADD(dd, 0, DATEDIFF(dd, 0, dateadd(mm, – 12, getutcdate())));

SET @12MonthsAgo = dateadd(dd, – (datepart(dd, @12MonthsAgo) – 1), @12MonthsAgo);

— Get the name of the current default trace

SELECT @filename = CAST(value AS NVARCHAR(1000))

FROM::fn_trace_getinfo(DEFAULT)

WHERE traceid = 1

AND property = 2;

INSERT INTO [utility].[dbo].[DataAndLogFileGrowthLog] (

[Spid]

,[DatabaseID]

,[DatabaseName]

,[FileId]

,[FileName]

,[PhysicalName]

,[Drive]

,[FreeSpaceMB]

,[ApplicationName]

,[EventClass]

,[GrowthMB]

,[GrowthCount]

,[GrowthDurationMS]

,[GrowthDate]

,[GrowthStartTime]

,[GrowthEndTime]

,[IsLogFile]

)

SELECT ftg.SPID

,ftg.DatabaseID

,ftg.DatabaseName

,m.file_id

,ftg.FileName

,m.physical_name

,substring(m.physical_name, 1, 1) AS Drive

,b.FreeSpace AS ‘FreeSpaceMB’

,ftg.ApplicationName

,EventClass

,SUM(ftg.IntegerData * 8) / 1024.0 AS ‘Growth(MB)’

,COUNT(*) AS GrowthCount

,SUM(ftg.duration / 1000) AS ‘GrowthDuration(MS)’

,convert(VARCHAR(10), DATEADD(d, – 1, GETDATE()), 120) AS GrowthDate

,convert(CHAR(5), StartTime, 108) AS GrowthStartTime

,convert(CHAR(5), EndTime, 108) AS GrowthEndTime

,CASE

WHEN EventClass = 93

THEN 1

ELSE 0

END AS ‘ISLogFile?’

FROM::fn_trace_gettable(@filename, DEFAULT) AS ftg

INNER JOIN sys.master_files m ON ftg.FileName = m.NAME

INNER JOIN #FreeSpaceDrives AS b ON substring(m.physical_name, 1, 1) = b.[DRIVE]

WHERE EventClass IN (92 , 93 ) — Data And Log File Auto-grow

AND StartTime > DATEADD(dy, – 1, GETDATE())

AND DatabaseName NOT IN (

SELECT NAME = D.NAME

FROM sys.databases D

WHERE (

D.database_id BETWEEN 1

AND 3 – exclude master, model, and msdb

OR D.NAME LIKE ‘ReportServer%’ – exclude Report Server

OR D.is_distributor = 1 – exclude Replication

)

AND D.source_database_id IS NULL — not a snapshot

AND D.state_desc = N’ONLINE’ — is online

AND D.user_access_desc = N’MULTI_USER’ — open for all users

AND D.is_read_only = 0

)

GROUP BY ftg.SPID

,ftg.DatabaseID

,ftg.DatabaseName

,m.file_id

,ftg.FileName

,m.physical_name

,Drive

,b.FREESPACE

,ftg.ApplicationName

,EventClass

,ftg.StartTime

,ftg.EndTime

ORDER BY databasename DESC

— Clean up

IF OBJECT_ID(‘tempdb..#FreeSpaceDrives’, ‘u’) IS NOT NULL

BEGIN

DROP TABLE #FreeSpaceDrives;

END

IF Day(dateadd(D, 1, GETUTCDATE())) = 1

BEGIN

UPDATE DGL

SET CanGrow = 1

,AlterAndReport = 1

,AlterStatement = ‘ALTER DATABASE ‘ + Databasename + ‘ MODIFY FILE (NAME=”’ + FileName + ”’,SIZE=’ + CAST(Qry.GrowthMB AS VARCHAR(50)) + ‘MB);’

FROM (

SELECT MAX(ID) AS ID

,Sum([GrowthMB]) AS GrowthMB

FROM [DataAndLogFileGrowthLog](NoLock)

GROUP BY Databasename

,FileId

,[Filename]

HAVING Sum([FreeSpaceMB]) >= Sum([GrowthMB])

) AS Qry

INNER JOIN [DataAndLogFileGrowthLog] (nolock) DGL ON Qry.ID = DGL.ID

— Delete data more than 1 year old.

DELETE FROM [utility].[dbo].[DataAndLogFileGrowthLog]  WHERE growthdate < @12MonthsAgo

END

GO

3. DataAndLogFileResize stored procedure to do the alter on last day of every month and send an the email.

USE [Utility]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspPub_DataAndLogFileResize]’) AND type IN (N’P’,N’PC’))

DROP PROCEDURE [dbo].[uspPub_DataAndLogFileResize]

GO

CREATE PROCEDURE [dbo].[uspPub_DataAndLogFileResize]

AS

BEGIN TRY

BEGIN

DECLARE @Sql NVARCHAR(4000)

DECLARE @RowCnt INT = 0

DECLARE @pointer INT = 0

SELECT @RowCnt = MAX(ID)

FROM dbo.DataAndLogFileGrowthLog(NOLOCK)

WHERE AlterAndReport = 1

AND Month(Growthdate) = Month(getutcdate())

AND Year(Growthdate) = Year(getutcdate())

WHILE @pointer < @RowCnt

BEGIN

SELECT @Sql = AlterStatement

FROM dbo.DataAndLogFileGrowthLog

WHERE AlterAndReport = 1

AND Month(Growthdate) = Month(getutcdate())

AND Year(Growthdate) = Year(getutcdate())

AND ID = @pointer

EXECUTE sp_executesql @Sql

SET @pointer = @pointer + 1

END

DECLARE @xmlHeaderdata XML

DECLARE  @xmlHeaderdata1 Varchar(max)

—          Select Top 1  @tableVar  ‘<p><br>’ + cast(ID as varchar(10)) +  Description + ‘</br></p>’ from NH_RPT..DimEventType

Select  @xmlHeaderdata1=

( SELECT

CAST(DatabaseName as Varchar(400)) as [td],

CAST([FileName]as Varchar(400))  as [td],

CAST(PhysicalName as Varchar(400)) as [td],

CAST(FreeSpaceMB as Varchar(400))  as [td],

CAST( GrowthMB as Varchar(400))           as [td],

CAST(GrowthCount as Varchar(400)) as [td],

CAST(GrowthDate as Varchar(400)) as [td],

CAST(IsLogFile as Varchar(400))   as [td],

CAST(AlterStatement as Varchar(400)) as [td]

FROM Utility.dbo.DataAndLogFileGrowthLog

–WHERE AlterAndReport = 1

—          AND Month(Growthdate) = Month(getutcdate())

—          AND Year(Growthdate) = Year(getutcdate())

For XML raw(‘tr’), Elements

)

–bgcolor=”lightblue”

SET @xmlHeaderdata1='<table border=”3″>

<tr>

<td>DatabaseName</td><td>FileName</td><td>PhysicalName</td><td>FreeSpaceMB</td><td>GrowthMB</td><td>GrowthCount</td>

<td>GrowthDate</td><td>IsLogFile</td><td>AlterStatement</td>

</tr>

‘ + @xmlHeaderdata1 + ‘</table>’

Print @xmlHeaderdata1

EXEC msdb..sp_send_dbmail

@profile_name = ‘DBMail’,

@recipients = ‘abc@gmail.com;def@gmail.com’,

@body_format = ‘HTML’,

@subject=’FileSize Has Been Altered For Following DataFiles and LogFiles.’,

@body= @xmlHeaderdata1

END

END TRY

BEGIN CATCH

EXEC uspPvt_RethrowError;

RETURN – 1;

END CATCH

GO

4. AlterDataAndLogFileGrowth will be the job that will take care of this activity. Initially I have disable the step 2 running the alter store procedure DataAndLogFileResize to monitor and check the logic for some time. Later you can enable the same.

USE [msdb]

GO

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N’Utility AlterDataAndLogFileGrowth’)

EXEC msdb.dbo.sp_delete_job @job_name=N’Utility AlterDataAndLogFileGrowth’, @delete_unused_schedule=1

GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’Utility AlterDataAndLogFileGrowth’,

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N’No description available.’,

@category_name=N'[Uncategorized (Local)]’,

@owner_login_name=N’sqldba’, @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’DataAndLogFileGrowthLog’,

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N’TSQL’,

@command=N’EXEC [utility].[dbo].[uspPub_DataAndLogFileGrowthLogging]’,

@database_name=N’Utility’,

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’DataAndLogFileResize’,

@step_id=2,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N’TSQL’,

@command=N’EXEC [Utility].[dbo].[uspPub_DataAndLogFileResize]’,

@database_name=N’Utility’,

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Email Alert after resizing the data and Log files:

DatabaseName FileName PhysicalName FreeSpaceMB GrowthMB GrowthCount GrowthDate IsLogFile AlterStatement
NH NH_Data1 D:\MSSQL10\MSSQL\DATA\NH_Data1.mdf 456000 100 15 2001-12-12 0 Alter   Database NH MODIFY FILE (NAME=’ NH_Data1’,SIZE=’1500’ MB);
NH NH_Index1 D:\MSSQL10\MSSQL\DATA\NH_Index1.ndf 456000 100 21 2001-12-12 0 Alter   Database NH MODIFY FILE (NAME=’ NH_Index1’,SIZE=’2100’ MB);
NH NH_Index2 D:\MSSQL10\MSSQL\DATA\NH_Index2.ndf 456000 100 11 2001-12-12 0 Alter   Database NH MODIFY FILE (NAME=’ NH_Index2’,SIZE=’1100’ MB);
NH NH_Data2 D:\MSSQL10\MSSQL\DATA\NH_Data2.ndf 456000 100 10 2001-12-12 0 Alter   Database NH MODIFY FILE (NAME=’ NH_Data2’,SIZE=’1000’ MB);
NH NH_Data3 D:\MSSQL10\MSSQL\DATA\NH_Data3.ndf 456000 100 13 2001-12-12 0 Alter   Database NH MODIFY FILE (NAME=’ NH_Data3’,SIZE=’1300’ MB);
NH NH_Dly_1 D:\MSSQL10\MSSQL\DATA\ NH_Dly_1.ndf 456000 100 16 2001-12-12 0 Alter   Database NH MODIFY FILE (NAME=’ NH_Dly_1’,SIZE=’1600’ MB);
NH NH_Mntr_1 D:\MSSQL10\MSSQL\DATA\NH_Mntr_1.ndf 456000 100 19 2001-12-12 0 Alter   Database NH MODIFY FILE (NAME=’ NH_Mntr_1’,SIZE=’1900’ MB);
NH NH_Dly_2 D:\MSSQL10\MSSQL\DATA\NH_Dly_2.ndf 456000 100 13 2001-12-12 0 Alter   Database NH MODIFY FILE (NAME=’ NH_Dly_2’,SIZE=’1300’ MB);
NH_RPT NH_Log1 D:\MSSQL10\MSSQL\DATA\NH_Log1.ldf 456000 100 25 2001-12-12 1 Alter   Database NH MODIFY FILE (NAME=’ NH_Log1’,SIZE=’2500’ MB);
NH_RPT NH_Log2 D:\MSSQL10\MSSQL\DATA\NH_Log2.ldf 456000 100 30 2001-12-12 1 Alter   Database NH MODIFY FILE (NAME=’ NH_Log2’,SIZE=’3000’ MB);

Posted in DATABASE SPACE MANAGEMENT | Leave a Comment »

About Me!

Posted by database-wiki on January 24, 2013

Lead Database Developer

Posted in PERSONAL | Leave a Comment »

Script to find Missing Indexes.

Posted by database-wiki on December 20, 2012

DECLARE @runtime datetime
DECLARE @cpu_time_start bigint, @cpu_time bigint, @elapsed_time_start bigint, @rowcount bigint
DECLARE @queryduration int, @qrydurationwarnthreshold int
DECLARE @querystarttime datetime
SET @runtime = GETDATE()
SET @qrydurationwarnthreshold = 5000
SELECT CONVERT (varchar, @runtime, 126) AS runtime,
mig.index_group_handle, mid.index_handle,
CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
‘CREATE INDEX missing_index_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ + CONVERT (varchar, mid.index_handle)
+ ‘ ON ‘ + mid.statement
+ ‘ (‘ + ISNULL (mid.equality_columns,”)
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END + ISNULL (mid.inequality_columns, ”)
+ ‘)’
+ ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, ”) AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
PRINT ”
GO

Posted in GENERAL PERFORMANCE | Leave a Comment »

A details explanation of Access Violation Dump in SQL Server.

Posted by database-wiki on October 20, 2012

I was trying to understand what’s logged in the error log when a dump is generated. I followed (KEN HENDERSON’s SQL SERVER 2005 PRACTICAL TROUBLESHOOTING. RIP KEN…)

SQL Server Stack Dumps: ( A look at the error log before hitting !analyze –v)

===================

SQL Server has used Structured Exception Handling since the first version of SQL Server on Windows NT back in 1993. In order to get the right diagnostic information to debug an exception that is handled, the SQL Server development team implemented the concept of stack dump. This concept is actually nothing new. If you run a program on a Windows XP or 2003 Server and the program does not have Structured Exception Handling, it is considered an unhandled exception. But this doesn’t mean you can’t get information about the exception. “Wrapped” around your program’s execution is a special Exception handler that “catches” the exception and invokes the default debugger. This default debugger for a standard windows instruction is a program called Dr. Watson (drwtsn32.exe). This program is designed to attach to your process and capture information about the exception, including stack frames for all threads and information about the exception. So, in essence, you could call this a stack Dump. So the stack dump contains information about the exception, including the stack frame of the thread that encountered the exception.

The engine produced the stack dump in the ERRORLOG (called the short stack dump) and a more comprehensive dump of the information into a .TXT file in the LOG directory of the sql server

Installation.

Starting in sql server 2000 SP3, SQL Server Dev team realized that debugging efforts would be improved if they also produced a mini-dump file in the standard Windows Debugging dump file format.

To accomplish this, they created a program called SQLDUMPER.EXE. This design was for the sql server engine to spawn the SQLDUMPER.EXE process and pass the appropriate information for SQLDUMPER.EXE to obtain a mini-dump file for the SQLSERVR.EXE process. SQLDUMPER.EXE was designed generic, though, so that it could be used for any program in the sql server box.(Analysis and reporting Services use this, for example).

In Fact, SQLDUMPER.EXE can be used with any Windows program. It uses the MiniDumpWriteDump API call from the Windows SDK.

You can actually run SQLDUMPER.EXE yourself, specifying the process ID of any ProcessID of any running windows program to obtain a dump file of its current state. (See KB 917825 for version of this program).

The location of the mini dump is in sql error log folder and all execution of SQLDUMPER.EXE are logged in SQLDUMPER_ERRORLOG.log.

During a stack dump four files are created:

  1. Mini-dump SQLDumpnnnn.mdmp
  2. A manifest file that is used by DW20.EXE and sent to Microsoft servers to classify the dump called SQLDumpnnnn.mft.
  3. A text file called SQLDumpnnnn.txt (old full stack dump).
  4. ERRORLOG file at the time of the exception called SQLDumpnnnn.log

What you see in the error log is:

  • The most common situation where SQL Server Engine produces a stack dump is a handled Windows exception. Sql server uses structure exception handling in much of its code

to catch Windows exceptions such as Access Violation (AV).

  • Below is an example of access violation(AV).
  • I took the latest dump from the error log folder and open the error log and search for the mdmp name without extension. (SQLDump6229 in my case and it has hit SQLDump6229.txt look below)

————————————————————————————————————————————————————————————————————————————————–

2008-09-17 05:09:46.84 spid66      Using ‘dbghelp.dll’ version ‘4.0.5’

2008-09-17 05:09:46.92 spid66      ***Stack Dump being sent to D:Program FilesMSSQL.1MSSQLLOGSQLDump6229.txt

2008-09-17 05:09:46.92 spid66      SqlDumpExceptionHandler: Process 66 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

2008-09-17 05:09:46.92 spid66      * *******************************************************************************

2008-09-17 05:09:46.92 spid66      *

2008-09-17 05:09:46.92 spid66      * BEGIN STACK DUMP:

2008-09-17 05:09:46.92 spid66      *   09/17/08 05:09:46 spid 66

2008-09-17 05:09:46.92 spid66      *

2008-09-17 05:09:46.92 spid66      *

2008-09-17 05:09:46.92 spid66      *   Exception Address = 01105772 Module(sqlservr+00105772)

2008-09-17 05:09:46.92 spid66      *   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION

2008-09-17 05:09:46.92 spid66      *   Access Violation occurred reading address 00000000

2008-09-17 05:09:46.92 spid66      * Input Buffer 104 bytes –

2008-09-17 05:09:46.92 spid66      *    d b o . p r o c  12 00 64 00 62 00 6f 00 2e 00 70 00 72 00 6f 00 63 00

2008-09-17 05:09:46.92 spid66      *  _ M S S _ C r a w  5f 00 4d 00 53 00 53 00 5f 00 43 00 72 00 61 00 77 00

2008-09-17 05:09:46.92 spid66      *  l     &        &   6c 00 00 00 00 00 26 04 04 03 00 00 00 00 00 26 04 04

2008-09-17 05:09:46.92 spid66      *        &        &   07 00 00 00 00 00 26 04 04 01 00 00 00 00 00 26 04 04

2008-09-17 05:09:46.92 spid66      *  *     &        &   2a 00 00 00 00 00 26 04 04 00 00 00 00 00 00 26 04 04

2008-09-17 05:09:46.92 spid66      *        &    &       00 00 00 00 00 01 26 04 00 00 01 26 04 00

2008-09-17 05:09:46.92 spid66      *

2008-09-17 05:09:46.92 spid66      *

2008-09-17 05:09:46.92 spid66      *  MODULE                          BASE      END       SIZE

2008-09-17 05:09:46.92 spid66      * sqlservr                       01000000  02BA7FFF  01ba8000

2008-09-17 05:09:46.92 spid66      * ntdll                          7C800000  7C8BFFFF  000c0000

2008-09-17 05:09:46.92 spid66      * kernel32                       77E40000  77F41FFF  00102000

2008-09-17 05:09:46.92 spid66      * MSVCR80                        78130000  781CAFFF  0009b000

2008-09-17 05:09:46.92 spid66      * msvcrt                         77BA0000  77BF9FFF  0005a000

2008-09-17 05:09:46.92 spid66      * MSVCP80                        7C420000  7C4A6FFF  00087000

2008-09-17 05:09:46.92 spid66      * ADVAPI32                       77F50000  77FEAFFF  0009b000

2008-09-17 05:09:46.92 spid66      * RPCRT4                         77C50000  77CEEFFF  0009f000

2008-09-17 05:09:46.92 spid66      * Secur32                        76F50000  76F62FFF  00013000

2008-09-17 05:09:46.92 spid66      * USER32                         77380000  77410FFF  00091000

2008-09-17 05:09:46.92 spid66      * GDI32                          77C00000  77C47FFF  00048000

2008-09-17 05:09:46.92 spid66      * CRYPT32                        761B0000  76242FFF  00093000

2008-09-17 05:09:46.92 spid66      * MSASN1                         76190000  761A1FFF  00012000

2008-09-17 05:09:46.92 spid66      * MSWSOCK                        71B20000  71B60FFF  00041000

2008-09-17 05:09:46.92 spid66      * WS2_32                         71C00000  71C16FFF  00017000

2008-09-17 05:09:46.92 spid66      * WS2HELP                        71BF0000  71BF7FFF  00008000

2008-09-17 05:09:46.92 spid66      * USERENV                        76920000  769E1FFF  000c2000

2008-09-17 05:09:46.92 spid66      * opends60                       333E0000  333E6FFF  00007000

2008-09-17 05:09:46.92 spid66      * NETAPI32                       71C40000  71C96FFF  00057000

2008-09-17 05:09:46.92 spid66      * SHELL32                        7C8D0000  7D0CDFFF  007fe000

2008-09-17 05:09:46.92 spid66      * SHLWAPI                        77DA0000  77DF1FFF  00052000

2008-09-17 05:09:46.92 spid66      * comctl32                       77420000  77522FFF  00103000

2008-09-17 05:09:46.92 spid66      * psapi                          76B70000  76B7AFFF  0000b000

2008-09-17 05:09:46.92 spid66      * instapi                        48060000  48069FFF  0000a000

2008-09-17 05:09:46.92 spid66      * sqlevn70                       4F610000  4F7A0FFF  00191000

2008-09-17 05:09:46.92 spid66      * SQLOS                          344D0000  344D4FFF  00005000

2008-09-17 05:09:46.92 spid66      * rsaenh                         68000000  68034FFF  00035000

2008-09-17 05:09:46.92 spid66      * AUTHZ                          76C40000  76C53FFF  00014000

2008-09-17 05:09:46.92 spid66      * MSCOREE                        34480000  344C4FFF  00045000

2008-09-17 05:09:46.92 spid66      * ole32                          77670000  777A8FFF  00139000

2008-09-17 05:09:46.92 spid66      * msv1_0                         76C90000  76CB6FFF  00027000

2008-09-17 05:09:46.92 spid66      * iphlpapi                       76CF0000  76D09FFF  0001a000

2008-09-17 05:09:46.92 spid66      * kerberos                       622C0000  62317FFF  00058000

2008-09-17 05:09:46.92 spid66      * cryptdll                       766E0000  766EBFFF  0000c000

2008-09-17 05:09:46.92 spid66      * schannel                       76750000  76776FFF  00027000

2008-09-17 05:09:46.92 spid66      * COMRES                         77010000  770D5FFF  000c6000

2008-09-17 05:09:46.92 spid66      * XOLEHLP                        62380000  62385FFF  00006000

2008-09-17 05:09:46.92 spid66      * MSDTCPRX                       62390000  62408FFF  00079000

2008-09-17 05:09:46.92 spid66      * OLEAUT32                       77D00000  77D8AFFF  0008b000

2008-09-17 05:09:46.92 spid66      * msvcp60                        62410000  62474FFF  00065000

2008-09-17 05:09:46.92 spid66      * MTXCLU                         62480000  62498FFF  00019000

2008-09-17 05:09:46.92 spid66      * VERSION                        77B90000  77B97FFF  00008000

2008-09-17 05:09:46.92 spid66      * WSOCK32                        71BB0000  71BB8FFF  00009000

2008-09-17 05:09:46.92 spid66      * CLUSAPI                        624A0000  624B1FFF  00012000

2008-09-17 05:09:46.92 spid66      * RESUTILS                       624C0000  624D2FFF  00013000

2008-09-17 05:09:46.92 spid66      * DNSAPI                         76ED0000  76EF9FFF  0002a000

2008-09-17 05:09:46.92 spid66      * winrnr                         76F70000  76F76FFF  00007000

2008-09-17 05:09:46.92 spid66      * WLDAP32                        76F10000  76F3DFFF  0002e000

2008-09-17 05:09:46.92 spid66      * rasadhlp                       76F80000  76F84FFF  00005000

2008-09-17 05:09:46.92 spid66      * security                       62910000  62913FFF  00004000

2008-09-17 05:09:46.92 spid66      * msfte                          63030000  63287FFF  00258000

2008-09-17 05:09:46.92 spid66      * dbghelp                        632A0000  633B7FFF  00118000

2008-09-17 05:09:46.92 spid66      * WINTRUST                       76BB0000  76BDAFFF  0002b000

2008-09-17 05:09:46.92 spid66      * imagehlp                       76C10000  76C37FFF  00028000

2008-09-17 05:09:46.92 spid66      * dssenh                         68100000  68126FFF  00027000

2008-09-17 05:09:46.92 spid66      * hnetcfg                        63600000  63659FFF  0005a000

2008-09-17 05:09:46.92 spid66      * wshtcpip                       71AE0000  71AE7FFF  00008000

2008-09-17 05:09:46.92 spid66      * NTMARTA                        77E00000  77E20FFF  00021000

2008-09-17 05:09:46.92 spid66      * SAMLIB                         7E020000  7E02EFFF  0000f000

2008-09-17 05:09:46.92 spid66      * ntdsapi                        766F0000  76703FFF  00014000

2008-09-17 05:09:46.92 spid66      * xpsp2res                       63720000  639E4FFF  002c5000

2008-09-17 05:09:46.92 spid66      * CLBCatQ                        777B0000  77832FFF  00083000

2008-09-17 05:09:46.92 spid66      * sqlncli                        639F0000  63C0DFFF  0021e000

2008-09-17 05:09:46.92 spid66      * COMCTL32                       77530000  775C6FFF  00097000

2008-09-17 05:09:46.92 spid66      * comdlg32                       762B0000  762F8FFF  00049000

2008-09-17 05:09:46.92 spid66      * SQLNCLIR                       007C0000  007F2FFF  00033000

2008-09-17 05:09:46.92 spid66      * msftepxy                       63D90000  63DA4FFF  00015000

2008-09-17 05:09:46.92 spid66      * xpsqlbot                       65000000  65005FFF  00006000

2008-09-17 05:09:46.92 spid66      * xpstar90                       65020000  65064FFF  00045000

2008-09-17 05:09:46.92 spid66      * SQLSCM90                       65080000  65088FFF  00009000

2008-09-17 05:09:46.92 spid66      * ODBC32                         650A0000  650DCFFF  0003d000

2008-09-17 05:09:46.92 spid66      * BatchParser90                  650E0000  650FDFFF  0001e000

2008-09-17 05:09:46.92 spid66      * SQLSVC90                       65110000  65129FFF  0001a000

2008-09-17 05:09:46.92 spid66      * SqlResourceLoader              65140000  65145FFF  00006000

2008-09-17 05:09:46.92 spid66      * ATL80                          7C630000  7C64AFFF  0001b000

2008-09-17 05:09:46.92 spid66      * odbcint                        65320000  65336FFF  00017000

2008-09-17 05:09:46.92 spid66      * SQLSVC90                       65340000  65342FFF  00003000

2008-09-17 05:09:46.92 spid66      * xpstar90                       65350000  65375FFF  00026000

2008-09-17 05:09:46.92 spid66      * xplog70                        65380000  6538BFFF  0000c000

2008-09-17 05:09:46.92 spid66      * xplog70                        653A0000  653A2FFF  00003000

2008-09-17 05:09:46.92 spid66      * dbghelp                        65B30000  65C47FFF  00118000

2008-09-17 05:09:46.92 spid66      *

2008-09-17 05:09:46.92 spid66      *        Edi: 3F883350:  0110300C  3F882DF0  01103308  00000000  00000000  00000000

2008-09-17 05:09:46.92 spid66      *        Esi: 3F883350:  0110300C  3F882DF0  01103308  00000000  00000000  00000000

2008-09-17 05:09:46.92 spid66      *        Eax: 62A9EE20:  62A9EE54  02450AD1  FFFFFFFF  62A9EE60  013804D2  3F883350

2008-09-17 05:09:46.92 spid66      *        Ebx: 00000000:

2008-09-17 05:09:46.92 spid66      *        Ecx: 00000000:

2008-09-17 05:09:46.92 spid66      *        Edx: 00000040:

2008-09-17 05:09:46.92 spid66      *        Eip: 01105772:  108B038B  CB8B016A  4D8BD2FF  F88B560C  FFDE39E8  89C033FF

2008-09-17 05:09:46.92 spid66      *        Ebp: 62A9EE2C:  62A9EE60  013804D2  3F883350  3FCE2028  00000000  62A9EF6D

2008-09-17 05:09:46.92 spid66      *      SegCs: 0000001B:

2008-09-17 05:09:46.92 spid66      *     EFlags: 00010206:  00610044  00610074  004D005C  00410063  00650066  005C0065

2008-09-17 05:09:46.92 spid66      *        Esp: 62A9EE10:  62A9EF21  3F883350  3FCE2028  3F8832F8  62A9EE54  02450AD1

2008-09-17 05:09:46.92 spid66      *      SegSs: 00000023:

2008-09-17 05:09:46.92 spid66      * *******************************************************************************

2008-09-17 05:09:46.92 spid66      * ——————————————————————————-

2008-09-17 05:09:46.92 spid66      * Short Stack Dump

2008-09-17 05:09:46.96 spid66      01105772 Module(sqlservr+00105772)

2008-09-17 05:09:46.96 spid66      013804D2 Module(sqlservr+003804D2)

2008-09-17 05:09:46.96 spid66      01439AD1 Module(sqlservr+00439AD1)

2008-09-17 05:09:46.96 spid66      0143995C Module(sqlservr+0043995C)

2008-09-17 05:09:46.96 spid66      01380F7A Module(sqlservr+00380F7A)

2008-09-17 05:09:46.96 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:46.96 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:46.96 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:46.96 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:46.96 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:46.96 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:46.96 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:46.96 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:46.96 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:46.96 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:46.96 spid66      013810A9 Module(sqlservr+003810A9)

2008-09-17 05:09:46.96 spid66      0125362A Module(sqlservr+0025362A)

2008-09-17 05:09:46.96 spid66      01253CCA Module(sqlservr+00253CCA)

2008-09-17 05:09:46.96 spid66      013CDC85 Module(sqlservr+003CDC85)

2008-09-17 05:09:46.96 spid66      013CDD56 Module(sqlservr+003CDD56)

2008-09-17 05:09:46.96 spid66      0125B712 Module(sqlservr+0025B712)

2008-09-17 05:09:46.96 spid66      0125B0B5 Module(sqlservr+0025B0B5)

2008-09-17 05:09:46.96 spid66      01320C83 Module(sqlservr+00320C83)

2008-09-17 05:09:46.96 spid66      0125B99F Module(sqlservr+0025B99F)

2008-09-17 05:09:46.96 spid66      0102C51D Module(sqlservr+0002C51D)

2008-09-17 05:09:46.96 spid66      010438E5 Module(sqlservr+000438E5)

2008-09-17 05:09:46.96 spid66      01041C35 Module(sqlservr+00041C35)

2008-09-17 05:09:46.96 spid66      0100889F Module(sqlservr+0000889F)

2008-09-17 05:09:46.96 spid66      010089C5 Module(sqlservr+000089C5)

2008-09-17 05:09:46.96 spid66      010086E7 Module(sqlservr+000086E7)

2008-09-17 05:09:46.96 spid66      010D764A Module(sqlservr+000D764A)

2008-09-17 05:09:46.96 spid66      010D7B71 Module(sqlservr+000D7B71)

2008-09-17 05:09:46.96 spid66      010D746E Module(sqlservr+000D746E)

2008-09-17 05:09:47.03 spid66      010D83F0 Module(sqlservr+000D83F0)

2008-09-17 05:09:47.03 spid66      781329AA Module(MSVCR80+000029AA)

2008-09-17 05:09:47.07 spid66      78132A36 Module(MSVCR80+00002A36)

2008-09-17 05:09:47.09 spid66      Stack Signature for the dump is 0x39F8A80D

2008-09-17 05:09:47.78 spid66      Potential image corruption/hotpatch detected. This may be a sign of a hardware problem or caused by presence of CLR/jitted images on the stack. Check SQLDUMPER_ERRORLOG.log for details.

2008-09-17 05:09:47.78 Server      Error: 17310, Severity: 20, State: 1.

2008-09-17 05:09:47.78 Server      A user request from the session with SPID 66 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.

2008-09-17 05:09:48.09 spid66      Using ‘dbghelp.dll’ version ‘4.0.5’

2008-09-17 05:09:48.14 spid66      ***Stack Dump being sent to D:Program FilesMSSQL.1MSSQLLOGSQLDump6230.txt

2008-09-17 05:09:48.14 spid66      SqlDumpExceptionHandler: Process 66 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

2008-09-17 05:09:48.14 spid66      * *******************************************************************************

2008-09-17 05:09:48.14 spid66      *

2008-09-17 05:09:48.14 spid66      * BEGIN STACK DUMP:

2008-09-17 05:09:48.14 spid66      *   09/17/08 05:09:48 spid 66

2008-09-17 05:09:48.14 spid66      *

2008-09-17 05:09:48.14 spid66      *

2008-09-17 05:09:48.14 spid66      *   Exception Address = 01105772 Module(sqlservr+00105772)

2008-09-17 05:09:48.14 spid66      *   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION

2008-09-17 05:09:48.14 spid66      *   Access Violation occurred reading address 00000000

2008-09-17 05:09:48.14 spid66      * Input Buffer 104 bytes –

2008-09-17 05:09:48.14 spid66      *    d b o . p r o c  12 00 64 00 62 00 6f 00 2e 00 70 00 72 00 6f 00 63 00

2008-09-17 05:09:48.14 spid66      *  _ M S S _ C r a w  5f 00 4d 00 53 00 53 00 5f 00 43 00 72 00 61 00 77 00

2008-09-17 05:09:48.14 spid66      *  l     &        &   6c 00 00 00 00 00 26 04 04 03 00 00 00 00 00 26 04 04

2008-09-17 05:09:48.14 spid66      *        &        &   07 00 00 00 00 00 26 04 04 01 00 00 00 00 00 26 04 04

2008-09-17 05:09:48.14 spid66      *  *     &        &   2a 00 00 00 00 00 26 04 04 00 00 00 00 00 00 26 04 04

2008-09-17 05:09:48.14 spid66      *        &    &       00 00 00 00 00 01 26 04 00 00 01 26 04 00

2008-09-17 05:09:48.14 spid66      *

2008-09-17 05:09:48.14 spid66      *

2008-09-17 05:09:48.14 spid66      *  MODULE                          BASE      END       SIZE

2008-09-17 05:09:48.14 spid66      * sqlservr                       01000000  02BA7FFF  01ba8000

2008-09-17 05:09:48.14 spid66      * ntdll                          7C800000  7C8BFFFF  000c0000

2008-09-17 05:09:48.14 spid66      * kernel32                       77E40000  77F41FFF  00102000

2008-09-17 05:09:48.14 spid66      * MSVCR80                        78130000  781CAFFF  0009b000

2008-09-17 05:09:48.14 spid66      * msvcrt                         77BA0000  77BF9FFF  0005a000

2008-09-17 05:09:48.14 spid66      * MSVCP80                        7C420000  7C4A6FFF  00087000

2008-09-17 05:09:48.14 spid66      * ADVAPI32                       77F50000  77FEAFFF  0009b000

2008-09-17 05:09:48.14 spid66      * RPCRT4                         77C50000  77CEEFFF  0009f000

2008-09-17 05:09:48.14 spid66      * Secur32                        76F50000  76F62FFF  00013000

2008-09-17 05:09:48.14 spid66      * USER32                         77380000  77410FFF  00091000

2008-09-17 05:09:48.14 spid66      * GDI32                          77C00000  77C47FFF  00048000

2008-09-17 05:09:48.14 spid66      * CRYPT32                        761B0000  76242FFF  00093000

2008-09-17 05:09:48.14 spid66      * MSASN1                         76190000  761A1FFF  00012000

2008-09-17 05:09:48.14 spid66      * MSWSOCK                        71B20000  71B60FFF  00041000

2008-09-17 05:09:48.14 spid66      * WS2_32                         71C00000  71C16FFF  00017000

2008-09-17 05:09:48.14 spid66      * WS2HELP                        71BF0000  71BF7FFF  00008000

2008-09-17 05:09:48.14 spid66      * USERENV                        76920000  769E1FFF  000c2000

2008-09-17 05:09:48.14 spid66      * opends60                       333E0000  333E6FFF  00007000

2008-09-17 05:09:48.14 spid66      * NETAPI32                       71C40000  71C96FFF  00057000

2008-09-17 05:09:48.14 spid66      * SHELL32                        7C8D0000  7D0CDFFF  007fe000

2008-09-17 05:09:48.14 spid66      * SHLWAPI                        77DA0000  77DF1FFF  00052000

2008-09-17 05:09:48.14 spid66      * comctl32                       77420000  77522FFF  00103000

2008-09-17 05:09:48.14 spid66      * psapi                          76B70000  76B7AFFF  0000b000

2008-09-17 05:09:48.14 spid66      * instapi                        48060000  48069FFF  0000a000

2008-09-17 05:09:48.14 spid66      * sqlevn70                       4F610000  4F7A0FFF  00191000

2008-09-17 05:09:48.14 spid66      * SQLOS                          344D0000  344D4FFF  00005000

2008-09-17 05:09:48.14 spid66      * rsaenh                         68000000  68034FFF  00035000

2008-09-17 05:09:48.14 spid66      * AUTHZ                          76C40000  76C53FFF  00014000

2008-09-17 05:09:48.14 spid66      * MSCOREE                        34480000  344C4FFF  00045000

2008-09-17 05:09:48.14 spid66      * ole32                          77670000  777A8FFF  00139000

2008-09-17 05:09:48.14 spid66      * msv1_0                         76C90000  76CB6FFF  00027000

2008-09-17 05:09:48.14 spid66      * iphlpapi                       76CF0000  76D09FFF  0001a000

2008-09-17 05:09:48.14 spid66      * kerberos                       622C0000  62317FFF  00058000

2008-09-17 05:09:48.14 spid66      * cryptdll                       766E0000  766EBFFF  0000c000

2008-09-17 05:09:48.14 spid66      * schannel                       76750000  76776FFF  00027000

2008-09-17 05:09:48.14 spid66      * COMRES                         77010000  770D5FFF  000c6000

2008-09-17 05:09:48.14 spid66      * XOLEHLP                        62380000  62385FFF  00006000

2008-09-17 05:09:48.14 spid66      * MSDTCPRX                       62390000  62408FFF  00079000

2008-09-17 05:09:48.14 spid66      * OLEAUT32                       77D00000  77D8AFFF  0008b000

2008-09-17 05:09:48.14 spid66      * msvcp60                        62410000  62474FFF  00065000

2008-09-17 05:09:48.14 spid66      * MTXCLU                         62480000  62498FFF  00019000

2008-09-17 05:09:48.14 spid66      * VERSION                        77B90000  77B97FFF  00008000

2008-09-17 05:09:48.14 spid66      * WSOCK32                        71BB0000  71BB8FFF  00009000

2008-09-17 05:09:48.14 spid66      * CLUSAPI                        624A0000  624B1FFF  00012000

2008-09-17 05:09:48.14 spid66      * RESUTILS                       624C0000  624D2FFF  00013000

2008-09-17 05:09:48.14 spid66      * DNSAPI                         76ED0000  76EF9FFF  0002a000

2008-09-17 05:09:48.14 spid66      * winrnr                         76F70000  76F76FFF  00007000

2008-09-17 05:09:48.14 spid66      * WLDAP32                        76F10000  76F3DFFF  0002e000

2008-09-17 05:09:48.14 spid66      * rasadhlp                       76F80000  76F84FFF  00005000

2008-09-17 05:09:48.14 spid66      * security                       62910000  62913FFF  00004000

2008-09-17 05:09:48.14 spid66      * msfte                          63030000  63287FFF  00258000

2008-09-17 05:09:48.14 spid66      * dbghelp                        632A0000  633B7FFF  00118000

2008-09-17 05:09:48.14 spid66      * WINTRUST                       76BB0000  76BDAFFF  0002b000

2008-09-17 05:09:48.14 spid66      * imagehlp                       76C10000  76C37FFF  00028000

2008-09-17 05:09:48.14 spid66      * dssenh                         68100000  68126FFF  00027000

2008-09-17 05:09:48.14 spid66      * hnetcfg                        63600000  63659FFF  0005a000

2008-09-17 05:09:48.14 spid66      * wshtcpip                       71AE0000  71AE7FFF  00008000

2008-09-17 05:09:48.14 spid66      * NTMARTA                        77E00000  77E20FFF  00021000

2008-09-17 05:09:48.14 spid66      * SAMLIB                         7E020000  7E02EFFF  0000f000

2008-09-17 05:09:48.14 spid66      * ntdsapi                        766F0000  76703FFF  00014000

2008-09-17 05:09:48.14 spid66      * xpsp2res                       63720000  639E4FFF  002c5000

2008-09-17 05:09:48.14 spid66      * CLBCatQ                        777B0000  77832FFF  00083000

2008-09-17 05:09:48.14 spid66      * sqlncli                        639F0000  63C0DFFF  0021e000

2008-09-17 05:09:48.14 spid66      * COMCTL32                       77530000  775C6FFF  00097000

2008-09-17 05:09:48.14 spid66      * comdlg32                       762B0000  762F8FFF  00049000

2008-09-17 05:09:48.14 spid66      * SQLNCLIR                       007C0000  007F2FFF  00033000

2008-09-17 05:09:48.14 spid66      * msftepxy                       63D90000  63DA4FFF  00015000

2008-09-17 05:09:48.14 spid66      * xpsqlbot                       65000000  65005FFF  00006000

2008-09-17 05:09:48.14 spid66      * xpstar90                       65020000  65064FFF  00045000

2008-09-17 05:09:48.14 spid66      * SQLSCM90                       65080000  65088FFF  00009000

2008-09-17 05:09:48.14 spid66      * ODBC32                         650A0000  650DCFFF  0003d000

2008-09-17 05:09:48.14 spid66      * BatchParser90                  650E0000  650FDFFF  0001e000

2008-09-17 05:09:48.14 spid66      * SQLSVC90                       65110000  65129FFF  0001a000

2008-09-17 05:09:48.14 spid66      * SqlResourceLoader              65140000  65145FFF  00006000

2008-09-17 05:09:48.14 spid66      * ATL80                          7C630000  7C64AFFF  0001b000

2008-09-17 05:09:48.14 spid66      * odbcint                        65320000  65336FFF  00017000

2008-09-17 05:09:48.14 spid66      * SQLSVC90                       65340000  65342FFF  00003000

2008-09-17 05:09:48.14 spid66      * xpstar90                       65350000  65375FFF  00026000

2008-09-17 05:09:48.14 spid66      * xplog70                        65380000  6538BFFF  0000c000

2008-09-17 05:09:48.14 spid66      * xplog70                        653A0000  653A2FFF  00003000

2008-09-17 05:09:48.14 spid66      * dbghelp                        65B30000  65C47FFF  00118000

2008-09-17 05:09:48.14 spid66      *

2008-09-17 05:09:48.14 spid66      *        Edi: 3FE75350:  0110300C  3FE74DF0  01103308  00000000  00000000  00000000

2008-09-17 05:09:48.14 spid66      *        Esi: 3FE75350:  0110300C  3FE74DF0  01103308  00000000  00000000  00000000

2008-09-17 05:09:48.14 spid66      *        Eax: 6302EE20:  6302EE54  02450AD1  FFFFFFFF  6302EE60  013804D2  3FE75350

2008-09-17 05:09:48.14 spid66      *        Ebx: 00000000:

2008-09-17 05:09:48.14 spid66      *        Ecx: 00000000:

2008-09-17 05:09:48.14 spid66      *        Edx: 00000040:

2008-09-17 05:09:48.14 spid66      *        Eip: 01105772:  108B038B  CB8B016A  4D8BD2FF  F88B560C  FFDE39E8  89C033FF

2008-09-17 05:09:48.14 spid66      *        Ebp: 6302EE2C:  6302EE60  013804D2  3FE75350  3F422028  00000000  6302EF6D

2008-09-17 05:09:48.14 spid66      *      SegCs: 0000001B:

2008-09-17 05:09:48.14 spid66      *     EFlags: 00010206:  00610044  00610074  004D005C  00410063  00650066  005C0065

2008-09-17 05:09:48.14 spid66      *        Esp: 6302EE10:  6302EF21  3FE75350  3F422028  3FE752F8  6302EE54  02450AD1

2008-09-17 05:09:48.14 spid66      *      SegSs: 00000023:

2008-09-17 05:09:48.14 spid66      * *******************************************************************************

2008-09-17 05:09:48.14 spid66      * ——————————————————————————-

2008-09-17 05:09:48.14 spid66      * Short Stack Dump

2008-09-17 05:09:48.20 spid66      01105772 Module(sqlservr+00105772)

2008-09-17 05:09:48.20 spid66      013804D2 Module(sqlservr+003804D2)

2008-09-17 05:09:48.20 spid66      01439AD1 Module(sqlservr+00439AD1)

2008-09-17 05:09:48.20 spid66      0143995C Module(sqlservr+0043995C)

2008-09-17 05:09:48.20 spid66      01380F7A Module(sqlservr+00380F7A)

2008-09-17 05:09:48.20 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:48.20 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:48.20 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:48.20 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:48.20 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:48.20 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:48.20 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:48.20 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:48.20 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:48.20 spid66      01380EAF Module(sqlservr+00380EAF)

2008-09-17 05:09:48.20 spid66      013810A9 Module(sqlservr+003810A9)

2008-09-17 05:09:48.20 spid66      0125362A Module(sqlservr+0025362A)

2008-09-17 05:09:48.20 spid66      01253CCA Module(sqlservr+00253CCA)

2008-09-17 05:09:48.20 spid66      013CDC85 Module(sqlservr+003CDC85)

2008-09-17 05:09:48.20 spid66      013CDD56 Module(sqlservr+003CDD56)

2008-09-17 05:09:48.20 spid66      0125B712 Module(sqlservr+0025B712)

2008-09-17 05:09:48.20 spid66      0125B0B5 Module(sqlservr+0025B0B5)

2008-09-17 05:09:48.20 spid66      01320C83 Module(sqlservr+00320C83)

2008-09-17 05:09:48.20 spid66      0125B99F Module(sqlservr+0025B99F)

2008-09-17 05:09:48.20 spid66      0102C51D Module(sqlservr+0002C51D)

2008-09-17 05:09:48.20 spid66      010438E5 Module(sqlservr+000438E5)

2008-09-17 05:09:48.20 spid66      01041C35 Module(sqlservr+00041C35)

2008-09-17 05:09:48.20 spid66      0100889F Module(sqlservr+0000889F)

2008-09-17 05:09:48.20 spid66      010089C5 Module(sqlservr+000089C5)

2008-09-17 05:09:48.20 spid66      010086E7 Module(sqlservr+000086E7)

2008-09-17 05:09:48.20 spid66      010D764A Module(sqlservr+000D764A)

2008-09-17 05:09:48.20 spid66      010D7B71 Module(sqlservr+000D7B71)

2008-09-17 05:09:48.20 spid66      010D746E Module(sqlservr+000D746E)

2008-09-17 05:09:48.25 spid66      010D83F0 Module(sqlservr+000D83F0)

2008-09-17 05:09:48.25 spid66      781329AA Module(MSVCR80+000029AA)

2008-09-17 05:09:48.29 spid66      78132A36 Module(MSVCR80+00002A36)

2008-09-17 05:09:48.31 spid66      Stack Signature for the dump is 0x39F8A80D

2008-09-17 05:09:48.99 spid66      Potential image corruption/hotpatch detected. This may be a sign of a hardware problem or caused by presence of CLR/jitted images on the stack. Check SQLDUMPER_ERRORLOG.log for details.

2008-09-17 05:09:48.99 Server      Error: 17310, Severity: 20, State: 1.

2008-09-17 05:09:48.99 Server      A user request from the session with SPID 66 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.

2008-09-17 05:09:49.31 spid66      Using ‘dbghelp.dll’ version ‘4.0.5’

2008-09-17 05:09:49.35 spid66      ***Stack Dump being sent to D:Program FilesMSSQL.1MSSQLLOGSQLDump6231.txt

2008-09-17 05:09:49.35 spid66      SqlDumpExceptionHandler: Process 66 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

2008-09-17 05:09:49.35 spid66      * *******************************************************************************

————————————————————————————————————————————————————————————————————————————————–

This stack dump is from an actual bug with sql server 2005 RTM that got fixed in SP1. (Apply SP3 considering the supportability)

Lets break the above stack dump.

Stack Header:

2008-09-17 04:24:43.07 spid87      Using ‘dbghelp.dll’ version ‘4.0.5’

2008-09-17 04:24:49.09 spid87      ***Stack Dump being sent to D:Program FilesMSSQL.1MSSQLLOGSQLDump3836.txt

The first line is the version of the dbghelp.dll used by SQL Server to produce information about an exception. It is present in the BINN folder of sql server installation files.

It provides “debugging” functions for an applications ( it is also used by SQLDUMPER.EXE for creating mini dumps).

2008-09-17 04:24:49.09 spid87      SqlDumpExceptionHandler: Process 87 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

2008-09-17 04:24:49.09 spid87      * *******************************************************************************

The line appears only for exceptions and it is a simple method to see from the beginning that the stack dump is the result of an AV (c0000005 is the windows internal error number for Access Violation).

2008-09-17 04:24:49.09 spid87      * BEGIN STACK DUMP:

2008-09-17 04:24:49.09 spid87      *   09/17/08 04:24:49 spid 87

2008-09-17 04:24:49.09 spid87      *

2008-09-17 04:24:49.09 spid87      *

2008-09-17 04:24:49.09 spid87      *   Exception Address = 01105772 Module(sqlservr+00105772)

2008-09-17 04:24:49.09 spid87      *   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION

2008-09-17 04:24:49.09 spid87      *   Access Violation occurred reading address 00000000

2008-09-17 04:24:49.09 spid87      * Input Buffer 104 bytes –

2008-09-17 04:24:49.09 spid87      *    d b o . p r o c  12 00 64 00 62 00 6f 00 2e 00 70 00 72 00 6f 00 63 00

2008-09-17 04:24:49.09 spid87      *  _ M S S _ C r a w  5f 00 4d 00 53 00 53 00 5f 00 43 00 72 00 61 00 77 00

2008-09-17 04:24:49.09 spid87      *  l     &        &   6c 00 00 00 00 00 26 04 04 03 00 00 00 00 00 26 04 04

2008-09-17 04:24:49.09 spid87      *        &        &   07 00 00 00 00 00 26 04 04 01 00 00 00 00 00 26 04 04

2008-09-17 04:24:49.09 spid87      *  *     &        &   2a 00 00 00 00 00 26 04 04 00 00 00 00 00 00 26 04 04

2008-09-17 04:24:49.09 spid87      *        &    &       00 00 00 00 00 01 26 04 00 00 01 26 04 00

The information about the exception that is directly extracted from a structure called the EXCEPTION_RECORD. Anytime a thread catches a windows exception, key information can be found in the EXCEPTION_RECORD.

2008-09-17 04:24:49.09 spid87      *   Exception Address = 01105772 Module(sqlservr+00105772)

This is the instruction address SQLSERVR.EXE that caused the exception (in this case, an AV).

A basic definition of an AV is an attempt to access a memory address that is not accessible to the program’s virtual address space. The typical cause is that the memory address is not actually committed memory, but it could also be that the memory address is part of a region that is marked NO_ACCESS or READY_ONLY. The first 64 KB of the virtual address space of any Windows process is automatically marked NO_ACCESS. This means that any attempt by a thread to access a memory address of 0 – 65536 results in AV. This is specifically done in windows to catch any NULL pointers in programs. Memory regions marked READ_ONLY cause an AV to occur if a thread attempts to write at a memory address in that region.

The exception address is very important for you in making a quick determination about whether this problem may be a SQL Server issue. The name of the module associated with the exception address instruction is listed next to the module Module(sqlservr+00105772). In this case, you can see that the instruction address where the exception occurred is SQLSERVR.EXE and so it a problem of SQL Server.

If the module name is not SQLSERVR.EXE? it could still be a SQL Server bug.

In this particular  issue, I see that the AV occurred when readying the memory address 00000000

2008-09-17 04:24:49.09 spid87      *   Access Violation occurred reading address 00000000

Remember first 64KB is marked NO_ACCESS. My bet is a NULL pointer problem because of the address 0x00 in the dump. Probably the code was attempting to access a member of a class or structure that is a offset 0x00 within the structure, but the pointer that the code is using the NULL.

The next line is very important for the purpose of diagnosing the problem:

2008-09-17 04:24:49.09 spid87      *    d b o . p r o c  12 00 64 00 62 00 6f 00 2e 00 70 00 72 00 6f 00 63 00

2008-09-17 04:24:49.09 spid87      *  _ M S S _ C r a w  5f 00 4d 00 53 00 53 00 5f 00 43 00 72 00 61 00 77 00

2008-09-17 04:24:49.09 spid87      *  l     &        &   6c 00 00 00 00 00 26 04 04 03 00 00 00 00 00 26 04 04

2008-09-17 04:24:49.09 spid87      *        &        &   07 00 00 00 00 00 26 04 04 01 00 00 00 00 00 26 04 04

2008-09-17 04:24:49.09 spid87      *  *     &        &   2a 00 00 00 00 00 26 04 04 00 00 00 00 00 00 26 04 04

2008-09-17 04:24:49.09 spid87      *        &    &       00 00 00 00 00 01 26 04 00 00 01 26 04 00

This is called the input buffer and represents the query executed by the session on behalf of the application that results in the condition for the stack dump. The input buffer is displayed only for

a session that actually ran a query or proc.

For example, if an access violation was encountered by a “System Session” like checkpoint, you would not see an input buffer. In my case you were able to find the proc  and it can be used to reproduce the issue. Further you will proceed by check the file bugs or check the build or work towards a fix.

The type of input buffer is a SQL Remote Procedure Call (RPC). An RPC is used by an application to excute a stored procedure and bind parameters to it without executing a T-SQL string command.

The name of the stored procedure is displayed on the left side of this dump output. If you encounter a situation like this, you must use SQLTrace to find out the parameters of procedure execution.

2008-09-17 04:24:49.09 spid87      *  MODULE                          BASE      END       SIZE

2008-09-17 04:24:49.09 spid87      * sqlservr                       01000000  02BA7FFF  01ba8000

2008-09-17 04:24:49.09 spid87      * ntdll                          7C800000  7C8BFFFF  000c0000

2008-09-17 04:24:49.09 spid87      * kernel32                       77E40000  77F41FFF  00102000

2008-09-17 04:24:49.09 spid87      * MSVCR80                        78130000  781CAFFF  0009b000

2008-09-17 04:24:49.09 spid87      * msvcrt                         77BA0000  77BF9FFF  0005a000

2008-09-17 04:24:49.09 spid87      * MSVCP80                        7C420000  7C4A6FFF  00087000

2008-09-17 04:24:49.09 spid87      * ADVAPI32                       77F50000  77FEAFFF  0009b000

2008-09-17 04:24:49.09 spid87      * RPCRT4                         77C50000  77CEEFFF  0009f000

2008-09-17 04:24:49.09 spid87      * Secur32                        76F50000  76F62FFF  00013000

2008-09-17 04:24:49.09 spid87      * USER32                         77380000  77410FFF  00091000

2008-09-17 04:24:49.09 spid87      * GDI32                          77C00000  77C47FFF  00048000

2008-09-17 04:24:49.09 spid87      * CRYPT32                        761B0000  76242FFF  00093000

2008-09-17 04:24:49.09 spid87      * MSASN1                         76190000  761A1FFF  00012000

2008-09-17 04:24:49.09 spid87      * MSWSOCK                        71B20000  71B60FFF  00041000

2008-09-17 04:24:49.09 spid87      * WS2_32                         71C00000  71C16FFF  00017000

2008-09-17 04:24:49.09 spid87      * WS2HELP                        71BF0000  71BF7FFF  00008000

2008-09-17 04:24:49.09 spid87      * USERENV                        76920000  769E1FFF  000c2000

2008-09-17 04:24:49.09 spid87      * opends60                       333E0000  333E6FFF  00007000

2008-09-17 04:24:49.09 spid87      * NETAPI32                       71C40000  71C96FFF  00057000

2008-09-17 04:24:49.09 spid87      * SHELL32                        7C8D0000  7D0CDFFF  007fe000

2008-09-17 04:24:49.09 spid87      * SHLWAPI                        77DA0000  77DF1FFF  00052000

2008-09-17 04:24:49.09 spid87      * comctl32                       77420000  77522FFF  00103000

2008-09-17 04:24:49.09 spid87      * psapi                          76B70000  76B7AFFF  0000b000

2008-09-17 04:24:49.09 spid87      * instapi                        48060000  48069FFF  0000a000

2008-09-17 04:24:49.09 spid87      * sqlevn70                       4F610000  4F7A0FFF  00191000

2008-09-17 04:24:49.09 spid87      * SQLOS                          344D0000  344D4FFF  00005000

2008-09-17 04:24:49.09 spid87      * rsaenh                         68000000  68034FFF  00035000

2008-09-17 04:24:49.09 spid87      * AUTHZ                          76C40000  76C53FFF  00014000

2008-09-17 04:24:49.09 spid87      * MSCOREE                        34480000  344C4FFF  00045000

2008-09-17 04:24:49.09 spid87      * ole32                          77670000  777A8FFF  00139000

2008-09-17 04:24:49.09 spid87      * msv1_0                         76C90000  76CB6FFF  00027000

2008-09-17 04:24:49.09 spid87      * iphlpapi                       76CF0000  76D09FFF  0001a000

2008-09-17 04:24:49.09 spid87      * kerberos                       622C0000  62317FFF  00058000

2008-09-17 04:24:49.09 spid87      * cryptdll                       766E0000  766EBFFF  0000c000

2008-09-17 04:24:49.09 spid87      * schannel                       76750000  76776FFF  00027000

2008-09-17 04:24:49.09 spid87      * COMRES                         77010000  770D5FFF  000c6000

2008-09-17 04:24:49.09 spid87      * XOLEHLP                        62380000  62385FFF  00006000

2008-09-17 04:24:49.09 spid87      * MSDTCPRX                       62390000  62408FFF  00079000

2008-09-17 04:24:49.09 spid87      * OLEAUT32                       77D00000  77D8AFFF  0008b000

2008-09-17 04:24:49.09 spid87      * msvcp60                        62410000  62474FFF  00065000

2008-09-17 04:24:49.09 spid87      * MTXCLU                         62480000  62498FFF  00019000

2008-09-17 04:24:49.09 spid87      * VERSION                        77B90000  77B97FFF  00008000

2008-09-17 04:24:49.09 spid87      * WSOCK32                        71BB0000  71BB8FFF  00009000

2008-09-17 04:24:49.09 spid87      * CLUSAPI                        624A0000  624B1FFF  00012000

2008-09-17 04:24:49.09 spid87      * RESUTILS                       624C0000  624D2FFF  00013000

2008-09-17 04:24:49.09 spid87      * DNSAPI                         76ED0000  76EF9FFF  0002a000

2008-09-17 04:24:49.09 spid87      * winrnr                         76F70000  76F76FFF  00007000

2008-09-17 04:24:49.09 spid87      * WLDAP32                        76F10000  76F3DFFF  0002e000

2008-09-17 04:24:49.09 spid87      * rasadhlp                       76F80000  76F84FFF  00005000

2008-09-17 04:24:49.09 spid87      * security                       62910000  62913FFF  00004000

2008-09-17 04:24:49.09 spid87      * msfte                          63030000  63287FFF  00258000

2008-09-17 04:24:49.09 spid87      * dbghelp                        632A0000  633B7FFF  00118000

2008-09-17 04:24:49.09 spid87      * WINTRUST                       76BB0000  76BDAFFF  0002b000

2008-09-17 04:24:49.09 spid87      * imagehlp                       76C10000  76C37FFF  00028000

2008-09-17 04:24:49.09 spid87      * dssenh                         68100000  68126FFF  00027000

2008-09-17 04:24:49.09 spid87      * hnetcfg                        63600000  63659FFF  0005a000

2008-09-17 04:24:49.09 spid87      * wshtcpip                       71AE0000  71AE7FFF  00008000

2008-09-17 04:24:49.09 spid87      * NTMARTA                        77E00000  77E20FFF  00021000

2008-09-17 04:24:49.09 spid87      * SAMLIB                         7E020000  7E02EFFF  0000f000

2008-09-17 04:24:49.09 spid87      * ntdsapi                        766F0000  76703FFF  00014000

2008-09-17 04:24:49.09 spid87      * xpsp2res                       63720000  639E4FFF  002c5000

2008-09-17 04:24:49.09 spid87      * CLBCatQ                        777B0000  77832FFF  00083000

2008-09-17 04:24:49.09 spid87      * sqlncli                        639F0000  63C0DFFF  0021e000

2008-09-17 04:24:49.09 spid87      * COMCTL32                       77530000  775C6FFF  00097000

2008-09-17 04:24:49.09 spid87      * comdlg32                       762B0000  762F8FFF  00049000

2008-09-17 04:24:49.09 spid87      * SQLNCLIR                       007C0000  007F2FFF  00033000

2008-09-17 04:24:49.09 spid87      * msftepxy                       63D90000  63DA4FFF  00015000

2008-09-17 04:24:49.09 spid87      * xpsqlbot                       65000000  65005FFF  00006000

2008-09-17 04:24:49.09 spid87      * xpstar90                       65020000  65064FFF  00045000

2008-09-17 04:24:49.09 spid87      * SQLSCM90                       65080000  65088FFF  00009000

2008-09-17 04:24:49.09 spid87      * ODBC32                         650A0000  650DCFFF  0003d000

2008-09-17 04:24:49.09 spid87      * BatchParser90                  650E0000  650FDFFF  0001e000

2008-09-17 04:24:49.09 spid87      * SQLSVC90                       65110000  65129FFF  0001a000

2008-09-17 04:24:49.09 spid87      * SqlResourceLoader              65140000  65145FFF  00006000

2008-09-17 04:24:49.09 spid87      * ATL80                          7C630000  7C64AFFF  0001b000

2008-09-17 04:24:49.09 spid87      * odbcint                        65320000  65336FFF  00017000

2008-09-17 04:24:49.09 spid87      * SQLSVC90                       65340000  65342FFF  00003000

2008-09-17 04:24:49.09 spid87      * xpstar90                       65350000  65375FFF  00026000

2008-09-17 04:24:49.09 spid87      * xplog70                        65380000  6538BFFF  0000c000

2008-09-17 04:24:49.09 spid87      * xplog70                        653A0000  653A2FFF  00003000

2008-09-17 04:24:49.09 spid87      * dbghelp                        65540000  65657FFF  00118000

This section list the DLL’s loaded in the SQL Server process and their memory address range. ( These can be used for further investigation of the stack dump)

This section is a dump of registers:

2008-09-17 04:24:49.09 spid87      *        Edi: 10247350:  00000000  00000000  00000000  00000000  00000000  00000000 

2008-09-17 04:24:49.09 spid87      *        Esi: 10247350:  00000000  00000000  00000000  00000000  00000000  00000000 

2008-09-17 04:24:49.09 spid87      *        Eax: 64DFEE20:  64DFEE54  02450AD1  FFFFFFFF  64DFEE60  013804D2  10247350 

2008-09-17 04:24:49.09 spid87      *        Ebx: 00000000: 

2008-09-17 04:24:49.09 spid87      *        Ecx: 00000000: 

2008-09-17 04:24:49.09 spid87      *        Edx: 00000040: 

2008-09-17 04:24:49.09 spid87      *        Eip: 01105772:  108B038B  CB8B016A  4D8BD2FF  F88B560C  FFDE39E8  89C033FF 

2008-09-17 04:24:49.09 spid87      *        Ebp: 64DFEE2C:  64DFEE60  013804D2  10247350  0FCE2028  00000000  64DFEF6D 

2008-09-17 04:24:49.09 spid87      *      SegCs: 0000001B: 

2008-09-17 04:24:49.09 spid87      *     EFlags: 00010206:  00610044  00610074  004D005C  00410063  00650066  005C0065 

2008-09-17 04:24:49.09 spid87      *        Esp: 64DFEE10:  64DFEF21  10247350  0FCE2028  102472F8  64DFEE54  02450AD1 

2008-09-17 04:24:49.09 spid87      *      SegSs: 00000023: 

2008-09-17 04:24:49.09 spid87      * *******************************************************************************

The format of this register dump is:

<register>: <value>: <first 24 bytes of memory at the address of value>

So for Edi register,

2008-09-17 04:24:49.09 spid87      *        Edi: 10247350:  00000000  00000000  00000000  00000000  00000000  00000000  (total values is 24 bytes from left to right starting from 00000000)

The value of the register at the time of exception was 00000000  this the data is less important but look the registers that only have values but don’t have a 24 bytes listed after Ebx, Ecx, Edx, SegCs, SegSs.

2008-09-17 04:24:49.09 spid87      *        Ebx: 00000000: 

2008-09-17 04:24:49.09 spid87      *        Ecx: 00000000: 

2008-09-17 04:24:49.09 spid87      *        Edx: 00000040: 

2008-09-17 04:24:49.09 spid87      *      SegCs: 0000001B: 

 

2008-09-17 04:24:49.09 spid87      *      SegSs: 00000023: 

When the server produces the stack dump, it tries to see if the value of the register is a valid memory address (it does this using the windows virtualquery API). If the value is not a valid memory address, the server doesn’t display any bytes on the right side. This is an easy method to see whether any registers might contain an invalid address.  Not all registers are used just for pointers, so it might be perfectly normal for it to contain a valid value that is not to be used as a memory address.

Short Stack Dump:

 

2008-09-17 04:24:49.09 spid87      * Short Stack Dump

2008-09-17 04:24:49.12 spid87      01105772 Module(sqlservr+00105772)

2008-09-17 04:24:49.12 spid87      013804D2 Module(sqlservr+003804D2)

2008-09-17 04:24:49.12 spid87      01439AD1 Module(sqlservr+00439AD1)

2008-09-17 04:24:49.12 spid87      0143995C Module(sqlservr+0043995C)

2008-09-17 04:24:49.12 spid87      01380F7A Module(sqlservr+00380F7A)

2008-09-17 04:24:49.12 spid87      01380EAF Module(sqlservr+00380EAF)

2008-09-17 04:24:49.12 spid87      01380EAF Module(sqlservr+00380EAF)

2008-09-17 04:24:49.12 spid87      01380EAF Module(sqlservr+00380EAF)

2008-09-17 04:24:49.12 spid87      01380EAF Module(sqlservr+00380EAF)

2008-09-17 04:24:49.12 spid87      01380EAF Module(sqlservr+00380EAF)

2008-09-17 04:24:49.12 spid87      01380EAF Module(sqlservr+00380EAF)

2008-09-17 04:24:49.12 spid87      01380EAF Module(sqlservr+00380EAF)

2008-09-17 04:24:49.12 spid87      01380EAF Module(sqlservr+00380EAF)

2008-09-17 04:24:49.12 spid87      01380EAF Module(sqlservr+00380EAF)

2008-09-17 04:24:49.12 spid87      01380EAF Module(sqlservr+00380EAF)

2008-09-17 04:24:49.12 spid87      013810A9 Module(sqlservr+003810A9)

2008-09-17 04:24:49.12 spid87      0125362A Module(sqlservr+0025362A)

2008-09-17 04:24:49.12 spid87      01253CCA Module(sqlservr+00253CCA)

2008-09-17 04:24:49.12 spid87      013CDC85 Module(sqlservr+003CDC85)

2008-09-17 04:24:49.12 spid87      013CDD56 Module(sqlservr+003CDD56)

2008-09-17 04:24:49.12 spid87      0125B712 Module(sqlservr+0025B712)

2008-09-17 04:24:49.12 spid87      0125B0B5 Module(sqlservr+0025B0B5)

2008-09-17 04:24:49.12 spid87      01320C83 Module(sqlservr+00320C83)

2008-09-17 04:24:49.12 spid87      0125B99F Module(sqlservr+0025B99F)

2008-09-17 04:24:49.12 spid87      0102C51D Module(sqlservr+0002C51D)

2008-09-17 04:24:49.12 spid87      010438E5 Module(sqlservr+000438E5)

2008-09-17 04:24:49.12 spid87      01041C35 Module(sqlservr+00041C35)

2008-09-17 04:24:49.12 spid87      0100889F Module(sqlservr+0000889F)

2008-09-17 04:24:49.12 spid87      010089C5 Module(sqlservr+000089C5)

2008-09-17 04:24:49.12 spid87      010086E7 Module(sqlservr+000086E7)

2008-09-17 04:24:49.12 spid87      010D764A Module(sqlservr+000D764A)

2008-09-17 04:24:49.12 spid87      010D7B71 Module(sqlservr+000D7B71)

2008-09-17 04:24:49.12 spid87      010D746E Module(sqlservr+000D746E)

2008-09-17 04:24:49.15 spid87      010D83F0 Module(sqlservr+000D83F0)

2008-09-17 04:24:49.15 spid87      781329AA Module(MSVCR80+000029AA)

2008-09-17 04:24:49.17 spid87      78132A36 Module(MSVCR80+00002A36)

This is called short stack dump because it is dump of the stack frame at the context of the event that caused the stack dump. (like an AV )

2008-09-17 04:24:49.20 spid87      Stack Signature for the dump is 0x39F8A80D

2008-09-17 04:24:50.57 spid87      Potential image corruption/hotpatch detected. This may be a sign of a hardware problem or caused by presence of CLR/jitted images on the stack. Check SQLDUMPER_ERRORLOG.log for details.

2008-09-17 04:24:50.59 Server      Error: 17310, Severity: 20, State: 1.

2008-09-17 04:24:50.59 Server      A user request from the session with SPID 87 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.

2008-09-17 04:24:50.90 spid87      Using ‘dbghelp.dll’ version ‘4.0.5’

2008-09-17 04:24:50.95 spid87      ***Stack Dump being sent to D:Program FilesMSSQL.1MSSQLLOGSQLDump3837.txt

2008-09-17 04:24:50.95 spid87      SqlDumpExceptionHandler: Process 87 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

The first line is called a stack hash and is a value the server generated to uniquely identify the address of functions in the short stack dump.

So entire sequence is:

 1.      The exception is handled and the stack dump is produced.

2.      Any active transactions are rolled back.

3.      Any other resources associated with the query or the connection are destroyed.

4.      An error message is sent back to the client application.

5.      The connection is closed.

Cheers,

Bala

Posted in SQL SERVER FAILURES | 1 Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 177 other followers