MDF Files currupted: updates

Showing posts with label updates. Show all posts

Monday, March 19, 2012

Mysterious SQL Server Dropping Tables

We had just setup MS SQL 2000 (SP3), running on Windows 2000 (SP4). Both SQL
and Windows2000 Server has been patched with all security updates as per
Microsoft websites.
On the server PC, it is also running Norton Antivirus Corporate Edition 7.6
with the latest AV definition. I've also lost count of how many times we
scan the server for virus but none were found.
This is a newly setup server but we're observing the tables in the SQL
server dropping out of no apparent reason. A check on the actual data
directory we found that the database .LDF is missing and the EM marks it as
'suspect'.
What kind of information should I provide in order to further trouble shoot
this problem?
Since this is a test server we're only running it on 2 x 80GB IDE harddisk
and as far as I can tell there is no bad sectors found. Has anyone encounter
anything of such? Please help. TQ.
--
Steven Ung
"The source of all greatness lies within you" - Anonymous> This is a newly setup server but we're observing the tables in the SQL
> server dropping out of no apparent reason. A check on the actual data
> directory we found that the database .LDF is missing and the EM marks it
> as
> 'suspect'.
The tables, or the log file? Sounds like both are happening. Once you get
the system back where it should be, you might want to use a program like
filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
process is accessing the LDF file.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:ulucZdfHEHA.2876@.TK2MSFTNGP09.phx.gbl...
> The tables, or the log file? Sounds like both are happening.
Both, but the tables are the ones dropping/missing first.
Does drive caching has anything to do with it? The Windows Event Log
complaints that drive cache is disabled. I've temporary enable it and still
checking the results. But since no one has had this problem before, I'm not
certain of whether this is the solution or what is causing the tables to
drop or go missing.

> Once you get
> the system back where it should be, you might want to use a program like
> filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
> process is accessing the LDF file.
I've downloaded the utility but could not find any process out of the
extraordinary.
Steven Ung
"The source of all greatness lies within you" - Anonymous

Mysterious SQL Server Dropping Tables

We had just setup MS SQL 2000 (SP3), running on Windows 2000 (SP4). Both SQL
and Windows2000 Server has been patched with all security updates as per
Microsoft websites.
On the server PC, it is also running Norton Antivirus Corporate Edition 7.6
with the latest AV definition. I've also lost count of how many times we
scan the server for virus but none were found.
This is a newly setup server but we're observing the tables in the SQL
server dropping out of no apparent reason. A check on the actual data
directory we found that the database .LDF is missing and the EM marks it as
'suspect'.
What kind of information should I provide in order to further trouble shoot
this problem?
Since this is a test server we're only running it on 2 x 80GB IDE harddisk
and as far as I can tell there is no bad sectors found. Has anyone encounter
anything of such? Please help. TQ.
--
Steven Ung
"The source of all greatness lies within you" - Anonymous> This is a newly setup server but we're observing the tables in the SQL
> server dropping out of no apparent reason. A check on the actual data
> directory we found that the database .LDF is missing and the EM marks it
> as
> 'suspect'.
The tables, or the log file? Sounds like both are happening. Once you get
the system back where it should be, you might want to use a program like
filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
process is accessing the LDF file.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:ulucZdfHEHA.2876@.TK2MSFTNGP09.phx.gbl...
> > This is a newly setup server but we're observing the tables in the SQL
> > server dropping out of no apparent reason. A check on the actual data
> > directory we found that the database .LDF is missing and the EM marks it
> > as
> > 'suspect'.
> The tables, or the log file? Sounds like both are happening.
Both, but the tables are the ones dropping/missing first.
Does drive caching has anything to do with it? The Windows Event Log
complaints that drive cache is disabled. I've temporary enable it and still
checking the results. But since no one has had this problem before, I'm not
certain of whether this is the solution or what is causing the tables to
drop or go missing.
> Once you get
> the system back where it should be, you might want to use a program like
> filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
> process is accessing the LDF file.
I've downloaded the utility but could not find any process out of the
extraordinary.
--
Steven Ung
"The source of all greatness lies within you" - Anonymous

Mysterious SQL Server Dropping Tables

