hi,
I have a T-sql as part of ETL. The code selects all data
from table x (with 3 million rows) and populates table y
(which already has 1.5 million rows). There are no where
conditions no calculations - simple insert into table x
(col1,col2,...coln) select col1,col2...coln from table y.
When I run sp_lock I see a lot locks of type EXT and mode
X. Can I used hint TABLOCKX.
I noticed that if I used hint TABLOCKX for the table
inserted into then number of locks reduces drastically. If
I use hint TABLOCKX in the table selected from there is no
impact.
Anybody encountered similar issues? Any input will be
useful..
Thx,
DeepaThat means you are getting extent locks which are OK for this type operation
but if you don't have other users accessing it would be best to use a table
level lock but don't need to use TABLOCKX. Instead try TABLOCK but if you
have users in the table you are selecting from they may prevent this from
happeing.
--
Andrew J. Kelly SQL MVP
"Deepa" <anonymous@.discussions.microsoft.com> wrote in message
news:01cc01c3b51f$f2276eb0$a301280a@.phx.gbl...
> hi,
> I have a T-sql as part of ETL. The code selects all data
> from table x (with 3 million rows) and populates table y
> (which already has 1.5 million rows). There are no where
> conditions no calculations - simple insert into table x
> (col1,col2,...coln) select col1,col2...coln from table y.
> When I run sp_lock I see a lot locks of type EXT and mode
> X. Can I used hint TABLOCKX.
> I noticed that if I used hint TABLOCKX for the table
> inserted into then number of locks reduces drastically. If
> I use hint TABLOCKX in the table selected from there is no
> impact.
> Anybody encountered similar issues? Any input will be
> useful..
> Thx,
> Deepa
>|||Thank you Andrew. I am new to sqlserver and your help is
appreciated.
Now, this table has six indexes and dropped them before
the insert and recreate them after.
What I notice is for a brief moment the number of locks
jumps to 30,000+ before reducing to around 200 incase of
tablock and 4000 when used without tablock. Why is there a
spike initially.
Also I read that each lock resource uses 96k. My box has
2GB memory which means I can have upto (2*1024*1024*1024)/
(96*1024) which comes to about 21800 then how can the lock
resource grow to 30,000?
i encounter resource lock issues only at the time that the
o.s backup is happening. My systems people are not able to
advice me. Would you know if an o.s. backup would be heavy
on memory. I'm not a windows person either...
Many thanks,
Deepa|||Its 96 bytes Deepa and not 96KB :-)
"Deepa" <anonymous@.discussions.microsoft.com> wrote in message
news:002b01c3b5dd$f18aef40$a501280a@.phx.gbl...
> Thank you Andrew. I am new to sqlserver and your help is
> appreciated.
> Now, this table has six indexes and dropped them before
> the insert and recreate them after.
> What I notice is for a brief moment the number of locks
> jumps to 30,000+ before reducing to around 200 incase of
> tablock and 4000 when used without tablock. Why is there a
> spike initially.
> Also I read that each lock resource uses 96k. My box has
> 2GB memory which means I can have upto (2*1024*1024*1024)/
> (96*1024) which comes to about 21800 then how can the lock
> resource grow to 30,000?
> i encounter resource lock issues only at the time that the
> o.s backup is happening. My systems people are not able to
> advice me. Would you know if an o.s. backup would be heavy
> on memory. I'm not a windows person either...
> Many thanks,
> Deepa
>
>|||Yes as Hassan pointsout it is 96 bytes and not KB so you ae not as short on
memory as you think. The reason you see it spike initialy is that sql sever
starts out with row or page locks and will escalate to a table lock if it
can after a while. You say it is an OS backup, are you sure they aren't
using a sql plug-in to do sql backups as well? If you are doing a sql
backup it will take locks when it reads the data. If it is strictly an OS
backup they should eliminate the sql erver files from the backup as they are
useless from a sql server point of view and only can cause issues when
accessing the sql files.
--
Andrew J. Kelly SQL MVP
"Deepa" <anonymous@.discussions.microsoft.com> wrote in message
news:002b01c3b5dd$f18aef40$a501280a@.phx.gbl...
> Thank you Andrew. I am new to sqlserver and your help is
> appreciated.
> Now, this table has six indexes and dropped them before
> the insert and recreate them after.
> What I notice is for a brief moment the number of locks
> jumps to 30,000+ before reducing to around 200 incase of
> tablock and 4000 when used without tablock. Why is there a
> spike initially.
> Also I read that each lock resource uses 96k. My box has
> 2GB memory which means I can have upto (2*1024*1024*1024)/
> (96*1024) which comes to about 21800 then how can the lock
> resource grow to 30,000?
> i encounter resource lock issues only at the time that the
> o.s backup is happening. My systems people are not able to
> advice me. Would you know if an o.s. backup would be heavy
> on memory. I'm not a windows person either...
> Many thanks,
> Deepa
>
>|||Thank you both. Yes it is 96 bytes and not 96kb.. my bad!
I have been doing tests with drop index / populate /
recreate index and testing is in progress but this is
likely to resolve the error.
Its a standard o.s backup. The files being backed up are
the backup files created by my maintenance plans - no the
datafiles.
many thanks for the response.
>--Original Message--
>Yes as Hassan pointsout it is 96 bytes and not KB so you
ae not as short on
>memory as you think. The reason you see it spike
initialy is that sql sever
>starts out with row or page locks and will escalate to a
table lock if it
>can after a while. You say it is an OS backup, are you
sure they aren't
>using a sql plug-in to do sql backups as well? If you
are doing a sql
>backup it will take locks when it reads the data. If it
is strictly an OS
>backup they should eliminate the sql erver files from the
backup as they are
>useless from a sql server point of view and only can
cause issues when
>accessing the sql files.
>--
>Andrew J. Kelly SQL MVP
>
>"Deepa" <anonymous@.discussions.microsoft.com> wrote in
message
>news:002b01c3b5dd$f18aef40$a501280a@.phx.gbl...
>> Thank you Andrew. I am new to sqlserver and your help is
>> appreciated.
>> Now, this table has six indexes and dropped them before
>> the insert and recreate them after.
>> What I notice is for a brief moment the number of locks
>> jumps to 30,000+ before reducing to around 200 incase of
>> tablock and 4000 when used without tablock. Why is
there a
>> spike initially.
>> Also I read that each lock resource uses 96k. My box has
>> 2GB memory which means I can have upto
(2*1024*1024*1024)/
>> (96*1024) which comes to about 21800 then how can the
lock
>> resource grow to 30,000?
>> i encounter resource lock issues only at the time that
the
>> o.s backup is happening. My systems people are not able
to
>> advice me. Would you know if an o.s. backup would be
heavy
>> on memory. I'm not a windows person either...
>> Many thanks,
>> Deepa
>>
>
>.
>
Friday, March 30, 2012
Resource Limits ?
In Oracle & Sybase you can set login resource limits to restrict, time of day, number of i-o's, cpu use etc for any login, at the login method. All actions done from a resource limit constrained login inherit the constraints.
Is there any way to implment this with any MS SQL Server version from 200-2005/SP2?
Before anyone answers, please do not suggest set rowcount etc.
thanks.I don't see this kind of feature available yet in any of the versions of SQL Server, however workaround are the only possible methods like the one you have suggested.sql
Is there any way to implment this with any MS SQL Server version from 200-2005/SP2?
Before anyone answers, please do not suggest set rowcount etc.
thanks.I don't see this kind of feature available yet in any of the versions of SQL Server, however workaround are the only possible methods like the one you have suggested.sql
resource limit was reached
Error:
The OLE DB provider "SQLNCLI" for linked server reported an error. Execution
terminated by the provider because a resource limit was reached.
Situation:
Large query is pulled from one server to another (data warehouse).
Suspect:
DTC on the Windows server 2003 where the source server is located (SQL 2k)
does not have DTC enabled:
Looking for:
A work-around as we are in a situation where the server cannot yet be
rebooted.
Question:
Is there a way to configure named pipes to effectively handle the resource
limitation on linked servers?
--
Regards,
JamieAs a workaround, can you try using TCP\IP provider for linking?
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A4DB5E7F-56F3-46AE-9858-0F67546B82ED@.microsoft.com...
> Error:
> The OLE DB provider "SQLNCLI" for linked server reported an error.
> Execution
> terminated by the provider because a resource limit was reached.
> Situation:
> Large query is pulled from one server to another (data warehouse).
> Suspect:
> DTC on the Windows server 2003 where the source server is located (SQL 2k)
> does not have DTC enabled:
> Looking for:
> A work-around as we are in a situation where the server cannot yet be
> rebooted.
> Question:
> Is there a way to configure named pipes to effectively handle the resource
> limitation on linked servers?
> --
> Regards,
> Jamie|||I think I may be misunderstanding. I'm unaware of TCP/IP configurations
other than what I already use. Which it may be that you misunderstood my
question, thinking I am using named pipes (not so - have named pipes turned
off).
Thought is - Will named pipes offer a temporary solution to overcome the
resource limitation imposed by the WinSvr2003 R2 without the DTC turned on?
Dunno enough to guess whether there is something in it that will work.
BACKGROUND:
I currently use TCP/IP and aliasing and I suspect the linked servers use
that already to communicate.
When we access a view on a SQL 2000 server using SQL 2005 on another server
(both are WinSvr 2003 R2 64 bit).
There is a problem with the DTC (I did not mention this one - I am waiting
for a server reboot to fix it - http://support.microsoft.com/kb/329332 or
http://support.microsoft.com/?kbid=873160 The operation could not be
performed because the OLE DB provider 'SQLOLEDB' was unable to begin a
distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]. [OLE/DB provider
returned message: New transaction cannot enlist in the specified transaction
coordinator. ]).
The one housing SQL 2000 does not yet have DTC setup for network access.
The situation does not allow us to reboot (short of an emergency of which
this is not - instead each view must be re-written to get around the resource
issue - usually just create a sub-query in place of the view).
Herein lies the problem: After rewriting a bunch of failing views we
discovered that if a view is altered and consumes more resources, the
borderline ones we use for data-warehousing archive information at night can
fail for no other reason than that - view times out - resource limit
reached... blah blah). I hate it when a job fails.
Regards,
Jamie
"TheSQLGuru" wrote:
> As a workaround, can you try using TCP\IP provider for linking?
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:A4DB5E7F-56F3-46AE-9858-0F67546B82ED@.microsoft.com...
> > Error:
> > The OLE DB provider "SQLNCLI" for linked server reported an error.
> > Execution
> > terminated by the provider because a resource limit was reached.
> > Situation:
> > Large query is pulled from one server to another (data warehouse).
> > Suspect:
> > DTC on the Windows server 2003 where the source server is located (SQL 2k)
> > does not have DTC enabled:
> > Looking for:
> > A work-around as we are in a situation where the server cannot yet be
> > rebooted.
> >
> > Question:
> > Is there a way to configure named pipes to effectively handle the resource
> > limitation on linked servers?
> > --
> > Regards,
> > Jamie
>
>|||Misread first post. My bad. I don't know that there is a way to cut DTC
out of the loop, regardless of what connection mechanism you are using. I
don't have any other suggestions for possible workarounds for your issue
either. :(
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:AF702DC6-3A07-4D85-9713-8C5406099EED@.microsoft.com...
>I think I may be misunderstanding. I'm unaware of TCP/IP configurations
> other than what I already use. Which it may be that you misunderstood my
> question, thinking I am using named pipes (not so - have named pipes
> turned
> off).
> Thought is - Will named pipes offer a temporary solution to overcome the
> resource limitation imposed by the WinSvr2003 R2 without the DTC turned
> on?
> Dunno enough to guess whether there is something in it that will work.
> BACKGROUND:
> I currently use TCP/IP and aliasing and I suspect the linked servers use
> that already to communicate.
> When we access a view on a SQL 2000 server using SQL 2005 on another
> server
> (both are WinSvr 2003 R2 64 bit).
> There is a problem with the DTC (I did not mention this one - I am waiting
> for a server reboot to fix it - http://support.microsoft.com/kb/329332 or
> http://support.microsoft.com/?kbid=873160 The operation could not be
> performed because the OLE DB provider 'SQLOLEDB' was unable to begin a
> distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]. [OLE/DB provider
> returned message: New transaction cannot enlist in the specified
> transaction
> coordinator. ]).
> The one housing SQL 2000 does not yet have DTC setup for network access.
> The situation does not allow us to reboot (short of an emergency of which
> this is not - instead each view must be re-written to get around the
> resource
> issue - usually just create a sub-query in place of the view).
> Herein lies the problem: After rewriting a bunch of failing views we
> discovered that if a view is altered and consumes more resources, the
> borderline ones we use for data-warehousing archive information at night
> can
> fail for no other reason than that - view times out - resource limit
> reached... blah blah). I hate it when a job fails.
>
> --
> Regards,
> Jamie
>
> "TheSQLGuru" wrote:
>> As a workaround, can you try using TCP\IP provider for linking?
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:A4DB5E7F-56F3-46AE-9858-0F67546B82ED@.microsoft.com...
>> > Error:
>> > The OLE DB provider "SQLNCLI" for linked server reported an error.
>> > Execution
>> > terminated by the provider because a resource limit was reached.
>> > Situation:
>> > Large query is pulled from one server to another (data warehouse).
>> > Suspect:
>> > DTC on the Windows server 2003 where the source server is located (SQL
>> > 2k)
>> > does not have DTC enabled:
>> > Looking for:
>> > A work-around as we are in a situation where the server cannot yet be
>> > rebooted.
>> >
>> > Question:
>> > Is there a way to configure named pipes to effectively handle the
>> > resource
>> > limitation on linked servers?
>> > --
>> > Regards,
>> > Jamie
>>
The OLE DB provider "SQLNCLI" for linked server reported an error. Execution
terminated by the provider because a resource limit was reached.
Situation:
Large query is pulled from one server to another (data warehouse).
Suspect:
DTC on the Windows server 2003 where the source server is located (SQL 2k)
does not have DTC enabled:
Looking for:
A work-around as we are in a situation where the server cannot yet be
rebooted.
Question:
Is there a way to configure named pipes to effectively handle the resource
limitation on linked servers?
--
Regards,
JamieAs a workaround, can you try using TCP\IP provider for linking?
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A4DB5E7F-56F3-46AE-9858-0F67546B82ED@.microsoft.com...
> Error:
> The OLE DB provider "SQLNCLI" for linked server reported an error.
> Execution
> terminated by the provider because a resource limit was reached.
> Situation:
> Large query is pulled from one server to another (data warehouse).
> Suspect:
> DTC on the Windows server 2003 where the source server is located (SQL 2k)
> does not have DTC enabled:
> Looking for:
> A work-around as we are in a situation where the server cannot yet be
> rebooted.
> Question:
> Is there a way to configure named pipes to effectively handle the resource
> limitation on linked servers?
> --
> Regards,
> Jamie|||I think I may be misunderstanding. I'm unaware of TCP/IP configurations
other than what I already use. Which it may be that you misunderstood my
question, thinking I am using named pipes (not so - have named pipes turned
off).
Thought is - Will named pipes offer a temporary solution to overcome the
resource limitation imposed by the WinSvr2003 R2 without the DTC turned on?
Dunno enough to guess whether there is something in it that will work.
BACKGROUND:
I currently use TCP/IP and aliasing and I suspect the linked servers use
that already to communicate.
When we access a view on a SQL 2000 server using SQL 2005 on another server
(both are WinSvr 2003 R2 64 bit).
There is a problem with the DTC (I did not mention this one - I am waiting
for a server reboot to fix it - http://support.microsoft.com/kb/329332 or
http://support.microsoft.com/?kbid=873160 The operation could not be
performed because the OLE DB provider 'SQLOLEDB' was unable to begin a
distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]. [OLE/DB provider
returned message: New transaction cannot enlist in the specified transaction
coordinator. ]).
The one housing SQL 2000 does not yet have DTC setup for network access.
The situation does not allow us to reboot (short of an emergency of which
this is not - instead each view must be re-written to get around the resource
issue - usually just create a sub-query in place of the view).
Herein lies the problem: After rewriting a bunch of failing views we
discovered that if a view is altered and consumes more resources, the
borderline ones we use for data-warehousing archive information at night can
fail for no other reason than that - view times out - resource limit
reached... blah blah). I hate it when a job fails.
Regards,
Jamie
"TheSQLGuru" wrote:
> As a workaround, can you try using TCP\IP provider for linking?
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:A4DB5E7F-56F3-46AE-9858-0F67546B82ED@.microsoft.com...
> > Error:
> > The OLE DB provider "SQLNCLI" for linked server reported an error.
> > Execution
> > terminated by the provider because a resource limit was reached.
> > Situation:
> > Large query is pulled from one server to another (data warehouse).
> > Suspect:
> > DTC on the Windows server 2003 where the source server is located (SQL 2k)
> > does not have DTC enabled:
> > Looking for:
> > A work-around as we are in a situation where the server cannot yet be
> > rebooted.
> >
> > Question:
> > Is there a way to configure named pipes to effectively handle the resource
> > limitation on linked servers?
> > --
> > Regards,
> > Jamie
>
>|||Misread first post. My bad. I don't know that there is a way to cut DTC
out of the loop, regardless of what connection mechanism you are using. I
don't have any other suggestions for possible workarounds for your issue
either. :(
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:AF702DC6-3A07-4D85-9713-8C5406099EED@.microsoft.com...
>I think I may be misunderstanding. I'm unaware of TCP/IP configurations
> other than what I already use. Which it may be that you misunderstood my
> question, thinking I am using named pipes (not so - have named pipes
> turned
> off).
> Thought is - Will named pipes offer a temporary solution to overcome the
> resource limitation imposed by the WinSvr2003 R2 without the DTC turned
> on?
> Dunno enough to guess whether there is something in it that will work.
> BACKGROUND:
> I currently use TCP/IP and aliasing and I suspect the linked servers use
> that already to communicate.
> When we access a view on a SQL 2000 server using SQL 2005 on another
> server
> (both are WinSvr 2003 R2 64 bit).
> There is a problem with the DTC (I did not mention this one - I am waiting
> for a server reboot to fix it - http://support.microsoft.com/kb/329332 or
> http://support.microsoft.com/?kbid=873160 The operation could not be
> performed because the OLE DB provider 'SQLOLEDB' was unable to begin a
> distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]. [OLE/DB provider
> returned message: New transaction cannot enlist in the specified
> transaction
> coordinator. ]).
> The one housing SQL 2000 does not yet have DTC setup for network access.
> The situation does not allow us to reboot (short of an emergency of which
> this is not - instead each view must be re-written to get around the
> resource
> issue - usually just create a sub-query in place of the view).
> Herein lies the problem: After rewriting a bunch of failing views we
> discovered that if a view is altered and consumes more resources, the
> borderline ones we use for data-warehousing archive information at night
> can
> fail for no other reason than that - view times out - resource limit
> reached... blah blah). I hate it when a job fails.
>
> --
> Regards,
> Jamie
>
> "TheSQLGuru" wrote:
>> As a workaround, can you try using TCP\IP provider for linking?
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:A4DB5E7F-56F3-46AE-9858-0F67546B82ED@.microsoft.com...
>> > Error:
>> > The OLE DB provider "SQLNCLI" for linked server reported an error.
>> > Execution
>> > terminated by the provider because a resource limit was reached.
>> > Situation:
>> > Large query is pulled from one server to another (data warehouse).
>> > Suspect:
>> > DTC on the Windows server 2003 where the source server is located (SQL
>> > 2k)
>> > does not have DTC enabled:
>> > Looking for:
>> > A work-around as we are in a situation where the server cannot yet be
>> > rebooted.
>> >
>> > Question:
>> > Is there a way to configure named pipes to effectively handle the
>> > resource
>> > limitation on linked servers?
>> > --
>> > Regards,
>> > Jamie
>>
resource limit was reached
Error:
The OLE DB provider "SQLNCLI" for linked server reported an error. Execution
terminated by the provider because a resource limit was reached.
Situation:
Large query is pulled from one server to another (data warehouse).
Suspect:
DTC on the Windows server 2003 where the source server is located (SQL 2k)
does not have DTC enabled:
Looking for:
A work-around as we are in a situation where the server cannot yet be
rebooted.
Question:
Is there a way to configure named pipes to effectively handle the resource
limitation on linked servers?
Regards,
Jamie
As a workaround, can you try using TCP\IP provider for linking?
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A4DB5E7F-56F3-46AE-9858-0F67546B82ED@.microsoft.com...
> Error:
> The OLE DB provider "SQLNCLI" for linked server reported an error.
> Execution
> terminated by the provider because a resource limit was reached.
> Situation:
> Large query is pulled from one server to another (data warehouse).
> Suspect:
> DTC on the Windows server 2003 where the source server is located (SQL 2k)
> does not have DTC enabled:
> Looking for:
> A work-around as we are in a situation where the server cannot yet be
> rebooted.
> Question:
> Is there a way to configure named pipes to effectively handle the resource
> limitation on linked servers?
> --
> Regards,
> Jamie
|||I think I may be misunderstanding. I'm unaware of TCP/IP configurations
other than what I already use. Which it may be that you misunderstood my
question, thinking I am using named pipes (not so - have named pipes turned
off).
Thought is - Will named pipes offer a temporary solution to overcome the
resource limitation imposed by the WinSvr2003 R2 without the DTC turned on?
Dunno enough to guess whether there is something in it that will work.
BACKGROUND:
I currently use TCP/IP and aliasing and I suspect the linked servers use
that already to communicate.
When we access a view on a SQL 2000 server using SQL 2005 on another server
(both are WinSvr 2003 R2 64 bit).
There is a problem with the DTC (I did not mention this one - I am waiting
for a server reboot to fix it - http://support.microsoft.com/kb/329332 or
http://support.microsoft.com/?kbid=873160 The operation could not be
performed because the OLE DB provider 'SQLOLEDB' was unable to begin a
distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]. [OLE/DB provider
returned message: New transaction cannot enlist in the specified transaction
coordinator. ]).
The one housing SQL 2000 does not yet have DTC setup for network access.
The situation does not allow us to reboot (short of an emergency of which
this is not - instead each view must be re-written to get around the resource
issue - usually just create a sub-query in place of the view).
Herein lies the problem: After rewriting a bunch of failing views we
discovered that if a view is altered and consumes more resources, the
borderline ones we use for data-warehousing archive information at night can
fail for no other reason than that - view times out - resource limit
reached... blah blah). I hate it when a job fails.
Regards,
Jamie
"TheSQLGuru" wrote:
> As a workaround, can you try using TCP\IP provider for linking?
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:A4DB5E7F-56F3-46AE-9858-0F67546B82ED@.microsoft.com...
>
>
|||Misread first post. My bad. I don't know that there is a way to cut DTC
out of the loop, regardless of what connection mechanism you are using. I
don't have any other suggestions for possible workarounds for your issue
either.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:AF702DC6-3A07-4D85-9713-8C5406099EED@.microsoft.com...[vbcol=seagreen]
>I think I may be misunderstanding. I'm unaware of TCP/IP configurations
> other than what I already use. Which it may be that you misunderstood my
> question, thinking I am using named pipes (not so - have named pipes
> turned
> off).
> Thought is - Will named pipes offer a temporary solution to overcome the
> resource limitation imposed by the WinSvr2003 R2 without the DTC turned
> on?
> Dunno enough to guess whether there is something in it that will work.
> BACKGROUND:
> I currently use TCP/IP and aliasing and I suspect the linked servers use
> that already to communicate.
> When we access a view on a SQL 2000 server using SQL 2005 on another
> server
> (both are WinSvr 2003 R2 64 bit).
> There is a problem with the DTC (I did not mention this one - I am waiting
> for a server reboot to fix it - http://support.microsoft.com/kb/329332 or
> http://support.microsoft.com/?kbid=873160 The operation could not be
> performed because the OLE DB provider 'SQLOLEDB' was unable to begin a
> distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]. [OLE/DB provider
> returned message: New transaction cannot enlist in the specified
> transaction
> coordinator. ]).
> The one housing SQL 2000 does not yet have DTC setup for network access.
> The situation does not allow us to reboot (short of an emergency of which
> this is not - instead each view must be re-written to get around the
> resource
> issue - usually just create a sub-query in place of the view).
> Herein lies the problem: After rewriting a bunch of failing views we
> discovered that if a view is altered and consumes more resources, the
> borderline ones we use for data-warehousing archive information at night
> can
> fail for no other reason than that - view times out - resource limit
> reached... blah blah). I hate it when a job fails.
>
> --
> Regards,
> Jamie
>
> "TheSQLGuru" wrote:
The OLE DB provider "SQLNCLI" for linked server reported an error. Execution
terminated by the provider because a resource limit was reached.
Situation:
Large query is pulled from one server to another (data warehouse).
Suspect:
DTC on the Windows server 2003 where the source server is located (SQL 2k)
does not have DTC enabled:
Looking for:
A work-around as we are in a situation where the server cannot yet be
rebooted.
Question:
Is there a way to configure named pipes to effectively handle the resource
limitation on linked servers?
Regards,
Jamie
As a workaround, can you try using TCP\IP provider for linking?
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A4DB5E7F-56F3-46AE-9858-0F67546B82ED@.microsoft.com...
> Error:
> The OLE DB provider "SQLNCLI" for linked server reported an error.
> Execution
> terminated by the provider because a resource limit was reached.
> Situation:
> Large query is pulled from one server to another (data warehouse).
> Suspect:
> DTC on the Windows server 2003 where the source server is located (SQL 2k)
> does not have DTC enabled:
> Looking for:
> A work-around as we are in a situation where the server cannot yet be
> rebooted.
> Question:
> Is there a way to configure named pipes to effectively handle the resource
> limitation on linked servers?
> --
> Regards,
> Jamie
|||I think I may be misunderstanding. I'm unaware of TCP/IP configurations
other than what I already use. Which it may be that you misunderstood my
question, thinking I am using named pipes (not so - have named pipes turned
off).
Thought is - Will named pipes offer a temporary solution to overcome the
resource limitation imposed by the WinSvr2003 R2 without the DTC turned on?
Dunno enough to guess whether there is something in it that will work.
BACKGROUND:
I currently use TCP/IP and aliasing and I suspect the linked servers use
that already to communicate.
When we access a view on a SQL 2000 server using SQL 2005 on another server
(both are WinSvr 2003 R2 64 bit).
There is a problem with the DTC (I did not mention this one - I am waiting
for a server reboot to fix it - http://support.microsoft.com/kb/329332 or
http://support.microsoft.com/?kbid=873160 The operation could not be
performed because the OLE DB provider 'SQLOLEDB' was unable to begin a
distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]. [OLE/DB provider
returned message: New transaction cannot enlist in the specified transaction
coordinator. ]).
The one housing SQL 2000 does not yet have DTC setup for network access.
The situation does not allow us to reboot (short of an emergency of which
this is not - instead each view must be re-written to get around the resource
issue - usually just create a sub-query in place of the view).
Herein lies the problem: After rewriting a bunch of failing views we
discovered that if a view is altered and consumes more resources, the
borderline ones we use for data-warehousing archive information at night can
fail for no other reason than that - view times out - resource limit
reached... blah blah). I hate it when a job fails.
Regards,
Jamie
"TheSQLGuru" wrote:
> As a workaround, can you try using TCP\IP provider for linking?
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:A4DB5E7F-56F3-46AE-9858-0F67546B82ED@.microsoft.com...
>
>
|||Misread first post. My bad. I don't know that there is a way to cut DTC
out of the loop, regardless of what connection mechanism you are using. I
don't have any other suggestions for possible workarounds for your issue
either.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:AF702DC6-3A07-4D85-9713-8C5406099EED@.microsoft.com...[vbcol=seagreen]
>I think I may be misunderstanding. I'm unaware of TCP/IP configurations
> other than what I already use. Which it may be that you misunderstood my
> question, thinking I am using named pipes (not so - have named pipes
> turned
> off).
> Thought is - Will named pipes offer a temporary solution to overcome the
> resource limitation imposed by the WinSvr2003 R2 without the DTC turned
> on?
> Dunno enough to guess whether there is something in it that will work.
> BACKGROUND:
> I currently use TCP/IP and aliasing and I suspect the linked servers use
> that already to communicate.
> When we access a view on a SQL 2000 server using SQL 2005 on another
> server
> (both are WinSvr 2003 R2 64 bit).
> There is a problem with the DTC (I did not mention this one - I am waiting
> for a server reboot to fix it - http://support.microsoft.com/kb/329332 or
> http://support.microsoft.com/?kbid=873160 The operation could not be
> performed because the OLE DB provider 'SQLOLEDB' was unable to begin a
> distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]. [OLE/DB provider
> returned message: New transaction cannot enlist in the specified
> transaction
> coordinator. ]).
> The one housing SQL 2000 does not yet have DTC setup for network access.
> The situation does not allow us to reboot (short of an emergency of which
> this is not - instead each view must be re-written to get around the
> resource
> issue - usually just create a sub-query in place of the view).
> Herein lies the problem: After rewriting a bunch of failing views we
> discovered that if a view is altered and consumes more resources, the
> borderline ones we use for data-warehousing archive information at night
> can
> fail for no other reason than that - view times out - resource limit
> reached... blah blah). I hate it when a job fails.
>
> --
> Regards,
> Jamie
>
> "TheSQLGuru" wrote:
Resource Isolation of Multiple SQL Instances
Hello,
I would like to know (without experimentation) if by using two instances of
SQL Server I can isolate a first database which requires high repeatability
in query update times from another much larger second database which may
consume both more CPU and memory resources.
Assume: (let me know also if this cannot be done)
(a) that the server has dual processors each SQL instance is assigned to its
own CPU.
(b) that I have allocated separate memory to each instance.
(I have read that the overall performance for a single instance is better
than multiple instances on the same server. This fact may be irrelavent if
I can live with a degraded overall performance but guarantee that the
instances are adequately isolated and queries remain repeatable on the first
database.)
Thanks in advance,
Ted HannahTed,
I think you've got the hang of it.
One thing to consider is that the DBMS relies on the OS. So, when the DBMS
has a lot to do, then the OS will place a heavier load as well. And, in your
scenario, you only have one OS. This means that your "OLTP" database will
still be somewhat affected by your "reporting" database (I'm guessing where
regarding OLTP and reporting). I don't think I've ever seen any tests on how
much this "bleeding" one can expect, but I'd thought I at least mention it.
My guess is that it won't be that much - main resource usage would be inside
each SQL Server.
--
Tibor Karaszi
"Ted Hannah" <tedhannah@.hotmail.com> wrote in message
news:eGVF6eyoDHA.2404@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I would like to know (without experimentation) if by using two instances
of
> SQL Server I can isolate a first database which requires high
repeatability
> in query update times from another much larger second database which may
> consume both more CPU and memory resources.
> Assume: (let me know also if this cannot be done)
> (a) that the server has dual processors each SQL instance is assigned to
its
> own CPU.
> (b) that I have allocated separate memory to each instance.
> (I have read that the overall performance for a single instance is better
> than multiple instances on the same server. This fact may be irrelavent
if
> I can live with a degraded overall performance but guarantee that the
> instances are adequately isolated and queries remain repeatable on the
first
> database.)
>
> Thanks in advance,
> Ted Hannah
>|||Thanks - I would be very interested if anyone has measured this with some
real test. It would save some time. Speculation, unfortunately, won't be
good enough.
TED
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:_22qb.32395$mU6.93437@.newsb.telia.net...
> Ted,
> I think you've got the hang of it.
> One thing to consider is that the DBMS relies on the OS. So, when the DBMS
> has a lot to do, then the OS will place a heavier load as well. And, in
your
> scenario, you only have one OS. This means that your "OLTP" database will
> still be somewhat affected by your "reporting" database (I'm guessing
where
> regarding OLTP and reporting). I don't think I've ever seen any tests on
how
> much this "bleeding" one can expect, but I'd thought I at least mention
it.
> My guess is that it won't be that much - main resource usage would be
inside
> each SQL Server.
> --
> Tibor Karaszi
>
> "Ted Hannah" <tedhannah@.hotmail.com> wrote in message
> news:eGVF6eyoDHA.2404@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I would like to know (without experimentation) if by using two instances
> of
> > SQL Server I can isolate a first database which requires high
> repeatability
> > in query update times from another much larger second database which may
> > consume both more CPU and memory resources.
> > Assume: (let me know also if this cannot be done)
> > (a) that the server has dual processors each SQL instance is assigned to
> its
> > own CPU.
> > (b) that I have allocated separate memory to each instance.
> >
> > (I have read that the overall performance for a single instance is
better
> > than multiple instances on the same server. This fact may be irrelavent
> if
> > I can live with a degraded overall performance but guarantee that the
> > instances are adequately isolated and queries remain repeatable on the
> first
> > database.)
> >
> >
> > Thanks in advance,
> > Ted Hannah
> >
> >
>|||IO contention will continue to be a problem unless the databases are on
different PCI Busses, Controllers,and disks.
There are not any internal OS bottlenecks that I have seen that would
prevent you from successfully isolating workloads like this, but in a large
hetergeneous environment, it makes sense to keep the DSS and OLAP stuff on
different machines than OLTP. If you're looking at one server for two apps
and can seperate the IO channels, memory, and CPU affinity, then I think you
won't have any issues.
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Ted Hannah" <tedhannah@.hotmail.com> wrote in message
news:u6IQbu#oDHA.2424@.TK2MSFTNGP10.phx.gbl...
> Thanks - I would be very interested if anyone has measured this with some
> real test. It would save some time. Speculation, unfortunately, won't be
> good enough.
> TED
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:_22qb.32395$mU6.93437@.newsb.telia.net...
> > Ted,
> >
> > I think you've got the hang of it.
> > One thing to consider is that the DBMS relies on the OS. So, when the
DBMS
> > has a lot to do, then the OS will place a heavier load as well. And, in
> your
> > scenario, you only have one OS. This means that your "OLTP" database
will
> > still be somewhat affected by your "reporting" database (I'm guessing
> where
> > regarding OLTP and reporting). I don't think I've ever seen any tests on
> how
> > much this "bleeding" one can expect, but I'd thought I at least mention
> it.
> > My guess is that it won't be that much - main resource usage would be
> inside
> > each SQL Server.
> >
> > --
> > Tibor Karaszi
> >
> >
> > "Ted Hannah" <tedhannah@.hotmail.com> wrote in message
> > news:eGVF6eyoDHA.2404@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > I would like to know (without experimentation) if by using two
instances
> > of
> > > SQL Server I can isolate a first database which requires high
> > repeatability
> > > in query update times from another much larger second database which
may
> > > consume both more CPU and memory resources.
> > > Assume: (let me know also if this cannot be done)
> > > (a) that the server has dual processors each SQL instance is assigned
to
> > its
> > > own CPU.
> > > (b) that I have allocated separate memory to each instance.
> > >
> > > (I have read that the overall performance for a single instance is
> better
> > > than multiple instances on the same server. This fact may be
irrelavent
> > if
> > > I can live with a degraded overall performance but guarantee that the
> > > instances are adequately isolated and queries remain repeatable on the
> > first
> > > database.)
> > >
> > >
> > > Thanks in advance,
> > > Ted Hannah
> > >
> > >
> >
> >
>
I would like to know (without experimentation) if by using two instances of
SQL Server I can isolate a first database which requires high repeatability
in query update times from another much larger second database which may
consume both more CPU and memory resources.
Assume: (let me know also if this cannot be done)
(a) that the server has dual processors each SQL instance is assigned to its
own CPU.
(b) that I have allocated separate memory to each instance.
(I have read that the overall performance for a single instance is better
than multiple instances on the same server. This fact may be irrelavent if
I can live with a degraded overall performance but guarantee that the
instances are adequately isolated and queries remain repeatable on the first
database.)
Thanks in advance,
Ted HannahTed,
I think you've got the hang of it.
One thing to consider is that the DBMS relies on the OS. So, when the DBMS
has a lot to do, then the OS will place a heavier load as well. And, in your
scenario, you only have one OS. This means that your "OLTP" database will
still be somewhat affected by your "reporting" database (I'm guessing where
regarding OLTP and reporting). I don't think I've ever seen any tests on how
much this "bleeding" one can expect, but I'd thought I at least mention it.
My guess is that it won't be that much - main resource usage would be inside
each SQL Server.
--
Tibor Karaszi
"Ted Hannah" <tedhannah@.hotmail.com> wrote in message
news:eGVF6eyoDHA.2404@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I would like to know (without experimentation) if by using two instances
of
> SQL Server I can isolate a first database which requires high
repeatability
> in query update times from another much larger second database which may
> consume both more CPU and memory resources.
> Assume: (let me know also if this cannot be done)
> (a) that the server has dual processors each SQL instance is assigned to
its
> own CPU.
> (b) that I have allocated separate memory to each instance.
> (I have read that the overall performance for a single instance is better
> than multiple instances on the same server. This fact may be irrelavent
if
> I can live with a degraded overall performance but guarantee that the
> instances are adequately isolated and queries remain repeatable on the
first
> database.)
>
> Thanks in advance,
> Ted Hannah
>|||Thanks - I would be very interested if anyone has measured this with some
real test. It would save some time. Speculation, unfortunately, won't be
good enough.
TED
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:_22qb.32395$mU6.93437@.newsb.telia.net...
> Ted,
> I think you've got the hang of it.
> One thing to consider is that the DBMS relies on the OS. So, when the DBMS
> has a lot to do, then the OS will place a heavier load as well. And, in
your
> scenario, you only have one OS. This means that your "OLTP" database will
> still be somewhat affected by your "reporting" database (I'm guessing
where
> regarding OLTP and reporting). I don't think I've ever seen any tests on
how
> much this "bleeding" one can expect, but I'd thought I at least mention
it.
> My guess is that it won't be that much - main resource usage would be
inside
> each SQL Server.
> --
> Tibor Karaszi
>
> "Ted Hannah" <tedhannah@.hotmail.com> wrote in message
> news:eGVF6eyoDHA.2404@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I would like to know (without experimentation) if by using two instances
> of
> > SQL Server I can isolate a first database which requires high
> repeatability
> > in query update times from another much larger second database which may
> > consume both more CPU and memory resources.
> > Assume: (let me know also if this cannot be done)
> > (a) that the server has dual processors each SQL instance is assigned to
> its
> > own CPU.
> > (b) that I have allocated separate memory to each instance.
> >
> > (I have read that the overall performance for a single instance is
better
> > than multiple instances on the same server. This fact may be irrelavent
> if
> > I can live with a degraded overall performance but guarantee that the
> > instances are adequately isolated and queries remain repeatable on the
> first
> > database.)
> >
> >
> > Thanks in advance,
> > Ted Hannah
> >
> >
>|||IO contention will continue to be a problem unless the databases are on
different PCI Busses, Controllers,and disks.
There are not any internal OS bottlenecks that I have seen that would
prevent you from successfully isolating workloads like this, but in a large
hetergeneous environment, it makes sense to keep the DSS and OLAP stuff on
different machines than OLTP. If you're looking at one server for two apps
and can seperate the IO channels, memory, and CPU affinity, then I think you
won't have any issues.
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Ted Hannah" <tedhannah@.hotmail.com> wrote in message
news:u6IQbu#oDHA.2424@.TK2MSFTNGP10.phx.gbl...
> Thanks - I would be very interested if anyone has measured this with some
> real test. It would save some time. Speculation, unfortunately, won't be
> good enough.
> TED
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:_22qb.32395$mU6.93437@.newsb.telia.net...
> > Ted,
> >
> > I think you've got the hang of it.
> > One thing to consider is that the DBMS relies on the OS. So, when the
DBMS
> > has a lot to do, then the OS will place a heavier load as well. And, in
> your
> > scenario, you only have one OS. This means that your "OLTP" database
will
> > still be somewhat affected by your "reporting" database (I'm guessing
> where
> > regarding OLTP and reporting). I don't think I've ever seen any tests on
> how
> > much this "bleeding" one can expect, but I'd thought I at least mention
> it.
> > My guess is that it won't be that much - main resource usage would be
> inside
> > each SQL Server.
> >
> > --
> > Tibor Karaszi
> >
> >
> > "Ted Hannah" <tedhannah@.hotmail.com> wrote in message
> > news:eGVF6eyoDHA.2404@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > I would like to know (without experimentation) if by using two
instances
> > of
> > > SQL Server I can isolate a first database which requires high
> > repeatability
> > > in query update times from another much larger second database which
may
> > > consume both more CPU and memory resources.
> > > Assume: (let me know also if this cannot be done)
> > > (a) that the server has dual processors each SQL instance is assigned
to
> > its
> > > own CPU.
> > > (b) that I have allocated separate memory to each instance.
> > >
> > > (I have read that the overall performance for a single instance is
> better
> > > than multiple instances on the same server. This fact may be
irrelavent
> > if
> > > I can live with a degraded overall performance but guarantee that the
> > > instances are adequately isolated and queries remain repeatable on the
> > first
> > > database.)
> > >
> > >
> > > Thanks in advance,
> > > Ted Hannah
> > >
> > >
> >
> >
>
Resource In SQL Reporting Services
Can someone help me
is SQL reporting services supports the Resource .
HI, Nezo:
Would you please elaborate more about your requirement? Do you want to use some resource files?
|||
Hi Nizar,
I think what you want is to use some Name/Value pairs, in which value can be accessed using name. I dont think there is anything like Resource files as we have in visual studio.
The only way I can think of to do something like this is by using custom assemblies(http://msdn2.microsoft.com/en-us/library/ms153561.aspx) , you should be able to access properties in assemblies.
|||
thanks alot
but i need drop down List which contain Language like (English , Arabic)
when someone choose language the report change the Title and Alignment to Left if English And to Right when arabic
if that possible in SQL reporting or not
please help me
|||I dont see any direct functionality provided in SSRS to do this, what you can do is to pass the selected language(from list) to the report and set the TextAlign property of Title using Expressions.
Resource Governor(searching.....)
Hi,
Unable to find the resource governor.
If it's available in the June CTP can tell me where to locate it and if its not there whn can we possibly get it.
It is not part of the June CTP. It will be available in a future CTP.Thanks,|||Thanks Marcel.
|||I am unable to find the Resource Governor in Jul-CTP as well. Can you tell when can i have a glimpse of this new feature.
sql
resource file 'msmdsrv.rll' for locale '1049' could not be loaded
Hi forum,
does anyone know the cause of this error message?
23-Mar-2007 16:03:50
Computer: <ComputerName>
Source: MSSQLServerOLAPService
Category: None
Type: Error
Event ID: 25
Description: The resource file 'msmdsrv.rll' for locale '1049' could not be loaded. Switching to US English (1033) if available.
Tia,
/Gert
There is no localized version for locale 1049, or it was not installed.|||Hello I'm with the same problem but from spanish(1034):
How can I solve it?
|||You need to install a version that includes the specific resource files for your locale. I'm not sure if one is available or not.resource file 'msmdsrv.rll' for locale '1049' could not be loaded
Hi forum,
does anyone know the cause of this error message?
23-Mar-2007 16:03:50
Computer: <ComputerName>
Source: MSSQLServerOLAPService
Category: None
Type: Error
Event ID: 25
Description: The resource file 'msmdsrv.rll' for locale '1049' could not be loaded. Switching to US English (1033) if available.
Tia,
/Gert
There is no localized version for locale 1049, or it was not installed.|||Hello I'm with the same problem but from spanish(1034):
How can I solve it?
|||You need to install a version that includes the specific resource files for your locale. I'm not sure if one is available or not.Resource Accumulation on SQLServer
We have a JDBC project that works with SQLServer, along with other
DBs. During development, we noticed that resource useage on SQLServer
seemed to grow steadily - Processes, Process Locks and/or Object Locks,
according to the "Current Activity" section of Enterprise Manager.
Fairly quickly (a few hours), SQLServer would use so many resources it
used up all available system memory and the system would crash.
DBs. During development, we noticed that resource useage on SQLServer
seemed to grow steadily - Processes, Process Locks and/or Object Locks,
according to the "Current Activity" section of Enterprise Manager.
Fairly quickly (a few hours), SQLServer would use so many resources it
used up all available system memory and the system would crash.
Without understanding much about why that was happening, mostly out of
trial and error, we started using transactions, which seemed to fix the
problem. Now, one of our customers seems to be seeing the problem again:
511 Process Locks, 18 Object Locks, 526 Processes
after a few hours. The system runs out of memory and crashes. We have
multiple test configurations, including Linux, Win2K, XP, but we do
not see the same behaviour - we typically see no more than 20 or 30
processes, process locks or object locks, and our tests seem to run
indefinitely. Since we can not reliably reproduce the symptoms that our
customer sees, it is very difficult for us to analyze, let alone, fix
the problem.
Does anyone - especially SQLServer pros - have any insight as to what is
going on here and/or how to fix it? Like how does a process, process
lock or object lock relate to JDBC? process <-> connection,
object <-> row, table... or what, locks?
Thanks,
Gary Whitten
whitteng@.con2inc.comGary Whitten wrote:
> We have a JDBC project that works with SQLServer, along with other
> DBs. During development, we noticed that resource useage on SQLServer
> seemed to grow steadily - Processes, Process Locks and/or Object Locks,
> according to the "Current Activity" section of Enterprise Manager.
> Fairly quickly (a few hours), SQLServer would use so many resources it
> used up all available system memory and the system would crash.
> Without understanding much about why that was happening, mostly out of
> trial and error, we started using transactions, which seemed to fix the
> problem. Now, one of our customers seems to be seeing the problem again:
> 511 Process Locks, 18 Object Locks, 526 Processes
> after a few hours. The system runs out of memory and crashes. We have
> multiple test configurations, including Linux, Win2K, XP, but we do
> not see the same behaviour - we typically see no more than 20 or 30
> processes, process locks or object locks, and our tests seem to run
> indefinitely. Since we can not reliably reproduce the symptoms that our
> customer sees, it is very difficult for us to analyze, let alone, fix
> the problem.
> Does anyone - especially SQLServer pros - have any insight as to what is
> going on here and/or how to fix it? Like how does a process, process
> lock or object lock relate to JDBC? process <-> connection,
> object <-> row, table... or what, locks?
Check that your application is closing Connections, Statements and
ResultSet after they have been used (also when exceptions has occurred)
In SQL server every connection get process id and to me 511
processes/connections sounds a lot.
I saw similar kind of problems at one application and reason was that
developer didn't close resultset's everytime, when he got Exception he
didn't close ResultSets.
- Sampsa
--
--------------
// Sampsa Sohlman //
// My email can be found on my homepage //
// http://sampsa.sohlman.com //
--------------|||[posted and mailed, please reply in news]
Gary Whitten (whitteng@.con2inc.com) writes:
> We have a JDBC project that works with SQLServer, along with other
> DBs. During development, we noticed that resource useage on SQLServer
> seemed to grow steadily - Processes, Process Locks and/or Object Locks,
> according to the "Current Activity" section of Enterprise Manager.
> Fairly quickly (a few hours), SQLServer would use so many resources it
> used up all available system memory and the system would crash.
Hm, what do you mean with "crashes"? If you exhaust some resource, I
would rather expect SQL Server kill a connection, or refuse to let
someone in, but I would not expect it to crash.
One thing I should point out from the beginning: the fact that SQL Server
grabs about all available memory on the machine is perfectly normal. It
thinks that the more data it can have in cache, the better.
That said, if the number of locks accumulate, then you might have a problem.
> Without understanding much about why that was happening, mostly out of
> trial and error, we started using transactions, which seemed to fix the
> problem. Now, one of our customers seems to be seeing the problem again:
> 511 Process Locks, 18 Object Locks, 526 Processes
I don't know what a "process lock" is, but I get the feeling that you've
been running the Performance Monitor; I never run that tool myself. I
would guess it is a lock on the current database, which about every
process has. Thus, nothing to lose sleep over.
18 object locks is not very much at all. It seems like an idle database.
So is 526 connections bad or not? I can't tell, because I don't know
your application. But assuming that you expect a far lower number, you
may need to review how you close connections. (But since I don't know
JDBC or Java, I can tell about the client-side parts here.) The connections
may just be oprhans. Sampsa suggested that you may not close result sets,
but if you had failed to get all data, I would expect a lot more object
locks.
A shot from the hip, is that you should look into to issue SET NOCOUNT
ON when you open your connections. If you are using stored procedures,
but that in your SPs. Without SET NOCOUNT ON, you get empty resultsets
with the row count for INSERT/DELETE/UPDATE statements; these could be
the culprits.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Resotring a replicated database
I have a database A on server_1 that is being merge replicated to database B on server_2 which is production a box
NOW
I want to restore database A on server_3 that is being merge replicated to database B on Server_4 which is test box.
How about do I do that with replication being involved?
ThanxI don't know that there is any way to restore a database that is involved in replication. It smells like a VERY bad thing to me because it would do horribly confusing things to the data and especially the log information that could send replication into hysterics!
If you really want to do this, I'd delete the server from the replication schema (probably all of the servers from the schema for Merge Replication), then do the restore. After that restore was complete, then I'd rebuild whatever replication I needed.
-PatP|||You can backup the replicated database but follow what Pat referred and go with this MSDN http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replbackup_8enn.asp link for more details.
NOW
I want to restore database A on server_3 that is being merge replicated to database B on Server_4 which is test box.
How about do I do that with replication being involved?
ThanxI don't know that there is any way to restore a database that is involved in replication. It smells like a VERY bad thing to me because it would do horribly confusing things to the data and especially the log information that could send replication into hysterics!
If you really want to do this, I'd delete the server from the replication schema (probably all of the servers from the schema for Merge Replication), then do the restore. After that restore was complete, then I'd rebuild whatever replication I needed.
-PatP|||You can backup the replicated database but follow what Pat referred and go with this MSDN http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replbackup_8enn.asp link for more details.
Resotring a backup file to a DB on a differerent server
Hi,
I'm trying to restore a backup file to a data base on a different server by
overwriting it.
In order to do this I need to move the logical file I am restoring to the
location of the physical database file I am restoring to (as per the prompt I
am receiving)
When it say "MOVE", will this in fact move the file over & possibly damage
the database from which the restore is being taken? Does it move any files or
just copy?
Is there any danger here?
Below is what I'm doing:
RESTORE DATABASE oneDB
FROM DISK = 'C:\myPhysicalBackupFile'
WITH REPLACE,
MOVE 'myLogicalFile' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PhysicalFile.MDF'
Many thanks for any help on this
AntIt's a little confusing the first time, but all the MOVE is doing is telling
the engine where to place the MDF file. The FROM is the .BAK file.
And if you're overwriting an exiting database, then yes, you're gonna whack
it. But that is expected behavior.
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:69B97720-9594-48A8-9181-53F874B8EB3E@.microsoft.com...
> Hi,
> I'm trying to restore a backup file to a data base on a different server
> by
> overwriting it.
> In order to do this I need to move the logical file I am restoring to the
> location of the physical database file I am restoring to (as per the
> prompt I
> am receiving)
> When it say "MOVE", will this in fact move the file over & possibly damage
> the database from which the restore is being taken? Does it move any files
> or
> just copy?
> Is there any danger here?
> Below is what I'm doing:
> RESTORE DATABASE oneDB
> FROM DISK = 'C:\myPhysicalBackupFile'
> WITH REPLACE,
> MOVE 'myLogicalFile' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\PhysicalFile.MDF'
>
> Many thanks for any help on this
> Ant
>|||Hi Jay, thanks very much for the reply.
It's not the Database I'm overwriting that I'm concerned about, so long as
it doesn't affect the database from where I'm using the Backup from. I was
concerned that it might damage that. Sounds safe.
Many thanks for your answer Jay.
"Jay" wrote:
> It's a little confusing the first time, but all the MOVE is doing is telling
> the engine where to place the MDF file. The FROM is the .BAK file.
> And if you're overwriting an exiting database, then yes, you're gonna whack
> it. But that is expected behavior.
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:69B97720-9594-48A8-9181-53F874B8EB3E@.microsoft.com...
> > Hi,
> >
> > I'm trying to restore a backup file to a data base on a different server
> > by
> > overwriting it.
> >
> > In order to do this I need to move the logical file I am restoring to the
> > location of the physical database file I am restoring to (as per the
> > prompt I
> > am receiving)
> >
> > When it say "MOVE", will this in fact move the file over & possibly damage
> > the database from which the restore is being taken? Does it move any files
> > or
> > just copy?
> >
> > Is there any danger here?
> >
> > Below is what I'm doing:
> >
> > RESTORE DATABASE oneDB
> >
> > FROM DISK = 'C:\myPhysicalBackupFile'
> > WITH REPLACE,
> > MOVE 'myLogicalFile' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\data\PhysicalFile.MDF'
> >
> >
> > Many thanks for any help on this
> >
> > Ant
> >
>
>|||Ant
Please read also
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:A48539B7-996B-4A16-8F99-1100515146C7@.microsoft.com...
> Hi Jay, thanks very much for the reply.
> It's not the Database I'm overwriting that I'm concerned about, so long as
> it doesn't affect the database from where I'm using the Backup from. I was
> concerned that it might damage that. Sounds safe.
> Many thanks for your answer Jay.
>
> "Jay" wrote:
>> It's a little confusing the first time, but all the MOVE is doing is
>> telling
>> the engine where to place the MDF file. The FROM is the .BAK file.
>> And if you're overwriting an exiting database, then yes, you're gonna
>> whack
>> it. But that is expected behavior.
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:69B97720-9594-48A8-9181-53F874B8EB3E@.microsoft.com...
>> > Hi,
>> >
>> > I'm trying to restore a backup file to a data base on a different
>> > server
>> > by
>> > overwriting it.
>> >
>> > In order to do this I need to move the logical file I am restoring to
>> > the
>> > location of the physical database file I am restoring to (as per the
>> > prompt I
>> > am receiving)
>> >
>> > When it say "MOVE", will this in fact move the file over & possibly
>> > damage
>> > the database from which the restore is being taken? Does it move any
>> > files
>> > or
>> > just copy?
>> >
>> > Is there any danger here?
>> >
>> > Below is what I'm doing:
>> >
>> > RESTORE DATABASE oneDB
>> >
>> > FROM DISK = 'C:\myPhysicalBackupFile'
>> > WITH REPLACE,
>> > MOVE 'myLogicalFile' TO 'C:\Program Files\Microsoft SQL
>> > Server\MSSQL\data\PhysicalFile.MDF'
>> >
>> >
>> > Many thanks for any help on this
>> >
>> > Ant
>> >
>>sql
I'm trying to restore a backup file to a data base on a different server by
overwriting it.
In order to do this I need to move the logical file I am restoring to the
location of the physical database file I am restoring to (as per the prompt I
am receiving)
When it say "MOVE", will this in fact move the file over & possibly damage
the database from which the restore is being taken? Does it move any files or
just copy?
Is there any danger here?
Below is what I'm doing:
RESTORE DATABASE oneDB
FROM DISK = 'C:\myPhysicalBackupFile'
WITH REPLACE,
MOVE 'myLogicalFile' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PhysicalFile.MDF'
Many thanks for any help on this
AntIt's a little confusing the first time, but all the MOVE is doing is telling
the engine where to place the MDF file. The FROM is the .BAK file.
And if you're overwriting an exiting database, then yes, you're gonna whack
it. But that is expected behavior.
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:69B97720-9594-48A8-9181-53F874B8EB3E@.microsoft.com...
> Hi,
> I'm trying to restore a backup file to a data base on a different server
> by
> overwriting it.
> In order to do this I need to move the logical file I am restoring to the
> location of the physical database file I am restoring to (as per the
> prompt I
> am receiving)
> When it say "MOVE", will this in fact move the file over & possibly damage
> the database from which the restore is being taken? Does it move any files
> or
> just copy?
> Is there any danger here?
> Below is what I'm doing:
> RESTORE DATABASE oneDB
> FROM DISK = 'C:\myPhysicalBackupFile'
> WITH REPLACE,
> MOVE 'myLogicalFile' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\PhysicalFile.MDF'
>
> Many thanks for any help on this
> Ant
>|||Hi Jay, thanks very much for the reply.
It's not the Database I'm overwriting that I'm concerned about, so long as
it doesn't affect the database from where I'm using the Backup from. I was
concerned that it might damage that. Sounds safe.
Many thanks for your answer Jay.
"Jay" wrote:
> It's a little confusing the first time, but all the MOVE is doing is telling
> the engine where to place the MDF file. The FROM is the .BAK file.
> And if you're overwriting an exiting database, then yes, you're gonna whack
> it. But that is expected behavior.
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:69B97720-9594-48A8-9181-53F874B8EB3E@.microsoft.com...
> > Hi,
> >
> > I'm trying to restore a backup file to a data base on a different server
> > by
> > overwriting it.
> >
> > In order to do this I need to move the logical file I am restoring to the
> > location of the physical database file I am restoring to (as per the
> > prompt I
> > am receiving)
> >
> > When it say "MOVE", will this in fact move the file over & possibly damage
> > the database from which the restore is being taken? Does it move any files
> > or
> > just copy?
> >
> > Is there any danger here?
> >
> > Below is what I'm doing:
> >
> > RESTORE DATABASE oneDB
> >
> > FROM DISK = 'C:\myPhysicalBackupFile'
> > WITH REPLACE,
> > MOVE 'myLogicalFile' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\data\PhysicalFile.MDF'
> >
> >
> > Many thanks for any help on this
> >
> > Ant
> >
>
>|||Ant
Please read also
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:A48539B7-996B-4A16-8F99-1100515146C7@.microsoft.com...
> Hi Jay, thanks very much for the reply.
> It's not the Database I'm overwriting that I'm concerned about, so long as
> it doesn't affect the database from where I'm using the Backup from. I was
> concerned that it might damage that. Sounds safe.
> Many thanks for your answer Jay.
>
> "Jay" wrote:
>> It's a little confusing the first time, but all the MOVE is doing is
>> telling
>> the engine where to place the MDF file. The FROM is the .BAK file.
>> And if you're overwriting an exiting database, then yes, you're gonna
>> whack
>> it. But that is expected behavior.
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:69B97720-9594-48A8-9181-53F874B8EB3E@.microsoft.com...
>> > Hi,
>> >
>> > I'm trying to restore a backup file to a data base on a different
>> > server
>> > by
>> > overwriting it.
>> >
>> > In order to do this I need to move the logical file I am restoring to
>> > the
>> > location of the physical database file I am restoring to (as per the
>> > prompt I
>> > am receiving)
>> >
>> > When it say "MOVE", will this in fact move the file over & possibly
>> > damage
>> > the database from which the restore is being taken? Does it move any
>> > files
>> > or
>> > just copy?
>> >
>> > Is there any danger here?
>> >
>> > Below is what I'm doing:
>> >
>> > RESTORE DATABASE oneDB
>> >
>> > FROM DISK = 'C:\myPhysicalBackupFile'
>> > WITH REPLACE,
>> > MOVE 'myLogicalFile' TO 'C:\Program Files\Microsoft SQL
>> > Server\MSSQL\data\PhysicalFile.MDF'
>> >
>> >
>> > Many thanks for any help on this
>> >
>> > Ant
>> >
>>sql
resotre the user in the database
Before formatting the server the user took the backup of the database.
After the installation the database was restored but we can find the all the
user in resored database obivously the all user are not in the logins under
the security. Is there anyway to copy the all the user from resoted database
to logins
Thanks in advance
NizhamTake a look at sp_change_users_login system stored procedure in the BOL
"Nizham" <junkmn@.gmail.com> wrote in message
news:%23Ue8kb%23SGHA.792@.TK2MSFTNGP10.phx.gbl...
> Before formatting the server the user took the backup of the database.
> After the installation the database was restored but we can find the all
> the user in resored database obivously the all user are not in the logins
> under the security. Is there anyway to copy the all the user from resoted
> database to logins
> Thanks in advance
> Nizham
>|||There are more than 100 user in the databse any can help me on this
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uQ4l%23e%23SGHA.1236@.TK2MSFTNGP11.phx.gbl...
> Take a look at sp_change_users_login system stored procedure in the BOL
>
>
> "Nizham" <junkmn@.gmail.com> wrote in message
> news:%23Ue8kb%23SGHA.792@.TK2MSFTNGP10.phx.gbl...
>|||There's a GUI over sp_change_users_login available at www.dbmaint.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nizham" <mnizham@.gmail.com> wrote in message news:e%236XkwDTGHA.4340@.tk2msftngp13.phx.gbl.
.
> There are more than 100 user in the databse any can help me on this
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uQ4l%23e%23SGHA.1236@.TK2MSFTNGP11.phx.gbl...
>
After the installation the database was restored but we can find the all the
user in resored database obivously the all user are not in the logins under
the security. Is there anyway to copy the all the user from resoted database
to logins
Thanks in advance
NizhamTake a look at sp_change_users_login system stored procedure in the BOL
"Nizham" <junkmn@.gmail.com> wrote in message
news:%23Ue8kb%23SGHA.792@.TK2MSFTNGP10.phx.gbl...
> Before formatting the server the user took the backup of the database.
> After the installation the database was restored but we can find the all
> the user in resored database obivously the all user are not in the logins
> under the security. Is there anyway to copy the all the user from resoted
> database to logins
> Thanks in advance
> Nizham
>|||There are more than 100 user in the databse any can help me on this
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uQ4l%23e%23SGHA.1236@.TK2MSFTNGP11.phx.gbl...
> Take a look at sp_change_users_login system stored procedure in the BOL
>
>
> "Nizham" <junkmn@.gmail.com> wrote in message
> news:%23Ue8kb%23SGHA.792@.TK2MSFTNGP10.phx.gbl...
>|||There's a GUI over sp_change_users_login available at www.dbmaint.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nizham" <mnizham@.gmail.com> wrote in message news:e%236XkwDTGHA.4340@.tk2msftngp13.phx.gbl.
.
> There are more than 100 user in the databse any can help me on this
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uQ4l%23e%23SGHA.1236@.TK2MSFTNGP11.phx.gbl...
>
Resorting Columns
Here's something I've never run into before, and I hope someone can help.
I have two identical SQL2000 databases (DB1 and DB2). Each has a different
name and is part of a different merge scheme. My developer has an
application that writes data from a table in DB1 to an identical table in
DB2, but it's failing. He says that it's because the columns in one table
aren't in the same order as the columns in the other table. E.g., one table
has column19 in the 19th position, and the other table has column19 in the
15th position.
Is there a way to move a column from one position to another without
removing the databases from replication?
I'd really appreciate some help in this.
Thanks,
John Steen
Developers should never ever rely on the column order. Tell your developer
to stop being lazy and specify the columns in the insert statement. If he
doesn't he will introduce some very subtle bugs over time that will be nearly
impossible to track down. I made the same mistake at my first job while I
was still in college and still regret it.
|||Thank, Scott, I'll definitely pass that along to our developer.
Unfortunately, I don't think it's going to fly this time. We're trying to
piece together a quick solution for a short-term problem that will be going
away by the end of the month. He's not going to want to rewrite all his apps
just for that. If there's a way for me to change the position of the column
in the table so the app will work, I'd rather do that.
Thanks,
John
"Scott S." wrote:
> Developers should never ever rely on the column order. Tell your developer
> to stop being lazy and specify the columns in the insert statement. If he
> doesn't he will introduce some very subtle bugs over time that will be nearly
> impossible to track down. I made the same mistake at my first job while I
> was still in college and still regret it.
I have two identical SQL2000 databases (DB1 and DB2). Each has a different
name and is part of a different merge scheme. My developer has an
application that writes data from a table in DB1 to an identical table in
DB2, but it's failing. He says that it's because the columns in one table
aren't in the same order as the columns in the other table. E.g., one table
has column19 in the 19th position, and the other table has column19 in the
15th position.
Is there a way to move a column from one position to another without
removing the databases from replication?
I'd really appreciate some help in this.
Thanks,
John Steen
Developers should never ever rely on the column order. Tell your developer
to stop being lazy and specify the columns in the insert statement. If he
doesn't he will introduce some very subtle bugs over time that will be nearly
impossible to track down. I made the same mistake at my first job while I
was still in college and still regret it.
|||Thank, Scott, I'll definitely pass that along to our developer.
Unfortunately, I don't think it's going to fly this time. We're trying to
piece together a quick solution for a short-term problem that will be going
away by the end of the month. He's not going to want to rewrite all his apps
just for that. If there's a way for me to change the position of the column
in the table so the app will work, I'd rather do that.
Thanks,
John
"Scott S." wrote:
> Developers should never ever rely on the column order. Tell your developer
> to stop being lazy and specify the columns in the insert statement. If he
> doesn't he will introduce some very subtle bugs over time that will be nearly
> impossible to track down. I made the same mistake at my first job while I
> was still in college and still regret it.
Resorting a report by Column click
have a report that has about 7 columns
I would like to enable the user to be able to resort the report by simply
clicking on a column heading. For example if my Column names are
County Organization Name Program Name
I would the user to be able to click the County Column header and re-sort
the entire report in a descending alpha order and then also click
Organization name column header to re-sort by column name.
Anyone have any ideas of how this might be accomplished?Various approaches for RS 2000 have been discussed on this newsgroup. Search
the newsgroup for "dynamic sort column" or similar keywords.
You may also want to check a sample posted on GotDotNet:
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=6a347b53-5594-40f9-861d-876beabeda16
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Report Guy" <Report Guy@.discussions.microsoft.com> wrote in message
news:F6EA8E3B-5810-4914-82D7-520C749E4B11@.microsoft.com...
> have a report that has about 7 columns
> I would like to enable the user to be able to resort the report by simply
> clicking on a column heading. For example if my Column names are
> County Organization Name Program Name
> I would the user to be able to click the County Column header and re-sort
> the entire report in a descending alpha order and then also click
> Organization name column header to re-sort by column name.
> Anyone have any ideas of how this might be accomplished?|||There are also several more examples on www.msbicentral.com
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:up5254EHFHA.2740@.TK2MSFTNGP12.phx.gbl...
> Various approaches for RS 2000 have been discussed on this newsgroup.
> Search the newsgroup for "dynamic sort column" or similar keywords.
> You may also want to check a sample posted on GotDotNet:
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=6a347b53-5594-40f9-861d-876beabeda16
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Report Guy" <Report Guy@.discussions.microsoft.com> wrote in message
> news:F6EA8E3B-5810-4914-82D7-520C749E4B11@.microsoft.com...
>> have a report that has about 7 columns
>> I would like to enable the user to be able to resort the report by simply
>> clicking on a column heading. For example if my Column names are
>> County Organization Name Program Name
>> I would the user to be able to click the County Column header and re-sort
>> the entire report in a descending alpha order and then also click
>> Organization name column header to re-sort by column name.
>> Anyone have any ideas of how this might be accomplished?
>|||Ok I went to the link that you posted below. I downloaded the sample report
and am trying to figure it out. Where is the code other other event that
does the re-sort?
I copied the same report parameters into the project I'm working on and they
didn't work. Of course, i taylored the fields in the database to field names
I have in my DB, but I can't get the resort to work. There's no readme or
other documentation, so i'm hoping someone can help me out...
"Robert Bruckner [MSFT]" wrote:
> Various approaches for RS 2000 have been discussed on this newsgroup. Search
> the newsgroup for "dynamic sort column" or similar keywords.
> You may also want to check a sample posted on GotDotNet:
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=6a347b53-5594-40f9-861d-876beabeda16
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Report Guy" <Report Guy@.discussions.microsoft.com> wrote in message
> news:F6EA8E3B-5810-4914-82D7-520C749E4B11@.microsoft.com...
> > have a report that has about 7 columns
> >
> > I would like to enable the user to be able to resort the report by simply
> > clicking on a column heading. For example if my Column names are
> > County Organization Name Program Name
> >
> > I would the user to be able to click the County Column header and re-sort
> > the entire report in a descending alpha order and then also click
> > Organization name column header to re-sort by column name.
> >
> > Anyone have any ideas of how this might be accomplished?
>
>
I would like to enable the user to be able to resort the report by simply
clicking on a column heading. For example if my Column names are
County Organization Name Program Name
I would the user to be able to click the County Column header and re-sort
the entire report in a descending alpha order and then also click
Organization name column header to re-sort by column name.
Anyone have any ideas of how this might be accomplished?Various approaches for RS 2000 have been discussed on this newsgroup. Search
the newsgroup for "dynamic sort column" or similar keywords.
You may also want to check a sample posted on GotDotNet:
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=6a347b53-5594-40f9-861d-876beabeda16
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Report Guy" <Report Guy@.discussions.microsoft.com> wrote in message
news:F6EA8E3B-5810-4914-82D7-520C749E4B11@.microsoft.com...
> have a report that has about 7 columns
> I would like to enable the user to be able to resort the report by simply
> clicking on a column heading. For example if my Column names are
> County Organization Name Program Name
> I would the user to be able to click the County Column header and re-sort
> the entire report in a descending alpha order and then also click
> Organization name column header to re-sort by column name.
> Anyone have any ideas of how this might be accomplished?|||There are also several more examples on www.msbicentral.com
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:up5254EHFHA.2740@.TK2MSFTNGP12.phx.gbl...
> Various approaches for RS 2000 have been discussed on this newsgroup.
> Search the newsgroup for "dynamic sort column" or similar keywords.
> You may also want to check a sample posted on GotDotNet:
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=6a347b53-5594-40f9-861d-876beabeda16
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Report Guy" <Report Guy@.discussions.microsoft.com> wrote in message
> news:F6EA8E3B-5810-4914-82D7-520C749E4B11@.microsoft.com...
>> have a report that has about 7 columns
>> I would like to enable the user to be able to resort the report by simply
>> clicking on a column heading. For example if my Column names are
>> County Organization Name Program Name
>> I would the user to be able to click the County Column header and re-sort
>> the entire report in a descending alpha order and then also click
>> Organization name column header to re-sort by column name.
>> Anyone have any ideas of how this might be accomplished?
>|||Ok I went to the link that you posted below. I downloaded the sample report
and am trying to figure it out. Where is the code other other event that
does the re-sort?
I copied the same report parameters into the project I'm working on and they
didn't work. Of course, i taylored the fields in the database to field names
I have in my DB, but I can't get the resort to work. There's no readme or
other documentation, so i'm hoping someone can help me out...
"Robert Bruckner [MSFT]" wrote:
> Various approaches for RS 2000 have been discussed on this newsgroup. Search
> the newsgroup for "dynamic sort column" or similar keywords.
> You may also want to check a sample posted on GotDotNet:
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=6a347b53-5594-40f9-861d-876beabeda16
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Report Guy" <Report Guy@.discussions.microsoft.com> wrote in message
> news:F6EA8E3B-5810-4914-82D7-520C749E4B11@.microsoft.com...
> > have a report that has about 7 columns
> >
> > I would like to enable the user to be able to resort the report by simply
> > clicking on a column heading. For example if my Column names are
> > County Organization Name Program Name
> >
> > I would the user to be able to click the County Column header and re-sort
> > the entire report in a descending alpha order and then also click
> > Organization name column header to re-sort by column name.
> >
> > Anyone have any ideas of how this might be accomplished?
>
>
Resoring transaction logs
We believe we lost data yesterday in one of our tables, but only have a full
backup from 7/12 and have transaction logs from 7/14. Is there anything we
can do here? Don't you need the transaction logs from the last full backup
time in order to restore from the logs?
Help!Yes, you should be able to apply those transaction logs to that full backup
since you have a break in the sequence.(please someone correct me if I'm
wrong). You don't have logs backed up to tape anywhere?
If not, then you might want to look into Lumigent's Log Explorer to examine
the transaction logs. www.lumigent.com
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||hi Mark. First you need to backup the transaction log (should be available
regardless of the state of the failed db) before you do anything else. Then
you can restore the db from the full specifying no recovery to allow you to
continue restoring. Finally you can restore the transaction logs with a
recovery so that transactions will be rolled forward or back and the db will
then become available.
Please refer to books online for this procedure
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||Yes and No.
Yes - If 7/14 logs are the immediate logs backed up after 7/12 full backup.
You can apply logs all the way up to the most recent backup.
No - You backed up logs two days later? Logs should be backed up much more
frequently. That's what log backup is meant. If at least one log backup was
missing between 7/12 and 7/14, then you can not use the 7/14 logs at all.
Richard
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||..agreed log backup should be performed at LEAST daily, however there should
be no reason why you should be missing any logs since your last full backup,
assuming of course you are not using the simple recovery model -in which
case you can kiss your logs goodbye.
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:eRtXVqsUDHA.1484@.TK2MSFTNGP12.phx.gbl...
> Yes and No.
> Yes - If 7/14 logs are the immediate logs backed up after 7/12 full
backup.
> You can apply logs all the way up to the most recent backup.
> No - You backed up logs two days later? Logs should be backed up much more
> frequently. That's what log backup is meant. If at least one log backup
was
> missing between 7/12 and 7/14, then you can not use the 7/14 logs at all.
>
> Richard
> "Mark" <maxmann@.kc.rr.com> wrote in message
> news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> > We believe we lost data yesterday in one of our tables, but only have a
> full
> > backup from 7/12 and have transaction logs from 7/14. Is there anything
> we
> > can do here? Don't you need the transaction logs from the last full
> backup
> > time in order to restore from the logs?
> >
> >
> > Help!
> >
> >
>|||Well, just to explain the situation a little further.
1. We are using Full Recover mode, which should be a good thing as far as
I'm concerned.
2. The reason we were missing the two days of logs is because we have two
jobs set in sql server.
One to do a daily full backup and 1 to do hourly trans log backups,
which is cool. The problem is
that on the 12th, somehow the job to do the full backups go disabled,
and we only have trans log backups
on tape back to the 14th. So that's how the gap happened. It's not
that we were doing trans log backups
every two days. It was just the issue w/ that job being disabled.
3. We have contacted lumigent and they said since we have the db in full
recovery mode that we should
be able to completely restore the database table that got deleted simply
by using the transaction log
that we have of when it happened.
Thanks for all the help.
Mark
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||James, firstly just because Mark states "full
backup from 7/12 and have transaction logs from 7/14" doesnt mean to suggest
that there is a break in the sequence only that he had a one day gap before
had did transaction log backups.
Secondly it doesnt matter whether he has got subsequent logs to tape or not
since the transaction log is *still* available for backup before he starts
the recovery. The Log Explorer tool (very good) is probably unnecessary in
this scenario).
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"James Luetkehoelter" <james.luetkehoetler@.NOSPAMspyglassllc.com> wrote in
message news:eEF4jHtUDHA.964@.TK2MSFTNGP09.phx.gbl...
> Er, this should have read "Shouldn't be able to apply". If there's a
break
> in the logs, you shouldn't be able to do a point-in-time recovery.
> "James Luetkehoelter" <james.luetkehoetler@.NOSPAMspyglassllc.com> wrote in
> message news:%23OK$bcsUDHA.1872@.TK2MSFTNGP12.phx.gbl...
> > Yes, you should be able to apply those transaction logs to that full
> backup
> > since you have a break in the sequence.(please someone correct me if I'm
> > wrong). You don't have logs backed up to tape anywhere?
> >
> > If not, then you might want to look into Lumigent's Log Explorer to
> examine
> > the transaction logs. www.lumigent.com
> >
> >
> > "Mark" <maxmann@.kc.rr.com> wrote in message
> > news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> > > We believe we lost data yesterday in one of our tables, but only have
a
> > full
> > > backup from 7/12 and have transaction logs from 7/14. Is there
anything
> > we
> > > can do here? Don't you need the transaction logs from the last full
> > backup
> > > time in order to restore from the logs?
> > >
> > >
> > > Help!
> > >
> > >
> >
> >
>|||Hi Mark, as you state your gap is literary that you "missed a backup". This
doesnt mean that you have missing backup data -just less backups. please
refer to and follow my previous posts, there is no reason why you should not
be able to recover point in time of your choosing.
***REMEMBER TO TAKE A FINAL TRANSACTION LOG BACKUP BEFORE ATTEMPTING YOUR
RECOVER***
You should read BOL, section Administering SQL Server/ Backing up and
Restoring Databases/ Using Recovery Models/ Full Recovery
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Mark" <maxmann@.kc.rr.com> wrote in message
news:ethVT$8UDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Well, just to explain the situation a little further.
> 1. We are using Full Recover mode, which should be a good thing as far as
> I'm concerned.
> 2. The reason we were missing the two days of logs is because we have two
> jobs set in sql server.
> One to do a daily full backup and 1 to do hourly trans log backups,
> which is cool. The problem is
> that on the 12th, somehow the job to do the full backups go disabled,
> and we only have trans log backups
> on tape back to the 14th. So that's how the gap happened. It's not
> that we were doing trans log backups
> every two days. It was just the issue w/ that job being disabled.
> 3. We have contacted lumigent and they said since we have the db in full
> recovery mode that we should
> be able to completely restore the database table that got deleted
simply
> by using the transaction log
> that we have of when it happened.
> Thanks for all the help.
> Mark
>
> "Mark" <maxmann@.kc.rr.com> wrote in message
> news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> > We believe we lost data yesterday in one of our tables, but only have a
> full
> > backup from 7/12 and have transaction logs from 7/14. Is there anything
> we
> > can do here? Don't you need the transaction logs from the last full
> backup
> > time in order to restore from the logs?
> >
> >
> > Help!
> >
> >
>
backup from 7/12 and have transaction logs from 7/14. Is there anything we
can do here? Don't you need the transaction logs from the last full backup
time in order to restore from the logs?
Help!Yes, you should be able to apply those transaction logs to that full backup
since you have a break in the sequence.(please someone correct me if I'm
wrong). You don't have logs backed up to tape anywhere?
If not, then you might want to look into Lumigent's Log Explorer to examine
the transaction logs. www.lumigent.com
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||hi Mark. First you need to backup the transaction log (should be available
regardless of the state of the failed db) before you do anything else. Then
you can restore the db from the full specifying no recovery to allow you to
continue restoring. Finally you can restore the transaction logs with a
recovery so that transactions will be rolled forward or back and the db will
then become available.
Please refer to books online for this procedure
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||Yes and No.
Yes - If 7/14 logs are the immediate logs backed up after 7/12 full backup.
You can apply logs all the way up to the most recent backup.
No - You backed up logs two days later? Logs should be backed up much more
frequently. That's what log backup is meant. If at least one log backup was
missing between 7/12 and 7/14, then you can not use the 7/14 logs at all.
Richard
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||..agreed log backup should be performed at LEAST daily, however there should
be no reason why you should be missing any logs since your last full backup,
assuming of course you are not using the simple recovery model -in which
case you can kiss your logs goodbye.
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:eRtXVqsUDHA.1484@.TK2MSFTNGP12.phx.gbl...
> Yes and No.
> Yes - If 7/14 logs are the immediate logs backed up after 7/12 full
backup.
> You can apply logs all the way up to the most recent backup.
> No - You backed up logs two days later? Logs should be backed up much more
> frequently. That's what log backup is meant. If at least one log backup
was
> missing between 7/12 and 7/14, then you can not use the 7/14 logs at all.
>
> Richard
> "Mark" <maxmann@.kc.rr.com> wrote in message
> news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> > We believe we lost data yesterday in one of our tables, but only have a
> full
> > backup from 7/12 and have transaction logs from 7/14. Is there anything
> we
> > can do here? Don't you need the transaction logs from the last full
> backup
> > time in order to restore from the logs?
> >
> >
> > Help!
> >
> >
>|||Well, just to explain the situation a little further.
1. We are using Full Recover mode, which should be a good thing as far as
I'm concerned.
2. The reason we were missing the two days of logs is because we have two
jobs set in sql server.
One to do a daily full backup and 1 to do hourly trans log backups,
which is cool. The problem is
that on the 12th, somehow the job to do the full backups go disabled,
and we only have trans log backups
on tape back to the 14th. So that's how the gap happened. It's not
that we were doing trans log backups
every two days. It was just the issue w/ that job being disabled.
3. We have contacted lumigent and they said since we have the db in full
recovery mode that we should
be able to completely restore the database table that got deleted simply
by using the transaction log
that we have of when it happened.
Thanks for all the help.
Mark
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||James, firstly just because Mark states "full
backup from 7/12 and have transaction logs from 7/14" doesnt mean to suggest
that there is a break in the sequence only that he had a one day gap before
had did transaction log backups.
Secondly it doesnt matter whether he has got subsequent logs to tape or not
since the transaction log is *still* available for backup before he starts
the recovery. The Log Explorer tool (very good) is probably unnecessary in
this scenario).
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"James Luetkehoelter" <james.luetkehoetler@.NOSPAMspyglassllc.com> wrote in
message news:eEF4jHtUDHA.964@.TK2MSFTNGP09.phx.gbl...
> Er, this should have read "Shouldn't be able to apply". If there's a
break
> in the logs, you shouldn't be able to do a point-in-time recovery.
> "James Luetkehoelter" <james.luetkehoetler@.NOSPAMspyglassllc.com> wrote in
> message news:%23OK$bcsUDHA.1872@.TK2MSFTNGP12.phx.gbl...
> > Yes, you should be able to apply those transaction logs to that full
> backup
> > since you have a break in the sequence.(please someone correct me if I'm
> > wrong). You don't have logs backed up to tape anywhere?
> >
> > If not, then you might want to look into Lumigent's Log Explorer to
> examine
> > the transaction logs. www.lumigent.com
> >
> >
> > "Mark" <maxmann@.kc.rr.com> wrote in message
> > news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> > > We believe we lost data yesterday in one of our tables, but only have
a
> > full
> > > backup from 7/12 and have transaction logs from 7/14. Is there
anything
> > we
> > > can do here? Don't you need the transaction logs from the last full
> > backup
> > > time in order to restore from the logs?
> > >
> > >
> > > Help!
> > >
> > >
> >
> >
>|||Hi Mark, as you state your gap is literary that you "missed a backup". This
doesnt mean that you have missing backup data -just less backups. please
refer to and follow my previous posts, there is no reason why you should not
be able to recover point in time of your choosing.
***REMEMBER TO TAKE A FINAL TRANSACTION LOG BACKUP BEFORE ATTEMPTING YOUR
RECOVER***
You should read BOL, section Administering SQL Server/ Backing up and
Restoring Databases/ Using Recovery Models/ Full Recovery
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Mark" <maxmann@.kc.rr.com> wrote in message
news:ethVT$8UDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Well, just to explain the situation a little further.
> 1. We are using Full Recover mode, which should be a good thing as far as
> I'm concerned.
> 2. The reason we were missing the two days of logs is because we have two
> jobs set in sql server.
> One to do a daily full backup and 1 to do hourly trans log backups,
> which is cool. The problem is
> that on the 12th, somehow the job to do the full backups go disabled,
> and we only have trans log backups
> on tape back to the 14th. So that's how the gap happened. It's not
> that we were doing trans log backups
> every two days. It was just the issue w/ that job being disabled.
> 3. We have contacted lumigent and they said since we have the db in full
> recovery mode that we should
> be able to completely restore the database table that got deleted
simply
> by using the transaction log
> that we have of when it happened.
> Thanks for all the help.
> Mark
>
> "Mark" <maxmann@.kc.rr.com> wrote in message
> news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> > We believe we lost data yesterday in one of our tables, but only have a
> full
> > backup from 7/12 and have transaction logs from 7/14. Is there anything
> we
> > can do here? Don't you need the transaction logs from the last full
> backup
> > time in order to restore from the logs?
> >
> >
> > Help!
> >
> >
>
Resore all databases with the same logical filename in a loop
I have about 500 databases. They all have the same logical filenames
"application_db_data" and "application_db_log". I am moving them from
SQL 2000 to SQL 2005 on a new server, keeping all logical filenames.
I have a table that stores all database parameters needed for RESTORE:
database_name, backup_file_location, current_Data_logical_file,
current_Log_logical_file, new_Data_logical_file, new_Log_logical_file
As I said, current_Data_logical_file="application_db_data" for all DBs,
current_log_logical_file="application_db_log" for all DBs.
I created a SP called "PROC_RESTORE_ALL_DBS". Inside it there is a loop
through the table (using cursor) and run the following restore statement:
--
RESTORE DATABASE @.database_name
FROM DISK = backup_file_location
WITH
MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
MOVE @.current_Log_logical_file TO @.new_Log_logical_file,
REPLACE
--
Unfortunately, I can only restore One database (the first one) for each
run. There are error messages for the resore of the second database and
the ones after it:
--
Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
Logical file 'application_db_log' is not part of database 'App_DB1'. Use
RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
RESTORE DATABASE is terminating abnormally.
--
Why is this happening? Is it because the loop is running too fast and
the logical file is still being held? Why is the error on the
'application_db_log', not on 'application_db_data'?
Anyway to get around it? I am running the SP on SQL 2005.I'm not sure what the problem might be. I successfully restored multiple
databases using the cursor example below. In any case, you might find it
easier to generate and execute a single script using the SQL 2005
varchar(MAX):
--script example
DECLARE @.RestoreStatements nvarchar(MAX)
SET @.RestoreStatements = ''
SELECT
@.RestoreStatements = @.RestoreStatements + '
DATABASE [' + database_name + ']
FROM DISK=''' + backup_file_location + '''
WITH
MOVE ''' + current_Data_logical_file +
''' TO ' + '''' + new_Data_logical_file + ''',
MOVE ''' + current_Log_logical_file +
''' TO ''' + new_Log_logical_file + ''''
FROM DatabaseNames
EXEC sp_executesql @.RestoreStatements
GO
--cursor example
DECLARE
@.database_name sysname,
@.backup_file_location varchar(255),
@.current_Data_logical_file sysname,
@.current_Log_logical_file sysname,
@.new_Data_logical_file varchar(255),
@.new_Log_logical_file varchar(255)
DECLARE DatabaseNames
CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
database_name,
backup_file_location,
current_Data_logical_file,
current_Log_logical_file,
new_Data_logical_file,
new_Log_logical_file
FROM DatabaseNames
OPEN DatabaseNames
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DatabaseNames INTO
@.database_name,
@.backup_file_location,
@.current_Data_logical_file,
@.current_Log_logical_file,
@.new_Data_logical_file,
@.new_Log_logical_file
IF @.@.FETCH_STATUS = -1 BREAK
RESTORE DATABASE @.database_name
FROM DISK=@.backup_file_location
WITH
MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
MOVE @.current_Log_logical_file TO @.new_Log_logical_file
END
CLOSE DatabaseNames
DEALLOCATE DatabaseNames
--
Hope this helps.
Dan Guzman
SQL Server MVP
"q_test" <jluost1@.yahoo.com> wrote in message
news:443FC542.6050509@.yahoo.com...
>I have about 500 databases. They all have the same logical filenames
>"application_db_data" and "application_db_log". I am moving them from SQL
>2000 to SQL 2005 on a new server, keeping all logical filenames.
> I have a table that stores all database parameters needed for RESTORE:
> database_name, backup_file_location, current_Data_logical_file,
> current_Log_logical_file, new_Data_logical_file, new_Log_logical_file
> As I said, current_Data_logical_file="application_db_data" for all DBs,
> current_log_logical_file="application_db_log" for all DBs.
> I created a SP called "PROC_RESTORE_ALL_DBS". Inside it there is a loop
> through the table (using cursor) and run the following restore statement:
> --
> RESTORE DATABASE @.database_name
> FROM DISK = backup_file_location
> WITH
> MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> MOVE @.current_Log_logical_file TO @.new_Log_logical_file,
> REPLACE
> --
> Unfortunately, I can only restore One database (the first one) for each
> run. There are error messages for the resore of the second database and
> the ones after it:
> --
> Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
> Logical file 'application_db_log' is not part of database 'App_DB1'. Use
> RESTORE FILELISTONLY to list the logical file names.
> Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
> RESTORE DATABASE is terminating abnormally.
> --
> Why is this happening? Is it because the loop is running too fast and the
> logical file is still being held? Why is the error on the
> 'application_db_log', not on 'application_db_data'?
> Anyway to get around it? I am running the SP on SQL 2005.
>
>
>|||I am using almost exact code as you have laid out. Since you didn't have
any problem, maybe the problem is in my backup file.
So I ran the following as instructed in error message:
RESTORE FILELISTONLY from disk='D:\Database\Backup\App_DB1.bak'
and here is what I see:
there are two files:
LogicalName, Type, FileGroupName, FileSize, FileID, isPresent
application_db_Data, D, Primary, 232343, 1, 1
application_db_log, L, NULL, 34433, 2, 0
Note the the second file's isPresent=0. Is it causing the error of
RESTORE? Why isPresent=0 since size>0? How can I get around?
Thank you very much on this.
Dan Guzman wrote:
> I'm not sure what the problem might be. I successfully restored multiple
> databases using the cursor example below. In any case, you might find it
> easier to generate and execute a single script using the SQL 2005
> varchar(MAX):
> --script example
> DECLARE @.RestoreStatements nvarchar(MAX)
> SET @.RestoreStatements = ''
> SELECT
> @.RestoreStatements = @.RestoreStatements + '
> DATABASE [' + database_name + ']
> FROM DISK=''' + backup_file_location + '''
> WITH
> MOVE ''' + current_Data_logical_file +
> ''' TO ' + '''' + new_Data_logical_file + ''',
> MOVE ''' + current_Log_logical_file +
> ''' TO ''' + new_Log_logical_file + ''''
> FROM DatabaseNames
> EXEC sp_executesql @.RestoreStatements
> GO
> --cursor example
> DECLARE
> @.database_name sysname,
> @.backup_file_location varchar(255),
> @.current_Data_logical_file sysname,
> @.current_Log_logical_file sysname,
> @.new_Data_logical_file varchar(255),
> @.new_Log_logical_file varchar(255)
> DECLARE DatabaseNames
> CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT
> database_name,
> backup_file_location,
> current_Data_logical_file,
> current_Log_logical_file,
> new_Data_logical_file,
> new_Log_logical_file
> FROM DatabaseNames
> OPEN DatabaseNames
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DatabaseNames INTO
> @.database_name,
> @.backup_file_location,
> @.current_Data_logical_file,
> @.current_Log_logical_file,
> @.new_Data_logical_file,
> @.new_Log_logical_file
> IF @.@.FETCH_STATUS = -1 BREAK
> RESTORE DATABASE @.database_name
> FROM DISK=@.backup_file_location
> WITH
> MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> MOVE @.current_Log_logical_file TO @.new_Log_logical_file
> END
> CLOSE DatabaseNames
> DEALLOCATE DatabaseNames
>
"q_test" <jluost1@.yahoo.com> wrote in message
news:443FC542.6050509@.yahoo.com...
>I have about 500 databases. They all have the same logical filenames
>"application_db_data" and "application_db_log". I am moving them from SQL
>2000 to SQL 2005 on a new server, keeping all logical filenames.
>
> I have a table that stores all database parameters needed for RESTORE:
>
> database_name, backup_file_location, current_Data_logical_file,
> current_Log_logical_file, new_Data_logical_file, new_Log_logical_file
>
> As I said, current_Data_logical_file="application_db_data" for all DBs,
> current_log_logical_file="application_db_log" for all DBs.
>
> I created a SP called "PROC_RESTORE_ALL_DBS". Inside it there is a loop
> through the table (using cursor) and run the following restore statement:
>
> --
> RESTORE DATABASE @.database_name
> FROM DISK = backup_file_location
> WITH
> MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> MOVE @.current_Log_logical_file TO @.new_Log_logical_file,
> REPLACE
> --
>
> Unfortunately, I can only restore One database (the first one) for each
> run. There are error messages for the resore of the second database and
> the ones after it:
>
> --
> Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
> Logical file 'application_db_log' is not part of database 'App_DB1'. Use
> RESTORE FILELISTONLY to list the logical file names.
> Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
> RESTORE DATABASE is terminating abnormally.
> --
>
> Why is this happening? Is it because the loop is running too fast and
the
> logical file is still being held? Why is the error on the
> 'application_db_log', not on 'application_db_data'?
>
> Anyway to get around it? I am running the SP on SQL 2005.
>
>
>
>
>|||> Note the the second file's isPresent=0. Is it causing the error of
> RESTORE? Why isPresent=0 since size>0? How can I get around?
Was this a full backup that completed successfully? I haven't seen
IsPresent = 0 before.
In your initial post, you mentioned that you can successfully restore the
first backup from 'each run'. Does this mean that when you later skip the
one that restored successfully, the second database then restores
successfully but the subsequent ones fail?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"q_test" <jluost1@.yahoo.com> wrote in message
news:443FDD53.2090600@.yahoo.com...
>I am using almost exact code as you have laid out. Since you didn't have
>any problem, maybe the problem is in my backup file.
> So I ran the following as instructed in error message:
> RESTORE FILELISTONLY from disk='D:\Database\Backup\App_DB1.bak'
> and here is what I see:
> there are two files:
> LogicalName, Type, FileGroupName, FileSize, FileID, isPresent
> application_db_Data, D, Primary, 232343, 1, 1
> application_db_log, L, NULL, 34433, 2, 0
> Note the the second file's isPresent=0. Is it causing the error of
> RESTORE? Why isPresent=0 since size>0? How can I get around?
> Thank you very much on this.
> Dan Guzman wrote:
>> I'm not sure what the problem might be. I successfully restored multiple
>> databases using the cursor example below. In any case, you might find it
>> easier to generate and execute a single script using the SQL 2005
>> varchar(MAX):
>> --script example
>> DECLARE @.RestoreStatements nvarchar(MAX)
>> SET @.RestoreStatements = ''
>> SELECT
>> @.RestoreStatements = @.RestoreStatements + '
>> DATABASE [' + database_name + ']
>> FROM DISK=''' + backup_file_location + '''
>> WITH
>> MOVE ''' + current_Data_logical_file +
>> ''' TO ' + '''' + new_Data_logical_file + ''',
>> MOVE ''' + current_Log_logical_file +
>> ''' TO ''' + new_Log_logical_file + ''''
>> FROM DatabaseNames
>> EXEC sp_executesql @.RestoreStatements
>> GO
>> --cursor example
>> DECLARE
>> @.database_name sysname,
>> @.backup_file_location varchar(255),
>> @.current_Data_logical_file sysname,
>> @.current_Log_logical_file sysname,
>> @.new_Data_logical_file varchar(255),
>> @.new_Log_logical_file varchar(255)
>> DECLARE DatabaseNames
>> CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
>> SELECT
>> database_name,
>> backup_file_location,
>> current_Data_logical_file,
>> current_Log_logical_file,
>> new_Data_logical_file,
>> new_Log_logical_file
>> FROM DatabaseNames
>> OPEN DatabaseNames
>> WHILE 1 = 1
>> BEGIN
>> FETCH NEXT FROM DatabaseNames INTO
>> @.database_name,
>> @.backup_file_location,
>> @.current_Data_logical_file,
>> @.current_Log_logical_file,
>> @.new_Data_logical_file,
>> @.new_Log_logical_file
>> IF @.@.FETCH_STATUS = -1 BREAK
>> RESTORE DATABASE @.database_name
>> FROM DISK=@.backup_file_location
>> WITH
>> MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
>> MOVE @.current_Log_logical_file TO @.new_Log_logical_file
>> END
>> CLOSE DatabaseNames
>> DEALLOCATE DatabaseNames
> "q_test" <jluost1@.yahoo.com> wrote in message
> news:443FC542.6050509@.yahoo.com...
> >I have about 500 databases. They all have the same logical filenames
> >"application_db_data" and "application_db_log". I am moving them from SQL
> >2000 to SQL 2005 on a new server, keeping all logical filenames.
> >
> > I have a table that stores all database parameters needed for RESTORE:
> >
> > database_name, backup_file_location, current_Data_logical_file,
> > current_Log_logical_file, new_Data_logical_file, new_Log_logical_file
> >
> > As I said, current_Data_logical_file="application_db_data" for all DBs,
> > current_log_logical_file="application_db_log" for all DBs.
> >
> > I created a SP called "PROC_RESTORE_ALL_DBS". Inside it there is a loop
> > through the table (using cursor) and run the following restore
> > statement:
> >
> > --
> > RESTORE DATABASE @.database_name
> > FROM DISK = backup_file_location
> > WITH
> > MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> > MOVE @.current_Log_logical_file TO @.new_Log_logical_file,
> > REPLACE
> > --
> >
> > Unfortunately, I can only restore One database (the first one) for each
> > run. There are error messages for the resore of the second database and
> > the ones after it:
> >
> > --
> > Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
> > Logical file 'application_db_log' is not part of database 'App_DB1'. Use
> > RESTORE FILELISTONLY to list the logical file names.
> > Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
> > RESTORE DATABASE is terminating abnormally.
> > --
> >
> > Why is this happening? Is it because the loop is running too fast and
> the
> > logical file is still being held? Why is the error on the
> > 'application_db_log', not on 'application_db_data'?
> >
> > Anyway to get around it? I am running the SP on SQL 2005.
> >
> >
> >
> >
> >
>
>|||Yes, it is a complete backup. Yes, the first one is always restored
successfully but the subsequent ones failed.
In my table, if one is restored successfully, it is flagged. So the next
run will skip it.
I can get each one restored by run the SP 500 times. But I want it done
with one run...
From the error message I found that, starting from the second database,
it is trying to find the physical directory of the logical file on the
server, which doesn't exist any more. The logical file's physical
directory is like "C:\MSSQL$instance1\data\app_db1.mdb". In the new
server, it is like "C:\MSSQL05_instance1\data\app_db1.mdb".
A revised code is more like:
--
RESTORE DATABASE @.database_name
FROM DISK=@.backup_file_location
WITH
MOVE @.current_Data_logical_file TO @.new_Data_file_LOCATION,
MOVE @.current_Log_logical_file TO @.new_Log_file_LOCATION
END
--
Why is the old physical location used? And only from the second database?
> Note the the second file's isPresent=0. Is it causing the error of
> RESTORE? Why isPresent=0 since size>0? How can I get around?
Was this a full backup that completed successfully? I haven't seen
IsPresent = 0 before.
In your initial post, you mentioned that you can successfully restore the
first backup from 'each run'. Does this mean that when you later skip the
one that restored successfully, the second database then restores
successfully but the subsequent ones fail?
-- Hope this helps. Dan Guzman SQL Server MVP "q_test"
<jluost1@.yahoo.com> wrote in message news:443FDD53.2090600@.yahoo.com...
>I am using almost exact code as you have laid out. Since you didn't have
>any problem, maybe the problem is in my backup file.
>
> So I ran the following as instructed in error message:
>
> RESTORE FILELISTONLY from disk='D:\Database\Backup\App_DB1.bak'
>
> and here is what I see:
>
> there are two files:
>
> LogicalName, Type, FileGroupName, FileSize, FileID, isPresent
> application_db_Data, D, Primary, 232343, 1, 1
> application_db_log, L, NULL, 34433, 2, 0
>
> Note the the second file's isPresent=0. Is it causing the error of
> RESTORE? Why isPresent=0 since size>0? How can I get around?
>
> Thank you very much on this.
>
> Dan Guzman wrote:
>> I'm not sure what the problem might be. I successfully restored
multiple
>> databases using the cursor example below. In any case, you might
find it
>> easier to generate and execute a single script using the SQL 2005
>> varchar(MAX):
>>
>> --script example
>> DECLARE @.RestoreStatements nvarchar(MAX)
>> SET @.RestoreStatements = ''
>> SELECT
>> @.RestoreStatements = @.RestoreStatements + '
>> DATABASE [' + database_name + ']
>> FROM DISK=''' + backup_file_location + '''
>> WITH
>> MOVE ''' + current_Data_logical_file +
>> ''' TO ' + '''' + new_Data_logical_file + ''',
>> MOVE ''' + current_Log_logical_file +
>> ''' TO ''' + new_Log_logical_file + ''''
>> FROM DatabaseNames
>> EXEC sp_executesql @.RestoreStatements
>> GO
>>
>> --cursor example
>> DECLARE
>> @.database_name sysname,
>> @.backup_file_location varchar(255),
>> @.current_Data_logical_file sysname,
>> @.current_Log_logical_file sysname,
>> @.new_Data_logical_file varchar(255),
>> @.new_Log_logical_file varchar(255)
>>
>> DECLARE DatabaseNames
>> CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
>> SELECT
>> database_name,
>> backup_file_location,
>> current_Data_logical_file,
>> current_Log_logical_file,
>> new_Data_logical_file,
>> new_Log_logical_file
>> FROM DatabaseNames
>> OPEN DatabaseNames
>> WHILE 1 = 1
>> BEGIN
>> FETCH NEXT FROM DatabaseNames INTO
>> @.database_name,
>> @.backup_file_location,
>> @.current_Data_logical_file,
>> @.current_Log_logical_file,
>> @.new_Data_logical_file,
>> @.new_Log_logical_file
>> IF @.@.FETCH_STATUS = -1 BREAK
>> RESTORE DATABASE @.database_name
>> FROM DISK=@.backup_file_location
>> WITH
>> MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
>> MOVE @.current_Log_logical_file TO @.new_Log_logical_file
>> END
>> CLOSE DatabaseNames
>> DEALLOCATE DatabaseNames
>>
> "q_test" <jluost1@.yahoo.com> wrote in message
> news:443FC542.6050509@.yahoo.com...
>
> >I have about 500 databases. They all have the same logical filenames
> >"application_db_data" and "application_db_log". I am moving them
from SQL
> >2000 to SQL 2005 on a new server, keeping all logical filenames.
> >
> > I have a table that stores all database parameters needed for RESTORE:
> >
> > database_name, backup_file_location, current_Data_logical_file,
> > current_Log_logical_file, new_Data_logical_file, new_Log_logical_file
> >
> > As I said, current_Data_logical_file="application_db_data" for all DBs,
> > current_log_logical_file="application_db_log" for all DBs.
> >
> > I created a SP called "PROC_RESTORE_ALL_DBS". Inside it there is a loop
> > through the table (using cursor) and run the following restore
> > statement:
> >
> > --
> > RESTORE DATABASE @.database_name
> > FROM DISK = backup_file_location
> > WITH
> > MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> > MOVE @.current_Log_logical_file TO @.new_Log_logical_file,
> > REPLACE
> > --
> >
> > Unfortunately, I can only restore One database (the first one) for each
> > run. There are error messages for the resore of the second database and
> > the ones after it:
> >
> > --
> > Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
> > Logical file 'application_db_log' is not part of database
'App_DB1'. Use
> > RESTORE FILELISTONLY to list the logical file names.
> > Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
> > RESTORE DATABASE is terminating abnormally.
> > --
> >
> > Why is this happening? Is it because the loop is running too fast and
> the
> > logical file is still being held? Why is the error on the
> > 'application_db_log', not on 'application_db_data'?
> >
> > Anyway to get around it? I am running the SP on SQL 2005.
> >
> >
> >
> >
> >
>
>
>|||It seems like SQL Server isn't recognizing the 'MOVE' and/or logical file
names on the subsequent restores. Can you post your actual script? I'd
like to try to reproduce your problem.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"q_test" <jluost1@.yahoo.com> wrote in message
news:443FEB36.2090408@.yahoo.com...
> Yes, it is a complete backup. Yes, the first one is always restored
> successfully but the subsequent ones failed.
> In my table, if one is restored successfully, it is flagged. So the next
> run will skip it.
> I can get each one restored by run the SP 500 times. But I want it done
> with one run...
> From the error message I found that, starting from the second database, it
> is trying to find the physical directory of the logical file on the
> server, which doesn't exist any more. The logical file's physical
> directory is like "C:\MSSQL$instance1\data\app_db1.mdb". In the new
> server, it is like "C:\MSSQL05_instance1\data\app_db1.mdb".
> A revised code is more like:
> --
> RESTORE DATABASE @.database_name
> FROM DISK=@.backup_file_location
> WITH
> MOVE @.current_Data_logical_file TO @.new_Data_file_LOCATION,
> MOVE @.current_Log_logical_file TO @.new_Log_file_LOCATION
> END
> --
> Why is the old physical location used? And only from the second database?
>
> > Note the the second file's isPresent=0. Is it causing the error of
> > RESTORE? Why isPresent=0 since size>0? How can I get around?
>
> Was this a full backup that completed successfully? I haven't seen
> IsPresent = 0 before.
> In your initial post, you mentioned that you can successfully restore the
> first backup from 'each run'. Does this mean that when you later skip the
> one that restored successfully, the second database then restores
> successfully but the subsequent ones fail?
> -- Hope this helps. Dan Guzman SQL Server MVP "q_test" <jluost1@.yahoo.com>
> wrote in message news:443FDD53.2090600@.yahoo.com...
> >I am using almost exact code as you have laid out. Since you didn't have
> >any problem, maybe the problem is in my backup file.
> >
> > So I ran the following as instructed in error message:
> >
> > RESTORE FILELISTONLY from disk='D:\Database\Backup\App_DB1.bak'
> >
> > and here is what I see:
> >
> > there are two files:
> >
> > LogicalName, Type, FileGroupName, FileSize, FileID, isPresent
> > application_db_Data, D, Primary, 232343, 1, 1
> > application_db_log, L, NULL, 34433, 2, 0
> >
> > Note the the second file's isPresent=0. Is it causing the error of
> > RESTORE? Why isPresent=0 since size>0? How can I get around?
> >
> > Thank you very much on this.
> >
> > Dan Guzman wrote:
> >> I'm not sure what the problem might be. I successfully restored
> multiple
> >> databases using the cursor example below. In any case, you might
> find it
> >> easier to generate and execute a single script using the SQL 2005
> >> varchar(MAX):
> >>
> >> --script example
> >> DECLARE @.RestoreStatements nvarchar(MAX)
> >> SET @.RestoreStatements = ''
> >> SELECT
> >> @.RestoreStatements = @.RestoreStatements + '
> >> DATABASE [' + database_name + ']
> >> FROM DISK=''' + backup_file_location + '''
> >> WITH
> >> MOVE ''' + current_Data_logical_file +
> >> ''' TO ' + '''' + new_Data_logical_file + ''',
> >> MOVE ''' + current_Log_logical_file +
> >> ''' TO ''' + new_Log_logical_file + ''''
> >> FROM DatabaseNames
> >> EXEC sp_executesql @.RestoreStatements
> >> GO
> >>
> >> --cursor example
> >> DECLARE
> >> @.database_name sysname,
> >> @.backup_file_location varchar(255),
> >> @.current_Data_logical_file sysname,
> >> @.current_Log_logical_file sysname,
> >> @.new_Data_logical_file varchar(255),
> >> @.new_Log_logical_file varchar(255)
> >>
> >> DECLARE DatabaseNames
> >> CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
> >> SELECT
> >> database_name,
> >> backup_file_location,
> >> current_Data_logical_file,
> >> current_Log_logical_file,
> >> new_Data_logical_file,
> >> new_Log_logical_file
> >> FROM DatabaseNames
> >> OPEN DatabaseNames
> >> WHILE 1 = 1
> >> BEGIN
> >> FETCH NEXT FROM DatabaseNames INTO
> >> @.database_name,
> >> @.backup_file_location,
> >> @.current_Data_logical_file,
> >> @.current_Log_logical_file,
> >> @.new_Data_logical_file,
> >> @.new_Log_logical_file
> >> IF @.@.FETCH_STATUS = -1 BREAK
> >> RESTORE DATABASE @.database_name
> >> FROM DISK=@.backup_file_location
> >> WITH
> >> MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> >> MOVE @.current_Log_logical_file TO @.new_Log_logical_file
> >> END
> >> CLOSE DatabaseNames
> >> DEALLOCATE DatabaseNames
> >>
> > "q_test" <jluost1@.yahoo.com> wrote in message
> > news:443FC542.6050509@.yahoo.com...
> >
> > >I have about 500 databases. They all have the same logical filenames
> > >"application_db_data" and "application_db_log". I am moving them
> from SQL
> > >2000 to SQL 2005 on a new server, keeping all logical filenames.
> > >
> > > I have a table that stores all database parameters needed for RESTORE:
> > >
> > > database_name, backup_file_location, current_Data_logical_file,
> > > current_Log_logical_file, new_Data_logical_file, new_Log_logical_file
> > >
> > > As I said, current_Data_logical_file="application_db_data" for all
> > > DBs,
> > > current_log_logical_file="application_db_log" for all DBs.
> > >
> > > I created a SP called "PROC_RESTORE_ALL_DBS". Inside it there is a
> > > loop
> > > through the table (using cursor) and run the following restore
> > > statement:
> > >
> > > --
> > > RESTORE DATABASE @.database_name
> > > FROM DISK = backup_file_location
> > > WITH
> > > MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> > > MOVE @.current_Log_logical_file TO @.new_Log_logical_file,
> > > REPLACE
> > > --
> > >
> > > Unfortunately, I can only restore One database (the first one) for
> > > each
> > > run. There are error messages for the resore of the second database
> > > and
> > > the ones after it:
> > >
> > > --
> > > Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
> > > Logical file 'application_db_log' is not part of database
> 'App_DB1'. Use
> > > RESTORE FILELISTONLY to list the logical file names.
> > > Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
> > > RESTORE DATABASE is terminating abnormally.
> > > --
> > >
> > > Why is this happening? Is it because the loop is running too fast and
> > the
> > > logical file is still being held? Why is the error on the
> > > 'application_db_log', not on 'application_db_data'?
> > >
> > > Anyway to get around it? I am running the SP on SQL 2005.
> > >
> > >
> > >
> > >
> > >
> >
> >
> >
>
>|||Here is the script for SP, table, insert data and exec SP statements:
--
CREATE PROCEDURE PROC_RESTORE_ALL_DBS
AS
/*
Purpose: upgrate from SQL 2000 to SQL 2005 by restoring
*/
set nocount on
declare @.oneWebDB varchar(75)
declare @.datapath varchar(100)
declare @.logicalDataFileName varchar(100),@.logicalLogFileName varchar(100)
declare @.DestPath varchar(100)
DECLARE @.err int
declare @.updQ varchar(300)
declare @.back_db_name varchar(75),@.diskname varchar(75)
declare @.full_bk_data_file varchar(75), @.full_bk_log_file varchar(75),
@.full_data_file varchar(75), @.full_log_file varchar(75),
@.logicalFileName varchar(100)
set @.datapath = 'D:\Migration\Backup\DEFAULT\'
set @.DestPath = 'D:\MSSQL05_DEFAULT\'
declare all_web_db cursor
LOCAL
READ_ONLY
for
select db_name_used
,logicalDataFileName
,logicalLogFileName
from migration_table_info
where is_not_active=0
and is_migrated=0
order by db_name_used
open all_web_db
fetch next from all_web_db
into @.oneWebDB,@.logicalDataFileName,@.logicalLogFileName
while @.@.FETCH_STATUS = 0 begin
begin
print '-- Restore database ' + @.oneWebDB + '
--'
set @.back_db_name = @.oneWebDB + '.bak'
set @.diskname = @.datapath +@.back_db_name
set @.full_bk_data_file =@.logicalDataFileName
set @.full_bk_log_file = @.logicalLogFileName
set @.full_data_file = @.DestPath + 'data\' + @.oneWebDB + '_data.mdf'
set @.full_log_file = @.DestPath + 'Log\' + @.oneWebDB + '_log.ldf'
print ' ' + @.diskname + ' '
print ' ' + @.full_data_file + ' '
print ' ' + @.full_log_file + ' '
--RESTORE FILELISTONLY
--FROM DISK = @.diskname
RESTORE DATABASE @.oneWebDB
FROM DISK = @.diskname
WITH
MOVE @.full_bk_data_file TO @.full_data_file,
MOVE @.full_bk_log_file TO @.full_log_file
--,REPLACE
-- update [migration_table_info] is_migrated=1
SELECT @.err = @.@.error
IF (@.err = 0)
BEGIN
set @.updQ = 'UPDATE migration_table_info SET is_migrated=1 WHERE
db_name_used = ''' + @.oneWebDB + ''''
execute(@.updQ)
END
end
fetch next from all_web_db into
@.oneWebDB,@.logicalDataFileName,@.logicalDataFileName
end
close all_web_db
deallocate all_web_db
set nocount off
return 1
GO
--
Here is the script for database table:
--
CREATE TABLE migration_table_info (
db_name_used varchar(75) primary key,
logicalDataFileName varchar(100) null,
logicalLogFileName varchar(100) null,
is_not_active bit null,
is_migrated bit null
)
--
Here is two example of database info:
--
INSERT INTO migration_table_info VALUES ('m7E','app_Data','app_Log',0,0)
INSERT INTO migration_table_info VALUES ('m7F','app_Data','app_Log',0,0)
--
Use the following to run the SP:
--
exec PROC_RESTORE_ALL_DBS
--
Hope that helps...
Dan Guzman wrote:
> It seems like SQL Server isn't recognizing the 'MOVE' and/or logical file
> names on the subsequent restores. Can you post your actual script? I'd
> like to try to reproduce your problem.
>|||I created 100 SQL 2000 databases, backup these up and then ran your script
under SQL 2005 SP1 CTP. I changed only the folder names and database names.
All 100 databases restored without problems.
From your initial post:
--
Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
Logical file 'application_db_log' is not part of database 'App_DB1'. Use
RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
RESTORE DATABASE is terminating abnormally.
--
And in a later post:
---
From the error message I found that, starting from the second database,
it is trying to find the physical directory of the logical file on the
server, which doesn't exist any more. The logical file's physical
directory is like "C:\MSSQL$instance1\data\app_db1.mdb". In the new
server, it is like "C:\MSSQL05_instance1\data\app_db1.mdb".
---
Why do you say that subsequent restores are looking for the physical
directory of the original server? I don't see that in the error you posted.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"q_test" <jluost1@.yahoo.com> wrote in message
news:443FF33D.70204@.yahoo.com...
> Here is the script for SP, table, insert data and exec SP statements:
>|||Dan Guzman,
You asked the right question. I looked at my code again and found a very
stupid mistake on the line below:
--
fetch next from all_web_db into
@.oneWebDB,@.logicalDataFileName,@.logicalDataFileName
--
The last one should be: @.logicalLogFileName instead of @.logicalDataFileName.
It is working fine now.
Thank you for all of your time and effort. I really really appreciate it.
Dan Guzman wrote:
> I created 100 SQL 2000 databases, backup these up and then ran your script
> under SQL 2005 SP1 CTP. I changed only the folder names and database names.
> All 100 databases restored without problems.
> From your initial post:
> --
> Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
> Logical file 'application_db_log' is not part of database 'App_DB1'. Use
> RESTORE FILELISTONLY to list the logical file names.
> Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
> RESTORE DATABASE is terminating abnormally.
> --
> And in a later post:
> ---
> From the error message I found that, starting from the second database,
> it is trying to find the physical directory of the logical file on the
> server, which doesn't exist any more. The logical file's physical
> directory is like "C:\MSSQL$instance1\data\app_db1.mdb". In the new
> server, it is like "C:\MSSQL05_instance1\data\app_db1.mdb".
> ---
> Why do you say that subsequent restores are looking for the physical
> directory of the original server? I don't see that in the error you posted.
>|||I'm glad you were able to get it sorted out.
--
Dan Guzman
SQL Server MVP
"q_test" <jluost1@.yahoo.com> wrote in message
news:4443A02D.8040704@.yahoo.com...
> Dan Guzman,
> You asked the right question. I looked at my code again and found a very
> stupid mistake on the line below:
> --
> fetch next from all_web_db into
> @.oneWebDB,@.logicalDataFileName,@.logicalDataFileName
> --
> The last one should be: @.logicalLogFileName instead of
> @.logicalDataFileName.
> It is working fine now.
> Thank you for all of your time and effort. I really really appreciate it.
> Dan Guzman wrote:
>> I created 100 SQL 2000 databases, backup these up and then ran your
>> script under SQL 2005 SP1 CTP. I changed only the folder names and
>> database names. All 100 databases restored without problems.
>> From your initial post:
>> --
>> Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
>> Logical file 'application_db_log' is not part of database 'App_DB1'. Use
>> RESTORE FILELISTONLY to list the logical file names.
>> Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
>> RESTORE DATABASE is terminating abnormally.
>> --
>> And in a later post:
>> ---
>> From the error message I found that, starting from the second database,
>> it is trying to find the physical directory of the logical file on the
>> server, which doesn't exist any more. The logical file's physical
>> directory is like "C:\MSSQL$instance1\data\app_db1.mdb". In the new
>> server, it is like "C:\MSSQL05_instance1\data\app_db1.mdb".
>> ---
>> Why do you say that subsequent restores are looking for the physical
>> directory of the original server? I don't see that in the error you
>> posted.
>sql
"application_db_data" and "application_db_log". I am moving them from
SQL 2000 to SQL 2005 on a new server, keeping all logical filenames.
I have a table that stores all database parameters needed for RESTORE:
database_name, backup_file_location, current_Data_logical_file,
current_Log_logical_file, new_Data_logical_file, new_Log_logical_file
As I said, current_Data_logical_file="application_db_data" for all DBs,
current_log_logical_file="application_db_log" for all DBs.
I created a SP called "PROC_RESTORE_ALL_DBS". Inside it there is a loop
through the table (using cursor) and run the following restore statement:
--
RESTORE DATABASE @.database_name
FROM DISK = backup_file_location
WITH
MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
MOVE @.current_Log_logical_file TO @.new_Log_logical_file,
REPLACE
--
Unfortunately, I can only restore One database (the first one) for each
run. There are error messages for the resore of the second database and
the ones after it:
--
Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
Logical file 'application_db_log' is not part of database 'App_DB1'. Use
RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
RESTORE DATABASE is terminating abnormally.
--
Why is this happening? Is it because the loop is running too fast and
the logical file is still being held? Why is the error on the
'application_db_log', not on 'application_db_data'?
Anyway to get around it? I am running the SP on SQL 2005.I'm not sure what the problem might be. I successfully restored multiple
databases using the cursor example below. In any case, you might find it
easier to generate and execute a single script using the SQL 2005
varchar(MAX):
--script example
DECLARE @.RestoreStatements nvarchar(MAX)
SET @.RestoreStatements = ''
SELECT
@.RestoreStatements = @.RestoreStatements + '
DATABASE [' + database_name + ']
FROM DISK=''' + backup_file_location + '''
WITH
MOVE ''' + current_Data_logical_file +
''' TO ' + '''' + new_Data_logical_file + ''',
MOVE ''' + current_Log_logical_file +
''' TO ''' + new_Log_logical_file + ''''
FROM DatabaseNames
EXEC sp_executesql @.RestoreStatements
GO
--cursor example
DECLARE
@.database_name sysname,
@.backup_file_location varchar(255),
@.current_Data_logical_file sysname,
@.current_Log_logical_file sysname,
@.new_Data_logical_file varchar(255),
@.new_Log_logical_file varchar(255)
DECLARE DatabaseNames
CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
database_name,
backup_file_location,
current_Data_logical_file,
current_Log_logical_file,
new_Data_logical_file,
new_Log_logical_file
FROM DatabaseNames
OPEN DatabaseNames
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DatabaseNames INTO
@.database_name,
@.backup_file_location,
@.current_Data_logical_file,
@.current_Log_logical_file,
@.new_Data_logical_file,
@.new_Log_logical_file
IF @.@.FETCH_STATUS = -1 BREAK
RESTORE DATABASE @.database_name
FROM DISK=@.backup_file_location
WITH
MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
MOVE @.current_Log_logical_file TO @.new_Log_logical_file
END
CLOSE DatabaseNames
DEALLOCATE DatabaseNames
--
Hope this helps.
Dan Guzman
SQL Server MVP
"q_test" <jluost1@.yahoo.com> wrote in message
news:443FC542.6050509@.yahoo.com...
>I have about 500 databases. They all have the same logical filenames
>"application_db_data" and "application_db_log". I am moving them from SQL
>2000 to SQL 2005 on a new server, keeping all logical filenames.
> I have a table that stores all database parameters needed for RESTORE:
> database_name, backup_file_location, current_Data_logical_file,
> current_Log_logical_file, new_Data_logical_file, new_Log_logical_file
> As I said, current_Data_logical_file="application_db_data" for all DBs,
> current_log_logical_file="application_db_log" for all DBs.
> I created a SP called "PROC_RESTORE_ALL_DBS". Inside it there is a loop
> through the table (using cursor) and run the following restore statement:
> --
> RESTORE DATABASE @.database_name
> FROM DISK = backup_file_location
> WITH
> MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> MOVE @.current_Log_logical_file TO @.new_Log_logical_file,
> REPLACE
> --
> Unfortunately, I can only restore One database (the first one) for each
> run. There are error messages for the resore of the second database and
> the ones after it:
> --
> Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
> Logical file 'application_db_log' is not part of database 'App_DB1'. Use
> RESTORE FILELISTONLY to list the logical file names.
> Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
> RESTORE DATABASE is terminating abnormally.
> --
> Why is this happening? Is it because the loop is running too fast and the
> logical file is still being held? Why is the error on the
> 'application_db_log', not on 'application_db_data'?
> Anyway to get around it? I am running the SP on SQL 2005.
>
>
>|||I am using almost exact code as you have laid out. Since you didn't have
any problem, maybe the problem is in my backup file.
So I ran the following as instructed in error message:
RESTORE FILELISTONLY from disk='D:\Database\Backup\App_DB1.bak'
and here is what I see:
there are two files:
LogicalName, Type, FileGroupName, FileSize, FileID, isPresent
application_db_Data, D, Primary, 232343, 1, 1
application_db_log, L, NULL, 34433, 2, 0
Note the the second file's isPresent=0. Is it causing the error of
RESTORE? Why isPresent=0 since size>0? How can I get around?
Thank you very much on this.
Dan Guzman wrote:
> I'm not sure what the problem might be. I successfully restored multiple
> databases using the cursor example below. In any case, you might find it
> easier to generate and execute a single script using the SQL 2005
> varchar(MAX):
> --script example
> DECLARE @.RestoreStatements nvarchar(MAX)
> SET @.RestoreStatements = ''
> SELECT
> @.RestoreStatements = @.RestoreStatements + '
> DATABASE [' + database_name + ']
> FROM DISK=''' + backup_file_location + '''
> WITH
> MOVE ''' + current_Data_logical_file +
> ''' TO ' + '''' + new_Data_logical_file + ''',
> MOVE ''' + current_Log_logical_file +
> ''' TO ''' + new_Log_logical_file + ''''
> FROM DatabaseNames
> EXEC sp_executesql @.RestoreStatements
> GO
> --cursor example
> DECLARE
> @.database_name sysname,
> @.backup_file_location varchar(255),
> @.current_Data_logical_file sysname,
> @.current_Log_logical_file sysname,
> @.new_Data_logical_file varchar(255),
> @.new_Log_logical_file varchar(255)
> DECLARE DatabaseNames
> CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT
> database_name,
> backup_file_location,
> current_Data_logical_file,
> current_Log_logical_file,
> new_Data_logical_file,
> new_Log_logical_file
> FROM DatabaseNames
> OPEN DatabaseNames
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DatabaseNames INTO
> @.database_name,
> @.backup_file_location,
> @.current_Data_logical_file,
> @.current_Log_logical_file,
> @.new_Data_logical_file,
> @.new_Log_logical_file
> IF @.@.FETCH_STATUS = -1 BREAK
> RESTORE DATABASE @.database_name
> FROM DISK=@.backup_file_location
> WITH
> MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> MOVE @.current_Log_logical_file TO @.new_Log_logical_file
> END
> CLOSE DatabaseNames
> DEALLOCATE DatabaseNames
>
"q_test" <jluost1@.yahoo.com> wrote in message
news:443FC542.6050509@.yahoo.com...
>I have about 500 databases. They all have the same logical filenames
>"application_db_data" and "application_db_log". I am moving them from SQL
>2000 to SQL 2005 on a new server, keeping all logical filenames.
>
> I have a table that stores all database parameters needed for RESTORE:
>
> database_name, backup_file_location, current_Data_logical_file,
> current_Log_logical_file, new_Data_logical_file, new_Log_logical_file
>
> As I said, current_Data_logical_file="application_db_data" for all DBs,
> current_log_logical_file="application_db_log" for all DBs.
>
> I created a SP called "PROC_RESTORE_ALL_DBS". Inside it there is a loop
> through the table (using cursor) and run the following restore statement:
>
> --
> RESTORE DATABASE @.database_name
> FROM DISK = backup_file_location
> WITH
> MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> MOVE @.current_Log_logical_file TO @.new_Log_logical_file,
> REPLACE
> --
>
> Unfortunately, I can only restore One database (the first one) for each
> run. There are error messages for the resore of the second database and
> the ones after it:
>
> --
> Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
> Logical file 'application_db_log' is not part of database 'App_DB1'. Use
> RESTORE FILELISTONLY to list the logical file names.
> Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
> RESTORE DATABASE is terminating abnormally.
> --
>
> Why is this happening? Is it because the loop is running too fast and
the
> logical file is still being held? Why is the error on the
> 'application_db_log', not on 'application_db_data'?
>
> Anyway to get around it? I am running the SP on SQL 2005.
>
>
>
>
>|||> Note the the second file's isPresent=0. Is it causing the error of
> RESTORE? Why isPresent=0 since size>0? How can I get around?
Was this a full backup that completed successfully? I haven't seen
IsPresent = 0 before.
In your initial post, you mentioned that you can successfully restore the
first backup from 'each run'. Does this mean that when you later skip the
one that restored successfully, the second database then restores
successfully but the subsequent ones fail?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"q_test" <jluost1@.yahoo.com> wrote in message
news:443FDD53.2090600@.yahoo.com...
>I am using almost exact code as you have laid out. Since you didn't have
>any problem, maybe the problem is in my backup file.
> So I ran the following as instructed in error message:
> RESTORE FILELISTONLY from disk='D:\Database\Backup\App_DB1.bak'
> and here is what I see:
> there are two files:
> LogicalName, Type, FileGroupName, FileSize, FileID, isPresent
> application_db_Data, D, Primary, 232343, 1, 1
> application_db_log, L, NULL, 34433, 2, 0
> Note the the second file's isPresent=0. Is it causing the error of
> RESTORE? Why isPresent=0 since size>0? How can I get around?
> Thank you very much on this.
> Dan Guzman wrote:
>> I'm not sure what the problem might be. I successfully restored multiple
>> databases using the cursor example below. In any case, you might find it
>> easier to generate and execute a single script using the SQL 2005
>> varchar(MAX):
>> --script example
>> DECLARE @.RestoreStatements nvarchar(MAX)
>> SET @.RestoreStatements = ''
>> SELECT
>> @.RestoreStatements = @.RestoreStatements + '
>> DATABASE [' + database_name + ']
>> FROM DISK=''' + backup_file_location + '''
>> WITH
>> MOVE ''' + current_Data_logical_file +
>> ''' TO ' + '''' + new_Data_logical_file + ''',
>> MOVE ''' + current_Log_logical_file +
>> ''' TO ''' + new_Log_logical_file + ''''
>> FROM DatabaseNames
>> EXEC sp_executesql @.RestoreStatements
>> GO
>> --cursor example
>> DECLARE
>> @.database_name sysname,
>> @.backup_file_location varchar(255),
>> @.current_Data_logical_file sysname,
>> @.current_Log_logical_file sysname,
>> @.new_Data_logical_file varchar(255),
>> @.new_Log_logical_file varchar(255)
>> DECLARE DatabaseNames
>> CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
>> SELECT
>> database_name,
>> backup_file_location,
>> current_Data_logical_file,
>> current_Log_logical_file,
>> new_Data_logical_file,
>> new_Log_logical_file
>> FROM DatabaseNames
>> OPEN DatabaseNames
>> WHILE 1 = 1
>> BEGIN
>> FETCH NEXT FROM DatabaseNames INTO
>> @.database_name,
>> @.backup_file_location,
>> @.current_Data_logical_file,
>> @.current_Log_logical_file,
>> @.new_Data_logical_file,
>> @.new_Log_logical_file
>> IF @.@.FETCH_STATUS = -1 BREAK
>> RESTORE DATABASE @.database_name
>> FROM DISK=@.backup_file_location
>> WITH
>> MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
>> MOVE @.current_Log_logical_file TO @.new_Log_logical_file
>> END
>> CLOSE DatabaseNames
>> DEALLOCATE DatabaseNames
> "q_test" <jluost1@.yahoo.com> wrote in message
> news:443FC542.6050509@.yahoo.com...
> >I have about 500 databases. They all have the same logical filenames
> >"application_db_data" and "application_db_log". I am moving them from SQL
> >2000 to SQL 2005 on a new server, keeping all logical filenames.
> >
> > I have a table that stores all database parameters needed for RESTORE:
> >
> > database_name, backup_file_location, current_Data_logical_file,
> > current_Log_logical_file, new_Data_logical_file, new_Log_logical_file
> >
> > As I said, current_Data_logical_file="application_db_data" for all DBs,
> > current_log_logical_file="application_db_log" for all DBs.
> >
> > I created a SP called "PROC_RESTORE_ALL_DBS". Inside it there is a loop
> > through the table (using cursor) and run the following restore
> > statement:
> >
> > --
> > RESTORE DATABASE @.database_name
> > FROM DISK = backup_file_location
> > WITH
> > MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> > MOVE @.current_Log_logical_file TO @.new_Log_logical_file,
> > REPLACE
> > --
> >
> > Unfortunately, I can only restore One database (the first one) for each
> > run. There are error messages for the resore of the second database and
> > the ones after it:
> >
> > --
> > Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
> > Logical file 'application_db_log' is not part of database 'App_DB1'. Use
> > RESTORE FILELISTONLY to list the logical file names.
> > Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
> > RESTORE DATABASE is terminating abnormally.
> > --
> >
> > Why is this happening? Is it because the loop is running too fast and
> the
> > logical file is still being held? Why is the error on the
> > 'application_db_log', not on 'application_db_data'?
> >
> > Anyway to get around it? I am running the SP on SQL 2005.
> >
> >
> >
> >
> >
>
>|||Yes, it is a complete backup. Yes, the first one is always restored
successfully but the subsequent ones failed.
In my table, if one is restored successfully, it is flagged. So the next
run will skip it.
I can get each one restored by run the SP 500 times. But I want it done
with one run...
From the error message I found that, starting from the second database,
it is trying to find the physical directory of the logical file on the
server, which doesn't exist any more. The logical file's physical
directory is like "C:\MSSQL$instance1\data\app_db1.mdb". In the new
server, it is like "C:\MSSQL05_instance1\data\app_db1.mdb".
A revised code is more like:
--
RESTORE DATABASE @.database_name
FROM DISK=@.backup_file_location
WITH
MOVE @.current_Data_logical_file TO @.new_Data_file_LOCATION,
MOVE @.current_Log_logical_file TO @.new_Log_file_LOCATION
END
--
Why is the old physical location used? And only from the second database?
> Note the the second file's isPresent=0. Is it causing the error of
> RESTORE? Why isPresent=0 since size>0? How can I get around?
Was this a full backup that completed successfully? I haven't seen
IsPresent = 0 before.
In your initial post, you mentioned that you can successfully restore the
first backup from 'each run'. Does this mean that when you later skip the
one that restored successfully, the second database then restores
successfully but the subsequent ones fail?
-- Hope this helps. Dan Guzman SQL Server MVP "q_test"
<jluost1@.yahoo.com> wrote in message news:443FDD53.2090600@.yahoo.com...
>I am using almost exact code as you have laid out. Since you didn't have
>any problem, maybe the problem is in my backup file.
>
> So I ran the following as instructed in error message:
>
> RESTORE FILELISTONLY from disk='D:\Database\Backup\App_DB1.bak'
>
> and here is what I see:
>
> there are two files:
>
> LogicalName, Type, FileGroupName, FileSize, FileID, isPresent
> application_db_Data, D, Primary, 232343, 1, 1
> application_db_log, L, NULL, 34433, 2, 0
>
> Note the the second file's isPresent=0. Is it causing the error of
> RESTORE? Why isPresent=0 since size>0? How can I get around?
>
> Thank you very much on this.
>
> Dan Guzman wrote:
>> I'm not sure what the problem might be. I successfully restored
multiple
>> databases using the cursor example below. In any case, you might
find it
>> easier to generate and execute a single script using the SQL 2005
>> varchar(MAX):
>>
>> --script example
>> DECLARE @.RestoreStatements nvarchar(MAX)
>> SET @.RestoreStatements = ''
>> SELECT
>> @.RestoreStatements = @.RestoreStatements + '
>> DATABASE [' + database_name + ']
>> FROM DISK=''' + backup_file_location + '''
>> WITH
>> MOVE ''' + current_Data_logical_file +
>> ''' TO ' + '''' + new_Data_logical_file + ''',
>> MOVE ''' + current_Log_logical_file +
>> ''' TO ''' + new_Log_logical_file + ''''
>> FROM DatabaseNames
>> EXEC sp_executesql @.RestoreStatements
>> GO
>>
>> --cursor example
>> DECLARE
>> @.database_name sysname,
>> @.backup_file_location varchar(255),
>> @.current_Data_logical_file sysname,
>> @.current_Log_logical_file sysname,
>> @.new_Data_logical_file varchar(255),
>> @.new_Log_logical_file varchar(255)
>>
>> DECLARE DatabaseNames
>> CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
>> SELECT
>> database_name,
>> backup_file_location,
>> current_Data_logical_file,
>> current_Log_logical_file,
>> new_Data_logical_file,
>> new_Log_logical_file
>> FROM DatabaseNames
>> OPEN DatabaseNames
>> WHILE 1 = 1
>> BEGIN
>> FETCH NEXT FROM DatabaseNames INTO
>> @.database_name,
>> @.backup_file_location,
>> @.current_Data_logical_file,
>> @.current_Log_logical_file,
>> @.new_Data_logical_file,
>> @.new_Log_logical_file
>> IF @.@.FETCH_STATUS = -1 BREAK
>> RESTORE DATABASE @.database_name
>> FROM DISK=@.backup_file_location
>> WITH
>> MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
>> MOVE @.current_Log_logical_file TO @.new_Log_logical_file
>> END
>> CLOSE DatabaseNames
>> DEALLOCATE DatabaseNames
>>
> "q_test" <jluost1@.yahoo.com> wrote in message
> news:443FC542.6050509@.yahoo.com...
>
> >I have about 500 databases. They all have the same logical filenames
> >"application_db_data" and "application_db_log". I am moving them
from SQL
> >2000 to SQL 2005 on a new server, keeping all logical filenames.
> >
> > I have a table that stores all database parameters needed for RESTORE:
> >
> > database_name, backup_file_location, current_Data_logical_file,
> > current_Log_logical_file, new_Data_logical_file, new_Log_logical_file
> >
> > As I said, current_Data_logical_file="application_db_data" for all DBs,
> > current_log_logical_file="application_db_log" for all DBs.
> >
> > I created a SP called "PROC_RESTORE_ALL_DBS". Inside it there is a loop
> > through the table (using cursor) and run the following restore
> > statement:
> >
> > --
> > RESTORE DATABASE @.database_name
> > FROM DISK = backup_file_location
> > WITH
> > MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> > MOVE @.current_Log_logical_file TO @.new_Log_logical_file,
> > REPLACE
> > --
> >
> > Unfortunately, I can only restore One database (the first one) for each
> > run. There are error messages for the resore of the second database and
> > the ones after it:
> >
> > --
> > Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
> > Logical file 'application_db_log' is not part of database
'App_DB1'. Use
> > RESTORE FILELISTONLY to list the logical file names.
> > Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
> > RESTORE DATABASE is terminating abnormally.
> > --
> >
> > Why is this happening? Is it because the loop is running too fast and
> the
> > logical file is still being held? Why is the error on the
> > 'application_db_log', not on 'application_db_data'?
> >
> > Anyway to get around it? I am running the SP on SQL 2005.
> >
> >
> >
> >
> >
>
>
>|||It seems like SQL Server isn't recognizing the 'MOVE' and/or logical file
names on the subsequent restores. Can you post your actual script? I'd
like to try to reproduce your problem.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"q_test" <jluost1@.yahoo.com> wrote in message
news:443FEB36.2090408@.yahoo.com...
> Yes, it is a complete backup. Yes, the first one is always restored
> successfully but the subsequent ones failed.
> In my table, if one is restored successfully, it is flagged. So the next
> run will skip it.
> I can get each one restored by run the SP 500 times. But I want it done
> with one run...
> From the error message I found that, starting from the second database, it
> is trying to find the physical directory of the logical file on the
> server, which doesn't exist any more. The logical file's physical
> directory is like "C:\MSSQL$instance1\data\app_db1.mdb". In the new
> server, it is like "C:\MSSQL05_instance1\data\app_db1.mdb".
> A revised code is more like:
> --
> RESTORE DATABASE @.database_name
> FROM DISK=@.backup_file_location
> WITH
> MOVE @.current_Data_logical_file TO @.new_Data_file_LOCATION,
> MOVE @.current_Log_logical_file TO @.new_Log_file_LOCATION
> END
> --
> Why is the old physical location used? And only from the second database?
>
> > Note the the second file's isPresent=0. Is it causing the error of
> > RESTORE? Why isPresent=0 since size>0? How can I get around?
>
> Was this a full backup that completed successfully? I haven't seen
> IsPresent = 0 before.
> In your initial post, you mentioned that you can successfully restore the
> first backup from 'each run'. Does this mean that when you later skip the
> one that restored successfully, the second database then restores
> successfully but the subsequent ones fail?
> -- Hope this helps. Dan Guzman SQL Server MVP "q_test" <jluost1@.yahoo.com>
> wrote in message news:443FDD53.2090600@.yahoo.com...
> >I am using almost exact code as you have laid out. Since you didn't have
> >any problem, maybe the problem is in my backup file.
> >
> > So I ran the following as instructed in error message:
> >
> > RESTORE FILELISTONLY from disk='D:\Database\Backup\App_DB1.bak'
> >
> > and here is what I see:
> >
> > there are two files:
> >
> > LogicalName, Type, FileGroupName, FileSize, FileID, isPresent
> > application_db_Data, D, Primary, 232343, 1, 1
> > application_db_log, L, NULL, 34433, 2, 0
> >
> > Note the the second file's isPresent=0. Is it causing the error of
> > RESTORE? Why isPresent=0 since size>0? How can I get around?
> >
> > Thank you very much on this.
> >
> > Dan Guzman wrote:
> >> I'm not sure what the problem might be. I successfully restored
> multiple
> >> databases using the cursor example below. In any case, you might
> find it
> >> easier to generate and execute a single script using the SQL 2005
> >> varchar(MAX):
> >>
> >> --script example
> >> DECLARE @.RestoreStatements nvarchar(MAX)
> >> SET @.RestoreStatements = ''
> >> SELECT
> >> @.RestoreStatements = @.RestoreStatements + '
> >> DATABASE [' + database_name + ']
> >> FROM DISK=''' + backup_file_location + '''
> >> WITH
> >> MOVE ''' + current_Data_logical_file +
> >> ''' TO ' + '''' + new_Data_logical_file + ''',
> >> MOVE ''' + current_Log_logical_file +
> >> ''' TO ''' + new_Log_logical_file + ''''
> >> FROM DatabaseNames
> >> EXEC sp_executesql @.RestoreStatements
> >> GO
> >>
> >> --cursor example
> >> DECLARE
> >> @.database_name sysname,
> >> @.backup_file_location varchar(255),
> >> @.current_Data_logical_file sysname,
> >> @.current_Log_logical_file sysname,
> >> @.new_Data_logical_file varchar(255),
> >> @.new_Log_logical_file varchar(255)
> >>
> >> DECLARE DatabaseNames
> >> CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
> >> SELECT
> >> database_name,
> >> backup_file_location,
> >> current_Data_logical_file,
> >> current_Log_logical_file,
> >> new_Data_logical_file,
> >> new_Log_logical_file
> >> FROM DatabaseNames
> >> OPEN DatabaseNames
> >> WHILE 1 = 1
> >> BEGIN
> >> FETCH NEXT FROM DatabaseNames INTO
> >> @.database_name,
> >> @.backup_file_location,
> >> @.current_Data_logical_file,
> >> @.current_Log_logical_file,
> >> @.new_Data_logical_file,
> >> @.new_Log_logical_file
> >> IF @.@.FETCH_STATUS = -1 BREAK
> >> RESTORE DATABASE @.database_name
> >> FROM DISK=@.backup_file_location
> >> WITH
> >> MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> >> MOVE @.current_Log_logical_file TO @.new_Log_logical_file
> >> END
> >> CLOSE DatabaseNames
> >> DEALLOCATE DatabaseNames
> >>
> > "q_test" <jluost1@.yahoo.com> wrote in message
> > news:443FC542.6050509@.yahoo.com...
> >
> > >I have about 500 databases. They all have the same logical filenames
> > >"application_db_data" and "application_db_log". I am moving them
> from SQL
> > >2000 to SQL 2005 on a new server, keeping all logical filenames.
> > >
> > > I have a table that stores all database parameters needed for RESTORE:
> > >
> > > database_name, backup_file_location, current_Data_logical_file,
> > > current_Log_logical_file, new_Data_logical_file, new_Log_logical_file
> > >
> > > As I said, current_Data_logical_file="application_db_data" for all
> > > DBs,
> > > current_log_logical_file="application_db_log" for all DBs.
> > >
> > > I created a SP called "PROC_RESTORE_ALL_DBS". Inside it there is a
> > > loop
> > > through the table (using cursor) and run the following restore
> > > statement:
> > >
> > > --
> > > RESTORE DATABASE @.database_name
> > > FROM DISK = backup_file_location
> > > WITH
> > > MOVE @.current_Data_logical_file TO @.new_Data_logical_file,
> > > MOVE @.current_Log_logical_file TO @.new_Log_logical_file,
> > > REPLACE
> > > --
> > >
> > > Unfortunately, I can only restore One database (the first one) for
> > > each
> > > run. There are error messages for the resore of the second database
> > > and
> > > the ones after it:
> > >
> > > --
> > > Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
> > > Logical file 'application_db_log' is not part of database
> 'App_DB1'. Use
> > > RESTORE FILELISTONLY to list the logical file names.
> > > Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
> > > RESTORE DATABASE is terminating abnormally.
> > > --
> > >
> > > Why is this happening? Is it because the loop is running too fast and
> > the
> > > logical file is still being held? Why is the error on the
> > > 'application_db_log', not on 'application_db_data'?
> > >
> > > Anyway to get around it? I am running the SP on SQL 2005.
> > >
> > >
> > >
> > >
> > >
> >
> >
> >
>
>|||Here is the script for SP, table, insert data and exec SP statements:
--
CREATE PROCEDURE PROC_RESTORE_ALL_DBS
AS
/*
Purpose: upgrate from SQL 2000 to SQL 2005 by restoring
*/
set nocount on
declare @.oneWebDB varchar(75)
declare @.datapath varchar(100)
declare @.logicalDataFileName varchar(100),@.logicalLogFileName varchar(100)
declare @.DestPath varchar(100)
DECLARE @.err int
declare @.updQ varchar(300)
declare @.back_db_name varchar(75),@.diskname varchar(75)
declare @.full_bk_data_file varchar(75), @.full_bk_log_file varchar(75),
@.full_data_file varchar(75), @.full_log_file varchar(75),
@.logicalFileName varchar(100)
set @.datapath = 'D:\Migration\Backup\DEFAULT\'
set @.DestPath = 'D:\MSSQL05_DEFAULT\'
declare all_web_db cursor
LOCAL
READ_ONLY
for
select db_name_used
,logicalDataFileName
,logicalLogFileName
from migration_table_info
where is_not_active=0
and is_migrated=0
order by db_name_used
open all_web_db
fetch next from all_web_db
into @.oneWebDB,@.logicalDataFileName,@.logicalLogFileName
while @.@.FETCH_STATUS = 0 begin
begin
print '-- Restore database ' + @.oneWebDB + '
--'
set @.back_db_name = @.oneWebDB + '.bak'
set @.diskname = @.datapath +@.back_db_name
set @.full_bk_data_file =@.logicalDataFileName
set @.full_bk_log_file = @.logicalLogFileName
set @.full_data_file = @.DestPath + 'data\' + @.oneWebDB + '_data.mdf'
set @.full_log_file = @.DestPath + 'Log\' + @.oneWebDB + '_log.ldf'
print ' ' + @.diskname + ' '
print ' ' + @.full_data_file + ' '
print ' ' + @.full_log_file + ' '
--RESTORE FILELISTONLY
--FROM DISK = @.diskname
RESTORE DATABASE @.oneWebDB
FROM DISK = @.diskname
WITH
MOVE @.full_bk_data_file TO @.full_data_file,
MOVE @.full_bk_log_file TO @.full_log_file
--,REPLACE
-- update [migration_table_info] is_migrated=1
SELECT @.err = @.@.error
IF (@.err = 0)
BEGIN
set @.updQ = 'UPDATE migration_table_info SET is_migrated=1 WHERE
db_name_used = ''' + @.oneWebDB + ''''
execute(@.updQ)
END
end
fetch next from all_web_db into
@.oneWebDB,@.logicalDataFileName,@.logicalDataFileName
end
close all_web_db
deallocate all_web_db
set nocount off
return 1
GO
--
Here is the script for database table:
--
CREATE TABLE migration_table_info (
db_name_used varchar(75) primary key,
logicalDataFileName varchar(100) null,
logicalLogFileName varchar(100) null,
is_not_active bit null,
is_migrated bit null
)
--
Here is two example of database info:
--
INSERT INTO migration_table_info VALUES ('m7E','app_Data','app_Log',0,0)
INSERT INTO migration_table_info VALUES ('m7F','app_Data','app_Log',0,0)
--
Use the following to run the SP:
--
exec PROC_RESTORE_ALL_DBS
--
Hope that helps...
Dan Guzman wrote:
> It seems like SQL Server isn't recognizing the 'MOVE' and/or logical file
> names on the subsequent restores. Can you post your actual script? I'd
> like to try to reproduce your problem.
>|||I created 100 SQL 2000 databases, backup these up and then ran your script
under SQL 2005 SP1 CTP. I changed only the folder names and database names.
All 100 databases restored without problems.
From your initial post:
--
Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
Logical file 'application_db_log' is not part of database 'App_DB1'. Use
RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
RESTORE DATABASE is terminating abnormally.
--
And in a later post:
---
From the error message I found that, starting from the second database,
it is trying to find the physical directory of the logical file on the
server, which doesn't exist any more. The logical file's physical
directory is like "C:\MSSQL$instance1\data\app_db1.mdb". In the new
server, it is like "C:\MSSQL05_instance1\data\app_db1.mdb".
---
Why do you say that subsequent restores are looking for the physical
directory of the original server? I don't see that in the error you posted.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"q_test" <jluost1@.yahoo.com> wrote in message
news:443FF33D.70204@.yahoo.com...
> Here is the script for SP, table, insert data and exec SP statements:
>|||Dan Guzman,
You asked the right question. I looked at my code again and found a very
stupid mistake on the line below:
--
fetch next from all_web_db into
@.oneWebDB,@.logicalDataFileName,@.logicalDataFileName
--
The last one should be: @.logicalLogFileName instead of @.logicalDataFileName.
It is working fine now.
Thank you for all of your time and effort. I really really appreciate it.
Dan Guzman wrote:
> I created 100 SQL 2000 databases, backup these up and then ran your script
> under SQL 2005 SP1 CTP. I changed only the folder names and database names.
> All 100 databases restored without problems.
> From your initial post:
> --
> Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
> Logical file 'application_db_log' is not part of database 'App_DB1'. Use
> RESTORE FILELISTONLY to list the logical file names.
> Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
> RESTORE DATABASE is terminating abnormally.
> --
> And in a later post:
> ---
> From the error message I found that, starting from the second database,
> it is trying to find the physical directory of the logical file on the
> server, which doesn't exist any more. The logical file's physical
> directory is like "C:\MSSQL$instance1\data\app_db1.mdb". In the new
> server, it is like "C:\MSSQL05_instance1\data\app_db1.mdb".
> ---
> Why do you say that subsequent restores are looking for the physical
> directory of the original server? I don't see that in the error you posted.
>|||I'm glad you were able to get it sorted out.
--
Dan Guzman
SQL Server MVP
"q_test" <jluost1@.yahoo.com> wrote in message
news:4443A02D.8040704@.yahoo.com...
> Dan Guzman,
> You asked the right question. I looked at my code again and found a very
> stupid mistake on the line below:
> --
> fetch next from all_web_db into
> @.oneWebDB,@.logicalDataFileName,@.logicalDataFileName
> --
> The last one should be: @.logicalLogFileName instead of
> @.logicalDataFileName.
> It is working fine now.
> Thank you for all of your time and effort. I really really appreciate it.
> Dan Guzman wrote:
>> I created 100 SQL 2000 databases, backup these up and then ran your
>> script under SQL 2005 SP1 CTP. I changed only the folder names and
>> database names. All 100 databases restored without problems.
>> From your initial post:
>> --
>> Msg 3234, Level 16, State 2, Procedure PROC_RESTORE_ALL_DBS, Line 70
>> Logical file 'application_db_log' is not part of database 'App_DB1'. Use
>> RESTORE FILELISTONLY to list the logical file names.
>> Msg 3013, Level 16, State 1, Procedure PROC_RESTORE_ALL_DBS, Line 70
>> RESTORE DATABASE is terminating abnormally.
>> --
>> And in a later post:
>> ---
>> From the error message I found that, starting from the second database,
>> it is trying to find the physical directory of the logical file on the
>> server, which doesn't exist any more. The logical file's physical
>> directory is like "C:\MSSQL$instance1\data\app_db1.mdb". In the new
>> server, it is like "C:\MSSQL05_instance1\data\app_db1.mdb".
>> ---
>> Why do you say that subsequent restores are looking for the physical
>> directory of the original server? I don't see that in the error you
>> posted.
>sql
Subscribe to:
Posts (Atom)