Another Suspect ?

Hi
My database went into suspect mode. I made some space on the drives after I
was unable to add additional log files
sp_add_log_file_recover_suspect_db nestledb_jp_coremart_v1, logfile2,
'D:\MSSQL\Data\db1_logfile2.ldf',
'100MB'
Server: Msg 9004, Level 23, State 1, Line 1
An error occurred while processing the log for database
'NestleDB_JP_CoreMart_V1'.
ALTER DATABASE nestledb_jp_coremart_v1 ADD LOG FILE(NAME = [logfile2],
FILENAME = 'D:\MSSQL\Data\db1_logfile2.ldf', SIZE = 100MB )
Connection Broken
So that didn't work so only way was to make space. So I made space on the
drive and ran the proc and restarted the lot. Nothing.
Thanks for helpHi,
If you have adequate space in hard disk Can you reset the status of the
database using sp_resetstatus system procedure and stop and start the SQL
server service and
see what happends. If the database is still marked suspect please check the
SQl server error log and post us the error.
Incase if the LDF is giving issues, you can start the database by emergency
mode. Update the status column of the suspect database in sysdatabases table
to 32768.
After that you can create a newe database and use DTS to transfer data and
other objects.
Thanks
Hari
SQL Server
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:3BAA266D-93F2-4268-B764-280409FD03E9@.microsoft.com...
> Hi
> My database went into suspect mode. I made some space on the drives after
> I
> was unable to add additional log files
> sp_add_log_file_recover_suspect_db nestledb_jp_coremart_v1, logfile2,
> 'D:\MSSQL\Data\db1_logfile2.ldf',
> '100MB'
> Server: Msg 9004, Level 23, State 1, Line 1
> An error occurred while processing the log for database
> 'NestleDB_JP_CoreMart_V1'.
> ALTER DATABASE nestledb_jp_coremart_v1 ADD LOG FILE(NAME = [logfile2],
> FILENAME = 'D:\MSSQL\Data\db1_logfile2.ldf', SIZE = 100MB )
> Connection Broken
> So that didn't work so only way was to make space. So I made space on the
> drive and ran the proc and restarted the lot. Nothing.
> Thanks for help
>|||The error log, seems there's a bit of a problem :)
I'll have to try this emergenvy startup.
Thanks for help so far
2005-04-01 16:16:24.21 server Microsoft SQL Server 2000 - 8.00.760
(Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2005-04-01 16:16:24.21 server Copyright (C) 1988-2002 Microsoft
Corporation.
2005-04-01 16:16:24.21 server All rights reserved.
2005-04-01 16:16:24.21 server Server Process ID is 1788.
2005-04-01 16:16:24.21 server Logging SQL Server messages in file
'e:\MSSQL\log\ERRORLOG'.
2005-04-01 16:16:24.22 server SQL Server is starting at priority class
'normal'(4 CPUs detected).
2005-04-01 16:16:24.30 server SQL Server configured for thread mode
processing.
2005-04-01 16:16:24.30 server Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks.
2005-04-01 16:16:24.32 server Attempting to initialize Distributed
Transaction Coordinator.
2005-04-01 16:16:26.36 spid3 Starting up database 'master'.
2005-04-01 16:16:26.49 server Using 'SSNETLIB.DLL' version '8.0.760'.
2005-04-01 16:16:26.49 spid5 Starting up database 'model'.
2005-04-01 16:16:26.49 spid3 Server name is 'DEVINCI'.
2005-04-01 16:16:26.49 spid8 Starting up database 'msdb'.
2005-04-01 16:16:26.49 spid9 Starting up database 'pubs'.
2005-04-01 16:16:26.49 spid10 Starting up database 'Northwind'.
2005-04-01 16:16:26.49 spid11 Starting up database
'Nestle_JP_WorkspaceDB_V1'.
2005-04-01 16:16:26.49 spid13 Starting up database
'NestleDB_JP_WebMart_V1'.
2005-04-01 16:16:26.49 spid15 Starting up database
'NestleDB_JP_Update_V1_TN'.
2005-04-01 16:16:26.49 spid14 Starting up database
'NestleDB_JP_WebMart_V1_TN_V1'.
2005-04-01 16:16:26.49 spid16 Starting up database 'NestleDB_JP_Update_V1
'.
2005-04-01 16:16:26.49 spid17 Starting up database
'NestleDB_JP_CoreMart_V1'.
2005-04-01 16:16:26.49 spid12 Starting up database
'Nestle_JP_WorkspaceDB_V2'.
2005-04-01 16:16:26.55 spid12 Analysis of database
'Nestle_JP_WorkspaceDB_V2' (8) is 100% complete (approximately 0 more second
s)
2005-04-01 16:16:26.60 spid13 Analysis of database
'NestleDB_JP_WebMart_V1' (10) is 100% complete (approximately 0 more seconds
)
2005-04-01 16:16:26.60 spid16 Analysis of database
'NestleDB_JP_Update_V1' (17) is 100% complete (approximately 0 more seconds)
2005-04-01 16:16:26.61 spid5 Clearing tempdb database.
2005-04-01 16:16:26.63 spid11 Analysis of database
'Nestle_JP_WorkspaceDB_V1' (7) is 100% complete (approximately 0 more second
s)
2005-04-01 16:16:26.72 server SQL server listening on 192.33.20.218: 1433
.
2005-04-01 16:16:26.72 server SQL server listening on 192.168.234.235:
1433.
2005-04-01 16:16:26.72 server SQL server listening on 127.0.0.1: 1433.
2005-04-01 16:16:26.82 server SQL server listening on TCP, Shared Memory,
Named Pipes.
2005-04-01 16:16:26.82 server SQL Server is ready for client connections
2005-04-01 16:16:27.03 spid5 Starting up database 'tempdb'.
2005-04-01 16:16:27.08 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
2005-04-01 16:16:29.47 spid15 Analysis of database
'NestleDB_JP_Update_V1_TN' (15) is 100% complete (approximately 0 more
seconds)
2005-04-01 16:16:32.13 spid17 Analysis of database
'NestleDB_JP_CoreMart_V1' (18) is 0% complete (approximately 56 more seconds
)
2005-04-01 16:16:33.46 spid17 Error: 9004, Severity: 23, State: 1
2005-04-01 16:16:33.46 spid17 An error occurred while processing the log
for database 'NestleDB_JP_CoreMart_V1'..
2005-04-01 16:16:33.47 spid17 Error: 3414, Severity: 21, State: 1
2005-04-01 16:16:33.47 spid17 Database 'NestleDB_JP_CoreMart_V1'
(database ID 18) could not recover. Contact Technical Support..
2005-04-01 16:16:33.49 spid3 Recovery complete.
2005-04-01 16:16:33.50 spid3 SQL global counter collection task is
created.
2005-04-01 16:16:34.10 spid51 Using 'xpsqlbot.dll' version '2000.80.194'
to execute extended stored procedure 'xp_qv'.
2005-04-01 16:16:36.36 spid1 Warning: unable to allocate 'min server
memory' of 3011MB.
"Hari Pra" wrote:

> Hi,
> If you have adequate space in hard disk Can you reset the status of the
> database using sp_resetstatus system procedure and stop and start the SQL
> server service and
> see what happends. If the database is still marked suspect please check th
e
> SQl server error log and post us the error.
> Incase if the LDF is giving issues, you can start the database by emergenc
y
> mode. Update the status column of the suspect database in sysdatabases tab
le
> to 32768.
> After that you can create a newe database and use DTS to transfer data and
> other objects.
>
> Thanks
> Hari
> SQL Server
> "Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
> news:3BAA266D-93F2-4268-B764-280409FD03E9@.microsoft.com...
>
>|||Mwuhahaha
I love SQL :P
So problem solved...
What was the problem ...
Some clever person set the transaction log size to a specific limit and "do
not grow automatically" thus even though I did make space it didn't help wit
h
the recovery since the file couldn't grow. I have no idea why I couldn't add
an additional log file though.
So as you said I changed it to emergency mode, fixed the automatically grow
log. Set status back to suspect and then did the restart and it recovered
correctly.
One thing that's not usefull is the fact that when you go into EM you can't
see filegroups and database setttings due to suspect mode. This is a pain
cause the moment I could see that the file didn't grow automatically I could
fix the problem.
Why do SQL disable the properties for viewing once it goes into suspect ?
How do these settings affect data ? assuming data integrity is the ONLY
reason why SQL change mode to suspect to start off with .
Thanks for help.
:)
"Mal" wrote:
> The error log, seems there's a bit of a problem :)
> I'll have to try this emergenvy startup.
> Thanks for help so far
> 2005-04-01 16:16:24.21 server Microsoft SQL Server 2000 - 8.00.760
> (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2005-04-01 16:16:24.21 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2005-04-01 16:16:24.21 server All rights reserved.
> 2005-04-01 16:16:24.21 server Server Process ID is 1788.
> 2005-04-01 16:16:24.21 server Logging SQL Server messages in file
> 'e:\MSSQL\log\ERRORLOG'.
> 2005-04-01 16:16:24.22 server SQL Server is starting at priority class
> 'normal'(4 CPUs detected).
> 2005-04-01 16:16:24.30 server SQL Server configured for thread mode
> processing.
> 2005-04-01 16:16:24.30 server Using dynamic lock allocation. [2500] Loc
k
> Blocks, [5000] Lock Owner Blocks.
> 2005-04-01 16:16:24.32 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2005-04-01 16:16:26.36 spid3 Starting up database 'master'.
> 2005-04-01 16:16:26.49 server Using 'SSNETLIB.DLL' version '8.0.760'.
> 2005-04-01 16:16:26.49 spid5 Starting up database 'model'.
> 2005-04-01 16:16:26.49 spid3 Server name is 'DEVINCI'.
> 2005-04-01 16:16:26.49 spid8 Starting up database 'msdb'.
> 2005-04-01 16:16:26.49 spid9 Starting up database 'pubs'.
> 2005-04-01 16:16:26.49 spid10 Starting up database 'Northwind'.
> 2005-04-01 16:16:26.49 spid11 Starting up database
> 'Nestle_JP_WorkspaceDB_V1'.
> 2005-04-01 16:16:26.49 spid13 Starting up database
> 'NestleDB_JP_WebMart_V1'.
> 2005-04-01 16:16:26.49 spid15 Starting up database
> 'NestleDB_JP_Update_V1_TN'.
> 2005-04-01 16:16:26.49 spid14 Starting up database
> 'NestleDB_JP_WebMart_V1_TN_V1'.
> 2005-04-01 16:16:26.49 spid16 Starting up database 'NestleDB_JP_Update_
V1'.
> 2005-04-01 16:16:26.49 spid17 Starting up database
> 'NestleDB_JP_CoreMart_V1'.
> 2005-04-01 16:16:26.49 spid12 Starting up database
> 'Nestle_JP_WorkspaceDB_V2'.
> 2005-04-01 16:16:26.55 spid12 Analysis of database
> 'Nestle_JP_WorkspaceDB_V2' (8) is 100% complete (approximately 0 more seco
nds)
> 2005-04-01 16:16:26.60 spid13 Analysis of database
> 'NestleDB_JP_WebMart_V1' (10) is 100% complete (approximately 0 more secon
ds)
> 2005-04-01 16:16:26.60 spid16 Analysis of database
> 'NestleDB_JP_Update_V1' (17) is 100% complete (approximately 0 more second
s)
> 2005-04-01 16:16:26.61 spid5 Clearing tempdb database.
> 2005-04-01 16:16:26.63 spid11 Analysis of database
> 'Nestle_JP_WorkspaceDB_V1' (7) is 100% complete (approximately 0 more seco
nds)
> 2005-04-01 16:16:26.72 server SQL server listening on 192.33.20.218: 14
33.
> 2005-04-01 16:16:26.72 server SQL server listening on 192.168.234.235:
> 1433.
> 2005-04-01 16:16:26.72 server SQL server listening on 127.0.0.1: 1433.
> 2005-04-01 16:16:26.82 server SQL server listening on TCP, Shared Memor
y,
> Named Pipes.
> 2005-04-01 16:16:26.82 server SQL Server is ready for client connection
s
> 2005-04-01 16:16:27.03 spid5 Starting up database 'tempdb'.
> 2005-04-01 16:16:27.08 spid5 Analysis of database 'tempdb' (2) is 100%
> complete (approximately 0 more seconds)
> 2005-04-01 16:16:29.47 spid15 Analysis of database
> 'NestleDB_JP_Update_V1_TN' (15) is 100% complete (approximately 0 more
> seconds)
> 2005-04-01 16:16:32.13 spid17 Analysis of database
> 'NestleDB_JP_CoreMart_V1' (18) is 0% complete (approximately 56 more secon
ds)
> 2005-04-01 16:16:33.46 spid17 Error: 9004, Severity: 23, State: 1
> 2005-04-01 16:16:33.46 spid17 An error occurred while processing the lo
g
> for database 'NestleDB_JP_CoreMart_V1'..
> 2005-04-01 16:16:33.47 spid17 Error: 3414, Severity: 21, State: 1
> 2005-04-01 16:16:33.47 spid17 Database 'NestleDB_JP_CoreMart_V1'
> (database ID 18) could not recover. Contact Technical Support..
> 2005-04-01 16:16:33.49 spid3 Recovery complete.
> 2005-04-01 16:16:33.50 spid3 SQL global counter collection task is
> created.
> 2005-04-01 16:16:34.10 spid51 Using 'xpsqlbot.dll' version '2000.80.194
'
> to execute extended stored procedure 'xp_qv'.
> 2005-04-01 16:16:36.36 spid1 Warning: unable to allocate 'min server
> memory' of 3011MB.
>
> "Hari Pra" wrote:
>

another backup question

why are 'transaction log' and 'file and file group' dimmed (not available)
on the general tab of the backup window? (right-click on database, all
tasks, backup database)
I am backing up to a file device and need to set a schedule of 1 daily full
backup and backup of the transaction logs every 1/2 hour.
any help is appreciated.
Because the database is in simple recovery mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:ucbsl59UEHA.3420@.TK2MSFTNGP12.phx.gbl...
> why are 'transaction log' and 'file and file group' dimmed (not available)
> on the general tab of the backup window? (right-click on database, all
> tasks, backup database)
> I am backing up to a file device and need to set a schedule of 1 daily full
> backup and backup of the transaction logs every 1/2 hour.
> any help is appreciated.
>
|||Thank you!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWjcUB%23UEHA.1048@.tk2msftngp13.phx.gbl...
> Because the database is in simple recovery mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message
news:ucbsl59UEHA.3420@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
available)[vbcol=seagreen]
full
>

