SQL Server Database Engine Forum

Performance Reports

Wed, 09 May 2012 03:50:32 Z

Hi,

I am an Oracle DBA and new in SQLServer 2012. If some one can help me to understand the performance monitoring in SQLServer.

1. How long history of performance related data is kept in the SQLServer (Like in Oracle, we can set a history for retention and later we can generate report based on this historical performance related data which is called AWR report or ASH reports).

2. How do we compare the performance reports of SQLServer when server is doing good and when server is not doing good because it will help us understand that where are the statistics differences between good and bad times.

Salman

temporary tables in tempdb

Tue, 08 May 2012 12:36:29 Z

I had created one temporary table and used in my project then i got the doubt that if sql server crashes and then i have to restart it then tempdb will be truncated.that means temporary tables created are also gone.then how can i restore the temporary tables which were created. I am a beginning so want to know.

Thanks in Advance

Stack Dump and SQL 2008 R2 server Autorestarted

Tue, 08 May 2012 23:15:54 Z

Hello My SQL server just crashed this afternoon can you take a look of my mdmp file and tell me what would happened

this is the address where I Upload the file

http://www.filefactory.com/file/1oc85w4e0z5l/n/SQLDump0001.mdmp

thanks

Ask a question about SQL performance tunning

Tue, 08 May 2012 23:29:27 Z

Hi guys,

I have a question about tunning SQL database engine. I need to maintain one SQL Server 2008. It runs on one Windows 2008 R2 Enterprise. OS is 64 Bit, SQL Server is 2008, 32Bit(on SSAS, has SSRS, also run SQL Agent).

When I start SQL Server Windows service, it consumes ~2GB memory. After several days running, it consumes 4~5GB memory. Now I need your help on:

1 How to find why SQL Server consumes so many memory?

2 Can I know which part consumes so many memory?

My purpost is to low the memory consumption so that I can gain performance improvement on this SQL Server. Because sometimes SQL server eats too much memory, my subscrition mail on SSRS even fails.

Warm regards,

Riquel

Best regards,
Riquel

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Is there a volume size limit on a windows 2008 R2 Enterprise 64 bit + sql 2008 SP2 Enterprise 64 bit cluster system

Tue, 08 May 2012 14:29:17 Z

What kind of disks are supported with sql server cluster and what is the size limit for those disks. Our san team just told me that 2TB is the limit but I don't think that's right, is there a link that can point me to a documentation to prove 2TB is not the limit?

Thank you

Gokhan Varol

DACPAC powershell deployment issue

Wed, 09 Nov 2011 15:22:34 Z

Hi,

I am using SQLServer 2008 R2 with SP1 and getting an error while deploying a DACPAC by using powershell script. The DACPAC has been generated by using MS VisualStudio 2010 Version 10.0.402191 SP1Rel.

Powershell script :

#####################################################################
# started powershell script code #####################################################################

## Set variables.

$logFile = "C:\Temp\DBDeploymentLog.txt"
$serverName = "TestServer"
$userName = "user"
$password = "password"
$dacpacPath = "C:\Temp\Database.dacpac"
$dacName = "SPStore"

$break = "`n----------------Starting Database Deployment---------------------------------"
Add-Content $logFile $break

$msg = "`nStarting DB creation: " + (date -format "yyyy-MM-dd:HH-mm-ss")
Add-Content $logFile $msg

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.Sdk.Sfc")
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.Dac")
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

$serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($serverName, $userName, $password)
$sqlStoreConnection = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection($serverConnection.SqlConnectionObject)

$sqlStoreConnection.Connect()
Echo ------------Connected-----------------

$newDacStore = New-Object Microsoft.SqlServer.Management.Dac.DacStore($sqlStoreConnection)
$fileStream = [System.IO.File]::Open($dacpacPath, [System.IO.FileMode]::OpenOrCreate)

ECHO -------------Loading FIlestream-------------------
$dacType = [Microsoft.SqlServer.Management.Dac.DacType]::Load($fileStream)
ECHO -------------Loaded FIlestream-------------------

$newDacStore.add_DacActionStarted({Write-Host `n`nStarting at $(get-date) :: $_.Description})
$newDacStore.add_DacActionFinished({Write-Host Completed at $(get-date) :: $_.Description})

$evaluateTSPolicy = $true
$deployProperties = New-Object Microsoft.SqlServer.Management.Dac.DatabaseDeploymentProperties($serverconnection,$dacName)

$msg = "`nDeploying new instance of database"
Add-Content $logFile $msg

ECHO -------------DEPLOYING-------------------
$newDacStore.Install($dacType, $deployProperties, $evaluateTSPolicy)
ECHO -------------DEPLOYING-------------------

$fileStream.Close()

#####################################################################
# Finished powershell script code ####################################################################

I am able to connect to database and getting the following error in line "New-Object Microsoft.SqlServer.Management.Dac.DacStore($sqlStoreConnection)"

error :

New-Object : Cannot find an overload for "DacStore" and the argument count: "1".
At C:\temp\DACPAC Installation.ps1:32 char:26
+ $newDacStore = New-Object <<<< Microsoft.SqlServer.Management.Dac.DacStore($sqlStoreConnection)
+ CategoryInfo : InvalidOperation: (:) [New-Object], MethodException
+ FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

Anybody has encountered the same error and any help would be appreciated.

Thanks,

Prav

Ask a question about upgrade SQL2008 to SQL 2012

Tue, 08 May 2012 23:32:46 Z

Hi guys,

I have a question for your kind help. I need to upgrade one SQL Server 2008 to SQL 2012. My OS is 64Bit, but SQL2008 is 32Bit. I need to upgrade to SQL2012 64Bit. I can't just install new SQL2012, because I have many SSRS reports. I want to migrate the SSRS reports easily once. Please give me some help on my requirement.

Thanks!

Best regards,
Riquel

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Fixed length Stack based logic in T-SQL

Tue, 08 May 2012 21:44:55 Z

Hello,

I am trying to implement Stack based mechanism in T-SQL to load Fact tables. I want my Fact tables to be always be loaded with data from last 90 days.

Today when I initially load the data it would have data for the last 90 days. When tomorrow comes then I want the new day data come in and the last day data at the bottom of stack to load in another table.

For instance , assume Mar, April and May has 90 days in total just for example. Table name is Fact_LC

Now 1st June data comes in Fact_LC, I want 1st March data to go from Fact_LC and then append into another table called Fact_LCFull.

Thus at a given point of time Fact_LC will always have 90 days of worth data and Fact_LCFull have all the data(say for 3 years).

This can be done using group functions(Max , min) .But I am wondering if there are any ideas or new concepts in SQL which can be efficient and quick to do such kind of thing.

Complete Image or SQL 2008 R2, Instance root directory shows ONLY default location

Mon, 07 May 2012 18:21:31 Z

I am preparing SQL 2008 R2 image (on Win 2008 R2 SP1 server) . Completed "Image Preparation" part with required info, including (non default) instance root directory. When I go thru 2nd step, "Image Completion", Instance Configuration step displays default location (C:\Program Files\Microsoft SQL Server) as Instance Root Directory, not the one I have given during preparation step. Obviousely, I can't change it in completion step.

How come Instance Root Directory is falling back to default, is it a known bug, what are my options here?

--Prabhu

Prabhu Boggavarapu

SQL Server Stack DUMP Generated

Tue, 11 Oct 2011 14:25:21 Z

We just had our SQL Server crash last week with a Crash dump generated, a bit about the Environment (This is a multi instance Environment)

