Database Overwritten: cluster

Showing posts with label cluster. Show all posts

Wednesday, March 21, 2012

Database Mirror in a SQL cluster

I read that you can implement DB mirror in a cluster. However, someone else
said that you cannot. Does anyone have any ideas which is correct?
I found this
Database Mirroring and Clustering
The most important contrast between database mirroring and failover
clustering is the level at which each provides its redundancy. Database
mirroring provides protection at the database level, whereas clustering
provides protection at the server instance level. Another important
difference is that in database mirroring, the principal and mirror servers
are separate SQL Server instances with distinct names, whereas a SQL Server
instance on a cluster gets one virtual server name and IP address that
remains the same no matter what node of the cluster is hosting the instance.
If you need database protection at the server level (for example, your
application requires access to many databases on the same database server
simultaneously), failover clustering may be a more appropriate choice.
However, if you are concerned to provide availability for one database at a
time, database mirroring has a number of advantages.
Unlike clustering, database mirroring does not require proprietary hardware
and does not have a potential failure point with shared storage. Database
mirroring brings the standby database into service much faster than any other
high availability technology, and works well with new capabilities in ADO.NET
and SQL Native Access Client for client-side failover.
You cannot use database mirroring within a cluster, but you may consider
using database mirroring as a method for creating a hot standby for a cluster
instance database. If you do, be forewarned that because a cluster failover
is longer than the timeout value on database mirroring, a High Availability
mode mirroring session will react to a cluster failover as a failure of the
principal server. It would then put the cluster node into a mirroring state.
So the above answers my question. Since we have implemented SQL cluster
already, there is really no need to implement DB mirroring. Also since we
cannot have DB mirroring in a cluster.
"SAM" wrote:

> I read that you can implement DB mirror in a cluster. However, someone else
> said that you cannot. Does anyone have any ideas which is correct?
|||I think what they mean is that you can not use database mirroring in
place of shared storage when creating a MSCS SQL Cluster. You can
however set up database mirroring as an additional level of protection
to mirror from your SQL cluster to another SQL server outside of the
cluster for a extra level of redundancy - possible as a DR solution.
David A. Bermingham, MCSE, MCSA:Messaging
Senior Systems Engineer
www.steeleye.com
SAM wrote:[vbcol=seagreen]
> I found this
> Database Mirroring and Clustering
> The most important contrast between database mirroring and failover
> clustering is the level at which each provides its redundancy. Database
> mirroring provides protection at the database level, whereas clustering
> provides protection at the server instance level. Another important
> difference is that in database mirroring, the principal and mirror servers
> are separate SQL Server instances with distinct names, whereas a SQL Server
> instance on a cluster gets one virtual server name and IP address that
> remains the same no matter what node of the cluster is hosting the instance.
> If you need database protection at the server level (for example, your
> application requires access to many databases on the same database server
> simultaneously), failover clustering may be a more appropriate choice.
> However, if you are concerned to provide availability for one database at a
> time, database mirroring has a number of advantages.
> Unlike clustering, database mirroring does not require proprietary hardware
> and does not have a potential failure point with shared storage. Database
> mirroring brings the standby database into service much faster than any other
> high availability technology, and works well with new capabilities in ADO.NET
> and SQL Native Access Client for client-side failover.
> You cannot use database mirroring within a cluster, but you may consider
> using database mirroring as a method for creating a hot standby for a cluster
> instance database. If you do, be forewarned that because a cluster failover
> is longer than the timeout value on database mirroring, a High Availability
> mode mirroring session will react to a cluster failover as a failure of the
> principal server. It would then put the cluster node into a mirroring state.
> So the above answers my question. Since we have implemented SQL cluster
> already, there is really no need to implement DB mirroring. Also since we
> cannot have DB mirroring in a cluster.
> "SAM" wrote:

Sunday, March 11, 2012

Database Maintenance Plans - Not removing Files Older than x days

