Database Maintenance problem

Hi again,
i was wondering if anyone has encountered this problem:
I make a database maintenance plan (through the wizard) because i want to
backup the
server files to another computer on my network
I have made a map to the other computer (as G:),
and i then go to the tab "Complete Backup" and then i select "Use this
directory" option
to select the directory for the baclup files in the other computer.
But, the listbox presented at me there, displays only the locals C:\ and D:\
drives,
not the other mapped one (G:).
I manually enter the full path name and of cource the job cannot execute.
Has anyone any solution to that ?
anthonybI use UNC paths and it works fine for me. Remember that YOU are not going
to be executing this plan it will be SQL Server Agent so G may have
absolutely no meaning to it whatsoever.
Allan
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Anthony Boudouvas" <anthonyb@.mediatel.gr> wrote in message
news:eK88Tv0VDHA.2252@.TK2MSFTNGP10.phx.gbl...
> Hi again,
> i was wondering if anyone has encountered this problem:
> I make a database maintenance plan (through the wizard) because i want to
> backup the
> server files to another computer on my network
> I have made a map to the other computer (as G:),
> and i then go to the tab "Complete Backup" and then i select "Use this
> directory" option
> to select the directory for the baclup files in the other computer.
> But, the listbox presented at me there, displays only the locals C:\ and
D:\
> drives,
> not the other mapped one (G:).
> I manually enter the full path name and of cource the job cannot execute.
> Has anyone any solution to that ?
> anthonyb
>

Database Mail stops working on SQL 2005

