Mysterious and sudden performance worsening

A few days ago one of the server I use become very slow. I've a tween
server, it's pretty equal to it's brother: it's still working properly (it
runs at about 10 time faster for the same query).
I checked the hardware (raid controller, network connection and general
performances) and it seems perfect. The memory occupation and the processor
use is equal to it's tween, but the disk access has abnormally increased. To
solve the proble I've:
1 - restarted the server
2 - installed all the patches, updates and services pack I could find.
My last chance is to reistall SQL server, but I still hope to find a more
"scientific" solution.
Thanks for any suggestion.
MTA variety of things coul cause this. Just a few ideas that might be useful:
Perhaps the database has become fragmented?
Perhaps index statistics are out of date / not being updated automatically?
Have indexes been dropped?
Are there any long running transactions?
Regards,
Greg Linwood
SQL Server MVP
"MT" <cutme.mttc1685@.hotmail.com> wrote in message
news:qACnb.73796$vO5.2693264@.twister1.libero.it...
> A few days ago one of the server I use become very slow. I've a tween
> server, it's pretty equal to it's brother: it's still working properly (it
> runs at about 10 time faster for the same query).
> I checked the hardware (raid controller, network connection and general
> performances) and it seems perfect. The memory occupation and the
processor
> use is equal to it's tween, but the disk access has abnormally increased.
To
> solve the proble I've:
> 1 - restarted the server
> 2 - installed all the patches, updates and services pack I could find.
> My last chance is to reistall SQL server, but I still hope to find a more
> "scientific" solution.
> Thanks for any suggestion.
> MT
>|||To add to Greg's post:
Check out the execution plans, se if that differs. This is what I would start with. Can for example
lead to out-of-date statistics (as per Greg's post).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"MT" <cutme.mttc1685@.hotmail.com> wrote in message
news:qACnb.73796$vO5.2693264@.twister1.libero.it...
> A few days ago one of the server I use become very slow. I've a tween
> server, it's pretty equal to it's brother: it's still working properly (it
> runs at about 10 time faster for the same query).
> I checked the hardware (raid controller, network connection and general
> performances) and it seems perfect. The memory occupation and the processor
> use is equal to it's tween, but the disk access has abnormally increased. To
> solve the proble I've:
> 1 - restarted the server
> 2 - installed all the patches, updates and services pack I could find.
> My last chance is to reistall SQL server, but I still hope to find a more
> "scientific" solution.
> Thanks for any suggestion.
> MT
>|||I have had similar proplems. Run a DBCC INDEX DEFRAG and
sp_updatestatistics. That should greatly improve the performance.
Thanks
Bryan
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eBWsv6fnDHA.2404@.TK2MSFTNGP12.phx.gbl...
> To add to Greg's post:
> Check out the execution plans, se if that differs. This is what I would
start with. Can for example
> lead to out-of-date statistics (as per Greg's post).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "MT" <cutme.mttc1685@.hotmail.com> wrote in message
> news:qACnb.73796$vO5.2693264@.twister1.libero.it...
> > A few days ago one of the server I use become very slow. I've a tween
> > server, it's pretty equal to it's brother: it's still working properly
(it
> > runs at about 10 time faster for the same query).
> > I checked the hardware (raid controller, network connection and general
> > performances) and it seems perfect. The memory occupation and the
processor
> > use is equal to it's tween, but the disk access has abnormally
increased. To
> > solve the proble I've:
> > 1 - restarted the server
> > 2 - installed all the patches, updates and services pack I could find.
> >
> > My last chance is to reistall SQL server, but I still hope to find a
more
> > "scientific" solution.
> > Thanks for any suggestion.
> > MT
> >
> >
>|||Problem "partially" solved: the test was made using microsoft DAO on a PC
with microsft XP with integrated security. I changed to SqlServer security
and now everything works properly. I'm afraid I will never know what caused
the sudden worsening.
If you use DAO with XP never access the database with integrated security,
as I don't believe that they will release a patch for an obsolete platform
like DAO.
"MT" <cutme.mttc1685@.hotmail.com> ha scritto nel messaggio
news:qACnb.73796$vO5.2693264@.twister1.libero.it...
> A few days ago one of the server I use become very slow. I've a tween
> server, it's pretty equal to it's brother: it's still working properly (it
> runs at about 10 time faster for the same query).
> I checked the hardware (raid controller, network connection and general
> performances) and it seems perfect. The memory occupation and the
processor
> use is equal to it's tween, but the disk access has abnormally increased.
To
> solve the proble I've:
> 1 - restarted the server
> 2 - installed all the patches, updates and services pack I could find.
> My last chance is to reistall SQL server, but I still hope to find a more
> "scientific" solution.
> Thanks for any suggestion.
> MT
>
>