I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
database on the server and have created a database maintenance plan that
backups up all the database nightly. I have also configured the maintenance
plan to delete back files older than 3 days old but this isn't currently
happening.
I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
seen this problem first. Or if anyone can confirm SP4 fixes this issue.You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
SP4 on another system to make sure your apps work correctly first, of course.
Have you checked the various logs SQL Server uses? There's a SQL Server Log,
a SQL Server Agent Log, and the Windows Application Event Log. They might
point out what is happening.
That being said, there is an extended stored procedure that Microsoft uses
to delete the files. If the Agent service doesn't have the proper rights to
the subdirectory you might find that to be the issue.
"Gary S" wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>|||What is the extended store procedure that's used so that I can check it
hasn't been removed.
"Buck Woody - Microsoft SQL Server Team" wrote:
> You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> SP4 on another system to make sure your apps work correctly first, of course.
> Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> a SQL Server Agent Log, and the Windows Application Event Log. They might
> point out what is happening.
> That being said, there is an extended stored procedure that Microsoft uses
> to delete the files. If the Agent service doesn't have the proper rights to
> the subdirectory you might find that to be the issue.
> "Gary S" wrote:
> > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > database on the server and have created a database maintenance plan that
> > backups up all the database nightly. I have also configured the maintenance
> > plan to delete back files older than 3 days old but this isn't currently
> > happening.
> >
> > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> >
> >|||It's sp_delete_file - and it's undocumented, meaning unsupported for direct
use by users. Check this thread:
https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
"Gary S" wrote:
> What is the extended store procedure that's used so that I can check it
> hasn't been removed.
> "Buck Woody - Microsoft SQL Server Team" wrote:
> > You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> > SP4 on another system to make sure your apps work correctly first, of course.
> >
> > Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> > a SQL Server Agent Log, and the Windows Application Event Log. They might
> > point out what is happening.
> >
> > That being said, there is an extended stored procedure that Microsoft uses
> > to delete the files. If the Agent service doesn't have the proper rights to
> > the subdirectory you might find that to be the issue.
> >
> > "Gary S" wrote:
> >
> > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > > database on the server and have created a database maintenance plan that
> > > backups up all the database nightly. I have also configured the maintenance
> > > plan to delete back files older than 3 days old but this isn't currently
> > > happening.
> > >
> > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> > >
> > >|||Slight correction - that's XP_delete_file. Had an S in there!
"Buck Woody - Microsoft SQL Server Team" wrote:
> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
> > What is the extended store procedure that's used so that I can check it
> > hasn't been removed.
> >
> > "Buck Woody - Microsoft SQL Server Team" wrote:
> >
> > > You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> > > SP4 on another system to make sure your apps work correctly first, of course.
> > >
> > > Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> > > a SQL Server Agent Log, and the Windows Application Event Log. They might
> > > point out what is happening.
> > >
> > > That being said, there is an extended stored procedure that Microsoft uses
> > > to delete the files. If the Agent service doesn't have the proper rights to
> > > the subdirectory you might find that to be the issue.
> > >
> > > "Gary S" wrote:
> > >
> > > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > > > database on the server and have created a database maintenance plan that
> > > > backups up all the database nightly. I have also configured the maintenance
> > > > plan to delete back files older than 3 days old but this isn't currently
> > > > happening.
> > > >
> > > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > > > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> > > >
> > > >|||On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
There are a couple of common causes for this:
1. The maintenance plan is configured to do transaction log backups,
but one of the databases included is in Simple mode. The plan fails
when it hits this database, and never reaches the "cleanup" step.
2. One of the files to be deleted is "in use" by another process,
such as a tape backup, causing the cleanup process to fail.
Right-click on the maintenance plan itself (not the Agent job), look
at the history, you should be able to get an actual error message from
there.|||You have to be a sysadmin to run this stored proc, even in SP2 CTP. I have
been told a fix will be raised for a later service pack.
Chris
"Buck Woody - Microsoft SQL Server Team"
<BuckWoodyMicrosoftSQLServerTeam@.discussions.microsoft.com> wrote in message
news:0751636B-0F16-40EA-BAE8-8ADE57217570@.microsoft.com...
> It's sp_delete_file - and it's undocumented, meaning unsupported for
> direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
>> What is the extended store procedure that's used so that I can check it
>> hasn't been removed.
>> "Buck Woody - Microsoft SQL Server Team" wrote:
>> > You'll want to apply SP4 anyway, for lots of other reasons. Test the
>> > setup of
>> > SP4 on another system to make sure your apps work correctly first, of
>> > course.
>> >
>> > Have you checked the various logs SQL Server uses? There's a SQL Server
>> > Log,
>> > a SQL Server Agent Log, and the Windows Application Event Log. They
>> > might
>> > point out what is happening.
>> >
>> > That being said, there is an extended stored procedure that Microsoft
>> > uses
>> > to delete the files. If the Agent service doesn't have the proper
>> > rights to
>> > the subdirectory you might find that to be the issue.
>> >
>> > "Gary S" wrote:
>> >
>> > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a
>> > > number of
>> > > database on the server and have created a database maintenance plan
>> > > that
>> > > backups up all the database nightly. I have also configured the
>> > > maintenance
>> > > plan to delete back files older than 3 days old but this isn't
>> > > currently
>> > > happening.
>> > >
>> > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone
>> > > else has
>> > > seen this problem first. Or if anyone can confirm SP4 fixes this
>> > > issue.
>> > >
>> > >|||> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users.
Buck,
Above is for 2005. For 2000, the files are removed by sqlmaint.exe (which is called by xp_sqlmaint
if the plan is created by the wizard).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Buck Woody - Microsoft SQL Server Team" <BuckWoodyMicrosoftSQLServerTeam@.discussions.microsoft.com>
wrote in message news:0751636B-0F16-40EA-BAE8-8ADE57217570@.microsoft.com...
> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
>> What is the extended store procedure that's used so that I can check it
>> hasn't been removed.
>> "Buck Woody - Microsoft SQL Server Team" wrote:
>> > You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
>> > SP4 on another system to make sure your apps work correctly first, of course.
>> >
>> > Have you checked the various logs SQL Server uses? There's a SQL Server Log,
>> > a SQL Server Agent Log, and the Windows Application Event Log. They might
>> > point out what is happening.
>> >
>> > That being said, there is an extended stored procedure that Microsoft uses
>> > to delete the files. If the Agent service doesn't have the proper rights to
>> > the subdirectory you might find that to be the issue.
>> >
>> > "Gary S" wrote:
>> >
>> > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
>> > > database on the server and have created a database maintenance plan that
>> > > backups up all the database nightly. I have also configured the maintenance
>> > > plan to delete back files older than 3 days old but this isn't currently
>> > > happening.
>> > >
>> > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
>> > > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>> > >
>> > >|||Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
big fan of database mainatenance plans (perfer setting up the jobs
individual) so as I'm not 100% certain why the databases are set to simple
I'll create seperate backup jobs.
"Tracy McKibben" wrote:
> On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > database on the server and have created a database maintenance plan that
> > backups up all the database nightly. I have also configured the maintenance
> > plan to delete back files older than 3 days old but this isn't currently
> > happening.
> >
> > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> There are a couple of common causes for this:
> 1. The maintenance plan is configured to do transaction log backups,
> but one of the databases included is in Simple mode. The plan fails
> when it hits this database, and never reaches the "cleanup" step.
> 2. One of the files to be deleted is "in use" by another process,
> such as a tape backup, causing the cleanup process to fail.
> Right-click on the maintenance plan itself (not the Agent job), look
> at the history, you should be able to get an actual error message from
> there.
>|||On Feb 15, 5:13 am, Gary S <G...@.discussions.microsoft.com> wrote:
> Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
> big fan of database mainatenance plans (perfer setting up the jobs
> individual) so as I'm not 100% certain why the databases are set to simple
> I'll create seperate backup jobs.
>
Here's a script of mine that might help you out:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html

