database-wiki

Don't reinvent the wheel, just realign it!

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 ’0×80070005′ 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 ’0×80070005′ 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 ’0×80070005′ 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 ’0×80070005′ 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 ’0×80070005′ 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 ’0×80070005′ 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 ’0×80070005′ 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 ’0×80070005′ 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 “0×80070005” 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 0×00 in the dump. Probably the code was attempting to access a member of a class or structure that is a offset 0×00 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 »

Documented SQL Server Trace Flags, use them cautiously to avoid CHAOS!

Posted by database-wiki on October 20, 2012

About Trace Flags:

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

I prefer to generally stick with the first two options of setting trace flags. It’s especially easy to turn on a trace flag using the DBCC TRACEON command. For example, if you want to enable trace flag 1807, use the following syntax:

DBCC TRACEON(1807)

Use the DBCC TRACEOFF command to disable traces.

For either command, target multiple traces by separating each trace with a comma:

DBCC TRACEOFF(1807, 3604)

In the previous examples, this will only set the trace for the current connection (there are exceptions to this rule that I’ll discuss in a moment). If you’d like to set the trace using the DBCC TRACEON command at a server level, you can use the -1 switch as shown here:

DBCC TRACEON (8602, -1)

Checking for Running Traces

When you enable traces, it’s a good idea to see if other traces are currently running on your system. If you’re looking for a specific trace, use the DBCC TRACESTATUS command followed by the list of trace flags you want to check on. For example, the following syntax checks for trace flags 3604 and 1807:

DBCC TRACESTATUS(3604, 1807)

The resulting output displays in two columns: the trace flag is in one column, and the status (0 for off and 1 for on) is in the other column. It’s not uncommon to be totally unaware of which traces are active. To enumerate a complete list of traces that are on, use the DBCC TRACESTATUS command followed by the (-1) parameter:

DBCC TRACESTATUS(-1)

Starting Traces Automatically

You can start traces automatically when starting SQL Server by using the -T switch. Set the trace flags you want to execute at startup in the Startup Parameters dialog box (see Figure 5-9), which you reach by selecting Startup Parameters in the General tab of the Server Properties dialog box.

In the Trenches

I alluded to a few moments ago an interesting quirk with SQL Server. The quirk appears when you want to set a trace flag at an individual connection level. When this is set, SQL Server will apply the trace flag against anyone who has turned on an individual connection trace flag. For example, let’s say you turned on trace flag 8602 for an individual connection:

DBCC TRACEON (8602)

Another user then turns on trace flag 8755 for his individual user connection: DBCC TRACEON (8755)

You can then see the status of the trace flags and see that both are indeed turned on for each of the users. DBCC TRACESTATUS (-1) will result in:

TraceFlag Status

——— ——

8602        1

8755         1

Apply Traces Among All Connections (-1) Normally, a trace that is set in Query Analyzer only applies at the client level. If you use the undocumented trace flag of -1, the system applies any traces you’ve set across all active and new connections. This trace is not reported when you use the DBCC TRACESTATUS command.

List of documented trace flags:

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

2551-generate filtered dump.

http://blogs.msdn.com/b/askjay/archive/2010/02/05/how-can-i-create-a-dump-of-sql-server.aspx

1262-Force a minidump every time schedmon detects a condition.

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

2544-Put maximum information in the dump: memory, threads, handles, etc.

http://blogs.msdn.com/b/askjay/archive/2010/02/05/how-can-i-create-a-dump-of-sql-server.aspx

2546-Dump all threads in the process.

http://blogs.msdn.com/b/askjay/archive/2010/02/05/how-can-i-create-a-dump-of-sql-server.aspx

1118 -Disables the first 8 mixed page allocations for tables.

http://support.microsoft.com/kb/328551

2505-Do not trace calls to traceon/traceoff.

http://support.microsoft.com/kb/243352

683-Disallow row counter and column mod counters to be partitioned.

http://support.microsoft.com/kb/821548

3502-Send checkpoint state changes to errorlog

http://support.microsoft.com/kb/815436

8026-Under the trace flag 8026 all memory allocated out of the caches will be immediately considered for removal by the Resource Monitor in case of the memory pressure. This should address the issue when we’re low on memory and have plenty of memory in the caches, and we don’t kick out the entries in the caches. The downside of the fix is that the RM will be running more often and would be cleaning caches faster trying to keep the total memory consumption at the 80% of the total memory.

http://blogs.msdn.com/b/psssql/archive/2009/11/17/how-it-works-controlling-sql-server-memory-dumps.aspx

834-Turn on large page support for Buffer Pool(64-Bit only)

http://support.microsoft.com/kb/920093

