Database MSDB in suspect state.

Hi Friends,
MSDB database for my SQL 2000 Enterprise is in SUSPECT state. I have enough free space on disk. I don't see anything particular in Windows event viewer.
Please help.
Regards,
Jamil
what happens if you run a sp_resetstatus 'msdb'?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jamil Ahsan" <JamilAhsan@.discussions.microsoft.com> wrote in message
news:3954BE12-CAD0-41C1-A437-EEC82D4E09E1@.microsoft.com...
> Hi Friends,
> MSDB database for my SQL 2000 Enterprise is in SUSPECT state. I have
enough free space on disk. I don't see anything particular in Windows event
viewer.
> Please help.
> Regards,
> Jamil
>
|||When I run resetstatus I get a warning that I need to recover this database first.
"Hilary Cotter" wrote:

> what happens if you run a sp_resetstatus 'msdb'?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Jamil Ahsan" <JamilAhsan@.discussions.microsoft.com> wrote in message
> news:3954BE12-CAD0-41C1-A437-EEC82D4E09E1@.microsoft.com...
> enough free space on disk. I don't see anything particular in Windows event
> viewer.
>
>
|||Jamil,
I use this checklist to troubleshoot such issues:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
HTH,
Paul Ibison
sql

Database mirroring and raw partitions

I have several servers that I am setting up using raw disk partitions for th
e
data and log storage, and at the same time I am very interested in the new
database mirroring technology in SQL 2005. My question and problem is this,
I can get mirroring to work if I have the logs on a ntfs partition, but if I
place them on a raw partition the mirror goes into a suspended state and the
error logs have the following errors:
----
--
While acting as a mirroring partner for database 'RealTime', server instance
'DATA2' encountered error 5123, status 1, severity 16. Database mirroring
will be suspended. Try to resolve the error and resume mirroring.
Error: 1454, Severity: 16, State: 1.
CREATE FILE encountered operating system error 32(The process cannot access
the file because it is being used by another process.) while attempting to
open or create the physical file 'C:\DriveMapping\RealTime_Log_Disk3\'.
----
--
The raw partitions are set up using mapped folders on a ntfs partition, and
the database can execute queries 20-25% faster than an identical server I
have set up with the data and logs on NTFS partitions.Hi
As you can't do normal OS operations on a RAW partition it does not really
surprise me that this is not possible, but I have not found anything to say
that!!!
What was the sector size on the NTFS volume that did not perform?
John
"MDBVV" wrote:

> I have several servers that I am setting up using raw disk partitions for
the
> data and log storage, and at the same time I am very interested in the new
> database mirroring technology in SQL 2005. My question and problem is thi
s,
> I can get mirroring to work if I have the logs on a ntfs partition, but if
I
> place them on a raw partition the mirror goes into a suspended state and t
he
> error logs have the following errors:
> ----
--
> While acting as a mirroring partner for database 'RealTime', server instan
ce
> 'DATA2' encountered error 5123, status 1, severity 16. Database mirroring
> will be suspended. Try to resolve the error and resume mirroring.
> Error: 1454, Severity: 16, State: 1.
> CREATE FILE encountered operating system error 32(The process cannot acces
s
> the file because it is being used by another process.) while attempting to
> open or create the physical file 'C:\DriveMapping\RealTime_Log_Disk3'.
> ----
--
> The raw partitions are set up using mapped folders on a ntfs partition, an
d
> the database can execute queries 20-25% faster than an identical server I
> have set up with the data and logs on NTFS partitions.
>
>|||Hi
As you can't do normal OS operations on a RAW partition it does not really
surprise me that this is not possible, but I have not found anything to say
that!!!
What was the sector size on the NTFS volume that did not perform?
John
"MDBVV" wrote:

> I have several servers that I am setting up using raw disk partitions for
the
> data and log storage, and at the same time I am very interested in the new
> database mirroring technology in SQL 2005. My question and problem is thi
s,
> I can get mirroring to work if I have the logs on a ntfs partition, but if
I
> place them on a raw partition the mirror goes into a suspended state and t
he
> error logs have the following errors:
> ----
--
> While acting as a mirroring partner for database 'RealTime', server instan
ce
> 'DATA2' encountered error 5123, status 1, severity 16. Database mirroring
> will be suspended. Try to resolve the error and resume mirroring.
> Error: 1454, Severity: 16, State: 1.
> CREATE FILE encountered operating system error 32(The process cannot acces
s
> the file because it is being used by another process.) while attempting to
> open or create the physical file 'C:\DriveMapping\RealTime_Log_Disk3'.
> ----
--
> The raw partitions are set up using mapped folders on a ntfs partition, an
d
> the database can execute queries 20-25% faster than an identical server I
> have set up with the data and logs on NTFS partitions.
>
>

