Database Overwritten: guys

Showing posts with label guys. Show all posts

Monday, March 19, 2012

Database marked Suspect after normabl Windows Shutdown

Hey Guys,
Looking to see if anyone has experienced this and if there is something
we are doing wrong. We have a weekly process to reboot our windows
servers (we have 100's). The process is a normal windows shutdown.
Yet weekly one of our servers will come back up and have a 'Torn Page'
error, then get marked suspect. In investigating this I notice that
the when the process works, there is a message in the SQL Server error
log that says 'SQL Server is terminating due to Server shutdown'.
Yet in the cases where it ends up suspect the message is not in the
log. So to me it looks like the SQL Server is getting shutdown hard,
but why?
I end up just restoring the 'Suspect' database, but I'm wondering why
the process sporatically fails on some servers. Is there a setting I'm
missing?
Some specifics about the environment is that these are all Windows 2003
servers running SQL Server 2000 SP3 (it also occurs on Desktop
Edition).
Thanks in advance for any assistance.Hi
It could be that SQL Server is not responding in time to the request to
terminate.
Could you do a NET STOP on the SQL Server Services before shutting down.
Why do you need to shut down each week?
John
"paul.esposito@.comcast.net" wrote:
> Hey Guys,
> Looking to see if anyone has experienced this and if there is something
> we are doing wrong. We have a weekly process to reboot our windows
> servers (we have 100's). The process is a normal windows shutdown.
> Yet weekly one of our servers will come back up and have a 'Torn Page'
> error, then get marked suspect. In investigating this I notice that
> the when the process works, there is a message in the SQL Server error
> log that says 'SQL Server is terminating due to Server shutdown'.
> Yet in the cases where it ends up suspect the message is not in the
> log. So to me it looks like the SQL Server is getting shutdown hard,
> but why?
> I end up just restoring the 'Suspect' database, but I'm wondering why
> the process sporatically fails on some servers. Is there a setting I'm
> missing?
> Some specifics about the environment is that these are all Windows 2003
> servers running SQL Server 2000 SP3 (it also occurs on Desktop
> Edition).
> Thanks in advance for any assistance.
>|||Well, corporate standard is one reason and other systems reside on the
server and need to be rebooted.
I'm going to incorporate a netstop into the procedures. I was just
wondering if this was a common problem.
Regardless of reason, whether I reboot the server once a week or once
every year, if the database corrupts on that shutdown there seems to be
an issue. I say this because I was told that Microsoft says W2K3 and
SQL Server are tuned not to need a reboot and that they should be left
running. Well some of our servers can't be left running all the time
Hurricanes, power outages ... Just seems like a bug to me.
Thanks for the input though. I won't fight it, I'll just do a net
stop.
John Bell wrote:
> Hi
> It could be that SQL Server is not responding in time to the request to
> terminate.
> Could you do a NET STOP on the SQL Server Services before shutting down.
> Why do you need to shut down each week?
> John
> "paul.esposito@.comcast.net" wrote:
> > Hey Guys,
> >
> > Looking to see if anyone has experienced this and if there is something
> > we are doing wrong. We have a weekly process to reboot our windows
> > servers (we have 100's). The process is a normal windows shutdown.
> > Yet weekly one of our servers will come back up and have a 'Torn Page'
> > error, then get marked suspect. In investigating this I notice that
> > the when the process works, there is a message in the SQL Server error
> > log that says 'SQL Server is terminating due to Server shutdown'.
> > Yet in the cases where it ends up suspect the message is not in the
> > log. So to me it looks like the SQL Server is getting shutdown hard,
> > but why?
> >
> > I end up just restoring the 'Suspect' database, but I'm wondering why
> > the process sporatically fails on some servers. Is there a setting I'm
> > missing?
> >
> > Some specifics about the environment is that these are all Windows 2003
> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> > Edition).
> >
> > Thanks in advance for any assistance.
> >
> >|||The problem is in the nature of hardware. A page it 8KB. The disks typically uses a sector as the
level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typically 16 sectors. A power
failure (or hard stop) in the middle of a page write will cause a partially written page (a torn
page). Having HW write cache *with a properly implemented battery backup* will minimize the risk of
a torn page (as long as the battery backup does its job).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<paul.esposito@.comcast.net> wrote in message
news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> Well, corporate standard is one reason and other systems reside on the
> server and need to be rebooted.
> I'm going to incorporate a netstop into the procedures. I was just
> wondering if this was a common problem.
> Regardless of reason, whether I reboot the server once a week or once
> every year, if the database corrupts on that shutdown there seems to be
> an issue. I say this because I was told that Microsoft says W2K3 and
> SQL Server are tuned not to need a reboot and that they should be left
> running. Well some of our servers can't be left running all the time
> Hurricanes, power outages ... Just seems like a bug to me.
> Thanks for the input though. I won't fight it, I'll just do a net
> stop.
>
> John Bell wrote:
>> Hi
>> It could be that SQL Server is not responding in time to the request to
>> terminate.
>> Could you do a NET STOP on the SQL Server Services before shutting down.
>> Why do you need to shut down each week?
>> John
>> "paul.esposito@.comcast.net" wrote:
>> > Hey Guys,
>> >
>> > Looking to see if anyone has experienced this and if there is something
>> > we are doing wrong. We have a weekly process to reboot our windows
>> > servers (we have 100's). The process is a normal windows shutdown.
>> > Yet weekly one of our servers will come back up and have a 'Torn Page'
>> > error, then get marked suspect. In investigating this I notice that
>> > the when the process works, there is a message in the SQL Server error
>> > log that says 'SQL Server is terminating due to Server shutdown'.
>> > Yet in the cases where it ends up suspect the message is not in the
>> > log. So to me it looks like the SQL Server is getting shutdown hard,
>> > but why?
>> >
>> > I end up just restoring the 'Suspect' database, but I'm wondering why
>> > the process sporatically fails on some servers. Is there a setting I'm
>> > missing?
>> >
>> > Some specifics about the environment is that these are all Windows 2003
>> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
>> > Edition).
>> >
>> > Thanks in advance for any assistance.
>> >
>> >
>|||Tibor,
Thanks for the response. But according to the user and the event logs,
a normal shutdown was requested. i.e. The user requested Windows to
shutdown. This should have sent a message to all the services to stop.
Yet I don't see that request in the SQL Server log. But the event log
does show that a normal shutdown was requested of the OS.
Thanks again.
Tibor Karaszi wrote:
> The problem is in the nature of hardware. A page it 8KB. The disks typically uses a sector as the
> level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typically 16 sectors. A power
> failure (or hard stop) in the middle of a page write will cause a partially written page (a torn
> page). Having HW write cache *with a properly implemented battery backup* will minimize the risk of
> a torn page (as long as the battery backup does its job).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <paul.esposito@.comcast.net> wrote in message
> news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> > Well, corporate standard is one reason and other systems reside on the
> > server and need to be rebooted.
> >
> > I'm going to incorporate a netstop into the procedures. I was just
> > wondering if this was a common problem.
> >
> > Regardless of reason, whether I reboot the server once a week or once
> > every year, if the database corrupts on that shutdown there seems to be
> > an issue. I say this because I was told that Microsoft says W2K3 and
> > SQL Server are tuned not to need a reboot and that they should be left
> > running. Well some of our servers can't be left running all the time
> > Hurricanes, power outages ... Just seems like a bug to me.
> >
> > Thanks for the input though. I won't fight it, I'll just do a net
> > stop.
> >
> >
> > John Bell wrote:
> >> Hi
> >>
> >> It could be that SQL Server is not responding in time to the request to
> >> terminate.
> >> Could you do a NET STOP on the SQL Server Services before shutting down.
> >> Why do you need to shut down each week?
> >>
> >> John
> >>
> >> "paul.esposito@.comcast.net" wrote:
> >>
> >> > Hey Guys,
> >> >
> >> > Looking to see if anyone has experienced this and if there is something
> >> > we are doing wrong. We have a weekly process to reboot our windows
> >> > servers (we have 100's). The process is a normal windows shutdown.
> >> > Yet weekly one of our servers will come back up and have a 'Torn Page'
> >> > error, then get marked suspect. In investigating this I notice that
> >> > the when the process works, there is a message in the SQL Server error
> >> > log that says 'SQL Server is terminating due to Server shutdown'.
> >> > Yet in the cases where it ends up suspect the message is not in the
> >> > log. So to me it looks like the SQL Server is getting shutdown hard,
> >> > but why?
> >> >
> >> > I end up just restoring the 'Suspect' database, but I'm wondering why
> >> > the process sporatically fails on some servers. Is there a setting I'm
> >> > missing?
> >> >
> >> > Some specifics about the environment is that these are all Windows 2003
> >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> >> > Edition).
> >> >
> >> > Thanks in advance for any assistance.
> >> >
> >> >
> >|||What sort of hardware are you using? How are your disks configured? Are
there any events or issues with your I/O controllers? How is your I/O
solution utilizing it's write cache?
I agree that you should be able to restart whenver you want. I have
several SQL installations in my environment and have never had an issue
with a normal shut down routine. Can you do a manual reboot of the
server watching it (by manual I mean not automated but going through
start/shutdown) and see what happens?
Just a point, while you should be able to reboot whenver you want
(gracefully and even safely under some not so graceful situations
should not be a problem and has not in my experience with my servers),
it is generally not always a best practice to reboot your database
servers.
For one thing there is no need to if you have a secure server and have
only SQL Server running on it. In most of the configurations out there
with only SQL running, you won't see a memory leak or any other issue
requiring reboot. Also SQL works hard to build it's caches with data
and compiled query execution plans that are optimized for your data and
workload. Everytime you reboot you clear this cache meaning that after
restart the server needs to first optimize and save all the plans again
as well as add the frequently accessed data into the data cache. Not a
huge deal but just some items to think about.
Please give more details regarding my above questions and we will see
what we can offer.
-Mike Walsh
SQL DBA
paul.esposito@.comcast.net wrote:
> Tibor,
> Thanks for the response. But according to the user and the event logs,
> a normal shutdown was requested. i.e. The user requested Windows to
> shutdown. This should have sent a message to all the services to stop.
> Yet I don't see that request in the SQL Server log. But the event log
> does show that a normal shutdown was requested of the OS.
> Thanks again.
>
> Tibor Karaszi wrote:
> > The problem is in the nature of hardware. A page it 8KB. The disks typically uses a sector as the
> > level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typically 16 sectors. A power
> > failure (or hard stop) in the middle of a page write will cause a partially written page (a torn
> > page). Having HW write cache *with a properly implemented battery backup* will minimize the risk of
> > a torn page (as long as the battery backup does its job).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > <paul.esposito@.comcast.net> wrote in message
> > news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> > > Well, corporate standard is one reason and other systems reside on the
> > > server and need to be rebooted.
> > >
> > > I'm going to incorporate a netstop into the procedures. I was just
> > > wondering if this was a common problem.
> > >
> > > Regardless of reason, whether I reboot the server once a week or once
> > > every year, if the database corrupts on that shutdown there seems to be
> > > an issue. I say this because I was told that Microsoft says W2K3 and
> > > SQL Server are tuned not to need a reboot and that they should be left
> > > running. Well some of our servers can't be left running all the time
> > > Hurricanes, power outages ... Just seems like a bug to me.
> > >
> > > Thanks for the input though. I won't fight it, I'll just do a net
> > > stop.
> > >
> > >
> > > John Bell wrote:
> > >> Hi
> > >>
> > >> It could be that SQL Server is not responding in time to the request to
> > >> terminate.
> > >> Could you do a NET STOP on the SQL Server Services before shutting down.
> > >> Why do you need to shut down each week?
> > >>
> > >> John
> > >>
> > >> "paul.esposito@.comcast.net" wrote:
> > >>
> > >> > Hey Guys,
> > >> >
> > >> > Looking to see if anyone has experienced this and if there is something
> > >> > we are doing wrong. We have a weekly process to reboot our windows
> > >> > servers (we have 100's). The process is a normal windows shutdown.
> > >> > Yet weekly one of our servers will come back up and have a 'Torn Page'
> > >> > error, then get marked suspect. In investigating this I notice that
> > >> > the when the process works, there is a message in the SQL Server error
> > >> > log that says 'SQL Server is terminating due to Server shutdown'.
> > >> > Yet in the cases where it ends up suspect the message is not in the
> > >> > log. So to me it looks like the SQL Server is getting shutdown hard,
> > >> > but why?
> > >> >
> > >> > I end up just restoring the 'Suspect' database, but I'm wondering why
> > >> > the process sporatically fails on some servers. Is there a setting I'm
> > >> > missing?
> > >> >
> > >> > Some specifics about the environment is that these are all Windows 2003
> > >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> > >> > Edition).
> > >> >
> > >> > Thanks in advance for any assistance.
> > >> >
> > >> >
> > >|||Unfortunately, a Server Shutdown request does exactly this: a request for
NET STOP on every running service. The problem is that there exists a time
out value. When SQL Server receives this message, it begins its shutdown
phase, which issues stops all new connections and issues a hard CHECKPOINT
of all databases.
Typically during the maintenance windows (especially the database REORGs),
this stop request could take quite a bit of time (even more on the start up
and recovery process).
Nearly the only way you are going to get a torn page from this (as opposed
to a lot of recovery rollback operations) is that the disks were powered
down before the cache committed.
1. Plan your database maintenance around these weekly system restarts.
2. Disable controller and Windows disk caching unless you can guarantee
controller power protection.
I would also agree with most everyone else that a DBMS should be on a
dedicated system, and, which case, should be very little justification for
weekly restarts.
Now, in our Data Center, we have monthly OS updates that are deployed. For
this, we (the DBA team) have demanded subsequent system restarts (mainly
because we believe patches leave PendingFileRename operations), but that is
only on a monthly basis, and we shut down database service before the server
team is allowed to begin.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
> What sort of hardware are you using? How are your disks configured? Are
> there any events or issues with your I/O controllers? How is your I/O
> solution utilizing it's write cache?
> I agree that you should be able to restart whenver you want. I have
> several SQL installations in my environment and have never had an issue
> with a normal shut down routine. Can you do a manual reboot of the
> server watching it (by manual I mean not automated but going through
> start/shutdown) and see what happens?
> Just a point, while you should be able to reboot whenver you want
> (gracefully and even safely under some not so graceful situations
> should not be a problem and has not in my experience with my servers),
> it is generally not always a best practice to reboot your database
> servers.
> For one thing there is no need to if you have a secure server and have
> only SQL Server running on it. In most of the configurations out there
> with only SQL running, you won't see a memory leak or any other issue
> requiring reboot. Also SQL works hard to build it's caches with data
> and compiled query execution plans that are optimized for your data and
> workload. Everytime you reboot you clear this cache meaning that after
> restart the server needs to first optimize and save all the plans again
> as well as add the frequently accessed data into the data cache. Not a
> huge deal but just some items to think about.
> Please give more details regarding my above questions and we will see
> what we can offer.
> -Mike Walsh
> SQL DBA
>
> paul.esposito@.comcast.net wrote:
> > Tibor,
> >
> > Thanks for the response. But according to the user and the event logs,
> > a normal shutdown was requested. i.e. The user requested Windows to
> > shutdown. This should have sent a message to all the services to stop.
> > Yet I don't see that request in the SQL Server log. But the event log
> > does show that a normal shutdown was requested of the OS.
> >
> > Thanks again.
> >
> >
> >
> > Tibor Karaszi wrote:
> > > The problem is in the nature of hardware. A page it 8KB. The disks
typically uses a sector as the
> > > level of atomic I/O. A sector is typically 512 bytes. I.e., a page is
typically 16 sectors. A power
> > > failure (or hard stop) in the middle of a page write will cause a
partially written page (a torn
> > > page). Having HW write cache *with a properly implemented battery
backup* will minimize the risk of
> > > a torn page (as long as the battery backup does its job).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > <paul.esposito@.comcast.net> wrote in message
> > > news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> > > > Well, corporate standard is one reason and other systems reside on
the
> > > > server and need to be rebooted.
> > > >
> > > > I'm going to incorporate a netstop into the procedures. I was just
> > > > wondering if this was a common problem.
> > > >
> > > > Regardless of reason, whether I reboot the server once a week or
once
> > > > every year, if the database corrupts on that shutdown there seems to
be
> > > > an issue. I say this because I was told that Microsoft says W2K3
and
> > > > SQL Server are tuned not to need a reboot and that they should be
left
> > > > running. Well some of our servers can't be left running all the
time
> > > > Hurricanes, power outages ... Just seems like a bug to me.
> > > >
> > > > Thanks for the input though. I won't fight it, I'll just do a net
> > > > stop.
> > > >
> > > >
> > > > John Bell wrote:
> > > >> Hi
> > > >>
> > > >> It could be that SQL Server is not responding in time to the
request to
> > > >> terminate.
> > > >> Could you do a NET STOP on the SQL Server Services before shutting
down.
> > > >> Why do you need to shut down each week?
> > > >>
> > > >> John
> > > >>
> > > >> "paul.esposito@.comcast.net" wrote:
> > > >>
> > > >> > Hey Guys,
> > > >> >
> > > >> > Looking to see if anyone has experienced this and if there is
something
> > > >> > we are doing wrong. We have a weekly process to reboot our
windows
> > > >> > servers (we have 100's). The process is a normal windows
shutdown.
> > > >> > Yet weekly one of our servers will come back up and have a 'Torn
Page'
> > > >> > error, then get marked suspect. In investigating this I notice
that
> > > >> > the when the process works, there is a message in the SQL Server
error
> > > >> > log that says 'SQL Server is terminating due to Server shutdown'.
> > > >> > Yet in the cases where it ends up suspect the message is not in
the
> > > >> > log. So to me it looks like the SQL Server is getting shutdown
hard,
> > > >> > but why?
> > > >> >
> > > >> > I end up just restoring the 'Suspect' database, but I'm wondering
why
> > > >> > the process sporatically fails on some servers. Is there a
setting I'm
> > > >> > missing?
> > > >> >
> > > >> > Some specifics about the environment is that these are all
Windows 2003
> > > >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> > > >> > Edition).
> > > >> >
> > > >> > Thanks in advance for any assistance.
> > > >> >
> > > >> >
> > > >
>|||Perhaps this SQL Server instance has been configured with a high value for sp_configure, recovery
interval? Less frequent checkpoint should mean more work for each checkpoint, including the one
submitted for a shutdown...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message news:uRmRmATDHHA.952@.TK2MSFTNGP03.phx.gbl...
> Unfortunately, a Server Shutdown request does exactly this: a request for
> NET STOP on every running service. The problem is that there exists a time
> out value. When SQL Server receives this message, it begins its shutdown
> phase, which issues stops all new connections and issues a hard CHECKPOINT
> of all databases.
> Typically during the maintenance windows (especially the database REORGs),
> this stop request could take quite a bit of time (even more on the start up
> and recovery process).
> Nearly the only way you are going to get a torn page from this (as opposed
> to a lot of recovery rollback operations) is that the disks were powered
> down before the cache committed.
> 1. Plan your database maintenance around these weekly system restarts.
> 2. Disable controller and Windows disk caching unless you can guarantee
> controller power protection.
> I would also agree with most everyone else that a DBMS should be on a
> dedicated system, and, which case, should be very little justification for
> weekly restarts.
> Now, in our Data Center, we have monthly OS updates that are deployed. For
> this, we (the DBA team) have demanded subsequent system restarts (mainly
> because we believe patches leave PendingFileRename operations), but that is
> only on a monthly basis, and we shut down database service before the server
> team is allowed to begin.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
>> What sort of hardware are you using? How are your disks configured? Are
>> there any events or issues with your I/O controllers? How is your I/O
>> solution utilizing it's write cache?
>> I agree that you should be able to restart whenver you want. I have
>> several SQL installations in my environment and have never had an issue
>> with a normal shut down routine. Can you do a manual reboot of the
>> server watching it (by manual I mean not automated but going through
>> start/shutdown) and see what happens?
>> Just a point, while you should be able to reboot whenver you want
>> (gracefully and even safely under some not so graceful situations
>> should not be a problem and has not in my experience with my servers),
>> it is generally not always a best practice to reboot your database
>> servers.
>> For one thing there is no need to if you have a secure server and have
>> only SQL Server running on it. In most of the configurations out there
>> with only SQL running, you won't see a memory leak or any other issue
>> requiring reboot. Also SQL works hard to build it's caches with data
>> and compiled query execution plans that are optimized for your data and
>> workload. Everytime you reboot you clear this cache meaning that after
>> restart the server needs to first optimize and save all the plans again
>> as well as add the frequently accessed data into the data cache. Not a
>> huge deal but just some items to think about.
>> Please give more details regarding my above questions and we will see
>> what we can offer.
>> -Mike Walsh
>> SQL DBA
>>
>> paul.esposito@.comcast.net wrote:
>> > Tibor,
>> >
>> > Thanks for the response. But according to the user and the event logs,
>> > a normal shutdown was requested. i.e. The user requested Windows to
>> > shutdown. This should have sent a message to all the services to stop.
>> > Yet I don't see that request in the SQL Server log. But the event log
>> > does show that a normal shutdown was requested of the OS.
>> >
>> > Thanks again.
>> >
>> >
>> >
>> > Tibor Karaszi wrote:
>> > > The problem is in the nature of hardware. A page it 8KB. The disks
> typically uses a sector as the
>> > > level of atomic I/O. A sector is typically 512 bytes. I.e., a page is
> typically 16 sectors. A power
>> > > failure (or hard stop) in the middle of a page write will cause a
> partially written page (a torn
>> > > page). Having HW write cache *with a properly implemented battery
> backup* will minimize the risk of
>> > > a torn page (as long as the battery backup does its job).
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://www.solidqualitylearning.com/
>> > >
>> > >
>> > > <paul.esposito@.comcast.net> wrote in message
>> > > news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
>> > > > Well, corporate standard is one reason and other systems reside on
> the
>> > > > server and need to be rebooted.
>> > > >
>> > > > I'm going to incorporate a netstop into the procedures. I was just
>> > > > wondering if this was a common problem.
>> > > >
>> > > > Regardless of reason, whether I reboot the server once a week or
> once
>> > > > every year, if the database corrupts on that shutdown there seems to
> be
>> > > > an issue. I say this because I was told that Microsoft says W2K3
> and
>> > > > SQL Server are tuned not to need a reboot and that they should be
> left
>> > > > running. Well some of our servers can't be left running all the
> time
>> > > > Hurricanes, power outages ... Just seems like a bug to me.
>> > > >
>> > > > Thanks for the input though. I won't fight it, I'll just do a net
>> > > > stop.
>> > > >
>> > > >
>> > > > John Bell wrote:
>> > > >> Hi
>> > > >>
>> > > >> It could be that SQL Server is not responding in time to the
> request to
>> > > >> terminate.
>> > > >> Could you do a NET STOP on the SQL Server Services before shutting
> down.
>> > > >> Why do you need to shut down each week?
>> > > >>
>> > > >> John
>> > > >>
>> > > >> "paul.esposito@.comcast.net" wrote:
>> > > >>
>> > > >> > Hey Guys,
>> > > >> >
>> > > >> > Looking to see if anyone has experienced this and if there is
> something
>> > > >> > we are doing wrong. We have a weekly process to reboot our
> windows
>> > > >> > servers (we have 100's). The process is a normal windows
> shutdown.
>> > > >> > Yet weekly one of our servers will come back up and have a 'Torn
> Page'
>> > > >> > error, then get marked suspect. In investigating this I notice
> that
>> > > >> > the when the process works, there is a message in the SQL Server
> error
>> > > >> > log that says 'SQL Server is terminating due to Server shutdown'.
>> > > >> > Yet in the cases where it ends up suspect the message is not in
> the
>> > > >> > log. So to me it looks like the SQL Server is getting shutdown
> hard,
>> > > >> > but why?
>> > > >> >
>> > > >> > I end up just restoring the 'Suspect' database, but I'm wondering
> why
>> > > >> > the process sporatically fails on some servers. Is there a
> setting I'm
>> > > >> > missing?
>> > > >> >
>> > > >> > Some specifics about the environment is that these are all
> Windows 2003
>> > > >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
>> > > >> > Edition).
>> > > >> >
>> > > >> > Thanks in advance for any assistance.
>> > > >> >
>> > > >> >
>> > > >
>|||Certainly a good place to examine. But, more often than not, especially if
this is in the evening on a weekend/Friday evening, the Index Rebuilds are
racking up large transactions. If the database(s) is(are) of any
appreciable size, then it could "hang" for awhile before letting go.
Anthony Thomas
--
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwmyoYDHHA.4144@.TK2MSFTNGP06.phx.gbl...
> Perhaps this SQL Server instance has been configured with a high value for
sp_configure, recovery
> interval? Less frequent checkpoint should mean more work for each
checkpoint, including the one
> submitted for a shutdown...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uRmRmATDHHA.952@.TK2MSFTNGP03.phx.gbl...
> > Unfortunately, a Server Shutdown request does exactly this: a request
for
> > NET STOP on every running service. The problem is that there exists a
time
> > out value. When SQL Server receives this message, it begins its
shutdown
> > phase, which issues stops all new connections and issues a hard
CHECKPOINT
> > of all databases.
> >
> > Typically during the maintenance windows (especially the database
REORGs),
> > this stop request could take quite a bit of time (even more on the start
up
> > and recovery process).
> >
> > Nearly the only way you are going to get a torn page from this (as
opposed
> > to a lot of recovery rollback operations) is that the disks were powered
> > down before the cache committed.
> >
> > 1. Plan your database maintenance around these weekly system restarts.
> > 2. Disable controller and Windows disk caching unless you can guarantee
> > controller power protection.
> >
> > I would also agree with most everyone else that a DBMS should be on a
> > dedicated system, and, which case, should be very little justification
for
> > weekly restarts.
> >
> > Now, in our Data Center, we have monthly OS updates that are deployed.
For
> > this, we (the DBA team) have demanded subsequent system restarts (mainly
> > because we believe patches leave PendingFileRename operations), but that
is
> > only on a monthly basis, and we shut down database service before the
server
> > team is allowed to begin.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> > news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
> >> What sort of hardware are you using? How are your disks configured? Are
> >> there any events or issues with your I/O controllers? How is your I/O
> >> solution utilizing it's write cache?
> >>
> >> I agree that you should be able to restart whenver you want. I have
> >> several SQL installations in my environment and have never had an issue
> >> with a normal shut down routine. Can you do a manual reboot of the
> >> server watching it (by manual I mean not automated but going through
> >> start/shutdown) and see what happens?
> >>
> >> Just a point, while you should be able to reboot whenver you want
> >> (gracefully and even safely under some not so graceful situations
> >> should not be a problem and has not in my experience with my servers),
> >> it is generally not always a best practice to reboot your database
> >> servers.
> >>
> >> For one thing there is no need to if you have a secure server and have
> >> only SQL Server running on it. In most of the configurations out there
> >> with only SQL running, you won't see a memory leak or any other issue
> >> requiring reboot. Also SQL works hard to build it's caches with data
> >> and compiled query execution plans that are optimized for your data and
> >> workload. Everytime you reboot you clear this cache meaning that after
> >> restart the server needs to first optimize and save all the plans again
> >> as well as add the frequently accessed data into the data cache. Not a
> >> huge deal but just some items to think about.
> >>
> >> Please give more details regarding my above questions and we will see
> >> what we can offer.
> >>
> >> -Mike Walsh
> >> SQL DBA
> >>
> >>
> >> paul.esposito@.comcast.net wrote:
> >> > Tibor,
> >> >
> >> > Thanks for the response. But according to the user and the event
logs,
> >> > a normal shutdown was requested. i.e. The user requested Windows to
> >> > shutdown. This should have sent a message to all the services to
stop.
> >> > Yet I don't see that request in the SQL Server log. But the event
log
> >> > does show that a normal shutdown was requested of the OS.
> >> >
> >> > Thanks again.
> >> >
> >> >
> >> >
> >> > Tibor Karaszi wrote:
> >> > > The problem is in the nature of hardware. A page it 8KB. The disks
> > typically uses a sector as the
> >> > > level of atomic I/O. A sector is typically 512 bytes. I.e., a page
is
> > typically 16 sectors. A power
> >> > > failure (or hard stop) in the middle of a page write will cause a
> > partially written page (a torn
> >> > > page). Having HW write cache *with a properly implemented battery
> > backup* will minimize the risk of
> >> > > a torn page (as long as the battery backup does its job).
> >> > >
> >> > > --
> >> > > Tibor Karaszi, SQL Server MVP
> >> > > http://www.karaszi.com/sqlserver/default.asp
> >> > > http://www.solidqualitylearning.com/
> >> > >
> >> > >
> >> > > <paul.esposito@.comcast.net> wrote in message
> >> > > news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> >> > > > Well, corporate standard is one reason and other systems reside
on
> > the
> >> > > > server and need to be rebooted.
> >> > > >
> >> > > > I'm going to incorporate a netstop into the procedures. I was
just
> >> > > > wondering if this was a common problem.
> >> > > >
> >> > > > Regardless of reason, whether I reboot the server once a week or
> > once
> >> > > > every year, if the database corrupts on that shutdown there seems
to
> > be
> >> > > > an issue. I say this because I was told that Microsoft says W2K3
> > and
> >> > > > SQL Server are tuned not to need a reboot and that they should be
> > left
> >> > > > running. Well some of our servers can't be left running all the
> > time
> >> > > > Hurricanes, power outages ... Just seems like a bug to me.
> >> > > >
> >> > > > Thanks for the input though. I won't fight it, I'll just do a
net
> >> > > > stop.
> >> > > >
> >> > > >
> >> > > > John Bell wrote:
> >> > > >> Hi
> >> > > >>
> >> > > >> It could be that SQL Server is not responding in time to the
> > request to
> >> > > >> terminate.
> >> > > >> Could you do a NET STOP on the SQL Server Services before
shutting
> > down.
> >> > > >> Why do you need to shut down each week?
> >> > > >>
> >> > > >> John
> >> > > >>
> >> > > >> "paul.esposito@.comcast.net" wrote:
> >> > > >>
> >> > > >> > Hey Guys,
> >> > > >> >
> >> > > >> > Looking to see if anyone has experienced this and if there is
> > something
> >> > > >> > we are doing wrong. We have a weekly process to reboot our
> > windows
> >> > > >> > servers (we have 100's). The process is a normal windows
> > shutdown.
> >> > > >> > Yet weekly one of our servers will come back up and have a
'Torn
> > Page'
> >> > > >> > error, then get marked suspect. In investigating this I
notice
> > that
> >> > > >> > the when the process works, there is a message in the SQL
Server
> > error
> >> > > >> > log that says 'SQL Server is terminating due to Server
shutdown'.
> >> > > >> > Yet in the cases where it ends up suspect the message is not
in
> > the
> >> > > >> > log. So to me it looks like the SQL Server is getting
shutdown
> > hard,
> >> > > >> > but why?
> >> > > >> >
> >> > > >> > I end up just restoring the 'Suspect' database, but I'm
wondering
> > why
> >> > > >> > the process sporatically fails on some servers. Is there a
> > setting I'm
> >> > > >> > missing?
> >> > > >> >
> >> > > >> > Some specifics about the environment is that these are all
> > Windows 2003
> >> > > >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> >> > > >> > Edition).
> >> > > >> >
> >> > > >> > Thanks in advance for any assistance.
> >> > > >> >
> >> > > >> >
> >> > > >
> >>
> >
> >
>

Database marked Suspect after normabl Windows Shutdown

Sunday, March 11, 2012

DataBase Maintenance Plan(DMP) Questions

Hello guys,
I have few Qs..
1) I created DMP and chose all database for backup. BUt master db doesnt get
backup. Any idea?
2) if in each and every database i checked the "update statistics" options,
do i need to check "reorganize data and index pages" in the DMP wizard? are
they different? what way they are different?
3) what's the best method to use for email.SQLMail or Exchange? Any good
link to configure the exchange and outlook to send emails?
4) Finally, is there any good links that shows step by step on restore
process? i am blur , esp, at the backup the transaction log and restoring it
back when the database is corrupted... (looking for up to the time restore)
Thanks a lot guys!
Hi
I don't really use maintenance plans as I prefer to write my own, but I will
try to answer inline:
"rupart" wrote:

> Hello guys,
> I have few Qs..
> 1) I created DMP and chose all database for backup. BUt master db doesnt get
> backup. Any idea?
I would expect the "All Databases" option to do what it says.

> 2) if in each and every database i checked the "update statistics" options,
> do i need to check "reorganize data and index pages" in the DMP wizard? are
> they different? what way they are different?
I believe the reorganize data and index pages does a DBCC DBREINDEX
(profiling the plan when it is running will show what it does). This is very
important as it will take out fragmentation in the pages and therefore reduce
the number of reads you need to get the data.
SQL Server keeps statistics about the distribution of the key values in each
index and uses these statistics to determine which index(es) to use in query
processing. Query optimization depends on the accuracy of the information,
therefore keeping these up-to-date is important.

> 3) what's the best method to use for email.SQLMail or Exchange? Any good
> link to configure the exchange and outlook to send emails?
http://msdn.microsoft.com/library/de...rverE-mail.asp
More links:
http://www.aspfaq.com/show.asp?id=2403
Alternatively you may want to use SMTP:
http://www.sqldev.net/xp/xpsmtp.htm

> 4) Finally, is there any good links that shows step by step on restore
> process? i am blur , esp, at the backup the transaction log and restoring it
> back when the database is corrupted... (looking for up to the time restore)
>
The best place start when requiring information on SQL server is usually
Books online. Time spent reading this will usually pay itself back
substantially in the future.
The latest version can be downloaded from:
http://www.microsoft.com/sql/techinf...2000/books.asp
Online copies can be found at
http://msdn.microsoft.com/library/de...asp?frame=true
e.g the restore command and examples at:
http://msdn.microsoft.com/library/de...asp?frame=true