=> Use Microsoft Windows large-page allocations for the buffer pool.

=> causes SQL Server 2005 to use Microsoft Windows large-page allocations for the memory that is allocated for the buffer pool. The page size varies depending on the hardware platform, but the page size may be from 2 MB to 16 MB. Large pages are allocated at startup and are kept throughout the lifetime of the process. Trace flag 834 improves performance by increasing the efficiency of the translation look-aside buffer (TLB) in the CPU.

=> Trace flag 834 applies only to 64-bit versions of SQL Server 2005. You must have the Lock pages in memory user right to turn on trace flag 834. You can turn on trace flag 834 only at startup.

=> Trace flag 834 may prevent the server from starting if memory is fragmented and if large pages cannot be allocated. Therefore, trace flag 834 is best suited for servers that are dedicated to SQL Server 2005.

8015-Disable auto detection and setup of NUMA

http://blogs.msdn.com/b/psssql/archive/2010/04/02/how-it-works-soft-numa-i-o-completion-thread-lazy-writer-workers-and-memory-nodes.aspx

-1 Sets trace flags for all client connections, rather than for a single client connection. Because trace flags set using the -T command-line option automatically apply to all connections, this trace flag is used only when setting trace flags using DBCC TRACEON and DBCC TRACEOFF.

106 Disables line number information for syntax errors.

http://msdn.microsoft.com/en-us/library/ms151872(v=sql.100).aspx

205 Report when a statistics-dependent stored procedure is being recompiled as a result of AutoStat.

http://support.microsoft.com/kb/195565

208 SET QUOTED IDENTIFIER ON.

http://support.microsoft.com/kb/243352

661 Re-enable cleanup process (turn on 661 first).

http://support.microsoft.com/kb/920093

652 Disables read ahead for the server.

http://support.microsoft.com/kb/920093

818 Retrieves extended HArdware Problems informations.

http://support.microsoft.com/kb/826433

1180 Forces allocation to use free pages for text or image data and maintain efficiency of storage.

http://support.microsoft.com/kb/272220

1204 Returns the type of lock participating in the deadlock and the current command affect by the deadlock.

http://msdn.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

1807 Allows you to configure SQL Server with network-based database files.

http://support.microsoft.com/kb/304261

3213 Trace for backups.

http://blogs.msdn.com/b/psssql/archive/2008/01/28/how-it-works-sql-server-backup-buffer-exchange-a-vdi-focus.aspx

3205 Disables hardware compression for tape drivers.

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

3226 This trace flag will prevent all informational BACKUP and RESTORE messages from being written to the SQL Server error log and the NT application event log.

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

3222 Disables the read ahead that is used by the recovery operation during roll forward operations.

http://support.microsoft.com/kb/268081

3502 Prints a message to the log at the start and end of each checkpoint.

http://support.microsoft.com/kb/815436

3602 Records all error and warning messages sent to the client.

http://support.microsoft.com/kb/199037

3604 Sends trace output to the client. Used only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF.

http://blogs.msdn.com/b/askjay/archive/2011/01/21/why-do-we-need-trace-flag-3604-for-dbcc-statements.aspx

3605 Sends trace output to the error log. (If you start SQL Server from the command prompt, the output also appears on the screen.)

http://support.microsoft.com/kb/199037

3607 Skips automatic recovery (at startup) for all databases.

3608 Skips automatic recovery (at startup) for all databases except the master database.

3609 Skips the creation of the tempdb database at startup. Use this trace flag if the device or devices on which tempdb resides are problematic or problems exist in the model database.

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/3a0c3b2d-ed1e-4737-8067-44a33f7e6db9/

3626 Turns on tracking of the CPU data for the sysprocesses table.

http://support.microsoft.com/kb/322109

3640 Eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. This is similar to the session setting of SET NOCOUNT ON, but when set as a trace flag, every client session is handled this way.

http://blogs.msdn.com/b/selvar/archive/2010/07/14/delete-operation-editing-a-data-source-from-a-reporting-service-2005-report-manager-fails-internalcatalogexception-and-throwing-watson-dump.aspx

4022 Bypasses automatically started procedures.

4032 trace flag to trace the calls from the driver.

http://support.microsoft.com/kb/139655

4030 Prints both a byte and ASCII representation of the receive buffer. Used when you want to see what queries a client is sending to SQL Server. You can use this trace flag if you experience a protection violation and want to determine which statement caused it. Typically, you can set this flag globally or use SQL Server Enterprise Manager. You can also use DBCC INPUTBUFFER.

4031 Prints both a byte and ASCII representation of the send buffers (what SQL Server sends back to the client). You can also use DBCC OUTPUTBUFFER. 4032 Traces the SQL commands coming in from the client. The output destination of the trace flag is controlled with the 3605/3604 trace flags.

