Losing some data

We had problems with our ERP application since the log file of the SQL serve
r
was full. We only have 1 database in the server. We use Drive D:\ for the lo
g
file. I moved one file to C:\ drive to free some space. This file has nothin
g
to do with the database. Then, we restarted the server. After the server was
up again, I noticed weird things such as:
1. log file (ldf) went down from 9 GB to 2 MB
2. we lost some data in some tables.
My question is:
1. What caused those weird sthings?
2. After the server was up, users have been entering/processing new data. Is
it possible to restore data from the last backup before the server had
problem without deleting the new data?
We don't have a DBA here so we don't have any clue on what to do.
Thanks for replying.Hi
"lwidjaya" wrote:

> We had problems with our ERP application since the log file of the SQL ser
ver
> was full. We only have 1 database in the server. We use Drive D:\ for the
log
> file. I moved one file to C:\ drive to free some space. This file has noth
ing
> to do with the database. Then, we restarted the server. After the server w
as
> up again, I noticed weird things such as:
> 1. log file (ldf) went down from 9 GB to 2 MB
> 2. we lost some data in some tables.
> My question is:
> 1. What caused those weird sthings?
> 2. After the server was up, users have been entering/processing new data.
Is
> it possible to restore data from the last backup before the server had
> problem without deleting the new data?
> We don't have a DBA here so we don't have any clue on what to do.
> Thanks for replying.
When you restarted the server did you do anything else? You should not have
lost any committed data, but uncomitted data may be rolled back.
If you have entered new data it may be better to go back to the old backup
and re-enter it. If you have space then the last backup can be restored as a
database with a different name, and you could use a tool such as SQL Data
Compare or dbghost to see what has changed and then make your decission. You
could do this first on a different server if there is not enough room on the
live environment. These tools do have options to insert the differences from
one database to the other, but if you have triggers and other actions
performed you may need to disable them and be very careful how you add the
information.
The longer you let users add/change data then the harder it will be to go
back.
HTH
John|||Hi John,
thanks for your reply.
I checked the database and since we just had this database since November
2006, we've never done any transaction log backup on it. Is doing back up
using veritas the same as doing backup from EM? We do file backup everynight
using veritas.
I'm planning to run transaction log backup from EM after the scheduled
backup from veritas, will it do any harm to the database? Should we set up
transaction log back up every hour? Is it true that transaction log back up
will shrink log file?
Thanks in advance,
Lisa
"John Bell" wrote:
> When you restarted the server did you do anything else? You should not hav
e
> lost any committed data, but uncomitted data may be rolled back.
> If you have entered new data it may be better to go back to the old backup
> and re-enter it. If you have space then the last backup can be restored as
a
> database with a different name, and you could use a tool such as SQL Data
> Compare or dbghost to see what has changed and then make your decission. Y
ou
> could do this first on a different server if there is not enough room on t
he
> live environment. These tools do have options to insert the differences fr
om
> one database to the other, but if you have triggers and other actions
> performed you may need to disable them and be very careful how you add the
> information.
> The longer you let users add/change data then the harder it will be to go
> back.
> HTH
> John|||Hi Lisa
Veritas is probably using an agent to backup the database, it will
effectively doing a full backup. You could set up your own schedule to do
transaction log backups to disc. My preference is to backup to disc and then
to tape, that way you can always have the most recent backups at hand if you
wish to quickly restore the database. There is a period between doing the
disc backup and putting it onto tape, but if you use a raid disc array this
is reduced.
Transaction log backups themselves will not shrink the file. It does enable
the transaction log file to be re-used so it will limit the size of the file
under normal workloads, and the transaction log should only grow if you have
an abnormally large number of changes. Continually shrinking the data and lo
g
files is not a good idea as it can lead to disc fragmentation of the file
which will impact on performance. Your database should be in full recovery
mode.
John
"lwidjaya" wrote:
[vbcol=seagreen]
> Hi John,
> thanks for your reply.
> I checked the database and since we just had this database since November
> 2006, we've never done any transaction log backup on it. Is doing back up
> using veritas the same as doing backup from EM? We do file backup everynig
ht
> using veritas.
> I'm planning to run transaction log backup from EM after the scheduled
> backup from veritas, will it do any harm to the database? Should we set up
> transaction log back up every hour? Is it true that transaction log back u
p
> will shrink log file?
> Thanks in advance,
> Lisa
> "John Bell" wrote:|||Hi John,
We're running transaction log backup every hour now. As you said, the
transaction log backup didn't shrink the ldf file. Is it ok if we shrink the
database once a while?
Thanks,
Lisa
"John Bell" wrote:
[vbcol=seagreen]
> Hi Lisa
> Veritas is probably using an agent to backup the database, it will
> effectively doing a full backup. You could set up your own schedule to do
> transaction log backups to disc. My preference is to backup to disc and th
en
> to tape, that way you can always have the most recent backups at hand if y
ou
> wish to quickly restore the database. There is a period between doing the
> disc backup and putting it onto tape, but if you use a raid disc array thi
s
> is reduced.
> Transaction log backups themselves will not shrink the file. It does enabl
e
> the transaction log file to be re-used so it will limit the size of the fi
le
> under normal workloads, and the transaction log should only grow if you ha
ve
> an abnormally large number of changes. Continually shrinking the data and
log
> files is not a good idea as it can lead to disc fragmentation of the file
> which will impact on performance. Your database should be in full recovery
> mode.
> John
> "lwidjaya" wrote:
>|||Hi Lisa
It is not a good idea to shrink any of the database files as this can lead
to disc fragmentation and potential degredation of performance. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp, the only exception
may be if you have done something very abnormal, such as mass data
migration/upgrade.
If you make sure that if you have auto expansion on, then the value should
not be a percentage. Alternatively you could turn expansion off and expand
manually when it is a quiet period.
John
"lwidjaya" <lwidjaya@.discussions.microsoft.com> wrote in message
news:3DAF644A-9416-4F05-9986-8A1D929FA0DD@.microsoft.com...[vbcol=seagreen]
> Hi John,
> We're running transaction log backup every hour now. As you said, the
> transaction log backup didn't shrink the ldf file. Is it ok if we shrink
> the
> database once a while?
> Thanks,
> Lisa
> "John Bell" wrote:
>|||> We're running transaction log backup every hour now. As you said, the
> transaction log backup didn't shrink the ldf file. Is it ok if we shrink
> the
> database once a while?
Why? Do you once in a while lease that disk space to some other process?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp|||No, we don't lease the space to other process. We had ldf file less than 1 G
B
before, then our consultant did 'tables reorganizing' and the ldf suddenly
grew to 13.5 GB. We only have 2 GB free space now for the log file. So, I'm
wondering if we can shrink it to get more space, just in case we need the
space in the future.
I have a question, in database property/Taskpad, it shows that the
transaction log space is 13.5 GB, used: 76 MB, and free: 13.4 GB. But how
come the ldf file size shows 13.5 GB? Does it mean the actual size of the ld
f
file is only 76 MB if we shrink it?
Thanks for the replies!
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why? Do you once in a while lease that disk space to some other process?
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
>|||> No, we don't lease the space to other process. We had ldf file less than 1
> GB
> before, then our consultant did 'tables reorganizing' and the ldf suddenly
> grew to 13.5 GB.
Well, that's a special case, and it fits under John Bell's comment
(something very abnormal). I assume your consultant does not reorganize
tables daily?