My SQL server doesn't exist?

So here's what happened:
I was running out of space on my DB partition (there was a sudden surge in
DB size due to a lot of scanned documents being attached in a short period
of time). To free up space for a day or two while I waited for an
additional drive to add to the RAID set, I decided to temporarily move an
older, infrequently-accessed database to another volume. Here's what I did:
- I stopped the server and server agent services for the database I was
moving -- I'll call it Old-DB -- , then simply copied its entire MSSQL
folder, lock stock and barrel, to a network share on another server.
- When the new drive arrived a day later, I added it to the RAID set and
increased the size of the volume.
- I rebooted the SQL server for reasons unrelated to this issue. On
startup, it reported that a driver or service had failed because the Old-DB
had tried to start, but of course it's MSSQL folder wasn't there.
- I copied the MSSQL folder for Old-DB back to its original location on the
SQL server.
Now I get a 1053 error when trying to start the SQL Server, and a 1068 error
when trying to start the SQL Server Agent. Enterprise Manager now shows a
(local) database that's not started, with the message (Connection failed,
check SQL Server Registration Properties) under it.
If I understand correctly, my big worry shouldn't be about the data (which
is probably fine), but about the user logins. Is that right?
What's my next step to getting this DB back online? It's not urgent, but it
IS important that we retain or regain access to the historical data in this
DB.
Thanks in advance!
BJYou should perform a full system restore to recover to the state you were in
before you started moving files.
Then start again and do the job "correctly" i.e. by following the documented
procedures (see Books Online) for moving databases.
"Bryan L" <blinton.nospam@.connellinsurance.nospam.com> wrote in message
news:e3%235claNHHA.2232@.TK2MSFTNGP02.phx.gbl...
> So here's what happened:
> I was running out of space on my DB partition (there was a sudden surge in
> DB size due to a lot of scanned documents being attached in a short period
> of time). To free up space for a day or two while I waited for an
> additional drive to add to the RAID set, I decided to temporarily move an
> older, infrequently-accessed database to another volume. Here's what I
> did:
> - I stopped the server and server agent services for the database I was
> moving -- I'll call it Old-DB -- , then simply copied its entire MSSQL
> folder, lock stock and barrel, to a network share on another server.
> - When the new drive arrived a day later, I added it to the RAID set and
> increased the size of the volume.
> - I rebooted the SQL server for reasons unrelated to this issue. On
> startup, it reported that a driver or service had failed because the
> Old-DB had tried to start, but of course it's MSSQL folder wasn't there.
> - I copied the MSSQL folder for Old-DB back to its original location on
> the SQL server.
> Now I get a 1053 error when trying to start the SQL Server, and a 1068
> error when trying to start the SQL Server Agent. Enterprise Manager now
> shows a (local) database that's not started, with the message (Connection
> failed, check SQL Server Registration Properties) under it.
> If I understand correctly, my big worry shouldn't be about the data (which
> is probably fine), but about the user logins. Is that right?
> What's my next step to getting this DB back online? It's not urgent, but
> it IS important that we retain or regain access to the historical data in
> this DB.
> Thanks in advance!
> BJ
>|||I have a full disk image of the SQL server that was taken just prior to
retiring the database and migrating to the new version of the application.
I'll restore that image to temporary hardware, verify that I can access the
DB, and then use Books Online to lookup and follow an accepted procedure for
moving or restoring that database to a new server.
Thanks for cutting to the chase. :-)
BJ
"Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
news:uwx0LO8NHHA.4172@.TK2MSFTNGP04.phx.gbl...
> You should perform a full system restore to recover to the state you were
> in before you started moving files.
> Then start again and do the job "correctly" i.e. by following the
> documented procedures (see Books Online) for moving databases.
> "Bryan L" <blinton.nospam@.connellinsurance.nospam.com> wrote in message
> news:e3%235claNHHA.2232@.TK2MSFTNGP02.phx.gbl...
>> So here's what happened:
>> I was running out of space on my DB partition (there was a sudden surge
>> in DB size due to a lot of scanned documents being attached in a short
>> period of time). To free up space for a day or two while I waited for an
>> additional drive to add to the RAID set, I decided to temporarily move an
>> older, infrequently-accessed database to another volume. Here's what I
>> did:
>> - I stopped the server and server agent services for the database I was
>> moving -- I'll call it Old-DB -- , then simply copied its entire MSSQL
>> folder, lock stock and barrel, to a network share on another server.
>> - When the new drive arrived a day later, I added it to the RAID set and
>> increased the size of the volume.
>> - I rebooted the SQL server for reasons unrelated to this issue. On
>> startup, it reported that a driver or service had failed because the
>> Old-DB had tried to start, but of course it's MSSQL folder wasn't there.
>> - I copied the MSSQL folder for Old-DB back to its original location on
>> the SQL server.
>> Now I get a 1053 error when trying to start the SQL Server, and a 1068
>> error when trying to start the SQL Server Agent. Enterprise Manager now
>> shows a (local) database that's not started, with the message (Connection
>> failed, check SQL Server Registration Properties) under it.
>> If I understand correctly, my big worry shouldn't be about the data
>> (which is probably fine), but about the user logins. Is that right?
>> What's my next step to getting this DB back online? It's not urgent, but
>> it IS important that we retain or regain access to the historical data in
>> this DB.
>> Thanks in advance!
>> BJ
>

