Tuesday, March 27, 2012
database model4IDR (10)
almost , but I don't have database named model4IDR. followinf are the
messages I found from the log:
Database restored: Database: model4IDR, creation date(time):
2000/08/06(01:40:52), first LSN: 4:305:1, last LSN: 4:307:1, number of dump
devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE:
{'RESTORE{c9f70be2-881e-4f25-ad1b-3d04f15d2435}'}).
BStarting up database 'model4IDR'.
Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
Starting up database 'model4IDR'.
Anybody know what is happening within my server? Thanks.
did you hear =?Utf-8?B?Q2F0ZWxpbiBXYW5n?=
<CatelinWang@.discussions.microsoft.com> say in news:81499AC3-0EC0-4F91-
9869-901C13FB78A3@.microsoft.com:
> model4IDR
that is the database that Veritas creates for the Inteligent Disaster
Recovery option. it appears to be a known issue though googling doesn't
turn up much (note this one though - http://tinyurl.com/clyph).
could this be the issue:
http://seer.support.veritas.com/docs/253885.htm
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||Thanks for the input.
I just found the server has another instance running, I am not sure if it
has something to do with this, I will check with NT admin, he may have backup
tape running.
"Neil MacMurchy" wrote:
> did you hear =?Utf-8?B?Q2F0ZWxpbiBXYW5n?=
> <CatelinWang@.discussions.microsoft.com> say in news:81499AC3-0EC0-4F91-
> 9869-901C13FB78A3@.microsoft.com:
>
> that is the database that Veritas creates for the Inteligent Disaster
> Recovery option. it appears to be a known issue though googling doesn't
> turn up much (note this one though - http://tinyurl.com/clyph).
> could this be the issue:
> http://seer.support.veritas.com/docs/253885.htm
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>
database model4IDR (10)
y
almost , but I don't have database named model4IDR. followinf are the
messages I found from the log:
Database restored: Database: model4IDR, creation date(time):
2000/08/06(01:40:52), first LSN: 4:305:1, last LSN: 4:307:1, number of dump
devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE:
{'RESTORE{c9f70be2-881e-4f25-ad1b-3d04f15d2435}'}).
BStarting up database 'model4IDR'.
Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
Starting up database 'model4IDR'.
Anybody know what is happening within my server? Thanks.did you hear examnotes
<CatelinWang@.discussions.microsoft.com> say in news:81499AC3-0EC0-4F91-
9869-901C13FB78A3@.microsoft.com:
> model4IDR
that is the database that Veritas creates for the Inteligent Disaster
Recovery option. it appears to be a known issue though googling doesn't
turn up much (note this one though - http://tinyurl.com/clyph).
could this be the issue:
http://seer.support.veritas.com/docs/253885.htm
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Thanks for the input.
I just found the server has another instance running, I am not sure if it
has something to do with this, I will check with NT admin, he may have backu
p
tape running.
"Neil MacMurchy" wrote:
> did you hear examnotes
> <CatelinWang@.discussions.microsoft.com> say in news:81499AC3-0EC0-4F91-
> 9869-901C13FB78A3@.microsoft.com:
>
> that is the database that Veritas creates for the Inteligent Disaster
> Recovery option. it appears to be a known issue though googling doesn't
> turn up much (note this one though - http://tinyurl.com/clyph).
> could this be the issue:
> http://seer.support.veritas.com/docs/253885.htm
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>
database model4IDR (10)
almost , but I don't have database named model4IDR. followinf are the
messages I found from the log:
Database restored: Database: model4IDR, creation date(time):
2000/08/06(01:40:52), first LSN: 4:305:1, last LSN: 4:307:1, number of dump
devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE:
{'RESTORE{c9f70be2-881e-4f25-ad1b-3d04f15d2435}'}).
BStarting up database 'model4IDR'.
Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
Starting up database 'model4IDR'.
Anybody know what is happening within my server? Thanks.did you hear =?Utf-8?B?Q2F0ZWxpbiBXYW5n?=<CatelinWang@.discussions.microsoft.com> say in news:81499AC3-0EC0-4F91-
9869-901C13FB78A3@.microsoft.com:
> model4IDR
that is the database that Veritas creates for the Inteligent Disaster
Recovery option. it appears to be a known issue though googling doesn't
turn up much (note this one though - http://tinyurl.com/clyph).
could this be the issue:
http://seer.support.veritas.com/docs/253885.htm
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Thanks for the input.
I just found the server has another instance running, I am not sure if it
has something to do with this, I will check with NT admin, he may have backup
tape running.
"Neil MacMurchy" wrote:
> did you hear =?Utf-8?B?Q2F0ZWxpbiBXYW5n?=> <CatelinWang@.discussions.microsoft.com> say in news:81499AC3-0EC0-4F91-
> 9869-901C13FB78A3@.microsoft.com:
> > model4IDR
> that is the database that Veritas creates for the Inteligent Disaster
> Recovery option. it appears to be a known issue though googling doesn't
> turn up much (note this one though - http://tinyurl.com/clyph).
> could this be the issue:
> http://seer.support.veritas.com/docs/253885.htm
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>
Monday, March 19, 2012
Database Maintenance question...
transaction logs, running optimizations, and to perform integrity checks.
I've been noticing that when I don't receive a notice when my jobs have
completed via email, the Status of the jobs are in "Performing completion
actions". The jobs will not re-run until I either restart SQL server or do a
reboot. Then it works for a while, then the status gets stuck again on
"Performing completion actions".
Does anyone know what could be causing this? It's happening on the database
and transaction log backups and the optimization job. Again, the maintenance
plan is configured for 3 databases, all of which are set to "Full" for their
recovery mode, and the total size of all 3 is around 7GB.
Should I maybe create a separate maintenance plan for each individual
database?
Thank you!
Hi
This may be an issue with sending the notification itself see the thread
http://tinyurl.com/yay4yj and
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b328197&Product=sql.
You could use SMTP emails instead see
http://www.sqldev.net/xp/xpsmtp.htminstead and use the job flow logic of the
job to either send a success or failure message.
John
"Saral6978" wrote:
> I set up a Database Maintenance plan for backing 3 databases, their
> transaction logs, running optimizations, and to perform integrity checks.
> I've been noticing that when I don't receive a notice when my jobs have
> completed via email, the Status of the jobs are in "Performing completion
> actions". The jobs will not re-run until I either restart SQL server or do a
> reboot. Then it works for a while, then the status gets stuck again on
> "Performing completion actions".
> Does anyone know what could be causing this? It's happening on the database
> and transaction log backups and the optimization job. Again, the maintenance
> plan is configured for 3 databases, all of which are set to "Full" for their
> recovery mode, and the total size of all 3 is around 7GB.
> Should I maybe create a separate maintenance plan for each individual
> database?
> Thank you!
|||Thank you, John for your response. However, can you double-check the last
link you posted? It doesn't seem to find the page.
Thanks!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This may be an issue with sending the notification itself see the thread
> http://tinyurl.com/yay4yj and
> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b328197&Product=sql.
> You could use SMTP emails instead see
> http://www.sqldev.net/xp/xpsmtp.htminstead and use the job flow logic of the
> job to either send a success or failure message.
> John
> "Saral6978" wrote:
|||Hi
There should have been a space before instead try
http://www.sqldev.net/xp/xpsmtp.htm
John
"Saral6978" wrote:
[vbcol=seagreen]
> Thank you, John for your response. However, can you double-check the last
> link you posted? It doesn't seem to find the page.
> Thanks!
> "John Bell" wrote:
|||Thank you!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> There should have been a space before instead try
> http://www.sqldev.net/xp/xpsmtp.htm
> John
> "Saral6978" wrote:
Database Maintenance question...
transaction logs, running optimizations, and to perform integrity checks.
I've been noticing that when I don't receive a notice when my jobs have
completed via email, the Status of the jobs are in "Performing completion
actions". The jobs will not re-run until I either restart SQL server or do
a
reboot. Then it works for a while, then the status gets stuck again on
"Performing completion actions".
Does anyone know what could be causing this? It's happening on the database
and transaction log backups and the optimization job. Again, the maintenanc
e
plan is configured for 3 databases, all of which are set to "Full" for their
recovery mode, and the total size of all 3 is around 7GB.
Should I maybe create a separate maintenance plan for each individual
database?
Thank you!Hi
This may be an issue with sending the notification itself see the thread
http://tinyurl.com/yay4yj and
http://support.microsoft.com/defaul...smtp.htminstead and use the job flow logic of the
job to either send a success or failure message.
John
"Saral6978" wrote:
> I set up a Database Maintenance plan for backing 3 databases, their
> transaction logs, running optimizations, and to perform integrity checks.
> I've been noticing that when I don't receive a notice when my jobs have
> completed via email, the Status of the jobs are in "Performing completion
> actions". The jobs will not re-run until I either restart SQL server or d
o a
> reboot. Then it works for a while, then the status gets stuck again on
> "Performing completion actions".
> Does anyone know what could be causing this? It's happening on the databa
se
> and transaction log backups and the optimization job. Again, the maintena
nce
> plan is configured for 3 databases, all of which are set to "Full" for the
ir
> recovery mode, and the total size of all 3 is around 7GB.
> Should I maybe create a separate maintenance plan for each individual
> database?
> Thank you!|||Thank you, John for your response. However, can you double-check the last
link you posted? It doesn't seem to find the page.
Thanks!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This may be an issue with sending the notification itself see the thread
> http://tinyurl.com/yay4yj and
> http://support.microsoft.com/defaul...smtp.htminstead and use the job flow logic of t
he
> job to either send a success or failure message.
> John
> "Saral6978" wrote:
>|||Hi
There should have been a space before instead try
http://www.sqldev.net/xp/xpsmtp.htm
John
"Saral6978" wrote:
[vbcol=seagreen]
> Thank you, John for your response. However, can you double-check the last
> link you posted? It doesn't seem to find the page.
> Thanks!
> "John Bell" wrote:
>|||Thank you!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> There should have been a space before instead try
> http://www.sqldev.net/xp/xpsmtp.htm
> John
> "Saral6978" wrote:
>
Thursday, March 8, 2012
Database Maintenance Plan no longer deleting old files
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Smith" <anonymous@.discussions.microsoft.com> wrote in message
news:B0355203-D93B-44F1-AD39-9EEE5194DEE3@.microsoft.com...
> My maintenance plan is set up to delete the old database dump files and transaction logs. After
months of this working, the dump files are no longer being deleted. This has happened before. I
recreated the plan and it worked for a while, then stopped again. The old transaction logs are
being deleted as scheduled. Please help.|||"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in message news:<O7uyC2XxDHA.3224@.tk2msftngp13.phx.gbl>...
> Below KB might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed to
> local drive), then you will need to modify the above (with respect to where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Smith" <anonymous@.discussions.microsoft.com> wrote in message
> news:B0355203-D93B-44F1-AD39-9EEE5194DEE3@.microsoft.com...
> > My maintenance plan is set up to delete the old database dump files and transaction logs. After
> months of this working, the dump files are no longer being deleted. This has happened before. I
> recreated the plan and it worked for a while, then stopped again. The old transaction logs are
> being deleted as scheduled. Please help.
You may also want to check what the 'effective' date is on your job -
see if your date range is out of whack, or else, if the process that
is supposed to be deleting the files is running into a 'file lock'. I
have had problems before like this, where a process that copies my old
backup files to a new directory was not actually terminating all the
time, and so then whenever the other process tried to delete them, it
couldn't. If you can look at that - it may help - or else, try
rebooting the server (if you can) and waiting to see if it behaves
normally again.
When in doubt, reboot *grin*
Sonya
Database maintenance plan backup
set to back up the transaction logs of several specified databases. I open
up the backup database task, then choose <select one or more> from the
Database(s) prompt. When the drop down window opens, I select the radio box
for "these databases", and I check the databases that I want. I save the
database maintenance plan and run it and it works fine. If I open up the
backup database task again, and go to see what databases I have selected,
none are showing as selected. If I save the database maintenance plan and
run it, it fails with the following message, which does not tell me much:
Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
100% complete End Progress DTExec: The package execution returned
DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
1.437 seconds. The package execution failed. The step failed.
This message contrasts with the text file report that shows:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
Report was generated on "pdcnt23".
Maintenance Plan: MaintenancePlan
Duration: 00:00:00
Status: Succeeded.
Details:
If I go back into the backup task and reselect the databases and save the
maintenance plan and run it, everything works fine.
This doesn't work as I would expect it to. Is there something I need to do?
Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
Thanks,
Paul
Paul,
Losing the database is a known problem. MS is looking into it for a later
build of SQL2005.
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
> I've created a database maintenance plan with backup database task which
> is
> set to back up the transaction logs of several specified databases. I
> open
> up the backup database task, then choose <select one or more> from the
> Database(s) prompt. When the drop down window opens, I select the radio
> box
> for "these databases", and I check the databases that I want. I save the
> database maintenance plan and run it and it works fine. If I open up the
> backup database task again, and go to see what databases I have selected,
> none are showing as selected. If I save the database maintenance plan and
> run it, it fails with the following message, which does not tell me much:
> Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
> Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
> Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
> 2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
> Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
> 100% complete End Progress DTExec: The package execution returned
> DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
> 1.437 seconds. The package execution failed. The step failed.
> This message contrasts with the text file report that shows:
> NEW COMPONENT OUTPUT
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
> Report was generated on "pdcnt23".
> Maintenance Plan: MaintenancePlan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> If I go back into the backup task and reselect the databases and save the
> maintenance plan and run it, everything works fine.
> This doesn't work as I would expect it to. Is there something I need to
> do?
> Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
> Thanks,
> Paul
>
|||Thanks Chris.
I had a look around to try and find information about it. Where would I
find more information about it?
Thanks,
Paul
"Chris Wood" wrote:
> Paul,
> Losing the database is a known problem. MS is looking into it for a later
> build of SQL2005.
> Chris
> "PT" <PT@.discussions.microsoft.com> wrote in message
> news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
>
>
|||Paul,
Signon to Microsoft Connect:- https://connect.microsoft.com/default.aspx and
register for SQL
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:4C86DAAF-11E8-4CA7-B740-A3CF4BF32C69@.microsoft.com...[vbcol=seagreen]
> Thanks Chris.
> I had a look around to try and find information about it. Where would I
> find more information about it?
> Thanks,
> Paul
> "Chris Wood" wrote:
Database maintenance plan backup
set to back up the transaction logs of several specified databases. I open
up the backup database task, then choose <select one or more> from the
Database(s) prompt. When the drop down window opens, I select the radio box
for "these databases", and I check the databases that I want. I save the
database maintenance plan and run it and it works fine. If I open up the
backup database task again, and go to see what databases I have selected,
none are showing as selected. If I save the database maintenance plan and
run it, it fails with the following message, which does not tell me much:
Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
100% complete End Progress DTExec: The package execution returned
DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
1.437 seconds. The package execution failed. The step failed.
This message contrasts with the text file report that shows:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
Report was generated on "pdcnt23".
Maintenance Plan: MaintenancePlan
Duration: 00:00:00
Status: Succeeded.
Details:
If I go back into the backup task and reselect the databases and save the
maintenance plan and run it, everything works fine.
This doesn't work as I would expect it to. Is there something I need to do?
Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
Thanks,
PaulPaul,
Losing the database is a known problem. MS is looking into it for a later
build of SQL2005.
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
> I've created a database maintenance plan with backup database task which
> is
> set to back up the transaction logs of several specified databases. I
> open
> up the backup database task, then choose <select one or more> from the
> Database(s) prompt. When the drop down window opens, I select the radio
> box
> for "these databases", and I check the databases that I want. I save the
> database maintenance plan and run it and it works fine. If I open up the
> backup database task again, and go to see what databases I have selected,
> none are showing as selected. If I save the database maintenance plan and
> run it, it fails with the following message, which does not tell me much:
> Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
> Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
> Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
> 2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
> Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
> 100% complete End Progress DTExec: The package execution returned
> DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
> 1.437 seconds. The package execution failed. The step failed.
> This message contrasts with the text file report that shows:
> NEW COMPONENT OUTPUT
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
> Report was generated on "pdcnt23".
> Maintenance Plan: MaintenancePlan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> If I go back into the backup task and reselect the databases and save the
> maintenance plan and run it, everything works fine.
> This doesn't work as I would expect it to. Is there something I need to
> do?
> Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
> Thanks,
> Paul
>|||Thanks Chris.
I had a look around to try and find information about it. Where would I
find more information about it?
Thanks,
Paul
"Chris Wood" wrote:
> Paul,
> Losing the database is a known problem. MS is looking into it for a later
> build of SQL2005.
> Chris
> "PT" <PT@.discussions.microsoft.com> wrote in message
> news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
> > I've created a database maintenance plan with backup database task which
> > is
> > set to back up the transaction logs of several specified databases. I
> > open
> > up the backup database task, then choose <select one or more> from the
> > Database(s) prompt. When the drop down window opens, I select the radio
> > box
> > for "these databases", and I check the databases that I want. I save the
> > database maintenance plan and run it and it works fine. If I open up the
> > backup database task again, and go to see what databases I have selected,
> > none are showing as selected. If I save the database maintenance plan and
> > run it, it fails with the following message, which does not tell me much:
> >
> > Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
> > Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
> > Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
> > 2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
> > Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
> > 100% complete End Progress DTExec: The package execution returned
> > DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
> > 1.437 seconds. The package execution failed. The step failed.
> >
> > This message contrasts with the text file report that shows:
> >
> > NEW COMPONENT OUTPUT
> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
> > Report was generated on "pdcnt23".
> > Maintenance Plan: MaintenancePlan
> > Duration: 00:00:00
> > Status: Succeeded.
> > Details:
> >
> > If I go back into the backup task and reselect the databases and save the
> > maintenance plan and run it, everything works fine.
> >
> > This doesn't work as I would expect it to. Is there something I need to
> > do?
> >
> > Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
> >
> > Thanks,
> >
> > Paul
> >
>
>|||Paul,
Signon to Microsoft Connect:- https://connect.microsoft.com/default.aspx and
register for SQL
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:4C86DAAF-11E8-4CA7-B740-A3CF4BF32C69@.microsoft.com...
> Thanks Chris.
> I had a look around to try and find information about it. Where would I
> find more information about it?
> Thanks,
> Paul
> "Chris Wood" wrote:
>> Paul,
>> Losing the database is a known problem. MS is looking into it for a later
>> build of SQL2005.
>> Chris
>> "PT" <PT@.discussions.microsoft.com> wrote in message
>> news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
>> > I've created a database maintenance plan with backup database task
>> > which
>> > is
>> > set to back up the transaction logs of several specified databases. I
>> > open
>> > up the backup database task, then choose <select one or more> from the
>> > Database(s) prompt. When the drop down window opens, I select the radio
>> > box
>> > for "these databases", and I check the databases that I want. I save
>> > the
>> > database maintenance plan and run it and it works fine. If I open up
>> > the
>> > backup database task again, and go to see what databases I have
>> > selected,
>> > none are showing as selected. If I save the database maintenance plan
>> > and
>> > run it, it fails with the following message, which does not tell me
>> > much:
>> >
>> > Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server
>> > Execute
>> > Package Utility Version 9.00.3042.00 for 32-bit Copyright (C)
>> > Microsoft
>> > Corp 1984-2005. All rights reserved. Started: 10:58:14 AM
>> > Progress:
>> > 2007-11-16 10:58:15.67 Source:
>> > {0EDA7F25-ED67-4796-9399-668FB2506E5F}
>> > Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE
>> > msdb..sp".:
>> > 100% complete End Progress DTExec: The package execution returned
>> > DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM
>> > Elapsed:
>> > 1.437 seconds. The package execution failed. The step failed.
>> >
>> > This message contrasts with the text file report that shows:
>> >
>> > NEW COMPONENT OUTPUT
>> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
>> > Report was generated on "pdcnt23".
>> > Maintenance Plan: MaintenancePlan
>> > Duration: 00:00:00
>> > Status: Succeeded.
>> > Details:
>> >
>> > If I go back into the backup task and reselect the databases and save
>> > the
>> > maintenance plan and run it, everything works fine.
>> >
>> > This doesn't work as I would expect it to. Is there something I need
>> > to
>> > do?
>> >
>> > Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
>> >
>> > Thanks,
>> >
>> > Paul
>> >
>>
Database maintenance plan backup
set to back up the transaction logs of several specified databases. I open
up the backup database task, then choose <select one or more> from the
Database(s) prompt. When the drop down window opens, I select the radio box
for "these databases", and I check the databases that I want. I save the
database maintenance plan and run it and it works fine. If I open up the
backup database task again, and go to see what databases I have selected,
none are showing as selected. If I save the database maintenance plan and
run it, it fails with the following message, which does not tell me much:
Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5
F}
Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
100% complete End Progress DTExec: The package execution returned
DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
1.437 seconds. The package execution failed. The step failed.
This message contrasts with the text file report that shows:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
Report was generated on "pdcnt23".
Maintenance Plan: MaintenancePlan
Duration: 00:00:00
Status: Succeeded.
Details:
If I go back into the backup task and reselect the databases and save the
maintenance plan and run it, everything works fine.
This doesn't work as I would expect it to. Is there something I need to do?
Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
Thanks,
PaulPaul,
Losing the database is a known problem. MS is looking into it for a later
build of SQL2005.
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
> I've created a database maintenance plan with backup database task which
> is
> set to back up the transaction logs of several specified databases. I
> open
> up the backup database task, then choose <select one or more> from the
> Database(s) prompt. When the drop down window opens, I select the radio
> box
> for "these databases", and I check the databases that I want. I save the
> database maintenance plan and run it and it works fine. If I open up the
> backup database task again, and go to see what databases I have selected,
> none are showing as selected. If I save the database maintenance plan and
> run it, it fails with the following message, which does not tell me much:
> Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
> Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
> Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
> 2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506
E5F}
> Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
> 100% complete End Progress DTExec: The package execution returned
> DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
> 1.437 seconds. The package execution failed. The step failed.
> This message contrasts with the text file report that shows:
> NEW COMPONENT OUTPUT
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
> Report was generated on "pdcnt23".
> Maintenance Plan: MaintenancePlan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> If I go back into the backup task and reselect the databases and save the
> maintenance plan and run it, everything works fine.
> This doesn't work as I would expect it to. Is there something I need to
> do?
> Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
> Thanks,
> Paul
>|||Thanks Chris.
I had a look around to try and find information about it. Where would I
find more information about it?
Thanks,
Paul
"Chris Wood" wrote:
> Paul,
> Losing the database is a known problem. MS is looking into it for a later
> build of SQL2005.
> Chris
> "PT" <PT@.discussions.microsoft.com> wrote in message
> news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
>
>|||Paul,
Signon to Microsoft Connect:- https://connect.microsoft.com/default.aspx and
register for SQL
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:4C86DAAF-11E8-4CA7-B740-A3CF4BF32C69@.microsoft.com...[vbcol=seagreen]
> Thanks Chris.
> I had a look around to try and find information about it. Where would I
> find more information about it?
> Thanks,
> Paul
> "Chris Wood" wrote:
>
Saturday, February 25, 2012
Database Mail: getting bounce info from Exchange?
connecting to the mail server. However, I also need to log bounced
emails (bad address, etc).
Are there any clean ways to do this with Database Mail and Exchange?
Or will I have to mix Database Mail with SQL Mail to read bounces out
of the inbox? Or, have a .NET windows service that talks to the
Exchange API?"Manny Klystron" <manny.klystron@.gmail.comwrote in message
news:1177542209.460490.41620@.t39g2000prd.googlegro ups.com...
Quote:
Originally Posted by
Database Mail only sends SMTP email messages and logs problems
connecting to the mail server. However, I also need to log bounced
emails (bad address, etc).
>
Are there any clean ways to do this with Database Mail and Exchange?
Or will I have to mix Database Mail with SQL Mail to read bounces out
of the inbox? Or, have a .NET windows service that talks to the
Exchange API?
I'd have a separate API that handles this. It's not really a good duty for
SQL Server.
Quote:
Originally Posted by
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Database Mail: getting bounce info from Exchange?
connecting to the mail server. However, I also need to log bounced
emails (bad address, etc).
Are there any clean ways to do this with Database Mail and Exchange?
Or will I have to mix Database Mail with SQL Mail to read bounces out
of the inbox? Or, have a .NET windows service that talks to the
Exchange API?
"Manny Klystron" <manny.klystron@.gmail.com> wrote in message
news:1177542209.460490.41620@.t39g2000prd.googlegro ups.com...
> Database Mail only sends SMTP email messages and logs problems
> connecting to the mail server. However, I also need to log bounced
> emails (bad address, etc).
> Are there any clean ways to do this with Database Mail and Exchange?
> Or will I have to mix Database Mail with SQL Mail to read bounces out
> of the inbox? Or, have a .NET windows service that talks to the
> Exchange API?
I'd have a separate API that handles this. It's not really a good duty for
SQL Server.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Database Mail: getting bounce info from Exchange?
connecting to the mail server. However, I also need to log bounced
emails (bad address, etc).
Are there any clean ways to do this with Database Mail and Exchange?
Or will I have to mix Database Mail with SQL Mail to read bounces out
of the inbox? Or, have a .NET windows service that talks to the
Exchange API?"Manny Klystron" <manny.klystron@.gmail.com> wrote in message
news:1177542209.460490.41620@.t39g2000prd.googlegroups.com...
> Database Mail only sends SMTP email messages and logs problems
> connecting to the mail server. However, I also need to log bounced
> emails (bad address, etc).
> Are there any clean ways to do this with Database Mail and Exchange?
> Or will I have to mix Database Mail with SQL Mail to read bounces out
> of the inbox? Or, have a .NET windows service that talks to the
> Exchange API?
I'd have a separate API that handles this. It's not really a good duty for
SQL Server.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Database Mail: getting bounce info from Exchange?
connecting to the mail server. However, I also need to log bounced
emails (bad address, etc).
Are there any clean ways to do this with Database Mail and Exchange?
Or will I have to mix Database Mail with SQL Mail to read bounces out
of the inbox? Or, have a .NET windows service that talks to the
Exchange API?"Manny Klystron" <manny.klystron@.gmail.com> wrote in message
news:1177542209.460490.41620@.t39g2000prd.googlegroups.com...
> Database Mail only sends SMTP email messages and logs problems
> connecting to the mail server. However, I also need to log bounced
> emails (bad address, etc).
> Are there any clean ways to do this with Database Mail and Exchange?
> Or will I have to mix Database Mail with SQL Mail to read bounces out
> of the inbox? Or, have a .NET windows service that talks to the
> Exchange API?
I'd have a separate API that handles this. It's not really a good duty for
SQL Server.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
database mail isn't working in the SQL Agent job notification
Hi,
Starting a few weeks ago I stopped receiving notifications when SQL Server Agent Jobs failed. The only error I see from the Error logs is the following: "[264] An attempt was made to send an email when no email session has been established"
I've followed all of the postings about stoping and restarting the Agent service, confirming that the database mail can send out test emails and making sure that database mail account name matches the actual SMTP account.
From my testing, I can successfully send out a test email by right-clicking on "Database Mail" - "Send Test E-Mail."
Here's my setup:
Active/Passive SQL Server 2005 Cluster.
32 bit
SS2005 SP1 installed
Database Mail worked for around 6 months and then stopped. Not sure if it's a permissions issue, patch issue or a configuration issue.
Any help resolving this would be great.
Sincerely,
Steve
Ditto for me. Standard SQL 2005 setup. Engine, AS, Integration, etc. with SP1. I've done all the troubleshooting steps detailed here because this was the closest related issue I found on the Web.
The stored proc sp_send_dbmail works. So I can craft a workaround.
I found the same issue posted under Technet here. Any ideas?
|||Well this is a slap in the face.
My manager suggested I do a restart of the SQL Server Agent. I had tried that before but since he is the boss I did it again expecting the same results. Well as you might have guessed - its working. And not just on the development server but the production too. Can't figure it.
|||Restarting has become my habit in debugging now thanks to this
"Restart" has been mentioned in couple threads though if you searched by your error message here (or Google)
It's really weird, SQL Mail stopped working randomly IMO, thank god this doesn't happen to SQL Server
|||Steve,
I've seen this from time-to-time on our servers, as well. After a restart of SQL Server Agent, database mail will usually run fine for a month and then the error will come back again. It is frustrating especially when it happens on production boxes with replication, but at least it doesn't require a server reboot like SQL Mail always did when it locked up.
Sunday, February 19, 2012
Database Logs are being truncated
Can someone help me with a SQL error that I'm getting in my event log. The
error is listed below.
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (6)
Event ID: 17055
Date: 12/15/2003
Time: 12:03:47 AM
User: N/A
Computer: SQL-SERVER
Description:
18278 :
Database log truncated: Database: model.
Data:
0000: 66 47 00 00 10 00 00 00 fG.....
0008: 0b 00 00 00 53 00 51 00 ...S.Q.
0010: 4c 00 2d 00 53 00 45 00 L.-.S.E.
0018: 52 00 56 00 45 00 52 00 R.V.E.R.
0020: 00 00 06 00 00 00 6d 00 .....m.
0028: 6f 00 64 00 65 00 6c 00 o.d.e.l.
0030: 00 00 ..
This is happening on 4 of my databases. 2 created by me, Crystal Reports
database (CE8), and the model database.
I'm backing up all db's and the logs for the 4 db's mentioned above full
every day. This problem has been happening long before I decided to backup
the logs though. I thought backing up the logs would fix this problem. The
4 db's above all have Recovery set to "Full", because log backups were
failing when Recovery was set to "Simple" and the error suggested that
recovery be set to something other than "Simple".
I increased the size of the db's and logs to 100 meg, but that didn't help.
Also the size kept going back to a small number. Now I set the databases to
grow automatically, but I set the maximum size allowed to grow to 100 meg
for both db's and logs. The db's are between 2 to 5 megs though.
We write about 100 - 200 lines of data to the server every day. We don't
use Crystal Reports anymore and I'm not sure what the model db is for. The
db grows very slowly.
Thanks!!!
DaveRegarding the error you see, check this article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;818202&Product=sql2k
replies in line.
Steve
"Dave" <none@.none.com> wrote in message
news:O06b7MMxDHA.1596@.TK2MSFTNGP10.phx.gbl...
> Can someone help me with a SQL error that I'm getting in my event log.
The
> error is listed below.
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (6)
> Event ID: 17055
> Date: 12/15/2003
> Time: 12:03:47 AM
> User: N/A
> Computer: SQL-SERVER
> Description:
> 18278 :
> Database log truncated: Database: model.
> Data:
> 0000: 66 47 00 00 10 00 00 00 fG.....
> 0008: 0b 00 00 00 53 00 51 00 ...S.Q.
> 0010: 4c 00 2d 00 53 00 45 00 L.-.S.E.
> 0018: 52 00 56 00 45 00 52 00 R.V.E.R.
> 0020: 00 00 06 00 00 00 6d 00 .....m.
> 0028: 6f 00 64 00 65 00 6c 00 o.d.e.l.
> 0030: 00 00 ..
> This is happening on 4 of my databases. 2 created by me, Crystal Reports
> database (CE8), and the model database.
> I'm backing up all db's and the logs for the 4 db's mentioned above full
> every day. This problem has been happening long before I decided to
backup
> the logs though. I thought backing up the logs would fix this problem.
The
> 4 db's above all have Recovery set to "Full", because log backups were
> failing when Recovery was set to "Simple" and the error suggested that
> recovery be set to something other than "Simple".
> I increased the size of the db's and logs to 100 meg, but that didn't
help.
> Also the size kept going back to a small number. Now I set the databases
to
> grow automatically, but I set the maximum size allowed to grow to 100 meg
> for both db's and logs. The db's are between 2 to 5 megs though.
> We write about 100 - 200 lines of data to the server every day. We don't
> use Crystal Reports anymore and I'm not sure what the model db is for. Th
e
> db grows very slowly.
There is no reason to set the recovery mode on model to full, simple would
be fine. Model is used as a template when you create new databases.
Steve|||Hi Steve,
Thanks for article. Here's the code for the log backups. It looks like
that I don't have the NO_LOG or the TRUNCATE_ONLY options set. I changed
the recover mode of the databases to bulk logged. I'll see if that helps.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
E79DF926-E729-4FA2-8769-62EE4F86120E -Rpt "C:\Program Files\Microsoft SQL
Server\MSSQL\LOG\DB Maintenance Plan - Log Backups6.txt" -DelTxtRpt
2WEEKS -WriteHistory -VrfyBackup -BkUpMedia
ISK -BkUpLog -UseDefDir -DelBkUps 2WEEKS -CrBkSubDir -BkExt "TRN"'
Thanks!!!|||Hi Dave,
Let us know if that helps... if it doesn't we can give you some alternatives
to maintenance plans.
Steve
"Dave" <none@.none.com> wrote in message
news:#$gd3DNxDHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hi Steve,
> Thanks for article. Here's the code for the log backups. It looks like
> that I don't have the NO_LOG or the TRUNCATE_ONLY options set. I changed
> the recover mode of the databases to bulk logged. I'll see if that helps.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> E79DF926-E729-4FA2-8769-62EE4F86120E -Rpt "C:\Program Files\Microsoft SQL
> Server\MSSQL\LOG\DB Maintenance Plan - Log Backups6.txt" -DelTxtRpt
> 2WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> ISK -BkUpLog -UseDefDir -DelBkUps 2WEEKS -CrBkSubDir -BkExt "TRN"'
> Thanks!!!
>|||Still getting the error after I set the recovery mode to bulk logged.
Is it possible that my space allocation for the logs isn't working right?
Here's what my setup looks like for one of my db's that's generating the
error.
&Transaction Log Files
Space Allocated = 3Mb
File Properties
Automatically Grow File = Checked
In Megabytes = 3
Restrict File Growth (MB) = 100
What else can I look at?
Thanks!
Dave|||Sometimes autogrow doesn't seem to "catch up". I suggest that you pre-allocate storage.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dave" <none@.none.com> wrote in message news:OudZx1YxDHA.2328@.TK2MSFTNGP10.phx.gbl...
> Still getting the error after I set the recovery mode to bulk logged.
> Is it possible that my space allocation for the logs isn't working right?
> Here's what my setup looks like for one of my db's that's generating the
> error.
> &Transaction Log Files
> Space Allocated = 3Mb
> File Properties
> Automatically Grow File = Checked
> In Megabytes = 3
> Restrict File Growth (MB) = 100
> What else can I look at?
> Thanks!
> Dave
>|||I agree with Tibor -- pre-allocate the database and transaction log size to
the estimated total size needed. Also, consider setting the recovery mode to
'simple' for the system databases; master, msdb, model & tempdb. Do place
the first three on a full backup cycle though...
Steve
"Dave" <none@.none.com> wrote in message
news:OudZx1YxDHA.2328@.TK2MSFTNGP10.phx.gbl...
> Still getting the error after I set the recovery mode to bulk logged.
> Is it possible that my space allocation for the logs isn't working right?
> Here's what my setup looks like for one of my db's that's generating the
> error.
> &Transaction Log Files
> Space Allocated = 3Mb
> File Properties
> Automatically Grow File = Checked
> In Megabytes = 3
> Restrict File Growth (MB) = 100
> What else can I look at?
> Thanks!
> Dave
>|||I have tried the following in the past and have not had success.
Under database properties->Transaction Log->&Transaction Log Files I
increased the Space Allocated (MB) field to 100 MB. It seemed to work, but
when I checked this again the next day, the space allocated value was down
to a small number (3 MB). Am I doing this right?
Thanks!!!
Dave|||Hi Steve,
I set model back to Simple recovery mode so I have all databases on Simple
recovery mode except for the 2 that we created and the Crystal Reports db.
I noticed something strange in the event log that I hadn't noticed before.
My backup schedule for the databases(not the logs) was at 5am, but the event
log says they're being backed up at around 12 am and this is when the errors
are occuring. The backups excecute again again at 5am successfully with no
event log errors. The logs get backed up successfully on schedule at 6:00
am. I'm guessing that the 12 o'clock backups are caused by our tape backup
software(Tapeware) which has a sql server connector option that we bought.
The Tapeware backups are scheduled at 12:01am every day. This is the only
thing I can think of that would cause the backups at around 12am.
Anyway, I'm going to disable the sql backups on Tapeware for a day and see
if I still get errors.
Should I not back up the logs for model? Simple recovery models cause log
backup errors.
Thanks!!!
Dave|||> Should I not back up the logs for model?
I'd say its overkill.
> Simple recovery models cause log
> backup errors.
IF you set model to simple, then don't do log backups for model, quite
simply.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dave" <none@.none.com> wrote in message
news:u8j09FlxDHA.2568@.TK2MSFTNGP09.phx.gbl...
> Hi Steve,
> I set model back to Simple recovery mode so I have all databases on Simple
> recovery mode except for the 2 that we created and the Crystal Reports db.
> I noticed something strange in the event log that I hadn't noticed before.
> My backup schedule for the databases(not the logs) was at 5am, but the
event
> log says they're being backed up at around 12 am and this is when the
errors
> are occuring. The backups excecute again again at 5am successfully with
no
> event log errors. The logs get backed up successfully on schedule at 6:00
> am. I'm guessing that the 12 o'clock backups are caused by our tape
backup
> software(Tapeware) which has a sql server connector option that we bought.
> The Tapeware backups are scheduled at 12:01am every day. This is the only
> thing I can think of that would cause the backups at around 12am.
> Anyway, I'm going to disable the sql backups on Tapeware for a day and see
> if I still get errors.
> Should I not back up the logs for model? Simple recovery models cause log
> backup errors.
> Thanks!!!
> Dave
>
>|||Thanks Tibor,
I disabled our tape backup software sql server backup function and the
errors went away.
My maintenance plan backups are working without any errors.
I'm going to assume that the sql server is working fine even though I'm
getting the log truncated errors. I don't think I can do anything to fix
that. Is this a good assumtion?
Thanks!!!
database logs
Any suggestions on how to eliminate all the new free space I have created? I am looking to get the extra space on my server
Thanks.First check for any open transactions using dbcc opentran. Then follow the
steps in
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q272318
For reasons why the log can fill up or grow unexpectadly check out
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q110139
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;317375
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Karen" <anonymous@.discussions.microsoft.com> wrote in message
news:34FB692C-3644-45A6-823F-628A5F4C3F18@.microsoft.com...
> I am trying to reduce the physical size of a large database log. I have
run several utilities - back up with the remove inactive entries from the
transaction log checked, the DBCC shrinkfile utility (with a target size and
a truncateonly option) and run DB maintenance to remove extra space. The
properties of this log file show that it has reduced the size of the file,
but the physical size of the file on the server still remains as big as it
was.
> Any suggestions on how to eliminate all the new free space I have created?
I am looking to get the extra space on my server.
> Thanks.
database logs
several utilities - back up with the remove inactive entries from the trans
action log checked, the DBCC shrinkfile utility (with a target size and a tr
uncateonly option) and run
DB maintenance to remove extra space. The properties of this log file show
that it has reduced the size of the file, but the physical size of the file
on the server still remains as big as it was.
Any suggestions on how to eliminate all the new free space I have created?
I am looking to get the extra space on my server.
Thanks.First check for any open transactions using dbcc opentran. Then follow the
steps in
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...b;en-us;Q272318
For reasons why the log can fill up or grow unexpectadly check out
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/defaul...b;en-us;Q110139
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/defaul...kb;EN-US;317375
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Karen" <anonymous@.discussions.microsoft.com> wrote in message
news:34FB692C-3644-45A6-823F-628A5F4C3F18@.microsoft.com...
quote:
> I am trying to reduce the physical size of a large database log. I have
run several utilities - back up with the remove inactive entries from the
transaction log checked, the DBCC shrinkfile utility (with a target size and
a truncateonly option) and run DB maintenance to remove extra space. The
properties of this log file show that it has reduced the size of the file,
but the physical size of the file on the server still remains as big as it
was.
quote:
> Any suggestions on how to eliminate all the new free space I have created?
I am looking to get the extra space on my server.
quote:
> Thanks.
Friday, February 17, 2012
Database log truncated
backup the transaction log with truncate_only
Switch your database back to Full recovery.
run your maintance plan.|||As the article states, the cause for this entry in Event Log is the presence of either TRUNCATE_ONLY or NO_LOG.
Look into SQLMAINT command line utility, it gives you higher level of control, and you can use it against Database Maintenance plans as well.|||Make sure you issue full backup statement once NO_LOG or TRUNCATE_ONLY is issued on Tlog.|||Originally posted by rdjabarov
As the article states, the cause for this entry in Event Log is the presence of either TRUNCATE_ONLY or NO_LOG.
Look into SQLMAINT command line utility, it gives you higher level of control, and you can use it against Database Maintenance plans as well.
Yes, the article states that, but where I can see presence of these two options(TRUNCATE_ONLY or NO_LOG). I mean this job was created by Maintenance plan and is running as :
EXECUTE master.dbo.xp_sqlmaint N'-PlanID E4B335C8-20F2-4E29-A332-BD4778515EF5 -Rpt "D:\MSSQL7\LOG\DB Maintenance Plan20006.txt" -DelTxtRpt 2WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "D:\MSSQL7\BACKUP" -DelBkUps 1DAYS -BkExt "TRN"'
And if i use SQLMAINT utility to run the Database Maintenance plans what swich should i use for TRUNCATE_ONLY or NO_LOG options?
One more thing i don't understand it only happened on one server and 2 other works just fine.|||That's a mystery. Did you try to run the job manually? Could it be that it's not your job that fires NO_LOG, but some other maybe even ad-hoc statement issued from QA?|||http://support.microsoft.com/default.aspx?scid=kb;en-us;288577 KBA to troubleshoot Maint.plans.
HTH
Database log files
curios is, the transaction logs are way to large for the amount of
transactions we are running(yes, the transaction logs are set to
automatically grow). Our application would not send that amount of
transactions to the database. The other thing I am thinking is that there is
another db within this sqlserver that I think access this DB and I think the
transaction logs would prove this.
Thanks for your assistance in advance....
I've listed three log reader tools on my links page:
http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJ" <TJ@.discussions.microsoft.com> wrote in message
news:92CF3A8E-4DA0-4E1B-BCA8-EC4491DCAFED@.microsoft.com...
> Does anyone know of anyway to read the transaction logs? The reason why I am
> curios is, the transaction logs are way to large for the amount of
> transactions we are running(yes, the transaction logs are set to
> automatically grow). Our application would not send that amount of
> transactions to the database. The other thing I am thinking is that there is
> another db within this sqlserver that I think access this DB and I think the
> transaction logs would prove this.
> Thanks for your assistance in advance....
|||What recovery mode are you in? If it is FULL and you don't issue regular
Log backups it will continue to grow and grow. Change it to simple mode and
you should not see it grow any more. If you really want to see what is
happening on the server you should use Profiler or Trace.
Andrew J. Kelly SQL MVP
"TJ" <TJ@.discussions.microsoft.com> wrote in message
news:92CF3A8E-4DA0-4E1B-BCA8-EC4491DCAFED@.microsoft.com...
> Does anyone know of anyway to read the transaction logs? The reason why I
> am
> curios is, the transaction logs are way to large for the amount of
> transactions we are running(yes, the transaction logs are set to
> automatically grow). Our application would not send that amount of
> transactions to the database. The other thing I am thinking is that there
> is
> another db within this sqlserver that I think access this DB and I think
> the
> transaction logs would prove this.
> Thanks for your assistance in advance....
|||TJ
Another option to consider is the Bulk-Logged recovery mode. Bulk-logged
has better performance than Full with large bulk inserts and bcp operations
because the transaction log files should not grow as quickly as a result of
only recording complete transactions. In other words, you sacrifice the
ability to recover row by row that might occur with large batches.
Regards,
Keith
This posting is provided "as is" without inferring rights or warranties.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eOihJpswFHA.2076@.TK2MSFTNGP14.phx.gbl...
> What recovery mode are you in? If it is FULL and you don't issue regular
> Log backups it will continue to grow and grow. Change it to simple mode
> and you should not see it grow any more. If you really want to see what
> is happening on the server you should use Profiler or Trace.
> --
> Andrew J. Kelly SQL MVP
>
> "TJ" <TJ@.discussions.microsoft.com> wrote in message
> news:92CF3A8E-4DA0-4E1B-BCA8-EC4491DCAFED@.microsoft.com...
>