another backup question

why are 'transaction log' and 'file and file group' dimmed (not available)
on the general tab of the backup window? (right-click on database, all
tasks, backup database)
I am backing up to a file device and need to set a schedule of 1 daily full
backup and backup of the transaction logs every 1/2 hour.
any help is appreciated.Because the database is in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:ucbsl59UEHA.3420@.TK2MSFTNGP12.phx.gbl...
> why are 'transaction log' and 'file and file group' dimmed (not available)
> on the general tab of the backup window? (right-click on database, all
> tasks, backup database)
> I am backing up to a file device and need to set a schedule of 1 daily full
> backup and backup of the transaction logs every 1/2 hour.
> any help is appreciated.
>|||Thank you!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWjcUB%23UEHA.1048@.tk2msftngp13.phx.gbl...
> Because the database is in simple recovery mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message
news:ucbsl59UEHA.3420@.TK2MSFTNGP12.phx.gbl...
> > why are 'transaction log' and 'file and file group' dimmed (not
available)
> > on the general tab of the backup window? (right-click on database, all
> > tasks, backup database)
> >
> > I am backing up to a file device and need to set a schedule of 1 daily
full
> > backup and backup of the transaction logs every 1/2 hour.
> >
> > any help is appreciated.
> >
> >
>

Anonymous Logon error since SP4