We had just setup MS SQL 2000 (SP3), running on Windows 2000 (SP4). Both SQL
and Windows2000 Server has been patched with all security updates as per
Microsoft websites.
On the server PC, it is also running Norton Antivirus Corporate Edition 7.6
with the latest AV definition. I've also lost count of how many times we
scan the server for virus but none were found.
This is a newly setup server but we're observing the tables in the SQL
server dropping out of no apparent reason. A check on the actual data
directory we found that the database .LDF is missing and the EM marks it as
'suspect'.
What kind of information should I provide in order to further trouble shoot
this problem?
Since this is a test server we're only running it on 2 x 80GB IDE harddisk
and as far as I can tell there is no bad sectors found. Has anyone encounter
anything of such? Please help. TQ.
Steven Ung
"The source of all greatness lies within you" - Anonymous
> This is a newly setup server but we're observing the tables in the SQL
> server dropping out of no apparent reason. A check on the actual data
> directory we found that the database .LDF is missing and the EM marks it
> as
> 'suspect'.
The tables, or the log file? Sounds like both are happening. Once you get
the system back where it should be, you might want to use a program like
filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
process is accessing the LDF file.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:ulucZdfHEHA.2876@.TK2MSFTNGP09.phx.gbl...
> The tables, or the log file? Sounds like both are happening.
Both, but the tables are the ones dropping/missing first.
Does drive caching has anything to do with it? The Windows Event Log
complaints that drive cache is disabled. I've temporary enable it and still
checking the results. But since no one has had this problem before, I'm not
certain of whether this is the solution or what is causing the tables to
drop or go missing.

> Once you get
> the system back where it should be, you might want to use a program like
> filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
> process is accessing the LDF file.
I've downloaded the utility but could not find any process out of the
extraordinary.
Steven Ung
"The source of all greatness lies within you" - Anonymous

Mysterious Performance Degradation

Hi,
A batch job which updates a table in a SQL Server database took three hours
to run last week. Nothing changed except perhaps the database grew by 1%.
Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
log file to match the production database server - this same job runs in
about 2 hours on that server. I monitored the buffer cache hit ratio,
%Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
Time, and Processor Queue Length counters - they all fall well within
recommended thresholds. The difference between the two servers: the problem
server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
know the job on my server won'f finish as fast as the same job on the Prod
server. But why this sudden change from 3 to 30 hours?index maintenance being done ?
Greg Jackson
PDX, Oregon|||The database had been restored using a production database backup. The
indexes are regularly defragmented on the production database.
"pdxJaxon" wrote:
> index maintenance being done ?
>
> Greg Jackson
> PDX, Oregon
>
>|||need to perform update statistics on the dev server.
"Simi B." wrote:
> The database had been restored using a production database backup. The
> indexes are regularly defragmented on the production database.
> "pdxJaxon" wrote:
> > index maintenance being done ?
> >
> >
> > Greg Jackson
> > PDX, Oregon
> >
> >
> >|||Dis you check the execution plan of the job on both the servers. That might
give you insight into if some statistics are missing etc.
"Simi B." wrote:
> Hi,
> A batch job which updates a table in a SQL Server database took three hours
> to run last week. Nothing changed except perhaps the database grew by 1%.
> Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> log file to match the production database server - this same job runs in
> about 2 hours on that server. I monitored the buffer cache hit ratio,
> %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> Time, and Processor Queue Length counters - they all fall well within
> recommended thresholds. The difference between the two servers: the problem
> server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> know the job on my server won'f finish as fast as the same job on the Prod
> server. But why this sudden change from 3 to 30 hours?|||Thanks all for your help. I was able to get it down from 41 hours to 7
hours...will see how I can tune it some more... =)
"harvinder" wrote:
> Dis you check the execution plan of the job on both the servers. That might
> give you insight into if some statistics are missing etc.
> "Simi B." wrote:
> > Hi,
> > A batch job which updates a table in a SQL Server database took three hours
> > to run last week. Nothing changed except perhaps the database grew by 1%.
> > Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> > log file to match the production database server - this same job runs in
> > about 2 hours on that server. I monitored the buffer cache hit ratio,
> > %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> > Time, and Processor Queue Length counters - they all fall well within
> > recommended thresholds. The difference between the two servers: the problem
> > server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> > know the job on my server won'f finish as fast as the same job on the Prod
> > server. But why this sudden change from 3 to 30 hours?|||Simi B,
I am facing a similar unexpected performance with one of my production
database servers. What was it that reduced your execution time?
I'm similarly stuck.
Jeremiah
"Simi B." wrote:
> Thanks all for your help. I was able to get it down from 41 hours to 7
> hours...will see how I can tune it some more... =)
> "harvinder" wrote:
> > Dis you check the execution plan of the job on both the servers. That might
> > give you insight into if some statistics are missing etc.
> >
> > "Simi B." wrote:
> >
> > > Hi,
> > > A batch job which updates a table in a SQL Server database took three hours
> > > to run last week. Nothing changed except perhaps the database grew by 1%.
> > > Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> > > log file to match the production database server - this same job runs in
> > > about 2 hours on that server. I monitored the buffer cache hit ratio,
> > > %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> > > Time, and Processor Queue Length counters - they all fall well within
> > > recommended thresholds. The difference between the two servers: the problem
> > > server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> > > know the job on my server won'f finish as fast as the same job on the Prod
> > > server. But why this sudden change from 3 to 30 hours?|||I reindexed all the tables, ran dbcc checkdb, updated statistics,
defragmented the hard drive on which the database resided.
"Jeremiah Traxler" wrote:
> Simi B,
> I am facing a similar unexpected performance with one of my production
> database servers. What was it that reduced your execution time?
> I'm similarly stuck.
> Jeremiah
> "Simi B." wrote:
> > Thanks all for your help. I was able to get it down from 41 hours to 7
> > hours...will see how I can tune it some more... =)
> >
> > "harvinder" wrote:
> >
> > > Dis you check the execution plan of the job on both the servers. That might
> > > give you insight into if some statistics are missing etc.
> > >
> > > "Simi B." wrote:
> > >
> > > > Hi,
> > > > A batch job which updates a table in a SQL Server database took three hours
> > > > to run last week. Nothing changed except perhaps the database grew by 1%.
> > > > Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> > > > log file to match the production database server - this same job runs in
> > > > about 2 hours on that server. I monitored the buffer cache hit ratio,
> > > > %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> > > > Time, and Processor Queue Length counters - they all fall well within
> > > > recommended thresholds. The difference between the two servers: the problem
> > > > server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> > > > know the job on my server won'f finish as fast as the same job on the Prod
> > > > server. But why this sudden change from 3 to 30 hours?