Database mirroring and raw partitions

I have several servers that I am setting up using raw disk partitions for the
data and log storage, and at the same time I am very interested in the new
database mirroring technology in SQL 2005. My question and problem is this,
I can get mirroring to work if I have the logs on a ntfs partition, but if I
place them on a raw partition the mirror goes into a suspended state and the
error logs have the following errors
-----
While acting as a mirroring partner for database 'RealTime', server instance
'DATA2' encountered error 5123, status 1, severity 16. Database mirroring
will be suspended. Try to resolve the error and resume mirroring.
Error: 1454, Severity: 16, State: 1.
CREATE FILE encountered operating system error 32(The process cannot access
the file because it is being used by another process.) while attempting to
open or create the physical file 'C:\DriveMapping\RealTime_Log_Disk3\'
-----
The raw partitions are set up using mapped folders on a ntfs partition, and
the database can execute queries 20-25% faster than an identical server I
have set up with the data and logs on NTFS partitions.Hi
As you can't do normal OS operations on a RAW partition it does not really
surprise me that this is not possible, but I have not found anything to say
that!!!
What was the sector size on the NTFS volume that did not perform?
John
"MDBVV" wrote:
> I have several servers that I am setting up using raw disk partitions for the
> data and log storage, and at the same time I am very interested in the new
> database mirroring technology in SQL 2005. My question and problem is this,
> I can get mirroring to work if I have the logs on a ntfs partition, but if I
> place them on a raw partition the mirror goes into a suspended state and the
> error logs have the following errors:
> -----
> While acting as a mirroring partner for database 'RealTime', server instance
> 'DATA2' encountered error 5123, status 1, severity 16. Database mirroring
> will be suspended. Try to resolve the error and resume mirroring.
> Error: 1454, Severity: 16, State: 1.
> CREATE FILE encountered operating system error 32(The process cannot access
> the file because it is being used by another process.) while attempting to
> open or create the physical file 'C:\DriveMapping\RealTime_Log_Disk3\'.
> -----
> The raw partitions are set up using mapped folders on a ntfs partition, and
> the database can execute queries 20-25% faster than an identical server I
> have set up with the data and logs on NTFS partitions.
>
>

Database Maintenance - Order of Actions

Is there anyway to make the maintenance plan delete the backup files
BEFORE it backs up. My backups are failing due to lack of disk space,
because it essentially creates two copies of backups before deleting.
I know i can just write a script to delete them before hand, but it
would be alot neater to use the maintenace plan because the maintenance
plan is more dynamic that the script. Suggestions?
Kelly
Yes. If you create a backup plan using the Database Maintenance Wizard, you
can set the amount of time you want to keep the backup files. In SQL 2005 it
translates in a Maintenance Cleanup Task.
"KellyLeia@.gmail.com" wrote:

> Is there anyway to make the maintenance plan delete the backup files
> BEFORE it backs up. My backups are failing due to lack of disk space,
> because it essentially creates two copies of backups before deleting.
> I know i can just write a script to delete them before hand, but it
> would be alot neater to use the maintenace plan because the maintenance
> plan is more dynamic that the script. Suggestions?
> Kelly
>
|||"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
<EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.micro soft.com> wrote in message
news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Yes. If you create a backup plan using the Database Maintenance Wizard,
> you
> can set the amount of time you want to keep the backup files. In SQL 2005
> it
> translates in a Maintenance Cleanup Task.
>
Right, 2000 is the same way. But as KellyLeia points out, that deletion
occurs AFTER the backup completes... if successul.
The problem is, if the backup fails, the deletion never occurs.
In addtion, I'd caution against doing what Kelly wants to do. Because if
the backup fails for another reason, you've already deleted one of your
backups.
Personally, I'd try to invest in more disk space if possible.
[vbcol=seagreen]
> "KellyLeia@.gmail.com" wrote:
|||Conviently, we have tape backups that run at 4am (the db backups run
between 1 and 2 am). So if the job fails, we've still got yesterday's
backup on tape. If it fails as it did, i don't get in till 8am, so the
damage is done. Thanks for your help guys.
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
> <EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.micro soft.com> wrote in message
> news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Right, 2000 is the same way. But as KellyLeia points out, that deletion
> occurs AFTER the backup completes... if successul.
> The problem is, if the backup fails, the deletion never occurs.
> In addtion, I'd caution against doing what Kelly wants to do. Because if
> the backup fails for another reason, you've already deleted one of your
> backups.
> Personally, I'd try to invest in more disk space if possible.
>