> Thanks a lot guys!
>
HTH
John
|||Before turning out automatic fragmentation remoal in a maintenance plan, I
recommend you read the whitepaper below which explains when and how it is
best to remove fragmentation, and when it simply doesn't matter.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:92DC9AC7-705F-4444-B84B-793BBF4F5250@.microsoft.com...
> Hi
> I don't really use maintenance plans as I prefer to write my own, but I
will[vbcol=seagreen]
> try to answer inline:
> "rupart" wrote:
get[vbcol=seagreen]
> I would expect the "All Databases" option to do what it says.
options,[vbcol=seagreen]
are
> I believe the reorganize data and index pages does a DBCC DBREINDEX
> (profiling the plan when it is running will show what it does). This is
very
> important as it will take out fragmentation in the pages and therefore
reduce
> the number of reads you need to get the data.
> SQL Server keeps statistics about the distribution of the key values in
each
> index and uses these statistics to determine which index(es) to use in
query
> processing. Query optimization depends on the accuracy of the
information,
> therefore keeping these up-to-date is important.
>
>
http://msdn.microsoft.com/library/de...rverE-mail.asp[vbcol=seagreen]
> More links:
> http://www.aspfaq.com/show.asp?id=2403
> Alternatively you may want to use SMTP:
> http://www.sqldev.net/xp/xpsmtp.htm
restoring it[vbcol=seagreen]
restore)
> The best place start when requiring information on SQL server is usually
> Books online. Time spent reading this will usually pay itself back
> substantially in the future.
> The latest version can be downloaded from:
> http://www.microsoft.com/sql/techinf...2000/books.asp
> Online copies can be found at
>
http://msdn.microsoft.com/library/de...asp?frame=true
> e.g the restore command and examples at:
>
http://msdn.microsoft.com/library/de...asp?frame=true
>
> HTH
> John