http://support.microsoft.com/kb/152032

7300 Retrieves extended information about any error you encounter when you execute a distributed query OLE Errors.

http://support.microsoft.com/kb/314530

7501 Dynamic cursors are used by default on forward-only cursors. Dynamic cursors are faster than in earlier versions and no longer require unique indexes. This flag disables the dynamic cursor enhancements and reverts to version 6.0 behavior.

http://support.microsoft.com/kb/152032

7502 Disables the caching of cursor plans for extended stored procedures.

http://support.microsoft.com/kb/235476

8202 Replicates all UPDATE commands as DELETE/INSERT pairs at the publisher.

http://support.microsoft.com/kb/251187

8206 Supports stored procedure execution with a user specified owner name for SQL Server subscribers or without owner qualification for heterogeneous subscribers in SQL Server 2000.

http://support.microsoft.com/kb/284228

8207 Enables singleton updates for Transactional Replication, released with SQL Server 2000 Service Pack 1.

http://support.microsoft.com/kb/302341

8599 Allows you to use a savepoint within a distributed transaction.

http://support.microsoft.com/kb/295027

8679 Prevents the SQL Server optimizer from using a Hash Match Team operator.

http://support.microsoft.com/kb/818671

8721 Dumps information into the error log when AutoStat has been run.

http://support.microsoft.com/kb/195565

=> You should not do anything to get autostats on system tables.

=> You can turn on trace flag 8721 and you will see what stats we auto create.

=> In addition, you can turn on trace flag 3805 and use sp_helpstats to see stats auto created on system tables.

*********************

While restoring the transaction log files, you can use the 3222 trace flag to disable the read ahead feature that is used by the recovery operation during the roll forward operations. However, you must not use the 652 trace flag that disables the read ahead feature for the server.

http://support.microsoft.com/kb/268081 http://support.microsoft.com/kb/920093

*********************

For a user database in an instance of SQL Server, you can clear the “Loading” status by running the following Transact-SQL statement in SQL Query Analyzer: RESTORE DATABASE <Database name> WITH RECOVERY

*********************

http://support.microsoft.com/kb/315447

This fix alone does not limit the amount of Lazy Write activity. Microsoft has added a new trace flag, 809, to limit the amount of Lazy Write activity.

The limit with the 809 trace flag in page writes is 10000 – Outstanding Pages from Checkpoint .

I was able to generate about 6300 Lazy Writes, which was about 101000 Page Writes (789 MB) in less than 5 seconds, which was almost 1/3 of the size of the buffer cache with which I was testing. Normally, checkpoint takes at least 100 seconds to write that number of pages.

Additionally, because the disk IO spike is very short term, the use of large collection intervals in System Monitor may hide an issue due to averaging; therefore, you should use an interval of 1 or 5 seconds maximum if you suspect this issue.

Finally, you can also use checkpoint trace flags 3502 and 3504 to detect this situation, in that extremely low rates of pages flushed per time (< 10 pages flushed/sec) may also be an indication that too many pages are being lazy written.

http://support.microsoft.com/kb/815436

http://blogs.msdn.com/b/joaol/archive/2008/11/20/sql-server-checkpoint-problems.aspx

*********************

To capture the output of the following DBCC commands, you must enable trace flag 3604 or 3605 on the connection that is running the queries:

• DBCC PSS

http://support.microsoft.com/kb/832977

*********************

http://support.microsoft.com/kb/308822

If you create a table in a transaction and the same transaction then populates the table, the cardinality estimates assume that the table still has zero rows. This affects the performance of any query that you execute against this table in the same transaction.

For example, you might create and populate a table with millions of rows of data, but the optimizer still considers the table empty. Subsequent queries in the same transaction that use the table may estimate the return of an incorrect number of rows, which leads to poor query performance.

To resolve this problem, follow these steps: 1.  Obtain the latest service pack for SQL Server 2000. For information on how to obtain the latest SQL Server 2000 Service Pack, see the following article in the Microsoft Knowledge Base: 290211 How to obtain the latest SQL Server 2000 service pack  2.  Turn on trace flag 3913.  3.  Execute complex queries that will not use trivial plans. For trivial plans, the cardinality estimate is still 1.0 for a scan of such a table.  Note When you turn on trace flag 3913, the optimizer gets row count information from in-memory metadata that is saved to sysindexes system table when the transaction commits.

********************************

related to checkpoint:

http://support.microsoft.com/kb/815436

3502 logs checkpoint 3505 disables checkpoint

*********************************

http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/26/lost-without-a-trace.aspx