Since SP4 my application and SQL log are filling with "Login failed for user
"NT Authority\Anonymous Logon". Where can I find more information
concerning this?
I've seen one or two references to this error showing up with SQL
replication but this is a single server install.
-trevor
Trevor,
Check that SQL Server is running under a domain account - this error
commonly occurs when running as LocalSystem and you try to access network
resources.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Trevor Miller" wrote:

> Since SP4 my application and SQL log are filling with "Login failed for user
> "NT Authority\Anonymous Logon". Where can I find more information
> concerning this?
> I've seen one or two references to this error showing up with SQL
> replication but this is a single server install.
> -trevor
>
>
|||SQL server indeed runs as LocalSystem but it's always been configured this
way, is the error msg a result of SP4 or a change in behavior? What network
resources would SQL be trying to access? I've tried putting the computer
account into domain admins but the error persists.
Additional advise?
-trevor
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:30AAB157-EB94-4680-B088-44689F364AF9@.microsoft.com...[vbcol=seagreen]
> Trevor,
> Check that SQL Server is running under a domain account - this error
> commonly occurs when running as LocalSystem and you try to access network
> resources.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "Trevor Miller" wrote:

Anonymous Logon error since SP4

Since SP4 my application and SQL log are filling with "Login failed for user
"NT Authority\Anonymous Logon". Where can I find more information
concerning this?
I've seen one or two references to this error showing up with SQL
replication but this is a single server install.
-trevorTrevor,
Check that SQL Server is running under a domain account - this error
commonly occurs when running as LocalSystem and you try to access network
resources.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Trevor Miller" wrote:

> Since SP4 my application and SQL log are filling with "Login failed for us
er
> "NT Authority\Anonymous Logon". Where can I find more information
> concerning this?
> I've seen one or two references to this error showing up with SQL
> replication but this is a single server install.
> -trevor
>
>|||SQL server indeed runs as LocalSystem but it's always been configured this
way, is the error msg a result of SP4 or a change in behavior? What network
resources would SQL be trying to access? I've tried putting the computer
account into domain admins but the error persists.
Additional advise?
-trevor
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:30AAB157-EB94-4680-B088-44689F364AF9@.microsoft.com...[vbcol=seagreen]
> Trevor,
> Check that SQL Server is running under a domain account - this error
> commonly occurs when running as LocalSystem and you try to access network
> resources.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "Trevor Miller" wrote:
>

Anonymous Logon error since SP4

Since SP4 my application and SQL log are filling with "Login failed for user
"NT Authority\Anonymous Logon". Where can I find more information
concerning this?
I've seen one or two references to this error showing up with SQL
replication but this is a single server install.
-trevorTrevor,
Check that SQL Server is running under a domain account - this error
commonly occurs when running as LocalSystem and you try to access network
resources.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Trevor Miller" wrote:
> Since SP4 my application and SQL log are filling with "Login failed for user
> "NT Authority\Anonymous Logon". Where can I find more information
> concerning this?
> I've seen one or two references to this error showing up with SQL
> replication but this is a single server install.
> -trevor
>
>|||SQL server indeed runs as LocalSystem but it's always been configured this
way, is the error msg a result of SP4 or a change in behavior? What network
resources would SQL be trying to access? I've tried putting the computer
account into domain admins but the error persists.
Additional advise?
-trevor
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:30AAB157-EB94-4680-B088-44689F364AF9@.microsoft.com...
> Trevor,
> Check that SQL Server is running under a domain account - this error
> commonly occurs when running as LocalSystem and you try to access network
> resources.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "Trevor Miller" wrote:
>> Since SP4 my application and SQL log are filling with "Login failed for
>> user
>> "NT Authority\Anonymous Logon". Where can I find more information
>> concerning this?
>> I've seen one or two references to this error showing up with SQL
>> replication but this is a single server install.
>> -trevor
>>

And here is part of the installation log...

I get to the point of starting the services and receive the following message after about 4 minutes of trying to start. I tried in Mixed Mode and Windows Authentication Mode. Message:

TITLE: Microsoft SQL Server 2005 Setup

The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=setup.rll&EvtID=29503&EvtType=sqlsetuplib%5cservice.cpp%40Do_sqlScript%40sqls%3a%3aService%3a%3aWaitForServiceState%40x41d

BUTTONS:

&Retry
Cancel

YOu need do have a look in the WIndows event log or in the log files of SQL Server mentioned. There is a problem starting the service, the message that you provided is just a generic one which doesn′t tell the specific error.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||I can find nothing useful in the Windows event log and I guess I do not understand where the sql server log files are.|||For me its located in here:

C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\LOG

Could be something different for you depending on your installation.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

here is the log contents of the last attempted installation and of course it timed out while trying to start the service:

2006-05-28 07:37:06.92 Server Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)

Apr 14 2006 01:12:25

Copyright (c) 1988-2005 Microsoft Corporation

Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 2)

2006-05-28 07:37:06.92 Server (c) 2005 Microsoft Corporation.

2006-05-28 07:37:06.92 Server All rights reserved.

2006-05-28 07:37:06.92 Server Server process ID is 4776.

2006-05-28 07:37:06.92 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.

2006-05-28 07:37:06.94 Server Registry startup parameters:

2006-05-28 07:37:06.94 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf

2006-05-28 07:37:06.94 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

2006-05-28 07:37:06.94 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

2006-05-28 07:37:06.94 Server Command Line Startup Parameters:

2006-05-28 07:37:06.95 Server -m SqlSetup

2006-05-28 07:37:06.95 Server SqlSetup