DataBase Maintenance Plan(DMP) Questions

Hello guys,
I have few Qs..
1) I created DMP and chose all database for backup. BUt master db doesnt get
backup. Any idea?
2) if in each and every database i checked the "update statistics" options,
do i need to check "reorganize data and index pages" in the DMP wizard? are
they different? what way they are different?
3) what's the best method to use for email.SQLMail or Exchange? Any good
link to configure the exchange and outlook to send emails?
4) Finally, is there any good links that shows step by step on restore
process? i am blur , esp, at the backup the transaction log and restoring it
back when the database is corrupted... (looking for up to the time restore)
Thanks a lot guys!Hi
I don't really use maintenance plans as I prefer to write my own, but I will
try to answer inline:
"rupart" wrote:

> Hello guys,
> I have few Qs..
> 1) I created DMP and chose all database for backup. BUt master db doesnt g
et
> backup. Any idea?
I would expect the "All Databases" option to do what it says.

> 2) if in each and every database i checked the "update statistics" options
,
> do i need to check "reorganize data and index pages" in the DMP wizard? ar
e
> they different? what way they are different?
I believe the reorganize data and index pages does a DBCC DBREINDEX
(profiling the plan when it is running will show what it does). This is very
important as it will take out fragmentation in the pages and therefore reduc
e
the number of reads you need to get the data.
SQL Server keeps statistics about the distribution of the key values in each
index and uses these statistics to determine which index(es) to use in query
processing. Query optimization depends on the accuracy of the information,
therefore keeping these up-to-date is important.

