DATABASE Becomes suspect

I have a SQL 7.0 Clustered Server with SP3. Sometimes a DB (16GB) becomes
suspect and the only way to get it back online is to reboot the server and
that gets it back to normal. This happens every week.
Any ideas?
Also can you tell me how to change the location where Microsoft Clustered
Server changes the Temporary file location. Currently on my server it is
c:\winnt which we want to change
Thanks
Rod
Hey Rod, great name!
I am of no help really, but I can tell you I run 18 SQL clusters with
several Databases way larger then 16GB and we never see this. We are running
SQL 2000 on Windows Server 2003 with various hardware. If you can migrate to
a newer config, that may solve the problem.
Now, for one of the real SQL experts to help you, do you have any log events
or error messages or stats of what is going on when this occurs? Does it
happen on all the nodes? Anything else you can give will help.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Rod" <Rod@.discussions.microsoft.com> wrote in message
news:77254067-96EE-46D3-BA60-EB9BA92C0D75@.microsoft.com...
>I have a SQL 7.0 Clustered Server with SP3. Sometimes a DB (16GB) becomes
> suspect and the only way to get it back online is to reboot the server and
> that gets it back to normal. This happens every week.
> Any ideas?
> Also can you tell me how to change the location where Microsoft Clustered
> Server changes the Temporary file location. Currently on my server it is
> c:\winnt which we want to change
> Thanks
> Rod
|||Hey Rodney,
My name is Rodney too. I agree Great Name.
Well here are some details/comments about the below issue:
Two things I saw on the event log were
1). The C drive was out of space. we were constantly getting the following
error in the system log:
" The Microsoft Cluster could write file (c:\winnt\CLSF9D.TMP). The Disk may
be low in space or some serious other condition exists "
The Qurorm Drive is the Q Drive.
My question is why is Cluster Server trying to write temp files to the
c:\winnt\ folder.
Can we change this location and if so how?
Thanks for your help
Rod
"Rodney R. Fournier [MVP]" wrote:

> Hey Rod, great name!
> I am of no help really, but I can tell you I run 18 SQL clusters with
> several Databases way larger then 16GB and we never see this. We are running
> SQL 2000 on Windows Server 2003 with various hardware. If you can migrate to
> a newer config, that may solve the problem.
> Now, for one of the real SQL experts to help you, do you have any log events
> or error messages or stats of what is going on when this occurs? Does it
> happen on all the nodes? Anything else you can give will help.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://www.msmvps.com/clustering - Blog
> "Rod" <Rod@.discussions.microsoft.com> wrote in message
> news:77254067-96EE-46D3-BA60-EB9BA92C0D75@.microsoft.com...
>
>
|||Each node has its own cluster log and temp space. Why was your C drive
running out of space? Have you fixed that yet?
I am not sure how to change the WINNT temp folder that clustering writes to.
Rod
"Rod" <Rod@.discussions.microsoft.com> wrote in message
news:84068A17-BE1E-44CB-BD06-597F33A5BE91@.microsoft.com...[vbcol=seagreen]
> Hey Rodney,
> My name is Rodney too. I agree Great Name.
> Well here are some details/comments about the below issue:
> Two things I saw on the event log were
> 1). The C drive was out of space. we were constantly getting the following
> error in the system log:
> " The Microsoft Cluster could write file (c:\winnt\CLSF9D.TMP). The Disk
> may
> be low in space or some serious other condition exists "
> The Qurorm Drive is the Q Drive.
> My question is why is Cluster Server trying to write temp files to the
> c:\winnt\ folder.
> Can we change this location and if so how?
> Thanks for your help
> Rod
> "Rodney R. Fournier [MVP]" wrote:

Database Backup in SQL Server failover Clustering.

