I added this line to a sproc yesterday:
--get rid of the product master for inactive raw-mats/packaging
DELETE FROM PRODUCT_MASTER
WHERE PRODUCT IN
(SELECT PRODUCT FROM SAPROD_PLAN.DBO.PARTMSTR
WHERE PART_STATUS IN ('I','D') AND PART_TYPE NOT IN ('F','N','K','D'))
This sql statement should have thrown an error, because there is no column named "product" in the table partmstr. Instead, all of the data in the table
product_master was deleted. Of course, this table cascade deletes to 18 other tables, so I lost a whole slew of data. No big deal there, I can get a tape backup. Just curious how it could have even happened in the first place.I added this line to a sproc yesterday:
--get rid of the product master for inactive raw-mats/packaging
DELETE FROM PRODUCT_MASTER
WHERE PRODUCT IN
(SELECT PRODUCT FROM SAPROD_PLAN.DBO.PARTMSTR
WHERE PART_STATUS IN ('I','D') AND PART_TYPE NOT IN ('F','N','K','D'))
This sql statement should have thrown an error, because there is no column named "product" in the table partmstr. Instead, all of the data in the table
product_master was deleted. Of course, this table cascade deletes to 18 other tables, so I lost a whole slew of data. No big deal there, I can get a tape backup. Just curious how it could have even happened in the first place.
u are kidding.Delete statement will raise error if any column name u mentioned in sql statement which is not in ur table.
Im 100% sure deletion will not happened in such case.|||U funny
You should post the DDL for both tables and provide some sample data.
Look at the sticky's at the top of the page, but...
USE Northwind
GO
SET NOCOUNT ON
SELECT * INTO myOrders99 FROM Orders
GO
sp_help myOrders99
GO
DELETE FROM myOrders99 WHERE Brett IN (SELECT Brett FROM myOrders99)
GO
SET NOCOUNT OFF
DROP TABLE myOrders99
GO|||Do you have a couple of PARTMSTR tables? I see this one is declared with the database name, as well as owner.|||This duplicates the problem...
CREATE PROCEDURE TMPTEST
AS
CREATE TABLE TMPPARTMSTR (
PART_CODE VARCHAR(18),
PART_TYPE CHAR(1),
PART_STATUS CHAR(1),
)
CREATE TABLE TMPPRODUCT_MASTER(
PRODUCT VARCHAR(18)
)
INSERT INTO TMPPARTMSTR (PART_CODE, PART_TYPE, PART_STATUS)
SELECT 'ABC123', 'F', 'A' UNION ALL
SELECT 'DEF123','K','A' UNION ALL
SELECT 'ABC456','R','A' UNION ALL
SELECT 'DEF456','R','I'
INSERT INTO TMPPRODUCT_MASTER(PRODUCT)
SELECT 'ABC123' UNION ALL
SELECT 'DEF123' UNION ALL
SELECT 'ABC456' UNION ALL
SELECT 'DEF456'
DELETE FROM TMPPRODUCT_MASTER
WHERE (PRODUCT IN
(SELECT PRODUCT
FROM TMPPARTMSTR
WHERE PART_STATUS IN ('I', 'D') AND PART_TYPE NOT IN ('F', 'N', 'K', 'D')))
GO
It ends up deleting all 4 rows from tmpproduct_master, when it should throw an error.|||It looks like it's coorelating the column without a lable because it's unique
SELECT * FROM TMPPRODUCT_MASTER
WHERE PRODUCT IN ( SELECT PRODUCT
FROM TMPPARTMSTR
WHERE PART_STATUS IN ('I', 'D')
AND PART_TYPE NOT IN ('F', 'N', 'K', 'D'))|||Hi,
Initially i found this query strange and thought that ur statement is not valid,but after testing it manually i found that the statement given is absolutely correct,and it even worked in the same way in oracle also..
The only way one can restrict this is by adding the table name in front of the column name
eg:
delete from deal where dealid in (select tst1.dealid from tst1 where id1=1)
where dealid is a column of deal table and id1 is the col of tst1 table..
After checking the execution plan I can see that
1.DB checks for the availability of clustered index for deal.dealid with the condition deal.dealid=deal.dealid(ordered)
2.Scans the inner query for tst1.id1=1.
one more weird thing which i have observed is
delete from deal where dealid in (select tst1.dealid from tst1 where dealid=1)
also works fine and deletes the rows from the deal table..
May be this is a bug in SQL.
regards,
Pavan.|||This duplicates the problem...
DELETE FROM TMPPRODUCT_MASTER
WHERE (PRODUCT IN
(SELECT PRODUCT
FROM TMPPARTMSTR
WHERE PART_STATUS IN ('I', 'D') AND PART_TYPE NOT IN ('F', 'N', 'K', 'D')))
GO
It ends up deleting all 4 rows from tmpproduct_master, when it should throw an error.
PRODUCT column is not in table TMPPARTMSTR and it is refered from TMPPRODUCT_MASTER .Obviously condition would be true and deleted all records.
In future always add alias name with table and refer ur column with alias name.|||It's not a bug.
But, why does this work
DELETE FROM TMPPRODUCT_MASTER
WHERE PRODUCT IN ( SELECT PART_CODE
FROM TMPPARTMSTR
WHERE PART_STATUS IN ('I', 'D')
AND PART_TYPE NOT IN ('F', 'N', 'K', 'D'))
SELECT * FROM TMPPRODUCT_MASTER
But this does not? (Not firing all the neurons today I guess)
DELETE FROM TMPPRODUCT_MASTER o
WHERE EXISTS ( SELECT *
FROM TMPPARTMSTR i
WHERE PART_STATUS IN ('I', 'D')
AND PART_TYPE NOT IN ('F', 'N', 'K', 'D')
AND i.PART_CODE = o.PRODUCT)|||PRODUCT column is not in table TMPPARTMSTR and it is refered from TMPPRODUCT_MASTER .Obviously condition would be true and deleted all records.
So, are you saying: because the subquery can't find a column named "product" in the only table in the subquery, it will go OUTSIDE of the subquery to look in other tables contained in the query? That seems counterintuitive to me, but that is what it appears to be doing.|||That's a fact JACK
Look at my SELECT Statement as a sample...
I thought it only coorelated in the predicate and not in the SELECT|||Well, between what I've read here and in BOL, I almost understand whats happening. In the future, I'll stick with the advice given here, and use tablename.columnname, even though BOL doesn't do that in some of their examples.
Learn something new every time I come here.
Thanks!|||tablename.columnname, even though BOL doesn't do that in some of their examples.
ahhh the blind dude way...
I use aliases instead
SELECT * FROM Orders o JOIN [Order Details] d ON o.OrderId = d.OrderId
Mr. dude fully qualifies everything, because, well, he's blind you see|||The blind dude codes with style!sql
Showing posts with label master. Show all posts
Showing posts with label master. Show all posts
Tuesday, March 20, 2012
Sunday, February 19, 2012
6.5 upgrade to 2000
When using the upgrade wizard to upgrade from 6.5 to 2000 how do you get logons moved to the 2000 server. Do you need to select the master database to do this? Under what conditions should you select the master database?
thanks| When using the upgrade wizard to upgrade from 6.5 to 2000 how do you get
logons
| moved to the 2000 server. Do you need to select the master database to
do this?
| Under what conditions should you select the master database?
--
From SQL Server Books online:
In the System Configuration screen, in System objects to transfer, select
the object types to transfer from SQL Server 6.5 to SQL Server 2000:
Server configuration
Login and remote login registrations and server configuration options
relevant to SQL Server 2000 are transferred as part of the version upgrade.
Hope this helps,
--
Eric Cárdenas
SQL Server support
thanks| When using the upgrade wizard to upgrade from 6.5 to 2000 how do you get
logons
| moved to the 2000 server. Do you need to select the master database to
do this?
| Under what conditions should you select the master database?
--
From SQL Server Books online:
In the System Configuration screen, in System objects to transfer, select
the object types to transfer from SQL Server 6.5 to SQL Server 2000:
Server configuration
Login and remote login registrations and server configuration options
relevant to SQL Server 2000 are transferred as part of the version upgrade.
Hope this helps,
--
Eric Cárdenas
SQL Server support
6.5 master database syslogs full
I inherited an application (or two) that run on SQL Server 6.5, which I
haven't used in years, and am having a problem. I get the error:
---
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
---
...when I... well... just about everything. If I try:
DUMP TRAN master WITH NO_LOG
...I get:
---
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
Unable to write CHECKPOINT record in database 1 because the log is out
of space.
Unable to continue logged version of DUMP TRANsaction. Please free up
some space in database 1 or use the NO_LOG option.
---
...If I try to expand the device or database, I get the "Can't allocate
space..." error. If I use "DBCC UPDATEUSAGE(0)" I get nothing, "DBCC
CHECKTABLE(syslogs)" tells me it's using one page and has 29 rows
(which is what I get if I "SELECT * FROM syslogs").
How can I expand the space available to the master database or
otherwise resolve this problem?
Thanks in advance for any help anyone can offer. I know there probably
aren't many of you still using 6.5.
Regards,
BrianHave you also tried:
DUMP TRAN master WITH TRUNCATE_ONLY
Also, consider just creating a new device and extend the log onto it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<brian.j.parker@.gmail.com> wrote in message
news:1156254945.725458.60220@.b28g2000cwb.googlegroups.com...
I inherited an application (or two) that run on SQL Server 6.5, which I
haven't used in years, and am having a problem. I get the error:
---
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
---
...when I... well... just about everything. If I try:
DUMP TRAN master WITH NO_LOG
...I get:
---
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
Unable to write CHECKPOINT record in database 1 because the log is out
of space.
Unable to continue logged version of DUMP TRANsaction. Please free up
some space in database 1 or use the NO_LOG option.
---
...If I try to expand the device or database, I get the "Can't allocate
space..." error. If I use "DBCC UPDATEUSAGE(0)" I get nothing, "DBCC
CHECKTABLE(syslogs)" tells me it's using one page and has 29 rows
(which is what I get if I "SELECT * FROM syslogs").
How can I expand the space available to the master database or
otherwise resolve this problem?
Thanks in advance for any help anyone can offer. I know there probably
aren't many of you still using 6.5.
Regards,
Brian|||Tom Moreau wrote:
> Have you also tried:
> DUMP TRAN master WITH TRUNCATE_ONLY
> Also, consider just creating a new device and extend the log onto it.
Thank you, Tom, for replying.
Using "DUMP TRAN master" with any combination of NO_LOG and/or TRUNCATE
ONLY gives me the error "Can't allocate space for object 'Syslogs' in
database 'master' because the 'logsegment' segment is full..."
Trying to create a new device in Enterprise Manager or by using "DISK
INIT" either hangs or returns the same error.
Very frustrating!... if I could just dump the log, it seems, I could
add plenty of space.|||Hmm. If possible, do a full backup of master. Also stop and start SQL
Server.
You may have to do a rebuild of master. I'd stop SQL Server, copy the
devices on which master resides to a safe place and then rebuild master.
Extend the master device. Start SQL Server in single-user mode and then
restore the backup.
Hopefully, I've got it right. It's been a very long time since I last used
6.5...
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<brian.j.parker@.gmail.com> wrote in message
news:1156257305.897687.128200@.p79g2000cwp.googlegroups.com...
Tom Moreau wrote:
> Have you also tried:
> DUMP TRAN master WITH TRUNCATE_ONLY
> Also, consider just creating a new device and extend the log onto it.
Thank you, Tom, for replying.
Using "DUMP TRAN master" with any combination of NO_LOG and/or TRUNCATE
ONLY gives me the error "Can't allocate space for object 'Syslogs' in
database 'master' because the 'logsegment' segment is full..."
Trying to create a new device in Enterprise Manager or by using "DISK
INIT" either hangs or returns the same error.
Very frustrating!... if I could just dump the log, it seems, I could
add plenty of space.|||Thank you very much for your help and advice, Tom.
Tom Moreau wrote:
> Hmm. If possible, do a full backup of master. Also stop and start SQL
> Server.
I got the same error trying to do a full backup of master. With some
reluctance, I rebooted the server, and... the problem magically cleared
up. I could truncate the log, expand the devices and the database and
log, and go on from there (get a backup, etc).
Sorry to raise a ruckus about what turned out to be an easy problem,
but I'd wished not to reboot since it's an important (what isn't?)
application and I'd felt a little paranoid I wouldn't get the server
started again.
Of course it's now a renewed priority to see if we can move the
application on SQL Server 2000! That should be much easier to manage.
Cheers,
Brian|||Well, MS keeps support for n - 1 releases. Considering that this is n - 3,
it would be a good idea. Indeed, I don't believe there is now a direct
upgrade path from 6.5 to 2005. You'll likely have to upgrade via either of
the following paths:
1) 6.5 -> 7.0; 7.0 -> 2005
2) 6.5 -> 2000; 2000 -> 2005
If you have 2000 in house, you can upgrade that part now.
If it's an "important" app, it's also important to keep the RDBMS current.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<brian.j.parker@.gmail.com> wrote in message
news:1156262141.064510.218430@.i42g2000cwa.googlegroups.com...
Thank you very much for your help and advice, Tom.
Tom Moreau wrote:
> Hmm. If possible, do a full backup of master. Also stop and start SQL
> Server.
I got the same error trying to do a full backup of master. With some
reluctance, I rebooted the server, and... the problem magically cleared
up. I could truncate the log, expand the devices and the database and
log, and go on from there (get a backup, etc).
Sorry to raise a ruckus about what turned out to be an easy problem,
but I'd wished not to reboot since it's an important (what isn't?)
application and I'd felt a little paranoid I wouldn't get the server
started again.
Of course it's now a renewed priority to see if we can move the
application on SQL Server 2000! That should be much easier to manage.
Cheers,
Brian|||(brian.j.parker@.gmail.com) writes:
> I got the same error trying to do a full backup of master. With some
> reluctance, I rebooted the server, and... the problem magically cleared
> up. I could truncate the log, expand the devices and the database and
> log, and go on from there (get a backup, etc).
Great to hear that it worked out.
BACKUP, DISK INIT etc must all fail in this situation. BACKUP will
always fail, since it wants to write to the log. DISK INIT could
work for a user database, but DISK INIT needs to write to a table
in guess which database.
I would expect NO_LOG to work, but may NO_LOG writes to master.
Oh, well, 6.5, itwasn't too bad in its days, but it is dated now.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
haven't used in years, and am having a problem. I get the error:
---
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
---
...when I... well... just about everything. If I try:
DUMP TRAN master WITH NO_LOG
...I get:
---
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
Unable to write CHECKPOINT record in database 1 because the log is out
of space.
Unable to continue logged version of DUMP TRANsaction. Please free up
some space in database 1 or use the NO_LOG option.
---
...If I try to expand the device or database, I get the "Can't allocate
space..." error. If I use "DBCC UPDATEUSAGE(0)" I get nothing, "DBCC
CHECKTABLE(syslogs)" tells me it's using one page and has 29 rows
(which is what I get if I "SELECT * FROM syslogs").
How can I expand the space available to the master database or
otherwise resolve this problem?
Thanks in advance for any help anyone can offer. I know there probably
aren't many of you still using 6.5.
Regards,
BrianHave you also tried:
DUMP TRAN master WITH TRUNCATE_ONLY
Also, consider just creating a new device and extend the log onto it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<brian.j.parker@.gmail.com> wrote in message
news:1156254945.725458.60220@.b28g2000cwb.googlegroups.com...
I inherited an application (or two) that run on SQL Server 6.5, which I
haven't used in years, and am having a problem. I get the error:
---
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
---
...when I... well... just about everything. If I try:
DUMP TRAN master WITH NO_LOG
...I get:
---
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
Unable to write CHECKPOINT record in database 1 because the log is out
of space.
Unable to continue logged version of DUMP TRANsaction. Please free up
some space in database 1 or use the NO_LOG option.
---
...If I try to expand the device or database, I get the "Can't allocate
space..." error. If I use "DBCC UPDATEUSAGE(0)" I get nothing, "DBCC
CHECKTABLE(syslogs)" tells me it's using one page and has 29 rows
(which is what I get if I "SELECT * FROM syslogs").
How can I expand the space available to the master database or
otherwise resolve this problem?
Thanks in advance for any help anyone can offer. I know there probably
aren't many of you still using 6.5.
Regards,
Brian|||Tom Moreau wrote:
> Have you also tried:
> DUMP TRAN master WITH TRUNCATE_ONLY
> Also, consider just creating a new device and extend the log onto it.
Thank you, Tom, for replying.
Using "DUMP TRAN master" with any combination of NO_LOG and/or TRUNCATE
ONLY gives me the error "Can't allocate space for object 'Syslogs' in
database 'master' because the 'logsegment' segment is full..."
Trying to create a new device in Enterprise Manager or by using "DISK
INIT" either hangs or returns the same error.
Very frustrating!... if I could just dump the log, it seems, I could
add plenty of space.|||Hmm. If possible, do a full backup of master. Also stop and start SQL
Server.
You may have to do a rebuild of master. I'd stop SQL Server, copy the
devices on which master resides to a safe place and then rebuild master.
Extend the master device. Start SQL Server in single-user mode and then
restore the backup.
Hopefully, I've got it right. It's been a very long time since I last used
6.5...
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<brian.j.parker@.gmail.com> wrote in message
news:1156257305.897687.128200@.p79g2000cwp.googlegroups.com...
Tom Moreau wrote:
> Have you also tried:
> DUMP TRAN master WITH TRUNCATE_ONLY
> Also, consider just creating a new device and extend the log onto it.
Thank you, Tom, for replying.
Using "DUMP TRAN master" with any combination of NO_LOG and/or TRUNCATE
ONLY gives me the error "Can't allocate space for object 'Syslogs' in
database 'master' because the 'logsegment' segment is full..."
Trying to create a new device in Enterprise Manager or by using "DISK
INIT" either hangs or returns the same error.
Very frustrating!... if I could just dump the log, it seems, I could
add plenty of space.|||Thank you very much for your help and advice, Tom.
Tom Moreau wrote:
> Hmm. If possible, do a full backup of master. Also stop and start SQL
> Server.
I got the same error trying to do a full backup of master. With some
reluctance, I rebooted the server, and... the problem magically cleared
up. I could truncate the log, expand the devices and the database and
log, and go on from there (get a backup, etc).
Sorry to raise a ruckus about what turned out to be an easy problem,
but I'd wished not to reboot since it's an important (what isn't?)
application and I'd felt a little paranoid I wouldn't get the server
started again.
Of course it's now a renewed priority to see if we can move the
application on SQL Server 2000! That should be much easier to manage.
Cheers,
Brian|||Well, MS keeps support for n - 1 releases. Considering that this is n - 3,
it would be a good idea. Indeed, I don't believe there is now a direct
upgrade path from 6.5 to 2005. You'll likely have to upgrade via either of
the following paths:
1) 6.5 -> 7.0; 7.0 -> 2005
2) 6.5 -> 2000; 2000 -> 2005
If you have 2000 in house, you can upgrade that part now.
If it's an "important" app, it's also important to keep the RDBMS current.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<brian.j.parker@.gmail.com> wrote in message
news:1156262141.064510.218430@.i42g2000cwa.googlegroups.com...
Thank you very much for your help and advice, Tom.
Tom Moreau wrote:
> Hmm. If possible, do a full backup of master. Also stop and start SQL
> Server.
I got the same error trying to do a full backup of master. With some
reluctance, I rebooted the server, and... the problem magically cleared
up. I could truncate the log, expand the devices and the database and
log, and go on from there (get a backup, etc).
Sorry to raise a ruckus about what turned out to be an easy problem,
but I'd wished not to reboot since it's an important (what isn't?)
application and I'd felt a little paranoid I wouldn't get the server
started again.
Of course it's now a renewed priority to see if we can move the
application on SQL Server 2000! That should be much easier to manage.
Cheers,
Brian|||(brian.j.parker@.gmail.com) writes:
> I got the same error trying to do a full backup of master. With some
> reluctance, I rebooted the server, and... the problem magically cleared
> up. I could truncate the log, expand the devices and the database and
> log, and go on from there (get a backup, etc).
Great to hear that it worked out.
BACKUP, DISK INIT etc must all fail in this situation. BACKUP will
always fail, since it wants to write to the log. DISK INIT could
work for a user database, but DISK INIT needs to write to a table
in guess which database.
I would expect NO_LOG to work, but may NO_LOG writes to master.
Oh, well, 6.5, itwasn't too bad in its days, but it is dated now.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
6.5 master database syslogs full
I inherited an application (or two) that run on SQL Server 6.5, which I
haven't used in years, and am having a problem. I get the error:
---
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
---
...when I... well... just about everything. If I try:
DUMP TRAN master WITH NO_LOG
...I get:
---
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
Unable to write CHECKPOINT record in database 1 because the log is out
of space.
Unable to continue logged version of DUMP TRANsaction. Please free up
some space in database 1 or use the NO_LOG option.
---
...If I try to expand the device or database, I get the "Can't allocate
space..." error. If I use "DBCC UPDATEUSAGE(0)" I get nothing, "DBCC
CHECKTABLE(syslogs)" tells me it's using one page and has 29 rows
(which is what I get if I "SELECT * FROM syslogs").
How can I expand the space available to the master database or
otherwise resolve this problem?
Thanks in advance for any help anyone can offer. I know there probably
aren't many of you still using 6.5.
Regards,
BrianHave you also tried:
DUMP TRAN master WITH TRUNCATE_ONLY
Also, consider just creating a new device and extend the log onto it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<brian.j.parker@.gmail.com> wrote in message
news:1156254945.725458.60220@.b28g2000cwb.googlegroups.com...
I inherited an application (or two) that run on SQL Server 6.5, which I
haven't used in years, and am having a problem. I get the error:
---
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
---
...when I... well... just about everything. If I try:
DUMP TRAN master WITH NO_LOG
...I get:
---
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
Unable to write CHECKPOINT record in database 1 because the log is out
of space.
Unable to continue logged version of DUMP TRANsaction. Please free up
some space in database 1 or use the NO_LOG option.
---
...If I try to expand the device or database, I get the "Can't allocate
space..." error. If I use "DBCC UPDATEUSAGE(0)" I get nothing, "DBCC
CHECKTABLE(syslogs)" tells me it's using one page and has 29 rows
(which is what I get if I "SELECT * FROM syslogs").
How can I expand the space available to the master database or
otherwise resolve this problem?
Thanks in advance for any help anyone can offer. I know there probably
aren't many of you still using 6.5.
Regards,
Brian|||Tom Moreau wrote:
> Have you also tried:
> DUMP TRAN master WITH TRUNCATE_ONLY
> Also, consider just creating a new device and extend the log onto it.
Thank you, Tom, for replying.
Using "DUMP TRAN master" with any combination of NO_LOG and/or TRUNCATE
ONLY gives me the error "Can't allocate space for object 'Syslogs' in
database 'master' because the 'logsegment' segment is full..."
Trying to create a new device in Enterprise Manager or by using "DISK
INIT" either hangs or returns the same error.
Very frustrating!... if I could just dump the log, it seems, I could
add plenty of space.|||Hmm. If possible, do a full backup of master. Also stop and start SQL
Server.
You may have to do a rebuild of master. I'd stop SQL Server, copy the
devices on which master resides to a safe place and then rebuild master.
Extend the master device. Start SQL Server in single-user mode and then
restore the backup.
Hopefully, I've got it right. It's been a very long time since I last used
6.5...
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<brian.j.parker@.gmail.com> wrote in message
news:1156257305.897687.128200@.p79g2000cwp.googlegroups.com...
Tom Moreau wrote:
> Have you also tried:
> DUMP TRAN master WITH TRUNCATE_ONLY
> Also, consider just creating a new device and extend the log onto it.
Thank you, Tom, for replying.
Using "DUMP TRAN master" with any combination of NO_LOG and/or TRUNCATE
ONLY gives me the error "Can't allocate space for object 'Syslogs' in
database 'master' because the 'logsegment' segment is full..."
Trying to create a new device in Enterprise Manager or by using "DISK
INIT" either hangs or returns the same error.
Very frustrating!... if I could just dump the log, it seems, I could
add plenty of space.|||Thank you very much for your help and advice, Tom.
Tom Moreau wrote:
> Hmm. If possible, do a full backup of master. Also stop and start SQL
> Server.
I got the same error trying to do a full backup of master. With some
reluctance, I rebooted the server, and... the problem magically cleared
up. I could truncate the log, expand the devices and the database and
log, and go on from there (get a backup, etc).
Sorry to raise a ruckus about what turned out to be an easy problem,
but I'd wished not to reboot since it's an important (what isn't?)
application and I'd felt a little paranoid I wouldn't get the server
started again.
Of course it's now a renewed priority to see if we can move the
application on SQL Server 2000! That should be much easier to manage.
Cheers,
Brian|||Well, MS keeps support for n - 1 releases. Considering that this is n - 3,
it would be a good idea. Indeed, I don't believe there is now a direct
upgrade path from 6.5 to 2005. You'll likely have to upgrade via either of
the following paths:
1) 6.5 -> 7.0; 7.0 -> 2005
2) 6.5 -> 2000; 2000 -> 2005
If you have 2000 in house, you can upgrade that part now.
If it's an "important" app, it's also important to keep the RDBMS current.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<brian.j.parker@.gmail.com> wrote in message
news:1156262141.064510.218430@.i42g2000cwa.googlegroups.com...
Thank you very much for your help and advice, Tom.
Tom Moreau wrote:
> Hmm. If possible, do a full backup of master. Also stop and start SQL
> Server.
I got the same error trying to do a full backup of master. With some
reluctance, I rebooted the server, and... the problem magically cleared
up. I could truncate the log, expand the devices and the database and
log, and go on from there (get a backup, etc).
Sorry to raise a ruckus about what turned out to be an easy problem,
but I'd wished not to reboot since it's an important (what isn't?)
application and I'd felt a little paranoid I wouldn't get the server
started again.
Of course it's now a renewed priority to see if we can move the
application on SQL Server 2000! That should be much easier to manage.
Cheers,
Brian|||(brian.j.parker@.gmail.com) writes:
> I got the same error trying to do a full backup of master. With some
> reluctance, I rebooted the server, and... the problem magically cleared
> up. I could truncate the log, expand the devices and the database and
> log, and go on from there (get a backup, etc).
Great to hear that it worked out.
BACKUP, DISK INIT etc must all fail in this situation. BACKUP will
always fail, since it wants to write to the log. DISK INIT could
work for a user database, but DISK INIT needs to write to a table
in guess which database.
I would expect NO_LOG to work, but may NO_LOG writes to master.
Oh, well, 6.5, itwasn't too bad in its days, but it is dated now.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
haven't used in years, and am having a problem. I get the error:
---
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
---
...when I... well... just about everything. If I try:
DUMP TRAN master WITH NO_LOG
...I get:
---
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
Unable to write CHECKPOINT record in database 1 because the log is out
of space.
Unable to continue logged version of DUMP TRANsaction. Please free up
some space in database 1 or use the NO_LOG option.
---
...If I try to expand the device or database, I get the "Can't allocate
space..." error. If I use "DBCC UPDATEUSAGE(0)" I get nothing, "DBCC
CHECKTABLE(syslogs)" tells me it's using one page and has 29 rows
(which is what I get if I "SELECT * FROM syslogs").
How can I expand the space available to the master database or
otherwise resolve this problem?
Thanks in advance for any help anyone can offer. I know there probably
aren't many of you still using 6.5.
Regards,
BrianHave you also tried:
DUMP TRAN master WITH TRUNCATE_ONLY
Also, consider just creating a new device and extend the log onto it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<brian.j.parker@.gmail.com> wrote in message
news:1156254945.725458.60220@.b28g2000cwb.googlegroups.com...
I inherited an application (or two) that run on SQL Server 6.5, which I
haven't used in years, and am having a problem. I get the error:
---
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
---
...when I... well... just about everything. If I try:
DUMP TRAN master WITH NO_LOG
...I get:
---
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
Unable to write CHECKPOINT record in database 1 because the log is out
of space.
Unable to continue logged version of DUMP TRANsaction. Please free up
some space in database 1 or use the NO_LOG option.
---
...If I try to expand the device or database, I get the "Can't allocate
space..." error. If I use "DBCC UPDATEUSAGE(0)" I get nothing, "DBCC
CHECKTABLE(syslogs)" tells me it's using one page and has 29 rows
(which is what I get if I "SELECT * FROM syslogs").
How can I expand the space available to the master database or
otherwise resolve this problem?
Thanks in advance for any help anyone can offer. I know there probably
aren't many of you still using 6.5.
Regards,
Brian|||Tom Moreau wrote:
> Have you also tried:
> DUMP TRAN master WITH TRUNCATE_ONLY
> Also, consider just creating a new device and extend the log onto it.
Thank you, Tom, for replying.
Using "DUMP TRAN master" with any combination of NO_LOG and/or TRUNCATE
ONLY gives me the error "Can't allocate space for object 'Syslogs' in
database 'master' because the 'logsegment' segment is full..."
Trying to create a new device in Enterprise Manager or by using "DISK
INIT" either hangs or returns the same error.
Very frustrating!... if I could just dump the log, it seems, I could
add plenty of space.|||Hmm. If possible, do a full backup of master. Also stop and start SQL
Server.
You may have to do a rebuild of master. I'd stop SQL Server, copy the
devices on which master resides to a safe place and then rebuild master.
Extend the master device. Start SQL Server in single-user mode and then
restore the backup.
Hopefully, I've got it right. It's been a very long time since I last used
6.5...
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<brian.j.parker@.gmail.com> wrote in message
news:1156257305.897687.128200@.p79g2000cwp.googlegroups.com...
Tom Moreau wrote:
> Have you also tried:
> DUMP TRAN master WITH TRUNCATE_ONLY
> Also, consider just creating a new device and extend the log onto it.
Thank you, Tom, for replying.
Using "DUMP TRAN master" with any combination of NO_LOG and/or TRUNCATE
ONLY gives me the error "Can't allocate space for object 'Syslogs' in
database 'master' because the 'logsegment' segment is full..."
Trying to create a new device in Enterprise Manager or by using "DISK
INIT" either hangs or returns the same error.
Very frustrating!... if I could just dump the log, it seems, I could
add plenty of space.|||Thank you very much for your help and advice, Tom.
Tom Moreau wrote:
> Hmm. If possible, do a full backup of master. Also stop and start SQL
> Server.
I got the same error trying to do a full backup of master. With some
reluctance, I rebooted the server, and... the problem magically cleared
up. I could truncate the log, expand the devices and the database and
log, and go on from there (get a backup, etc).
Sorry to raise a ruckus about what turned out to be an easy problem,
but I'd wished not to reboot since it's an important (what isn't?)
application and I'd felt a little paranoid I wouldn't get the server
started again.
Of course it's now a renewed priority to see if we can move the
application on SQL Server 2000! That should be much easier to manage.
Cheers,
Brian|||Well, MS keeps support for n - 1 releases. Considering that this is n - 3,
it would be a good idea. Indeed, I don't believe there is now a direct
upgrade path from 6.5 to 2005. You'll likely have to upgrade via either of
the following paths:
1) 6.5 -> 7.0; 7.0 -> 2005
2) 6.5 -> 2000; 2000 -> 2005
If you have 2000 in house, you can upgrade that part now.
If it's an "important" app, it's also important to keep the RDBMS current.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<brian.j.parker@.gmail.com> wrote in message
news:1156262141.064510.218430@.i42g2000cwa.googlegroups.com...
Thank you very much for your help and advice, Tom.
Tom Moreau wrote:
> Hmm. If possible, do a full backup of master. Also stop and start SQL
> Server.
I got the same error trying to do a full backup of master. With some
reluctance, I rebooted the server, and... the problem magically cleared
up. I could truncate the log, expand the devices and the database and
log, and go on from there (get a backup, etc).
Sorry to raise a ruckus about what turned out to be an easy problem,
but I'd wished not to reboot since it's an important (what isn't?)
application and I'd felt a little paranoid I wouldn't get the server
started again.
Of course it's now a renewed priority to see if we can move the
application on SQL Server 2000! That should be much easier to manage.
Cheers,
Brian|||(brian.j.parker@.gmail.com) writes:
> I got the same error trying to do a full backup of master. With some
> reluctance, I rebooted the server, and... the problem magically cleared
> up. I could truncate the log, expand the devices and the database and
> log, and go on from there (get a backup, etc).
Great to hear that it worked out.
BACKUP, DISK INIT etc must all fail in this situation. BACKUP will
always fail, since it wants to write to the log. DISK INIT could
work for a user database, but DISK INIT needs to write to a table
in guess which database.
I would expect NO_LOG to work, but may NO_LOG writes to master.
Oh, well, 6.5, itwasn't too bad in its days, but it is dated now.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
6.5 master database syslogs full
I inherited an application (or two) that run on SQL Server 6.5, which I
haven't used in years, and am having a problem. I get the error:
Thank you, Tom, for replying.
Thank you, Tom, for replying.
I got the same error trying to do a full backup of master. With some
reluctance, I rebooted the server, and... the problem magically cleared
up. I could truncate the log, expand the devices and the database and
log, and go on from there (get a backup, etc).
I got the same error trying to do a full backup of master. With some
reluctance, I rebooted the server, and... the problem magically cleared
up. I could truncate the log, expand the devices and the database and
log, and go on from there (get a backup, etc).
Great to hear that it worked out.
haven't used in years, and am having a problem. I get the error:
----------------------
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
----------------------
...when I... well... just about everything. If I try:
DUMP TRAN master WITH NO_LOG
...I get:
----------------------
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
Unable to write CHECKPOINT record in database 1 because the log is out
of space.
Unable to continue logged version of DUMP TRANsaction. Please free up
some space in database 1 or use the NO_LOG option.
----------------------
...If I try to expand the device or database, I get the "Can't allocate
space..." error. If I use "DBCC UPDATEUSAGE(0)" I get nothing, "DBCC
CHECKTABLE(syslogs)" tells me it's using one page and has 29 rows
(which is what I get if I "SELECT * FROM syslogs").
How can I expand the space available to the master database or
otherwise resolve this problem?
Thanks in advance for any help anyone can offer. I know there probably
aren't many of you still using 6.5.
Regards,
BrianHave you also tried:
DUMP TRAN master WITH TRUNCATE_ONLY
Also, consider just creating a new device and extend the log onto it.
--
Tom
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<brian.j.parker@.gmail.comwrote in message
news:1156254945.725458.60220@.b28g2000cwb.googlegro ups.com...
I inherited an application (or two) that run on SQL Server 6.5, which I
haven't used in years, and am having a problem. I get the error:
----------------------
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
----------------------
...when I... well... just about everything. If I try:
DUMP TRAN master WITH NO_LOG
...I get:
----------------------
Can't allocate space for object 'Syslogs' in database 'master' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or
sp_extendsegment to increase the size of the segment.
Unable to write CHECKPOINT record in database 1 because the log is out
of space.
Unable to continue logged version of DUMP TRANsaction. Please free up
some space in database 1 or use the NO_LOG option.
----------------------
...If I try to expand the device or database, I get the "Can't allocate
space..." error. If I use "DBCC UPDATEUSAGE(0)" I get nothing, "DBCC
CHECKTABLE(syslogs)" tells me it's using one page and has 29 rows
(which is what I get if I "SELECT * FROM syslogs").
How can I expand the space available to the master database or
otherwise resolve this problem?
Thanks in advance for any help anyone can offer. I know there probably
aren't many of you still using 6.5.
Regards,
Brian|||Tom Moreau wrote:
Quote:
Originally Posted by
Have you also tried:
>
DUMP TRAN master WITH TRUNCATE_ONLY
>
Also, consider just creating a new device and extend the log onto it.
Thank you, Tom, for replying.
Using "DUMP TRAN master" with any combination of NO_LOG and/or TRUNCATE
ONLY gives me the error "Can't allocate space for object 'Syslogs' in
database 'master' because the 'logsegment' segment is full..."
Trying to create a new device in Enterprise Manager or by using "DISK
INIT" either hangs or returns the same error.
Very frustrating!... if I could just dump the log, it seems, I could
add plenty of space.|||Hmm. If possible, do a full backup of master. Also stop and start SQL
Server.
You may have to do a rebuild of master. I'd stop SQL Server, copy the
devices on which master resides to a safe place and then rebuild master.
Extend the master device. Start SQL Server in single-user mode and then
restore the backup.
Hopefully, I've got it right. It's been a very long time since I last used
6.5...
--
Tom
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<brian.j.parker@.gmail.comwrote in message
news:1156257305.897687.128200@.p79g2000cwp.googlegr oups.com...
Tom Moreau wrote:
Quote:
Originally Posted by
Have you also tried:
>
DUMP TRAN master WITH TRUNCATE_ONLY
>
Also, consider just creating a new device and extend the log onto it.
Thank you, Tom, for replying.
Using "DUMP TRAN master" with any combination of NO_LOG and/or TRUNCATE
ONLY gives me the error "Can't allocate space for object 'Syslogs' in
database 'master' because the 'logsegment' segment is full..."
Trying to create a new device in Enterprise Manager or by using "DISK
INIT" either hangs or returns the same error.
Very frustrating!... if I could just dump the log, it seems, I could
add plenty of space.|||Thank you very much for your help and advice, Tom.
Tom Moreau wrote:
Quote:
Originally Posted by
Hmm. If possible, do a full backup of master. Also stop and start SQL
Server.
I got the same error trying to do a full backup of master. With some
reluctance, I rebooted the server, and... the problem magically cleared
up. I could truncate the log, expand the devices and the database and
log, and go on from there (get a backup, etc).
Sorry to raise a ruckus about what turned out to be an easy problem,
but I'd wished not to reboot since it's an important (what isn't?)
application and I'd felt a little paranoid I wouldn't get the server
started again.
Of course it's now a renewed priority to see if we can move the
application on SQL Server 2000! That should be much easier to manage.
Cheers,
Brian|||Well, MS keeps support for n - 1 releases. Considering that this is n - 3,
it would be a good idea. Indeed, I don't believe there is now a direct
upgrade path from 6.5 to 2005. You'll likely have to upgrade via either of
the following paths:
1) 6.5 -7.0; 7.0 -2005
2) 6.5 -2000; 2000 -2005
If you have 2000 in house, you can upgrade that part now.
If it's an "important" app, it's also important to keep the RDBMS current.
--
Tom
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<brian.j.parker@.gmail.comwrote in message
news:1156262141.064510.218430@.i42g2000cwa.googlegr oups.com...
Thank you very much for your help and advice, Tom.
Tom Moreau wrote:
Quote:
Originally Posted by
Hmm. If possible, do a full backup of master. Also stop and start SQL
Server.
I got the same error trying to do a full backup of master. With some
reluctance, I rebooted the server, and... the problem magically cleared
up. I could truncate the log, expand the devices and the database and
log, and go on from there (get a backup, etc).
Sorry to raise a ruckus about what turned out to be an easy problem,
but I'd wished not to reboot since it's an important (what isn't?)
application and I'd felt a little paranoid I wouldn't get the server
started again.
Of course it's now a renewed priority to see if we can move the
application on SQL Server 2000! That should be much easier to manage.
Cheers,
Brian|||(brian.j.parker@.gmail.com) writes:
Quote:
Originally Posted by
I got the same error trying to do a full backup of master. With some
reluctance, I rebooted the server, and... the problem magically cleared
up. I could truncate the log, expand the devices and the database and
log, and go on from there (get a backup, etc).
Great to hear that it worked out.
BACKUP, DISK INIT etc must all fail in this situation. BACKUP will
always fail, since it wants to write to the log. DISK INIT could
work for a user database, but DISK INIT needs to write to a table
in guess which database.
I would expect NO_LOG to work, but may NO_LOG writes to master.
Oh, well, 6.5, itwasn't too bad in its days, but it is dated now.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)