> 3) what's the best method to use for email.SQLMail or Exchange? Any good
> link to configure the exchange and outlook to send emails?
http://msdn.microsoft.com/library/d...erverE-mail.asp
More links:
http://www.aspfaq.com/show.asp?id=2403
Alternatively you may want to use SMTP:
http://www.sqldev.net/xp/xpsmtp.htm

> 4) Finally, is there any good links that shows step by step on restore
> process? i am blur , esp, at the backup the transaction log and restoring
it
> back when the database is corrupted... (looking for up to the time restore
)
>
The best place start when requiring information on SQL server is usually
Books online. Time spent reading this will usually pay itself back
substantially in the future.
The latest version can be downloaded from:
http://www.microsoft.com/sql/techin.../2000/books.asp
Online copies can be found at
http://msdn.microsoft.com/library/d...asp?frame=true
e.g the restore command and examples at:
http://msdn.microsoft.com/library/d...asp?frame=true

> Thanks a lot guys!
>
HTH
John|||Before turning out automatic fragmentation remoal in a maintenance plan, I
recommend you read the whitepaper below which explains when and how it is
best to remove fragmentation, and when it simply doesn't matter.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:92DC9AC7-705F-4444-B84B-793BBF4F5250@.microsoft.com...
> Hi
> I don't really use maintenance plans as I prefer to write my own, but I
will
> try to answer inline:
> "rupart" wrote:
>
get[vbcol=seagreen]
> I would expect the "All Databases" option to do what it says.
>
options,[vbcol=seagreen]
are[vbcol=seagreen]
> I believe the reorganize data and index pages does a DBCC DBREINDEX
> (profiling the plan when it is running will show what it does). This is
very
> important as it will take out fragmentation in the pages and therefore
reduce
> the number of reads you need to get the data.
> SQL Server keeps statistics about the distribution of the key values in
each
> index and uses these statistics to determine which index(es) to use in
query
> processing. Query optimization depends on the accuracy of the
information,
> therefore keeping these up-to-date is important.
>
>
http://msdn.microsoft.com/library/d...ail.asp