Trace Flags 8602 (ignore all index hints)

*********************************

2301 – SQL 2005

http://support.microsoft.com/kb/920093

Trace flag 2301 affects costing, which affects plan choice. Trace flag 2301 discourages order-preserving parallelism. The most significant effect is that trace flag 2301 discourages parallel merge join. When to use trace flag 2301 Trace flag 2301 enables the following advanced logic in the cardinality estimation: • Base containment assumption  • Integer-based interpolation  • Use of the histogram even when the cardinality estimate is low  • Unlimited density remapping  When a potential cardinality estimation issue exists, enable trace flag 2301 to determine whether the issue is addressed by the logic that is enabled by this trace flag. When you should not use trace flag 2301 The potential disadvantage of this trace flag is that it uses more time and more memory during optimization. Do not use this trace flag for OLTP queries and for frequently compiled queries. One known issue is that if applications perform many column remapping functions (such as CONVERT, CAST, UPPER, or LOWER) and have many densities, enabling trace flag 2301 consumes lots of memory.

Trace flag 2301: Enable advanced decision support optimizations Trace flag 2301 enables advanced optimizations that are specific to decision support queries. This option applies to decision support processing of large data sets.

When you turn on trace flag 2301 at startup, the trace flag has global scope. When you turn on trace flag 2301 in a user session, the trace flag has session scope.

**********************************

KB920093

http://support.microsoft.com/kb/920093

This article describes the various trace flags that you can use to improve performance in Microsoft SQL Server 2005. Typically, you use these trace flags when SQL Server 2005 is running under a heavy workload.

Note The trace flags that this article describes are advanced tuning techniques. You should consider using these trace flags only after you do more basic and routine optimizations. For example, you should consider using these trace flags after you do the following optimizations:

• Index analysis

• I/O balancing

• SQL query profiling

• System statistics monitoring

Additionally, these trace flags are mainly helpful on high-end servers that have the following characteristics:

• Many CPUs, for example, more than eight CPUs

• Lots of main memory, for example, more than 8 gigabytes (GB) of memory

• High I/O rates, for example, more than 10,000 physical I/O per second or more than 500 megabytes (MB) per second  The trace flags that this article describes may decrease performance under some workloads. We recommend that you evaluate the effects on your workload on a test system before you deploy any changes in a production environment.

For more information about large-page support in Windows, visit the following Microsoft Developer Network (MSDN) Web site: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/memory/base/large_page_support.asp

http://support.microsoft.com/kb/920093

Trace flag 836: Use the max server memory option for the buffer pool Trace flag 836 causes SQL Server 2005 to size the buffer pool at startup based on the value of the max server memory option instead of based on the total physical memory. You can use trace flag 836 to reduce the number of buffer descriptors that are allocated at startup in 32-bit Address Windowing Extensions (AWE) mode. This gives room for more stolen pages. Trace flag 836 applies only to 32-bit versions of SQL Server 2005 that have the AWE allocation enabled. You can turn on trace flag 836 only at startup.

******************************************************************************

In SQL 2005, we can use with DATA_PURITY.

*******************************************************************************

DBCC TRACEON(2520,-1): for getting help on undocumented DBCC commands.

*******************************************************************************

Skip Startup Stored Procedures (4022) This is a handy trace flag for troubleshooting. It forces SQL Server to skip startup stored procedures. This is especially useful if a stored procedure has been altered and causes harm to your system. After you set this trace flag, you can then debug the stored procedure and set it back to its original state.

Ignore All Index Hints (8602) Trace flag 8602 is a commonly used trace flag to ignore index hints that are specified in a query or stored procedure. This is a fantastic option when you’re trying to determine if an index hint is hurting more than helping. Rather than rewriting the query, you can disable the hint using this trace flag and rerun the query to determine if SQL Server is handling the index selection better than the index hint.

Disable Locking Hints (8755) Trace flag 8755 will disable any locking hints like READONLY. By setting this, you allow SQL Server to dynamically select the best locking hint for the query. If you feel the query’s locking hint may be hurting performance, you can disable it and rerun the query.

Disable All Other Hints (8722) Lastly, the 8722 trace flag will disable all other types of hints. This includes the OPTION clause.

TIP By running all three 8602, 8755, and 8722 trace flags, you can disable all hints in a query. If you feel your performance is being negatively affected by a hint, you can set these rather than rewrite all the queries while you test. Generally speaking, there’s no reason to place hints on queries in SQL Server 7.0 or 2000.

*************************************************************

From SQL Server 2008 and later.

-T902 – bypass script upgrade mode.

*************************************************************

Cheers,

Bala

Posted in SQL SERVER | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 175 other followers