Problem with dsigning tables. when changing datatype, size
etc'
i get message:
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid
cursor state
microsoft is reffering to: http://support.microsoft.com/?
kbid=831997
but there is nothing there that can fix the problem
what can i do to fix it?
Thankseran,shalom
Check out if the log file is full, what is recovery model of the log file?
<eranv@.gat.co.il> wrote in message
news:369601c48812$bb6d5530$a301280a@.phx.gbl...
> Problem with dsigning tables. when changing datatype, size
> etc'
> i get message:
> ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid
> cursor state
> microsoft is reffering to: http://support.microsoft.com/?
> kbid=831997
> but there is nothing there that can fix the problem
> what can i do to fix it?
> Thanks|||Uri hi and thanks for responding,
The log file is not full and the mode is set to "simple"
this error occurs on all tables and in every database. I
think it has something to do with hotfix or patch that we
installed after sp3a
we are working around that problem by recreating the
tables everytime a change is needed. you can imagin how
hard it is to work like that.
thanks again
Eran
.
>--Original Message--
>eran,shalom
>Check out if the log file is full, what is recovery model
of the log file?
>
><eranv@.gat.co.il> wrote in message
>news:369601c48812$bb6d5530$a301280a@.phx.gbl...
>> Problem with dsigning tables. when changing datatype,
size
>> etc'
>> i get message:
>> ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid
>> cursor state
>> microsoft is reffering to:
http://support.microsoft.com/?
>> kbid=831997
>> but there is nothing there that can fix the problem
>> what can i do to fix it?
>> Thanks
>
>.
>|||Eran
I see, have you looked at http://support.microsoft.com/?kbid=821334
Do you have the same MDAC on the client and server?
<anonymous@.discussions.microsoft.com> wrote in message
news:acf101c48834$30264d70$a601280a@.phx.gbl...
> Uri hi and thanks for responding,
> The log file is not full and the mode is set to "simple"
> this error occurs on all tables and in every database. I
> think it has something to do with hotfix or patch that we
> installed after sp3a
> we are working around that problem by recreating the
> tables everytime a change is needed. you can imagin how
> hard it is to work like that.
> thanks again
> Eran
> .
>
> >--Original Message--
> >eran,shalom
> >Check out if the log file is full, what is recovery model
> of the log file?
> >
> >
> ><eranv@.gat.co.il> wrote in message
> >news:369601c48812$bb6d5530$a301280a@.phx.gbl...
> >> Problem with dsigning tables. when changing datatype,
> size
> >> etc'
> >> i get message:
> >> ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid
> >> cursor state
> >>
> >> microsoft is reffering to:
> http://support.microsoft.com/?
> >> kbid=831997
> >> but there is nothing there that can fix the problem
> >> what can i do to fix it?
> >>
> >> Thanks
> >
> >
> >.
> >|||yes the MDAC versions are the same. the error message
occurs also when working directly on the server.
a general search led me to http://support.microsoft.com/?
kbid=831997
and i think that the link discribes an issue somewhat
relevant, however i could not find a workaround or hotfix
to correct the problem.
>--Original Message--
>Eran
>I see, have you looked at http://support.microsoft.com/?
kbid=821334
>Do you have the same MDAC on the client and server?
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:acf101c48834$30264d70$a601280a@.phx.gbl...
>> Uri hi and thanks for responding,
>> The log file is not full and the mode is set to "simple"
>> this error occurs on all tables and in every database. I
>> think it has something to do with hotfix or patch that
we
>> installed after sp3a
>> we are working around that problem by recreating the
>> tables everytime a change is needed. you can imagin how
>> hard it is to work like that.
>> thanks again
>> Eran
>> .
>>
>> >--Original Message--
>> >eran,shalom
>> >Check out if the log file is full, what is recovery
model
>> of the log file?
>> >
>> >
>> ><eranv@.gat.co.il> wrote in message
>> >news:369601c48812$bb6d5530$a301280a@.phx.gbl...
>> >> Problem with dsigning tables. when changing datatype,
>> size
>> >> etc'
>> >> i get message:
>> >> ODBC error: [Microsoft][ODBC SQL Server Driver]
Invalid
>> >> cursor state
>> >>
>> >> microsoft is reffering to:
>> http://support.microsoft.com/?
>> >> kbid=831997
>> >> but there is nothing there that can fix the problem
>> >> what can i do to fix it?
>> >>
>> >> Thanks
>> >
>> >
>> >.
>> >
>
>.
>|||Eran
You may want to delete the hotfix and re-install sp3.
<anonymous@.discussions.microsoft.com> wrote in message
news:ad1f01c4883a$cd180280$a601280a@.phx.gbl...
> yes the MDAC versions are the same. the error message
> occurs also when working directly on the server.
> a general search led me to http://support.microsoft.com/?
> kbid=831997
> and i think that the link discribes an issue somewhat
> relevant, however i could not find a workaround or hotfix
> to correct the problem.
> >--Original Message--
> >Eran
> >I see, have you looked at http://support.microsoft.com/?
> kbid=821334
> >Do you have the same MDAC on the client and server?
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:acf101c48834$30264d70$a601280a@.phx.gbl...
> >> Uri hi and thanks for responding,
> >> The log file is not full and the mode is set to "simple"
> >> this error occurs on all tables and in every database. I
> >> think it has something to do with hotfix or patch that
> we
> >> installed after sp3a
> >> we are working around that problem by recreating the
> >> tables everytime a change is needed. you can imagin how
> >> hard it is to work like that.
> >> thanks again
> >> Eran
> >> .
> >>
> >>
> >> >--Original Message--
> >> >eran,shalom
> >> >Check out if the log file is full, what is recovery
> model
> >> of the log file?
> >> >
> >> >
> >> ><eranv@.gat.co.il> wrote in message
> >> >news:369601c48812$bb6d5530$a301280a@.phx.gbl...
> >> >> Problem with dsigning tables. when changing datatype,
> >> size
> >> >> etc'
> >> >> i get message:
> >> >> ODBC error: [Microsoft][ODBC SQL Server Driver]
> Invalid
> >> >> cursor state
> >> >>
> >> >> microsoft is reffering to:
> >> http://support.microsoft.com/?
> >> >> kbid=831997
> >> >> but there is nothing there that can fix the problem
> >> >> what can i do to fix it?
> >> >>
> >> >> Thanks
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
Showing posts with label size. Show all posts
Showing posts with label size. Show all posts
Monday, March 19, 2012
Friday, February 24, 2012
interview question
here is a question asked in a interview:
u created the sales database with the initial size of hte transaction log of 50 mb with autogrow, but no autoshrink
u do not make any backup and donot set the truncate log on check point option to on.
what will be the transaction log size after the next check point has passed?Umm... >= 50mb|||You are in a job interview where the person hiring asks you inane and pointless questions that have little or no value in either database administration or database design, and for which the answers could easily be looked up in Books On Line. At the end of the interview, you are offered the job at a substantial salary bump. Do you work for the dolt, or do you continue seeking an employer that has actual competence?|||lol@.blindman. In the instance above, you tell him he hasn't provided you enough information (which he hasn't btw). If he could do some research though, create a business proposition for why he should waste your time answering stupid questions, get business signoff, send it through QA and user-acceptance testing, then test the release, you'll have a production answer for his highness right away.|||First, there's no "Trancate on checkpoint" in 2K, which means they are talking about 7.0 or 6.5, which is bad in itself.
Second, unless a user process kicked in within less than 0.00000001 millisecond after the "create database..." statement completed, peterlemonjello's answer is almost absolutely correct, - except it will be 50MB exactly, not more or equal to.|||hmmm what an interesting way of asking for clarification... I like it. :D
by the way, it was't me who was being interviewed and it was a colleague of mine, I wasn't sure about the question itself so thought to get some expert comments from the guru's here.
anyways thnks for the replies :p
u created the sales database with the initial size of hte transaction log of 50 mb with autogrow, but no autoshrink
u do not make any backup and donot set the truncate log on check point option to on.
what will be the transaction log size after the next check point has passed?Umm... >= 50mb|||You are in a job interview where the person hiring asks you inane and pointless questions that have little or no value in either database administration or database design, and for which the answers could easily be looked up in Books On Line. At the end of the interview, you are offered the job at a substantial salary bump. Do you work for the dolt, or do you continue seeking an employer that has actual competence?|||lol@.blindman. In the instance above, you tell him he hasn't provided you enough information (which he hasn't btw). If he could do some research though, create a business proposition for why he should waste your time answering stupid questions, get business signoff, send it through QA and user-acceptance testing, then test the release, you'll have a production answer for his highness right away.|||First, there's no "Trancate on checkpoint" in 2K, which means they are talking about 7.0 or 6.5, which is bad in itself.
Second, unless a user process kicked in within less than 0.00000001 millisecond after the "create database..." statement completed, peterlemonjello's answer is almost absolutely correct, - except it will be 50MB exactly, not more or equal to.|||hmmm what an interesting way of asking for clarification... I like it. :D
by the way, it was't me who was being interviewed and it was a colleague of mine, I wasn't sure about the question itself so thought to get some expert comments from the guru's here.
anyways thnks for the replies :p
Interrupt DBCC SHRINKDATABASE
BACKGROUND:
We have a large (330 GB) OLTP database. After archiving a
lot of records, we still have the same size of db and full
backups (BackupExec). The database has 2 filegroups, 24
data files, a log file over 4 logical drives. Log is
small, gets backup every hour and its file is no issue.
The data files range from 50 GB down to 70 MB since drives
and files were added as needed. Files auto grow, but do
not auto shrink. All maintenance is done manually via
Query Analyzer during scheduled times. A replicated
database with a subset of the information is used for OLTP
during these times.
Our goal was to shrink the database to recover OS space
and minimize backup time. This would be done with NO
LOAD. We scheduled 5 to 24 hours (based on some rough
estimates).
The SHRINKDATABASE has been running for 26 hours when load
was shifted back. It has now been running 31 hours. We
may need to cancel (using stop on Query Analyzer. However,
we hesitate to do so and risk corruption.
I found one reference in a PPT about shrink saying "may be
stopped without losing work". However, this is not a
mainstream URL:
http://www.microsoft.com/uk/partner/strategy/server_busines
s_agility/customers/business-
intelligence/downloads/RDBMSFeaturesandDataWarehousing.ppt
I would like an official, "Yes, if you started the DBCC
SHRINKDATABASE in the Query Analyzer, you can stop it by
using the Cancel Query. There will be now rollback time
or risk of corruption".
The documentation for these long running commands should
have a section on Cancellation.
Even an unofficial response is welcome.
Thanks,
SMI had what sounds like the same problem on SQL 2000. I calculated that the
database would take a week to come up. Not acceptable.
I had to use this solution 3 times: It worked just fine each time. I did
not get fired
It comes down to running this command;
UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='DBNAME'
then restart the server.
It set the database in emergency mode, which bypasses all recovery stuff
http://support.microsoft.com/default.aspx?scid=kb;EN-US;165918
"Spencer Morley" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote in
message news:023001c34f1d$32c14a40$a101280a@.phx.gbl...
> BACKGROUND:
> We have a large (330 GB) OLTP database. After archiving a
> lot of records, we still have the same size of db and full
> backups (BackupExec). The database has 2 filegroups, 24
> data files, a log file over 4 logical drives. Log is
> small, gets backup every hour and its file is no issue.
> The data files range from 50 GB down to 70 MB since drives
> and files were added as needed. Files auto grow, but do
> not auto shrink. All maintenance is done manually via
> Query Analyzer during scheduled times. A replicated
> database with a subset of the information is used for OLTP
> during these times.
> Our goal was to shrink the database to recover OS space
> and minimize backup time. This would be done with NO
> LOAD. We scheduled 5 to 24 hours (based on some rough
> estimates).
> The SHRINKDATABASE has been running for 26 hours when load
> was shifted back. It has now been running 31 hours. We
> may need to cancel (using stop on Query Analyzer. However,
> we hesitate to do so and risk corruption.
> I found one reference in a PPT about shrink saying "may be
> stopped without losing work". However, this is not a
> mainstream URL:
> http://www.microsoft.com/uk/partner/strategy/server_busines
> s_agility/customers/business-
> intelligence/downloads/RDBMSFeaturesandDataWarehousing.ppt
> I would like an official, "Yes, if you started the DBCC
> SHRINKDATABASE in the Query Analyzer, you can stop it by
> using the Cancel Query. There will be now rollback time
> or risk of corruption".
> The documentation for these long running commands should
> have a section on Cancellation.
> Even an unofficial response is welcome.
> Thanks,
> SM
>|||You absolutely can stop shrink at any time with no rollback cost and no risk
of corruption.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spencer Morley" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote in
message news:023001c34f1d$32c14a40$a101280a@.phx.gbl...
> BACKGROUND:
> We have a large (330 GB) OLTP database. After archiving a
> lot of records, we still have the same size of db and full
> backups (BackupExec). The database has 2 filegroups, 24
> data files, a log file over 4 logical drives. Log is
> small, gets backup every hour and its file is no issue.
> The data files range from 50 GB down to 70 MB since drives
> and files were added as needed. Files auto grow, but do
> not auto shrink. All maintenance is done manually via
> Query Analyzer during scheduled times. A replicated
> database with a subset of the information is used for OLTP
> during these times.
> Our goal was to shrink the database to recover OS space
> and minimize backup time. This would be done with NO
> LOAD. We scheduled 5 to 24 hours (based on some rough
> estimates).
> The SHRINKDATABASE has been running for 26 hours when load
> was shifted back. It has now been running 31 hours. We
> may need to cancel (using stop on Query Analyzer. However,
> we hesitate to do so and risk corruption.
> I found one reference in a PPT about shrink saying "may be
> stopped without losing work". However, this is not a
> mainstream URL:
> http://www.microsoft.com/uk/partner/strategy/server_busines
> s_agility/customers/business-
> intelligence/downloads/RDBMSFeaturesandDataWarehousing.ppt
> I would like an official, "Yes, if you started the DBCC
> SHRINKDATABASE in the Query Analyzer, you can stop it by
> using the Cancel Query. There will be now rollback time
> or risk of corruption".
> The documentation for these long running commands should
> have a section on Cancellation.
> Even an unofficial response is welcome.
> Thanks,
> SM
>|||Hi Spencer,
Firstly, you don't say how big the database is or what other load is running
on it. Shrink will wait for locks it cannot immediately acquire so there may
be something blocking it. This is especially likely with long-running,
pervasive update queries.
Shrink will always run quicker without a concurrent workload, so quiescing
the database (using single-user mode, for example) should decrease the
runtime.
The wizard option you mention is the same underlying code as shrinkdatabase,
and so can be run online and can also be safely interrupted with no risk of
corruption or losing already completed work.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote in
message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> While, we have been running the SHRINKDATABASE for 98
> hours now! Production does not seem to be affected.
> Assume this is because we have ample CPU, memory, and I/O?
> Should I expect this kind of run time on a database of
> this size?
> The process has shunk 6 files so far and we have gained
> about 2 GB per file. These are the files on the filegroup
> that is more static, so we expect better result on the
> other files. Also, these are the files on the RAID 5.
> Once the work on the current file is complete, we will
> move to those on the RAID 10, thus speedier?
> I plan to let this process run to completion, but we have
> a number of db's of simular size that will need to be
> shrunk as archiving is done.
> My question:
> Is there a way to force priority to a shrink (single user
> mode, etc) that will can be used while db is out of
> production that would be quicker?
> Also, if I use the "Database Maintenance Plan
> Wizzard", "Update Data Optimization Information"
> page, "Remove unused space from database files" option to
> shrink will it faster? Is the lower level process
> essentially the same as a SHRINKDATABSE? Can it be run
> while database is in use?
>|||yah but this database is in recovery mode, no?
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23Mi22eiUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> Hi Spencer,
> Firstly, you don't say how big the database is or what other load is
running
> on it. Shrink will wait for locks it cannot immediately acquire so there
may
> be something blocking it. This is especially likely with long-running,
> pervasive update queries.
> Shrink will always run quicker without a concurrent workload, so quiescing
> the database (using single-user mode, for example) should decrease the
> runtime.
> The wizard option you mention is the same underlying code as
shrinkdatabase,
> and so can be run online and can also be safely interrupted with no risk
of
> corruption or losing already completed work.
> Regards,
> Paul.
> --
> Paul Randal
> DBCC Technical Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote
in
> message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> > While, we have been running the SHRINKDATABASE for 98
> > hours now! Production does not seem to be affected.
> > Assume this is because we have ample CPU, memory, and I/O?
> >
> > Should I expect this kind of run time on a database of
> > this size?
> >
> > The process has shunk 6 files so far and we have gained
> > about 2 GB per file. These are the files on the filegroup
> > that is more static, so we expect better result on the
> > other files. Also, these are the files on the RAID 5.
> > Once the work on the current file is complete, we will
> > move to those on the RAID 10, thus speedier?
> >
> > I plan to let this process run to completion, but we have
> > a number of db's of simular size that will need to be
> > shrunk as archiving is done.
> >
> > My question:
> > Is there a way to force priority to a shrink (single user
> > mode, etc) that will can be used while db is out of
> > production that would be quicker?
> >
> > Also, if I use the "Database Maintenance Plan
> > Wizzard", "Update Data Optimization Information"
> > page, "Remove unused space from database files" option to
> > shrink will it faster? Is the lower level process
> > essentially the same as a SHRINKDATABSE? Can it be run
> > while database is in use?
> >
> >
>|||I don't see where recovery mode is mentioned in the original post..
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roy in (H.E.Double Toothpicks)" <Roy@.DoubleToothpicks.com> wrote in message
news:O0Pn1vnUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> yah but this database is in recovery mode, no?
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23Mi22eiUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> > Hi Spencer,
> >
> > Firstly, you don't say how big the database is or what other load is
> running
> > on it. Shrink will wait for locks it cannot immediately acquire so there
> may
> > be something blocking it. This is especially likely with long-running,
> > pervasive update queries.
> >
> > Shrink will always run quicker without a concurrent workload, so
quiescing
> > the database (using single-user mode, for example) should decrease the
> > runtime.
> >
> > The wizard option you mention is the same underlying code as
> shrinkdatabase,
> > and so can be run online and can also be safely interrupted with no risk
> of
> > corruption or losing already completed work.
> >
> > Regards,
> >
> > Paul.
> >
> > --
> > Paul Randal
> > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > "Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote
> in
> > message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> > > While, we have been running the SHRINKDATABASE for 98
> > > hours now! Production does not seem to be affected.
> > > Assume this is because we have ample CPU, memory, and I/O?
> > >
> > > Should I expect this kind of run time on a database of
> > > this size?
> > >
> > > The process has shunk 6 files so far and we have gained
> > > about 2 GB per file. These are the files on the filegroup
> > > that is more static, so we expect better result on the
> > > other files. Also, these are the files on the RAID 5.
> > > Once the work on the current file is complete, we will
> > > move to those on the RAID 10, thus speedier?
> > >
> > > I plan to let this process run to completion, but we have
> > > a number of db's of simular size that will need to be
> > > shrunk as archiving is done.
> > >
> > > My question:
> > > Is there a way to force priority to a shrink (single user
> > > mode, etc) that will can be used while db is out of
> > > production that would be quicker?
> > >
> > > Also, if I use the "Database Maintenance Plan
> > > Wizzard", "Update Data Optimization Information"
> > > page, "Remove unused space from database files" option to
> > > shrink will it faster? Is the lower level process
> > > essentially the same as a SHRINKDATABSE? Can it be run
> > > while database is in use?
> > >
> > >
> >
> >
>|||true, i inferred that.
Still, I guess that it is in recovery mode, although the guy didn't notice
or say that.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:Os8udJsUDHA.1004@.TK2MSFTNGP12.phx.gbl...
> I don't see where recovery mode is mentioned in the original post..
> --
> Paul Randal
> DBCC Technical Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Roy in (H.E.Double Toothpicks)" <Roy@.DoubleToothpicks.com> wrote in
message
> news:O0Pn1vnUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> > yah but this database is in recovery mode, no?
> >
> > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > news:%23Mi22eiUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> > > Hi Spencer,
> > >
> > > Firstly, you don't say how big the database is or what other load is
> > running
> > > on it. Shrink will wait for locks it cannot immediately acquire so
there
> > may
> > > be something blocking it. This is especially likely with long-running,
> > > pervasive update queries.
> > >
> > > Shrink will always run quicker without a concurrent workload, so
> quiescing
> > > the database (using single-user mode, for example) should decrease the
> > > runtime.
> > >
> > > The wizard option you mention is the same underlying code as
> > shrinkdatabase,
> > > and so can be run online and can also be safely interrupted with no
risk
> > of
> > > corruption or losing already completed work.
> > >
> > > Regards,
> > >
> > > Paul.
> > >
> > > --
> > > Paul Randal
> > > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > "Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net>
wrote
> > in
> > > message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> > > > While, we have been running the SHRINKDATABASE for 98
> > > > hours now! Production does not seem to be affected.
> > > > Assume this is because we have ample CPU, memory, and I/O?
> > > >
> > > > Should I expect this kind of run time on a database of
> > > > this size?
> > > >
> > > > The process has shunk 6 files so far and we have gained
> > > > about 2 GB per file. These are the files on the filegroup
> > > > that is more static, so we expect better result on the
> > > > other files. Also, these are the files on the RAID 5.
> > > > Once the work on the current file is complete, we will
> > > > move to those on the RAID 10, thus speedier?
> > > >
> > > > I plan to let this process run to completion, but we have
> > > > a number of db's of simular size that will need to be
> > > > shrunk as archiving is done.
> > > >
> > > > My question:
> > > > Is there a way to force priority to a shrink (single user
> > > > mode, etc) that will can be used while db is out of
> > > > production that would be quicker?
> > > >
> > > > Also, if I use the "Database Maintenance Plan
> > > > Wizzard", "Update Data Optimization Information"
> > > > page, "Remove unused space from database files" option to
> > > > shrink will it faster? Is the lower level process
> > > > essentially the same as a SHRINKDATABSE? Can it be run
> > > > while database is in use?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Why would the db be in recovery mode?
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roy in (H.E.Double Toothpicks)" <Roy@.DoubleToothpicks.com> wrote in message
news:#29aFlvUDHA.2312@.TK2MSFTNGP12.phx.gbl...
> true, i inferred that.
> Still, I guess that it is in recovery mode, although the guy didn't
notice
> or say that.
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:Os8udJsUDHA.1004@.TK2MSFTNGP12.phx.gbl...
> > I don't see where recovery mode is mentioned in the original post..
> >
> > --
> > Paul Randal
> > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Roy in (H.E.Double Toothpicks)" <Roy@.DoubleToothpicks.com> wrote in
> message
> > news:O0Pn1vnUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> > > yah but this database is in recovery mode, no?
> > >
> > > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > > news:%23Mi22eiUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> > > > Hi Spencer,
> > > >
> > > > Firstly, you don't say how big the database is or what other load is
> > > running
> > > > on it. Shrink will wait for locks it cannot immediately acquire so
> there
> > > may
> > > > be something blocking it. This is especially likely with
long-running,
> > > > pervasive update queries.
> > > >
> > > > Shrink will always run quicker without a concurrent workload, so
> > quiescing
> > > > the database (using single-user mode, for example) should decrease
the
> > > > runtime.
> > > >
> > > > The wizard option you mention is the same underlying code as
> > > shrinkdatabase,
> > > > and so can be run online and can also be safely interrupted with no
> risk
> > > of
> > > > corruption or losing already completed work.
> > > >
> > > > Regards,
> > > >
> > > > Paul.
> > > >
> > > > --
> > > > Paul Randal
> > > > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > > "Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net>
> wrote
> > > in
> > > > message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> > > > > While, we have been running the SHRINKDATABASE for 98
> > > > > hours now! Production does not seem to be affected.
> > > > > Assume this is because we have ample CPU, memory, and I/O?
> > > > >
> > > > > Should I expect this kind of run time on a database of
> > > > > this size?
> > > > >
> > > > > The process has shunk 6 files so far and we have gained
> > > > > about 2 GB per file. These are the files on the filegroup
> > > > > that is more static, so we expect better result on the
> > > > > other files. Also, these are the files on the RAID 5.
> > > > > Once the work on the current file is complete, we will
> > > > > move to those on the RAID 10, thus speedier?
> > > > >
> > > > > I plan to let this process run to completion, but we have
> > > > > a number of db's of simular size that will need to be
> > > > > shrunk as archiving is done.
> > > > >
> > > > > My question:
> > > > > Is there a way to force priority to a shrink (single user
> > > > > mode, etc) that will can be used while db is out of
> > > > > production that would be quicker?
> > > > >
> > > > > Also, if I use the "Database Maintenance Plan
> > > > > Wizzard", "Update Data Optimization Information"
> > > > > page, "Remove unused space from database files" option to
> > > > > shrink will it faster? Is the lower level process
> > > > > essentially the same as a SHRINKDATABSE? Can it be run
> > > > > while database is in use?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
We have a large (330 GB) OLTP database. After archiving a
lot of records, we still have the same size of db and full
backups (BackupExec). The database has 2 filegroups, 24
data files, a log file over 4 logical drives. Log is
small, gets backup every hour and its file is no issue.
The data files range from 50 GB down to 70 MB since drives
and files were added as needed. Files auto grow, but do
not auto shrink. All maintenance is done manually via
Query Analyzer during scheduled times. A replicated
database with a subset of the information is used for OLTP
during these times.
Our goal was to shrink the database to recover OS space
and minimize backup time. This would be done with NO
LOAD. We scheduled 5 to 24 hours (based on some rough
estimates).
The SHRINKDATABASE has been running for 26 hours when load
was shifted back. It has now been running 31 hours. We
may need to cancel (using stop on Query Analyzer. However,
we hesitate to do so and risk corruption.
I found one reference in a PPT about shrink saying "may be
stopped without losing work". However, this is not a
mainstream URL:
http://www.microsoft.com/uk/partner/strategy/server_busines
s_agility/customers/business-
intelligence/downloads/RDBMSFeaturesandDataWarehousing.ppt
I would like an official, "Yes, if you started the DBCC
SHRINKDATABASE in the Query Analyzer, you can stop it by
using the Cancel Query. There will be now rollback time
or risk of corruption".
The documentation for these long running commands should
have a section on Cancellation.
Even an unofficial response is welcome.
Thanks,
SMI had what sounds like the same problem on SQL 2000. I calculated that the
database would take a week to come up. Not acceptable.
I had to use this solution 3 times: It worked just fine each time. I did
not get fired
It comes down to running this command;
UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='DBNAME'
then restart the server.
It set the database in emergency mode, which bypasses all recovery stuff
http://support.microsoft.com/default.aspx?scid=kb;EN-US;165918
"Spencer Morley" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote in
message news:023001c34f1d$32c14a40$a101280a@.phx.gbl...
> BACKGROUND:
> We have a large (330 GB) OLTP database. After archiving a
> lot of records, we still have the same size of db and full
> backups (BackupExec). The database has 2 filegroups, 24
> data files, a log file over 4 logical drives. Log is
> small, gets backup every hour and its file is no issue.
> The data files range from 50 GB down to 70 MB since drives
> and files were added as needed. Files auto grow, but do
> not auto shrink. All maintenance is done manually via
> Query Analyzer during scheduled times. A replicated
> database with a subset of the information is used for OLTP
> during these times.
> Our goal was to shrink the database to recover OS space
> and minimize backup time. This would be done with NO
> LOAD. We scheduled 5 to 24 hours (based on some rough
> estimates).
> The SHRINKDATABASE has been running for 26 hours when load
> was shifted back. It has now been running 31 hours. We
> may need to cancel (using stop on Query Analyzer. However,
> we hesitate to do so and risk corruption.
> I found one reference in a PPT about shrink saying "may be
> stopped without losing work". However, this is not a
> mainstream URL:
> http://www.microsoft.com/uk/partner/strategy/server_busines
> s_agility/customers/business-
> intelligence/downloads/RDBMSFeaturesandDataWarehousing.ppt
> I would like an official, "Yes, if you started the DBCC
> SHRINKDATABASE in the Query Analyzer, you can stop it by
> using the Cancel Query. There will be now rollback time
> or risk of corruption".
> The documentation for these long running commands should
> have a section on Cancellation.
> Even an unofficial response is welcome.
> Thanks,
> SM
>|||You absolutely can stop shrink at any time with no rollback cost and no risk
of corruption.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spencer Morley" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote in
message news:023001c34f1d$32c14a40$a101280a@.phx.gbl...
> BACKGROUND:
> We have a large (330 GB) OLTP database. After archiving a
> lot of records, we still have the same size of db and full
> backups (BackupExec). The database has 2 filegroups, 24
> data files, a log file over 4 logical drives. Log is
> small, gets backup every hour and its file is no issue.
> The data files range from 50 GB down to 70 MB since drives
> and files were added as needed. Files auto grow, but do
> not auto shrink. All maintenance is done manually via
> Query Analyzer during scheduled times. A replicated
> database with a subset of the information is used for OLTP
> during these times.
> Our goal was to shrink the database to recover OS space
> and minimize backup time. This would be done with NO
> LOAD. We scheduled 5 to 24 hours (based on some rough
> estimates).
> The SHRINKDATABASE has been running for 26 hours when load
> was shifted back. It has now been running 31 hours. We
> may need to cancel (using stop on Query Analyzer. However,
> we hesitate to do so and risk corruption.
> I found one reference in a PPT about shrink saying "may be
> stopped without losing work". However, this is not a
> mainstream URL:
> http://www.microsoft.com/uk/partner/strategy/server_busines
> s_agility/customers/business-
> intelligence/downloads/RDBMSFeaturesandDataWarehousing.ppt
> I would like an official, "Yes, if you started the DBCC
> SHRINKDATABASE in the Query Analyzer, you can stop it by
> using the Cancel Query. There will be now rollback time
> or risk of corruption".
> The documentation for these long running commands should
> have a section on Cancellation.
> Even an unofficial response is welcome.
> Thanks,
> SM
>|||Hi Spencer,
Firstly, you don't say how big the database is or what other load is running
on it. Shrink will wait for locks it cannot immediately acquire so there may
be something blocking it. This is especially likely with long-running,
pervasive update queries.
Shrink will always run quicker without a concurrent workload, so quiescing
the database (using single-user mode, for example) should decrease the
runtime.
The wizard option you mention is the same underlying code as shrinkdatabase,
and so can be run online and can also be safely interrupted with no risk of
corruption or losing already completed work.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote in
message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> While, we have been running the SHRINKDATABASE for 98
> hours now! Production does not seem to be affected.
> Assume this is because we have ample CPU, memory, and I/O?
> Should I expect this kind of run time on a database of
> this size?
> The process has shunk 6 files so far and we have gained
> about 2 GB per file. These are the files on the filegroup
> that is more static, so we expect better result on the
> other files. Also, these are the files on the RAID 5.
> Once the work on the current file is complete, we will
> move to those on the RAID 10, thus speedier?
> I plan to let this process run to completion, but we have
> a number of db's of simular size that will need to be
> shrunk as archiving is done.
> My question:
> Is there a way to force priority to a shrink (single user
> mode, etc) that will can be used while db is out of
> production that would be quicker?
> Also, if I use the "Database Maintenance Plan
> Wizzard", "Update Data Optimization Information"
> page, "Remove unused space from database files" option to
> shrink will it faster? Is the lower level process
> essentially the same as a SHRINKDATABSE? Can it be run
> while database is in use?
>|||yah but this database is in recovery mode, no?
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23Mi22eiUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> Hi Spencer,
> Firstly, you don't say how big the database is or what other load is
running
> on it. Shrink will wait for locks it cannot immediately acquire so there
may
> be something blocking it. This is especially likely with long-running,
> pervasive update queries.
> Shrink will always run quicker without a concurrent workload, so quiescing
> the database (using single-user mode, for example) should decrease the
> runtime.
> The wizard option you mention is the same underlying code as
shrinkdatabase,
> and so can be run online and can also be safely interrupted with no risk
of
> corruption or losing already completed work.
> Regards,
> Paul.
> --
> Paul Randal
> DBCC Technical Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote
in
> message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> > While, we have been running the SHRINKDATABASE for 98
> > hours now! Production does not seem to be affected.
> > Assume this is because we have ample CPU, memory, and I/O?
> >
> > Should I expect this kind of run time on a database of
> > this size?
> >
> > The process has shunk 6 files so far and we have gained
> > about 2 GB per file. These are the files on the filegroup
> > that is more static, so we expect better result on the
> > other files. Also, these are the files on the RAID 5.
> > Once the work on the current file is complete, we will
> > move to those on the RAID 10, thus speedier?
> >
> > I plan to let this process run to completion, but we have
> > a number of db's of simular size that will need to be
> > shrunk as archiving is done.
> >
> > My question:
> > Is there a way to force priority to a shrink (single user
> > mode, etc) that will can be used while db is out of
> > production that would be quicker?
> >
> > Also, if I use the "Database Maintenance Plan
> > Wizzard", "Update Data Optimization Information"
> > page, "Remove unused space from database files" option to
> > shrink will it faster? Is the lower level process
> > essentially the same as a SHRINKDATABSE? Can it be run
> > while database is in use?
> >
> >
>|||I don't see where recovery mode is mentioned in the original post..
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roy in (H.E.Double Toothpicks)" <Roy@.DoubleToothpicks.com> wrote in message
news:O0Pn1vnUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> yah but this database is in recovery mode, no?
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23Mi22eiUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> > Hi Spencer,
> >
> > Firstly, you don't say how big the database is or what other load is
> running
> > on it. Shrink will wait for locks it cannot immediately acquire so there
> may
> > be something blocking it. This is especially likely with long-running,
> > pervasive update queries.
> >
> > Shrink will always run quicker without a concurrent workload, so
quiescing
> > the database (using single-user mode, for example) should decrease the
> > runtime.
> >
> > The wizard option you mention is the same underlying code as
> shrinkdatabase,
> > and so can be run online and can also be safely interrupted with no risk
> of
> > corruption or losing already completed work.
> >
> > Regards,
> >
> > Paul.
> >
> > --
> > Paul Randal
> > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > "Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote
> in
> > message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> > > While, we have been running the SHRINKDATABASE for 98
> > > hours now! Production does not seem to be affected.
> > > Assume this is because we have ample CPU, memory, and I/O?
> > >
> > > Should I expect this kind of run time on a database of
> > > this size?
> > >
> > > The process has shunk 6 files so far and we have gained
> > > about 2 GB per file. These are the files on the filegroup
> > > that is more static, so we expect better result on the
> > > other files. Also, these are the files on the RAID 5.
> > > Once the work on the current file is complete, we will
> > > move to those on the RAID 10, thus speedier?
> > >
> > > I plan to let this process run to completion, but we have
> > > a number of db's of simular size that will need to be
> > > shrunk as archiving is done.
> > >
> > > My question:
> > > Is there a way to force priority to a shrink (single user
> > > mode, etc) that will can be used while db is out of
> > > production that would be quicker?
> > >
> > > Also, if I use the "Database Maintenance Plan
> > > Wizzard", "Update Data Optimization Information"
> > > page, "Remove unused space from database files" option to
> > > shrink will it faster? Is the lower level process
> > > essentially the same as a SHRINKDATABSE? Can it be run
> > > while database is in use?
> > >
> > >
> >
> >
>|||true, i inferred that.
Still, I guess that it is in recovery mode, although the guy didn't notice
or say that.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:Os8udJsUDHA.1004@.TK2MSFTNGP12.phx.gbl...
> I don't see where recovery mode is mentioned in the original post..
> --
> Paul Randal
> DBCC Technical Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Roy in (H.E.Double Toothpicks)" <Roy@.DoubleToothpicks.com> wrote in
message
> news:O0Pn1vnUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> > yah but this database is in recovery mode, no?
> >
> > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > news:%23Mi22eiUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> > > Hi Spencer,
> > >
> > > Firstly, you don't say how big the database is or what other load is
> > running
> > > on it. Shrink will wait for locks it cannot immediately acquire so
there
> > may
> > > be something blocking it. This is especially likely with long-running,
> > > pervasive update queries.
> > >
> > > Shrink will always run quicker without a concurrent workload, so
> quiescing
> > > the database (using single-user mode, for example) should decrease the
> > > runtime.
> > >
> > > The wizard option you mention is the same underlying code as
> > shrinkdatabase,
> > > and so can be run online and can also be safely interrupted with no
risk
> > of
> > > corruption or losing already completed work.
> > >
> > > Regards,
> > >
> > > Paul.
> > >
> > > --
> > > Paul Randal
> > > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > "Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net>
wrote
> > in
> > > message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> > > > While, we have been running the SHRINKDATABASE for 98
> > > > hours now! Production does not seem to be affected.
> > > > Assume this is because we have ample CPU, memory, and I/O?
> > > >
> > > > Should I expect this kind of run time on a database of
> > > > this size?
> > > >
> > > > The process has shunk 6 files so far and we have gained
> > > > about 2 GB per file. These are the files on the filegroup
> > > > that is more static, so we expect better result on the
> > > > other files. Also, these are the files on the RAID 5.
> > > > Once the work on the current file is complete, we will
> > > > move to those on the RAID 10, thus speedier?
> > > >
> > > > I plan to let this process run to completion, but we have
> > > > a number of db's of simular size that will need to be
> > > > shrunk as archiving is done.
> > > >
> > > > My question:
> > > > Is there a way to force priority to a shrink (single user
> > > > mode, etc) that will can be used while db is out of
> > > > production that would be quicker?
> > > >
> > > > Also, if I use the "Database Maintenance Plan
> > > > Wizzard", "Update Data Optimization Information"
> > > > page, "Remove unused space from database files" option to
> > > > shrink will it faster? Is the lower level process
> > > > essentially the same as a SHRINKDATABSE? Can it be run
> > > > while database is in use?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Why would the db be in recovery mode?
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roy in (H.E.Double Toothpicks)" <Roy@.DoubleToothpicks.com> wrote in message
news:#29aFlvUDHA.2312@.TK2MSFTNGP12.phx.gbl...
> true, i inferred that.
> Still, I guess that it is in recovery mode, although the guy didn't
notice
> or say that.
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:Os8udJsUDHA.1004@.TK2MSFTNGP12.phx.gbl...
> > I don't see where recovery mode is mentioned in the original post..
> >
> > --
> > Paul Randal
> > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Roy in (H.E.Double Toothpicks)" <Roy@.DoubleToothpicks.com> wrote in
> message
> > news:O0Pn1vnUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> > > yah but this database is in recovery mode, no?
> > >
> > > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > > news:%23Mi22eiUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> > > > Hi Spencer,
> > > >
> > > > Firstly, you don't say how big the database is or what other load is
> > > running
> > > > on it. Shrink will wait for locks it cannot immediately acquire so
> there
> > > may
> > > > be something blocking it. This is especially likely with
long-running,
> > > > pervasive update queries.
> > > >
> > > > Shrink will always run quicker without a concurrent workload, so
> > quiescing
> > > > the database (using single-user mode, for example) should decrease
the
> > > > runtime.
> > > >
> > > > The wizard option you mention is the same underlying code as
> > > shrinkdatabase,
> > > > and so can be run online and can also be safely interrupted with no
> risk
> > > of
> > > > corruption or losing already completed work.
> > > >
> > > > Regards,
> > > >
> > > > Paul.
> > > >
> > > > --
> > > > Paul Randal
> > > > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > > "Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net>
> wrote
> > > in
> > > > message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> > > > > While, we have been running the SHRINKDATABASE for 98
> > > > > hours now! Production does not seem to be affected.
> > > > > Assume this is because we have ample CPU, memory, and I/O?
> > > > >
> > > > > Should I expect this kind of run time on a database of
> > > > > this size?
> > > > >
> > > > > The process has shunk 6 files so far and we have gained
> > > > > about 2 GB per file. These are the files on the filegroup
> > > > > that is more static, so we expect better result on the
> > > > > other files. Also, these are the files on the RAID 5.
> > > > > Once the work on the current file is complete, we will
> > > > > move to those on the RAID 10, thus speedier?
> > > > >
> > > > > I plan to let this process run to completion, but we have
> > > > > a number of db's of simular size that will need to be
> > > > > shrunk as archiving is done.
> > > > >
> > > > > My question:
> > > > > Is there a way to force priority to a shrink (single user
> > > > > mode, etc) that will can be used while db is out of
> > > > > production that would be quicker?
> > > > >
> > > > > Also, if I use the "Database Maintenance Plan
> > > > > Wizzard", "Update Data Optimization Information"
> > > > > page, "Remove unused space from database files" option to
> > > > > shrink will it faster? Is the lower level process
> > > > > essentially the same as a SHRINKDATABSE? Can it be run
> > > > > while database is in use?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
Subscribe to:
Posts (Atom)