Database Maintenance - Order of Actions

Is there anyway to make the maintenance plan delete the backup files
BEFORE it backs up. My backups are failing due to lack of disk space,
because it essentially creates two copies of backups before deleting.
I know i can just write a script to delete them before hand, but it
would be alot neater to use the maintenace plan because the maintenance
plan is more dynamic that the script. Suggestions?
KellyYes. If you create a backup plan using the Database Maintenance Wizard, you
can set the amount of time you want to keep the backup files. In SQL 2005 it
translates in a Maintenance Cleanup Task.
"KellyLeia@.gmail.com" wrote:
> Is there anyway to make the maintenance plan delete the backup files
> BEFORE it backs up. My backups are failing due to lack of disk space,
> because it essentially creates two copies of backups before deleting.
> I know i can just write a script to delete them before hand, but it
> would be alot neater to use the maintenace plan because the maintenance
> plan is more dynamic that the script. Suggestions?
> Kelly
>|||"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
<EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Yes. If you create a backup plan using the Database Maintenance Wizard,
> you
> can set the amount of time you want to keep the backup files. In SQL 2005
> it
> translates in a Maintenance Cleanup Task.
>
Right, 2000 is the same way. But as KellyLeia points out, that deletion
occurs AFTER the backup completes... if successul.
The problem is, if the backup fails, the deletion never occurs.
In addtion, I'd caution against doing what Kelly wants to do. Because if
the backup fails for another reason, you've already deleted one of your
backups.
Personally, I'd try to invest in more disk space if possible.
> "KellyLeia@.gmail.com" wrote:
>> Is there anyway to make the maintenance plan delete the backup files
>> BEFORE it backs up. My backups are failing due to lack of disk space,
>> because it essentially creates two copies of backups before deleting.
>> I know i can just write a script to delete them before hand, but it
>> would be alot neater to use the maintenace plan because the maintenance
>> plan is more dynamic that the script. Suggestions?
>> Kelly
>>|||Conviently, we have tape backups that run at 4am (the db backups run
between 1 and 2 am). So if the job fails, we've still got yesterday's
backup on tape. If it fails as it did, i don't get in till 8am, so the
damage is done. Thanks for your help guys.
Greg D. Moore (Strider) wrote:
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
> <EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.microsoft.com> wrote in message
> news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> > Yes. If you create a backup plan using the Database Maintenance Wizard,
> > you
> > can set the amount of time you want to keep the backup files. In SQL 2005
> > it
> > translates in a Maintenance Cleanup Task.
> >
> Right, 2000 is the same way. But as KellyLeia points out, that deletion
> occurs AFTER the backup completes... if successul.
> The problem is, if the backup fails, the deletion never occurs.
> In addtion, I'd caution against doing what Kelly wants to do. Because if
> the backup fails for another reason, you've already deleted one of your
> backups.
> Personally, I'd try to invest in more disk space if possible.
>
> > "KellyLeia@.gmail.com" wrote:
> >
> >> Is there anyway to make the maintenance plan delete the backup files
> >> BEFORE it backs up. My backups are failing due to lack of disk space,
> >> because it essentially creates two copies of backups before deleting.
> >> I know i can just write a script to delete them before hand, but it
> >> would be alot neater to use the maintenace plan because the maintenance
> >> plan is more dynamic that the script. Suggestions?
> >>
> >> Kelly
> >>
> >>

Database Maintenance - Order of Actions

Is there anyway to make the maintenance plan delete the backup files
BEFORE it backs up. My backups are failing due to lack of disk space,
because it essentially creates two copies of backups before deleting.
I know i can just write a script to delete them before hand, but it
would be alot neater to use the maintenace plan because the maintenance
plan is more dynamic that the script. Suggestions?
KellyYes. If you create a backup plan using the Database Maintenance Wizard, you
can set the amount of time you want to keep the backup files. In SQL 2005 it
translates in a Maintenance Cleanup Task.
"KellyLeia@.gmail.com" wrote:

> Is there anyway to make the maintenance plan delete the backup files
> BEFORE it backs up. My backups are failing due to lack of disk space,
> because it essentially creates two copies of backups before deleting.
> I know i can just write a script to delete them before hand, but it
> would be alot neater to use the maintenace plan because the maintenance
> plan is more dynamic that the script. Suggestions?
> Kelly
>|||"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
< EdgardoValdezMCTSMCITPMCSDMCDBA@.discussi
ons.microsoft.com> wrote in message
news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Yes. If you create a backup plan using the Database Maintenance Wizard,
> you
> can set the amount of time you want to keep the backup files. In SQL 2005
> it
> translates in a Maintenance Cleanup Task.
>
Right, 2000 is the same way. But as KellyLeia points out, that deletion
occurs AFTER the backup completes... if successul.
The problem is, if the backup fails, the deletion never occurs.
In addtion, I'd caution against doing what Kelly wants to do. Because if
the backup fails for another reason, you've already deleted one of your
backups.
Personally, I'd try to invest in more disk space if possible.
[vbcol=seagreen]
> "KellyLeia@.gmail.com" wrote:
>|||Conviently, we have tape backups that run at 4am (the db backups run
between 1 and 2 am). So if the job fails, we've still got yesterday's
backup on tape. If it fails as it did, i don't get in till 8am, so the
damage is done. Thanks for your help guys.
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
> < EdgardoValdezMCTSMCITPMCSDMCDBA@.discussi
ons.microsoft.com> wrote in messa
ge
> news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Right, 2000 is the same way. But as KellyLeia points out, that deletion
> occurs AFTER the backup completes... if successul.
> The problem is, if the backup fails, the deletion never occurs.
> In addtion, I'd caution against doing what Kelly wants to do. Because if
> the backup fails for another reason, you've already deleted one of your
> backups.
> Personally, I'd try to invest in more disk space if possible.
>

Database Maint issue

I've got an issue when creating a database maintenance plan. When I get to
the
Specify Backup Disk Directory (in the DB Maintenance Plan Wizard), the
drop-down box for specifying the remove files older than is blank, and the
default timeframe is 0, and the Backup file extension is blank. On other SQL
servers, by default these locations show the drop-down as Week(s), the
timeframe as 4, and the extention as BAK.
If I continue setting up the maintenance plan, when I get to the Specify
Transaction Log Backup Disk Directory, I'll run into the same problem as the
Database (blank and 0 for the same fields).
In any event it will allow me to setup the plans, however I'd like to be
able to set the remove feature and I can't...
Any ideas?
hi,
Darth Blubber wrote:
> I've got an issue when creating a database maintenance plan. When I
> get to the
> Specify Backup Disk Directory (in the DB Maintenance Plan Wizard), the
> drop-down box for specifying the remove files older than is blank,
> and the default timeframe is 0, and the Backup file extension is
> blank. On other SQL servers, by default these locations show the
> drop-down as Week(s), the timeframe as 4, and the extention as BAK.
> If I continue setting up the maintenance plan, when I get to the
> Specify Transaction Log Backup Disk Directory, I'll run into the same
> problem as the Database (blank and 0 for the same fields).
> In any event it will allow me to setup the plans, however I'd like to
> be able to set the remove feature and I can't...
> Any ideas?
this is a long nightmare with Maintenance Plan withard (which actually is
very buggy)... please have a look at
http://groups.google.it/groups?as_q=...0&as_maxy=2005
for a short idea...
I strongly recommend you to write your own SQL Server jobs in
Transact-SQL... this can be a little bit longer than in the wizard,
requiring you to improve your skills, but it will pay in the long terms...
Andrea Montanari
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Database mail attachments are filling the hard disk

My company started using database mail recently.Most emails sent have relatively large attachments. We've set up procedures for removing emails older than a given period of time from the MSDB database, however we've now had a server completely run out of the disk space due to attachments being stored in the C:\Documents and Settings\User\Local Settings\Temp folder!?! sysmail_delete_mailitems_sp sproc only removes entries in the MSDB tables, and doesn't remove these temporary files in the temp directory.

Somebody claims that those temp files are needed in case the file being attached is deleted before it has been sent for example. I'm agree with the procedure, but... Why SQL server does not delete the temporary file after a sucessfull delivery of the message?

It is not too hard to code some job to delete these files daily, but ... Is it too hard for Microsoft Team to develop this simple functionality?

Thanks,

Philip

I think that temp directory is associated to operating system for such actions its better to code a seperate steps to drop those temp files too. Also if you have any anti-virus or anti-spyware tool installed on the server then these will be in exclusive usage hence operating system may not delete them, hence the explicit deletion is required.

I feel this is a good feature to have, you could provide such feedback on http://connect.microsoft.com site in thsi case to involve on next Service pack or in SQL 2008 release.