Intermittent SQL Server shutdown

I am running a scheduled job through SQL Server Agent every morning, Tuesday
to Saturday, at 3:00 am.
It runs perfectly for ws, sometimes months. Then, one morning, something
happens to cause SQL Server
and SQL Server Agent to stop running. *Nothing* has changed in the scheduled
job.
When this situation occurs, it always occurs while running the scheduled job
(i.e., the machine is not idle).
The scheduled job executes my stored procedure si_ImportMaster.
Furthermore, si_ImportMaster simply does some error checking and then execut
es a series of 10 stored procedures,
and when the error happens, it is always when si_ImportMaster is executing t
he same stored procedure (of those 10).
When I come in to work and realize what has happened, I re-start SQL Server
and SQL Server Agent
and execute the rest of the stored procedures in the process (including the
one it stopped on).
That "re-run" process completes without a hitch.
I would be happy to post the contents of the stored procedure in question, b
ut my thinking was that
if it runs correctly for ws or months, it can't be the fault of the SQL c
ode. Can it . . . ?
I have been given the job of troubleshooting this. I am much more familiar w
ith SQL Server programming
than the DBA side of things, so any and all assistance is appreciated.
We're running SQL 7.0 SP2.
If you need me to post anything else, let me know.
Thanks in advance -- Carl
Following is some error log output. I was able to find 4 relevant log files:
...\LOG\ERRORLOG.1 -- excerpted below
...\LOG\SQLAGENT.1 -- excerpted below
...\LOG\SQL00031.dmp -- 48K of mostly technical stuff (core dump info, etc
.)
...\LOG\SQL00032.dmp -- same as above.
Anyway,
Here's some output from ...\LOG\ERRORLOG.1
2005-03-13 19:05:06.75 kernel Microsoft SQL Server 7.00 - 7.00.842 (Intel
X86)
Mar 2 2000 06:49:37
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
----
--
2005-03-15 04:18:04.09 spid9 Process 9 unlocking unowned resource: KEY: 7
:1451737566:5 (57028e42a833)
2005-03-15 04:18:04.12 spid9 Process 9 unlocking unowned resource: KEY: 7
:1451737566:5 (57028e42a833)
2005-03-15 04:18:04.28 spid9 Using 'sqlimage.dll' version '4.0.5'
Dump thread - spid = 9, PSS = 0x354df154, EC = 0x23a40084
Stack Dump being sent to E:\log\SQL00031.dmp
****************************************
************************************
***
*
* BEGIN STACK DUMP:
* 03/15/05 04:18:17 spid 9
*
* Input Buffer 40 bytes -
* E X E C s i _ I m p o r t M a s t e r
*
****************************************
************************************
***
----
--
Short Stack Dump
0x77f94956 Module(ntdll+14956) (ZwGetContextThread+b)
0x00784f83 Module(sqlservr+384f83) (utassert_fail+19f)
0x005b572c Module(sqlservr+1b572c) (ExecutionContext::Cleanup+9d)
0x004eb421 Module(sqlservr+eb421) (ExecutionContext::Purge+45)
0x004eb182 Module(sqlservr+eb182) (stopsubprocess+e5)
0x004e9d4e Module(sqlservr+e9d4e) (subproc_main+174)
0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
0x7800c9eb Module(MSVCRT+c9eb) (beginthread+ce)
0x7c57438b Module(KERNEL32+438b) (TlsSetValue+f0)
2005-03-15 04:18:17.81 kernel SQL Server Assertion: File: <proc.c>, line=1
927
Failed Assertion = 'm_activeSdesList.Head () == NULL'.
2005-03-15 04:18:17.87 spid9 Using 'sqlimage.dll' version '4.0.5'
Dump thread - spid = 9, PSS = 0x354df154, EC = 0x23a40084
Stack Dump being sent to E:\log\SQL00032.dmp
****************************************
************************************
***
*
* BEGIN STACK DUMP:
* 03/15/05 04:18:22 spid 9
*
* Input Buffer 40 bytes -
* E X E C s i _ I m p o r t M a s t e r
*
****************************************
************************************
***
----
--
Short Stack Dump
0x77f94956 Module(ntdll+14956) (ZwGetContextThread+b)
0x0077b371 Module(sqlservr+37b371) (ex_raise2+35b)
0x0077b3dd Module(sqlservr+37b3dd) (ex_raise+5f)
0x00784f16 Module(sqlservr+384f16) (utassert_fail+37d)
0x005b572c Module(sqlservr+1b572c) (ExecutionContext::Cleanup+9d)
0x004eb421 Module(sqlservr+eb421) (ExecutionContext::Purge+45)
0x004eb182 Module(sqlservr+eb182) (stopsubprocess+e5)
0x004e9d4e Module(sqlservr+e9d4e) (subproc_main+174)
0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
0x7800c9eb Module(MSVCRT+c9eb) (beginthread+ce)
0x7c57438b Module(KERNEL32+438b) (TlsSetValue+f0)
2005-03-15 04:18:23.32 spid9 Error: 1203, Severity: 20, State: 1
2005-03-15 04:18:23.32 spid9 Process ID 9 attempting to unlock unowned re
source KEY: 7:1451737566:5 (57028e42a833)..
2005-03-15 04:18:23.32 spid9 Error: 1203, Severity: 20, State: 1
2005-03-15 04:18:23.32 spid9 Process ID 9 attempting to unlock unowned re
source KEY: 7:1451737566:5 (57028e42a833)..
Here's the relevant output from ...\LOG\SQLAGENT.1
3/15/2005 4:18:23 AM - ! [298] SQLServer Error: 4, Connection broken. [SQLST
ATE 08S01]
3/15/2005 4:18:23 AM - ! [298] SQLServer Error: 109, ConnectionRead (GetOver
LappedResult()). [SQLSTATE 01000]
3/15/2005 4:18:23 AM - ! [382] Logon to server '(local)' failed (ConnAttempt
CachableOp)
3/15/2005 4:18:28 AM - + [360] SQLServerAgent initiating shutdown following
MSSQLServer shutdown
3/15/2005 4:18:44 AM - + [188] Scheduler engine timed out (after 15 seconds)
waiting for 1 jobs(s) to stop
3/15/2005 4:18:44 AM - ! [359] The local host server is not running
3/15/2005 4:18:45 AM - + [098] SQLServerAgent terminated (forcefully)We had this problem too when I used to work as DBA.
The problem was with the Code that was written in the SP that was being
called in a Job. It used to crash
the SQLServer Engine (and along with it SQLAgent) and this is used to happen
randomly.
Finally we traced it to that part of the code. Fortunately, Microsoft has a
fix for this.
You dont seem to have the latest SQLServer 7 Service Pack. May be your
problem has been addressed
in one of them.
Did you the SQLServer Log file for any errors, Assertions ... etc.
Gopi
"Carl Imthurn" <nospam@.all.com> wrote in message
news:ua9EVkYKFHA.1096@.tk2msftngp13.phx.gbl...
>I am running a scheduled job through SQL Server Agent every morning,
>Tuesday to Saturday, at 3:00 am.
> It runs perfectly for ws, sometimes months. Then, one morning,
> something happens to cause SQL Server
> and SQL Server Agent to stop running. *Nothing* has changed in the
> scheduled job.
> When this situation occurs, it always occurs while running the scheduled
> job (i.e., the machine is not idle).
> The scheduled job executes my stored procedure si_ImportMaster.
> Furthermore, si_ImportMaster simply does some error checking and then
> executes a series of 10 stored procedures,
> and when the error happens, it is always when si_ImportMaster is executing
> the same stored procedure (of those 10).
> When I come in to work and realize what has happened, I re-start SQL
> Server and SQL Server Agent
> and execute the rest of the stored procedures in the process (including
> the one it stopped on).
> That "re-run" process completes without a hitch.
> I would be happy to post the contents of the stored procedure in question,
> but my thinking was that
> if it runs correctly for ws or months, it can't be the fault of the SQL
> code. Can it . . . ?
> I have been given the job of troubleshooting this. I am much more familiar
> with SQL Server programming
> than the DBA side of things, so any and all assistance is appreciated.
> We're running SQL 7.0 SP2.
> If you need me to post anything else, let me know.
> Thanks in advance -- Carl
> Following is some error log output. I was able to find 4 relevant log
> files:
> ...\LOG\ERRORLOG.1 -- excerpted below
> ...\LOG\SQLAGENT.1 -- excerpted below
> ...\LOG\SQL00031.dmp -- 48K of mostly technical stuff (core dump info,
> etc.)
> ...\LOG\SQL00032.dmp -- same as above.
> Anyway,
> Here's some output from ...\LOG\ERRORLOG.1
> 2005-03-13 19:05:06.75 kernel Microsoft SQL Server 7.00 - 7.00.842
> (Intel X86)
> Mar 2 2000 06:49:37
> Copyright (c) 1988-1998 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
> ----
--
> 2005-03-15 04:18:04.09 spid9 Process 9 unlocking unowned resource: KEY:
> 7:1451737566:5 (57028e42a833)
> 2005-03-15 04:18:04.12 spid9 Process 9 unlocking unowned resource: KEY:
> 7:1451737566:5 (57028e42a833)
> 2005-03-15 04:18:04.28 spid9 Using 'sqlimage.dll' version '4.0.5'
> Dump thread - spid = 9, PSS = 0x354df154, EC = 0x23a40084
> Stack Dump being sent to E:\log\SQL00031.dmp
> ****************************************
**********************************
*****
> *
> * BEGIN STACK DUMP:
> * 03/15/05 04:18:17 spid 9
> *
> * Input Buffer 40 bytes -
> * E X E C s i _ I m p o r t M a s t e r
> *
> ****************************************
**********************************
*****
> ----
--
> Short Stack Dump
> 0x77f94956 Module(ntdll+14956) (ZwGetContextThread+b)
> 0x00784f83 Module(sqlservr+384f83) (utassert_fail+19f)
> 0x005b572c Module(sqlservr+1b572c) (ExecutionContext::Cleanup+9d)
> 0x004eb421 Module(sqlservr+eb421) (ExecutionContext::Purge+45)
> 0x004eb182 Module(sqlservr+eb182) (stopsubprocess+e5)
> 0x004e9d4e Module(sqlservr+e9d4e) (subproc_main+174)
> 0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
> 0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
> 0x7800c9eb Module(MSVCRT+c9eb) (beginthread+ce)
> 0x7c57438b Module(KERNEL32+438b) (TlsSetValue+f0)
> 2005-03-15 04:18:17.81 kernel SQL Server Assertion: File: <proc.c>,
> line=1927
> Failed Assertion = 'm_activeSdesList.Head () == NULL'.
> 2005-03-15 04:18:17.87 spid9 Using 'sqlimage.dll' version '4.0.5'
> Dump thread - spid = 9, PSS = 0x354df154, EC = 0x23a40084
> Stack Dump being sent to E:\log\SQL00032.dmp
> ****************************************
**********************************
*****
> *
> * BEGIN STACK DUMP:
> * 03/15/05 04:18:22 spid 9
> *
> * Input Buffer 40 bytes -
> * E X E C s i _ I m p o r t M a s t e r
> *
> ****************************************
**********************************
*****
> ----
--
> Short Stack Dump
> 0x77f94956 Module(ntdll+14956) (ZwGetContextThread+b)
> 0x0077b371 Module(sqlservr+37b371) (ex_raise2+35b)
> 0x0077b3dd Module(sqlservr+37b3dd) (ex_raise+5f)
> 0x00784f16 Module(sqlservr+384f16) (utassert_fail+37d)
> 0x005b572c Module(sqlservr+1b572c) (ExecutionContext::Cleanup+9d)
> 0x004eb421 Module(sqlservr+eb421) (ExecutionContext::Purge+45)
> 0x004eb182 Module(sqlservr+eb182) (stopsubprocess+e5)
> 0x004e9d4e Module(sqlservr+e9d4e) (subproc_main+174)
> 0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
> 0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
> 0x7800c9eb Module(MSVCRT+c9eb) (beginthread+ce)
> 0x7c57438b Module(KERNEL32+438b) (TlsSetValue+f0)
> 2005-03-15 04:18:23.32 spid9 Error: 1203, Severity: 20, State: 1
> 2005-03-15 04:18:23.32 spid9 Process ID 9 attempting to unlock unowned
> resource KEY: 7:1451737566:5 (57028e42a833)..
> 2005-03-15 04:18:23.32 spid9 Error: 1203, Severity: 20, State: 1
> 2005-03-15 04:18:23.32 spid9 Process ID 9 attempting to unlock unowned
> resource KEY: 7:1451737566:5 (57028e42a833)..
>
> Here's the relevant output from ...\LOG\SQLAGENT.1
> 3/15/2005 4:18:23 AM - ! [298] SQLServer Error: 4, Connection broken.
> [SQLSTATE 08S01]
> 3/15/2005 4:18:23 AM - ! [298] SQLServer Error: 109, ConnectionRead
> (GetOverLappedResult()). [SQLSTATE 01000]
> 3/15/2005 4:18:23 AM - ! [382] Logon to server '(local)' failed
> (ConnAttemptCachableOp)
> 3/15/2005 4:18:28 AM - + [360] SQLServerAgent initiating shutdown
> following MSSQLServer shutdown
> 3/15/2005 4:18:44 AM - + [188] Scheduler engine timed out (after 15
> seconds) waiting for 1 jobs(s) to stop
> 3/15/2005 4:18:44 AM - ! [359] The local host server is not running
> 3/15/2005 4:18:45 AM - + [098] SQLServerAgent terminated (forcefully)
>|||Thanks Gopi -- I had wondered about that myself.
I will install the latest SP and see if that cures the problem.
Thanks for your time.
rgn wrote:
> We had this problem too when I used to work as DBA.
> The problem was with the Code that was written in the SP that was being
> called in a Job. It used to crash
> the SQLServer Engine (and along with it SQLAgent) and this is used to happ
en
> randomly.
> Finally we traced it to that part of the code. Fortunately, Microsoft has
a
> fix for this.
> You dont seem to have the latest SQLServer 7 Service Pack. May be your
> problem has been addressed
> in one of them.
> Did you the SQLServer Log file for any errors, Assertions ... etc.
> Gopi
> "Carl Imthurn" <nospam@.all.com> wrote in message
> news:ua9EVkYKFHA.1096@.tk2msftngp13.phx.gbl...
>
>
>sql