2006-05-28 07:37:06.95 Server -Q

2006-05-28 07:37:06.95 Server -q SQL_Latin1_General_CP1_CI_AS

2006-05-28 07:37:06.95 Server -T 4022

2006-05-28 07:37:06.95 Server -T 3659

2006-05-28 07:37:06.95 Server -T 3610

2006-05-28 07:37:06.95 Server -T 4010

2006-05-28 07:37:06.98 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2006-05-28 07:37:06.98 Server Detected 1 CPUs. This is an informational message; no user action is required.

2006-05-28 07:40:49.72 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.

2006-05-28 07:40:50.88 Server Database Mirroring Transport is disabled in the endpoint configuration.

2006-05-28 07:59:24.50 Server Service control: stop before startup

|||

Thanks.....

Property(S): DatabaseReplRes.D9BC9C10_2DCD_44D3_AACC_9C58CAF76128 = C:\Program Files\Common Files\Microsoft Shared\Database Replication\Resources\
Property(S): DatabaseReplRes1033.D9BC9C10_2DCD_44D3_AACC_9C58CAF76128 = C:\Program Files\Common Files\Microsoft Shared\Database Replication\Resources\1033\
Property(S): SqlVerComBin.8B75390F_DF2F_4C2C_92F5_9B83F3B36340 = C:\Program Files\Microsoft SQL Server\90\COM\
Property(S): Ver.8B75390F_DF2F_4C2C_92F5_9B83F3B36340 = C:\Program Files\Microsoft SQL Server\90\
Property(S): SqlVerComBin.889BED4C_E3F4_4943_956C_6FFD882F721F = C:\Program Files\Microsoft SQL Server\90\COM\
Property(S): Ver.889BED4C_E3F4_4943_956C_6FFD882F721F = C:\Program Files\Microsoft SQL Server\90\
Property(S): SqlVerComBin.CC4DBEA7_CD8B_4AAE_A10F_657CBA390BD6 = C:\Program Files\Microsoft SQL Server\90\COM\
Property(S): Ver.CC4DBEA7_CD8B_4AAE_A10F_657CBA390BD6 = C:\Program Files\Microsoft SQL Server\90\
Property(S): SqlVerComBinRes.CC4DBEA7_CD8B_4AAE_A10F_657CBA390BD6 = C:\Program Files\Microsoft SQL Server\90\COM\Resources\
Property(S): SqlVerComBinRes1033.CC4DBEA7_CD8B_4AAE_A10F_657CBA390BD6 = C:\Program Files\Microsoft SQL Server\90\COM\Resources\1033\
Property(S): CostingComplete = 1
Property(S): OutOfDiskSpace = 0
Property(S): OutOfNoRbDiskSpace = 0
Property(S): PrimaryVolumeSpaceAvailable = 0
Property(S): PrimaryVolumeSpaceRequired = 0
Property(S): PrimaryVolumeSpaceRemaining = 0
Property(S): RSVirtualDirectoryServer = ReportServer$SQLEXPRESS
Property(S): SqlActionManaged = 3
Property(S): SqlNamedInstance = 1
Property(S): SqlStateManaged = 2
Property(S): RSVirtualDirectoryManager = Reports$SQLEXPRESS
Property(S): SOURCEDIR = d:\69a81902561ca8c7a8df\Setup\
Property(S): SourcedirProduct = {2AFFFDD7-ED85-4A90-8C52-5DA9EBDC9B8F}
Property(S): InstallNgenTicks = 110000
Property(S): SQLBROWSERSCMACCOUNT = NT AUTHORITY\NetworkService
Property(S): SQLSCMACCOUNT = NT AUTHORITY\NetworkService
Property(S): DebugClsid.CC1A8C58_27D1_4D38_BF1B_C0A5CBB90616 = {84AFA01D-0112-4F29-990C-7A3EECC00498}
Property(S): ProductToBeRegistered = 1
MSI (s) (EC:1C) [08:37:27:207]: Note: 1: 1708
MSI (s) (EC:1C) [08:37:27:207]: Product: Microsoft SQL Server 2005 Express Edition -- Installation failed.

MSI (s) (EC:1C) [08:37:27:227]: Cleaning up uninstalled install packages, if any exist
MSI (s) (EC:1C) [08:37:27:227]: MainEngineThread is returning 1603
MSI (s) (EC:5C) [08:37:27:337]: Destroying RemoteAPI object.
MSI (s) (EC:D8) [08:37:27:337]: Custom Action Manager thread ending.
=== Logging stopped: 5/28/2006 8:37:27 ===
MSI (c) (78:AC) [08:37:27:377]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1
MSI (c) (78:AC) [08:37:27:377]: MainEngineThread is returning 1603
=== Verbose logging stopped: 5/28/2006 8:37:27 ===

|||What service account are you using for the service. It could be that the service account used doesn′t have the appropiate permissions to start up SQL Server. Change that (e.g. to Local System) and see if a manual startup comes up successfull.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Analyzing Error log with Trace Flag 1204 turned on

Hello, I have what I believe should be a fairly simple question. I have a
server with trace flag 1204 turned on. I have entries in this log that show
deadlock information. i'm looking for information about how to analyze the
data in this log. It contains a log of information about things such as
Grant lists, keys, owners etc. I need information to explain what I'm
looking at.
The article "Troubleshooting Deadlocks" in Books Online explains what all
these terms mean.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> Hello, I have what I believe should be a fairly simple question. I have a
> server with trace flag 1204 turned on. I have entries in this log that
> show
> deadlock information. i'm looking for information about how to analyze
> the
> data in this log. It contains a log of information about things such as
> Grant lists, keys, owners etc. I need information to explain what I'm
> looking at.
|||Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
Here is an snippet from my error log:
10/30/04 22:16...
10/30/04 22:16
10/30/04 22:16Wait-for graph
10/30/04 22:16
10/30/04 22:16Node:1
10/30/04 22:16KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16Wait List:
10/30/04 22:16Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
SPID:79 ECID:0
10/30/04 22:16SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
10/30/04 22:16Input Buf: RPC Event: app_ProductUnit_RetrieveProductUnitData;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
Ec0x73377558) Value:0x5b0
10/30/04 22:16
10/30/04 22:16Node:2
10/30/04 22:16KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16Grant List 0::
10/30/04 22:16Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:77 ECID:0
10/30/04 22:16SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
10/30/04 22:16Input Buf: RPC Event: app_ProductUnitAssoc_RetrieveTargetData;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec0x5182B558) Value:0x23a
10/30/04 22:16
10/30/04 22:16Node:3
10/30/04 22:16KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X Flags:
0x0
10/30/04 22:16Grant List 1::
10/30/04 22:16Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:71 ECID:0
10/30/04 22:16SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
10/30/04 22:16Input Buf: RPC Event: sp_executesql;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
Ec0x51DF7558) Value:0x3b4
10/30/04 22:16Victim Resource Owner:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec0x5182B558) Value:0x23a
I do not see enough information in BOL around what the information on the
lines that start with KEY represent. Are they locks currently held or locks
requested? Also I do not understand what information is included on the line
starting with ResType (i.e. what are ResType and Stype) I'm finding it very
challenging to look at this log and deduce the sequence of the calls and
locks that led to my deadlock.
If you have any further advice or links I'd appreciate it.
Mike
"Kalen Delaney" wrote:

> The article "Troubleshooting Deadlocks" in Books Online explains what all
> these terms mean.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
>
>
|||Hi Mike, and thanks, :-)
If the truth be known, I usually don't use the traceflag output for the real
troubleshooting. If I am trying to track down deadlocks, I hae this flag
enabled, and I also have a trace running to capture deadlock events. I use
this traceflag output only to get the spids and the time, and then I can
find what I need in the trace output, which shows me the statements that led
up to the deadlock. Usually that's enough to figure it out.
The keys can be either the ones being waited on or the ones requested. It
depends where in the output the line occurs.
I have quite a bit of info on interpreting this output and understand lock
resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
Locking and Blocking.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...[vbcol=seagreen]
> Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> Here is an snippet from my error log:
> 10/30/04 22:16 ...
> 10/30/04 22:16
> 10/30/04 22:16 Wait-for graph
> 10/30/04 22:16
> 10/30/04 22:16 Node:1
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Wait List:
> 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> SPID:79 ECID:0
> 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnit_RetrieveProductUnitData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> Ec0x73377558) Value:0x5b0
> 10/30/04 22:16
> 10/30/04 22:16 Node:2
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 0::
> 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:77 ECID:0
> 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnitAssoc_RetrieveTargetData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec0x5182B558) Value:0x23a
> 10/30/04 22:16
> 10/30/04 22:16 Node:3
> 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 1::
> 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:71 ECID:0
> 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> Ec0x51DF7558) Value:0x3b4
> 10/30/04 22:16 Victim Resource Owner:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec0x5182B558) Value:0x23a
> I do not see enough information in BOL around what the information on the
> lines that start with KEY represent. Are they locks currently held or
> locks
> requested? Also I do not understand what information is included on the
> line
> starting with ResType (i.e. what are ResType and Stype) I'm finding it
> very
> challenging to look at this log and deduce the sequence of the calls and
> locks that led to my deadlock.
> If you have any further advice or links I'd appreciate it.
> Mike
>
> "Kalen Delaney" wrote:
|||Hi Kalen,
Ok, sounds good. I have read the appropriate section out of Inside SQL
Server 2000. One last (hopefully) follow up question for you. Do you have a
sample profiler template that you commonly use to assist in troubleshooting
deadlocks? My dilemna is this...is there a way to show the queries leading
up to a deadlock only from the spids involved in the deadlock? I don't want
to capture all database SQL activity because this becomes very large very
quick.
"Kalen Delaney" wrote:

> Hi Mike, and thanks, :-)
> If the truth be known, I usually don't use the traceflag output for the real
> troubleshooting. If I am trying to track down deadlocks, I hae this flag
> enabled, and I also have a trace running to capture deadlock events. I use
> this traceflag output only to get the spids and the time, and then I can
> find what I need in the trace output, which shows me the statements that led
> up to the deadlock. Usually that's enough to figure it out.
> The keys can be either the ones being waited on or the ones requested. It
> depends where in the output the line occurs.
> I have quite a bit of info on interpreting this output and understand lock
> resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
> Locking and Blocking.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
>
>

Analyzing Error log with Trace Flag 1204 turned on

Hello, I have what I believe should be a fairly simple question. I have a
server with trace flag 1204 turned on. I have entries in this log that show
deadlock information. i'm looking for information about how to analyze the
data in this log. It contains a log of information about things such as
Grant lists, keys, owners etc. I need information to explain what I'm
looking at.The article "Troubleshooting Deadlocks" in Books Online explains what all
these terms mean.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> Hello, I have what I believe should be a fairly simple question. I have a
> server with trace flag 1204 turned on. I have entries in this log that
> show
> deadlock information. i'm looking for information about how to analyze
> the
> data in this log. It contains a log of information about things such as
> Grant lists, keys, owners etc. I need information to explain what I'm
> looking at.|||Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
Here is an snippet from my error log:
10/30/04 22:16 ...
10/30/04 22:16
10/30/04 22:16 Wait-for graph
10/30/04 22:16
10/30/04 22:16 Node:1
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Wait List:
10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
SPID:79 ECID:0
10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnit_RetrieveProductUnitData;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
Ec:(0x73377558) Value:0x5b0
10/30/04 22:16
10/30/04 22:16 Node:2
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 0::
10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:77 ECID:0
10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnitAssoc_RetrieveTargetData;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec:(0x5182B558) Value:0x23a
10/30/04 22:16
10/30/04 22:16 Node:3
10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 1::
10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:71 ECID:0
10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
Ec:(0x51DF7558) Value:0x3b4
10/30/04 22:16 Victim Resource Owner:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec:(0x5182B558) Value:0x23a
I do not see enough information in BOL around what the information on the
lines that start with KEY represent. Are they locks currently held or locks
requested? Also I do not understand what information is included on the line
starting with ResType (i.e. what are ResType and Stype) I'm finding it very
challenging to look at this log and deduce the sequence of the calls and
locks that led to my deadlock.
If you have any further advice or links I'd appreciate it.
Mike
"Kalen Delaney" wrote:
> The article "Troubleshooting Deadlocks" in Books Online explains what all
> these terms mean.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> >
> > Hello, I have what I believe should be a fairly simple question. I have a
> > server with trace flag 1204 turned on. I have entries in this log that
> > show
> > deadlock information. i'm looking for information about how to analyze
> > the
> > data in this log. It contains a log of information about things such as
> > Grant lists, keys, owners etc. I need information to explain what I'm
> > looking at.
>
>|||Hi Mike, and thanks, :-)
If the truth be known, I usually don't use the traceflag output for the real
troubleshooting. If I am trying to track down deadlocks, I hae this flag
enabled, and I also have a trace running to capture deadlock events. I use
this traceflag output only to get the spids and the time, and then I can
find what I need in the trace output, which shows me the statements that led
up to the deadlock. Usually that's enough to figure it out.
The keys can be either the ones being waited on or the ones requested. It
depends where in the output the line occurs.
I have quite a bit of info on interpreting this output and understand lock
resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
Locking and Blocking.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
> Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> Here is an snippet from my error log:
> 10/30/04 22:16 ...
> 10/30/04 22:16
> 10/30/04 22:16 Wait-for graph
> 10/30/04 22:16
> 10/30/04 22:16 Node:1
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Wait List:
> 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> SPID:79 ECID:0
> 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnit_RetrieveProductUnitData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> Ec:(0x73377558) Value:0x5b0
> 10/30/04 22:16
> 10/30/04 22:16 Node:2
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 0::
> 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:77 ECID:0
> 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnitAssoc_RetrieveTargetData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec:(0x5182B558) Value:0x23a
> 10/30/04 22:16
> 10/30/04 22:16 Node:3
> 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 1::
> 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:71 ECID:0
> 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> Ec:(0x51DF7558) Value:0x3b4
> 10/30/04 22:16 Victim Resource Owner:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec:(0x5182B558) Value:0x23a
> I do not see enough information in BOL around what the information on the
> lines that start with KEY represent. Are they locks currently held or
> locks
> requested? Also I do not understand what information is included on the
> line
> starting with ResType (i.e. what are ResType and Stype) I'm finding it
> very
> challenging to look at this log and deduce the sequence of the calls and
> locks that led to my deadlock.
> If you have any further advice or links I'd appreciate it.
> Mike
>
> "Kalen Delaney" wrote:
>> The article "Troubleshooting Deadlocks" in Books Online explains what all
>> these terms mean.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in
>> message
>> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
>> >
>> > Hello, I have what I believe should be a fairly simple question. I
>> > have a
>> > server with trace flag 1204 turned on. I have entries in this log that
>> > show
>> > deadlock information. i'm looking for information about how to analyze
>> > the
>> > data in this log. It contains a log of information about things such
>> > as
>> > Grant lists, keys, owners etc. I need information to explain what I'm
>> > looking at.
>>|||Hi Kalen,
Ok, sounds good. I have read the appropriate section out of Inside SQL
Server 2000. One last (hopefully) follow up question for you. Do you have a
sample profiler template that you commonly use to assist in troubleshooting
deadlocks? My dilemna is this...is there a way to show the queries leading
up to a deadlock only from the spids involved in the deadlock? I don't want
to capture all database SQL activity because this becomes very large very
quick.
"Kalen Delaney" wrote:
> Hi Mike, and thanks, :-)
> If the truth be known, I usually don't use the traceflag output for the real
> troubleshooting. If I am trying to track down deadlocks, I hae this flag
> enabled, and I also have a trace running to capture deadlock events. I use
> this traceflag output only to get the spids and the time, and then I can
> find what I need in the trace output, which shows me the statements that led
> up to the deadlock. Usually that's enough to figure it out.
> The keys can be either the ones being waited on or the ones requested. It
> depends where in the output the line occurs.
> I have quite a bit of info on interpreting this output and understand lock
> resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
> Locking and Blocking.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
> >
> > Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> > Here is an snippet from my error log:
> >
> > 10/30/04 22:16 ...
> > 10/30/04 22:16
> > 10/30/04 22:16 Wait-for graph
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:1
> > 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Wait List:
> > 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> > SPID:79 ECID:0
> > 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> > 10/30/04 22:16 Input Buf: RPC Event:
> > app_ProductUnit_RetrieveProductUnitData;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> > Ec:(0x73377558) Value:0x5b0
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:2
> > 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Grant List 0::
> > 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> > SPID:77 ECID:0
> > 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> > 10/30/04 22:16 Input Buf: RPC Event:
> > app_ProductUnitAssoc_RetrieveTargetData;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> > Ec:(0x5182B558) Value:0x23a
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:3
> > 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Grant List 1::
> > 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> > SPID:71 ECID:0
> > 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> > 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> > Ec:(0x51DF7558) Value:0x3b4
> > 10/30/04 22:16 Victim Resource Owner:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> > Ec:(0x5182B558) Value:0x23a
> >
> > I do not see enough information in BOL around what the information on the
> > lines that start with KEY represent. Are they locks currently held or
> > locks
> > requested? Also I do not understand what information is included on the
> > line
> > starting with ResType (i.e. what are ResType and Stype) I'm finding it
> > very
> > challenging to look at this log and deduce the sequence of the calls and
> > locks that led to my deadlock.
> >
> > If you have any further advice or links I'd appreciate it.
> >
> > Mike
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> The article "Troubleshooting Deadlocks" in Books Online explains what all
> >> these terms mean.
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in
> >> message
> >> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> >> >
> >> > Hello, I have what I believe should be a fairly simple question. I
> >> > have a
> >> > server with trace flag 1204 turned on. I have entries in this log that
> >> > show
> >> > deadlock information. i'm looking for information about how to analyze
> >> > the
> >> > data in this log. It contains a log of information about things such
> >> > as
> >> > Grant lists, keys, owners etc. I need information to explain what I'm
> >> > looking at.
> >>
> >>
> >>
>
>