Server OS: Windows 2008 R2 Enterprise,64 bit
Memory: 256 GB
SQL Server: 2008 R2 Enterprise Edition
Max Server Memory: 60 GB
Processor Count: 16

I am attaching Excerpts from the SQL Servr Error log (Below) while SQL Server went down. Can someone help me analyze this.

2011-10-07 10:45:49.07 Server      Error: 17311, Severity: 16, State: 1.
2011-10-07 10:45:49.07 Server      SQL Server is terminating because of fatal exception e06d7363. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).
2011-10-07 10:45:49.14 Server      Using 'dbghelp.dll' version '4.0.5'
2011-10-07 10:45:49.16 Server      **Dump thread - spid = 0, EC = 0x0000000000000000
2011-10-07 10:45:49.16 Server      ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL10_50.DWDEV1\MSSQL\LOG\SQLDump0001.txt
2011-10-07 10:45:49.16 Server      * *******************************************************************************
2011-10-07 10:45:49.16 Server      *
2011-10-07 10:45:49.16 Server      * BEGIN STACK DUMP:
2011-10-07 10:45:49.16 Server      *   10/07/11 10:45:49 spid 24308
2011-10-07 10:45:49.16 Server      *
2011-10-07 10:45:49.16 Server      * ex_handle_except encountered exception E06D7363 - Server terminating
2011-10-07 10:45:49.16 Server      *
2011-10-07 10:45:49.16 Server      *
2011-10-07 10:45:49.16 Server      * MODULE                          BASE      END       SIZE
2011-10-07 10:45:49.16 Server      * sqlservr                       0000000000B00000 00000000046ADFFF 03bae000
2011-10-07 10:45:49.16 Server      * ntdll                          0000000077880000 0000000077A2BFFF 001ac000
2011-10-07 10:45:49.16 Server      * kernel32                       0000000077660000 000000007777EFFF 0011f000
2011-10-07 10:45:49.16 Server      * KERNELBASE                     000007FEFDA00000 000007FEFDA6AFFF 0006b000
2011-10-07 10:45:49.16 Server      * ADVAPI32                       000007FEFE100000 000007FEFE1DAFFF 000db000
2011-10-07 10:45:49.16 Server      * msvcrt                         000007FEFF520000 000007FEFF5BEFFF 0009f000
2011-10-07 10:45:49.16 Server      * sechost                        000007FEFF5C0000 000007FEFF5DEFFF 0001f000
2011-10-07 10:45:49.16 Server      * RPCRT4                         000007FEFF760000 000007FEFF88DFFF 0012e000
2011-10-07 10:45:49.16 Server      * MSVCR80                        00000000753D0000 0000000075498FFF 000c9000
2011-10-07 10:45:49.16 Server      * MSVCP80                        00000000754A0000 00000000755A8FFF 00109000
2011-10-07 10:45:49.16 Server      * sqlos                          0000000071060000 0000000071066FFF 00007000
2011-10-07 10:45:49.16 Server      * Secur32                        000007FEFD3F0000 000007FEFD3FAFFF 0000b000
2011-10-07 10:45:49.16 Server      * SSPICLI                        000007FEFD680000 000007FEFD6A4FFF 00025000
2011-10-07 10:45:49.16 Server      * pdh                            000007FEFA920000 000007FEFA96DFFF 0004e000
2011-10-07 10:45:49.16 Server      * SHLWAPI                        000007FEFF930000 000007FEFF9A0FFF 00071000
2011-10-07 10:45:49.16 Server      * GDI32                          000007FEFF250000 000007FEFF2B6FFF 00067000
2011-10-07 10:45:49.16 Server      * USER32                         0000000077780000 0000000077879FFF 000fa000
2011-10-07 10:45:49.16 Server      * LPK                            000007FEFDDC0000 000007FEFDDCDFFF 0000e000
2011-10-07 10:45:49.16 Server      * USP10                          000007FEFDBB0000 000007FEFDC79FFF 000ca000
2011-10-07 10:45:49.16 Server      * USERENV                        000007FEFCB00000 000007FEFCB1DFFF 0001e000
2011-10-07 10:45:49.16 Server      * profapi                        000007FEFD7E0000 000007FEFD7EEFFF 0000f000
2011-10-07 10:45:49.16 Server      * WINMM                          000007FEF6A80000 000007FEF6ABAFFF 0003b000
2011-10-07 10:45:49.16 Server      * IPHLPAPI                       000007FEFBA00000 000007FEFBA26FFF 00027000
2011-10-07 10:45:49.16 Server      * NSI                            000007FEFDDB0000 000007FEFDDB7FFF 00008000
2011-10-07 10:45:49.16 Server      * WINNSI                         000007FEFB9F0000 000007FEFB9FAFFF 0000b000
2011-10-07 10:45:49.16 Server      * opends60                       0000000071050000 0000000071057FFF 00008000
2011-10-07 10:45:49.16 Server      * NETAPI32                       000007FEFB780000 000007FEFB795FFF 00016000
2011-10-07 10:45:49.16 Server      * netutils                       000007FEFCD60000 000007FEFCD6BFFF 0000c000
2011-10-07 10:45:49.16 Server      * srvcli                         000007FEFD330000 000007FEFD352FFF 00023000
2011-10-07 10:45:49.16 Server      * wkscli                         000007FEFB750000 000007FEFB764FFF 00015000
2011-10-07 10:45:49.16 Server      * LOGONCLI                       000007FEFCF20000 000007FEFCF4FFFF 00030000
2011-10-07 10:45:49.16 Server      * SAMCLI                         000007FEFA560000 000007FEFA573FFF 00014000
2011-10-07 10:45:49.16 Server      * BatchParser                    0000000071020000 000000007104CFFF 0002d000
2011-10-07 10:45:49.16 Server      * IMM32                          000007FEFE2F0000 000007FEFE31DFFF 0002e000
2011-10-07 10:45:49.16 Server      * MSCTF                          000007FEFE1E0000 000007FEFE2E8FFF 00109000
2011-10-07 10:45:49.16 Server      * psapi                          0000000077A40000 0000000077A46FFF 00007000
2011-10-07 10:45:49.16 Server      * instapi10                      0000000074B70000 0000000074B7CFFF 0000d000
2011-10-07 10:45:49.16 Server      * cscapi                         000007FEF6A70000 000007FEF6A7EFFF 0000f000
2011-10-07 10:45:49.16 Server      * sqlevn70                       0000000070E10000 0000000071010FFF 00201000
2011-10-07 10:45:49.16 Server      * CRYPTSP                        000007FEFD060000 000007FEFD076FFF 00017000
2011-10-07 10:45:49.16 Server      * rsaenh                         000007FEFCD70000 000007FEFCDB6FFF 00047000
2011-10-07 10:45:49.16 Server      * CRYPTBASE                      000007FEFD710000 000007FEFD71EFFF 0000f000
2011-10-07 10:45:49.16 Server      * BROWCLI                        000007FEF68C0000 000007FEF68D1FFF 00012000
2011-10-07 10:45:49.16 Server      * AUTHZ                          000007FEFD190000 000007FEFD1BEFFF 0002f000
2011-10-07 10:45:49.16 Server      * MSCOREE                        000007FEFA360000 000007FEFA3CEFFF 0006f000
2011-10-07 10:45:49.16 Server      * ole32                          000007FEFF310000 000007FEFF511FFF 00202000
2011-10-07 10:45:49.16 Server      * credssp                        000007FEFCBB0000 000007FEFCBB8FFF 00009000
2011-10-07 10:45:49.16 Server      * msv1_0                         000007FEFCE80000 000007FEFCED0FFF 00051000
2011-10-07 10:45:49.16 Server      * cryptdll                       000007FEFD360000 000007FEFD373FFF 00014000
2011-10-07 10:45:49.16 Server      * kerberos                       000007FEFD090000 000007FEFD142FFF 000b3000
2011-10-07 10:45:49.16 Server      * MSASN1                         000007FEFD880000 000007FEFD88EFFF 0000f000
2011-10-07 10:45:49.16 Server      * schannel                       000007FEFCDC0000 000007FEFCE16FFF 00057000
2011-10-07 10:45:49.16 Server      * CRYPT32                        000007FEFD890000 000007FEFD9F5FFF 00166000
2011-10-07 10:45:49.16 Server      * security                       0000000074B40000 0000000074B42FFF 00003000
2011-10-07 10:45:49.16 Server      * WS2_32                         000007FEFF2C0000 000007FEFF30CFFF 0004d000
2011-10-07 10:45:49.16 Server      * RsFxFt                         0000000070A40000 0000000070A48FFF 00009000
2011-10-07 10:45:49.16 Server      * SHELL32                        000007FEFE320000 000007FEFF0A5FFF 00d86000
2011-10-07 10:45:49.16 Server      * OLEAUT32                       000007FEFF170000 000007FEFF246FFF 000d7000
2011-10-07 10:45:49.16 Server      * ftimport                       0000000060000000 0000000060024FFF 00025000
2011-10-07 10:45:49.16 Server      * MSFTE                          000000004DB10000 000000004DEBDFFF 003ae000
2011-10-07 10:45:49.16 Server      * VERSION                        000007FEFC930000 000007FEFC93BFFF 0000c000
2011-10-07 10:45:49.16 Server      * dbghelp                        00000000708D0000 0000000070A2DFFF 0015e000
2011-10-07 10:45:49.16 Server      * WINTRUST                       000007FEFDA70000 000007FEFDAA9FFF 0003a000
2011-10-07 10:45:49.16 Server      * ncrypt                         000007FEFD220000 000007FEFD26DFFF 0004e000
2011-10-07 10:45:49.16 Server      * bcrypt                         000007FEFD1F0000 000007FEFD211FFF 00022000
2011-10-07 10:45:49.16 Server      * mswsock                        000007FEFD000000 000007FEFD053FFF 00054000
2011-10-07 10:45:49.16 Server      * wship6                         000007FEFD080000 000007FEFD086FFF 00007000
2011-10-07 10:45:49.16 Server      * wshtcpip                       000007FEFCA00000 000007FEFCA06FFF 00007000
2011-10-07 10:45:49.16 Server      * ntmarta                        000007FEFC900000 000007FEFC92CFFF 0002d000
2011-10-07 10:45:49.16 Server      * WLDAP32                        000007FEFDE50000 000007FEFDE9FFFF 00050000
2011-10-07 10:45:49.16 Server      * ntdsapi                        000007FEFA9E0000 000007FEFAA06FFF 00027000
2011-10-07 10:45:49.16 Server      * DNSAPI                         000007FEFCE20000 000007FEFCE7AFFF 0005b000
2011-10-07 10:45:49.16 Server      * rasadhlp                       000007FEFAA20000 000007FEFAA27FFF 00008000
2011-10-07 10:45:49.16 Server      * fwpuclnt                       000007FEFB860000 000007FEFB8B2FFF 00053000
2011-10-07 10:45:49.16 Server      * bcryptprimitives               000007FEFCC70000 000007FEFCCBBFFF 0004c000
2011-10-07 10:45:49.16 Server      * CLBCatQ                        000007FEFF890000 000007FEFF928FFF 00099000
2011-10-07 10:45:49.16 Server      * sqlncli10                      000000006FB00000 000000006FDB7FFF 002b8000
2011-10-07 10:45:49.16 Server      * COMCTL32                       000007FEFAE50000 000007FEFAEEFFFF 000a0000
2011-10-07 10:45:49.16 Server      * COMDLG32                       000007FEFF0B0000 000007FEFF147FFF 00098000
2011-10-07 10:45:49.16 Server      * SQLNCLIR10                     000000006ECE0000 000000006ED16FFF 00037000
2011-10-07 10:45:49.16 Server      * netbios                        000007FEF3E70000 000007FEF3E79FFF 0000a000
2011-10-07 10:45:49.16 Server      * xpsqlbot                       000000006E240000 000000006E247FFF 00008000
2011-10-07 10:45:49.16 Server      * xpstar                         000000006E1B0000 000000006E237FFF 00088000
2011-10-07 10:45:49.16 Server      * SQLSCM                         000000006F940000 000000006F94DFFF 0000e000
2011-10-07 10:45:49.16 Server      * ODBC32                         000007FEF54A0000 000007FEF5550FFF 000b1000
2011-10-07 10:45:49.16 Server      * ATL80                          0000000074E20000 0000000074E3FFFF 00020000
2011-10-07 10:45:49.16 Server      * odbcint                        000000006FAA0000 000000006FAD7FFF 00038000
2011-10-07 10:45:49.16 Server      * clusapi                        000007FEFA8D0000 000007FEFA91FFFF 00050000
2011-10-07 10:45:49.16 Server      * resutils                       000007FEFA8B0000 000007FEFA8C8FFF 00019000
2011-10-07 10:45:49.16 Server      * xpstar                         000000006E180000 000000006E1A4FFF 00025000
2011-10-07 10:45:49.16 Server      * xplog70                        000000006E170000 000000006E17FFFF 00010000
2011-10-07 10:45:49.16 Server      * xplog70                        000000006E160000 000000006E161FFF 00002000
2011-10-07 10:45:49.16 Server      * instapi                        0000000019C40000 0000000019C4CFFF 0000d000
2011-10-07 10:45:49.16 Server      * dsrole                         000007FEFC730000 000007FEFC73BFFF 0000c000
2011-10-07 10:45:49.16 Server      * apphelp                        000007FEFD6B0000 000007FEFD706FFF 00057000
2011-10-07 10:45:49.16 Server      * msxmlsql                       000000006EAF0000 000000006EC40FFF 00151000
2011-10-07 10:45:49.16 Server      * oledb32                        000007FEF3BF0000 000007FEF3CFDFFF 0010e000
2011-10-07 10:45:49.16 Server      * MSDART                         000007FEF3BC0000 000007FEF3BE7FFF 00028000
2011-10-07 10:45:49.16 Server      * Comctl32                       000007FEFC170000 000007FEFC363FFF 001f4000
2011-10-07 10:45:49.16 Server      * OLEDB32R                       000000006E710000 000000006E723FFF 00014000
2011-10-07 10:45:49.16 Server      * comsvcs                        000007FEF35E0000 000007FEF378FFFF 001b0000
2011-10-07 10:45:49.16 Server      * db2oledb                       0000000035B80000 0000000035C58FFF 000d9000
2011-10-07 10:45:49.16 Server      * dbgtrace                       000000006DB50000 000000006DB6EFFF 0001f000
2011-10-07 10:45:49.16 Server      * SNAREG                         000000006DB40000 000000006DB4CFFF 0000d000
2011-10-07 10:45:49.16 Server      * RpcRtRemote                    000007FEFD7C0000 000007FEFD7D3FFF 00014000
2011-10-07 10:45:49.16 Server      * MSDRDA                         000000006D9D0000 000000006DB3EFFF 0016f000
2011-10-07 10:45:49.16 Server      * MFC80                          000000006D820000 000000006D9BBFFF 0019c000
2011-10-07 10:45:49.16 Server      * XOLEHLP                        000007FEF38B0000 000007FEF38C3FFF 00014000
2011-10-07 10:45:49.16 Server      * MFC80ENU                       000000006D810000 000000006D81CFFF 0000d000
2011-10-07 10:45:49.16 Server      * snanls                         000000006D7D0000 000000006D7F3FFF 00024000
2011-10-07 10:45:49.16 Server      * TRNSDT                         000000006D7C0000 000000006D7C7FFF 00008000
2011-10-07 10:45:49.16 Server      * TRNSBIDI                       000000006D7A0000 000000006D7B3FFF 00014000
2011-10-07 10:45:49.16 Server      * ddmstr                         000000006ED20000 000000006ED2FFFF 00010000
2011-10-07 10:45:49.16 Server      * dbghelp                        00000000386F0000 000000003884DFFF 0015e000
2011-10-07 10:45:49.16 Server      *
2011-10-07 10:45:49.16 Server      *     P1Home: 002E0032002E0031:
2011-10-07 10:45:49.16 Server      *     P2Home: 0000000000000030:
2011-10-07 10:45:49.16 Server      *     P3Home: 0051005300A400A2:
2011-10-07 10:45:49.16 Server      *     P4Home: 0000000060EE3A68: 0050005C003A0043 00720067006F0072 00460020006D0061 00730065006C0069 00630069004D005C 006F0073006F0072
2011-10-07 10:45:49.16 Server      *     P5Home: 0000000066CDCD70: 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000
2011-10-07 10:45:49.16 Server      *     P6Home: 00000000778D4043: 4100000120C48148 5F5C415D415E415F 9090909090C35B5E 56F3FF9090909090 4156415541544157 00000120EC814857
2011-10-07 10:45:49.16 Server      * ContextFlags: 000000000010000F: 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000
2011-10-07 10:45:49.16 Server      *      MxCsr: 0000000000001F80:
2011-10-07 10:45:49.16 Server      *      SegCs: 0000000000000033:
2011-10-07 10:45:49.16 Server      *      SegDs: 000000000000002B:
2011-10-07 10:45:49.16 Server      *      SegEs: 000000000000002B:
2011-10-07 10:45:49.16 Server      *      SegFs: 0000000000000053:
2011-10-07 10:45:49.16 Server      *      SegGs: 000000000000002B:
2011-10-07 10:45:49.16 Server      *      SegSs: 000000000000002B:
2011-10-07 10:45:49.16 Server      *     EFlags: 0000000000000202:
2011-10-07 10:45:49.16 Server      *        Rax: 0000000011541802: 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000
2011-10-07 10:45:49.16 Server      *        Rcx: 0000000066CDCB70: 002E0032002E0031 0000000000000030 0051005300A400A2 0000000060EE3A68 0000000066CDCD70 00000000778D4043
2011-10-07 10:45:49.16 Server      *        Rdx: 0000000000000000:
2011-10-07 10:45:49.16 Server      *        Rbx: 0000000000000000:
2011-10-07 10:45:49.16 Server      *        Rsp: 0000000066CDD180: 0000000000000000 0000000000000000 0000000066CDD3B0 000007FFFFE20000 00000000000042AC 0000000000000000
2011-10-07 10:45:49.16 Server      *        Rbp: 0000000066CDFE20: 0000000000000000 0000000000000000 0000000000000000 0000000000000000 00000000776F9220 00000000776F9220
2011-10-07 10:45:49.17 Server      *        Rsi: 0000000000000000:
2011-10-07 10:45:49.17 Server      *        Rdi: 0000000066CDD3B0: 0000000060EE3A60 0000000066CDE5A0 0000000066CE0000 00000000778CF001 000000006DA43D32 0000000066CD0033
2011-10-07 10:45:49.17 Server      *         R8: 0000000000000000:
2011-10-07 10:45:49.17 Server      *         R9: 0000000000000000:
2011-10-07 10:45:49.17 Server      *        R10: 0000000000000000:
2011-10-07 10:45:49.17 Server      *        R11: 0000000066CDD990: 002E0032002E0031 0000000000000030 0051005300A400A2 0000000060EE3A68 0000000066CDCD70 00000000778D4043
2011-10-07 10:45:49.17 Server      *        R12: 0000000000000001:
2011-10-07 10:45:49.17 Server      *        R13: 0000000000000000:
2011-10-07 10:45:49.17 Server      *        R14: 0000000066CDE860: 0068005F00780065 006C0064006E0061 00780065005F0065 0074007000650063 0063006E00650020 0074006E0075006F
2011-10-07 10:45:49.17 Server      *        R15: 000000000000003F:
2011-10-07 10:45:49.17 Server      *        Rip: 000007FEFDA0AA7D: C3000000C8C48148 9090909090909090 8348EC8348909090 00016A428D0F08F9 74894858245C8948 4D30247C89483824
2011-10-07 10:45:49.17 Server      * *******************************************************************************
2011-10-07 10:45:49.17 Server      * -------------------------------------------------------------------------------
2011-10-07 10:45:49.17 Server      * Short Stack Dump
2011-10-07 10:45:49.17 Server      000007FEFDA0AA7D Module(KERNELBASE+000000000000AA7D)
2011-10-07 10:45:49.17 Server      0000000002BA981D Module(sqlservr+00000000020A981D)
2011-10-07 10:45:49.17 Server      0000000002BADD3A Module(sqlservr+00000000020ADD3A)
2011-10-07 10:45:49.17 Server      0000000002BAD89D Module(sqlservr+00000000020AD89D)
2011-10-07 10:45:49.18 Server      0000000002FED496 Module(sqlservr+00000000024ED496)
2011-10-07 10:45:49.19 Server      00000000776F9380 Module(kernel32+0000000000099380)
2011-10-07 10:45:49.19 Server      0000000077916228 Module(ntdll+0000000000096228)
2011-10-07 10:45:49.19 Server      0000000077894F48 Module(ntdll+0000000000014F48)
2011-10-07 10:45:49.19 Server      00000000778B4F6D Module(ntdll+0000000000034F6D)
2011-10-07 10:45:49.19 Server      0000000077895B2C Module(ntdll+0000000000015B2C)
2011-10-07 10:45:49.19 Server      00000000778960FE Module(ntdll+00000000000160FE)
2011-10-07 10:45:49.20 Server      000007FEFDA0AA7D Module(KERNELBASE+000000000000AA7D)
2011-10-07 10:45:49.20 Server      000000007540B164 Module(MSVCR80+000000000003B164)
2011-10-07 10:45:49.20 Server      000000006DA2C0D0 Module(MSDRDA+000000000005C0D0)
2011-10-07 10:45:49.20 Server      000000006DA410CB Module(MSDRDA+00000000000710CB)
2011-10-07 10:45:49.20 Server      000000006DACA853 Module(MSDRDA+00000000000FA853)
2011-10-07 10:45:49.20 Server      000000006DA43D30 Module(MSDRDA+0000000000073D30)
2011-10-07 10:45:49.20 Server      000000006DA46EB6 Module(MSDRDA+0000000000076EB6)
2011-10-07 10:45:49.20 Server      000000006DA47250 Module(MSDRDA+0000000000077250)
2011-10-07 10:45:49.20 Server      000000006DA3E202 Module(MSDRDA+000000000006E202)
2011-10-07 10:45:49.20 Server      000000007767F56D Module(kernel32+000000000001F56D)
2011-10-07 10:45:49.20 Server      00000000778B2CC1 Module(ntdll+0000000000032CC1)
2011-10-07 10:45:49.20 Server      Stack Signature for the dump is 0x000000011404535D
2011-10-07 10:45:50.33 Server      External dump process return code 0x20000001.
External dump process returned no errors.