Intermittent issue with outer joins on subqueries

Hello, we are running into an issue which seems to be a bug in the way
MSSQL joins on subqueries. The below query is run daily by a scheduled
task and usually runs correctly. About once a week the query produces
a resultset that makes it appear that the outer join on the subquery
(alias: POINFO) was changed to an inner join. The resultset has only a
percentage of the records it should have as a result. Are there any
know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
problem happens on a Win2000 server. Thanks.
SELECT *
FROM rc.dbo.LineItems LI
INNER JOIN rc.dbo.orders O
ON O.guidPK = LI.orderguidPK
INNER JOIN decode DECODE
ON O.status = DECODE.status
INNER JOIN products P
ON LI.productPK = P.PK
/* The below join periodically seems to become an inner join
*/
LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
from purchase_orders PO
INNER JOIN purchase_orders_lines POL
ON PO.PK = POL.purchaseorderPK
INNER JOIN fulfillment_products FP
ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
INNER JOIN I18NManufacturer MAN
ON FP.manPK = MAN.PK
INNER JOIN suppliers S
ON FP.supplierPK = SM.PK
WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
ON O.order_number = POINFO.order_number AND LI.productPK = POINFO.productPK
WHERE O.order_type <> 1 and O.active=1
and O.status between 2 and 32Can you capture the plan when the bad result happens, and compare it to the
normal plan?
(In other words, does it happen frequently enough that if you tried, you
could reproduce the problem in Query Analyzer?)
"Jesse Hogan" <JesseHogan0@.gmail.com> wrote in message
news:049ac85e-1cde-4fd9-8755-0b03494302d2@.1g2000hsl.googlegroups.com...
> Hello, we are running into an issue which seems to be a bug in the way
> MSSQL joins on subqueries. The below query is run daily by a scheduled
> task and usually runs correctly. About once a week the query produces
> a resultset that makes it appear that the outer join on the subquery
> (alias: POINFO) was changed to an inner join. The resultset has only a
> percentage of the records it should have as a result. Are there any
> know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
> problem happens on a Win2000 server. Thanks.
> SELECT *
> FROM rc.dbo.LineItems LI
> INNER JOIN rc.dbo.orders O
> ON O.guidPK = LI.orderguidPK
> INNER JOIN decode DECODE
> ON O.status = DECODE.status
> INNER JOIN products P
> ON LI.productPK = P.PK
> /* The below join periodically seems to become an inner join
> */
> LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
> from purchase_orders PO
> INNER JOIN purchase_orders_lines POL
> ON PO.PK = POL.purchaseorderPK
> INNER JOIN fulfillment_products FP
> ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
> INNER JOIN I18NManufacturer MAN
> ON FP.manPK = MAN.PK
> INNER JOIN suppliers S
> ON FP.supplierPK = SM.PK
> WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
> ON O.order_number = POINFO.order_number AND LI.productPK => POINFO.productPK
> WHERE O.order_type <> 1 and O.active=1
> and O.status between 2 and 32|||I would be much more likely to think the WHERE clause is limiting the rows
'unexpectedly'. Perhaps you should set up an audit trail whereby you store
the COUNT(*) of the entire SELECT as well as the COUNT(*) for the SELECT
without the join to POINFO by itself into a table each time this process is
run (with enough identifying information added to ensure you can pinpoint
the rows for each run). This will help you see if you are affecting fewer
rows with the main query simply because of the WHERE clause or is the LOJ
magically translating into an INNER JOIN. That will give you definitive
evidence to take to Microsoft if it is a bug too (which I will definitely
admit isn't out of the question). :-)
Doing a quick check of post-SP4 hotfixes, see if this one applies
http://support.microsoft.com/kb/892310/
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jesse Hogan" <JesseHogan0@.gmail.com> wrote in message
news:049ac85e-1cde-4fd9-8755-0b03494302d2@.1g2000hsl.googlegroups.com...
> Hello, we are running into an issue which seems to be a bug in the way
> MSSQL joins on subqueries. The below query is run daily by a scheduled
> task and usually runs correctly. About once a week the query produces
> a resultset that makes it appear that the outer join on the subquery
> (alias: POINFO) was changed to an inner join. The resultset has only a
> percentage of the records it should have as a result. Are there any
> know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
> problem happens on a Win2000 server. Thanks.
> SELECT *
> FROM rc.dbo.LineItems LI
> INNER JOIN rc.dbo.orders O
> ON O.guidPK = LI.orderguidPK
> INNER JOIN decode DECODE
> ON O.status = DECODE.status
> INNER JOIN products P
> ON LI.productPK = P.PK
> /* The below join periodically seems to become an inner join
> */
> LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
> from purchase_orders PO
> INNER JOIN purchase_orders_lines POL
> ON PO.PK = POL.purchaseorderPK
> INNER JOIN fulfillment_products FP
> ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
> INNER JOIN I18NManufacturer MAN
> ON FP.manPK = MAN.PK
> INNER JOIN suppliers S
> ON FP.supplierPK = SM.PK
> WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
> ON O.order_number = POINFO.order_number AND LI.productPK => POINFO.productPK
> WHERE O.order_type <> 1 and O.active=1
> and O.status between 2 and 32

Intermittent email issue

We are having a problem with a report that is scheduled to be emailed
only works sometimes. Other time it fails with a "Reporting Services
Failure sending mail: Cannot access a closed file" error message.
The report is running off a snapshot that is generated every half hour
and a subscription is setup to email the report to some users. The
snapshot is generated even when the email fails.
It seems the problem is pointed to by userName: [DOMAIN\username]not
found in the database. This seems to be a security problem - but why
would it work sometimes? The log file is below for reference. The user
in question is in the same domain as the report service is running
under.
Environment:
Report service SP2 running on a Windows 2003 EE with SQL Server 2000
Active Director Domain server running on Windows 2003 EE
The report server service is running under a domain account that is in
the local administrator group on the local server.
Has anyone seen this problem?
Thanks
ReportingServicesService!library!2b4!09/02/2005-09:09:38:: i INFO:
Initializing EnableExecutionLogging to 'True' as specified in Server
system properties.
ReportingServicesService!session!2b4!09/02/2005-09:09:38:: i INFO:
LoadSnapshot: Item with session: tc2ixx55bnczhjifyjr2z42q, reportPath:
/Company Reports/MyReport, userName: [DOMAIN\username]not found in the
database
ReportingServicesService!chunks!2b4!09/02/2005-09:09:38:: i INFO: ###
GetReportChunk('MyReport_style', 1), chunk was not found!
this=38c10bb1-1bae-4d41-a03e-f56437fc3c86
ReportingServicesService!emailextension!2b4!09/02/2005-09:09:38:: Error
sending email. System.ObjectDisposedException: Cannot access a closed
file.
at System.IO.__Error.FileNotOpen()
at System.IO.FileStream.Read(Byte[] array, Int32 offset, Int32
count)
at
Microsoft.ReportingServices.Library.PartitionFileStream.Read(Byte[]
buffer, Int32 offset, Int32 count)
at
Microsoft.ReportingServices.Library.MemoryUntilThresholdStream.Read(Byte[]
buffer, Int32 offset, Int32 count)
at Microsoft.ReportingServices.Library.RSStream.Read(Byte[] buffer,
Int32 offset, Int32 count)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.EmbedReport(IMessage
message, RenderedOutputFile[] reportData, Notification notification,
SubscriptionData data)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMessageBody(IMessage
message, Notification notification, SubscriptionData data)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessage(Notification
notification)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification
notification)
ReportingServicesService!notification!2b4!09/02/2005-09:09:38::
Notification 2180101f-6faa-41b7-b1a0-b3217c5d630e completed. Success:
False, Status: Failure sending mail: Cannot access a closed file.,
DeliveryExtension: Report Server Email, Report: MyReport, Attempt 0
ReportingServicesService!dbpolling!2b4!09/02/2005-09:09:38::
NotificationPolling finished processing item
2180101f-6faa-41b7-b1a0-b3217c5d630ejmbackup1024@.gmail.com wrote:
> We are having a problem with a report that is scheduled to be emailed
> only works sometimes. Other time it fails with a "Reporting Services
> Failure sending mail: Cannot access a closed file" error message.
> The report is running off a snapshot that is generated every half hour
> and a subscription is setup to email the report to some users. The
> snapshot is generated even when the email fails.
> It seems the problem is pointed to by userName: [DOMAIN\username]not
> found in the database. This seems to be a security problem - but why
> would it work sometimes? The log file is below for reference. The user
> in question is in the same domain as the report service is running
> under.
> Environment:
> Report service SP2 running on a Windows 2003 EE with SQL Server 2000
> Active Director Domain server running on Windows 2003 EE
> The report server service is running under a domain account that is in
> the local administrator group on the local server.
> Has anyone seen this problem?
> Thanks
> ReportingServicesService!library!2b4!09/02/2005-09:09:38:: i INFO:
> Initializing EnableExecutionLogging to 'True' as specified in Server
> system properties.
> ReportingServicesService!session!2b4!09/02/2005-09:09:38:: i INFO:
> LoadSnapshot: Item with session: tc2ixx55bnczhjifyjr2z42q, reportPath:
> /Company Reports/MyReport, userName: [DOMAIN\username]not found in the
> database
> ReportingServicesService!chunks!2b4!09/02/2005-09:09:38:: i INFO: ###
> GetReportChunk('MyReport_style', 1), chunk was not found!
> this=38c10bb1-1bae-4d41-a03e-f56437fc3c86
> ReportingServicesService!emailextension!2b4!09/02/2005-09:09:38:: Error
> sending email. System.ObjectDisposedException: Cannot access a closed
> file.
> at System.IO.__Error.FileNotOpen()
> at System.IO.FileStream.Read(Byte[] array, Int32 offset, Int32
> count)
> at
> Microsoft.ReportingServices.Library.PartitionFileStream.Read(Byte[]
> buffer, Int32 offset, Int32 count)
> at
> Microsoft.ReportingServices.Library.MemoryUntilThresholdStream.Read(Byte[]
> buffer, Int32 offset, Int32 count)
> at Microsoft.ReportingServices.Library.RSStream.Read(Byte[] buffer,
> Int32 offset, Int32 count)
> at
> Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.EmbedReport(IMessage
> message, RenderedOutputFile[] reportData, Notification notification,
> SubscriptionData data)
> at
> Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMessageBody(IMessage
> message, Notification notification, SubscriptionData data)
> at
> Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessage(Notification
> notification)
> at
> Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification
> notification)
> ReportingServicesService!notification!2b4!09/02/2005-09:09:38::
> Notification 2180101f-6faa-41b7-b1a0-b3217c5d630e completed. Success:
> False, Status: Failure sending mail: Cannot access a closed file.,
> DeliveryExtension: Report Server Email, Report: MyReport, Attempt 0
> ReportingServicesService!dbpolling!2b4!09/02/2005-09:09:38::
> NotificationPolling finished processing item
> 2180101f-6faa-41b7-b1a0-b3217c5d630e
Experiencing a very similar issue, running SP1. I created a
parameterized report, from which 93 link reports point to. Those 93
linked reports are set to run nightly at 2:00 am by generating a
snapshot (for historical purposes). All 93 snapshots were created
within about 3 mintutes of 2:00 am. The subscription is suposed to
fire an email whenever it is updated, but only half or so are
successful. On those that are unsuccessful, I get no error message at
all.
It seems timed subscriptions work just fine. Perhaps what I will do is
send the email based only on a timed subscription and also create a
snapshot for historical purposes...I would much rather not have to do
double the processor work, as many more reports are needed for
conversion.