Analyzing Error log with Trace Flag 1204 turned on

Hello, I have what I believe should be a fairly simple question. I have a
server with trace flag 1204 turned on. I have entries in this log that show
deadlock information. i'm looking for information about how to analyze the
data in this log. It contains a log of information about things such as
Grant lists, keys, owners etc. I need information to explain what I'm
looking at.The article "Troubleshooting Deadlocks" in Books Online explains what all
these terms mean.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> Hello, I have what I believe should be a fairly simple question. I have a
> server with trace flag 1204 turned on. I have entries in this log that
> show
> deadlock information. i'm looking for information about how to analyze
> the
> data in this log. It contains a log of information about things such as
> Grant lists, keys, owners etc. I need information to explain what I'm
> looking at.|||Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
Here is an snippet from my error log:
10/30/04 22:16 ...
10/30/04 22:16
10/30/04 22:16 Wait-for graph
10/30/04 22:16
10/30/04 22:16 Node:1
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Wait List:
10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
SPID:79 ECID:0
10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnit_RetrieveProductUnitData
;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
Ec0x73377558) Value:0x5b0
10/30/04 22:16
10/30/04 22:16 Node:2
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 0::
10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:77 ECID:0
10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnitAssoc_RetrieveTargetData
;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec0x5182B558) Value:0x23a
10/30/04 22:16
10/30/04 22:16 Node:3
10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 1::
10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:71 ECID:0
10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
Ec0x51DF7558) Value:0x3b4
10/30/04 22:16 Victim Resource Owner:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec0x5182B558) Value:0x23a
I do not see enough information in BOL around what the information on the
lines that start with KEY represent. Are they locks currently held or locks
requested? Also I do not understand what information is included on the lin
e
starting with ResType (i.e. what are ResType and Stype) I'm finding it very
challenging to look at this log and deduce the sequence of the calls and
locks that led to my deadlock.
If you have any further advice or links I'd appreciate it.
Mike
"Kalen Delaney" wrote:

> The article "Troubleshooting Deadlocks" in Books Online explains what all
> these terms mean.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in messa
ge
> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
>
>|||Hi Mike, and thanks, :-)
If the truth be known, I usually don't use the traceflag output for the real
troubleshooting. If I am trying to track down deadlocks, I hae this flag
enabled, and I also have a trace running to capture deadlock events. I use
this traceflag output only to get the spids and the time, and then I can
find what I need in the trace output, which shows me the statements that led
up to the deadlock. Usually that's enough to figure it out.
The keys can be either the ones being waited on or the ones requested. It
depends where in the output the line occurs.
I have quite a bit of info on interpreting this output and understand lock
resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
Locking and Blocking.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...[vbcol=seagreen]
> Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> Here is an snippet from my error log:
> 10/30/04 22:16 ...
> 10/30/04 22:16
> 10/30/04 22:16 Wait-for graph
> 10/30/04 22:16
> 10/30/04 22:16 Node:1
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Wait List:
> 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> SPID:79 ECID:0
> 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnit_RetrieveProductUnitData;
1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> Ec0x73377558) Value:0x5b0
> 10/30/04 22:16
> 10/30/04 22:16 Node:2
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 0::
> 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:77 ECID:0
> 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnitAssoc_RetrieveTargetData;
1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec0x5182B558) Value:0x23a
> 10/30/04 22:16
> 10/30/04 22:16 Node:3
> 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 1::
> 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:71 ECID:0
> 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> Ec0x51DF7558) Value:0x3b4
> 10/30/04 22:16 Victim Resource Owner:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec0x5182B558) Value:0x23a
> I do not see enough information in BOL around what the information on the
> lines that start with KEY represent. Are they locks currently held or
> locks
> requested? Also I do not understand what information is included on the
> line
> starting with ResType (i.e. what are ResType and Stype) I'm finding it
> very
> challenging to look at this log and deduce the sequence of the calls and
> locks that led to my deadlock.
> If you have any further advice or links I'd appreciate it.
> Mike
>
> "Kalen Delaney" wrote:
>|||Hi Kalen,
Ok, sounds good. I have read the appropriate section out of Inside SQL
Server 2000. One last (hopefully) follow up question for you. Do you have
a
sample profiler template that you commonly use to assist in troubleshooting
deadlocks? My dilemna is this...is there a way to show the queries leading
up to a deadlock only from the spids involved in the deadlock? I don't want
to capture all database SQL activity because this becomes very large very
quick.
"Kalen Delaney" wrote:

> Hi Mike, and thanks, :-)
> If the truth be known, I usually don't use the traceflag output for the re
al
> troubleshooting. If I am trying to track down deadlocks, I hae this flag
> enabled, and I also have a trace running to capture deadlock events. I use
> this traceflag output only to get the spids and the time, and then I can
> find what I need in the trace output, which shows me the statements that l
ed
> up to the deadlock. Usually that's enough to figure it out.
> The keys can be either the ones being waited on or the ones requested. It
> depends where in the output the line occurs.
> I have quite a bit of info on interpreting this output and understand lock
> resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
> Locking and Blocking.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in messa
ge
> news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
>
>

Analyze SqlServer 7.0 trace output

In one of our SqlServer 7.0 databases we get the following
error message:
Database AllWork: Transaction Log Backup...
Destination:
[e:\SQL7Data\BACKUP\AllWork\AllWork_tlog_200403290 853.TRN]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4213:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot
allow BACKUP LOG because file 'AllWork' has been subjected
to nonlogged updates and cannot be rolled forward. Perform
a full database, or differential database, backup.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or
restore operation terminating abnormally.
After a full database backup, everything is fine for some
time, but then the problem reoccurs.
I managed to capture a full trace using Profiler. At 7:40
the transaction log backup succeeded without errors and at
7:45 the transaction log backup failed with the
errormessage shown above. Allthough the trace is only 5
minutes, it still is over 1.100.000 records. I already
looked for SELECT INTO, WRITETEXT and UPDATETEXT
statements but I can't find any.
How can I determine what causes this error message?
What should I look for in the trace?
Any help would be appreciated,
TIA,
Gerrit van Ham
Gerrit,
Is 'select into/bulk copy' switched on? If so, there may be a bcp fast load taking place. I can't remember if this gets shown in the profiler trace in SQL7 or not - I don't think it does, but you may need to check this. It sounds like a bcp operation is t
aking place with select into/bulk copy switched on.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk

Analysis Services Installation Problem

Did you take a look at the setup log file 'olapstp.log'
under WINNT directory ?

>--Original Message--
>I have installed SQL Server and added SP-3. When I try
to add Analysis
>services the installation fails after 90% of the file
extraction is
>accomplished. I have tried to shutdown all services that
may be using ODBC
>and the -z switch from a command line. This server is
Windows 2000 advanced
>server with IIS loaded
>.
>
There is no olapstp.log in the directory apparently the setup did not make it
that far.
"Mark" wrote:

> Did you take a look at the setup log file 'olapstp.log'
> under WINNT directory ?
>
> to add Analysis
> extraction is
> may be using ODBC
> Windows 2000 advanced
>

Analysis Services Installation Problem

Did you take a look at the setup log file 'olapstp.log'
under WINNT directory '

>--Original Message--
>I have installed SQL Server and added SP-3. When I try
to add Analysis
>services the installation fails after 90% of the file
extraction is
>accomplished. I have tried to shutdown all services that
may be using ODBC
>and the -z switch from a command line. This server is
Windows 2000 advanced
>server with IIS loaded
>.
>There is no olapstp.log in the directory apparently the setup did not make i
t
that far.
"Mark" wrote:

> Did you take a look at the setup log file 'olapstp.log'
> under WINNT directory '
>
>
> to add Analysis
> extraction is
> may be using ODBC
> Windows 2000 advanced
>

Analysis Services 2005 Processing Log

Analysis Services 2000 had a "processing log" that all processing activities could be logged to. Is there a similar capability in Analysis Services 2005? How do I enable it?

Thanks!

Keith Spitz, Software Engineer, Wall Street On Demand

I am not aware of an identical capacity in SSAS 2005, but there are a couple of options that would get you similar information.

If you want to capture errors there is an error log setting that you can use, but I know the processing log in AS 2000 used to capture a lot more information than just errors.|||You can define ErrorConfiguration object, which contains the path to the processing log or define ErrorConfiguration element if you are processing from DDL script. If you are processing from UI (Management Studio or BI Dev Studio), on processing dialog click on Change Settings and go to the Dimension(Partition) key error tab. Or in SSMS right click on the object, dimension for example, and choose properties->Select a page: Error Configuration.|||

Where is the error log setting described above and in the MSDN docs?

Error Log

ErrorLog\ ErrorLogFileName

I don't see this in my Analysis Services Properties with the Advanced box checked. I'm hoping to get processing errors logged to a consistent location. I'm on SP2.

Thanks, David

|||

Where is the error log setting described above and in the MSDN docs?

Error Log

ErrorLog\ ErrorLogFileName

I don't see this in my Analysis Services Properties with the Advanced box checked. I'm hoping to get processing errors logged to a consistent location. I'm on SP2.

Thanks, David

|||

I have SP2 and I can't see this setting either, I can't remember if it was there previously. You can set this setting at a number of different levels and in the processing command itself. I don't know if setting it at the server level changes the default or if the server setting is used to seed new objects.

You can edit the server setting by editing the settings file, the properties window is basically showing you the settings from msmdsrv.ini which you can find at:

<Program Files>\Microsoft SQL Server\MSSQL.<x>\OLAP\Config