Thanks in advance

Regards,
Mani

OLE Automation Error Information - 0x80040220 Source: CDO.Message.1 Description: The "SendUsing" configuration value is invalid

Tue, 08 May 2012 21:04:28 Z

OLE Automation Error Information - 0x80040220 Source: CDO.Message.1 Description: The "SendUsing" configuration value is invalid

We have a stored procedure that is used to send out mail.
This stored procedure works fine on sql server 2005 on windows 2003.
We created a new instance Sql server 2008R2 on the same windows server 2003 to test our database before migration.

And we get this error while using the same code

And here is the stored procedure

CREATE Procedure [dbo].[sp_SMTPMailNew_debug]
@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@HtmlBody varchar(8000)=null,

AS

SET nocount on

declare @oMail int --Object reference
declare @resultcode int

EXEC @resultcode = sp_OACreate 'CDO.Message', @oMail OUT
if @resultcode = 0
BEGIN
   EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
   -- SELECT 'from ', @RESULTCODE
   EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress
   -- SELECT 'to ', @RESULTCODE
   EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
   -- SELECT 'subject ', @RESULTCODE

   EXEC @resultcode = sp_OASetProperty @oMail, 'HtmlBody', @HtmlBody
   -- SELECT 'HTML' , @RESULTCODE

   EXEC @resultcode = sp_OAMethod @oMail, 'Fields.Update', NULL
   -- SELECT 'fields.update ', @RESULTCODE
   EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
   exec sp_displayoaerrorinfo @omail, @resultcode
   EXEC sp_OADestroy @oMail