Mysterious loss of data from mssql2k

Hi All,

I'm trying to track down a mysterious problem we're experiencing in
which updates and inserts to tables in our mssql2k server appear to be
'disappearing.'

To explain our situation:

We have a web page (written in ASP, if that's relevant) on which we
accept enrollment information.

When that page is submitted, the form data is passed to a stored
procedure on our mssql2k server, which performs several operations,
all of which are wrapped in a transaction.

In particular, the stored procedure performs an update operation on a
record in one table (i'll call it TableA) and an insert into another
table (TableB).

If the procedure encounters a problem (ie after each update / insert
operation in the procedure we test for IF @.@.Error<>0) it performs a
rollback, performs a select similar to the one immediately below, and
then RETURNs.

SELECT '1' as error, 'Unable to update TableA' as errormsg

If the procedure doesn't fail any of the @.@.Error tests, the
transaction is committed, and a membership number is SELECTed to be
returned.

SELECT '0' as error, @.memnum as membershipnumber

The @.memnum variable is populated within the transaction.

Back in the ASP page we test both for the proc returning an empty
recordset, or for it passing an explicit value in the error field, and
push the page to an error page if either of these conditions are met.

If, on the other hand, none of these conditions are met, and the
membershipnumber field in the recordset is populated with a valid
membership number, we push to a confirmation page.

This confirmation page receives the membership number in a session
variable, performs a SELECT against TableB (the table that received
the insert during the proc) using that membership number in the WHERE
clause, and the resultant recordset is used to populate the
confirmation details on that page. That recordset is also then used to
populate the details of a confirmation email, which is automatically
sent by the confirmation page.

And now here's our problem: we've become aware of a handfull of people
who have gone through the enrollment process, have received the
confirmation email containing the information they supplied as
expected, but the data appears to be entirely missing from our tables.
By that I mean that the record in TableA does not appear to have been
updated (under normal circumstances that record should have had
several flags set, and several other fields updated with information
supplied by the person enrolling), and the record in TableB does not
appear to have been inserted.

In essence, looking at our tables, it *feels* like the transaction in
the stored procedure for that particular enrollment hit a problem and
was rolled back. However, the evidence that we have in the form of the
confirmation email argues strongly that the data must have existed in
our tables (particularly in TableB), if only for an unknown period of
time.

We're kind of at our wit's end to work out what is going wrong with
these enrollments. From my understanding of transactions (and I could
well be wrong) any changes to data (ie updates, inserts etc) contained
within are essentially 'invisible' to any other operation (ie the
SELECT that happens in the confirmation page) until the transaction is
committed, implying that the effect of the update and insert should
have been 'permanently' successful if no error code is received and if
a valid membership number was returned. I ask, because someone in our
team has suggested that maybe the operations in the transaction
'lasted long enough' in the tables to have been visible for the SELECT
on the confirmation page to have worked, but were then subsequently
rolled back, explaining why the confirmation email is appropriately
populated and why the data then appears to be missing. However, as I
said, this doesn't match my understanding of how transactions behave.

Sorry for the length of this post, but I felt it was best to explain
this as best as I could.

Does anyone have any advice they can give us on this situation? ie,
are there any known problems with operations in transactions 'bleeding
over' into tables, but then being rolled back at some later point?
Does anyone have any thoughts or suggestions on how we can further
diagnose this issue?

Truly, any help will be immensely appreciated...

Thanks in advance,

M Wells"M Wells" <planetthoughtful@.gmail.com> wrote in message
news:hmba41p1gvtsmudtbus027ad3rapgo1ghd@.4ax.com...
> Hi All,
> I'm trying to track down a mysterious problem we're experiencing in
> which updates and inserts to tables in our mssql2k server appear to be
> 'disappearing.'
> To explain our situation:
> We have a web page (written in ASP, if that's relevant) on which we
> accept enrollment information.

<snip
First, to address your question about data inside a transaction being
visible to other connections, this would only happen if the other connection
explicitly sets its transaction level to READ UNCOMMITTED, which would allow
it to see data which has been inserted/updated but not committed. See SET
TRANSACTION ISOLATION LEVEL in Books Online for more details - I suppose
your ASP connections could be setting this isolation level, but it isn't the
default, so it would be somewhat unusual.

As for tracking down what's going on with the data, you can use Profiler to
run a trace, perhaps filtered on those specific tables and any relevant
stored procedures. If this problem happens fairly often, then running it
interactively may be possible, otherwise see the sp_trace_% procs in Books
Online for details of setting up a server-side trace.

You might also want to check for any triggers on the tables, as sometimes
they can be fired at times you don't expect. And if you have a middle tier
layer, you could also see if it's initiating a transaction before calling
the procedure - it could be that the procedure itself commits correctly, and
the email is sent, but there's an additional outer transaction started by
the middle tier which is then sometimes rolled back after sending the email.
Checking @.@.TRANCOUNT inside the procedure would give you a clue.

Simon|||M Wells (planetthoughtful@.gmail.com) writes:
> If the procedure doesn't fail any of the @.@.Error tests, the
> transaction is committed, and a membership number is SELECTed to be
> returned.
> SELECT '0' as error, @.memnum as membershipnumber
> The @.memnum variable is populated within the transaction.
> Back in the ASP page we test both for the proc returning an empty
> recordset, or for it passing an explicit value in the error field, and
> push the page to an error page if either of these conditions are met.
> If, on the other hand, none of these conditions are met, and the
> membershipnumber field in the recordset is populated with a valid
> membership number, we push to a confirmation page.
> This confirmation page receives the membership number in a session
> variable, performs a SELECT against TableB (the table that received
> the insert during the proc) using that membership number in the WHERE
> clause, and the resultant recordset is used to populate the
> confirmation details on that page. That recordset is also then used to
> populate the details of a confirmation email, which is automatically
> sent by the confirmation page.
> And now here's our problem: we've become aware of a handfull of people
> who have gone through the enrollment process, have received the
> confirmation email containing the information they supplied as
> expected, but the data appears to be entirely missing from our tables.

As I understand, this is an intermittent problem, and you don't have a
reproducible scenario. This make such a problem much more difficult
to track down. And if you make changes to address, you cannot really
be sure that you fixed the right thing.

One thing that is not clear to me is whether it is the same SQL Server
process that runs the stored procedure and the gets the data to the
confirmation page, or whether they are two different. (I should butt
in that I don't know ASP or IIS, so this talk about session variables
etc, tells me little.)

If it is the same SQL Server process, here is something that could
happen:

1) The stored procedure first run unsuccessfully, and a transaction
is started, but then neither committed nor rolled back.
2) The process then runs the procedure successfully, and then gets
the data to the confirmation page. This time a nested transaction
was started and committed. However, "commit" in the case of an
inner transaction just means that transaction count is decremented.
3) The process goes on and registers and confirms more enrollments.
4) Eventually the process is logged out, still with an open transaction.
All enrollments are now rolled back.