> I have a question, in database property/Taskpad, it shows that the
> transaction log space is 13.5 GB, used: 76 MB, and free: 13.4 GB. But how
> come the ldf file size shows 13.5 GB? Does it mean the actual size of the
> ldf
> file is only 76 MB if we shrink it?
No, that is not necessarily true. There are multiple virtual log files, and
how the shrink will physically change the physical log file depends on where
any active transactions are stored in the log file.
A|||No, our consultant only did it one time because we made changes to our ERP
data.
So, I guess we can do the database shrink since it's an 'abnormal case'?
Thanks.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Well, that's a special case, and it fits under John Bell's comment
> (something very abnormal). I assume your consultant does not reorganize
> tables daily?
>
> No, that is not necessarily true. There are multiple virtual log files, a
nd
> how the shrink will physically change the physical log file depends on whe
re
> any active transactions are stored in the log file.
> A
>
>

Losing some data

We had problems with our ERP application since the log file of the SQL server
was full. We only have 1 database in the server. We use Drive D:\ for the log
file. I moved one file to C:\ drive to free some space. This file has nothing
to do with the database. Then, we restarted the server. After the server was
up again, I noticed weird things such as:
1. log file (ldf) went down from 9 GB to 2 MB
2. we lost some data in some tables.
My question is:
1. What caused those weird sthings?
2. After the server was up, users have been entering/processing new data. Is
it possible to restore data from the last backup before the server had
problem without deleting the new data?
We don't have a DBA here so we don't have any clue on what to do.
Thanks for replying.
Hi
"lwidjaya" wrote:

> We had problems with our ERP application since the log file of the SQL server
> was full. We only have 1 database in the server. We use Drive D:\ for the log
> file. I moved one file to C:\ drive to free some space. This file has nothing
> to do with the database. Then, we restarted the server. After the server was
> up again, I noticed weird things such as:
> 1. log file (ldf) went down from 9 GB to 2 MB
> 2. we lost some data in some tables.
> My question is:
> 1. What caused those weird sthings?
> 2. After the server was up, users have been entering/processing new data. Is
> it possible to restore data from the last backup before the server had
> problem without deleting the new data?
> We don't have a DBA here so we don't have any clue on what to do.
> Thanks for replying.
When you restarted the server did you do anything else? You should not have
lost any committed data, but uncomitted data may be rolled back.
If you have entered new data it may be better to go back to the old backup
and re-enter it. If you have space then the last backup can be restored as a
database with a different name, and you could use a tool such as SQL Data
Compare or dbghost to see what has changed and then make your decission. You
could do this first on a different server if there is not enough room on the
live environment. These tools do have options to insert the differences from
one database to the other, but if you have triggers and other actions
performed you may need to disable them and be very careful how you add the
information.
The longer you let users add/change data then the harder it will be to go
back.
HTH
John
|||Hi John,
thanks for your reply.
I checked the database and since we just had this database since November
2006, we've never done any transaction log backup on it. Is doing back up
using veritas the same as doing backup from EM? We do file backup everynight
using veritas.
I'm planning to run transaction log backup from EM after the scheduled
backup from veritas, will it do any harm to the database? Should we set up
transaction log back up every hour? Is it true that transaction log back up
will shrink log file?
Thanks in advance,
Lisa
"John Bell" wrote:
> When you restarted the server did you do anything else? You should not have
> lost any committed data, but uncomitted data may be rolled back.
> If you have entered new data it may be better to go back to the old backup
> and re-enter it. If you have space then the last backup can be restored as a
> database with a different name, and you could use a tool such as SQL Data
> Compare or dbghost to see what has changed and then make your decission. You
> could do this first on a different server if there is not enough room on the
> live environment. These tools do have options to insert the differences from
> one database to the other, but if you have triggers and other actions
> performed you may need to disable them and be very careful how you add the
> information.
> The longer you let users add/change data then the harder it will be to go
> back.
> HTH
> John
|||Hi Lisa
Veritas is probably using an agent to backup the database, it will
effectively doing a full backup. You could set up your own schedule to do
transaction log backups to disc. My preference is to backup to disc and then
to tape, that way you can always have the most recent backups at hand if you
wish to quickly restore the database. There is a period between doing the
disc backup and putting it onto tape, but if you use a raid disc array this
is reduced.
Transaction log backups themselves will not shrink the file. It does enable
the transaction log file to be re-used so it will limit the size of the file
under normal workloads, and the transaction log should only grow if you have
an abnormally large number of changes. Continually shrinking the data and log
files is not a good idea as it can lead to disc fragmentation of the file
which will impact on performance. Your database should be in full recovery
mode.
John
"lwidjaya" wrote:
[vbcol=seagreen]
> Hi John,
> thanks for your reply.
> I checked the database and since we just had this database since November
> 2006, we've never done any transaction log backup on it. Is doing back up
> using veritas the same as doing backup from EM? We do file backup everynight
> using veritas.
> I'm planning to run transaction log backup from EM after the scheduled
> backup from veritas, will it do any harm to the database? Should we set up
> transaction log back up every hour? Is it true that transaction log back up
> will shrink log file?
> Thanks in advance,
> Lisa
> "John Bell" wrote:
|||Hi John,
We're running transaction log backup every hour now. As you said, the
transaction log backup didn't shrink the ldf file. Is it ok if we shrink the
database once a while?
Thanks,
Lisa
"John Bell" wrote:
[vbcol=seagreen]
> Hi Lisa
> Veritas is probably using an agent to backup the database, it will
> effectively doing a full backup. You could set up your own schedule to do
> transaction log backups to disc. My preference is to backup to disc and then
> to tape, that way you can always have the most recent backups at hand if you
> wish to quickly restore the database. There is a period between doing the
> disc backup and putting it onto tape, but if you use a raid disc array this
> is reduced.
> Transaction log backups themselves will not shrink the file. It does enable
> the transaction log file to be re-used so it will limit the size of the file
> under normal workloads, and the transaction log should only grow if you have
> an abnormally large number of changes. Continually shrinking the data and log
> files is not a good idea as it can lead to disc fragmentation of the file
> which will impact on performance. Your database should be in full recovery
> mode.
> John
> "lwidjaya" wrote:
|||Hi Lisa
It is not a good idea to shrink any of the database files as this can lead
to disc fragmentation and potential degredation of performance. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp, the only exception
may be if you have done something very abnormal, such as mass data
migration/upgrade.
If you make sure that if you have auto expansion on, then the value should
not be a percentage. Alternatively you could turn expansion off and expand
manually when it is a quiet period.
John
"lwidjaya" <lwidjaya@.discussions.microsoft.com> wrote in message
news:3DAF644A-9416-4F05-9986-8A1D929FA0DD@.microsoft.com...[vbcol=seagreen]
> Hi John,
> We're running transaction log backup every hour now. As you said, the
> transaction log backup didn't shrink the ldf file. Is it ok if we shrink
> the
> database once a while?
> Thanks,
> Lisa
> "John Bell" wrote:
|||> We're running transaction log backup every hour now. As you said, the
> transaction log backup didn't shrink the ldf file. Is it ok if we shrink
> the
> database once a while?
Why? Do you once in a while lease that disk space to some other process?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
|||No, we don't lease the space to other process. We had ldf file less than 1 GB
before, then our consultant did 'tables reorganizing' and the ldf suddenly
grew to 13.5 GB. We only have 2 GB free space now for the log file. So, I'm
wondering if we can shrink it to get more space, just in case we need the
space in the future.
I have a question, in database property/Taskpad, it shows that the
transaction log space is 13.5 GB, used: 76 MB, and free: 13.4 GB. But how
come the ldf file size shows 13.5 GB? Does it mean the actual size of the ldf
file is only 76 MB if we shrink it?
Thanks for the replies!
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why? Do you once in a while lease that disk space to some other process?
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
>
|||> No, we don't lease the space to other process. We had ldf file less than 1
> GB
> before, then our consultant did 'tables reorganizing' and the ldf suddenly
> grew to 13.5 GB.
Well, that's a special case, and it fits under John Bell's comment
(something very abnormal). I assume your consultant does not reorganize
tables daily?

