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
>
>