You can see these settings in there. This is just an xml file (inspite of it's .ini extension) - you should take a backup of this file if you do edit it, as if you make a mistake you may not be able to start the SSAS server.

|||

> you should take a backup of this file if you do edit it, as if you make a mistake you may not be able to start the SSAS server

Actually, you would see that AS automatically backs up the last good known version of config file in the form of msmdsrv.bak file, so if something goes wrong with .ini, it has something to fall on. But taking backups is always a good idea - one can never trust software...

|||

So what happened to all those error handling settings at server level ? Why have they gone from SQL Management Studio ? I got the same problem. Now after migrating to SP2 I see in my msdmsrv.ini that I have KeyErrors set on with fail on first error. However, my cubes don't behave that way during processing after migrating to SP2 - processing just continues after the first error, when it should not. This is a change in behaviour that seemed to happen with installation of SP2. Is anyone else experiencing this ? Any solution ?

|||

The change here appears to be in the DISCOVER_XML_METADATA command which is no longer returning these properties - not sure why.

However these properties can be overridden at the object level and again in the actual processing command. So if you always want your cube/dimension etc to process with a particular error configuration you can set this up in BI Development Studio on the object(s) in question. You should also double check the object(s) and whatever is sending the processing command to make sure that they are not overriding the error configuration.

That said the default was (and rightly so in my opinion) to stop on the first error and it's a bit disturbing that anything would change this.

You are not by any chance working in a team where someone else may have altered these settings on the object(s) in question are you? I had this happen to me once and took me ages to figure out why the cube and fact table did not reconcile.

Analysis Services 2005 Processing Log

Analysis Services 2000 had a "processing log" that all processing activities could be logged to. Is there a similar capability in Analysis Services 2005? How do I enable it?

Thanks!

Keith Spitz, Software Engineer, Wall Street On Demand

I am not aware of an identical capacity in SSAS 2005, but there are a couple of options that would get you similar information.

If you want to capture errors there is an error log setting that you can use, but I know the processing log in AS 2000 used to capture a lot more information than just errors.|||You can define ErrorConfiguration object, which contains the path to the processing log or define ErrorConfiguration element if you are processing from DDL script. If you are processing from UI (Management Studio or BI Dev Studio), on processing dialog click on Change Settings and go to the Dimension(Partition) key error tab. Or in SSMS right click on the object, dimension for example, and choose properties->Select a page: Error Configuration.|||

Where is the error log setting described above and in the MSDN docs?

Error Log

ErrorLog\ ErrorLogFileName

I don't see this in my Analysis Services Properties with the Advanced box checked. I'm hoping to get processing errors logged to a consistent location. I'm on SP2.

Thanks, David

|||

Where is the error log setting described above and in the MSDN docs?

Error Log

ErrorLog\ ErrorLogFileName

I don't see this in my Analysis Services Properties with the Advanced box checked. I'm hoping to get processing errors logged to a consistent location. I'm on SP2.

Thanks, David

|||

I have SP2 and I can't see this setting either, I can't remember if it was there previously. You can set this setting at a number of different levels and in the processing command itself. I don't know if setting it at the server level changes the default or if the server setting is used to seed new objects.

You can edit the server setting by editing the settings file, the properties window is basically showing you the settings from msmdsrv.ini which you can find at:

<Program Files>\Microsoft SQL Server\MSSQL.<x>\OLAP\Config

You can see these settings in there. This is just an xml file (inspite of it's .ini extension) - you should take a backup of this file if you do edit it, as if you make a mistake you may not be able to start the SSAS server.

|||

> you should take a backup of this file if you do edit it, as if you make a mistake you may not be able to start the SSAS server

Actually, you would see that AS automatically backs up the last good known version of config file in the form of msmdsrv.bak file, so if something goes wrong with .ini, it has something to fall on. But taking backups is always a good idea - one can never trust software...

|||

So what happened to all those error handling settings at server level ? Why have they gone from SQL Management Studio ? I got the same problem. Now after migrating to SP2 I see in my msdmsrv.ini that I have KeyErrors set on with fail on first error. However, my cubes don't behave that way during processing after migrating to SP2 - processing just continues after the first error, when it should not. This is a change in behaviour that seemed to happen with installation of SP2. Is anyone else experiencing this ? Any solution ?

|||

The change here appears to be in the DISCOVER_XML_METADATA command which is no longer returning these properties - not sure why.

However these properties can be overridden at the object level and again in the actual processing command. So if you always want your cube/dimension etc to process with a particular error configuration you can set this up in BI Development Studio on the object(s) in question. You should also double check the object(s) and whatever is sending the processing command to make sure that they are not overriding the error configuration.

That said the default was (and rightly so in my opinion) to stop on the first error and it's a bit disturbing that anything would change this.

You are not by any chance working in a team where someone else may have altered these settings on the object(s) in question are you? I had this happen to me once and took me ages to figure out why the cube and fact table did not reconcile.

Analysis Services 2005 Processing Log

Analysis Services 2000 had a "processing log" that all processing activities could be logged to. Is there a similar capability in Analysis Services 2005? How do I enable it?

Thanks!

Keith Spitz, Software Engineer, Wall Street On Demand

I am not aware of an identical capacity in SSAS 2005, but there are a couple of options that would get you similar information.

If you want to capture errors there is an error log setting that you can use, but I know the processing log in AS 2000 used to capture a lot more information than just errors.|||You can define ErrorConfiguration object, which contains the path to the processing log or define ErrorConfiguration element if you are processing from DDL script. If you are processing from UI (Management Studio or BI Dev Studio), on processing dialog click on Change Settings and go to the Dimension(Partition) key error tab. Or in SSMS right click on the object, dimension for example, and choose properties->Select a page: Error Configuration.|||

Where is the error log setting described above and in the MSDN docs?

Error Log

ErrorLog\ ErrorLogFileName

I don't see this in my Analysis Services Properties with the Advanced box checked. I'm hoping to get processing errors logged to a consistent location. I'm on SP2.

Thanks, David

|||

Where is the error log setting described above and in the MSDN docs?

Error Log

ErrorLog\ ErrorLogFileName

I don't see this in my Analysis Services Properties with the Advanced box checked. I'm hoping to get processing errors logged to a consistent location. I'm on SP2.

Thanks, David

|||

I have SP2 and I can't see this setting either, I can't remember if it was there previously. You can set this setting at a number of different levels and in the processing command itself. I don't know if setting it at the server level changes the default or if the server setting is used to seed new objects.

You can edit the server setting by editing the settings file, the properties window is basically showing you the settings from msmdsrv.ini which you can find at:

<Program Files>\Microsoft SQL Server\MSSQL.<x>\OLAP\Config

You can see these settings in there. This is just an xml file (inspite of it's .ini extension) - you should take a backup of this file if you do edit it, as if you make a mistake you may not be able to start the SSAS server.

|||

> you should take a backup of this file if you do edit it, as if you make a mistake you may not be able to start the SSAS server

Actually, you would see that AS automatically backs up the last good known version of config file in the form of msmdsrv.bak file, so if something goes wrong with .ini, it has something to fall on. But taking backups is always a good idea - one can never trust software...

|||

So what happened to all those error handling settings at server level ? Why have they gone from SQL Management Studio ? I got the same problem. Now after migrating to SP2 I see in my msdmsrv.ini that I have KeyErrors set on with fail on first error. However, my cubes don't behave that way during processing after migrating to SP2 - processing just continues after the first error, when it should not. This is a change in behaviour that seemed to happen with installation of SP2. Is anyone else experiencing this ? Any solution ?

|||

The change here appears to be in the DISCOVER_XML_METADATA command which is no longer returning these properties - not sure why.

However these properties can be overridden at the object level and again in the actual processing command. So if you always want your cube/dimension etc to process with a particular error configuration you can set this up in BI Development Studio on the object(s) in question. You should also double check the object(s) and whatever is sending the processing command to make sure that they are not overriding the error configuration.

That said the default was (and rightly so in my opinion) to stop on the first error and it's a bit disturbing that anything would change this.

You are not by any chance working in a team where someone else may have altered these settings on the object(s) in question are you? I had this happen to me once and took me ages to figure out why the cube and fact table did not reconcile.