> I have a question, in database property/Taskpad, it shows that the
> transaction log space is 13.5 GB, used: 76 MB, and free: 13.4 GB. But how
> come the ldf file size shows 13.5 GB? Does it mean the actual size of the
> ldf
> file is only 76 MB if we shrink it?
No, that is not necessarily true. There are multiple virtual log files, and
how the shrink will physically change the physical log file depends on where
any active transactions are stored in the log file.
A
|||No, our consultant only did it one time because we made changes to our ERP
data.
So, I guess we can do the database shrink since it's an 'abnormal case'?
Thanks.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Well, that's a special case, and it fits under John Bell's comment
> (something very abnormal). I assume your consultant does not reorganize
> tables daily?
>
> No, that is not necessarily true. There are multiple virtual log files, and
> how the shrink will physically change the physical log file depends on where
> any active transactions are stored in the log file.
> A
>
>

Losing server means data loss even when transaction log is unhurt?

Hi,
I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it is
possible to switch over to the secondary log shipping server and recover up
to the point of failure when the primary data file has failed. But what
happens in all the other failure scenarios when the production log shipping
server is gone, the transaction log file is still available but it's
impossible to backup the last transaction log with the NO_TRUNCATE option
(since the server itself isn't running and probably the master database is
damaged)? Am I bound to lose all the transactions since the last transaction
log backup?
-- Thanks, Oskar.
Oscar
If you cannot run BACKUP LOG on production , you will have to run something
like that
RESTORE DATABASE database_name WITH RECOVERY
EXEC SP_DBOPTION 'database_name', 'read only', 'false'
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:BCCD71E3-BB89-4BEE-B601-03E58ECBFA79@.microsoft.com...
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it
> is
> possible to switch over to the secondary log shipping server and recover
> up
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log
> shipping
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last
> transaction
> log backup?
> -- Thanks, Oskar.
>
|||Oskar wrote:
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it is
> possible to switch over to the secondary log shipping server and recover up
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log shipping
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last transaction
> log backup?
> -- Thanks, Oskar.
>
Yes, you will lose anything that occurred after the last log backup.
Backup as frequently as necessary to minimize the damage - if you can't
afford to lose 15 minutes of data, backup every 5 minutes.
Bringing the standby database online is as simple as running
RESTORE DATABASE standbyDBName WITH RECOVERY
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I think this will answer some of you questions (if I understand the issue
correctly)
http://msdn2.microsoft.com/en-us/library/ms179314.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:BCCD71E3-BB89-4BEE-B601-03E58ECBFA79@.microsoft.com...
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it
> is
> possible to switch over to the secondary log shipping server and recover
> up
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log
> shipping
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last
> transaction
> log backup?
> -- Thanks, Oskar.
>
|||Roger, thank you. Unfortunately this isn't what I'm after. Basically I wanted
to know if it's still possible to recover up to the point of failure in cases
when primary data file of a database and the server to which it was attached
are gone but the transaction log of the database is still intact. If that
happens there is no way I can issue a BACKUP LOG ... WITH NO_TRUNCATE (or any
other command) on the server because it's gone. Also mind that I don't have
MS SQL Server 2005 but 2000.
-- Thanks, Oskar
"Roger Wolter[MSFT]" wrote:

> I think this will answer some of you questions (if I understand the issue
> correctly)
> http://msdn2.microsoft.com/en-us/library/ms179314.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:BCCD71E3-BB89-4BEE-B601-03E58ECBFA79@.microsoft.com...
>
>
|||But you must have a server someplace right? Your log shipping destination?
Can't you do the backup log command from there? Maybe I'm missing something
here.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:DCD111CF-E527-4C22-81EE-E84027ADDCD2@.microsoft.com...[vbcol=seagreen]
> Roger, thank you. Unfortunately this isn't what I'm after. Basically I
> wanted
> to know if it's still possible to recover up to the point of failure in
> cases
> when primary data file of a database and the server to which it was
> attached
> are gone but the transaction log of the database is still intact. If that
> happens there is no way I can issue a BACKUP LOG ... WITH NO_TRUNCATE (or
> any
> other command) on the server because it's gone. Also mind that I don't
> have
> MS SQL Server 2005 but 2000.
> -- Thanks, Oskar
> "Roger Wolter[MSFT]" wrote:
|||Here's the KB http://support.microsoft.com/kb/253817/en-us
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OJr4L3yOHHA.4644@.TK2MSFTNGP03.phx.gbl...
> Yes, that should be possible:
> On some healthy SQL Server, you create a new database. Stop that SQL
> Server. Delete the two database files. "Slide" in your log file (ldf) from
> the production SQL Server where the log file were for this newly created
> database. Start this new SQL Server. Do the log backup (with NO_TRUNCATE).
> I believe that there's a KB describing this (search and you should find),
> but the steps are pretty straight forward.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:DCD111CF-E527-4C22-81EE-E84027ADDCD2@.microsoft.com...
>
|||Roger,
Log shipping destination of course would be available and I would be able to
do log backups there. The point is that log shipping destination would be
behind log shipping source (i.e. production database) in regard to the latest
transactions that happened between the time last backup was made on the
source and copied to the destination and the time of failure of the source.
So if the source is lost and I'm not able to make the last backup of those
transactions (with NO_TRUNCATE option), because the server itself is also
nonfunctional, then I'm losing those transactions which is unacceptable.
Sorry Roger, I can't explain it any better. Tibor seems to have got the point.
-- Thanks, Oskar.
"Roger Wolter[MSFT]" wrote:

> But you must have a server someplace right? Your log shipping destination?
> Can't you do the backup log command from there? Maybe I'm missing something
> here.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:DCD111CF-E527-4C22-81EE-E84027ADDCD2@.microsoft.com...
>
>
|||Thanks Roger. I think this is the one.
"Roger Wolter[MSFT]" wrote:

> Here's the KB http://support.microsoft.com/kb/253817/en-us
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OJr4L3yOHHA.4644@.TK2MSFTNGP03.phx.gbl...
>
>
sql

Losing server means data loss even when transaction log is unhurt?

Hi,
I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it is
possible to switch over to the secondary log shipping server and recover up
to the point of failure when the primary data file has failed. But what
happens in all the other failure scenarios when the production log shipping
server is gone, the transaction log file is still available but it's
impossible to backup the last transaction log with the NO_TRUNCATE option
(since the server itself isn't running and probably the master database is
damaged)? Am I bound to lose all the transactions since the last transaction
log backup?
-- Thanks, Oskar.Oscar
If you cannot run BACKUP LOG on production , you will have to run something
like that
RESTORE DATABASE database_name WITH RECOVERY
EXEC SP_DBOPTION 'database_name', 'read only', 'false'
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:BCCD71E3-BB89-4BEE-B601-03E58ECBFA79@.microsoft.com...
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it
> is
> possible to switch over to the secondary log shipping server and recover
> up
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log
> shipping
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last
> transaction
> log backup?
> -- Thanks, Oskar.
>|||Oskar wrote:
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it
is
> possible to switch over to the secondary log shipping server and recover u
p
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log shippin
g
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last transacti
on
> log backup?
> -- Thanks, Oskar.
>
Yes, you will lose anything that occurred after the last log backup.
Backup as frequently as necessary to minimize the damage - if you can't
afford to lose 15 minutes of data, backup every 5 minutes.
Bringing the standby database online is as simple as running
RESTORE DATABASE standbyDBName WITH RECOVERY
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I think this will answer some of you questions (if I understand the issue
correctly)
http://msdn2.microsoft.com/en-us/library/ms179314.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:BCCD71E3-BB89-4BEE-B601-03E58ECBFA79@.microsoft.com...
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it
> is
> possible to switch over to the secondary log shipping server and recover
> up
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log
> shipping
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last
> transaction
> log backup?
> -- Thanks, Oskar.
>

Loosing transactions

Hi,
My application is loosing some transactions fired from a trigger, this lost is because CPU usage is very high abut I cant find any log that indicates the lost transactions.
Regards.
This probably is not the best place for this post. Try:
microsoft.public.sqlserver.server
Rand
This posting is provided "as is" with no warranties and confers no rights.

