losing data from tables

Hi!
Is there any known problem with MSSQL 7.0 losing data from tables?
I have an application that is used by 150 seperate customer and each
customer have about 10 users. The customers have theirs database at a
server administrated by me. The application access its database through
ODBC. In maintenanse plan I have a short-time backup of the databases
three times a day (8:30, 11:30, 14:30) and a 7 days backup every day at
19:00.
Sometime a stange malfunction occur. Two tables are empty. The last 7
days backup from yeasterday is ok but when they started using the
application next morning the two tables are empty. I is always the same
two tables. This malfunction happens one to two times per year.
any proposal?
Best regards,
Bertil MorefältBertil,
To know what is happening you'd need to be able to view the activity for the
database. This can be done by a trace (Profiler) and or by viewing the
activities contained in the transaction log (third-party log tool). Might
check out:
http://www.lumigent.com/products/le_sql.html
for the future. This type of tool may allow you to "undo" the deletes as
well.
HTH
Jerry
"Bertil Morefalt" <bertil@.community.nospam> wrote in message
news:OxDh9qbyFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi!
> Is there any known problem with MSSQL 7.0 losing data from tables?
> I have an application that is used by 150 seperate customer and each
> customer have about 10 users. The customers have theirs database at a
> server administrated by me. The application access its database through
> ODBC. In maintenanse plan I have a short-time backup of the databases
> three times a day (8:30, 11:30, 14:30) and a 7 days backup every day at
> 19:00.
> Sometime a stange malfunction occur. Two tables are empty. The last 7 days
> backup from yeasterday is ok but when they started using the application
> next morning the two tables are empty. I is always the same two tables.
> This malfunction happens one to two times per year.
> any proposal?
> Best regards,
> Bertil Morefält|||Hi Bertil,
It's really strange that two tables will become empty and I believe some
operations must be done. You may follow the Jerry's suggestion using
Profiler to trace the SQL actions.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I've recently had some data issues with my MS SQL Server 2000. From
time to time, we will lose some data from tables here and there. I
don't know what's causing it, but it's a pretty decent size database.
We won't loose a whole tables' worth, but some data.
About 50 tables, some of the tables, such as our: vendors, leads,
contacts, manufacturers can have up to 12,000 records, but that's about it.
About Jerry's suggestion.. Does Microsoft have any profilers for their
own products or do you always have to go third party? Thanks.
Marc F.
Michael Cheng [MSFT] wrote:
> Hi Bertil,
> It's really strange that two tables will become empty and I believe some
> operations must be done. You may follow the Jerry's suggestion using
> Profiler to trace the SQL actions.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||You can use SQL Profiler which comes with SQL Server.
-Sue
On Fri, 20 Jan 2006 15:31:59 -0500, Marc Ferguson
<marc@.digitalalias.net> wrote:
>Hi,
>I've recently had some data issues with my MS SQL Server 2000. From
>time to time, we will lose some data from tables here and there. I
>don't know what's causing it, but it's a pretty decent size database.
>We won't loose a whole tables' worth, but some data.
>About 50 tables, some of the tables, such as our: vendors, leads,
>contacts, manufacturers can have up to 12,000 records, but that's about it.
>About Jerry's suggestion.. Does Microsoft have any profilers for their
>own products or do you always have to go third party? Thanks.
>Marc F.
>
>Michael Cheng [MSFT] wrote:
>> Hi Bertil,
>> It's really strange that two tables will become empty and I believe some
>> operations must be done. You may follow the Jerry's suggestion using
>> Profiler to trace the SQL actions.
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no rights.|||Hey Sue,
Thanks. I didn't know it had that. I'm playing with it now.. is there
any way for this to automatically run in the background or do I always
have to start it up?
Marc F.
Sue Hoegemeier wrote:
> You can use SQL Profiler which comes with SQL Server.
> -Sue
> On Fri, 20 Jan 2006 15:31:59 -0500, Marc Ferguson
> <marc@.digitalalias.net> wrote:
>
>>Hi,
>>I've recently had some data issues with my MS SQL Server 2000. From
>>time to time, we will lose some data from tables here and there. I
>>don't know what's causing it, but it's a pretty decent size database.
>>We won't loose a whole tables' worth, but some data.
>>About 50 tables, some of the tables, such as our: vendors, leads,
>>contacts, manufacturers can have up to 12,000 records, but that's about it.
>>About Jerry's suggestion.. Does Microsoft have any profilers for their
>>own products or do you always have to go third party? Thanks.
>>Marc F.
>>
>>Michael Cheng [MSFT] wrote:
>>Hi Bertil,
>>It's really strange that two tables will become empty and I believe some
>>operations must be done. You may follow the Jerry's suggestion using
>>Profiler to trace the SQL actions.
>>
>>Sincerely yours,
>>Michael Cheng
>>Microsoft Online Partner Support
>>When responding to posts, please "Reply to Group" via your newsreader so
>>that others may learn and benefit from your issue.
>>=====================================================>>This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Marc,
Sure...You can run the same thing that Profiler runs using
server side traces - produces a trc file that you can open
in profiler or you can view as well as import into a table
using fn_trace_gettable.
The quickest way to get started with it is to just use
Profiler to create the trace you want. Then in Profiler, go
to File and select Script Trace. This will create the server
side trace for you - all you would really need to change on
the script is the destination file for the trace. You can
execute traces in jobs if you want.
If you execute the entire script you generated with
Profiler, it will start up the trace. You can see in the
script how the tracing is done with the trace stored
procedures. The stored procedures for traces are named
sp_trace_xxx.
There are also a few fn_trace_xxx trace functions you can
use as well. To get information on all traces that are
running, use:
SELECT * FROM :: fn_trace_getinfo(default)
You get the trace id when you execute this which can help
you in using the other functions, stored procedures that use
the trace id for a parameter (such as sp_trace_setstatus).
Hope that helps get you going with it -
-Sue
On Fri, 20 Jan 2006 16:57:31 -0500, Marc Ferguson
<marc@.digitalalias.net> wrote:
>Hey Sue,
>Thanks. I didn't know it had that. I'm playing with it now.. is there
>any way for this to automatically run in the background or do I always
>have to start it up?
>Marc F.
>Sue Hoegemeier wrote:
>> You can use SQL Profiler which comes with SQL Server.
>> -Sue
>> On Fri, 20 Jan 2006 15:31:59 -0500, Marc Ferguson
>> <marc@.digitalalias.net> wrote:
>>
>>Hi,
>>I've recently had some data issues with my MS SQL Server 2000. From
>>time to time, we will lose some data from tables here and there. I
>>don't know what's causing it, but it's a pretty decent size database.
>>We won't loose a whole tables' worth, but some data.
>>About 50 tables, some of the tables, such as our: vendors, leads,
>>contacts, manufacturers can have up to 12,000 records, but that's about it.
>>About Jerry's suggestion.. Does Microsoft have any profilers for their
>>own products or do you always have to go third party? Thanks.
>>Marc F.
>>
>>Michael Cheng [MSFT] wrote:
>>Hi Bertil,
>>It's really strange that two tables will become empty and I believe some
>>operations must be done. You may follow the Jerry's suggestion using
>>Profiler to trace the SQL actions.
>>
>>Sincerely yours,
>>Michael Cheng
>>Microsoft Online Partner Support
>>When responding to posts, please "Reply to Group" via your newsreader so
>>that others may learn and benefit from your issue.
>>=====================================================>>This posting is provided "AS IS" with no warranties, and confers no rights.
>>|||Hi Sue,
Thanks for that info. I was wondering if there are any tutorials or
docs on Profiler? I have "Sams Teach Yourself Microsoft SQL Server 2000
in 21 Days", but it only mentions profiler. I was playing with it and a
lot of the events and their displayed data doesn't make sense to me.
I'm trying them one-by-one to see what kind of result they show after I
do something related to our database. As you probably know.. it's
becoming a long process. :D
Marc F.
Sue Hoegemeier wrote:
> Hi Marc,
> Sure...You can run the same thing that Profiler runs using
> server side traces - produces a trc file that you can open
> in profiler or you can view as well as import into a table
> using fn_trace_gettable.
> The quickest way to get started with it is to just use
> Profiler to create the trace you want. Then in Profiler, go
> to File and select Script Trace. This will create the server
> side trace for you - all you would really need to change on
> the script is the destination file for the trace. You can
> execute traces in jobs if you want.
> If you execute the entire script you generated with
> Profiler, it will start up the trace. You can see in the
> script how the tracing is done with the trace stored
> procedures. The stored procedures for traces are named
> sp_trace_xxx.
> There are also a few fn_trace_xxx trace functions you can
> use as well. To get information on all traces that are
> running, use:
> SELECT * FROM :: fn_trace_getinfo(default)
> You get the trace id when you execute this which can help
> you in using the other functions, stored procedures that use
> the trace id for a parameter (such as sp_trace_setstatus).
> Hope that helps get you going with it -
> -Sue
> On Fri, 20 Jan 2006 16:57:31 -0500, Marc Ferguson
> <marc@.digitalalias.net> wrote:
>
>>Hey Sue,
>>Thanks. I didn't know it had that. I'm playing with it now.. is there
>>any way for this to automatically run in the background or do I always
>>have to start it up?
>>Marc F.
>>Sue Hoegemeier wrote:
>>You can use SQL Profiler which comes with SQL Server.
>>-Sue
>>On Fri, 20 Jan 2006 15:31:59 -0500, Marc Ferguson
>><marc@.digitalalias.net> wrote:
>>
>>Hi,
>>I've recently had some data issues with my MS SQL Server 2000. From
>>time to time, we will lose some data from tables here and there. I
>>don't know what's causing it, but it's a pretty decent size database.
>>We won't loose a whole tables' worth, but some data.
>>About 50 tables, some of the tables, such as our: vendors, leads,
>>contacts, manufacturers can have up to 12,000 records, but that's about it.
>>About Jerry's suggestion.. Does Microsoft have any profilers for their
>>own products or do you always have to go third party? Thanks.
>>Marc F.
>>
>>Michael Cheng [MSFT] wrote:
>>
>>Hi Bertil,
>>It's really strange that two tables will become empty and I believe some
>>operations must be done. You may follow the Jerry's suggestion using
>>Profiler to trace the SQL actions.
>>
>>Sincerely yours,
>>Michael Cheng
>>Microsoft Online Partner Support
>>When responding to posts, please "Reply to Group" via your newsreader so
>>that others may learn and benefit from your issue.
>>=====================================================>>This posting is provided "AS IS" with no warranties, and confers no rights.
>>
>|||Marc,
SQL Magazine had some good articles on Profiler - you can
try searching for those at: www.sqlmag.com
This web cast may help:
Support WebCast: SQL Server 2000 Profiler: What's New and
How to Effectively Use It
http://support.microsoft.com/default.aspx?id=325263
And you can find Profiler tips at:
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
-Sue
On Mon, 23 Jan 2006 09:49:02 -0500, Marc Ferguson
<marc@.digitalalias.net> wrote:
>Hi Sue,
>Thanks for that info. I was wondering if there are any tutorials or
>docs on Profiler? I have "Sams Teach Yourself Microsoft SQL Server 2000
>in 21 Days", but it only mentions profiler. I was playing with it and a
>lot of the events and their displayed data doesn't make sense to me.
>I'm trying them one-by-one to see what kind of result they show after I
>do something related to our database. As you probably know.. it's
>becoming a long process. :D
>Marc F.
>Sue Hoegemeier wrote:
>> Hi Marc,
>> Sure...You can run the same thing that Profiler runs using
>> server side traces - produces a trc file that you can open
>> in profiler or you can view as well as import into a table
>> using fn_trace_gettable.
>> The quickest way to get started with it is to just use
>> Profiler to create the trace you want. Then in Profiler, go
>> to File and select Script Trace. This will create the server
>> side trace for you - all you would really need to change on
>> the script is the destination file for the trace. You can
>> execute traces in jobs if you want.
>> If you execute the entire script you generated with
>> Profiler, it will start up the trace. You can see in the
>> script how the tracing is done with the trace stored
>> procedures. The stored procedures for traces are named
>> sp_trace_xxx.
>> There are also a few fn_trace_xxx trace functions you can
>> use as well. To get information on all traces that are
>> running, use:
>> SELECT * FROM :: fn_trace_getinfo(default)
>> You get the trace id when you execute this which can help
>> you in using the other functions, stored procedures that use
>> the trace id for a parameter (such as sp_trace_setstatus).
>> Hope that helps get you going with it -
>> -Sue
>> On Fri, 20 Jan 2006 16:57:31 -0500, Marc Ferguson
>> <marc@.digitalalias.net> wrote:
>>
>>Hey Sue,
>>Thanks. I didn't know it had that. I'm playing with it now.. is there
>>any way for this to automatically run in the background or do I always
>>have to start it up?
>>Marc F.
>>Sue Hoegemeier wrote:
>>You can use SQL Profiler which comes with SQL Server.
>>-Sue
>>On Fri, 20 Jan 2006 15:31:59 -0500, Marc Ferguson
>><marc@.digitalalias.net> wrote:
>>
>>Hi,
>>I've recently had some data issues with my MS SQL Server 2000. From
>>time to time, we will lose some data from tables here and there. I
>>don't know what's causing it, but it's a pretty decent size database.
>>We won't loose a whole tables' worth, but some data.
>>About 50 tables, some of the tables, such as our: vendors, leads,
>>contacts, manufacturers can have up to 12,000 records, but that's about it.
>>About Jerry's suggestion.. Does Microsoft have any profilers for their
>>own products or do you always have to go third party? Thanks.
>>Marc F.
>>
>>Michael Cheng [MSFT] wrote:
>>
>>Hi Bertil,
>>It's really strange that two tables will become empty and I believe some
>>operations must be done. You may follow the Jerry's suggestion using
>>Profiler to trace the SQL actions.
>>
>>Sincerely yours,
>>Michael Cheng
>>Microsoft Online Partner Support
>>When responding to posts, please "Reply to Group" via your newsreader so
>>that others may learn and benefit from your issue.
>>=====================================================>>This posting is provided "AS IS" with no warranties, and confers no rights.
>>
>>