> More links:
> http://www.aspfaq.com/show.asp?id=2403
> Alternatively you may want to use SMTP:
> http://www.sqldev.net/xp/xpsmtp.htm
>
restoring it[vbcol=seagreen]
restore)[vbcol=seagreen]
> The best place start when requiring information on SQL server is usually
> Books online. Time spent reading this will usually pay itself back
> substantially in the future.
> The latest version can be downloaded from:
> http://www.microsoft.com/sql/techin.../2000/books.asp
> Online copies can be found at
>
http://msdn.microsoft.com/library/d...me=tr
ue
> e.g the restore command and examples at:
>
http://msdn.microsoft.com/library/d...me=t
rue
>
> HTH
> John

DataBase Maintenance Plan(DMP) Questions

Hello guys,
I have few Qs..
1) I created DMP and chose all database for backup. BUt master db doesnt get
backup. Any idea?
2) if in each and every database i checked the "update statistics" options,
do i need to check "reorganize data and index pages" in the DMP wizard? are
they different? what way they are different?
3) what's the best method to use for email.SQLMail or Exchange? Any good
link to configure the exchange and outlook to send emails?
4) Finally, is there any good links that shows step by step on restore
process? i am blur , esp, at the backup the transaction log and restoring it
back when the database is corrupted... (looking for up to the time restore)
Thanks a lot guys!Hi
I don't really use maintenance plans as I prefer to write my own, but I will
try to answer inline:
"rupart" wrote:
> Hello guys,
> I have few Qs..
> 1) I created DMP and chose all database for backup. BUt master db doesnt get
> backup. Any idea?
I would expect the "All Databases" option to do what it says.
> 2) if in each and every database i checked the "update statistics" options,
> do i need to check "reorganize data and index pages" in the DMP wizard? are
> they different? what way they are different?
I believe the reorganize data and index pages does a DBCC DBREINDEX
(profiling the plan when it is running will show what it does). This is very
important as it will take out fragmentation in the pages and therefore reduce
the number of reads you need to get the data.
SQL Server keeps statistics about the distribution of the key values in each
index and uses these statistics to determine which index(es) to use in query
processing. Query optimization depends on the accuracy of the information,
therefore keeping these up-to-date is important.
> 3) what's the best method to use for email.SQLMail or Exchange? Any good
> link to configure the exchange and outlook to send emails?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba01/html/SQLServerE-mail.asp
More links:
http://www.aspfaq.com/show.asp?id=2403
Alternatively you may want to use SMTP:
http://www.sqldev.net/xp/xpsmtp.htm
> 4) Finally, is there any good links that shows step by step on restore
> process? i am blur , esp, at the backup the transaction log and restoring it
> back when the database is corrupted... (looking for up to the time restore)
>
The best place start when requiring information on SQL server is usually
Books online. Time spent reading this will usually pay itself back
substantially in the future.
The latest version can be downloaded from:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Online copies can be found a
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp?frame=true
e.g the restore command and examples at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp?frame=true
> Thanks a lot guys!
>
HTH
John|||Before turning out automatic fragmentation remoal in a maintenance plan, I
recommend you read the whitepaper below which explains when and how it is
best to remove fragmentation, and when it simply doesn't matter.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:92DC9AC7-705F-4444-B84B-793BBF4F5250@.microsoft.com...
> Hi
> I don't really use maintenance plans as I prefer to write my own, but I
will
> try to answer inline:
> "rupart" wrote:
> > Hello guys,
> > I have few Qs..
> >
> > 1) I created DMP and chose all database for backup. BUt master db doesnt
get
> > backup. Any idea?
> I would expect the "All Databases" option to do what it says.
> >
> > 2) if in each and every database i checked the "update statistics"
options,
> > do i need to check "reorganize data and index pages" in the DMP wizard?
are
> > they different? what way they are different?
> I believe the reorganize data and index pages does a DBCC DBREINDEX
> (profiling the plan when it is running will show what it does). This is
very
> important as it will take out fragmentation in the pages and therefore
reduce
> the number of reads you need to get the data.
> SQL Server keeps statistics about the distribution of the key values in
each
> index and uses these statistics to determine which index(es) to use in
query
> processing. Query optimization depends on the accuracy of the
information,
> therefore keeping these up-to-date is important.
> >
> > 3) what's the best method to use for email.SQLMail or Exchange? Any good
> > link to configure the exchange and outlook to send emails?
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba01/html/SQLServerE-mail.asp
> More links:
> http://www.aspfaq.com/show.asp?id=2403
> Alternatively you may want to use SMTP:
> http://www.sqldev.net/xp/xpsmtp.htm
> >
> > 4) Finally, is there any good links that shows step by step on restore
> > process? i am blur , esp, at the backup the transaction log and
restoring it
> > back when the database is corrupted... (looking for up to the time
restore)
> >
> The best place start when requiring information on SQL server is usually
> Books online. Time spent reading this will usually pay itself back
> substantially in the future.
> The latest version can be downloaded from:
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> Online copies can be found at
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp?frame=true
> e.g the restore command and examples at:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp?frame=true
> > Thanks a lot guys!
> >
> >
> HTH
> John

