I am running SQL Server 2000 SP3a on Windows 2003
I get the following error when I run DBCC SHRINKDATABASE
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
If I shrink the database using Enterprise Manager, I can shrink all the
files, including the log, except the MDF (PRIMARY) file.
I get the following error when I try to shrink this file:
Error 0 : This server has been connected.You must reconnect to perform this
operation.
I have re-booted the server , but get the same error messages.
I have run DBCC CHECKDB and DBCC CHECKFILEGROUP , and 0 errors are reported
Has anyone any ideas how to resolve this problem?
--
DuncanJTry setting single-user mode.
DuncanJ wrote:
> I am running SQL Server 2000 SP3a on Windows 2003
> I get the following error when I run DBCC SHRINKDATABASE
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> If I shrink the database using Enterprise Manager, I can shrink all the
> files, including the log, except the MDF (PRIMARY) file.
> I get the following error when I try to shrink this file:
> Error 0 : This server has been connected.You must reconnect to perform this
> operation.
> I have re-booted the server , but get the same error messages.
> I have run DBCC CHECKDB and DBCC CHECKFILEGROUP , and 0 errors are reported
> Has anyone any ideas how to resolve this problem?
> --
> DuncanJ
Showing posts with label sp3a. Show all posts
Showing posts with label sp3a. Show all posts
Tuesday, March 27, 2012
Thursday, March 8, 2012
dbcc freeprocache/dropcleanbuffers - please help
sql server 2000 Enterprise sp3a
I am running a loop in Query Analyzer as follows (pseudo coded) for some
testing
that I'm doing.
declare @.var int, @.id int
set @.var = 1
while (@.var < 4)
begin
begin tran
dbcc freeproccache
dbcc dropcleanbuffers
print 'start delete at : ' + cast (getdate() as varchar)
set @.id = 10
delete from mytable where @.id = 10
print 'end delete at : ' + cast (getdate() as varchar)
rollback tran
set @.var = @.var + 1
end
I'm getting weird execution times for each delete. For instance,
the first pass shows me an execution time of around 500ms which
I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
3ms. I used set statistics io on to check the reads and the number
of physical reads drops. I don't know why the dbcc commands
aren't working. Is it the transaction? Any ideas? There is no
other activity on the server... it's my personal box. Please help.You could try CHECKPOINT before the DROPCLEANBUFFER. Note the word "clean", hence adding a
checkpoint will result in all pages clean in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dodo Lurker" <none@.noemailplease> wrote in message
news:P9OdnXgvFv_MppLYnZ2dnUVZ_tidnZ2d@.comcast.com...
> sql server 2000 Enterprise sp3a
> I am running a loop in Query Analyzer as follows (pseudo coded) for some
> testing
> that I'm doing.
>
> declare @.var int, @.id int
> set @.var = 1
> while (@.var < 4)
> begin
> begin tran
> dbcc freeproccache
> dbcc dropcleanbuffers
> print 'start delete at : ' + cast (getdate() as varchar)
> set @.id = 10
> delete from mytable where @.id = 10
> print 'end delete at : ' + cast (getdate() as varchar)
> rollback tran
> set @.var = @.var + 1
> end
>
> I'm getting weird execution times for each delete. For instance,
> the first pass shows me an execution time of around 500ms which
> I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
> 3ms. I used set statistics io on to check the reads and the number
> of physical reads drops. I don't know why the dbcc commands
> aren't working. Is it the transaction? Any ideas? There is no
> other activity on the server... it's my personal box. Please help.
>|||Hi Tibor
Thank you
I ended up taking the transaction out (the begin transaction and rollback).
What I did instead
- inserted the rows into a temp table
- performed my delete
- re-inserted the rows
I repeated the delete/re-insert within the loop
When I did this, my problem went away. What do you think the transaction
was doing or not
doing? Would it be a log cache issue?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6ZDWR72GHA.4632@.TK2MSFTNGP03.phx.gbl...
> You could try CHECKPOINT before the DROPCLEANBUFFER. Note the word
"clean", hence adding a
> checkpoint will result in all pages clean in the database.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:P9OdnXgvFv_MppLYnZ2dnUVZ_tidnZ2d@.comcast.com...
> > sql server 2000 Enterprise sp3a
> >
> > I am running a loop in Query Analyzer as follows (pseudo coded) for some
> > testing
> > that I'm doing.
> >
> >
> > declare @.var int, @.id int
> >
> > set @.var = 1
> > while (@.var < 4)
> > begin
> >
> > begin tran
> >
> > dbcc freeproccache
> > dbcc dropcleanbuffers
> >
> > print 'start delete at : ' + cast (getdate() as varchar)
> > set @.id = 10
> > delete from mytable where @.id = 10
> > print 'end delete at : ' + cast (getdate() as varchar)
> >
> > rollback tran
> >
> > set @.var = @.var + 1
> > end
> >
> >
> > I'm getting weird execution times for each delete. For instance,
> > the first pass shows me an execution time of around 500ms which
> > I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
> > 3ms. I used set statistics io on to check the reads and the number
> > of physical reads drops. I don't know why the dbcc commands
> > aren't working. Is it the transaction? Any ideas? There is no
> > other activity on the server... it's my personal box. Please help.
> >
> >
>
I am running a loop in Query Analyzer as follows (pseudo coded) for some
testing
that I'm doing.
declare @.var int, @.id int
set @.var = 1
while (@.var < 4)
begin
begin tran
dbcc freeproccache
dbcc dropcleanbuffers
print 'start delete at : ' + cast (getdate() as varchar)
set @.id = 10
delete from mytable where @.id = 10
print 'end delete at : ' + cast (getdate() as varchar)
rollback tran
set @.var = @.var + 1
end
I'm getting weird execution times for each delete. For instance,
the first pass shows me an execution time of around 500ms which
I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
3ms. I used set statistics io on to check the reads and the number
of physical reads drops. I don't know why the dbcc commands
aren't working. Is it the transaction? Any ideas? There is no
other activity on the server... it's my personal box. Please help.You could try CHECKPOINT before the DROPCLEANBUFFER. Note the word "clean", hence adding a
checkpoint will result in all pages clean in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dodo Lurker" <none@.noemailplease> wrote in message
news:P9OdnXgvFv_MppLYnZ2dnUVZ_tidnZ2d@.comcast.com...
> sql server 2000 Enterprise sp3a
> I am running a loop in Query Analyzer as follows (pseudo coded) for some
> testing
> that I'm doing.
>
> declare @.var int, @.id int
> set @.var = 1
> while (@.var < 4)
> begin
> begin tran
> dbcc freeproccache
> dbcc dropcleanbuffers
> print 'start delete at : ' + cast (getdate() as varchar)
> set @.id = 10
> delete from mytable where @.id = 10
> print 'end delete at : ' + cast (getdate() as varchar)
> rollback tran
> set @.var = @.var + 1
> end
>
> I'm getting weird execution times for each delete. For instance,
> the first pass shows me an execution time of around 500ms which
> I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
> 3ms. I used set statistics io on to check the reads and the number
> of physical reads drops. I don't know why the dbcc commands
> aren't working. Is it the transaction? Any ideas? There is no
> other activity on the server... it's my personal box. Please help.
>|||Hi Tibor
Thank you
I ended up taking the transaction out (the begin transaction and rollback).
What I did instead
- inserted the rows into a temp table
- performed my delete
- re-inserted the rows
I repeated the delete/re-insert within the loop
When I did this, my problem went away. What do you think the transaction
was doing or not
doing? Would it be a log cache issue?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6ZDWR72GHA.4632@.TK2MSFTNGP03.phx.gbl...
> You could try CHECKPOINT before the DROPCLEANBUFFER. Note the word
"clean", hence adding a
> checkpoint will result in all pages clean in the database.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:P9OdnXgvFv_MppLYnZ2dnUVZ_tidnZ2d@.comcast.com...
> > sql server 2000 Enterprise sp3a
> >
> > I am running a loop in Query Analyzer as follows (pseudo coded) for some
> > testing
> > that I'm doing.
> >
> >
> > declare @.var int, @.id int
> >
> > set @.var = 1
> > while (@.var < 4)
> > begin
> >
> > begin tran
> >
> > dbcc freeproccache
> > dbcc dropcleanbuffers
> >
> > print 'start delete at : ' + cast (getdate() as varchar)
> > set @.id = 10
> > delete from mytable where @.id = 10
> > print 'end delete at : ' + cast (getdate() as varchar)
> >
> > rollback tran
> >
> > set @.var = @.var + 1
> > end
> >
> >
> > I'm getting weird execution times for each delete. For instance,
> > the first pass shows me an execution time of around 500ms which
> > I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
> > 3ms. I used set statistics io on to check the reads and the number
> > of physical reads drops. I don't know why the dbcc commands
> > aren't working. Is it the transaction? Any ideas? There is no
> > other activity on the server... it's my personal box. Please help.
> >
> >
>
Wednesday, March 7, 2012
DBCC DBREINDEX versus INDEXDEFRAG.
(SQL Server 2000, SP3a)
Hello all!
For our production environment, we have a decent window which to run some maintenance
scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX, is that
essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX do everything a
INDEXDEFRAG does (and possibly more)? Or are there situations where it'd be beneficial to
run *both*?
Thanks!
John PetersonAs my understanding goes, it is better to go for DBCC DBREINDEX command, as it takes care of all the table and view indexes, and any constraints on the columns are automatically taken care of. One can use, DBCC INDEXDEFRAG, if the indexes needs to be used on-line, which allows any DML operations.
So if locking of rows during index rebuliding is not a criteria, I guess you can go with DBCC DBREINDEX.
Am not sure, if I was able to answer yur query.
Thanks
GYK
-- John Peterson wrote: --
(SQL Server 2000, SP3a)
Hello all!
For our production environment, we have a decent window which to run some maintenance
scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX, is that
essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX do everything a
INDEXDEFRAG does (and possibly more)? Or are there situations where it'd be beneficial to
run *both*?
Thanks!
John Peterson|||John
BOL says:
Unlike DBCC DBREINDEX (or the index building operation in general), DBCC
INDEXDEFRAG is an online operation. It does not hold locks long term and
thus will not block running queries or updates. A relatively unfragmented
index can be defragmented faster than a new index can be built because the
time to defragment is related to the amount of fragmentation. A very
fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged,
regardless of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
Also, DBCC INDEXDEFRAG will not help if two indexes are interleaved on the
disk because INDEXDEFRAG shuffles the pages in place. To improve the
clustering of pages, rebuild the index.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:#Bgv70zpDHA.1740@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> For our production environment, we have a decent window which to run some
maintenance
> scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX,
is that
> essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX
do everything a
> INDEXDEFRAG does (and possibly more)? Or are there situations where it'd
be beneficial to
> run *both*?
> Thanks!
> John Peterson
>|||Please read "Microsoft SQL Server 2000 Index Defragmentation Best Practices"
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Bgv70zpDHA.1740@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> For our production environment, we have a decent window which to run some
maintenance
> scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX,
is that
> essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX
do everything a
> INDEXDEFRAG does (and possibly more)? Or are there situations where it'd
be beneficial to
> run *both*?
> Thanks!
> John Peterson
>|||Thanks, Gert!
The way I read this article, it sounds as if these two techniques *are* different, and
like many things, it depends on your system.
The article seems to suggest that rebuilding the index (DBCC DBREINDEX) should typically
yield better performance than doing a INDEXDEFRAG, but it showed cases where that might
not always be the case.
Now I'm even more confused than ever. I had hoped that the INDEXDEFRAG was essentially a
subset of the DBREINDEX operation so that my choice was simple. ;-)
"Gert E.R. Drapers" <GertD@.Online.SQLDev.Net> wrote in message
news:%23B95Rl2pDHA.976@.tk2msftngp13.phx.gbl...
> Please read "Microsoft SQL Server 2000 Index Defragmentation Best Practices"
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2003 All rights reserved.
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23Bgv70zpDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > For our production environment, we have a decent window which to run some
> maintenance
> > scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX,
> is that
> > essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX
> do everything a
> > INDEXDEFRAG does (and possibly more)? Or are there situations where it'd
> be beneficial to
> > run *both*?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>
Hello all!
For our production environment, we have a decent window which to run some maintenance
scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX, is that
essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX do everything a
INDEXDEFRAG does (and possibly more)? Or are there situations where it'd be beneficial to
run *both*?
Thanks!
John PetersonAs my understanding goes, it is better to go for DBCC DBREINDEX command, as it takes care of all the table and view indexes, and any constraints on the columns are automatically taken care of. One can use, DBCC INDEXDEFRAG, if the indexes needs to be used on-line, which allows any DML operations.
So if locking of rows during index rebuliding is not a criteria, I guess you can go with DBCC DBREINDEX.
Am not sure, if I was able to answer yur query.
Thanks
GYK
-- John Peterson wrote: --
(SQL Server 2000, SP3a)
Hello all!
For our production environment, we have a decent window which to run some maintenance
scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX, is that
essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX do everything a
INDEXDEFRAG does (and possibly more)? Or are there situations where it'd be beneficial to
run *both*?
Thanks!
John Peterson|||John
BOL says:
Unlike DBCC DBREINDEX (or the index building operation in general), DBCC
INDEXDEFRAG is an online operation. It does not hold locks long term and
thus will not block running queries or updates. A relatively unfragmented
index can be defragmented faster than a new index can be built because the
time to defragment is related to the amount of fragmentation. A very
fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged,
regardless of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
Also, DBCC INDEXDEFRAG will not help if two indexes are interleaved on the
disk because INDEXDEFRAG shuffles the pages in place. To improve the
clustering of pages, rebuild the index.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:#Bgv70zpDHA.1740@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> For our production environment, we have a decent window which to run some
maintenance
> scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX,
is that
> essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX
do everything a
> INDEXDEFRAG does (and possibly more)? Or are there situations where it'd
be beneficial to
> run *both*?
> Thanks!
> John Peterson
>|||Please read "Microsoft SQL Server 2000 Index Defragmentation Best Practices"
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Bgv70zpDHA.1740@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> For our production environment, we have a decent window which to run some
maintenance
> scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX,
is that
> essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX
do everything a
> INDEXDEFRAG does (and possibly more)? Or are there situations where it'd
be beneficial to
> run *both*?
> Thanks!
> John Peterson
>|||Thanks, Gert!
The way I read this article, it sounds as if these two techniques *are* different, and
like many things, it depends on your system.
The article seems to suggest that rebuilding the index (DBCC DBREINDEX) should typically
yield better performance than doing a INDEXDEFRAG, but it showed cases where that might
not always be the case.
Now I'm even more confused than ever. I had hoped that the INDEXDEFRAG was essentially a
subset of the DBREINDEX operation so that my choice was simple. ;-)
"Gert E.R. Drapers" <GertD@.Online.SQLDev.Net> wrote in message
news:%23B95Rl2pDHA.976@.tk2msftngp13.phx.gbl...
> Please read "Microsoft SQL Server 2000 Index Defragmentation Best Practices"
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2003 All rights reserved.
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23Bgv70zpDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > For our production environment, we have a decent window which to run some
> maintenance
> > scripts. I was wondering, if we have the ability to run a DBCC DBREINDEX,
> is that
> > essentially a "superset" of a DBCC INDEXDEFRAG? That is, does a DBREINDEX
> do everything a
> > INDEXDEFRAG does (and possibly more)? Or are there situations where it'd
> be beneficial to
> > run *both*?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>
Friday, February 17, 2012
dbcc checkdb errors against sql_variant
Recently, I've started to get DBCC Checkdb errors against a few databases - seems to be since applying sp3a.
The error (2537) affects certain tables that contain SQL_Variant fields. I just completed an exercise to run DBCC CHECKDB ('dbname', REPAIR_ALLOW_DATA_LOSS) against a copy of the offending table - then comparing the original table and the repaired table to isolate the rows that throw up the consistency errors. What I found was that all the rows trapped by DBCC CHECKDB for error 2537 contain real numbers in the SQL_Variant field. All other rows with text or integer contents in the SQL_Variant field do not throw up errors.
I tried a MS hotfix for error 2537 but it didn't fix the problem. Any ideas?
Cliveahhh...blindman?
Clive...how does the data get in to the table?
How long has the symptom been appearing? Was it right after the column(s) where created?
Was it after a bcp/dts/bulk insert/simple insert?|||Brett,
The data gets into the table via a browser front-end application - ASP pages. I think the original idea of using the variant type for this app was the the users could define their own fields for their own purposes via the app front-end.
I think the problem started to surface after applying SP3a. I've been running DBCC Checkdb for a long time against these same tables without getting errors. So, to answer your question, yes it was right after the columns were created.
Clive|||No sh-t
I'm wondering if it's a connection issue...
USE Northwind
GO
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 sql_variant)
GO
INSERT INTO myTable99(Col2)
SELECT 1 UNION ALL
SELECT GetDate() UNION ALL
SELECT 'MOO'
GO
SELECT * FROM myTable99
GO
DBCC CHECKTABLE ('myTable99')
GO
DROP TABLE myTable99
GO|||You using XML?
http://support.microsoft.com/?kbid=822757|||[QUOTE][SIZE=1]Originally posted by Brett Kaiser
You using XML?
No XML in this case.
I ran your test script - no problems reported by DBCC CHECKDB. I also added in a row including a real number - also no problems.
We tried running DBCC checkdb with the allow data loss option to fix the problem and then re-inserted the data. This seemed ok but then then users found that this caused a problem to the app - so had to restore the db.
Clive|||Set up a trace and monitor the situation...
did you see anything in the error logs?
The error (2537) affects certain tables that contain SQL_Variant fields. I just completed an exercise to run DBCC CHECKDB ('dbname', REPAIR_ALLOW_DATA_LOSS) against a copy of the offending table - then comparing the original table and the repaired table to isolate the rows that throw up the consistency errors. What I found was that all the rows trapped by DBCC CHECKDB for error 2537 contain real numbers in the SQL_Variant field. All other rows with text or integer contents in the SQL_Variant field do not throw up errors.
I tried a MS hotfix for error 2537 but it didn't fix the problem. Any ideas?
Cliveahhh...blindman?
Clive...how does the data get in to the table?
How long has the symptom been appearing? Was it right after the column(s) where created?
Was it after a bcp/dts/bulk insert/simple insert?|||Brett,
The data gets into the table via a browser front-end application - ASP pages. I think the original idea of using the variant type for this app was the the users could define their own fields for their own purposes via the app front-end.
I think the problem started to surface after applying SP3a. I've been running DBCC Checkdb for a long time against these same tables without getting errors. So, to answer your question, yes it was right after the columns were created.
Clive|||No sh-t
I'm wondering if it's a connection issue...
USE Northwind
GO
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 sql_variant)
GO
INSERT INTO myTable99(Col2)
SELECT 1 UNION ALL
SELECT GetDate() UNION ALL
SELECT 'MOO'
GO
SELECT * FROM myTable99
GO
DBCC CHECKTABLE ('myTable99')
GO
DROP TABLE myTable99
GO|||You using XML?
http://support.microsoft.com/?kbid=822757|||[QUOTE][SIZE=1]Originally posted by Brett Kaiser
You using XML?
No XML in this case.
I ran your test script - no problems reported by DBCC CHECKDB. I also added in a row including a real number - also no problems.
We tried running DBCC checkdb with the allow data loss option to fix the problem and then re-inserted the data. This seemed ok but then then users found that this caused a problem to the app - so had to restore the db.
Clive|||Set up a trace and monitor the situation...
did you see anything in the error logs?
Subscribe to:
Posts (Atom)