looping through query result column and PRINT to log file....

i am creating a log file, and at 1 point I record how many rows are deleted after an update process.. I want to also record which productID are actually deleted... how would I write that?!

Basically I need know how I would get the list, and I am using PRINT command... I need to somehow write a loop that works through the list and PRINTS to the file...

Thanks in advanceI got it :)

For any1 who might be interested heres how its done

DECLARE @.NbrList VarChar(300)
SELECT @.NbrList = COALESCE(@.NbrList + ', ', '') + CAST(Invoice AS varchar(30))
from TRANSACTIONS where ProductID = 'CVSDBN'
PRINT @.NbrList

Thanks ;)sql

looking for script to backup Transaction log and delete any thing older than 24 hours

looking for script to backup Transaction log and delete any thing older than 24 hoursLook at Tracy's suggestion at:
http://www.realsqlguy.com/serendipity/archives/9-Out-With-The-Old.html#extended
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Omran Abdurrahman" <omranab@.comcast.net> wrote in message
news:OaYXTC9%23GHA.4472@.TK2MSFTNGP05.phx.gbl...
>

looking for script to backup Transaction log and delete any thing older than 24 hours

looking for script to backup Transaction log and delete any thing older than
24 hoursOften, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Posted to .tools
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Omran Abdurrahman" <omranab@.comcast.net> wrote in message
news:ux9eBD9%23GHA.2408@.TK2MSFTNGP05.phx.gbl...
> looking for script to backup Transaction log and delete any thing older
> than 24 hours
>|||Omran Abdurrahman wrote:
> looking for script to backup Transaction log and delete any thing older than
> 24 hours
>
Start here:
http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Omran,
If you are using SQL 2005, look at the new XP_Delete_File stored
procedure. Otherwise, Tracy is on the right track. You can store your
backups in a table and run cmd queries with xp_cmdshell to delete files
older than your threshold. This does not need to be a persisted table.
You can also use the dir command to populate a temp table, and delete
based off that.
Delete bak files over 46 hours old
Declare @.deletedate nvarchar(255). @.Path nvarchar(200)
set @.Path = '\\fileserver\db_backup\'
set @.deletedate = cast(dateadd(hh,-46,getdate()) as varchar(255))
EXECUTE master.dbo.xp_delete_file 0,@.Path,N'bak',@.deletedate
Personally I would disregard the comments from Arnie Rowland. It is too
bad he does not offer constructive comments.
Terry
Tracy McKibben wrote:
> Omran Abdurrahman wrote:
> > looking for script to backup Transaction log and delete any thing older than
> > 24 hours
> >
> >
> Start here:
> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

looking for script to backup Transaction log and delete any thing older than 24 hours

looking for script to backup Transaction log and delete any thing older than
24 hoursOften, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Posted to .tools
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Omran Abdurrahman" <omranab@.comcast.net> wrote in message
news:ux9eBD9%23GHA.2408@.TK2MSFTNGP05.phx.gbl...
> looking for script to backup Transaction log and delete any thing older
> than 24 hours
>|||Omran,
If you are using SQL 2005, look at the new XP_Delete_File stored
procedure. Otherwise, Tracy is on the right track. You can store your
backups in a table and run cmd queries with xp_cmdshell to delete files
older than your threshold. This does not need to be a persisted table.
You can also use the dir command to populate a temp table, and delete
based off that.
Delete bak files over 46 hours old
Declare @.deletedate nvarchar(255). @.Path nvarchar(200)
set @.Path = '\\fileserver\db_backup'
set @.deletedate = cast(dateadd(hh,-46,getdate()) as varchar(255))
EXECUTE master.dbo.xp_delete_file 0,@.Path,N'bak',@.deletedate
Personally I would disregard the comments from Arnie Rowland. It is too
bad he does not offer constructive comments.
Terry
Tracy McKibben wrote:
> Omran Abdurrahman wrote:
> Start here:
> http://realsqlguy.com/serendipity/a...-Send-In-Backup!.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com