Friday, February 17, 2012

DataBase Log error.

Hi guys,

Toda i try to insert one row in my databse product table.....that time i got this kind of error.....

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database 'testDatabase' is full. Back up the transaction log for the database to free up some log space.

the transaction log for the database to free up some log space.

/xxxx/yyyy/zzzzzzzzzzzz.asp, line 109

any one know about this kind of error .......pls help me......

thanks in advanseHave a look at this:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#EYRAE|||ever since they changed the default recovery mode from 7 to 2000 I wish they had added a screen to the setup with big flashing letters about the implications.|||Can i use following command for this one,

dbcc shrinkfile ([dbname_log])|||well you could do a dbcc shrinkfile ([dbname_log],1) but the real issue you need to address here is your recovery model and what I suspect is your non-existent disaster recovery plan.

sql2k defaults to full recovery. which means you can recover to any point in time as long as you are performing database and transaction log backups. I suspect you are either not doing this or not doing it frequently enough because if you had been your transaction log would not have filled up your drive. unless of course you have your ldf and mdf on the same drive god forbid. In which case you are just straight running out of space and you do not really care what happens if you lose a drive or a machine which will eventually happen.

Monday, March 19, 2012

Database marked Suspect after normabl Windows Shutdown

Database marked Suspect after normabl Windows Shutdown

Database marked Suspect after normabl Windows Shutdown

Sunday, March 11, 2012

DataBase Maintenance Plan(DMP) Questions

DataBase Maintenance Plan(DMP) Questions

DataBase Maintenance Plan(DMP) Questions

Friday, February 17, 2012

DataBase Log error.

Database Overwritten

Blog Archive

About Me