Hi,
I have done MS SQL Server failover clustering with SQL Server 2000
as
One Active node and one Passive Node.
I have set G: drive as a Clustered Drive where Database Files are
placed.
Now i want to take a backup of the database locally on Non-Clustered
Disk .
Is it possible to do this. If yes then please let me know How?
Please give me reply.
Thanks & Regards,
Sajid N. Chhapekar.yes it is possible like any other backup
"csajid@.gmail.com" wrote:
> Hi,
> I have done MS SQL Server failover clustering with SQL Server 2000
> as
> One Active node and one Passive Node.
> I have set G: drive as a Clustered Drive where Database Files are
> placed.
> Now i want to take a backup of the database locally on Non-Clustered
> Disk .
> Is it possible to do this. If yes then please let me know How?
> Please give me reply.
> Thanks & Regards,
> Sajid N. Chhapekar.
>|||Hi,
Thanks for your reply.
I have create a backup job which will take a full database backup
at 9:00 PM every night.
But my concern here is that, the backup will happened on local disk
of node which is online but if that node goes offline then the backup
will happened on another node which is online or not.
this is my please reply to this as it is production issue.
Thanks & Regards,
Sajid N. Chhapekar.
Aleksandar Grbic wrote:
> yes it is possible like any other backup
>
> "csajid@.gmail.com" wrote:
> > Hi,
> >
> > I have done MS SQL Server failover clustering with SQL Server 2000
> > as
> > One Active node and one Passive Node.
> > I have set G: drive as a Clustered Drive where Database Files are
> > placed.
> > Now i want to take a backup of the database locally on Non-Clustered
> > Disk .
> > Is it possible to do this. If yes then please let me know How?
> >
> > Please give me reply.
> >
> > Thanks & Regards,
> > Sajid N. Chhapekar.
> >
> >|||I am not quite sure I understand what you are asking, but I'll try to
respond.
I assume you have your data files on your G:\ drive which is a shared drive
that is accessible to the cluster and part of a cluster group.
You want to backup your database to a local drive other than that g:\ drive.
So you want to back up to a non-shared drive such as the c:\ drive?
I would not suggest that. I would suggest that you backup your databases to
a separate shared array that is clustered and in your cluster group.
HTH
Mike Walsh
SQL Server DBA
mwalsh9815(nospam-at-)gmail
<csajid@.gmail.com> wrote in message
news:1151137838.512224.314790@.p79g2000cwp.googlegroups.com...
> Hi,
> Thanks for your reply.
> I have create a backup job which will take a full database backup
> at 9:00 PM every night.
> But my concern here is that, the backup will happened on local disk
> of node which is online but if that node goes offline then the backup
> will happened on another node which is online or not.
> this is my please reply to this as it is production issue.
> Thanks & Regards,
> Sajid N. Chhapekar.
> Aleksandar Grbic wrote:
>> yes it is possible like any other backup
>>
>> "csajid@.gmail.com" wrote:
>> > Hi,
>> >
>> > I have done MS SQL Server failover clustering with SQL Server 2000
>> > as
>> > One Active node and one Passive Node.
>> > I have set G: drive as a Clustered Drive where Database Files are
>> > placed.
>> > Now i want to take a backup of the database locally on Non-Clustered
>> > Disk .
>> > Is it possible to do this. If yes then please let me know How?
>> >
>> > Please give me reply.
>> >
>> > Thanks & Regards,
>> > Sajid N. Chhapekar.
>> >
>> >
>|||do not use your backup strategy for production.
if first node go offline, your backup go offline too
create backup on cluster disk group
--
Aleksandar Grbic
"csajid@.gmail.com" wrote:
> Hi,
> Thanks for your reply.
> I have create a backup job which will take a full database backup
> at 9:00 PM every night.
> But my concern here is that, the backup will happened on local disk
> of node which is online but if that node goes offline then the backup
> will happened on another node which is online or not.
> this is my please reply to this as it is production issue.
> Thanks & Regards,
> Sajid N. Chhapekar.
> Aleksandar Grbic wrote:
> > yes it is possible like any other backup
> >
> >
> > "csajid@.gmail.com" wrote:
> >
> > > Hi,
> > >
> > > I have done MS SQL Server failover clustering with SQL Server 2000
> > > as
> > > One Active node and one Passive Node.
> > > I have set G: drive as a Clustered Drive where Database Files are
> > > placed.
> > > Now i want to take a backup of the database locally on Non-Clustered
> > > Disk .
> > > Is it possible to do this. If yes then please let me know How?
> > >
> > > Please give me reply.
> > >
> > > Thanks & Regards,
> > > Sajid N. Chhapekar.
> > >
> > >
>|||Hi,
Thanks for your reply.
you mean to say that, if my Node goes offline then my backup will
not switch to the
node which is online.
If yes, then is there any way to take a backup on Local disk as my
seniors want to
do this.
Thanks & Regards,
Sajid N. Chhapekar.
Aleksandar Grbic wrote:
> do not use your backup strategy for production.
> if first node go offline, your backup go offline too
> create backup on cluster disk group
> --
> Aleksandar Grbic
>
> "csajid@.gmail.com" wrote:
> > Hi,
> >
> > Thanks for your reply.
> >
> > I have create a backup job which will take a full database backup
> > at 9:00 PM every night.
> > But my concern here is that, the backup will happened on local disk
> > of node which is online but if that node goes offline then the backup
> > will happened on another node which is online or not.
> > this is my please reply to this as it is production issue.
> >
> > Thanks & Regards,
> > Sajid N. Chhapekar.
> > Aleksandar Grbic wrote:
> > > yes it is possible like any other backup
> > >
> > >
> > > "csajid@.gmail.com" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have done MS SQL Server failover clustering with SQL Server 2000
> > > > as
> > > > One Active node and one Passive Node.
> > > > I have set G: drive as a Clustered Drive where Database Files are
> > > > placed.
> > > > Now i want to take a backup of the database locally on Non-Clustered
> > > > Disk .
> > > > Is it possible to do this. If yes then please let me know How?
> > > >
> > > > Please give me reply.
> > > >
> > > > Thanks & Regards,
> > > > Sajid N. Chhapekar.
> > > >
> > > >
> >
> >|||<csajid@.gmail.com> wrote in message
news:1151397825.548700.255990@.b68g2000cwa.googlegroups.com...
> Hi,
> Thanks for your reply.
> you mean to say that, if my Node goes offline then my backup will
> not switch to the
> node which is online.
> If yes, then is there any way to take a backup on Local disk as my
> seniors want to
> do this.
If the backup job points to say C:\backup, it should always save it to the
local C: drive of the active node.
If you always want it to a specific node, create a share and use the UNC
path: \\node2\backup.
However, I wouldn't do this OR to another disk on the cluster.
I'd backup to a disk on a separate machine. For example, we've got a Snap
4200 that we backup to a UNC on.
> Thanks & Regards,
> Sajid N. Chhapekar.
>
> Aleksandar Grbic wrote:
> > do not use your backup strategy for production.
> > if first node go offline, your backup go offline too
> >
> > create backup on cluster disk group
> > --
> > Aleksandar Grbic
> >
> >
> >
> > "csajid@.gmail.com" wrote:
> >
> > > Hi,
> > >
> > > Thanks for your reply.
> > >
> > > I have create a backup job which will take a full database backup
> > > at 9:00 PM every night.
> > > But my concern here is that, the backup will happened on local
disk
> > > of node which is online but if that node goes offline then the backup
> > > will happened on another node which is online or not.
> > > this is my please reply to this as it is production issue.
> > >
> > > Thanks & Regards,
> > > Sajid N. Chhapekar.
> > > Aleksandar Grbic wrote:
> > > > yes it is possible like any other backup
> > > >
> > > >
> > > > "csajid@.gmail.com" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have done MS SQL Server failover clustering with SQL Server
2000
> > > > > as
> > > > > One Active node and one Passive Node.
> > > > > I have set G: drive as a Clustered Drive where Database Files
are
> > > > > placed.
> > > > > Now i want to take a backup of the database locally on
Non-Clustered
> > > > > Disk .
> > > > > Is it possible to do this. If yes then please let me know How?
> > > > >
> > > > > Please give me reply.
> > > > >
> > > > > Thanks & Regards,
> > > > > Sajid N. Chhapekar.
> > > > >
> > > > >
> > >
> > >
>|||Hi,
Thanks for your reply.
Now, i try to make it more simple.
I have to nodes in a Active/Passive Cluster.
Where node A is Passive and Node B is Active.
I have same drive on both the nodes that is, C: D: E: Q: and G:
where G: is clustered
disk & Of course here Q: is Quorum disk.
Here i schedule daily backup on E: drive, which is same location
on both the nodes.
as my node B is active the backup is pointing to E: drive of node
B.
Now my point here is if my node B becomes Passive and node A
active then backup
will point to E: drive of Node A or it fails.
Thanks & Regards,
Sajid N.Chhapekar.
Greg D. Moore (Strider) wrote:
> <csajid@.gmail.com> wrote in message
> news:1151397825.548700.255990@.b68g2000cwa.googlegroups.com...
> > Hi,
> >
> > Thanks for your reply.
> > you mean to say that, if my Node goes offline then my backup will
> > not switch to the
> > node which is online.
> > If yes, then is there any way to take a backup on Local disk as my
> > seniors want to
> > do this.
> If the backup job points to say C:\backup, it should always save it to the
> local C: drive of the active node.
> If you always want it to a specific node, create a share and use the UNC
> path: \\node2\backup.
> However, I wouldn't do this OR to another disk on the cluster.
> I'd backup to a disk on a separate machine. For example, we've got a Snap
> 4200 that we backup to a UNC on.
>
> >
> > Thanks & Regards,
> > Sajid N. Chhapekar.
> >
> >
> > Aleksandar Grbic wrote:
> > > do not use your backup strategy for production.
> > > if first node go offline, your backup go offline too
> > >
> > > create backup on cluster disk group
> > > --
> > > Aleksandar Grbic
> > >
> > >
> > >
> > > "csajid@.gmail.com" wrote:
> > >
> > > > Hi,
> > > >
> > > > Thanks for your reply.
> > > >
> > > > I have create a backup job which will take a full database backup
> > > > at 9:00 PM every night.
> > > > But my concern here is that, the backup will happened on local
> disk
> > > > of node which is online but if that node goes offline then the backup
> > > > will happened on another node which is online or not.
> > > > this is my please reply to this as it is production issue.
> > > >
> > > > Thanks & Regards,
> > > > Sajid N. Chhapekar.
> > > > Aleksandar Grbic wrote:
> > > > > yes it is possible like any other backup
> > > > >
> > > > >
> > > > > "csajid@.gmail.com" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have done MS SQL Server failover clustering with SQL Server
> 2000
> > > > > > as
> > > > > > One Active node and one Passive Node.
> > > > > > I have set G: drive as a Clustered Drive where Database Files
> are
> > > > > > placed.
> > > > > > Now i want to take a backup of the database locally on
> Non-Clustered
> > > > > > Disk .
> > > > > > Is it possible to do this. If yes then please let me know How?
> > > > > >
> > > > > > Please give me reply.
> > > > > >
> > > > > > Thanks & Regards,
> > > > > > Sajid N. Chhapekar.
> > > > > >
> > > > > >
> > > >
> > > >
> >|||You really should have the backup on shared (clustered) disks, not local disks. Say that E: is a
local disk which exists on both node A and B. The backups will work fine, and store the backup file
on the local drive (which one depends on what node is the active node). The database engine doesn't
really know or care whether it is clustered, it just sees a number of drives.
But here's a scenario which might make you re-consider:
Node A does backup locally.
It then fails, and you also have to do a restore.
Node B is started and you cannot get to the backup file produced by node A, because that node has
failed and stored the backup locally.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<csajid@.gmail.com> wrote in message news:1151484909.281425.75170@.p79g2000cwp.googlegroups.com...
> Hi,
> Thanks for your reply.
> Now, i try to make it more simple.
> I have to nodes in a Active/Passive Cluster.
> Where node A is Passive and Node B is Active.
> I have same drive on both the nodes that is, C: D: E: Q: and G:
> where G: is clustered
> disk & Of course here Q: is Quorum disk.
> Here i schedule daily backup on E: drive, which is same location
> on both the nodes.
> as my node B is active the backup is pointing to E: drive of node
> B.
> Now my point here is if my node B becomes Passive and node A
> active then backup
> will point to E: drive of Node A or it fails.
> Thanks & Regards,
> Sajid N.Chhapekar.
> Greg D. Moore (Strider) wrote:
>> <csajid@.gmail.com> wrote in message
>> news:1151397825.548700.255990@.b68g2000cwa.googlegroups.com...
>> > Hi,
>> >
>> > Thanks for your reply.
>> > you mean to say that, if my Node goes offline then my backup will
>> > not switch to the
>> > node which is online.
>> > If yes, then is there any way to take a backup on Local disk as my
>> > seniors want to
>> > do this.
>> If the backup job points to say C:\backup, it should always save it to the
>> local C: drive of the active node.
>> If you always want it to a specific node, create a share and use the UNC
>> path: \\node2\backup.
>> However, I wouldn't do this OR to another disk on the cluster.
>> I'd backup to a disk on a separate machine. For example, we've got a Snap
>> 4200 that we backup to a UNC on.
>>
>> >
>> > Thanks & Regards,
>> > Sajid N. Chhapekar.
>> >
>> >
>> > Aleksandar Grbic wrote:
>> > > do not use your backup strategy for production.
>> > > if first node go offline, your backup go offline too
>> > >
>> > > create backup on cluster disk group
>> > > --
>> > > Aleksandar Grbic
>> > >
>> > >
>> > >
>> > > "csajid@.gmail.com" wrote:
>> > >
>> > > > Hi,
>> > > >
>> > > > Thanks for your reply.
>> > > >
>> > > > I have create a backup job which will take a full database backup
>> > > > at 9:00 PM every night.
>> > > > But my concern here is that, the backup will happened on local
>> disk
>> > > > of node which is online but if that node goes offline then the backup
>> > > > will happened on another node which is online or not.
>> > > > this is my please reply to this as it is production issue.
>> > > >
>> > > > Thanks & Regards,
>> > > > Sajid N. Chhapekar.
>> > > > Aleksandar Grbic wrote:
>> > > > > yes it is possible like any other backup
>> > > > >
>> > > > >
>> > > > > "csajid@.gmail.com" wrote:
>> > > > >
>> > > > > > Hi,
>> > > > > >
>> > > > > > I have done MS SQL Server failover clustering with SQL Server
>> 2000
>> > > > > > as
>> > > > > > One Active node and one Passive Node.
>> > > > > > I have set G: drive as a Clustered Drive where Database Files
>> are
>> > > > > > placed.
>> > > > > > Now i want to take a backup of the database locally on
>> Non-Clustered
>> > > > > > Disk .
>> > > > > > Is it possible to do this. If yes then please let me know How?
>> > > > > >
>> > > > > > Please give me reply.
>> > > > > >
>> > > > > > Thanks & Regards,
>> > > > > > Sajid N. Chhapekar.
>> > > > > >
>> > > > > >
>> > > >
>> > > >
>> >
>|||Hi,
Thanks for reply.
I understand what u are trying to say.
In this case, as we have windows backup stratergy which backup the
the folder where backup file is stored.
So, that if one node fails where backup file is stored, we can get
it from backup tape.
Thanks & Regards,
Sajid N. Chhapekar.
Tibor Karaszi wrote:
> You really should have the backup on shared (clustered) disks, not local disks. Say that E: is a
> local disk which exists on both node A and B. The backups will work fine, and store the backup file
> on the local drive (which one depends on what node is the active node). The database engine doesn't
> really know or care whether it is clustered, it just sees a number of drives.
> But here's a scenario which might make you re-consider:
> Node A does backup locally.
> It then fails, and you also have to do a restore.
> Node B is started and you cannot get to the backup file produced by node A, because that node has
> failed and stored the backup locally.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <csajid@.gmail.com> wrote in message news:1151484909.281425.75170@.p79g2000cwp.googlegroups.com...
> > Hi,
> >
> > Thanks for your reply.
> >
> > Now, i try to make it more simple.
> > I have to nodes in a Active/Passive Cluster.
> > Where node A is Passive and Node B is Active.
> > I have same drive on both the nodes that is, C: D: E: Q: and G:
> > where G: is clustered
> > disk & Of course here Q: is Quorum disk.
> > Here i schedule daily backup on E: drive, which is same location
> > on both the nodes.
> > as my node B is active the backup is pointing to E: drive of node
> > B.
> > Now my point here is if my node B becomes Passive and node A
> > active then backup
> > will point to E: drive of Node A or it fails.
> >
> > Thanks & Regards,
> > Sajid N.Chhapekar.
> >
> > Greg D. Moore (Strider) wrote:
> >> <csajid@.gmail.com> wrote in message
> >> news:1151397825.548700.255990@.b68g2000cwa.googlegroups.com...
> >> > Hi,
> >> >
> >> > Thanks for your reply.
> >> > you mean to say that, if my Node goes offline then my backup will
> >> > not switch to the
> >> > node which is online.
> >> > If yes, then is there any way to take a backup on Local disk as my
> >> > seniors want to
> >> > do this.
> >>
> >> If the backup job points to say C:\backup, it should always save it to the
> >> local C: drive of the active node.
> >>
> >> If you always want it to a specific node, create a share and use the UNC
> >> path: \\node2\backup.
> >>
> >> However, I wouldn't do this OR to another disk on the cluster.
> >>
> >> I'd backup to a disk on a separate machine. For example, we've got a Snap
> >> 4200 that we backup to a UNC on.
> >>
> >>
> >> >
> >> > Thanks & Regards,
> >> > Sajid N. Chhapekar.
> >> >
> >> >
> >> > Aleksandar Grbic wrote:
> >> > > do not use your backup strategy for production.
> >> > > if first node go offline, your backup go offline too
> >> > >
> >> > > create backup on cluster disk group
> >> > > --
> >> > > Aleksandar Grbic
> >> > >
> >> > >
> >> > >
> >> > > "csajid@.gmail.com" wrote:
> >> > >
> >> > > > Hi,
> >> > > >
> >> > > > Thanks for your reply.
> >> > > >
> >> > > > I have create a backup job which will take a full database backup
> >> > > > at 9:00 PM every night.
> >> > > > But my concern here is that, the backup will happened on local
> >> disk
> >> > > > of node which is online but if that node goes offline then the backup
> >> > > > will happened on another node which is online or not.
> >> > > > this is my please reply to this as it is production issue.
> >> > > >
> >> > > > Thanks & Regards,
> >> > > > Sajid N. Chhapekar.
> >> > > > Aleksandar Grbic wrote:
> >> > > > > yes it is possible like any other backup
> >> > > > >
> >> > > > >
> >> > > > > "csajid@.gmail.com" wrote:
> >> > > > >
> >> > > > > > Hi,
> >> > > > > >
> >> > > > > > I have done MS SQL Server failover clustering with SQL Server
> >> 2000
> >> > > > > > as
> >> > > > > > One Active node and one Passive Node.
> >> > > > > > I have set G: drive as a Clustered Drive where Database Files
> >> are
> >> > > > > > placed.
> >> > > > > > Now i want to take a backup of the database locally on
> >> Non-Clustered
> >> > > > > > Disk .
> >> > > > > > Is it possible to do this. If yes then please let me know How?
> >> > > > > >
> >> > > > > > Please give me reply.
> >> > > > > >
> >> > > > > > Thanks & Regards,
> >> > > > > > Sajid N. Chhapekar.
> >> > > > > >
> >> > > > > >
> >> > > >
> >> > > >
> >> >
> >