Database Maintenance Plans - Not removing Files Older than x days

Saturday, February 25, 2012

Database mail running on cluster error

we are running Sql 2005 enterprise edition sp1 on a 2 node cluster.When we send a test message via database mail we recieve the following error

Date 3/7/2007 9:11:18 AM
Log Database Mail (Database Mail Log)

Log ID 5
Process ID 2892
Last Modified 3/7/2007 9:11:18 AM
Last Modified By JDG\pssqlservice

Message
1) Exception Information
===================
Exception Type: System.NullReferenceException
Message: Object reference not set to an instance of an object.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32)
HelpLink: NULL
Source: DatabaseMailEngine

StackTrace Information
===================
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.SessionManager.GetAccount(Int32 accountID)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)

Does anybody know what could be the problem here?

Just a guess here: No known FROM address provided...|||we do have a reply address configured in the batabase mail configuration.so it is not this

Friday, February 17, 2012

'Database Loading' state after Restore

Hi,
I have a Windows Server 2003 cluster setup with 3 SQL
Server 2000 virtual servers installed. When I perform a
restore of an 85 GB database, the database gets stuck in
a 'Database Loading' state. It doesn't appear to be a
resource issue since SQL has access to all 8 processors
and 4 GB of memory and the system is not under stress. I
have checked the SQL and Event Viewer logs for clues but
have found nothing to pursue. Any assistance is greatly
appreciated.
Sincerely,
ABGABG,
Did you get a message that the database restore finished
successfully (even though it is 'loading' state')?
During the restore process, you must the with 'RECOVERY'
OPTION.
If the restore is finished, you could do the following to
recover the database:
RESTORE DATABASE_NAME
WITH RECOVERY
hth
DeeJay
>--Original Message--
>Hi,
> I have a Windows Server 2003 cluster setup with 3 SQL
>Server 2000 virtual servers installed. When I perform a
>restore of an 85 GB database, the database gets stuck in
>a 'Database Loading' state. It doesn't appear to be a
>resource issue since SQL has access to all 8 processors
>and 4 GB of memory and the system is not under stress. I
>have checked the SQL and Event Viewer logs for clues but
>have found nothing to pursue. Any assistance is greatly
>appreciated.
> Sincerely,
> ABG
>.
>|||Yes, it stated that the recovery completed. Although the
database stayed in Loading state. I'm running the restore
from Enterprise Manager, is there a way to specify
the 'Recovery' option from here? Or must the restore be
performed from Query Analyzer? Thank you for your reply.
Sincerely,
ABG
>--Original Message--
>ABG,
>Did you get a message that the database restore finished
>successfully (even though it is 'loading' state')?
>During the restore process, you must the with 'RECOVERY'
>OPTION.
>If the restore is finished, you could do the following to
>recover the database:
>RESTORE DATABASE_NAME
>WITH RECOVERY
>hth
>DeeJay
>>--Original Message--
>>Hi,
>> I have a Windows Server 2003 cluster setup with 3 SQL
>>Server 2000 virtual servers installed. When I perform a
>>restore of an 85 GB database, the database gets stuck in
>>a 'Database Loading' state. It doesn't appear to be a
>>resource issue since SQL has access to all 8 processors
>>and 4 GB of memory and the system is not under stress. I
>>have checked the SQL and Event Viewer logs for clues but
>>have found nothing to pursue. Any assistance is greatly
>>appreciated.
>> Sincerely,
>> ABG
>>.
>.
>|||In EM, right.most tab (at the bottom), you have option for
RECOVERY/NORECOVERY/STANDBY. Default is, however RECOVERY on the last backup
(the topmost alternative). The command shown by DeeJay to recovery without
doing an actual restore is not available from EM.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ABG" <anonymous@.discussions.microsoft.com> wrote in message
news:03c101c3a8a0$d4e30f70$a401280a@.phx.gbl...
> Yes, it stated that the recovery completed. Although the
> database stayed in Loading state. I'm running the restore
> from Enterprise Manager, is there a way to specify
> the 'Recovery' option from here? Or must the restore be
> performed from Query Analyzer? Thank you for your reply.
> Sincerely,
> ABG
> >--Original Message--
> >ABG,
> >
> >Did you get a message that the database restore finished
> >successfully (even though it is 'loading' state')?
> >
> >During the restore process, you must the with 'RECOVERY'
> >OPTION.
> >
> >If the restore is finished, you could do the following to
> >recover the database:
> >
> >RESTORE DATABASE_NAME
> >WITH RECOVERY
> >
> >hth
> >
> >DeeJay
> >>--Original Message--
> >>Hi,
> >> I have a Windows Server 2003 cluster setup with 3 SQL
> >>Server 2000 virtual servers installed. When I perform a
> >>restore of an 85 GB database, the database gets stuck in
> >>a 'Database Loading' state. It doesn't appear to be a
> >>resource issue since SQL has access to all 8 processors
> >>and 4 GB of memory and the system is not under stress. I
> >>have checked the SQL and Event Viewer logs for clues but
> >>have found nothing to pursue. Any assistance is greatly
> >>appreciated.
> >> Sincerely,
> >> ABG
> >>
> >>.
> >>
> >.
> >

Tuesday, February 14, 2012

'Database Loading' state after Restore

I have a SQL 2000 Ent. SP3 on W2k3 cluster.
Every time when I restore a db from EM ( Use : leave database operational ...) it starts restoring, I se NO progression on the progressbar, and then it says: Successfully restored ...
And the db is gray and says Loading. I cant acess it in any way. No errors in any logs.
The db I now have problem with restores ok on another system.
I havent had this problem with small db's. The one I'm trying now is 9GB.try RESTORE DATABASE ... WITH RECOVERY option
"Bjørn" <anonymous@.discussions.microsoft.com> wrote in message
news:6DDC2092-7FCC-4C57-9759-812AAA5CEB84@.microsoft.com...
> I have a SQL 2000 Ent. SP3 on W2k3 cluster.
> Every time when I restore a db from EM ( Use : leave database operational
...) it starts restoring, I se NO progression on the progressbar, and then
it says: Successfully restored ...
> And the db is gray and says Loading. I cant acess it in any way. No errors
in any logs.
> The db I now have problem with restores ok on another system.
> I havent had this problem with small db's. The one I'm trying now is 9GB.
>
>|||Says :
File 'db_data' was only partially restored by a database or file restore. The entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
And trying to get the properties of the db says:
Error 927. Database '...' cannot be opened. It is in the middle of a restore.

'Database Loading' state after Restore

quote:

> I have a SQL 2000 Ent. SP3 on W2k3 cluster.
> Every time when I restore a db from EM ( Use : leave database operational

...) it starts restoring, I se NO progression on the progressbar, and then
it says: Successfully restored ...

quote:

> And the db is gray and says Loading. I cant acess it in any way. No errors

in any logs.

quote:

> The db I now have problem with restores ok on another system.
> I havent had this problem with small db's. The one I'm trying now is 9GB.
>
>

|||Says :
File 'db_data' was only partially restored by a database or file restore. Th
e entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
And trying to get the properties of the db says:
Error 927. Database '...' cannot be opened. It is in the middle of a restor
e.

Wednesday, March 21, 2012

Database Mirror in a SQL cluster

Sunday, March 11, 2012

Database Maintenance Plans - Not removing Files Older than x days

Database Maintenance Plans - Not removing Files Older than x days

Database Maintenance Plans - Not removing Files Older than x days

Saturday, February 25, 2012

Database mail running on cluster error

Friday, February 17, 2012

'Database Loading' state after Restore

Tuesday, February 14, 2012

'Database Loading' state after Restore

'Database Loading' state after Restore

Database Overwritten

Blog Archive

About Me