Integrity Check/Optimization Check

As part of a Maintenance plan we have an Integrity check scheduled for 12:00
AM every Sunday Morning and an Optimization Check 1:00 AM every Sunday. I'm
noticing errors from one of of our Web Application that connects to a SQL
database. It has a process that runs every night 12:00 AM. This process
requires access to one of the Databses on the SQL server. The error is
basically a SQL error that states cannot open db, login failed. My question
is if the integrity check or optimization check would cause this. Does it
prevent anything from connecting to the database?
The activities created from the Maintenance Plan wizard 'should' not keep
any user from connecting to the database. There may be some slowness, but
normal activity should not be impeded.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:D916B0EF-7158-4429-90B6-61CD9CB8B356@.microsoft.com...
> As part of a Maintenance plan we have an Integrity check scheduled for
> 12:00
> AM every Sunday Morning and an Optimization Check 1:00 AM every Sunday.
> I'm
> noticing errors from one of of our Web Application that connects to a SQL
> database. It has a process that runs every night 12:00 AM. This process
> requires access to one of the Databses on the SQL server. The error is
> basically a SQL error that states cannot open db, login failed. My
> question
> is if the integrity check or optimization check would cause this. Does it
> prevent anything from connecting to the database?
|||George Schneider wrote:
> As part of a Maintenance plan we have an Integrity check scheduled for 12:00
> AM every Sunday Morning and an Optimization Check 1:00 AM every Sunday. I'm
> noticing errors from one of of our Web Application that connects to a SQL
> database. It has a process that runs every night 12:00 AM. This process
> requires access to one of the Databses on the SQL server. The error is
> basically a SQL error that states cannot open db, login failed. My question
> is if the integrity check or optimization check would cause this. Does it
> prevent anything from connecting to the database?
If you've told the integrity check to attempt to fix any errors that it
finds, it will put the database into single-user/DBO only mode,
preventing other logins from connecting.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Oops, I forgot about that 'checkbox'. (Never use it and hope folks don't.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:456C78F0.4040101@.realsqlguy.com...
> George Schneider wrote:
> If you've told the integrity check to attempt to fix any errors that it
> finds, it will put the database into single-user/DBO only mode, preventing
> other logins from connecting.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Arnie Rowland wrote:
> Oops, I forgot about that 'checkbox'. (Never use it and hope folks don't.)
>
Ditto...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||If this is checked is the Databse put nto single user mode/DBO during the
scan or only if it finds an issue? If this is not checked how would I know
if there is an Integrity error? I would have to fix it myself instead of
having SQL automatically do this? How would I go about fixing integrity
issues then?
"Arnie Rowland" wrote:

> Oops, I forgot about that 'checkbox'. (Never use it and hope folks don't.)
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:456C78F0.4040101@.realsqlguy.com...
>
>
|||George Schneider wrote:
> If this is checked is the Databse put nto single user mode/DBO during the
> scan or only if it finds an issue? If this is not checked how would I know
> if there is an Integrity error? I would have to fix it myself instead of
> having SQL automatically do this? How would I go about fixing integrity
> issues then?
>
You're better off learning the various DBCC commands that are available
to you, as opposed to letting the maintenance plan auto-magically fix
things.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Is that checkbox what is causing the databse to lock and not allow any user
login?
"Tracy McKibben" wrote:

> Arnie Rowland wrote:
> Ditto...
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Integrity Check/Optimization Check

As part of a Maintenance plan we have an Integrity check scheduled for 12:00
AM every Sunday Morning and an Optimization Check 1:00 AM every Sunday. I'm
noticing errors from one of of our Web Application that connects to a SQL
database. It has a process that runs every night 12:00 AM. This process
requires access to one of the Databses on the SQL server. The error is
basically a SQL error that states cannot open db, login failed. My question
is if the integrity check or optimization check would cause this. Does it
prevent anything from connecting to the database?The activities created from the Maintenance Plan wizard 'should' not keep
any user from connecting to the database. There may be some slowness, but
normal activity should not be impeded.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:D916B0EF-7158-4429-90B6-61CD9CB8B356@.microsoft.com...
> As part of a Maintenance plan we have an Integrity check scheduled for
> 12:00
> AM every Sunday Morning and an Optimization Check 1:00 AM every Sunday.
> I'm
> noticing errors from one of of our Web Application that connects to a SQL
> database. It has a process that runs every night 12:00 AM. This process
> requires access to one of the Databses on the SQL server. The error is
> basically a SQL error that states cannot open db, login failed. My
> question
> is if the integrity check or optimization check would cause this. Does it
> prevent anything from connecting to the database?|||George Schneider wrote:
> As part of a Maintenance plan we have an Integrity check scheduled for 12:
00
> AM every Sunday Morning and an Optimization Check 1:00 AM every Sunday. I
'm
> noticing errors from one of of our Web Application that connects to a SQL
> database. It has a process that runs every night 12:00 AM. This process
> requires access to one of the Databses on the SQL server. The error is
> basically a SQL error that states cannot open db, login failed. My questi
on
> is if the integrity check or optimization check would cause this. Does it
> prevent anything from connecting to the database?
If you've told the integrity check to attempt to fix any errors that it
finds, it will put the database into single-user/DBO only mode,
preventing other logins from connecting.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Oops, I forgot about that 'checkbox'. (Never use it and hope folks don't.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:456C78F0.4040101@.realsqlguy.com...
> George Schneider wrote:
> If you've told the integrity check to attempt to fix any errors that it
> finds, it will put the database into single-user/DBO only mode, preventing
> other logins from connecting.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Arnie Rowland wrote:
> Oops, I forgot about that 'checkbox'. (Never use it and hope folks don't.)
>
Ditto...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||If this is checked is the Databse put nto single user mode/DBO during the
scan or only if it finds an issue? If this is not checked how would I know
if there is an Integrity error? I would have to fix it myself instead of
having SQL automatically do this? How would I go about fixing integrity
issues then?
"Arnie Rowland" wrote:

> Oops, I forgot about that 'checkbox'. (Never use it and hope folks don't.)
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:456C78F0.4040101@.realsqlguy.com...
>
>|||George Schneider wrote:
> If this is checked is the Databse put nto single user mode/DBO during the
> scan or only if it finds an issue? If this is not checked how would I kno
w
> if there is an Integrity error? I would have to fix it myself instead of
> having SQL automatically do this? How would I go about fixing integrity
> issues then?
>
You're better off learning the various DBCC commands that are available
to you, as opposed to letting the maintenance plan auto-magically fix
things.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||You need to read up about the DBCC CHECKDB command. This is the very basic s
tarting point. When the
option is checked, the database is put in single user mode and then DBCC CHE
CKDB command is executed
with the REPAIR_FAST option, and the db is put back in multi user mode.
What we do is to *not* check this option, and have CHECKDB report the error
it finds, the job will
fail and you will be notified of the job failure so you can act.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:B5B7C210-DB54-43C0-AAEE-0FDF48CBFF28@.microsoft.com...[vbcol=seagreen]
> If this is checked is the Databse put nto single user mode/DBO during the
> scan or only if it finds an issue? If this is not checked how would I kno
w
> if there is an Integrity error? I would have to fix it myself instead of
> having SQL automatically do this? How would I go about fixing integrity
> issues then?
> "Arnie Rowland" wrote:
>|||Is that checkbox what is causing the databse to lock and not allow any user
login?
"Tracy McKibben" wrote:

> Arnie Rowland wrote:
> Ditto...
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:FD54EDB5-3346-4B64-AF4F-6602CF87F568@.microsoft.com...[vbcol=seagreen]
> Is that checkbox what is causing the databse to lock and not allow any use
r
> login?
> "Tracy McKibben" wrote:
>

Integrity Check/Optimization Check

As part of a Maintenance plan we have an Integrity check scheduled for 12:00
AM every Sunday Morning and an Optimization Check 1:00 AM every Sunday. I'm
noticing errors from one of of our Web Application that connects to a SQL
database. It has a process that runs every night 12:00 AM. This process
requires access to one of the Databses on the SQL server. The error is
basically a SQL error that states cannot open db, login failed. My question
is if the integrity check or optimization check would cause this. Does it
prevent anything from connecting to the database?The activities created from the Maintenance Plan wizard 'should' not keep
any user from connecting to the database. There may be some slowness, but
normal activity should not be impeded.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:D916B0EF-7158-4429-90B6-61CD9CB8B356@.microsoft.com...
> As part of a Maintenance plan we have an Integrity check scheduled for
> 12:00
> AM every Sunday Morning and an Optimization Check 1:00 AM every Sunday.
> I'm
> noticing errors from one of of our Web Application that connects to a SQL
> database. It has a process that runs every night 12:00 AM. This process
> requires access to one of the Databses on the SQL server. The error is
> basically a SQL error that states cannot open db, login failed. My
> question
> is if the integrity check or optimization check would cause this. Does it
> prevent anything from connecting to the database?|||George Schneider wrote:
> As part of a Maintenance plan we have an Integrity check scheduled for 12:00
> AM every Sunday Morning and an Optimization Check 1:00 AM every Sunday. I'm
> noticing errors from one of of our Web Application that connects to a SQL
> database. It has a process that runs every night 12:00 AM. This process
> requires access to one of the Databses on the SQL server. The error is
> basically a SQL error that states cannot open db, login failed. My question
> is if the integrity check or optimization check would cause this. Does it
> prevent anything from connecting to the database?
If you've told the integrity check to attempt to fix any errors that it
finds, it will put the database into single-user/DBO only mode,
preventing other logins from connecting.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Oops, I forgot about that 'checkbox'. (Never use it and hope folks don't.)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:456C78F0.4040101@.realsqlguy.com...
> George Schneider wrote:
>> As part of a Maintenance plan we have an Integrity check scheduled for
>> 12:00 AM every Sunday Morning and an Optimization Check 1:00 AM every
>> Sunday. I'm noticing errors from one of of our Web Application that
>> connects to a SQL database. It has a process that runs every night 12:00
>> AM. This process requires access to one of the Databses on the SQL
>> server. The error is basically a SQL error that states cannot open db,
>> login failed. My question is if the integrity check or optimization
>> check would cause this. Does it prevent anything from connecting to the
>> database?
> If you've told the integrity check to attempt to fix any errors that it
> finds, it will put the database into single-user/DBO only mode, preventing
> other logins from connecting.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Arnie Rowland wrote:
> Oops, I forgot about that 'checkbox'. (Never use it and hope folks don't.)
>
Ditto...
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||If this is checked is the Databse put nto single user mode/DBO during the
scan or only if it finds an issue? If this is not checked how would I know
if there is an Integrity error? I would have to fix it myself instead of
having SQL automatically do this? How would I go about fixing integrity
issues then?
"Arnie Rowland" wrote:
> Oops, I forgot about that 'checkbox'. (Never use it and hope folks don't.)
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:456C78F0.4040101@.realsqlguy.com...
> > George Schneider wrote:
> >> As part of a Maintenance plan we have an Integrity check scheduled for
> >> 12:00 AM every Sunday Morning and an Optimization Check 1:00 AM every
> >> Sunday. I'm noticing errors from one of of our Web Application that
> >> connects to a SQL database. It has a process that runs every night 12:00
> >> AM. This process requires access to one of the Databses on the SQL
> >> server. The error is basically a SQL error that states cannot open db,
> >> login failed. My question is if the integrity check or optimization
> >> check would cause this. Does it prevent anything from connecting to the
> >> database?
> >
> > If you've told the integrity check to attempt to fix any errors that it
> > finds, it will put the database into single-user/DBO only mode, preventing
> > other logins from connecting.
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com
>
>|||George Schneider wrote:
> If this is checked is the Databse put nto single user mode/DBO during the
> scan or only if it finds an issue? If this is not checked how would I know
> if there is an Integrity error? I would have to fix it myself instead of
> having SQL automatically do this? How would I go about fixing integrity
> issues then?
>
You're better off learning the various DBCC commands that are available
to you, as opposed to letting the maintenance plan auto-magically fix
things.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||You need to read up about the DBCC CHECKDB command. This is the very basic starting point. When the
option is checked, the database is put in single user mode and then DBCC CHECKDB command is executed
with the REPAIR_FAST option, and the db is put back in multi user mode.
What we do is to *not* check this option, and have CHECKDB report the error it finds, the job will
fail and you will be notified of the job failure so you can act.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:B5B7C210-DB54-43C0-AAEE-0FDF48CBFF28@.microsoft.com...
> If this is checked is the Databse put nto single user mode/DBO during the
> scan or only if it finds an issue? If this is not checked how would I know
> if there is an Integrity error? I would have to fix it myself instead of
> having SQL automatically do this? How would I go about fixing integrity
> issues then?
> "Arnie Rowland" wrote:
>> Oops, I forgot about that 'checkbox'. (Never use it and hope folks don't.)
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to the
>> top yourself.
>> - H. Norman Schwarzkopf
>>
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> news:456C78F0.4040101@.realsqlguy.com...
>> > George Schneider wrote:
>> >> As part of a Maintenance plan we have an Integrity check scheduled for
>> >> 12:00 AM every Sunday Morning and an Optimization Check 1:00 AM every
>> >> Sunday. I'm noticing errors from one of of our Web Application that
>> >> connects to a SQL database. It has a process that runs every night 12:00
>> >> AM. This process requires access to one of the Databses on the SQL
>> >> server. The error is basically a SQL error that states cannot open db,
>> >> login failed. My question is if the integrity check or optimization
>> >> check would cause this. Does it prevent anything from connecting to the
>> >> database?
>> >
>> > If you've told the integrity check to attempt to fix any errors that it
>> > finds, it will put the database into single-user/DBO only mode, preventing
>> > other logins from connecting.
>> >
>> >
>> > --
>> > Tracy McKibben
>> > MCDBA
>> > http://www.realsqlguy.com
>>|||Is that checkbox what is causing the databse to lock and not allow any user
login?
"Tracy McKibben" wrote:
> Arnie Rowland wrote:
> > Oops, I forgot about that 'checkbox'. (Never use it and hope folks don't.)
> >
> Ditto...
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:FD54EDB5-3346-4B64-AF4F-6602CF87F568@.microsoft.com...
> Is that checkbox what is causing the databse to lock and not allow any user
> login?
> "Tracy McKibben" wrote:
>> Arnie Rowland wrote:
>> > Oops, I forgot about that 'checkbox'. (Never use it and hope folks don't.)
>> >
>> Ditto...
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com