END

Anyone have any idea why it returns this error on sql server 2008r2 but not on sql server 2005 ( on the same windows server 2003 ) ?

In use mdf file with no corresponding database

Tue, 08 May 2012 16:04:52 Z

Windows Server 2003 R2 64-bit SP3

SQL Server Enterprise 2008 R2 64-bit

I was cleaning up databases on a test server when I noticed a .mdf file without a associated database in sysdatabases. This .mdf file is "access denied", when I try to attach the file to peek into it's contents. I checked SQL Server error logs but there is no mention of this .mdf file or a database with a file pointing to it. When I try to delete this file, I receive the error message "Access Denied". I am the admin on this Windows Server.

I am speculating that a database somewhere in our servers is using this file. But, I don't know how I could investigate further without running sys.databases on every SQL Server (through Central Management Server or individually on every server). In other words, is there a simpler way of investigating which database is using this .mdf file.

Thank you.

-Jeelani

Detect query timeout across Linked Server

Tue, 08 May 2012 19:30:00 Z

Hi,

I have a procedure on Server 1 returning some output parameters to a procedure on Server 2. Server 1 and Server 2 are linked servers.

--code on Server 1-------------------------------

create procedure returnAccountdata_toRemote

begin try

select top 1000 * from Accounts

end try

begin catch