We've encountered a problem on several SQL 2005 installations where
database mail completely stops working for no reason. Has anyone else
encountered this? This only affects calls to sp_send_dbmail and job
alerts, etc. When I right-click DBMail in SSMS and choose Send Test
Email - that one works. But none of the stuff that actually WANT to
work. Any help would be appreciated. No errors anywhere - just the
message never arrives.
Did sp_send_dbmail ever work? Did you enable service broker in the msdb database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CoreyB" <unc27932@.yahoo.com> wrote in message
news:1142005500.961901.109950@.j33g2000cwa.googlegr oups.com...
> We've encountered a problem on several SQL 2005 installations where
> database mail completely stops working for no reason. Has anyone else
> encountered this? This only affects calls to sp_send_dbmail and job
> alerts, etc. When I right-click DBMail in SSMS and choose Send Test
> Email - that one works. But none of the stuff that actually WANT to
> work. Any help would be appreciated. No errors anywhere - just the
> message never arrives.
>
|||Yes - we were using sp_send_dbmail in our SPs and they were running
great. Then they just stopped working - or should I say stopped
delivering. The status in the sys tables say that the messages were
sent. I've done the all the checks found on MS troubleshooting
page...
http://msdn2.microsoft.com/en-US/library/ms189959(SQL.90).aspx
This included checking to be sure service broker was enabled, which it
was, and dbmail was started. I'm fairly confident it's not the SMTP
server b/c the manual test in the GUI of SSMS works fine.
|||Can you run these statements and post back the result?
select count(*) from msdb.dbo.InternalMailQueue
select count(*) from msdb.dbo.ExternalMailQueue
select count(*) from msdb.sys.transmission_queue
select distinct transmission_status from msdb.sys.transmission_queue
select sq.name,qm.* from sys.dm_broker_queue_monitors qm
join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
where database_id = db_id('msdb')
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"CoreyB" <unc27932@.yahoo.com> wrote in message
news:1142005500.961901.109950@.j33g2000cwa.googlegr oups.com...
> We've encountered a problem on several SQL 2005 installations where
> database mail completely stops working for no reason. Has anyone else
> encountered this? This only affects calls to sp_send_dbmail and job
> alerts, etc. When I right-click DBMail in SSMS and choose Send Test
> Email - that one works. But none of the stuff that actually WANT to
> work. Any help would be appreciated. No errors anywhere - just the
> message never arrives.
>
|||Here are the results of those SQL statements...
select count(*) from msdb.dbo.InternalMailQueue
0
select count(*) from msdb.dbo.ExternalMailQueue
0
select count(*) from msdb.sys.transmission_queue
0
select distinct transmission_status from msdb.sys.transmission_queue
No Rows Returned
select sq.name,qm.* from sys.dm_broker_queue_monitors qm
join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
where database_id = db_id('msdb')
InternalMailQueue4843150049INACTIVE2006-03-10
16:29:19.9972006-03-10 16:29:19.9830
ExternalMailQueue4875150163INACTIVE2006-03-10
16:39:19.9572006-03-10 16:24:42.2430
Remus Rusanu [MSFT] wrote:[vbcol=seagreen]
> Can you run these statements and post back the result?
> select count(*) from msdb.dbo.InternalMailQueue
> select count(*) from msdb.dbo.ExternalMailQueue
> select count(*) from msdb.sys.transmission_queue
> select distinct transmission_status from msdb.sys.transmission_queue
> select sq.name,qm.* from sys.dm_broker_queue_monitors qm
> join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
> where database_id = db_id('msdb')
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "CoreyB" <unc27932@.yahoo.com> wrote in message
> news:1142005500.961901.109950@.j33g2000cwa.googlegr oups.com...
|||I'm still hoping someone might know about this...anyone?
CoreyB wrote:[vbcol=seagreen]
> Here are the results of those SQL statements...
> select count(*) from msdb.dbo.InternalMailQueue
> 0
> select count(*) from msdb.dbo.ExternalMailQueue
> 0
> select count(*) from msdb.sys.transmission_queue
> 0
> select distinct transmission_status from msdb.sys.transmission_queue
> No Rows Returned
>
> select sq.name,qm.* from sys.dm_broker_queue_monitors qm
> join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
> where database_id = db_id('msdb')
> InternalMailQueue4843150049INACTIVE2006-03-10
> 16:29:19.9972006-03-10 16:29:19.9830
> ExternalMailQueue4875150163INACTIVE2006-03-10
> 16:39:19.9572006-03-10 16:24:42.2430
>
> Remus Rusanu [MSFT] wrote:
|||CoreyB,
I had similar symptoms but not identical. Database Mail stopped
working unexpectedly including send test email as well as alerts.
Also, database mail log had no activity since a couple of days ago when
it stopped working. I had 37 messages unsent.
I double-checked the following:
1. database mail enabled via surface configuration
2. proper agent and profiles setup
3. service broker message delivery activated
4. database mail in mail host database started
All looked good so far...
5. messages stuck in queue -- yes, 37 total 6. status of messages in
queue -- status "unsent" which traces back to problem with database
mail configuration or external program not getting called for some
reason.
So I basically navigated to location of databasemail90.exe and
double-clicked to run manually and viola, messages started flowing.
However, CMD window stayed open longer than expected so I closed
abruptly.
After this, Step 4 above, "database mail started" now showed status of
"stopped". I ran a command in SQL studio to restart and now things
worked as expected -- mail flowing without me having to double-click
anything.
In summary, my observation is that the external program,
databasemail90.exe was in a "bad state" and required me to kick start
it manually.
http://msdn2.microsoft.com/en-US/library/ms189959(SQL.90).aspx
Here is relevant SQL obtained from MSDN troubleshooting guides if
anyone is interested:
--check to see if service broker is enabled SELECT is_broker_enabled
FROM sys.databases WHERE name = 'msdb' ;
--check to see if database mail is started in msdb database use msdb go
EXECUTE dbo.sysmail_help_status_sp ;
--start database mail if necessary
EXECUTE dbo.sysmail_start_sp ;
--check status of mail queue
sysmail_help_queue_sp @.queue_type = 'Mail' ;
--check database mail event log
select * from sysmail_event_log
--check database mail queue for status of all items select * from
sysmail_allitems
select * from sysmail_allitems

Database Mail stops working on SQL 2005