Database Backup in 0.5 B and Transaction log backup in GBs

SQL gurus,
Simple question:
Our database is 470MB and transaction log has become 18GB! (Clustered). I
see local Fixed drives in both the SQL boxes from 'System Information' and
disk manager where data is being stored.
We are backing up database once in 24 hours and transaction logs every two
hours during business hours and full transaction log at night.
Simple Question is if we are backing full transaction log at night then
isn't supposed to make the transaction log to "zero" bytes or say clear the
transaction log automatically? After all when transaction logs have been
backup then why the transaction log continue to grow'
If the database is crashed what would be the sequence to recover:
Restore database in restore mode then restore first transaction log backup
taken after full backup of database and then restore the next backup taken
of transaction log and so on. (to apply in sequence starting from the first
one backup of transaction log taken).
Now question is :
Situation #1
1) if database is crashed and we still have transaction logs then can we
restore transaction logs (not backup of transaction logs) at the end of
procedure to restore as given above'
Situation #2
2) database is crashed and we do not have transaction logs then we can
restore as given in the procedure above except that in the last stage we
keep "no restore mode"'
Thanks
MeiNarendra
All of these issues are described on BOL very well.
Please refer to BOL.
"Narendra Talreja" <ntalreja@.no_spam_comcast.net> wrote in message
news:XpOcnbaESOUAmhiiXTWJhQ@.comcast.com...
> SQL gurus,
> Simple question:
> Our database is 470MB and transaction log has become 18GB! (Clustered). I
> see local Fixed drives in both the SQL boxes from 'System Information' and
> disk manager where data is being stored.
> We are backing up database once in 24 hours and transaction logs every two
> hours during business hours and full transaction log at night.
> Simple Question is if we are backing full transaction log at night then
> isn't supposed to make the transaction log to "zero" bytes or say clear
the
> transaction log automatically? After all when transaction logs have been
> backup then why the transaction log continue to grow'
> If the database is crashed what would be the sequence to recover:
> Restore database in restore mode then restore first transaction log backup
> taken after full backup of database and then restore the next backup taken
> of transaction log and so on. (to apply in sequence starting from the
first
> one backup of transaction log taken).
> Now question is :
> Situation #1
> 1) if database is crashed and we still have transaction logs then can we
> restore transaction logs (not backup of transaction logs) at the end of
> procedure to restore as given above'
> Situation #2
> 2) database is crashed and we do not have transaction logs then we can
> restore as given in the procedure above except that in the last stage we
> keep "no restore mode"'
> Thanks
> Mei
>|||see inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Narendra Talreja" <ntalreja@.no_spam_comcast.net> wrote in message
news:XpOcnbaESOUAmhiiXTWJhQ@.comcast.com...
> SQL gurus,
> Simple question:
> Our database is 470MB and transaction log has become 18GB! (Clustered). I
> see local Fixed drives in both the SQL boxes from 'System Information' and
> disk manager where data is being stored.
> We are backing up database once in 24 hours and transaction logs every two
> hours during business hours and full transaction log at night.
> Simple Question is if we are backing full transaction log at night then
> isn't supposed to make the transaction log to "zero" bytes or say clear
the
> transaction log automatically?
Backing up the log does truncate the log( making space available for
re-use), but it does not shrink the log. Long running transactions can cause
the log to grow larger than you intended. After backing up the log, you may
use dbcc shrinkfile on the log file to reduce its physical size.
After all when transaction logs have been
> backup then why the transaction log continue to grow'
The only explanation I can think of is long running transactions...
> If the database is crashed what would be the sequence to recover:
>
set the db to dbo use only and kick off all users
backup the log with truncate_only
Restore the last whole data backup
restore each transaction log in the proper sequence.
> Restore database in restore mode then restore first transaction log backup
> taken after full backup of database and then restore the next backup taken
> of transaction log and so on. (to apply in sequence starting from the
first
> one backup of transaction log taken).
> Now question is :
> Situation #1
> 1) if database is crashed and we still have transaction logs then can we
> restore transaction logs (not backup of transaction logs) at the end of
> procedure to restore as given above'
No, restore the log backups
> Situation #2
> 2) database is crashed and we do not have transaction logs then we can
> restore as given in the procedure above except that in the last stage we
> keep "no restore mode"'
Yes, you may restore the whole database backup and allow recovery to run.
The database will be available, but you will have lost all of the data which
was in the transaction logs which you did not restore.
> Thanks
> Mei
>