raiserror ('Error on returnAccountData_toRemote', 20,1) with log

end catch

------------------------------------------------------

--code on Server 2-------------------------------

create procedure getAccountdata_toRemote

begin try

execute Server2.DBName.dbo.returnAccountdata_toRemote

end try

begin catch

select error_message()

end catch

--------------------------------------------------------------------------------

My problem is that this code does not handle a query time out error from Server2 to Server1. I need to detect when the timeout happened so I can gracefully stop further execution of the batch.

Is there any workaround to this problem? There were some resources online suggesting to parse output buffer but that is quite internal to SQL Server and could change in any version/service pack.

Thank you

"Compatibility level" how good a match to the Real McCoy

Tue, 08 May 2012 18:46:00 Z

We need to upgrade a sql 2000 database to sql 2008, but then set the upgraded databse's "compatibility level" to sql 2000 (to match our sql 2000 production database.)

I hope that setting the compatbility level to sql 2000 would make that database act just like it did in the sql 2000 environment (e.g. I would expect an error if I tried to create a CTE in the sql 2000-level database.) Is this correct, close to true, or not at all true?

Barkingdog

SQL Agent service is getting stopped when the server gets rebooted

Tue, 08 May 2012 19:17:16 Z

Hello All,

I have an issue with the SQL Agent. Every night we will reboot our server and SQL Agent service is getting stopped and we need to manually start the service.I have already checked Automatically restart the SQL Agent when it stops unexpectedly. But it is not working.Could anyone tell me how to automatically start the SQL agent when the server gets rebooted.

Please assist.

Thank you

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Mon, 07 May 2012 23:16:58 Z

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I have read alot about this error but none of the sluation work for me.

Here is my setup:

Frontend - Windows SBS 2011 - Webserver IIS7

Backend - Windows SBS 2003 - SQL 2005

What im trying to accomplish -

upload excel file and import the data to my sql server.

Code was taking from the following website EXCEL TO SQL code working till the part it suppose to get the data sent to the sql server im able to load the excel file to the upload folder i have on the IIS and able to pick the sheet number the data is on. when trying to submit i get the above error code.

I follow all the instractions to solve the issue but none help. the guy that wrote the code say i have a problem with the driver but i did a fresh install on on my IIS server which did not help at all.

Here is a list of what i did on each server

Web Server II7-

Install fresh copy of SBS 2011 and reconfig the IIS7 to work .net4 for my web application.
Microsoft Access database engine 2010 installed.
give everyone full permission to the NETWORK Services Temp folder

SQL Server -

Microsoft Access database engine 2010 installed.
change the SQL Server service to run on local account
make sure to enable AD Hoc
Reinstall the Microsoft Access database engine 2010
Ran the below sql

USE [master]
 GO
 
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
 GO
 
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
 GO

I will appreacite any suggestions you might have. Thank you

*** I just found something i think - when i log in using windows auth and run the following

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=z:\test.xlsx', 'SELECT * FROM [Sheet3$]');

I get a the data and no err when i login with my string user i get the err message. so this is a permission issue with sql i wonder what permission i need to give my user to be able to use the OLEDB 12???

Performance issue in Migrating SQL 2005 Express to SQL 2008 R2 Standard version

Tue, 08 May 2012 14:31:26 Z

I face it really weird situation where the Performace of my Database got decreased significantly when i have migrated it from SQL 2005 express edition to SQL 2008 R2 Standard edition.

Pl. help me in what can be gone wrong and how is it possible ...................

FI : Both instaces are on the same Server.

Linked Server Login Error

Fri, 04 May 2012 17:07:16 Z

All,

We have two SQLServer Default Instances with SQLServer 2008 on two different physical machines - SrvApps and SrvSSIS. On SrvApps, the SQLServer Service runs with the Local System Account but on SrvSSIS, the SQLServer Service runs with a DomainAccount - "SQLDom\SQLSrvLogin". Both the machines are in the same Domain SQLDom and the login exists in both the servers.

I tried establishing a linked server on SrvSSIS against SrvApps and I tried various ways and each of them keep failing. I want to use the Windows Authenticated Login "SQLDom\SQLSrvLogin" since it exists on both the servers with relevent permissions. So, in the Security screen, I entered "SQLDom\SQLSrvLogin" for the local login and checked the Impersonate box. Listed are the details with the relevent errors:

Any ideas?

-- Selected "Not to be Made" Radio button
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SrvApps', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SrvApps', @locallogin = N'SQLDom\SQLSrvLogin', @useself = N'True'
GO
Access to the remote server is denied because no login-mapping exists. (.Net SqlClient Data Provider)

-- Selected "Be Made without using a Security Context" Radio button

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SrvApps', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SrvApps', @locallogin = N'SQLDom\SQLSrvLogin', @useself = N'True'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SrvApps', @locallogin = NULL , @useself = N'False'
GO