We've encountered a problem on several SQL 2005 installations where
database mail completely stops working for no reason. Has anyone else
encountered this? This only affects calls to sp_send_dbmail and job
alerts, etc. When I right-click DBMail in SSMS and choose Send Test
Email - that one works. But none of the stuff that actually WANT to
work. Any help would be appreciated. No errors anywhere - just the
message never arrives.Did sp_send_dbmail ever work? Did you enable service broker in the msdb database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CoreyB" <unc27932@.yahoo.com> wrote in message
news:1142005500.961901.109950@.j33g2000cwa.googlegroups.com...
> We've encountered a problem on several SQL 2005 installations where
> database mail completely stops working for no reason. Has anyone else
> encountered this? This only affects calls to sp_send_dbmail and job
> alerts, etc. When I right-click DBMail in SSMS and choose Send Test
> Email - that one works. But none of the stuff that actually WANT to
> work. Any help would be appreciated. No errors anywhere - just the
> message never arrives.
>|||Yes - we were using sp_send_dbmail in our SPs and they were running
great. Then they just stopped working - or should I say stopped
delivering. The status in the sys tables say that the messages were
sent. I've done the all the checks found on MS troubleshooting
page...
http://msdn2.microsoft.com/en-US/library/ms189959(SQL.90).aspx
This included checking to be sure service broker was enabled, which it
was, and dbmail was started. I'm fairly confident it's not the SMTP
server b/c the manual test in the GUI of SSMS works fine.|||Can you run these statements and post back the result?
select count(*) from msdb.dbo.InternalMailQueue
select count(*) from msdb.dbo.ExternalMailQueue
select count(*) from msdb.sys.transmission_queue
select distinct transmission_status from msdb.sys.transmission_queue
select sq.name,qm.* from sys.dm_broker_queue_monitors qm
join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
where database_id = db_id('msdb')
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"CoreyB" <unc27932@.yahoo.com> wrote in message
news:1142005500.961901.109950@.j33g2000cwa.googlegroups.com...
> We've encountered a problem on several SQL 2005 installations where
> database mail completely stops working for no reason. Has anyone else
> encountered this? This only affects calls to sp_send_dbmail and job
> alerts, etc. When I right-click DBMail in SSMS and choose Send Test
> Email - that one works. But none of the stuff that actually WANT to
> work. Any help would be appreciated. No errors anywhere - just the
> message never arrives.
>|||Here are the results of those SQL statements...
select count(*) from msdb.dbo.InternalMailQueue
0
select count(*) from msdb.dbo.ExternalMailQueue
0
select count(*) from msdb.sys.transmission_queue
0
select distinct transmission_status from msdb.sys.transmission_queue
No Rows Returned
select sq.name,qm.* from sys.dm_broker_queue_monitors qm
join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
where database_id = db_id('msdb')
InternalMailQueue 4 843150049 INACTIVE 2006-03-10
16:29:19.997 2006-03-10 16:29:19.983 0
ExternalMailQueue 4 875150163 INACTIVE 2006-03-10
16:39:19.957 2006-03-10 16:24:42.243 0
Remus Rusanu [MSFT] wrote:
> Can you run these statements and post back the result?
> select count(*) from msdb.dbo.InternalMailQueue
> select count(*) from msdb.dbo.ExternalMailQueue
> select count(*) from msdb.sys.transmission_queue
> select distinct transmission_status from msdb.sys.transmission_queue
> select sq.name,qm.* from sys.dm_broker_queue_monitors qm
> join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
> where database_id = db_id('msdb')
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "CoreyB" <unc27932@.yahoo.com> wrote in message
> news:1142005500.961901.109950@.j33g2000cwa.googlegroups.com...
> > We've encountered a problem on several SQL 2005 installations where
> > database mail completely stops working for no reason. Has anyone else
> > encountered this? This only affects calls to sp_send_dbmail and job
> > alerts, etc. When I right-click DBMail in SSMS and choose Send Test
> > Email - that one works. But none of the stuff that actually WANT to
> > work. Any help would be appreciated. No errors anywhere - just the
> > message never arrives.
> >|||I'm still hoping someone might know about this...anyone'
CoreyB wrote:
> Here are the results of those SQL statements...
> select count(*) from msdb.dbo.InternalMailQueue
> 0
> select count(*) from msdb.dbo.ExternalMailQueue
> 0
> select count(*) from msdb.sys.transmission_queue
> 0
> select distinct transmission_status from msdb.sys.transmission_queue
> No Rows Returned
>
> select sq.name,qm.* from sys.dm_broker_queue_monitors qm
> join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
> where database_id = db_id('msdb')
> InternalMailQueue 4 843150049 INACTIVE 2006-03-10
> 16:29:19.997 2006-03-10 16:29:19.983 0
> ExternalMailQueue 4 875150163 INACTIVE 2006-03-10
> 16:39:19.957 2006-03-10 16:24:42.243 0
>
> Remus Rusanu [MSFT] wrote:
> > Can you run these statements and post back the result?
> >
> > select count(*) from msdb.dbo.InternalMailQueue
> > select count(*) from msdb.dbo.ExternalMailQueue
> > select count(*) from msdb.sys.transmission_queue
> > select distinct transmission_status from msdb.sys.transmission_queue
> >
> > select sq.name,qm.* from sys.dm_broker_queue_monitors qm
> > join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
> > where database_id = db_id('msdb')
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > HTH,
> > ~ Remus Rusanu
> >
> > SQL Service Broker
> > http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> >
> >
> > "CoreyB" <unc27932@.yahoo.com> wrote in message
> > news:1142005500.961901.109950@.j33g2000cwa.googlegroups.com...
> > > We've encountered a problem on several SQL 2005 installations where
> > > database mail completely stops working for no reason. Has anyone else
> > > encountered this? This only affects calls to sp_send_dbmail and job
> > > alerts, etc. When I right-click DBMail in SSMS and choose Send Test
> > > Email - that one works. But none of the stuff that actually WANT to
> > > work. Any help would be appreciated. No errors anywhere - just the
> > > message never arrives.
> > >|||CoreyB,
I had similar symptoms but not identical. Database Mail stopped
working unexpectedly including send test email as well as alerts.
Also, database mail log had no activity since a couple of days ago when
it stopped working. I had 37 messages unsent.
I double-checked the following:
1. database mail enabled via surface configuration
2. proper agent and profiles setup
3. service broker message delivery activated
4. database mail in mail host database started
All looked good so far...
5. messages stuck in queue -- yes, 37 total 6. status of messages in
queue -- status "unsent" which traces back to problem with database
mail configuration or external program not getting called for some
reason.
So I basically navigated to location of databasemail90.exe and
double-clicked to run manually and viola, messages started flowing.
However, CMD window stayed open longer than expected so I closed
abruptly.
After this, Step 4 above, "database mail started" now showed status of
"stopped". I ran a command in SQL studio to restart and now things
worked as expected -- mail flowing without me having to double-click
anything.
In summary, my observation is that the external program,
databasemail90.exe was in a "bad state" and required me to kick start
it manually.
http://msdn2.microsoft.com/en-US/library/ms189959(SQL.90).aspx
Here is relevant SQL obtained from MSDN troubleshooting guides if
anyone is interested:
--check to see if service broker is enabled SELECT is_broker_enabled
FROM sys.databases WHERE name = 'msdb' ;
--check to see if database mail is started in msdb database use msdb go
EXECUTE dbo.sysmail_help_status_sp ;
--start database mail if necessary
EXECUTE dbo.sysmail_start_sp ;
--check status of mail queue
sysmail_help_queue_sp @.queue_type = 'Mail' ;
--check database mail event log
select * from sysmail_event_log
--check database mail queue for status of all items select * from
sysmail_allitems
select * from sysmail_allitems