My SQL server doesn't exist?

So here's what happened:
I was running out of space on my DB partition (there was a sudden surge in
DB size due to a lot of scanned documents being attached in a short period
of time). To free up space for a day or two while I waited for an
additional drive to add to the RAID set, I decided to temporarily move an
older, infrequently-accessed database to another volume. Here's what I did:
- I stopped the server and server agent services for the database I was
moving -- I'll call it Old-DB -- , then simply copied its entire MSSQL
folder, lock stock and barrel, to a network share on another server.
- When the new drive arrived a day later, I added it to the RAID set and
increased the size of the volume.
- I rebooted the SQL server for reasons unrelated to this issue. On
startup, it reported that a driver or service had failed because the Old-DB
had tried to start, but of course it's MSSQL folder wasn't there.
- I copied the MSSQL folder for Old-DB back to its original location on the
SQL server.
Now I get a 1053 error when trying to start the SQL Server, and a 1068 error
when trying to start the SQL Server Agent. Enterprise Manager now shows a
(local) database that's not started, with the message (Connection failed,
check SQL Server Registration Properties) under it.
If I understand correctly, my big worry shouldn't be about the data (which
is probably fine), but about the user logins. Is that right?
What's my next step to getting this DB back online? It's not urgent, but it
IS important that we retain or regain access to the historical data in this
DB.
Thanks in advance!
BJ
I have a full disk image of the SQL server that was taken just prior to
retiring the database and migrating to the new version of the application.
I'll restore that image to temporary hardware, verify that I can access the
DB, and then use Books Online to lookup and follow an accepted procedure for
moving or restoring that database to a new server.
Thanks for cutting to the chase. :-)
BJ
"Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
news:uwx0LO8NHHA.4172@.TK2MSFTNGP04.phx.gbl...
> You should perform a full system restore to recover to the state you were
> in before you started moving files.
> Then start again and do the job "correctly" i.e. by following the
> documented procedures (see Books Online) for moving databases.
> "Bryan L" <blinton.nospam@.connellinsurance.nospam.com> wrote in message
> news:e3%235claNHHA.2232@.TK2MSFTNGP02.phx.gbl...
>