The OLE DB provider "SQLNCLI10" for linked server 'SrvApps' reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server 'SrvApps'.
OLE DB provider "SQLNCLI10" for linked server 'SrvApps' returned message "Invalid authorization specification". (.Net SqlClient Data Provider)

-- Selected "Be Made using the login's current Security Context" Radio button

Also, in the last option, why is it using 'NT AUTHORITY\ANONYMOUS LOGON' since the SQLServer service on SrvSSIS runs off
of "SQLDom\SQLSrvLogin" Login?

Thanks,
MS

want a query through which we can check that from how much time the sql server is running???

Tue, 08 May 2012 12:38:55 Z

want a query through which we can check that from how much time the sql server is running???

Disk IO and CPU in sp_who2

Sun, 06 May 2012 14:34:15 Z

Hi,

Please tell me in which case Disk IO is more than CPU Time and vice-versa --Cpu Time is more than Disk IO

and what to do if Disk IO is too much then CPU like Disk IO --377889 and cpu --89

and Disk IO is more than CPu is cause any problem in database...

Thanks

create subfolder each DB when backup log performed

Tue, 08 May 2012 07:23:26 Z

dear all

I'm newbie on SQL Server I want to the folder will be automatically created for each DB's name when Log backup performed..???

anybody has scripts?? please help me..

Thanks for any helps

Regards

Receiving error when querying Linked Server

Thu, 03 May 2012 17:07:00 Z

I am having an Issue with Linked Servers, perhaps someone can explain what is going on.

I have three servers: ServerA, ServerB and Server C. Each has a Linked Server configured to the other two. (All MS SQL Server 2008 SP1).

All the linked servers are configured the same: "Be Made using the login's current security context" is selected, and "Data Access", "RPC", "RPC OUT" are all set to True.

My Domain account is a sysamdin on all three servers.

This is what I am experiencing:

1) Log In to Server A with Remote Desktop
2) Open SSMS, and Connect to ServerB
3) Execute Query ( SELECT Top 1 * from [LnkServerA].[Database].[dbo].[TableName] )
4) Receive error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
5) Log On to ServerB with Remote Desktop, and Connect to ServerB With SSMS
6) Execute Query ( SELECT Top 1 * from [LnkServerA].[Database].[dbo].[TableName] ) Successfully
7) Go back to ServerA and Run same query from Step 3, this time it is successful.

What is happening that I am not allowed to execute the query until I log on to ServerB and and execute it? From then on, I am able to run the Query from ServerA with no trouble. Is this a configuration problem?

Thanks

Matt

How to improve database performance

Thu, 03 May 2012 06:31:06 Z

Hi Friends ,

We are facing DB performance issue frequently and planning to improve the performance. But I don't know where to stat and where to end. Our DB is growing fast and if we are not making it healthy we will face serious issue soon. Please suggest...

We are not using any index views. So I'm planning to use it in all SP at thins pint. Will it increase the performance ?

-Mr.Nobody

xp_readerrorlog parameters in SQL Server 2012?

Wed, 25 Apr 2012 12:52:46 Z

Was trying to run the following on a 2012 box.

master

..xp_ReadErrorLog0, 1, 'scheduler'

Worked great on 2008 R2. However, on 2012 I get

Msg 22004, Level 12, State 1, Line 0

Error executing extended stored procedure: Invalid Parameter Type

Any ideas? Thanks

Edit or Creating new job gives error 18030: xp_sendmail in SQL 2000

Sun, 06 May 2012 21:43:25 Z

I am getting a error "Error 18030: xp_sendmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client. The job was not saved" when I try to create a new job or edit existing job.

I can save the jobs and create jobs but the error pops every time I edit/create.

Is a mail client needs to be installed even if its not used or needed by the jobs that I am going to create or edit ?

Thank you in advance.

Don

64 bit to 32 bit linked server problems

Mon, 07 May 2012 12:33:35 Z

I have a sql2008 r2 64 but computer trying to update a 32 bit sql2000 machine

I tried a multi-row update and got the error message

Msg 682, Level 22, State 148, Line 1
Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.

I saw that while multi-row updates crash with this error message, a single row update should work. I converted the update to a cursor and now I get the following error message.

OLE DB provider "SQLNCLI10" for linked server "xxx" returned message "Bookmark is invalid.".
Msg 7333, Level 16, State 2, Line 1
Cannot fetch a row using a bookmark from OLE DB provider "SQLNCLI10" for linked server "xxx".

the update stmt is like:

update y

set col=x.value

from sql2008.table x inner join sql2000.table y

on x.val=y.val collate <collasion>

???

Document Maintanceplan

Mon, 07 May 2012 13:19:54 Z

Hi

Is there an easy way to document a Maintanceplan?

I would like to document when the scheduledtasks run and what they perform, is there an easy way for this?
like export to pdf or an query to perform to view the plan?

Thanks

Jacob

If only I had time to learn everything I wanted ...

What is the wait state PREEMPTIVE_OS_WSASETLASTERROR?

Tue, 03 Apr 2012 16:41:31 Z

I've been having problems with one of our SQL Servers, 2008 R2, where once or twice a day it will hang (and timeout) all connection attempts for 2-5 minutes.

I've been trying to figure it out and the one thing that really stands out as odd, is that it is logging really high wait times for the Wait State "PREEMPTIVE_OS_WSASETLASTERROR". For a 15-minute period when this problem was occurring (for at least three minutes), this wait state added 30 to the "wait_state_count", and 651,200 to the "wait_time_ms" columns (from sys.dm_os_wait_states).

Unfortunately, this state does not seem to be documented any where on the Internet.

So, I have two questions about this:

What is the wait state "PREEMPTIVE_OS_WSASETLASTERROR"? and,
Why would I be seeing so much of it on our SQL Server?

(NOTE: I changed the wait state name because I had the wrong name before... sorry!)

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Please!Remember to Vote allhelpful replies as Helpful

Please Guide Me

Thu, 19 Apr 2012 12:26:46 Z

Hi,

When I am executing a query with more than 4 tables joins its taking more time to display the data

please give me some guidance to write a queries without loosing its performance to display the data

waiting for reply..................

Deadlock while performing insertion(no Bulk operations) operations in parallel

Wed, 02 May 2012 12:28:21 Z

Hello all,

This is my first post so please tolerate me for all my silly mistakes.

I am facing issues while troubleshooting Deadlock issues, we are getting data from some source using web services and putting it up in a Queue. This data is then feed into SQL server database tables using window services created on the server. We primarily used only one service but later increased them to 8 services that run all at the same time and execute same piece of code. Here, in the code we are trying to insert data in to 16 different tables. That means all 8 services try to insert data in same 16 tables all at the same time. Now, during this we are facing deadlocks. We have run SQL profiler with template: TSQL_Locks and found that there are deadlocks while insertions. I am pasting the deadlock details here:

Deadlock graph                    <deadlock-list>
 <deadlock victim="process559ae08">
  <process-list>
   <process id="process559ae08" taskpriority="0" logused="0" waitresource="OBJECT: 8:740197687:9 " waittime="11030" ownerId="1864028770" transactionname="user_transaction" lasttranstarted="2012-04-24T07:31:00.043" XDES="0x159dee3b0" lockMode="X"
 schedulerid="14" kpid="19016" status="suspended" spid="130" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-04-24T07:31:00.100" lastbatchcompleted="2012-04-24T07:31:00.043" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="4828"
 loginname="sqluser" isolationlevel="read committed (2)" xactid="1864028770" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="760" sqlhandle="0x02000000758b67241f8bad4a6c289d3a8cbdb950255d73cb">