Database Mail stops working on SQL 2005

We've encountered a problem on several SQL 2005 installations where
database mail completely stops working for no reason. Has anyone else
encountered this? This only affects calls to sp_send_dbmail and job
alerts, etc. When I right-click DBMail in SSMS and choose Send Test
Email - that one works. But none of the stuff that actually WANT to
work. Any help would be appreciated. No errors anywhere - just the
message never arrives.Did sp_send_dbmail ever work? Did you enable service broker in the msdb data
base?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CoreyB" <unc27932@.yahoo.com> wrote in message
news:1142005500.961901.109950@.j33g2000cwa.googlegroups.com...
> We've encountered a problem on several SQL 2005 installations where
> database mail completely stops working for no reason. Has anyone else
> encountered this? This only affects calls to sp_send_dbmail and job
> alerts, etc. When I right-click DBMail in SSMS and choose Send Test
> Email - that one works. But none of the stuff that actually WANT to
> work. Any help would be appreciated. No errors anywhere - just the
> message never arrives.
>|||Yes - we were using sp_send_dbmail in our SPs and they were running
great. Then they just stopped working - or should I say stopped
delivering. The status in the sys tables say that the messages were
sent. I've done the all the checks found on MS troubleshooting
page...
http://msdn2.microsoft.com/en-US/library/ms189959(SQL.90).aspx
This included checking to be sure service broker was enabled, which it
was, and dbmail was started. I'm fairly confident it's not the SMTP
server b/c the manual test in the GUI of SSMS works fine.|||Can you run these statements and post back the result?
select count(*) from msdb.dbo.InternalMailQueue
select count(*) from msdb.dbo.ExternalMailQueue
select count(*) from msdb.sys.transmission_queue
select distinct transmission_status from msdb.sys.transmission_queue
select sq.name,qm.* from sys.dm_broker_queue_monitors qm
join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
where database_id = db_id('msdb')
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"CoreyB" <unc27932@.yahoo.com> wrote in message
news:1142005500.961901.109950@.j33g2000cwa.googlegroups.com...
> We've encountered a problem on several SQL 2005 installations where
> database mail completely stops working for no reason. Has anyone else
> encountered this? This only affects calls to sp_send_dbmail and job
> alerts, etc. When I right-click DBMail in SSMS and choose Send Test
> Email - that one works. But none of the stuff that actually WANT to
> work. Any help would be appreciated. No errors anywhere - just the
> message never arrives.
>|||Here are the results of those SQL statements...
select count(*) from msdb.dbo.InternalMailQueue
0
select count(*) from msdb.dbo.ExternalMailQueue
0
select count(*) from msdb.sys.transmission_queue
0
select distinct transmission_status from msdb.sys.transmission_queue
No Rows Returned
select sq.name,qm.* from sys.dm_broker_queue_monitors qm
join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
where database_id = db_id('msdb')
InternalMailQueue 4 843150049 INACTIVE 2
006-03-10
16:29:19.997 2006-03-10 16:29:19.983 0
ExternalMailQueue 4 875150163 INACTIVE 2
006-03-10
16:39:19.957 2006-03-10 16:24:42.243 0
Remus Rusanu [MSFT] wrote:[vbcol=seagreen]
> Can you run these statements and post back the result?
> select count(*) from msdb.dbo.InternalMailQueue
> select count(*) from msdb.dbo.ExternalMailQueue
> select count(*) from msdb.sys.transmission_queue
> select distinct transmission_status from msdb.sys.transmission_queue
> select sq.name,qm.* from sys.dm_broker_queue_monitors qm
> join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
> where database_id = db_id('msdb')
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "CoreyB" <unc27932@.yahoo.com> wrote in message
> news:1142005500.961901.109950@.j33g2000cwa.googlegroups.com...|||I'm still hoping someone might know about this...anyone'
CoreyB wrote:[vbcol=seagreen]
> Here are the results of those SQL statements...
> select count(*) from msdb.dbo.InternalMailQueue
> 0
> select count(*) from msdb.dbo.ExternalMailQueue
> 0
> select count(*) from msdb.sys.transmission_queue
> 0
> select distinct transmission_status from msdb.sys.transmission_queue
> No Rows Returned
>
> select sq.name,qm.* from sys.dm_broker_queue_monitors qm
> join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
> where database_id = db_id('msdb')
> InternalMailQueue 4 843150049 INACTIVE 2
006-03-10
> 16:29:19.997 2006-03-10 16:29:19.983 0
> ExternalMailQueue 4 875150163 INACTIVE 2
006-03-10
> 16:39:19.957 2006-03-10 16:24:42.243 0
>
> Remus Rusanu [MSFT] wrote:|||CoreyB,
I had similar symptoms but not identical. Database Mail stopped
working unexpectedly including send test email as well as alerts.
Also, database mail log had no activity since a couple of days ago when
it stopped working. I had 37 messages unsent.
I double-checked the following:
1. database mail enabled via surface configuration
2. proper agent and profiles setup
3. service broker message delivery activated
4. database mail in mail host database started
All looked good so far...
5. messages stuck in queue -- yes, 37 total 6. status of messages in
queue -- status "unsent" which traces back to problem with database
mail configuration or external program not getting called for some
reason.
So I basically navigated to location of databasemail90.exe and
double-clicked to run manually and viola, messages started flowing.
However, CMD window stayed open longer than expected so I closed
abruptly.
After this, Step 4 above, "database mail started" now showed status of
"stopped". I ran a command in SQL studio to restart and now things
worked as expected -- mail flowing without me having to double-click
anything.
In summary, my observation is that the external program,
databasemail90.exe was in a "bad state" and required me to kick start
it manually.
http://msdn2.microsoft.com/en-US/library/ms189959(SQL.90).aspx
Here is relevant SQL obtained from MSDN troubleshooting guides if
anyone is interested:
--check to see if service broker is enabled SELECT is_broker_enabled
FROM sys.databases WHERE name = 'msdb' ;
--check to see if database mail is started in msdb database use msdb go
EXECUTE dbo.sysmail_help_status_sp ;
--start database mail if necessary
EXECUTE dbo.sysmail_start_sp ;
--check status of mail queue
sysmail_help_queue_sp @.queue_type = 'Mail' ;
--check database mail event log
select * from sysmail_event_log
--check database mail queue for status of all items select * from
sysmail_allitems
select * from sysmail_allitems