So why in step 1, would this happen? It can be a coding error in
the stored procedure, so that a rollback is not executed when it
should. But it could also be a client-side thing. Say that the
procedure is blocked for some reason, and the client gets a command
timeout. In this case the transaction started by the stored procedure
is *not* rolled back. This a really nasty gotcha.

If the confirmation page is really a separate SQL Server connection -
and you should really use the SQL Server Profiler to verify this - then
the data has been committed, and thus it has later been removed. Well,
if the confirmation reads with NOLOCK, are back to the previous
scenario.

One thing you should investigate in either case, is whether these missing
enrollments happened at different points in time, or if they are clustered.
That could give a clue of what may have happened.

I hope this has given you some more ideas of what to look for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 7, 2012

my SQL Server updates another server in another city

I just started today at this company. They have a 2003 SQL server here in
Dallas and twice a day it updates with the main SQL server in NY. And I
don't know that much about it. When I look at the SQL Server network
connections..there is only one connection..the LAN connection..however when
I
look at Ipconfig /all there is this:
Tunnel adapter Automatic Tunneling Pseudo-Interface:
Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : Automatic Tunneling Pseudo-Interface
Physical Address. . . . . . . . . : C0-A8-03-69
DHCP Enabled. . . . . . . . . . . : No
IP Address. . . . . . . . . . . . : fe80::5efe:192.168.3.105%2
Default Gateway . . . . . . . . . :
DNS Servers . . . . . . . . . . . : fec0:0:0:ffff::1%1
fec0:0:0:ffff::2%1
fec0:0:0:ffff::3%1
NetBIOS over Tcpip. . . . . . . . : Disabled
This appears to be some sort of VPN tunnel but I wish I knew more about it.
Can anyone help me. Is this VPN tunnel established w/ the SQL software or
WK2003 software or is it in my T1 router or my firewall. Can anyone help me
decipher this to see the IP of the computer in NY its connecting too. We ar
e
scheduled to move buildings in 4 weeks and I am concerened this VPN thing
won't work at the new location. When we move I plan on keeping all the IP
address the same with our T1 provider. Anyone that can help me , that has
seen this before , that knows anything that I might not know about this
please post...The boss says our SQL server in Dallas replicates 2 times a da
y
to the main server in NY and I just don't get it..thanksI think you just need to read up on IPv6 - perhaps
http://www.microsoft.com/windowsser...v6/default.mspx
might help?
Zilog
Michael.Hall00 wrote:
> I just started today at this company.
> This appears to be some sort of VPN tunnel but I wish I knew more about it
.
> Can anyone help me. Is this VPN tunnel established w/ the SQL software or
> WK2003 software or is it in my T1 router or my firewall. Can anyone help
me
> decipher this to see the IP of the computer in NY its connecting too. We
are
> scheduled to move buildings in 4 weeks and I am concerened this VPN thing
> won't work at the new location. When we move I plan on keeping all the IP
> address the same with our T1 provider. Anyone that can help me , that has
> seen this before , that knows anything that I might not know about this
> please post...The boss says our SQL server in Dallas replicates 2 times a
day
> to the main server in NY and I just don't get it..thanks

Monday, March 19, 2012

Mysterious SQL Server Dropping Tables

Mysterious SQL Server Dropping Tables

Mysterious SQL Server Dropping Tables

Mysterious Performance Degradation

Mysterious Performance Degradation

Mysterious Performance Degradation

Mysterious loss of data from mssql2k

Wednesday, March 7, 2012

my SQL Server updates another server in another city

MDF Files currupted

Blog Archive

About Me