INSERT INTO [ABC]([BatchRecordID],[DateOfBirth],[Disability],[Email],[LEACode],[SITSLastName],[Country_Birth],[FeeStatus],[LEAName],[Nationality],[BeenInCareValue],[ParentsHigherEducationCode],[BeenInCareCode],[ParentsHigherEducationValue],[UCASID],[Gender],[HomeEmail],[SITSStudentNumber],[Title],[SITSFirstName],[StudentNo],[Country])
 values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22)     </frame>
     <frame procname="adhoc" line="1" stmtend="1176" sqlhandle="0x0200000058ff67308d69296547143dae303a7e80b9dd8617">
INSERT INTO ABC (BatchRecordID , DateOfBirth, Disability, Email, LEACode, SITSLastName, Country_Birth, FeeStatus, LEAName, Nationality, BeenInCareValue, ParentsHigherEducationCode, BeenInCareCode, ParentsHigherEducationValue, UCASID, Gender, HomeEmail, SITSStudentNumber,
 Title, SITSFirstName, StudentNo, Country)  VALUES(--------------------------Values cleared out----------)     </frame>
    </executionStack>
    <inputbuf>
INSERT INTO ABC(BatchRecordID , DateOfBirth, Disability, Email, LEACode, SITSLastName, Country_Birth, FeeStatus, LEAName, Nationality, BeenInCareValue, ParentsHigherEducationCode, BeenInCareCode, ParentsHigherEducationValue, UCASID, Gender, HomeEmail, SITSStudentNumber,
 Title, SITSFirstName, StudentNo, Country)  VALUES(--------------------------Values cleared out----------)

INSERT INTO DEF(BatchRecordID , UCASCourseCode, InstituteCode, InstituteName, APFSeqNo, Decision, SeqNum)  VALUES(--------------------------Values cleared out----------)

INSERT INTO DEF (BatchRecordID , UCASCourseCode, InstituteCode, InstituteName, Decision)  VALUES(--------------------------Values cleared out----------)
    </inputbuf>
   </process>
   <process id="process4875048" taskpriority="0" logused="0" waitresource="OBJECT: 8:740197687:0 " waittime="11029" ownerId="1864028790" transactionname="user_transaction" lasttranstarted="2012-04-24T07:31:00.060" XDES="0x8cca09950" lockMode="X"
 schedulerid="10" kpid="9492" status="suspended" spid="125" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-04-24T07:31:00.120" lastbatchcompleted="2012-04-24T07:31:00.060" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="8272"
 loginname="sqluser" isolationlevel="read committed (2)" xactid="1864028790" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="580" sqlhandle="0x020000005b791e049c815fb4512ce3b249070e0b3f6635df">
INSERT INTO [ABC]([BatchRecordID],[UCASID],[UKEntryDate],[FeeStatus],[SITSStudentNumber],[Title],[SITSLastName],[Email],[SITSFirstName],[Country_Birth],[Nationality],[Country],[Gender],[HomeEmail],[StudentNo],[Disability],[DateOfBirth]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17)    
 </frame>
     <frame procname="adhoc" line="1" stmtend="898" sqlhandle="0x02000000d7b55c16df84264b7965df66ab75e47cda870abb">
INSERT INTO ABC(BatchRecordID , UCASID, UKEntryDate, FeeStatus, SITSStudentNumber, Title, SITSLastName, Email, SITSFirstName, Country_Birth, Nationality, Country, Gender, HomeEmail, StudentNo, Disability, DateOfBirth)  VALUES(--------------------------Values
 cleared out----------)
     </frame>
    </executionStack>
    <inputbuf>
INSERT INTO ABC(BatchRecordID , UCASID, UKEntryDate, FeeStatus, SITSStudentNumber, Title, SITSLastName, Email, SITSFirstName, Country_Birth, Nationality, Country, Gender, HomeEmail, StudentNo, Disability, DateOfBirth)  VALUES(--------------------------Values
 cleared out----------)

INSERT INTO DEF (BatchRecordID , UCASCourseCode, InstituteCode, InstituteName, Decision)  VALUES(--------------------------Values cleared out---------)
INSERT INTO DEF (BatchRecordID , UCASCourseCode, InstituteCode, InstituteName, Response, Decision, OfferSummary)  VALUES(--------------------------Values cleared out---------)
INSERT INTO DEF (BatchRecordID , UCASCourseCode, InstituteCode, InstituteName, Decision    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <objectlock lockPartition="9" objid="740197687" subresource="FULL" dbid="8" objectname="A.dbo.ABC" id="lock246aa1f80" mode="IX" associatedObjectId="740197687">
    <owner-list>
     <owner id="process4875048" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process559ae08" mode="X" requestType="wait"/>
    </waiter-list>
   </objectlock>
   <objectlock lockPartition="0" objid="740197687" subresource="FULL" dbid="8" objectname="A.dbo.ABC" id="locke6668a300" mode="X" associatedObjectId="740197687">
    <owner-list>
     <owner id="process559ae08" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process4875048" mode="X" requestType="wait"/>
    </waiter-list>
   </objectlock>
  </resource-list>
 </deadlock>
</deadlock-list>

The deadlock occurs on table ABC. This table has a primary key on ApplicantID and thus has a clustered index on this, Applicantid is the identity column. We have a non clustered index on BatchRecordID that is non unique. This is created as after insert we need to perform some update and select statements.

After insertions, we have a few(2-3) update statements on tables ABC and another table GHI. We then will perform select on these 16 tables and move data to our final tables i.e. will perform insert again. All this need to be either get successful and reach to final tables or get rolled back.

We have tried to drop non clustered index here that resulted in successful insertions but the deadlocks started occurring at the next level i.e. at update in ABC table as there is now only clustered index(we deleted the non clustered index as mentioned earlier).

We cannot reduce isolation level also. We may not be able to use non overlapping as we are using 8 processes to run the same code.We are doubtful to DISABLE LOCK ESCALATION here as we fear from memory issue on the server. Is memory a concern if we are disabling lock escalation?

Please suggest how to avoid deadlocks and maintain concurrency. We may want to increase concurrency to 16 services/streams if we are able to resolve deadlock situation but at this point we would like to restrict ourselves to 8 services only. SQL version is SQL 2008R2

Thanks a lot.

Link Server Connectivity with Vertica DB via ODBC connections

Tue, 08 May 2012 03:28:11 Z

Dear All/Admin,

Please do let us know if the below questions on the delays on queries between the MS(Microsoft)-Link Server and the Vertica DB can be solved in any way: -

Details related to technical issue faced for this ODBC connectivity -

1) Test are being done to ascertain ODBC connectivity between a Loyalty platform(InTelestage) and an EDW platform(Vertica)

2) This ODBC connectivity is to connect an application platform to an Enterprise Level Database for information related to campaign management
3) The enterprise Database is a Vertica Columnar based system/platform
4) The Application is a CLM (Customer Lifecycle Manager) platform using an SQL database to run the campaigns

5) The solution to use ODBC is neccesary because CLM queries are based on SQL syntax and the EDW Database is a Vertica database

SUMMARY detail reference
-> on connections it was realised that the ODBC connectivity is too slow - refer to attached document reference on slow speed of ODBC queries
-> on further investigation's this was narrowed to be an issue between the Microsoft Link Server and the Vertica adapters/driver
-> it was noted that the link server process is too slow and we need help to determine if performance can be improved or if some evaluation can be done to improve performance

TECHNICAL DETAILS related to queries for ODBC query

Methodology : - Our application are using the T-sql syntax in order to access third party databases (As vertica). We want to use views in order to acces Vertica systems.