My SQL server doesn't exist?

So here's what happened:
I was running out of space on my DB partition (there was a sudden surge in
DB size due to a lot of scanned documents being attached in a short period
of time). To free up space for a day or two while I waited for an
additional drive to add to the RAID set, I decided to temporarily move an
older, infrequently-accessed database to another volume. Here's what I did:
- I stopped the server and server agent services for the database I was
moving -- I'll call it Old-DB -- , then simply copied its entire MSSQL
folder, lock stock and barrel, to a network share on another server.
- When the new drive arrived a day later, I added it to the RAID set and
increased the size of the volume.
- I rebooted the SQL server for reasons unrelated to this issue. On
startup, it reported that a driver or service had failed because the Old-DB
had tried to start, but of course it's MSSQL folder wasn't there.
- I copied the MSSQL folder for Old-DB back to its original location on the
SQL server.
Now I get a 1053 error when trying to start the SQL Server, and a 1068 error
when trying to start the SQL Server Agent. Enterprise Manager now shows a
(local) database that's not started, with the message (Connection failed,
check SQL Server Registration Properties) under it.
If I understand correctly, my big worry shouldn't be about the data (which
is probably fine), but about the user logins. Is that right?
What's my next step to getting this DB back online? It's not urgent, but it
IS important that we retain or regain access to the historical data in this
DB.
Thanks in advance!
BJYou should perform a full system restore to recover to the state you were in
before you started moving files.
Then start again and do the job "correctly" i.e. by following the documented
procedures (see Books Online) for moving databases.
"Bryan L" <blinton.nospam@.connellinsurance.nospam.com> wrote in message
news:e3%235claNHHA.2232@.TK2MSFTNGP02.phx.gbl...
> So here's what happened:
> I was running out of space on my DB partition (there was a sudden surge in
> DB size due to a lot of scanned documents being attached in a short period
> of time). To free up space for a day or two while I waited for an
> additional drive to add to the RAID set, I decided to temporarily move an
> older, infrequently-accessed database to another volume. Here's what I
> did:
> - I stopped the server and server agent services for the database I was
> moving -- I'll call it Old-DB -- , then simply copied its entire MSSQL
> folder, lock stock and barrel, to a network share on another server.
> - When the new drive arrived a day later, I added it to the RAID set and
> increased the size of the volume.
> - I rebooted the SQL server for reasons unrelated to this issue. On
> startup, it reported that a driver or service had failed because the
> Old-DB had tried to start, but of course it's MSSQL folder wasn't there.
> - I copied the MSSQL folder for Old-DB back to its original location on
> the SQL server.
> Now I get a 1053 error when trying to start the SQL Server, and a 1068
> error when trying to start the SQL Server Agent. Enterprise Manager now
> shows a (local) database that's not started, with the message (Connection
> failed, check SQL Server Registration Properties) under it.
> If I understand correctly, my big worry shouldn't be about the data (which
> is probably fine), but about the user logins. Is that right?
> What's my next step to getting this DB back online? It's not urgent, but
> it IS important that we retain or regain access to the historical data in
> this DB.
> Thanks in advance!
> BJ
>|||I have a full disk image of the SQL server that was taken just prior to
retiring the database and migrating to the new version of the application.
I'll restore that image to temporary hardware, verify that I can access the
DB, and then use Books Online to lookup and follow an accepted procedure for
moving or restoring that database to a new server.
Thanks for cutting to the chase. :-)
BJ
"Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
news:uwx0LO8NHHA.4172@.TK2MSFTNGP04.phx.gbl...
> You should perform a full system restore to recover to the state you were
> in before you started moving files.
> Then start again and do the job "correctly" i.e. by following the
> documented procedures (see Books Online) for moving databases.
> "Bryan L" <blinton.nospam@.connellinsurance.nospam.com> wrote in message
> news:e3%235claNHHA.2232@.TK2MSFTNGP02.phx.gbl...
>