Hi
Can anybody tell me what are the possible causes when a client update request to a server fails..
Some of the ones i suppose r
1 Master server down
2 Link Failure
3 Update Request query syntax invalid
Any more.............Login does not exist - permissions issue, database offline/suspect/corruption, locking issues...|||I read your other post about replication concerns - do you have problems now ?|||Hi
thnx a lot sir for responding to my second query...as far as the first is concened i am still facing the same problems...
I understand the concept of master n slave relationship but have certain queries regarding this...
1) In case the master is down i do i configure my sysem so that a present active slave becomes the master or any other way to tackle this situation.How does the clients know about this new server being set as the master .
2)The master is run as a myslq demon . Now the slaves though being servers ..are run as clients of the master server with a definate usernme who has been granted REPLICATION SLAVE PERMISSion set at the master server??
i.e do i run the slaves as mysql -h xxx -u xxxx -p ?? --mysql client ?
3)Is it necessary t take a snapshot of the datadir at the serer , for the first time i set up replication ?/?
Kindly help me out
Thnx
Varun
Showing posts with label update. Show all posts
Showing posts with label update. Show all posts
Monday, March 19, 2012
Hi
I'm cuestion
how to update at all
this is my sentence
update Table1
set Total = sum(b.monto)
from Table1 a , Details b
Where a.numerCode = b.numerCode
the answer is : an aggregate may not apper in the set list of an Update
statement.
Thanks for you help.
ChreesHello, MJ
Try something like this:
update Table1 set Total = (
select sum(b.monto) from Details b
Where a.numerCode = b.numerCode
)
from Table1 a
where exists (
select * from Details b
Where a.numerCode = b.numerCode
)
or:
update Table1 set Total = isnull((
select sum(b.monto) from Details b
Where a.numerCode = b.numerCode
),0)
from Table1 a
Razvan|||Thanks
try Ok.
thank you for you help.
"Razvan Socol" escribió:
> Hello, MJ
> Try something like this:
> update Table1 set Total = (
> select sum(b.monto) from Details b
> Where a.numerCode = b.numerCode
> )
> from Table1 a
> where exists (
> select * from Details b
> Where a.numerCode = b.numerCode
> )
> or:
> update Table1 set Total = isnull((
> select sum(b.monto) from Details b
> Where a.numerCode = b.numerCode
> ),0)
> from Table1 a
> Razvan
>
how to update at all
this is my sentence
update Table1
set Total = sum(b.monto)
from Table1 a , Details b
Where a.numerCode = b.numerCode
the answer is : an aggregate may not apper in the set list of an Update
statement.
Thanks for you help.
ChreesHello, MJ
Try something like this:
update Table1 set Total = (
select sum(b.monto) from Details b
Where a.numerCode = b.numerCode
)
from Table1 a
where exists (
select * from Details b
Where a.numerCode = b.numerCode
)
or:
update Table1 set Total = isnull((
select sum(b.monto) from Details b
Where a.numerCode = b.numerCode
),0)
from Table1 a
Razvan|||Thanks
try Ok.
thank you for you help.
"Razvan Socol" escribió:
> Hello, MJ
> Try something like this:
> update Table1 set Total = (
> select sum(b.monto) from Details b
> Where a.numerCode = b.numerCode
> )
> from Table1 a
> where exists (
> select * from Details b
> Where a.numerCode = b.numerCode
> )
> or:
> update Table1 set Total = isnull((
> select sum(b.monto) from Details b
> Where a.numerCode = b.numerCode
> ),0)
> from Table1 a
> Razvan
>
Hi
I'm cuestion
how to update at all
this is my sentence
update Table1
set Total = sum(b.monto)
from Table1 a , Details b
Where a.numerCode = b.numerCode
the answer is : an aggregate may not apper in the set list of an Update
statement.
Thanks for you help.
ChreesHello, MJ
Try something like this:
update Table1 set Total = (
select sum(b.monto) from Details b
Where a.numerCode = b.numerCode
)
from Table1 a
where exists (
select * from Details b
Where a.numerCode = b.numerCode
)
or:
update Table1 set Total = isnull((
select sum(b.monto) from Details b
Where a.numerCode = b.numerCode
),0)
from Table1 a
Razvan|||Thanks
try Ok.
thank you for you help.
"Razvan Socol" escribió:
> Hello, MJ
> Try something like this:
> update Table1 set Total = (
> select sum(b.monto) from Details b
> Where a.numerCode = b.numerCode
> )
> from Table1 a
> where exists (
> select * from Details b
> Where a.numerCode = b.numerCode
> )
> or:
> update Table1 set Total = isnull((
> select sum(b.monto) from Details b
> Where a.numerCode = b.numerCode
> ),0)
> from Table1 a
> Razvan
>
how to update at all
this is my sentence
update Table1
set Total = sum(b.monto)
from Table1 a , Details b
Where a.numerCode = b.numerCode
the answer is : an aggregate may not apper in the set list of an Update
statement.
Thanks for you help.
ChreesHello, MJ
Try something like this:
update Table1 set Total = (
select sum(b.monto) from Details b
Where a.numerCode = b.numerCode
)
from Table1 a
where exists (
select * from Details b
Where a.numerCode = b.numerCode
)
or:
update Table1 set Total = isnull((
select sum(b.monto) from Details b
Where a.numerCode = b.numerCode
),0)
from Table1 a
Razvan|||Thanks
try Ok.
thank you for you help.
"Razvan Socol" escribió:
> Hello, MJ
> Try something like this:
> update Table1 set Total = (
> select sum(b.monto) from Details b
> Where a.numerCode = b.numerCode
> )
> from Table1 a
> where exists (
> select * from Details b
> Where a.numerCode = b.numerCode
> )
> or:
> update Table1 set Total = isnull((
> select sum(b.monto) from Details b
> Where a.numerCode = b.numerCode
> ),0)
> from Table1 a
> Razvan
>
hhhhelp, restore
ho fatto un update che non dovevo senza fare backup prima come posso
ripescare i dati?
gia committato ovviamente
carlo
Usted habla ingls?
(Also, pick a single newsgroup to post to.)
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Carlo wrote:
>ho fatto un update che non dovevo senza fare backup prima come posso
>ripescare i dati?
>gia committato ovviamente
>carlo
>
>
|||Translated by bablefish:
I have made update that I did not have without to make before backup as I can find again the data?
Probably not, if you don't have any backups at all. Do you have transaction log backups?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carlo" <carletto.mNOSPAM@.gmail.com> wrote in message news:OnD6BLncFHA.2664@.TK2MSFTNGP15.phx.gbl...
> ho fatto un update che non dovevo senza fare backup prima come posso ripescare i dati?
> gia committato ovviamente
> carlo
>
|||Thank you all for Translation.
Carlo,
All on to tibor,
You can see the database before update Provided you have the below :-
1. Datbase must be in FULL recovery mode
2. Full database backup
3. All transaction log backups
If you passed the above then you can do a Point in time recovery until you
update the data:-
1. Perform a Transaction log backup in your database.
2. Create a new database
3. Restore the full database backup WITH NORECOVERY option into the new
database
4. Restore the subsequent transaction log backups till the last (DO not
restore last) with NORECOVERY option
5. Restore the Last Transaction log backup with Recovery option and STOPAT
='Date and time"
Refer RESTORE database/Log in books online for more information
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esihFlncFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Translated by bablefish:
> I have made update that I did not have without to make before backup as I
> can find again the data?
> Probably not, if you don't have any backups at all. Do you have
> transaction log backups?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Carlo" <carletto.mNOSPAM@.gmail.com> wrote in message
> news:OnD6BLncFHA.2664@.TK2MSFTNGP15.phx.gbl...
>
|||Out of curiosity, would this restore up to the last complete transaction up
to the point in time specified but leave any transactions that bridge the
time un-applied (I would hope so)? And I would think maybe a log reader
would be helpful to pinpoint the EXACT time. Just plugging in a time because
it was "about 8:15 when it happened" doesn't seem like a great idea.
Bob Castleman
DBA Poseur.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OY95kSqcFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Thank you all for Translation.
> Carlo,
> All on to tibor,
> You can see the database before update Provided you have the below :-
> 1. Datbase must be in FULL recovery mode
> 2. Full database backup
> 3. All transaction log backups
> If you passed the above then you can do a Point in time recovery until you
> update the data:-
> 1. Perform a Transaction log backup in your database.
> 2. Create a new database
> 3. Restore the full database backup WITH NORECOVERY option into the new
> database
> 4. Restore the subsequent transaction log backups till the last (DO not
> restore last) with NORECOVERY option
> 5. Restore the Last Transaction log backup with Recovery option and STOPAT
> ='Date and time"
> Refer RESTORE database/Log in books online for more information
> Thanks
> Hari
> SQL Server MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:esihFlncFHA.2180@.TK2MSFTNGP12.phx.gbl...
>
|||Yes, uncommitted transactions will be rolled back. And, yes a log mining tool is good to have to
pinpoint the time. An option is to restore the same last log backup several times with standby and
STOPAT pushing the time forward a little bit each time:
http://www.karaszi.com/SQLServer/inf...eral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bob Castleman" <nomail@.here> wrote in message news:%23MAq5ErcFHA.720@.TK2MSFTNGP15.phx.gbl...
> Out of curiosity, would this restore up to the last complete transaction up to the point in time
> specified but leave any transactions that bridge the time un-applied (I would hope so)? And I
> would think maybe a log reader would be helpful to pinpoint the EXACT time. Just plugging in a
> time because it was "about 8:15 when it happened" doesn't seem like a great idea.
> Bob Castleman
> DBA Poseur.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OY95kSqcFHA.3504@.TK2MSFTNGP12.phx.gbl...
>
ripescare i dati?
gia committato ovviamente
carlo
Usted habla ingls?
(Also, pick a single newsgroup to post to.)
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Carlo wrote:
>ho fatto un update che non dovevo senza fare backup prima come posso
>ripescare i dati?
>gia committato ovviamente
>carlo
>
>
|||Translated by bablefish:
I have made update that I did not have without to make before backup as I can find again the data?
Probably not, if you don't have any backups at all. Do you have transaction log backups?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carlo" <carletto.mNOSPAM@.gmail.com> wrote in message news:OnD6BLncFHA.2664@.TK2MSFTNGP15.phx.gbl...
> ho fatto un update che non dovevo senza fare backup prima come posso ripescare i dati?
> gia committato ovviamente
> carlo
>
|||Thank you all for Translation.
Carlo,
All on to tibor,
You can see the database before update Provided you have the below :-
1. Datbase must be in FULL recovery mode
2. Full database backup
3. All transaction log backups
If you passed the above then you can do a Point in time recovery until you
update the data:-
1. Perform a Transaction log backup in your database.
2. Create a new database
3. Restore the full database backup WITH NORECOVERY option into the new
database
4. Restore the subsequent transaction log backups till the last (DO not
restore last) with NORECOVERY option
5. Restore the Last Transaction log backup with Recovery option and STOPAT
='Date and time"
Refer RESTORE database/Log in books online for more information
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esihFlncFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Translated by bablefish:
> I have made update that I did not have without to make before backup as I
> can find again the data?
> Probably not, if you don't have any backups at all. Do you have
> transaction log backups?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Carlo" <carletto.mNOSPAM@.gmail.com> wrote in message
> news:OnD6BLncFHA.2664@.TK2MSFTNGP15.phx.gbl...
>
|||Out of curiosity, would this restore up to the last complete transaction up
to the point in time specified but leave any transactions that bridge the
time un-applied (I would hope so)? And I would think maybe a log reader
would be helpful to pinpoint the EXACT time. Just plugging in a time because
it was "about 8:15 when it happened" doesn't seem like a great idea.
Bob Castleman
DBA Poseur.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OY95kSqcFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Thank you all for Translation.
> Carlo,
> All on to tibor,
> You can see the database before update Provided you have the below :-
> 1. Datbase must be in FULL recovery mode
> 2. Full database backup
> 3. All transaction log backups
> If you passed the above then you can do a Point in time recovery until you
> update the data:-
> 1. Perform a Transaction log backup in your database.
> 2. Create a new database
> 3. Restore the full database backup WITH NORECOVERY option into the new
> database
> 4. Restore the subsequent transaction log backups till the last (DO not
> restore last) with NORECOVERY option
> 5. Restore the Last Transaction log backup with Recovery option and STOPAT
> ='Date and time"
> Refer RESTORE database/Log in books online for more information
> Thanks
> Hari
> SQL Server MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:esihFlncFHA.2180@.TK2MSFTNGP12.phx.gbl...
>
|||Yes, uncommitted transactions will be rolled back. And, yes a log mining tool is good to have to
pinpoint the time. An option is to restore the same last log backup several times with standby and
STOPAT pushing the time forward a little bit each time:
http://www.karaszi.com/SQLServer/inf...eral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bob Castleman" <nomail@.here> wrote in message news:%23MAq5ErcFHA.720@.TK2MSFTNGP15.phx.gbl...
> Out of curiosity, would this restore up to the last complete transaction up to the point in time
> specified but leave any transactions that bridge the time un-applied (I would hope so)? And I
> would think maybe a log reader would be helpful to pinpoint the EXACT time. Just plugging in a
> time because it was "about 8:15 when it happened" doesn't seem like a great idea.
> Bob Castleman
> DBA Poseur.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OY95kSqcFHA.3504@.TK2MSFTNGP12.phx.gbl...
>
hhhhelp, restore
ho fatto un update che non dovevo senza fare backup prima come posso
ripescare i dati?
gia committato ovviamente
carlo
Usted habla ingls?
(Also, pick a single newsgroup to post to.)
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Carlo wrote:
>ho fatto un update che non dovevo senza fare backup prima come posso
>ripescare i dati?
>gia committato ovviamente
>carlo
>
>
|||Translated by bablefish:
I have made update that I did not have without to make before backup as I can find again the data?
Probably not, if you don't have any backups at all. Do you have transaction log backups?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carlo" <carletto.mNOSPAM@.gmail.com> wrote in message news:OnD6BLncFHA.2664@.TK2MSFTNGP15.phx.gbl...
> ho fatto un update che non dovevo senza fare backup prima come posso ripescare i dati?
> gia committato ovviamente
> carlo
>
|||Mike, I believe he's Italian (I recognize the writing since I'm Italian too).
I think he's saying he updated some data without backing up his database first.
Now he thinks he messed up and wants to uncommit those changes.
Carlo, non e eventuale senza backup.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:ey%23DQPncFHA.4040@.TK2MSFTNGP14.phx.gbl...
Usted habla ingls?
(Also, pick a single newsgroup to post to.)
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Carlo wrote:
ho fatto un update che non dovevo senza fare backup prima come posso
ripescare i dati?
gia committato ovviamente
carlo
|||Thank you all for Translation.
Carlo,
All on to tibor,
You can see the database before update Provided you have the below :-
1. Datbase must be in FULL recovery mode
2. Full database backup
3. All transaction log backups
If you passed the above then you can do a Point in time recovery until you
update the data:-
1. Perform a Transaction log backup in your database.
2. Create a new database
3. Restore the full database backup WITH NORECOVERY option into the new
database
4. Restore the subsequent transaction log backups till the last (DO not
restore last) with NORECOVERY option
5. Restore the Last Transaction log backup with Recovery option and STOPAT
='Date and time"
Refer RESTORE database/Log in books online for more information
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esihFlncFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Translated by bablefish:
> I have made update that I did not have without to make before backup as I
> can find again the data?
> Probably not, if you don't have any backups at all. Do you have
> transaction log backups?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Carlo" <carletto.mNOSPAM@.gmail.com> wrote in message
> news:OnD6BLncFHA.2664@.TK2MSFTNGP15.phx.gbl...
>
|||Out of curiosity, would this restore up to the last complete transaction up
to the point in time specified but leave any transactions that bridge the
time un-applied (I would hope so)? And I would think maybe a log reader
would be helpful to pinpoint the EXACT time. Just plugging in a time because
it was "about 8:15 when it happened" doesn't seem like a great idea.
Bob Castleman
DBA Poseur.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OY95kSqcFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Thank you all for Translation.
> Carlo,
> All on to tibor,
> You can see the database before update Provided you have the below :-
> 1. Datbase must be in FULL recovery mode
> 2. Full database backup
> 3. All transaction log backups
> If you passed the above then you can do a Point in time recovery until you
> update the data:-
> 1. Perform a Transaction log backup in your database.
> 2. Create a new database
> 3. Restore the full database backup WITH NORECOVERY option into the new
> database
> 4. Restore the subsequent transaction log backups till the last (DO not
> restore last) with NORECOVERY option
> 5. Restore the Last Transaction log backup with Recovery option and STOPAT
> ='Date and time"
> Refer RESTORE database/Log in books online for more information
> Thanks
> Hari
> SQL Server MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:esihFlncFHA.2180@.TK2MSFTNGP12.phx.gbl...
>
|||Yes, uncommitted transactions will be rolled back. And, yes a log mining tool is good to have to
pinpoint the time. An option is to restore the same last log backup several times with standby and
STOPAT pushing the time forward a little bit each time:
http://www.karaszi.com/SQLServer/inf...eral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bob Castleman" <nomail@.here> wrote in message news:%23MAq5ErcFHA.720@.TK2MSFTNGP15.phx.gbl...
> Out of curiosity, would this restore up to the last complete transaction up to the point in time
> specified but leave any transactions that bridge the time un-applied (I would hope so)? And I
> would think maybe a log reader would be helpful to pinpoint the EXACT time. Just plugging in a
> time because it was "about 8:15 when it happened" doesn't seem like a great idea.
> Bob Castleman
> DBA Poseur.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OY95kSqcFHA.3504@.TK2MSFTNGP12.phx.gbl...
>
ripescare i dati?
gia committato ovviamente
carlo
Usted habla ingls?
(Also, pick a single newsgroup to post to.)
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Carlo wrote:
>ho fatto un update che non dovevo senza fare backup prima come posso
>ripescare i dati?
>gia committato ovviamente
>carlo
>
>
|||Translated by bablefish:
I have made update that I did not have without to make before backup as I can find again the data?
Probably not, if you don't have any backups at all. Do you have transaction log backups?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carlo" <carletto.mNOSPAM@.gmail.com> wrote in message news:OnD6BLncFHA.2664@.TK2MSFTNGP15.phx.gbl...
> ho fatto un update che non dovevo senza fare backup prima come posso ripescare i dati?
> gia committato ovviamente
> carlo
>
|||Mike, I believe he's Italian (I recognize the writing since I'm Italian too).
I think he's saying he updated some data without backing up his database first.
Now he thinks he messed up and wants to uncommit those changes.
Carlo, non e eventuale senza backup.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:ey%23DQPncFHA.4040@.TK2MSFTNGP14.phx.gbl...
Usted habla ingls?
(Also, pick a single newsgroup to post to.)
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Carlo wrote:
ho fatto un update che non dovevo senza fare backup prima come posso
ripescare i dati?
gia committato ovviamente
carlo
|||Thank you all for Translation.
Carlo,
All on to tibor,
You can see the database before update Provided you have the below :-
1. Datbase must be in FULL recovery mode
2. Full database backup
3. All transaction log backups
If you passed the above then you can do a Point in time recovery until you
update the data:-
1. Perform a Transaction log backup in your database.
2. Create a new database
3. Restore the full database backup WITH NORECOVERY option into the new
database
4. Restore the subsequent transaction log backups till the last (DO not
restore last) with NORECOVERY option
5. Restore the Last Transaction log backup with Recovery option and STOPAT
='Date and time"
Refer RESTORE database/Log in books online for more information
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esihFlncFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Translated by bablefish:
> I have made update that I did not have without to make before backup as I
> can find again the data?
> Probably not, if you don't have any backups at all. Do you have
> transaction log backups?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Carlo" <carletto.mNOSPAM@.gmail.com> wrote in message
> news:OnD6BLncFHA.2664@.TK2MSFTNGP15.phx.gbl...
>
|||Out of curiosity, would this restore up to the last complete transaction up
to the point in time specified but leave any transactions that bridge the
time un-applied (I would hope so)? And I would think maybe a log reader
would be helpful to pinpoint the EXACT time. Just plugging in a time because
it was "about 8:15 when it happened" doesn't seem like a great idea.
Bob Castleman
DBA Poseur.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OY95kSqcFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Thank you all for Translation.
> Carlo,
> All on to tibor,
> You can see the database before update Provided you have the below :-
> 1. Datbase must be in FULL recovery mode
> 2. Full database backup
> 3. All transaction log backups
> If you passed the above then you can do a Point in time recovery until you
> update the data:-
> 1. Perform a Transaction log backup in your database.
> 2. Create a new database
> 3. Restore the full database backup WITH NORECOVERY option into the new
> database
> 4. Restore the subsequent transaction log backups till the last (DO not
> restore last) with NORECOVERY option
> 5. Restore the Last Transaction log backup with Recovery option and STOPAT
> ='Date and time"
> Refer RESTORE database/Log in books online for more information
> Thanks
> Hari
> SQL Server MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:esihFlncFHA.2180@.TK2MSFTNGP12.phx.gbl...
>
|||Yes, uncommitted transactions will be rolled back. And, yes a log mining tool is good to have to
pinpoint the time. An option is to restore the same last log backup several times with standby and
STOPAT pushing the time forward a little bit each time:
http://www.karaszi.com/SQLServer/inf...eral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bob Castleman" <nomail@.here> wrote in message news:%23MAq5ErcFHA.720@.TK2MSFTNGP15.phx.gbl...
> Out of curiosity, would this restore up to the last complete transaction up to the point in time
> specified but leave any transactions that bridge the time un-applied (I would hope so)? And I
> would think maybe a log reader would be helpful to pinpoint the EXACT time. Just plugging in a
> time because it was "about 8:15 when it happened" doesn't seem like a great idea.
> Bob Castleman
> DBA Poseur.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OY95kSqcFHA.3504@.TK2MSFTNGP12.phx.gbl...
>
hhhhelp, restore
ho fatto un update che non dovevo senza fare backup prima come posso
ripescare i dati?
gia committato ovviamente
carloUsted habla ingls?
(Also, pick a single newsgroup to post to.)
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Carlo wrote:
>ho fatto un update che non dovevo senza fare backup prima come posso
>ripescare i dati?
>gia committato ovviamente
>carlo
>
>|||Translated by bablefish:
I have made update that I did not have without to make before backup as I ca
n find again the data?
Probably not, if you don't have any backups at all. Do you have transaction
log backups?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carlo" <carletto.mNOSPAM@.gmail.com> wrote in message news:OnD6BLncFHA.2664@.TK2MSFTNGP15.phx
.gbl...
> ho fatto un update che non dovevo senza fare backup prima come posso ripes
care i dati?
> gia committato ovviamente
> carlo
>|||Mike, I believe he's Italian (I recognize the writing since I'm Italian too)
.
I think he's saying he updated some data without backing up his database fir
st.
Now he thinks he messed up and wants to uncommit those changes.
Carlo, non e eventuale senza backup.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:ey%
23DQPncFHA.4040@.TK2MSFTNGP14.phx.gbl...
Usted habla ingls?
(Also, pick a single newsgroup to post to.)
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Carlo wrote:
ho fatto un update che non dovevo senza fare backup prima come posso
ripescare i dati?
gia committato ovviamente
carlo|||Thank you all for Translation.
Carlo,
All on to tibor,
You can see the database before update Provided you have the below :-
1. Datbase must be in FULL recovery mode
2. Full database backup
3. All transaction log backups
If you passed the above then you can do a Point in time recovery until you
update the data:-
1. Perform a Transaction log backup in your database.
2. Create a new database
3. Restore the full database backup WITH NORECOVERY option into the new
database
4. Restore the subsequent transaction log backups till the last (DO not
restore last) with NORECOVERY option
5. Restore the Last Transaction log backup with Recovery option and STOPAT
='Date and time"
Refer RESTORE database/Log in books online for more information
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esihFlncFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Translated by bablefish:
> I have made update that I did not have without to make before backup as I
> can find again the data?
> Probably not, if you don't have any backups at all. Do you have
> transaction log backups?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Carlo" <carletto.mNOSPAM@.gmail.com> wrote in message
> news:OnD6BLncFHA.2664@.TK2MSFTNGP15.phx.gbl...
>|||Out of curiosity, would this restore up to the last complete transaction up
to the point in time specified but leave any transactions that bridge the
time un-applied (I would hope so)? And I would think maybe a log reader
would be helpful to pinpoint the EXACT time. Just plugging in a time because
it was "about 8:15 when it happened" doesn't seem like a great idea.
Bob Castleman
DBA Poseur.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OY95kSqcFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Thank you all for Translation.
> Carlo,
> All on to tibor,
> You can see the database before update Provided you have the below :-
> 1. Datbase must be in FULL recovery mode
> 2. Full database backup
> 3. All transaction log backups
> If you passed the above then you can do a Point in time recovery until you
> update the data:-
> 1. Perform a Transaction log backup in your database.
> 2. Create a new database
> 3. Restore the full database backup WITH NORECOVERY option into the new
> database
> 4. Restore the subsequent transaction log backups till the last (DO not
> restore last) with NORECOVERY option
> 5. Restore the Last Transaction log backup with Recovery option and STOPAT
> ='Date and time"
> Refer RESTORE database/Log in books online for more information
> Thanks
> Hari
> SQL Server MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:esihFlncFHA.2180@.TK2MSFTNGP12.phx.gbl...
>|||Yes, uncommitted transactions will be rolled back. And, yes a log mining too
l is good to have to
pinpoint the time. An option is to restore the same last log backup several
times with standby and
STOPAT pushing the time forward a little bit each time:
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bob Castleman" <nomail@.here> wrote in message news:%23MAq5ErcFHA.720@.TK2MSFTNGP15.phx.gbl..
.
> Out of curiosity, would this restore up to the last complete transaction u
p to the point in time
> specified but leave any transactions that bridge the time un-applied (I wo
uld hope so)? And I
> would think maybe a log reader would be helpful to pinpoint the EXACT time
. Just plugging in a
> time because it was "about 8:15 when it happened" doesn't seem like a grea
t idea.
> Bob Castleman
> DBA Poseur.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OY95kSqcFHA.3504@.TK2MSFTNGP12.phx.gbl...
>
ripescare i dati?
gia committato ovviamente
carloUsted habla ingls?
(Also, pick a single newsgroup to post to.)
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Carlo wrote:
>ho fatto un update che non dovevo senza fare backup prima come posso
>ripescare i dati?
>gia committato ovviamente
>carlo
>
>|||Translated by bablefish:
I have made update that I did not have without to make before backup as I ca
n find again the data?
Probably not, if you don't have any backups at all. Do you have transaction
log backups?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carlo" <carletto.mNOSPAM@.gmail.com> wrote in message news:OnD6BLncFHA.2664@.TK2MSFTNGP15.phx
.gbl...
> ho fatto un update che non dovevo senza fare backup prima come posso ripes
care i dati?
> gia committato ovviamente
> carlo
>|||Mike, I believe he's Italian (I recognize the writing since I'm Italian too)
.
I think he's saying he updated some data without backing up his database fir
st.
Now he thinks he messed up and wants to uncommit those changes.
Carlo, non e eventuale senza backup.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:ey%
23DQPncFHA.4040@.TK2MSFTNGP14.phx.gbl...
Usted habla ingls?
(Also, pick a single newsgroup to post to.)
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Carlo wrote:
ho fatto un update che non dovevo senza fare backup prima come posso
ripescare i dati?
gia committato ovviamente
carlo|||Thank you all for Translation.
Carlo,
All on to tibor,
You can see the database before update Provided you have the below :-
1. Datbase must be in FULL recovery mode
2. Full database backup
3. All transaction log backups
If you passed the above then you can do a Point in time recovery until you
update the data:-
1. Perform a Transaction log backup in your database.
2. Create a new database
3. Restore the full database backup WITH NORECOVERY option into the new
database
4. Restore the subsequent transaction log backups till the last (DO not
restore last) with NORECOVERY option
5. Restore the Last Transaction log backup with Recovery option and STOPAT
='Date and time"
Refer RESTORE database/Log in books online for more information
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esihFlncFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Translated by bablefish:
> I have made update that I did not have without to make before backup as I
> can find again the data?
> Probably not, if you don't have any backups at all. Do you have
> transaction log backups?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Carlo" <carletto.mNOSPAM@.gmail.com> wrote in message
> news:OnD6BLncFHA.2664@.TK2MSFTNGP15.phx.gbl...
>|||Out of curiosity, would this restore up to the last complete transaction up
to the point in time specified but leave any transactions that bridge the
time un-applied (I would hope so)? And I would think maybe a log reader
would be helpful to pinpoint the EXACT time. Just plugging in a time because
it was "about 8:15 when it happened" doesn't seem like a great idea.
Bob Castleman
DBA Poseur.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OY95kSqcFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Thank you all for Translation.
> Carlo,
> All on to tibor,
> You can see the database before update Provided you have the below :-
> 1. Datbase must be in FULL recovery mode
> 2. Full database backup
> 3. All transaction log backups
> If you passed the above then you can do a Point in time recovery until you
> update the data:-
> 1. Perform a Transaction log backup in your database.
> 2. Create a new database
> 3. Restore the full database backup WITH NORECOVERY option into the new
> database
> 4. Restore the subsequent transaction log backups till the last (DO not
> restore last) with NORECOVERY option
> 5. Restore the Last Transaction log backup with Recovery option and STOPAT
> ='Date and time"
> Refer RESTORE database/Log in books online for more information
> Thanks
> Hari
> SQL Server MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:esihFlncFHA.2180@.TK2MSFTNGP12.phx.gbl...
>|||Yes, uncommitted transactions will be rolled back. And, yes a log mining too
l is good to have to
pinpoint the time. An option is to restore the same last log backup several
times with standby and
STOPAT pushing the time forward a little bit each time:
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bob Castleman" <nomail@.here> wrote in message news:%23MAq5ErcFHA.720@.TK2MSFTNGP15.phx.gbl..
.
> Out of curiosity, would this restore up to the last complete transaction u
p to the point in time
> specified but leave any transactions that bridge the time un-applied (I wo
uld hope so)? And I
> would think maybe a log reader would be helpful to pinpoint the EXACT time
. Just plugging in a
> time because it was "about 8:15 when it happened" doesn't seem like a grea
t idea.
> Bob Castleman
> DBA Poseur.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OY95kSqcFHA.3504@.TK2MSFTNGP12.phx.gbl...
>
Friday, March 9, 2012
HELP--still cannot shrink the transaction log--PLEASE HELP
Hello:
This is an update to my other posting. I know that it is unorthodox to post
twice but I don't know what else to do.
Below is my original posting. I had already done the items posted by the
other gentleman such as reivewing the sysfiles. I cannot get the logical
file name from anywhere either.
Again, please help! Should I just not shrink the file. The client only has
1.82 GB of free space left and there is nothing else that can be deleted.
Below is my message:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sNevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"childofthe1980s" wrote:
> Hello:
> This is an update to my other posting. I know that it is unorthodox to post
> twice but I don't know what else to do.
> Below is my original posting. I had already done the items posted by the
> other gentleman such as reivewing the sysfiles. I cannot get the logical
> file name from anywhere either.
> Again, please help! Should I just not shrink the file. The client only has
> 1.82 GB of free space left and there is nothing else that can be deleted.
> Below is my message:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message saying
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s
>|||> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
The documentation clearly states that you can use *either* the logical filename or the file id. The
documentation is correct. The command you posted had 'GPSJCLog.ldf' for the logical filename, and I
guess that you by mistake put the *physical* filename there instead of the *logical* filename. If
you show ut the output from below, we can say for sure:
SELECT name, filename FROM sysfiles
I have shrunk database files occasionally and I always used the logical filename. In addition,
here's a script (executed on my SQL Server 2000) which clearly show the usage of the *logical*
filename:
USE master
CREATE DATABASE myTestShrink ON PRIMARY
(NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
,SIZE = 1024KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Increase log file size
BACKUP DATABASE myTestShrink TO DISK = 'nul'
CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
INSERT INTO myTestShrink..test(c2)
SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
GO
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
--Empty the log
BACKUP LOG myTestShrink WITH NO_LOG
--Check the logical and physical files names
SELECT name, filename FROM myTestShrink..sysfiles
--Perform the shrink, ***based on logical filename***
USE myTestShrink
GO
DBCC SHRINKFILE('myTestShrink_log', 2)
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
USE master
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:868E9C82-C2C4-4151-BBBD-40741201E676@.microsoft.com...
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
> "childofthe1980s" wrote:
>> Hello:
>> This is an update to my other posting. I know that it is unorthodox to post
>> twice but I don't know what else to do.
>> Below is my original posting. I had already done the items posted by the
>> other gentleman such as reivewing the sysfiles. I cannot get the logical
>> file name from anywhere either.
>> Again, please help! Should I just not shrink the file. The client only has
>> 1.82 GB of free space left and there is nothing else that can be deleted.
>> Below is my message:
>> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>> analyzer in SQL 2000 against the master database.
>> What is the EXACT syntax? You see, the name of the database is JC and the
>> log file is named GPSJClog.ldf. I have tried to place both names in the
>> syntax of the statement but with no success. I keep getting a message saying
>> that the file name is not in sysfiles.
>> This is what I have tried:
>> DBCC SHRINKFILE (GPJClog.ldf , 2)
>> Please help!
>> childofthe1980s|||you were getting that error because you were running it in the master
database, not the databse you wanted to shrink the log for. the
documentation's correct.
"childofthe1980s" wrote:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
>|||Tibor Karaszi wrote:
>> You see, the support documentation on the web did not say that you
>> have to
>> take the fileID of the database--2 in the syntax above-- in order to
>> make it
>> work! You get the number 2 from running the following query against the
>> database:
>> select * from sysfiles
>> It would have been nice if this had been in the documentation!
> The documentation clearly states that you can use *either* the logical
> filename or the file id. The documentation is correct. The command you
> posted had 'GPSJCLog.ldf' for the logical filename, and I guess that you
> by mistake put the *physical* filename there instead of the *logical*
> filename. If you show ut the output from below, we can say for sure:
> SELECT name, filename FROM sysfiles
> I have shrunk database files occasionally and I always used the logical
> filename. In addition, here's a script (executed on my SQL Server 2000)
> which clearly show the usage of the *logical* filename:
> USE master
> CREATE DATABASE myTestShrink ON PRIMARY
> (NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
> ,SIZE = 1024KB , FILEGROWTH = 10%)
> LOG ON
> (NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
> , SIZE = 1024KB , FILEGROWTH = 10%)
> GO
> --Increase log file size
> BACKUP DATABASE myTestShrink TO DISK = 'nul'
> CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
> INSERT INTO myTestShrink..test(c2)
> SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
> GO
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> --Empty the log
> BACKUP LOG myTestShrink WITH NO_LOG
> --Check the logical and physical files names
> SELECT name, filename FROM myTestShrink..sysfiles
> --Perform the shrink, ***based on logical filename***
> USE myTestShrink
> GO
> DBCC SHRINKFILE('myTestShrink_log', 2)
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> USE master
>
...just to add to Tibor's response. You can retreive the logical
filename by running sp_helpdb 'YourDatabaseName'.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||No, I did all different combinations--with ldf, without ldf, against the
master, against the database, and nothing was working until I placed the
database fileID of "2" in the suntax.
So, yes the documentation needs updating!
Thank you to all who helped!
childofthe1980s
"thomarse" wrote:
> you were getting that error because you were running it in the master
> database, not the databse you wanted to shrink the log for. the
> documentation's correct.
> "childofthe1980s" wrote:
> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> > analyzer in SQL 2000 against the master database.
> >
>|||childofthe1980s wrote:
> No, I did all different combinations--with ldf, without ldf, against the
> master, against the database, and nothing was working until I placed the
> database fileID of "2" in the suntax.
> So, yes the documentation needs updating!
>
I think you need to show up the documentation that's wrong. If you look
in BOL, the syntax is :
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
where file_name and file_id is explained as
"file_name
Is the logical name of the file shrunk. File names must conform to the
rules for identifiers. For more information, see Using Identifiers. "
"file_id
Is the identification (ID) number of the file to be shrunk. To obtain a
file ID, use the FILE_ID function or search sysfiles in the current
database."
I only see that it says you can use the LOGICAL filename or the fileID.
I don't see it mentions the physical filename anywhere?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
This is an update to my other posting. I know that it is unorthodox to post
twice but I don't know what else to do.
Below is my original posting. I had already done the items posted by the
other gentleman such as reivewing the sysfiles. I cannot get the logical
file name from anywhere either.
Again, please help! Should I just not shrink the file. The client only has
1.82 GB of free space left and there is nothing else that can be deleted.
Below is my message:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sNevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"childofthe1980s" wrote:
> Hello:
> This is an update to my other posting. I know that it is unorthodox to post
> twice but I don't know what else to do.
> Below is my original posting. I had already done the items posted by the
> other gentleman such as reivewing the sysfiles. I cannot get the logical
> file name from anywhere either.
> Again, please help! Should I just not shrink the file. The client only has
> 1.82 GB of free space left and there is nothing else that can be deleted.
> Below is my message:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message saying
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s
>|||> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
The documentation clearly states that you can use *either* the logical filename or the file id. The
documentation is correct. The command you posted had 'GPSJCLog.ldf' for the logical filename, and I
guess that you by mistake put the *physical* filename there instead of the *logical* filename. If
you show ut the output from below, we can say for sure:
SELECT name, filename FROM sysfiles
I have shrunk database files occasionally and I always used the logical filename. In addition,
here's a script (executed on my SQL Server 2000) which clearly show the usage of the *logical*
filename:
USE master
CREATE DATABASE myTestShrink ON PRIMARY
(NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
,SIZE = 1024KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Increase log file size
BACKUP DATABASE myTestShrink TO DISK = 'nul'
CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
INSERT INTO myTestShrink..test(c2)
SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
GO
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
--Empty the log
BACKUP LOG myTestShrink WITH NO_LOG
--Check the logical and physical files names
SELECT name, filename FROM myTestShrink..sysfiles
--Perform the shrink, ***based on logical filename***
USE myTestShrink
GO
DBCC SHRINKFILE('myTestShrink_log', 2)
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
USE master
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:868E9C82-C2C4-4151-BBBD-40741201E676@.microsoft.com...
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
> "childofthe1980s" wrote:
>> Hello:
>> This is an update to my other posting. I know that it is unorthodox to post
>> twice but I don't know what else to do.
>> Below is my original posting. I had already done the items posted by the
>> other gentleman such as reivewing the sysfiles. I cannot get the logical
>> file name from anywhere either.
>> Again, please help! Should I just not shrink the file. The client only has
>> 1.82 GB of free space left and there is nothing else that can be deleted.
>> Below is my message:
>> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>> analyzer in SQL 2000 against the master database.
>> What is the EXACT syntax? You see, the name of the database is JC and the
>> log file is named GPSJClog.ldf. I have tried to place both names in the
>> syntax of the statement but with no success. I keep getting a message saying
>> that the file name is not in sysfiles.
>> This is what I have tried:
>> DBCC SHRINKFILE (GPJClog.ldf , 2)
>> Please help!
>> childofthe1980s|||you were getting that error because you were running it in the master
database, not the databse you wanted to shrink the log for. the
documentation's correct.
"childofthe1980s" wrote:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
>|||Tibor Karaszi wrote:
>> You see, the support documentation on the web did not say that you
>> have to
>> take the fileID of the database--2 in the syntax above-- in order to
>> make it
>> work! You get the number 2 from running the following query against the
>> database:
>> select * from sysfiles
>> It would have been nice if this had been in the documentation!
> The documentation clearly states that you can use *either* the logical
> filename or the file id. The documentation is correct. The command you
> posted had 'GPSJCLog.ldf' for the logical filename, and I guess that you
> by mistake put the *physical* filename there instead of the *logical*
> filename. If you show ut the output from below, we can say for sure:
> SELECT name, filename FROM sysfiles
> I have shrunk database files occasionally and I always used the logical
> filename. In addition, here's a script (executed on my SQL Server 2000)
> which clearly show the usage of the *logical* filename:
> USE master
> CREATE DATABASE myTestShrink ON PRIMARY
> (NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
> ,SIZE = 1024KB , FILEGROWTH = 10%)
> LOG ON
> (NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
> , SIZE = 1024KB , FILEGROWTH = 10%)
> GO
> --Increase log file size
> BACKUP DATABASE myTestShrink TO DISK = 'nul'
> CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
> INSERT INTO myTestShrink..test(c2)
> SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
> GO
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> --Empty the log
> BACKUP LOG myTestShrink WITH NO_LOG
> --Check the logical and physical files names
> SELECT name, filename FROM myTestShrink..sysfiles
> --Perform the shrink, ***based on logical filename***
> USE myTestShrink
> GO
> DBCC SHRINKFILE('myTestShrink_log', 2)
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> USE master
>
...just to add to Tibor's response. You can retreive the logical
filename by running sp_helpdb 'YourDatabaseName'.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||No, I did all different combinations--with ldf, without ldf, against the
master, against the database, and nothing was working until I placed the
database fileID of "2" in the suntax.
So, yes the documentation needs updating!
Thank you to all who helped!
childofthe1980s
"thomarse" wrote:
> you were getting that error because you were running it in the master
> database, not the databse you wanted to shrink the log for. the
> documentation's correct.
> "childofthe1980s" wrote:
> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> > analyzer in SQL 2000 against the master database.
> >
>|||childofthe1980s wrote:
> No, I did all different combinations--with ldf, without ldf, against the
> master, against the database, and nothing was working until I placed the
> database fileID of "2" in the suntax.
> So, yes the documentation needs updating!
>
I think you need to show up the documentation that's wrong. If you look
in BOL, the syntax is :
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
where file_name and file_id is explained as
"file_name
Is the logical name of the file shrunk. File names must conform to the
rules for identifiers. For more information, see Using Identifiers. "
"file_id
Is the identification (ID) number of the file to be shrunk. To obtain a
file ID, use the FILE_ID function or search sysfiles in the current
database."
I only see that it says you can use the LOGICAL filename or the fileID.
I don't see it mentions the physical filename anywhere?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
HELP--still cannot shrink the transaction log--PLEASE HELP
Hello:
This is an update to my other posting. I know that it is unorthodox to post
twice but I don't know what else to do.
Below is my original posting. I had already done the items posted by the
other gentleman such as reivewing the sysfiles. I cannot get the logical
file name from anywhere either.
Again, please help! Should I just not shrink the file. The client only has
1.82 GB of free space left and there is nothing else that can be deleted.
Below is my message:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message sayin
g
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sNevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"childofthe1980s" wrote:
> Hello:
> This is an update to my other posting. I know that it is unorthodox to po
st
> twice but I don't know what else to do.
> Below is my original posting. I had already done the items posted by the
> other gentleman such as reivewing the sysfiles. I cannot get the logical
> file name from anywhere either.
> Again, please help! Should I just not shrink the file. The client only h
as
> 1.82 GB of free space left and there is nothing else that can be deleted.
> Below is my message:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in que
ry
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message say
ing
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s
>|||> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
The documentation clearly states that you can use *either* the logical filen
ame or the file id. The
documentation is correct. The command you posted had 'GPSJCLog.ldf' for the
logical filename, and I
guess that you by mistake put the *physical* filename there instead of the *
logical* filename. If
you show ut the output from below, we can say for sure:
SELECT name, filename FROM sysfiles
I have shrunk database files occasionally and I always used the logical file
name. In addition,
here's a script (executed on my SQL Server 2000) which clearly show the usag
e of the *logical*
filename:
USE master
CREATE DATABASE myTestShrink ON PRIMARY
(NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
,SIZE = 1024KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Increase log file size
BACKUP DATABASE myTestShrink TO DISK = 'nul'
CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
INSERT INTO myTestShrink..test(c2)
SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
GO
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles W
HERE groupid = 0
--Empty the log
BACKUP LOG myTestShrink WITH NO_LOG
--Check the logical and physical files names
SELECT name, filename FROM myTestShrink..sysfiles
--Perform the shrink, ***based on logical filename***
USE myTestShrink
GO
DBCC SHRINKFILE('myTestShrink_log', 2)
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles W
HERE groupid = 0
USE master
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:868E9C82-C2C4-4151-BBBD-40741201E676@.microsoft.com...[vbcol=seagreen]
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
> "childofthe1980s" wrote:
>|||you were getting that error because you were running it in the master
database, not the databse you wanted to shrink the log for. the
documentation's correct.
"childofthe1980s" wrote:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in que
ry
> analyzer in SQL 2000 against the master database.
>|||Tibor Karaszi wrote:
> The documentation clearly states that you can use *either* the logical
> filename or the file id. The documentation is correct. The command you
> posted had 'GPSJCLog.ldf' for the logical filename, and I guess that you
> by mistake put the *physical* filename there instead of the *logical*
> filename. If you show ut the output from below, we can say for sure:
> SELECT name, filename FROM sysfiles
> I have shrunk database files occasionally and I always used the logical
> filename. In addition, here's a script (executed on my SQL Server 2000)
> which clearly show the usage of the *logical* filename:
> USE master
> CREATE DATABASE myTestShrink ON PRIMARY
> (NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
> ,SIZE = 1024KB , FILEGROWTH = 10%)
> LOG ON
> (NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
> , SIZE = 1024KB , FILEGROWTH = 10%)
> GO
> --Increase log file size
> BACKUP DATABASE myTestShrink TO DISK = 'nul'
> CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
> INSERT INTO myTestShrink..test(c2)
> SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
> GO
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> --Empty the log
> BACKUP LOG myTestShrink WITH NO_LOG
> --Check the logical and physical files names
> SELECT name, filename FROM myTestShrink..sysfiles
> --Perform the shrink, ***based on logical filename***
> USE myTestShrink
> GO
> DBCC SHRINKFILE('myTestShrink_log', 2)
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> USE master
>
...just to add to Tibor's response. You can retreive the logical
filename by running sp_helpdb 'YourDatabaseName'.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||No, I did all different combinations--with ldf, without ldf, against the
master, against the database, and nothing was working until I placed the
database fileID of "2" in the suntax.
So, yes the documentation needs updating!
Thank you to all who helped!
childofthe1980s
"thomarse" wrote:
> you were getting that error because you were running it in the master
> database, not the databse you wanted to shrink the log for. the
> documentation's correct.
> "childofthe1980s" wrote:
>
>|||childofthe1980s wrote:
> No, I did all different combinations--with ldf, without ldf, against the
> master, against the database, and nothing was working until I placed the
> database fileID of "2" in the suntax.
> So, yes the documentation needs updating!
>
I think you need to show up the documentation that's wrong. If you look
in BOL, the syntax is :
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
where file_name and file_id is explained as
"file_name
Is the logical name of the file shrunk. File names must conform to the
rules for identifiers. For more information, see Using Identifiers. "
"file_id
Is the identification (ID) number of the file to be shrunk. To obtain a
file ID, use the FILE_ID function or search sysfiles in the current
database."
I only see that it says you can use the LOGICAL filename or the fileID.
I don't see it mentions the physical filename anywhere?
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
This is an update to my other posting. I know that it is unorthodox to post
twice but I don't know what else to do.
Below is my original posting. I had already done the items posted by the
other gentleman such as reivewing the sysfiles. I cannot get the logical
file name from anywhere either.
Again, please help! Should I just not shrink the file. The client only has
1.82 GB of free space left and there is nothing else that can be deleted.
Below is my message:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message sayin
g
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sNevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"childofthe1980s" wrote:
> Hello:
> This is an update to my other posting. I know that it is unorthodox to po
st
> twice but I don't know what else to do.
> Below is my original posting. I had already done the items posted by the
> other gentleman such as reivewing the sysfiles. I cannot get the logical
> file name from anywhere either.
> Again, please help! Should I just not shrink the file. The client only h
as
> 1.82 GB of free space left and there is nothing else that can be deleted.
> Below is my message:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in que
ry
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message say
ing
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s
>|||> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
The documentation clearly states that you can use *either* the logical filen
ame or the file id. The
documentation is correct. The command you posted had 'GPSJCLog.ldf' for the
logical filename, and I
guess that you by mistake put the *physical* filename there instead of the *
logical* filename. If
you show ut the output from below, we can say for sure:
SELECT name, filename FROM sysfiles
I have shrunk database files occasionally and I always used the logical file
name. In addition,
here's a script (executed on my SQL Server 2000) which clearly show the usag
e of the *logical*
filename:
USE master
CREATE DATABASE myTestShrink ON PRIMARY
(NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
,SIZE = 1024KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Increase log file size
BACKUP DATABASE myTestShrink TO DISK = 'nul'
CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
INSERT INTO myTestShrink..test(c2)
SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
GO
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles W
HERE groupid = 0
--Empty the log
BACKUP LOG myTestShrink WITH NO_LOG
--Check the logical and physical files names
SELECT name, filename FROM myTestShrink..sysfiles
--Perform the shrink, ***based on logical filename***
USE myTestShrink
GO
DBCC SHRINKFILE('myTestShrink_log', 2)
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles W
HERE groupid = 0
USE master
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:868E9C82-C2C4-4151-BBBD-40741201E676@.microsoft.com...[vbcol=seagreen]
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
> "childofthe1980s" wrote:
>|||you were getting that error because you were running it in the master
database, not the databse you wanted to shrink the log for. the
documentation's correct.
"childofthe1980s" wrote:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in que
ry
> analyzer in SQL 2000 against the master database.
>|||Tibor Karaszi wrote:
> The documentation clearly states that you can use *either* the logical
> filename or the file id. The documentation is correct. The command you
> posted had 'GPSJCLog.ldf' for the logical filename, and I guess that you
> by mistake put the *physical* filename there instead of the *logical*
> filename. If you show ut the output from below, we can say for sure:
> SELECT name, filename FROM sysfiles
> I have shrunk database files occasionally and I always used the logical
> filename. In addition, here's a script (executed on my SQL Server 2000)
> which clearly show the usage of the *logical* filename:
> USE master
> CREATE DATABASE myTestShrink ON PRIMARY
> (NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
> ,SIZE = 1024KB , FILEGROWTH = 10%)
> LOG ON
> (NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
> , SIZE = 1024KB , FILEGROWTH = 10%)
> GO
> --Increase log file size
> BACKUP DATABASE myTestShrink TO DISK = 'nul'
> CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
> INSERT INTO myTestShrink..test(c2)
> SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
> GO
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> --Empty the log
> BACKUP LOG myTestShrink WITH NO_LOG
> --Check the logical and physical files names
> SELECT name, filename FROM myTestShrink..sysfiles
> --Perform the shrink, ***based on logical filename***
> USE myTestShrink
> GO
> DBCC SHRINKFILE('myTestShrink_log', 2)
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> USE master
>
...just to add to Tibor's response. You can retreive the logical
filename by running sp_helpdb 'YourDatabaseName'.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||No, I did all different combinations--with ldf, without ldf, against the
master, against the database, and nothing was working until I placed the
database fileID of "2" in the suntax.
So, yes the documentation needs updating!
Thank you to all who helped!
childofthe1980s
"thomarse" wrote:
> you were getting that error because you were running it in the master
> database, not the databse you wanted to shrink the log for. the
> documentation's correct.
> "childofthe1980s" wrote:
>
>|||childofthe1980s wrote:
> No, I did all different combinations--with ldf, without ldf, against the
> master, against the database, and nothing was working until I placed the
> database fileID of "2" in the suntax.
> So, yes the documentation needs updating!
>
I think you need to show up the documentation that's wrong. If you look
in BOL, the syntax is :
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
where file_name and file_id is explained as
"file_name
Is the logical name of the file shrunk. File names must conform to the
rules for identifiers. For more information, see Using Identifiers. "
"file_id
Is the identification (ID) number of the file to be shrunk. To obtain a
file ID, use the FILE_ID function or search sysfiles in the current
database."
I only see that it says you can use the LOGICAL filename or the fileID.
I don't see it mentions the physical filename anywhere?
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
HELP--still cannot shrink the transaction log--PLEASE HELP
Hello:
This is an update to my other posting. I know that it is unorthodox to post
twice but I don't know what else to do.
Below is my original posting. I had already done the items posted by the
other gentleman such as reivewing the sysfiles. I cannot get the logical
file name from anywhere either.
Again, please help! Should I just not shrink the file. The client only has
1.82 GB of free space left and there is nothing else that can be deleted.
Below is my message:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980s
Nevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"childofthe1980s" wrote:
> Hello:
> This is an update to my other posting. I know that it is unorthodox to post
> twice but I don't know what else to do.
> Below is my original posting. I had already done the items posted by the
> other gentleman such as reivewing the sysfiles. I cannot get the logical
> file name from anywhere either.
> Again, please help! Should I just not shrink the file. The client only has
> 1.82 GB of free space left and there is nothing else that can be deleted.
> Below is my message:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message saying
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s
>
|||you were getting that error because you were running it in the master
database, not the databse you wanted to shrink the log for. the
documentation's correct.
"childofthe1980s" wrote:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
>
|||No, I did all different combinations--with ldf, without ldf, against the
master, against the database, and nothing was working until I placed the
database fileID of "2" in the suntax.
So, yes the documentation needs updating!
Thank you to all who helped!
childofthe1980s
"thomarse" wrote:
> you were getting that error because you were running it in the master
> database, not the databse you wanted to shrink the log for. the
> documentation's correct.
> "childofthe1980s" wrote:
>
This is an update to my other posting. I know that it is unorthodox to post
twice but I don't know what else to do.
Below is my original posting. I had already done the items posted by the
other gentleman such as reivewing the sysfiles. I cannot get the logical
file name from anywhere either.
Again, please help! Should I just not shrink the file. The client only has
1.82 GB of free space left and there is nothing else that can be deleted.
Below is my message:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980s
Nevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"childofthe1980s" wrote:
> Hello:
> This is an update to my other posting. I know that it is unorthodox to post
> twice but I don't know what else to do.
> Below is my original posting. I had already done the items posted by the
> other gentleman such as reivewing the sysfiles. I cannot get the logical
> file name from anywhere either.
> Again, please help! Should I just not shrink the file. The client only has
> 1.82 GB of free space left and there is nothing else that can be deleted.
> Below is my message:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message saying
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s
>
|||you were getting that error because you were running it in the master
database, not the databse you wanted to shrink the log for. the
documentation's correct.
"childofthe1980s" wrote:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
>
|||No, I did all different combinations--with ldf, without ldf, against the
master, against the database, and nothing was working until I placed the
database fileID of "2" in the suntax.
So, yes the documentation needs updating!
Thank you to all who helped!
childofthe1980s
"thomarse" wrote:
> you were getting that error because you were running it in the master
> database, not the databse you wanted to shrink the log for. the
> documentation's correct.
> "childofthe1980s" wrote:
>
Wednesday, March 7, 2012
Help:Locking...
Hello .
I am using SQL Server 2000 in order to create a multi user program that accesses data.
The problem is that multiple users will update and select data at the same time at the same table.
Is there a way to avoid deadlocks ?
I heard about two ways: using a temporary table to store data and then write the data only when the user finished the update.
and the other is using xml to write the database to a xml file that is stored locally. do the updates on the file and then after completion insert the xml file into the database.
does anybody know much about these ways? do you know where i can find code for this ?
is there a better way?
thanks !
and happy new year !yeah, according to the textbook of database, there should be 2 other ways instead of write the information to other place from the database.
1. lock all what u will lock in the transaction when u begin the transaction.
2. all kinds of resources should be locked at the same order.
hehe.
happy new year.|||Those two ways you described will help but truely it all depends on how you write your sql script. Are all the updates and deletes using cursors? Do you have begin trans and commit on all your transactions? There is no one way you can completely avoid deadlocks, but there are ways to manage it. The best way is to let us know what sql script in your environment that creates deadlock so we can better help with your situations.|||Are we talking about deadlocks or blocking here? It surely sounds that the poster is concerned about object availability when more than one connection attempts to access its data.|||There is no silver bullet to avoid deadlocking other than fully analysing the transactions you intend to commit. Using a temporary table may work, as long as your transaction does not depend upon data previously read from the permanent tables remaining unchanged during the accretion of the temporary data. XML in this regard is a complete red herring.
Essentially you control locking policy using the SET TRANSACTION ISOLATION LEVEL command and wrapping all calls with an outer transaction until all components of the unit of work are available for committing.
You need to consider whether the integrity of data reads is essential to the integrity of the intended data writes. If youre booking seats on an aircraft you must ensure no other user books your particular seat between you reading that its free and you writing that youre booking it. To make reads fully transactional with writes you use isolation level SERIALIZABLE everything you read is locked against being updated or inserted against for the duration of the transaction. Isolation level REPEATABLE READ ensures that data you have read cannot be updated by another user, but allows background inserts.
If it is not possible to extend the duration of the transaction between critical reads (i.e. you can keep your option on the flight seat open for twenty minutes) you must implement soft locking of the seat row in the permanent tables using something like a timestamp or a flag.
Isolation level READ COMMITTED ensures you can only read data that has had full transactional commit to the database. You use this read mode to ensure you never read over another users half finished work, but you have no locks of the data you have read for the purposes of subsequent writes. READ UNCOMMITTED means you read through any locks applied during any other users writes. You cannot write through any other users locks.
Particularly useful in ensuring transactional integrity is SET XACT_ABORT ON, which will cause any error anywhere in your SQL to Rollback the entire transaction.
Also be aware that if a deadlock does occur SQL will terminate one or other transaction, at random, unless you explicitly set a deadlock priority on the thread whose death you would prefer to occur.|||Yeah, use strored procedures and stroe the data locally...
don't do dynamic sql
don't open recordsets...
Read data store
manipulate data in the app
when an action is to occur...update or delete, check the records timestamp to see if someone else alread modified the record and act accordingly
if ok, exec (through a sproc) your transaction...
keep'em short
I am using SQL Server 2000 in order to create a multi user program that accesses data.
The problem is that multiple users will update and select data at the same time at the same table.
Is there a way to avoid deadlocks ?
I heard about two ways: using a temporary table to store data and then write the data only when the user finished the update.
and the other is using xml to write the database to a xml file that is stored locally. do the updates on the file and then after completion insert the xml file into the database.
does anybody know much about these ways? do you know where i can find code for this ?
is there a better way?
thanks !
and happy new year !yeah, according to the textbook of database, there should be 2 other ways instead of write the information to other place from the database.
1. lock all what u will lock in the transaction when u begin the transaction.
2. all kinds of resources should be locked at the same order.
hehe.
happy new year.|||Those two ways you described will help but truely it all depends on how you write your sql script. Are all the updates and deletes using cursors? Do you have begin trans and commit on all your transactions? There is no one way you can completely avoid deadlocks, but there are ways to manage it. The best way is to let us know what sql script in your environment that creates deadlock so we can better help with your situations.|||Are we talking about deadlocks or blocking here? It surely sounds that the poster is concerned about object availability when more than one connection attempts to access its data.|||There is no silver bullet to avoid deadlocking other than fully analysing the transactions you intend to commit. Using a temporary table may work, as long as your transaction does not depend upon data previously read from the permanent tables remaining unchanged during the accretion of the temporary data. XML in this regard is a complete red herring.
Essentially you control locking policy using the SET TRANSACTION ISOLATION LEVEL command and wrapping all calls with an outer transaction until all components of the unit of work are available for committing.
You need to consider whether the integrity of data reads is essential to the integrity of the intended data writes. If youre booking seats on an aircraft you must ensure no other user books your particular seat between you reading that its free and you writing that youre booking it. To make reads fully transactional with writes you use isolation level SERIALIZABLE everything you read is locked against being updated or inserted against for the duration of the transaction. Isolation level REPEATABLE READ ensures that data you have read cannot be updated by another user, but allows background inserts.
If it is not possible to extend the duration of the transaction between critical reads (i.e. you can keep your option on the flight seat open for twenty minutes) you must implement soft locking of the seat row in the permanent tables using something like a timestamp or a flag.
Isolation level READ COMMITTED ensures you can only read data that has had full transactional commit to the database. You use this read mode to ensure you never read over another users half finished work, but you have no locks of the data you have read for the purposes of subsequent writes. READ UNCOMMITTED means you read through any locks applied during any other users writes. You cannot write through any other users locks.
Particularly useful in ensuring transactional integrity is SET XACT_ABORT ON, which will cause any error anywhere in your SQL to Rollback the entire transaction.
Also be aware that if a deadlock does occur SQL will terminate one or other transaction, at random, unless you explicitly set a deadlock priority on the thread whose death you would prefer to occur.|||Yeah, use strored procedures and stroe the data locally...
don't do dynamic sql
don't open recordsets...
Read data store
manipulate data in the app
when an action is to occur...update or delete, check the records timestamp to see if someone else alread modified the record and act accordingly
if ok, exec (through a sproc) your transaction...
keep'em short
HELP: update a table from a text file
I have a field in a DB that I want to update with the contents of a
text file. I retrieved the contents of the text file FROM the entry
but now that I edited them, I want to put them back.
This text file has only a single entry in it. No row/column
delimeters needed.I tried using the Enterprise Manager import data
wizard but it doesn't do what I want.
I guess the sql statement would look something like this:
Update mytable
Set data = contents of textfile
WHERE data LIKE '%uniquething%'
HELP
JohnYou could set up a linked server to the text file and read the value in
TSQL. Pls have a look at the example "F. Using the Microsoft OLE DB Provider
for Jet to access a text file" at
http://msdn2.microsoft.com/en-us/library/ms190479.aspx for more info.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
text file. I retrieved the contents of the text file FROM the entry
but now that I edited them, I want to put them back.
This text file has only a single entry in it. No row/column
delimeters needed.I tried using the Enterprise Manager import data
wizard but it doesn't do what I want.
I guess the sql statement would look something like this:
Update mytable
Set data = contents of textfile
WHERE data LIKE '%uniquething%'
HELP
JohnYou could set up a linked server to the text file and read the value in
TSQL. Pls have a look at the example "F. Using the Microsoft OLE DB Provider
for Jet to access a text file" at
http://msdn2.microsoft.com/en-us/library/ms190479.aspx for more info.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
HELP: update a table from a text file
I have a field in a DB that I want to update with the contents of a
text file. I retrieved the contents of the text file FROM the entry
but now that I edited them, I want to put them back.
This text file has only a single entry in it. No row/column
delimeters needed.I tried using the Enterprise Manager import data
wizard but it doesn't do what I want.
I guess the sql statement would look something like this:
Update mytable
Set data = contents of textfile
WHERE data LIKE '%uniquething%'
HELP
John
You could set up a linked server to the text file and read the value in
TSQL. Pls have a look at the example "F. Using the Microsoft OLE DB Provider
for Jet to access a text file" at
http://msdn2.microsoft.com/en-us/library/ms190479.aspx for more info.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
text file. I retrieved the contents of the text file FROM the entry
but now that I edited them, I want to put them back.
This text file has only a single entry in it. No row/column
delimeters needed.I tried using the Enterprise Manager import data
wizard but it doesn't do what I want.
I guess the sql statement would look something like this:
Update mytable
Set data = contents of textfile
WHERE data LIKE '%uniquething%'
HELP
John
You could set up a linked server to the text file and read the value in
TSQL. Pls have a look at the example "F. Using the Microsoft OLE DB Provider
for Jet to access a text file" at
http://msdn2.microsoft.com/en-us/library/ms190479.aspx for more info.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Monday, February 27, 2012
Help: Programmatically update data from an SQLDataSource (C#)
ello all
Would someone be so kind as to save me from getting balder through pulling my hair out.
My aim is to extract data from a database using SQLDataSource, then edit the data and update the database using the SQLDataSource.
I have achieve the problem of retrieving the data from the sqlDataSource:
DataView openRemindingSeats = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
//Int32 openRemindingSeats = SqlDataSource2.Select(DataSourceSelectArguments.Empty), DataView;
foreach (DataRowView rowProduct in openReminding)
{
//Output the name and price
lbl_NumOfSeatsLeft.Text = rowProduct["Remaining"].ToString();
}
Within the sqlDataSource the sql code is as follows:
SELECT [refNumber], [refRemaining] FROM [refFlights] WHERE ([refNumber] = @.Number)
So at the moment my problems is being able to edit and update data to the same SELECTed data.
Thank you for any help that you might have...
SynDrome
Do you want to control everything from your code or you are good enough using the existing controls. If the latter suits you, you can use gridview control and bind it with your datasource and allow editing etc. If you are more interested in former, let me know and I can take you forward from there.
|||I'm fine using DataGrides etc, thats not a problem, I just need to use code to manage some of the data from a form and others... Theres only 1-2 pages where this needs to be done, but its driving me crazy.
Thanks for the reply
|||For editing, you need to have some input from the user (from textboxes, dropdownlists etc) and then use that data to update your table. This article might help you.
http://www.codeguru.com/Csharp/Csharp/cs_data/article.php/c4211/
P.S.: Keep you sanity, we programmers suffer from this syndrome a lot.
Thanx m8,
I'll have a look, now I'm still finding it hard to get the correct code for using the sqlDataSource.
For example the following code uses sqlDataSource2.Select command but I can't find anywhere that explains how to use the update code, which would be similar to the following code.
I have read through so much code and web sites but for the live of me I can find how to 'update' data using the sqlDataSource.
DataView openRemindingSeats = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
Don't you just h8 it where one simple thing can, just one bloody bit of code can hold you up,
Thanks
SynDrome
Did you have a look at this one:
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.update.aspx
|||Working through it now thanks. I'l give you I-O-U on a Pint...
Looks like I might have to changes a few things around, but it looks work able.
Thanks m8
You are welcome...and thanx for the offer but I don't drink...
Again thank you buddy.
Incase anyone else is having the same problem I feel I should return the help, to help other, and also in order to reduce the growing population of bald people lol.
First the problem, I wanted to programmatically edit data that is being managed by an SqlDataSource (C#).
First I want to extract data from a SqlDataSource programmatically, the following code does this, and if you wanted it will gather multiple record data, by use the FOREACH loop, but this example I am just extracting one record field:
DataView openRemindingSeats = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
foreach (DataRowView rowProduct in openReminding)
{
//Output the name and price
lbl_NumOfSeatsLeft.Text = rowProduct["Remaining"].ToString();
}
Now that I have the required data from the database, I can now do anything you need to it, now all you need to do is insert it back into same place within the database.
The following code is an SqlDataSource, with this I can add an "asp:ControlParameter ", this obtains data from a control, in this case I have used both txt & lbl, then using the "Name" from the "asp:ControlParameter", you can pass the varible into the "UpdateCommand" in this example "@.numSeats" .
Example.aspx
<asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDB_Home %>"
SelectCommand="SELECT FlightNumber, Num_Seat_Class1 FROM Flights)"
UpdateCommand="UPDATE Flights SET Num_Seat_Class1 = @.numSeats WHERE FlightNumber = @.flighNum">
<UpdateParameters>
<asp:Parameter Name="FlightNumber" />
<asp:ControlParameter Name="numSeats" ControlId="txt_totalNumPass" PropertyName="Text"/>
<asp:ControlParameter Name="flighNum" ControlId="lbl_flighNum" PropertyName="Text"/>
</UpdateParameters>
</asp:SqlDataSource>
To insert the the data you need an OnClick, to fire the "UpdateCommand" as shown below.
Example.aspx.cs
protected void On_Click(object sender, EventArgs e)
{
// executes the Update sql command from the SqlDataSource5
// which takes data from txt boxes and updates them into the correct class
SqlDataSource5.Update();
}
I hope this might help someone, and I also hope that you can understand the code lol.
SynDrome
HELP: Problem with naming convention.
I'm trying to update a table in one database with data from a table in
another database on the same server, but I'm having trouble with my naming
convention.
Here's the script (thanks to Russel Loski for providing it):
update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
where DB2.dbo.table1.col1 like 'text string'
When I run it I get this error:
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'DB2' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.
I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
trying to resolve the database as a server. I don't know of another way to
point to the database. Can anyone help?
Thanks in advance.
John
Hi John,
Are u able to see another server(linked server on which DB2 resides) in
sysservers table. If yes, then please use the following notation:
Servername.dbname.tablename--make it an alias and then refer the desired
column using aliasname.columnname
For e.g
select Server2.col1 from Linkedservername.DB2.dbo.table1 Server2
Manu
"John Steen" wrote:
> I'm trying to update a table in one database with data from a table in
> another database on the same server, but I'm having trouble with my naming
> convention.
> Here's the script (thanks to Russel Loski for providing it):
> update DB1.dbo.table1
> set col1 = Db2.dbo.table1.col1
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
> where DB2.dbo.table1.col1 like 'text string'
> When I run it I get this error:
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'DB2' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
> I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
> trying to resolve the database as a server. I don't know of another way to
> point to the database. Can anyone help?
> Thanks in advance.
> John
>
|||John Steen wrote:
> I'm trying to update a table in one database with data from a table in
> another database on the same server, but I'm having trouble with my naming
> convention.
> Here's the script (thanks to Russel Loski for providing it):
> update DB1.dbo.table1
> set col1 = Db2.dbo.table1.col1
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
> where DB2.dbo.table1.col1 like 'text string'
> When I run it I get this error:
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'DB2' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
> I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
> trying to resolve the database as a server. I don't know of another way to
> point to the database. Can anyone help?
> Thanks in advance.
> John
>
Use aliases.
update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1 d1
inner join
DB2.dbo.table1 d2
on d1.col2 = d2.col2
where d2.col1 like 'text string'
another database on the same server, but I'm having trouble with my naming
convention.
Here's the script (thanks to Russel Loski for providing it):
update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
where DB2.dbo.table1.col1 like 'text string'
When I run it I get this error:
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'DB2' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.
I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
trying to resolve the database as a server. I don't know of another way to
point to the database. Can anyone help?
Thanks in advance.
John
Hi John,
Are u able to see another server(linked server on which DB2 resides) in
sysservers table. If yes, then please use the following notation:
Servername.dbname.tablename--make it an alias and then refer the desired
column using aliasname.columnname
For e.g
select Server2.col1 from Linkedservername.DB2.dbo.table1 Server2
Manu
"John Steen" wrote:
> I'm trying to update a table in one database with data from a table in
> another database on the same server, but I'm having trouble with my naming
> convention.
> Here's the script (thanks to Russel Loski for providing it):
> update DB1.dbo.table1
> set col1 = Db2.dbo.table1.col1
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
> where DB2.dbo.table1.col1 like 'text string'
> When I run it I get this error:
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'DB2' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
> I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
> trying to resolve the database as a server. I don't know of another way to
> point to the database. Can anyone help?
> Thanks in advance.
> John
>
|||John Steen wrote:
> I'm trying to update a table in one database with data from a table in
> another database on the same server, but I'm having trouble with my naming
> convention.
> Here's the script (thanks to Russel Loski for providing it):
> update DB1.dbo.table1
> set col1 = Db2.dbo.table1.col1
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
> where DB2.dbo.table1.col1 like 'text string'
> When I run it I get this error:
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'DB2' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
> I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
> trying to resolve the database as a server. I don't know of another way to
> point to the database. Can anyone help?
> Thanks in advance.
> John
>
Use aliases.
update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1 d1
inner join
DB2.dbo.table1 d2
on d1.col2 = d2.col2
where d2.col1 like 'text string'
HELP: Problem with naming convention.
I'm trying to update a table in one database with data from a table in
another database on the same server, but I'm having trouble with my naming
convention.
Here's the script (thanks to Russel Loski for providing it):
update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
where DB2.dbo.table1.col1 like 'text string'
When I run it I get this error:
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'DB2' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.
I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
trying to resolve the database as a server. I don't know of another way to
point to the database. Can anyone help?
Thanks in advance.
JohnHi John,
Are u able to see another server(linked server on which DB2 resides) in
sysservers table. If yes, then please use the following notation:
Servername.dbname.tablename--make it an alias and then refer the desired
column using aliasname.columnname
For e.g
select Server2.col1 from Linkedservername.DB2.dbo.table1 Server2
Manu
"John Steen" wrote:
> I'm trying to update a table in one database with data from a table in
> another database on the same server, but I'm having trouble with my naming
> convention.
> Here's the script (thanks to Russel Loski for providing it):
> update DB1.dbo.table1
> set col1 = Db2.dbo.table1.col1
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
> where DB2.dbo.table1.col1 like 'text string'
> When I run it I get this error:
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'DB2' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
> I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it
's
> trying to resolve the database as a server. I don't know of another way t
o
> point to the database. Can anyone help?
> Thanks in advance.
> John
>|||John Steen wrote:
> I'm trying to update a table in one database with data from a table in
> another database on the same server, but I'm having trouble with my naming
> convention.
> Here's the script (thanks to Russel Loski for providing it):
> update DB1.dbo.table1
> set col1 = Db2.dbo.table1.col1
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
> where DB2.dbo.table1.col1 like 'text string'
> When I run it I get this error:
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'DB2' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
> I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it
's
> trying to resolve the database as a server. I don't know of another way t
o
> point to the database. Can anyone help?
> Thanks in advance.
> John
>
Use aliases.
update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1 d1
inner join
DB2.dbo.table1 d2
on d1.col2 = d2.col2
where d2.col1 like 'text string'
another database on the same server, but I'm having trouble with my naming
convention.
Here's the script (thanks to Russel Loski for providing it):
update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
where DB2.dbo.table1.col1 like 'text string'
When I run it I get this error:
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'DB2' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.
I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
trying to resolve the database as a server. I don't know of another way to
point to the database. Can anyone help?
Thanks in advance.
JohnHi John,
Are u able to see another server(linked server on which DB2 resides) in
sysservers table. If yes, then please use the following notation:
Servername.dbname.tablename--make it an alias and then refer the desired
column using aliasname.columnname
For e.g
select Server2.col1 from Linkedservername.DB2.dbo.table1 Server2
Manu
"John Steen" wrote:
> I'm trying to update a table in one database with data from a table in
> another database on the same server, but I'm having trouble with my naming
> convention.
> Here's the script (thanks to Russel Loski for providing it):
> update DB1.dbo.table1
> set col1 = Db2.dbo.table1.col1
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
> where DB2.dbo.table1.col1 like 'text string'
> When I run it I get this error:
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'DB2' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
> I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it
's
> trying to resolve the database as a server. I don't know of another way t
o
> point to the database. Can anyone help?
> Thanks in advance.
> John
>|||John Steen wrote:
> I'm trying to update a table in one database with data from a table in
> another database on the same server, but I'm having trouble with my naming
> convention.
> Here's the script (thanks to Russel Loski for providing it):
> update DB1.dbo.table1
> set col1 = Db2.dbo.table1.col1
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
> where DB2.dbo.table1.col1 like 'text string'
> When I run it I get this error:
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'DB2' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
> I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it
's
> trying to resolve the database as a server. I don't know of another way t
o
> point to the database. Can anyone help?
> Thanks in advance.
> John
>
Use aliases.
update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1 d1
inner join
DB2.dbo.table1 d2
on d1.col2 = d2.col2
where d2.col1 like 'text string'
HELP: Problem with naming convention.
I'm trying to update a table in one database with data from a table in
another database on the same server, but I'm having trouble with my naming
convention.
Here's the script (thanks to Russel Loski for providing it):
update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
where DB2.dbo.table1.col1 like 'text string'
When I run it I get this error:
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'DB2' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.
I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
trying to resolve the database as a server. I don't know of another way to
point to the database. Can anyone help?
Thanks in advance.
JohnHi John,
Are u able to see another server(linked server on which DB2 resides) in
sysservers table. If yes, then please use the following notation:
Servername.dbname.tablename--make it an alias and then refer the desired
column using aliasname.columnname
For e.g
select Server2.col1 from Linkedservername.DB2.dbo.table1 Server2
Manu
"John Steen" wrote:
> I'm trying to update a table in one database with data from a table in
> another database on the same server, but I'm having trouble with my naming
> convention.
> Here's the script (thanks to Russel Loski for providing it):
> update DB1.dbo.table1
> set col1 = Db2.dbo.table1.col1
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
> where DB2.dbo.table1.col1 like 'text string'
> When I run it I get this error:
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'DB2' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
> I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
> trying to resolve the database as a server. I don't know of another way to
> point to the database. Can anyone help?
> Thanks in advance.
> John
>|||John Steen wrote:
> I'm trying to update a table in one database with data from a table in
> another database on the same server, but I'm having trouble with my naming
> convention.
> Here's the script (thanks to Russel Loski for providing it):
> update DB1.dbo.table1
> set col1 = Db2.dbo.table1.col1
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
> where DB2.dbo.table1.col1 like 'text string'
> When I run it I get this error:
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'DB2' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
> I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
> trying to resolve the database as a server. I don't know of another way to
> point to the database. Can anyone help?
> Thanks in advance.
> John
>
Use aliases.
update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1 d1
inner join
DB2.dbo.table1 d2
on d1.col2 = d2.col2
where d2.col1 like 'text string'
another database on the same server, but I'm having trouble with my naming
convention.
Here's the script (thanks to Russel Loski for providing it):
update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
where DB2.dbo.table1.col1 like 'text string'
When I run it I get this error:
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'DB2' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.
I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
trying to resolve the database as a server. I don't know of another way to
point to the database. Can anyone help?
Thanks in advance.
JohnHi John,
Are u able to see another server(linked server on which DB2 resides) in
sysservers table. If yes, then please use the following notation:
Servername.dbname.tablename--make it an alias and then refer the desired
column using aliasname.columnname
For e.g
select Server2.col1 from Linkedservername.DB2.dbo.table1 Server2
Manu
"John Steen" wrote:
> I'm trying to update a table in one database with data from a table in
> another database on the same server, but I'm having trouble with my naming
> convention.
> Here's the script (thanks to Russel Loski for providing it):
> update DB1.dbo.table1
> set col1 = Db2.dbo.table1.col1
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
> where DB2.dbo.table1.col1 like 'text string'
> When I run it I get this error:
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'DB2' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
> I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
> trying to resolve the database as a server. I don't know of another way to
> point to the database. Can anyone help?
> Thanks in advance.
> John
>|||John Steen wrote:
> I'm trying to update a table in one database with data from a table in
> another database on the same server, but I'm having trouble with my naming
> convention.
> Here's the script (thanks to Russel Loski for providing it):
> update DB1.dbo.table1
> set col1 = Db2.dbo.table1.col1
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
> where DB2.dbo.table1.col1 like 'text string'
> When I run it I get this error:
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'DB2' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
> I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
> trying to resolve the database as a server. I don't know of another way to
> point to the database. Can anyone help?
> Thanks in advance.
> John
>
Use aliases.
update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1 d1
inner join
DB2.dbo.table1 d2
on d1.col2 = d2.col2
where d2.col1 like 'text string'
Friday, February 24, 2012
HELP: problem updating table from table in same server
I'm trying to update a table in one database with data from a table in
another database on the same server, but I'm having trouble with my naming
convention.
Here's the script:
update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
where DB2.dbo.table1.col1 like
'textstring'
When I run it I get this error:
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'lobbysqldbrawtest' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.
I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
trying to resolve the database as a server. I don't know of another way to
point to the database. Any suggestions?
Thanks in advance.
John
On Tue, 27 Mar 2007 19:03:20 -0700, John Steen
<moderndads(nospam)@.hotmail.com> wrote:
>I'm trying to update a table in one database with data from a table in
>another database on the same server, but I'm having trouble with my naming
>convention.
>Here's the script:
>update DB1.dbo.table1
>set col1 = Db2.dbo.table1.col1
>from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
>where DB2.dbo.table1.col1 like
>'textstring'
>When I run it I get this error:
>Server: Msg 7202, Level 11, State 2, Line 1
>Could not find server 'lobbysqldbrawtest' in sysservers. Execute
>sp_addlinkedserver to add the server to sysservers.
Hi John,
I don't see any 'lobbysqldbrawtest' in the code you posted. May I assume
that you simplified the code?
>I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
>trying to resolve the database as a server. I don't know of another way to
>point to the database. Any suggestions?
In column names, you don't repeat the server name. In fact, the database
name can be omitted as well; just the table name is enough - though I
personally prefer to use an alias. In this case, with the table names
being equal, you actually *need* to use an alias!
UPDATE dest
SET col1 = src.col1
FROM DB1.dbo.table1 AS src
INNER JOIN DB2.dbo.table1 AS dest
ON src.col2 = dest.col2
WHERE src.col1 LIKE 'textstring';
(untested)
And since there are no wildcards in 'textstring', you can replace LIKE
with = to gain some performance.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
another database on the same server, but I'm having trouble with my naming
convention.
Here's the script:
update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
where DB2.dbo.table1.col1 like
'textstring'
When I run it I get this error:
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'lobbysqldbrawtest' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.
I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
trying to resolve the database as a server. I don't know of another way to
point to the database. Any suggestions?
Thanks in advance.
John
On Tue, 27 Mar 2007 19:03:20 -0700, John Steen
<moderndads(nospam)@.hotmail.com> wrote:
>I'm trying to update a table in one database with data from a table in
>another database on the same server, but I'm having trouble with my naming
>convention.
>Here's the script:
>update DB1.dbo.table1
>set col1 = Db2.dbo.table1.col1
>from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
>where DB2.dbo.table1.col1 like
>'textstring'
>When I run it I get this error:
>Server: Msg 7202, Level 11, State 2, Line 1
>Could not find server 'lobbysqldbrawtest' in sysservers. Execute
>sp_addlinkedserver to add the server to sysservers.
Hi John,
I don't see any 'lobbysqldbrawtest' in the code you posted. May I assume
that you simplified the code?
>I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
>trying to resolve the database as a server. I don't know of another way to
>point to the database. Any suggestions?
In column names, you don't repeat the server name. In fact, the database
name can be omitted as well; just the table name is enough - though I
personally prefer to use an alias. In this case, with the table names
being equal, you actually *need* to use an alias!
UPDATE dest
SET col1 = src.col1
FROM DB1.dbo.table1 AS src
INNER JOIN DB2.dbo.table1 AS dest
ON src.col2 = dest.col2
WHERE src.col1 LIKE 'textstring';
(untested)
And since there are no wildcards in 'textstring', you can replace LIKE
with = to gain some performance.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Subscribe to:
Posts (Atom)