Driver : The vertica driver version in used is 4.1. We have performance issues when trying to query using the T-sql syntax as following:

select account_no from LNKServer.thirdpartyDB.table where account_no='1'

versus the same statement when using openquery:

select * from openquery(LNKServer,'select account_no from table
where account_no=''1'')

CONCLUSION

The performance issue becomes worse when we try to make more complex queries. *The standard delay for a simple query takes approx. 2 minutes(SQL syntax) as compared to a direct open query on the platform(EDW-Vertica) - using Vertica syntax.

Questions :

The question is can we improve somehow the performance in order for the first query to run with similar performance as is going the openquery ?
Is there a way to improve performance related to these type of queries and who can assist us in this elimination process
Is there a way to achieve a possible solution for ODBC solutions which use VIEWS to connect to Vertica DB's

Thank you for your considerations.

DBCC Shrinkdatabase - can it be broken into multiple runs

Mon, 07 May 2012 23:29:47 Z

We have a 2 TB database, after we applied data compression, we want to release about 1 TB of space. We are using the DBCC shrinkdatabase command. my question is if we stop it in the middle, when we resume, does it pickup from where it left off? What does the SQL server do behind the scenes so as to shrink the DB?

Thanks

Strange $$$ temp files eating up drive

Mon, 07 May 2012 16:19:58 Z

The service account on my SQL Server 2008 box is generating temp files in its c:\users\<accname>\AppData\Local\Temp folder. They are all *.$$$ files and are being written continuously. None of them are very large in size. It appears as though they start out as a *.tmp file but are renamed to *.$$$ after a moment.

These files go back years and I'd like to delete them to reclaim a large amount of space on the drive, but I want to understand what they are, and why the SQL service account is writing them in the first place, before I do that.

btw, in case it is involved, TDE is turned on... maybe these are temp files that are used and then encrypted once the process releases them? Do they have a role in backups?

Thank you,

-Ethan Nelson

FileStream Option Questions...

Mon, 07 May 2012 14:39:51 Z

What does Allow remote clients to have streaming access to FileStream data mean? What are the repercussions? What kind of remote clients does this refer to? Instances of SSMS, .net app clients? I'm confused.
Also, what backup/restore implications should we be aware of when using FileStream? (i.e.: How do you recover from something happening to the file system that invalidates the pointers in the FileStream column?) .. I know it is off the wall but just want to gauge what our recoverability under this feature is if the following happened.
1. Scenario: SQL Server Service is turned off. Someone inadvertently deletes a file associated with a filestream column from the file system. What happens when SQL kicks back off & realizes that a file pointer it has is no longer valid?

Again, any assistance in helping us better understand the implications of this feature would be greatly appreciated.

Not able to connect to SQL Server - SPN Issue

Mon, 08 Dec 2008 15:57:24 Z

I am getting the following same error meessage

"The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies."

I am using Windows 2008 server with SQL Server 2008. I tried to run the SQL Server service with local account that doesnt work. Then i made that account to local admin, doesnt work either. Then i use the domain user, doesnt work. Then i thought of removing this server from the domain, delete the entry for this server from the AD and then join the server back into the domain. This is make sure that AD issue is not there. this also doesnt solve the prob.

I got couple of links on the different blogs on this error and none of them have specific answers to this problem. If anyone from the MS can also reply on this, would be greatly appreciated.

Regards

Prashant Thakwani

The request failed or the service did not respond in a timely fashion. Consult the event log or other application error logs for details.

Mon, 23 Aug 2010 06:43:09 Z

Hi,

Actually i have installed SQL Server 2008 R2 on XP Pro SP3 it works fine after instaling first time but when i restarted my pc SQL Server Service didn't restarted and so i tried to restart that manuly by clicking start in Sql Server Configuration Manager at that it gave this error "The request failed or the service did not respond in a timely fashion. Consult the event log or other application error logs for details."

I tried to repair it again but it doesent resolved .So i Uninstalled it SQL Server 2008 R2 and all its shared tools and installed it again but nothing changed it again gave me the same problem as in 1st instalation after restating maching.

Any idea! Where i am going wrong?.

i got this thing in Event log of Application

Event 1

"FCB::RemoveAlternateStreamsByHandle(BackupSeek): Operating system error (null) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\transport.mdf'. Diagnose and correct the operating system error, and retry the operation."

Event 2

"FCB::RemoveAlternateStreamsByHandle(BackupSeek): Operating system error (null) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf'. Diagnose and correct the operating system error, and retry the operation."

Event 3

"FCB::RemoveAlternateStreamsByHandle(BackupSeek): Operating system error (null) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf'. Diagnose and correct the operating system error, and retry the operation."

Event 4

"FCB::RemoveAlternateStreamsByHandle(BackupSeek): Operating system error (null) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB.mdf'. Diagnose and correct the operating system error, and retry the operation."

Event 5

FCB::RemoveAlternateStreamsByHandle(BackupSeek): Operating system error (null) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ReportServer.mdf'. Diagnose and correct the operating system error, and retry the operation.

Event 6

FCB::RemoveAlternateStreamsByHandle(BackupSeek): Operating system error (null) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf'. Diagnose and correct the operating system error, and retry the operation.

Thank you So much for replying

SQL Server Maintenance Plan Wizard nor SQL Server Agent visible in SQL Server 2008R installation?

Mon, 07 May 2012 16:01:47 Z

Hello Community

SQL Server 2008R is supposed to have an "Maintenance Plan Wizard" and of course
a SQL Server Agent.

The problem is that I am trying to access the Maintenance Plan Wizard and/or the
SQL Server Agent but none of those folders are visible in my SQL Server 2008R installation.

How can I get the Maintenance Plan Wizard and SQL Server Agent to be resident in my
SQL Server 2008R installation?

Thank you
Shabeaut

The specified Filestream share name is not a valid windows share name. Verify that the share name is a valid windows share name.

Thu, 26 Apr 2012 18:52:06 Z

Hi,

When I go to SQL Server Configuration manager, SQL Server Services, right click default instance name and press properties. go to filestream tab,enable file stream t-sql access.

Then When I give the network path of \\SERVERNAME\NAME in the windows share name under Enable Filestream for file I/O Streaming access . It gives me the below error message.

The specified Filestream share name is not a valid windows share name. Verify that the share name is a valid windows share name.

I am doing these tasks using the user account domain\user on the server .

But I can open the location \\SERVERNAME\NAME when I type this location in the windows explorer or under run command.

I am using SQL Server 2008 r2 ent edition 64 bit on windows server 2008 r2 ent edition 64 bit

Is there any way we can fix this problem.

I found the below article

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

This article states the cause as Cluster service does not accept an NT path when you create a cluster file share resource. This article is related to windows server 2003 . But over server is windows server 2008 r2

Our Share path is a cluster aware .

The domain user DOMAIN\user which i am using is a local admin on the server and has security admin and dbcreator server roles on the SQL Server.

Even when we make the user as sysadmin i am still getting the error message.

Please let me know.

Thank You,

Impact of changing MS DTC Settings

Mon, 07 May 2012 16:20:15 Z

Hi,

Is there an article that explains the impact of changing MS DTC settings on a data base server (SQL 2008) and a web server (Windows Server 2008)?

Thanks

Vasu

How to determine the amount of log space usage for session

Mon, 07 May 2012 16:56:34 Z

Is there anyway I can find out which session's transaction is taking how much space in the transaction log? If the database is in simple logging and transaction log space that cannot be cleared is over 600GB how do I know which session is holding how much of this space?

Thank you

Gokhan Varol