Dont know if that is the right group ... but here goes
2 questions:
1. What are the diffenrence on adding 1 column per Index, or adding more
columns per index... i'm a little confused here. Adn waht ORDER BY? is
that just the default ordering rule when doing "ORDER BY column"?
2. Are there any easy why to examine sql queries comming to the server
so its easier to debug what quries are slow ... ? so its possible to
make some optimazations on them ...
kind regards
Mikael SyskaAs far as 1) goes theres a world of difference. I would suggest reading
about indexes, if you have the time 'Inside Sql server 2000' would probably
give you all the answers you need.
2) you can use Profiler tool. Trace queries and possibly filter them by
duration value. If you put over 3000 you'll see only queries that last over
3 seconds...
MC
"Mikael Syska" <news01@.syska.dk> wrote in message
news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Dont know if that is the right group ... but here goes
> 2 questions:
> 1. What are the diffenrence on adding 1 column per Index, or adding more
> columns per index... i'm a little confused here. Adn waht ORDER BY? is
> that just the default ordering rule when doing "ORDER BY column"?
> 2. Are there any easy why to examine sql queries comming to the server so
> its easier to debug what quries are slow ... ? so its possible to make
> some optimazations on them ...
> kind regards
> Mikael Syska|||Mikael:
When people ask me this question (#1), I always say, look at the phone
book. You can think of a phone book as being like a 2-way composite
index on Lastname (Ascending) and Firstname (Ascending), in that order:
Adams, John
Bryant, Frank
Coolidge, Adam
Coolidge, Calvin
Coolidge, Zach
If you ordered it as Lastname (Ascending) and Firstname (Descending):
Adams, John
Bryant, Frank
Coolidge, Zach
Coolidge, Calvin
Coolidge, Adam
If you ordered it as First name (Ascending) ...
Coolidge, Adam
Coolidge, Calvin
Bryant, Frank
Adams, John
Coolidge, Zach
Get the idea?
MC wrote:
> As far as 1) goes theres a world of difference. I would suggest reading
> about indexes, if you have the time 'Inside Sql server 2000' would probably
> give you all the answers you need.
> 2) you can use Profiler tool. Trace queries and possibly filter them by
> duration value. If you put over 3000 you'll see only queries that last over
> 3 seconds...
>
> MC
> "Mikael Syska" <news01@.syska.dk> wrote in message
> news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Dont know if that is the right group ... but here goes
>> 2 questions:
>> 1. What are the diffenrence on adding 1 column per Index, or adding more
>> columns per index... i'm a little confused here. Adn waht ORDER BY? is
>> that just the default ordering rule when doing "ORDER BY column"?
>> 2. Are there any easy why to examine sql queries comming to the server so
>> its easier to debug what quries are slow ... ? so its possible to make
>> some optimazations on them ...
>> kind regards
>> Mikael Syska
>|||Yes, I think so ...
So an index on FirstName DESC, LastName ASC
and the query ...
SELECT Firname, LastName FROM t1 ORDER BY LastName, FirstName
would are the list after that index ... ASC on FirstName and DESC on
LastName ?
and if I had a other index on FirstName ASC and did a
SELECT Firname, LastName FROM t1 ORDER BY FirstName
it would sort DESC ... right ? if i guess they are grouped ...
But are there any performance gain by doing it that way (then some
columns would be added more than 1 time) ?
best regrads
Mikael Syska
David Markle wrote:
> Mikael:
> When people ask me this question (#1), I always say, look at the phone
> book. You can think of a phone book as being like a 2-way composite
> index on Lastname (Ascending) and Firstname (Ascending), in that order:
> Adams, John
> Bryant, Frank
> Coolidge, Adam
> Coolidge, Calvin
> Coolidge, Zach
> If you ordered it as Lastname (Ascending) and Firstname (Descending):
> Adams, John
> Bryant, Frank
> Coolidge, Zach
> Coolidge, Calvin
> Coolidge, Adam
> If you ordered it as First name (Ascending) ...
> Coolidge, Adam
> Coolidge, Calvin
> Bryant, Frank
> Adams, John
> Coolidge, Zach
> Get the idea?
> MC wrote:
>> As far as 1) goes theres a world of difference. I would suggest
>> reading about indexes, if you have the time 'Inside Sql server 2000'
>> would probably give you all the answers you need.
>> 2) you can use Profiler tool. Trace queries and possibly filter them
>> by duration value. If you put over 3000 you'll see only queries that
>> last over 3 seconds...
>>
>> MC
>> "Mikael Syska" <news01@.syska.dk> wrote in message
>> news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Dont know if that is the right group ... but here goes
>> 2 questions:
>> 1. What are the diffenrence on adding 1 column per Index, or adding
>> more columns per index... i'm a little confused here. Adn waht ORDER
>> BY? is that just the default ordering rule when doing "ORDER BY column"?
>> 2. Are there any easy why to examine sql queries comming to the
>> server so its easier to debug what quries are slow ... ? so its
>> possible to make some optimazations on them ...
>> kind regards
>> Mikael Syska
>>|||"Mikael Syska" <news01@.syska.dk> wrote in message
news:eS7AKXsXHHA.3952@.TK2MSFTNGP04.phx.gbl...
> Yes, I think so ...
> So an index on FirstName DESC, LastName ASC
> and the query ...
> SELECT Firname, LastName FROM t1 ORDER BY LastName, FirstName
> would are the list after that index ... ASC on FirstName and DESC on
> LastName ?
No, ASC on both. An ORDER BY is ASCending unless otherwise specified.
However, the query plans will be different.
Given this script:
create table order_test
( fname varchar(10),
lname varchar(10))
insert into order_test values ('albert', 'zilch')
insert into order_test values ('albert', 'allen')
insert into order_test values ('albert', 'Brend')
insert into order_test values ('Bill', 'zilch')
insert into order_test values ('Bill', 'Cramden')
insert into order_test values ('Wendy', 'Jillson')
create index fname_idx on order_test (fname asc, lname desc)
go
set showplan_all on
go
select * from order_test order by fname, lname
select * from order_test order by fname, lname desc
drop table order_test
Since we're ordering the results in "reverse" of the index, we have an extra
scan in there.
creating an INDEX with DESC is useful when you will generally return the
results in DESC order. It's just an optimization step.
select * from order_test order by fname, lname
|--Sort(ORDER BY:([testing].[dbo].[order_test].[fname] ASC,
[testing].[dbo].[order_test].[lname] ASC))
|--Index Scan(OBJECT:([testing].[dbo].[order_test].[fname_idx]))
select * from order_test order by fname, lname desc
|--Index Scan(OBJECT:([testing].[dbo].[order_test].[fname_idx]), ORDERED
FORWARD)
> and if I had a other index on FirstName ASC and did a
> SELECT Firname, LastName FROM t1 ORDER BY FirstName
> it would sort DESC ... right ? if i guess they are grouped ...
> But are there any performance gain by doing it that way (then some columns
> would be added more than 1 time) ?
> best regrads
> Mikael Syska
> David Markle wrote:
>> Mikael:
>> When people ask me this question (#1), I always say, look at the phone
>> book. You can think of a phone book as being like a 2-way composite
>> index on Lastname (Ascending) and Firstname (Ascending), in that order:
>> Adams, John
>> Bryant, Frank
>> Coolidge, Adam
>> Coolidge, Calvin
>> Coolidge, Zach
>> If you ordered it as Lastname (Ascending) and Firstname (Descending):
>> Adams, John
>> Bryant, Frank
>> Coolidge, Zach
>> Coolidge, Calvin
>> Coolidge, Adam
>> If you ordered it as First name (Ascending) ...
>> Coolidge, Adam
>> Coolidge, Calvin
>> Bryant, Frank
>> Adams, John
>> Coolidge, Zach
>> Get the idea?
>> MC wrote:
>> As far as 1) goes theres a world of difference. I would suggest reading
>> about indexes, if you have the time 'Inside Sql server 2000' would
>> probably give you all the answers you need.
>> 2) you can use Profiler tool. Trace queries and possibly filter them by
>> duration value. If you put over 3000 you'll see only queries that last
>> over 3 seconds...
>>
>> MC
>> "Mikael Syska" <news01@.syska.dk> wrote in message
>> news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Dont know if that is the right group ... but here goes
>> 2 questions:
>> 1. What are the diffenrence on adding 1 column per Index, or adding
>> more columns per index... i'm a little confused here. Adn waht ORDER
>> BY? is that just the default ordering rule when doing "ORDER BY
>> column"?
>> 2. Are there any easy why to examine sql queries comming to the server
>> so its easier to debug what quries are slow ... ? so its possible to
>> make some optimazations on them ...
>> kind regards
>> Mikael Syska
>>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Monday, March 19, 2012
2q - Indexs and Optimize...
Dont know if that is the right group ... but here goes
2 questions:
1. What are the diffenrence on adding 1 column per Index, or adding more
columns per index... i'm a little confused here. Adn waht ORDER BY? is
that just the default ordering rule when doing "ORDER BY column"?
2. Are there any easy why to examine sql queries comming to the server
so its easier to debug what quries are slow ... ? so its possible to
make some optimazations on them ...
kind regards
Mikael SyskaAs far as 1) goes theres a world of difference. I would suggest reading
about indexes, if you have the time 'Inside Sql server 2000' would probably
give you all the answers you need.
2) you can use Profiler tool. Trace queries and possibly filter them by
duration value. If you put over 3000 you'll see only queries that last over
3 seconds...
MC
"Mikael Syska" <news01@.syska.dk> wrote in message
news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Dont know if that is the right group ... but here goes
> 2 questions:
> 1. What are the diffenrence on adding 1 column per Index, or adding more
> columns per index... i'm a little confused here. Adn waht ORDER BY? is
> that just the default ordering rule when doing "ORDER BY column"?
> 2. Are there any easy why to examine sql queries comming to the server so
> its easier to debug what quries are slow ... ? so its possible to make
> some optimazations on them ...
> kind regards
> Mikael Syska|||Mikael:
When people ask me this question (#1), I always say, look at the phone
book. You can think of a phone book as being like a 2-way composite
index on Lastname (Ascending) and Firstname (Ascending), in that order:
Adams, John
Bryant, Frank
Coolidge, Adam
Coolidge, Calvin
Coolidge, Zach
If you ordered it as Lastname (Ascending) and Firstname (Descending):
Adams, John
Bryant, Frank
Coolidge, Zach
Coolidge, Calvin
Coolidge, Adam
If you ordered it as First name (Ascending) ...
Coolidge, Adam
Coolidge, Calvin
Bryant, Frank
Adams, John
Coolidge, Zach
Get the idea?
MC wrote:
> As far as 1) goes theres a world of difference. I would suggest reading
> about indexes, if you have the time 'Inside Sql server 2000' would probabl
y
> give you all the answers you need.
> 2) you can use Profiler tool. Trace queries and possibly filter them by
> duration value. If you put over 3000 you'll see only queries that last ove
r
> 3 seconds...
>
> MC
> "Mikael Syska" <news01@.syska.dk> wrote in message
> news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
>|||Yes, I think so ...
So an index on FirstName DESC, LastName ASC
and the query ...
SELECT Firname, LastName FROM t1 ORDER BY LastName, FirstName
would are the list after that index ... ASC on FirstName and DESC on
LastName ?
and if I had a other index on FirstName ASC and did a
SELECT Firname, LastName FROM t1 ORDER BY FirstName
it would sort DESC ... right ? if i guess they are grouped ...
But are there any performance gain by doing it that way (then some
columns would be added more than 1 time) ?
best regrads
Mikael Syska
David Markle wrote:[vbcol=seagreen]
> Mikael:
> When people ask me this question (#1), I always say, look at the phone
> book. You can think of a phone book as being like a 2-way composite
> index on Lastname (Ascending) and Firstname (Ascending), in that order:
> Adams, John
> Bryant, Frank
> Coolidge, Adam
> Coolidge, Calvin
> Coolidge, Zach
> If you ordered it as Lastname (Ascending) and Firstname (Descending):
> Adams, John
> Bryant, Frank
> Coolidge, Zach
> Coolidge, Calvin
> Coolidge, Adam
> If you ordered it as First name (Ascending) ...
> Coolidge, Adam
> Coolidge, Calvin
> Bryant, Frank
> Adams, John
> Coolidge, Zach
> Get the idea?
> MC wrote:|||"Mikael Syska" <news01@.syska.dk> wrote in message
news:eS7AKXsXHHA.3952@.TK2MSFTNGP04.phx.gbl...
> Yes, I think so ...
> So an index on FirstName DESC, LastName ASC
> and the query ...
> SELECT Firname, LastName FROM t1 ORDER BY LastName, FirstName
> would are the list after that index ... ASC on FirstName and DESC on
> LastName ?
No, ASC on both. An ORDER BY is ASCending unless otherwise specified.
However, the query plans will be different.
Given this script:
create table order_test
( fname varchar(10),
lname varchar(10))
insert into order_test values ('albert', 'zilch')
insert into order_test values ('albert', 'allen')
insert into order_test values ('albert', 'Brend')
insert into order_test values ('Bill', 'zilch')
insert into order_test values ('Bill', 'Cramden')
insert into order_test values ('Wendy', 'Jillson')
create index fname_idx on order_test (fname asc, lname desc)
go
set showplan_all on
go
select * from order_test order by fname, lname
select * from order_test order by fname, lname desc
drop table order_test
Since we're ordering the results in "reverse" of the index, we have an extra
scan in there.
creating an INDEX with DESC is useful when you will generally return the
results in DESC order. It's just an optimization step.
select * from order_test order by fname, lname
|--Sort(ORDER BY
[testing].[dbo].[order_test].[fname] ASC,
[testing].[dbo].[order_test].[lname] ASC))
|--Index Scan(OBJECT[testing].[dbo].[order_test].[fname_id
x]))
select * from order_test order by fname, lname desc
|--Index Scan(OBJECT[testing].[dbo].[order_test].[fname_id
x]), ORDERED
FORWARD)
[vbcol=seagreen]
> and if I had a other index on FirstName ASC and did a
> SELECT Firname, LastName FROM t1 ORDER BY FirstName
> it would sort DESC ... right ? if i guess they are grouped ...
> But are there any performance gain by doing it that way (then some columns
> would be added more than 1 time) ?
> best regrads
> Mikael Syska
> David Markle wrote:
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
2 questions:
1. What are the diffenrence on adding 1 column per Index, or adding more
columns per index... i'm a little confused here. Adn waht ORDER BY? is
that just the default ordering rule when doing "ORDER BY column"?
2. Are there any easy why to examine sql queries comming to the server
so its easier to debug what quries are slow ... ? so its possible to
make some optimazations on them ...
kind regards
Mikael SyskaAs far as 1) goes theres a world of difference. I would suggest reading
about indexes, if you have the time 'Inside Sql server 2000' would probably
give you all the answers you need.
2) you can use Profiler tool. Trace queries and possibly filter them by
duration value. If you put over 3000 you'll see only queries that last over
3 seconds...
MC
"Mikael Syska" <news01@.syska.dk> wrote in message
news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Dont know if that is the right group ... but here goes
> 2 questions:
> 1. What are the diffenrence on adding 1 column per Index, or adding more
> columns per index... i'm a little confused here. Adn waht ORDER BY? is
> that just the default ordering rule when doing "ORDER BY column"?
> 2. Are there any easy why to examine sql queries comming to the server so
> its easier to debug what quries are slow ... ? so its possible to make
> some optimazations on them ...
> kind regards
> Mikael Syska|||Mikael:
When people ask me this question (#1), I always say, look at the phone
book. You can think of a phone book as being like a 2-way composite
index on Lastname (Ascending) and Firstname (Ascending), in that order:
Adams, John
Bryant, Frank
Coolidge, Adam
Coolidge, Calvin
Coolidge, Zach
If you ordered it as Lastname (Ascending) and Firstname (Descending):
Adams, John
Bryant, Frank
Coolidge, Zach
Coolidge, Calvin
Coolidge, Adam
If you ordered it as First name (Ascending) ...
Coolidge, Adam
Coolidge, Calvin
Bryant, Frank
Adams, John
Coolidge, Zach
Get the idea?
MC wrote:
> As far as 1) goes theres a world of difference. I would suggest reading
> about indexes, if you have the time 'Inside Sql server 2000' would probabl
y
> give you all the answers you need.
> 2) you can use Profiler tool. Trace queries and possibly filter them by
> duration value. If you put over 3000 you'll see only queries that last ove
r
> 3 seconds...
>
> MC
> "Mikael Syska" <news01@.syska.dk> wrote in message
> news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
>|||Yes, I think so ...
So an index on FirstName DESC, LastName ASC
and the query ...
SELECT Firname, LastName FROM t1 ORDER BY LastName, FirstName
would are the list after that index ... ASC on FirstName and DESC on
LastName ?
and if I had a other index on FirstName ASC and did a
SELECT Firname, LastName FROM t1 ORDER BY FirstName
it would sort DESC ... right ? if i guess they are grouped ...
But are there any performance gain by doing it that way (then some
columns would be added more than 1 time) ?
best regrads
Mikael Syska
David Markle wrote:[vbcol=seagreen]
> Mikael:
> When people ask me this question (#1), I always say, look at the phone
> book. You can think of a phone book as being like a 2-way composite
> index on Lastname (Ascending) and Firstname (Ascending), in that order:
> Adams, John
> Bryant, Frank
> Coolidge, Adam
> Coolidge, Calvin
> Coolidge, Zach
> If you ordered it as Lastname (Ascending) and Firstname (Descending):
> Adams, John
> Bryant, Frank
> Coolidge, Zach
> Coolidge, Calvin
> Coolidge, Adam
> If you ordered it as First name (Ascending) ...
> Coolidge, Adam
> Coolidge, Calvin
> Bryant, Frank
> Adams, John
> Coolidge, Zach
> Get the idea?
> MC wrote:|||"Mikael Syska" <news01@.syska.dk> wrote in message
news:eS7AKXsXHHA.3952@.TK2MSFTNGP04.phx.gbl...
> Yes, I think so ...
> So an index on FirstName DESC, LastName ASC
> and the query ...
> SELECT Firname, LastName FROM t1 ORDER BY LastName, FirstName
> would are the list after that index ... ASC on FirstName and DESC on
> LastName ?
No, ASC on both. An ORDER BY is ASCending unless otherwise specified.
However, the query plans will be different.
Given this script:
create table order_test
( fname varchar(10),
lname varchar(10))
insert into order_test values ('albert', 'zilch')
insert into order_test values ('albert', 'allen')
insert into order_test values ('albert', 'Brend')
insert into order_test values ('Bill', 'zilch')
insert into order_test values ('Bill', 'Cramden')
insert into order_test values ('Wendy', 'Jillson')
create index fname_idx on order_test (fname asc, lname desc)
go
set showplan_all on
go
select * from order_test order by fname, lname
select * from order_test order by fname, lname desc
drop table order_test
Since we're ordering the results in "reverse" of the index, we have an extra
scan in there.
creating an INDEX with DESC is useful when you will generally return the
results in DESC order. It's just an optimization step.
select * from order_test order by fname, lname
|--Sort(ORDER BY
[testing].[dbo].[order_test].[fname] ASC,[testing].[dbo].[order_test].[lname] ASC))
|--Index Scan(OBJECT
[testing].[dbo].[order_test].[fname_idx]))
select * from order_test order by fname, lname desc
|--Index Scan(OBJECT
[testing].[dbo].[order_test].[fname_idx]), ORDERED
FORWARD)
[vbcol=seagreen]
> and if I had a other index on FirstName ASC and did a
> SELECT Firname, LastName FROM t1 ORDER BY FirstName
> it would sort DESC ... right ? if i guess they are grouped ...
> But are there any performance gain by doing it that way (then some columns
> would be added more than 1 time) ?
> best regrads
> Mikael Syska
> David Markle wrote:
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Thursday, February 16, 2012
2005 -lock being held by an orphan transaction?
SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is
a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables ar
e
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is
a
>lock being held on a table by a particular transaction id which doesn't see
m
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables a
re
>locked by transaction_id XXXX but the spid listed is 0. Is there any way t
o
>kill a transaction that has no spid without recycling the SQL Server servic
e
>(this is a production box)? Any idea how this sort of thing happens? Belo
w
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
>
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is
a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables ar
e
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is
a
>lock being held on a table by a particular transaction id which doesn't see
m
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables a
re
>locked by transaction_id XXXX but the spid listed is 0. Is there any way t
o
>kill a transaction that has no spid without recycling the SQL Server servic
e
>(this is a production box)? Any idea how this sort of thing happens? Belo
w
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
>
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>
2005 -lock being held by an orphan transaction?
SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
>
>
|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
>
>
|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
2005 -lock being held by an orphan transaction?
SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
> >
> >We have a problem that started with an index rebuild job getting hung and
> >blocking other users. After digging into the problem I found that there is a
> >lock being held on a table by a particular transaction id which doesn't seem
> >to be tied to a process (spid) any longer. By querying certain DMVs like
> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
> >kill a transaction that has no spid without recycling the SQL Server service
> >(this is a production box)? Any idea how this sort of thing happens? Below
> >is the output of some of the queries:
> >
> >--sp_lock (only columns with info)
> >spid dbid ObjId IndId Type Mode Status
> >0 5 377872513 0 TAB IS GRANT
> >0 5 73871430 0 TAB IS GRANT
> >0 5 9871202 0 TAB IS GRANT
> >
> >--sys.dm_tran_locks (only the columns with info)
> >resource_type resource_associated_entity_id request_mode request_type
> >request_status request_owner_type request_owner_id
> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
> >
> >--sys.dm_tran_active_transactions (only the columns with info)
> >transaction_id name transaction_begin_time transaction_type
> >transaction_state transaction_status
> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>> You could trying killing it by unit of work(UOW) ID instead
>> of spid.
>> You can get the UOW from sys.syslockinfo.
>> -Sue
>> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
>> <ChrisC@.discussions.microsoft.com> wrote:
>> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>> >
>> >We have a problem that started with an index rebuild job getting hung and
>> >blocking other users. After digging into the problem I found that there is a
>> >lock being held on a table by a particular transaction id which doesn't seem
>> >to be tied to a process (spid) any longer. By querying certain DMVs like
>> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>> >kill a transaction that has no spid without recycling the SQL Server service
>> >(this is a production box)? Any idea how this sort of thing happens? Below
>> >is the output of some of the queries:
>> >
>> >--sp_lock (only columns with info)
>> >spid dbid ObjId IndId Type Mode Status
>> >0 5 377872513 0 TAB IS GRANT
>> >0 5 73871430 0 TAB IS GRANT
>> >0 5 9871202 0 TAB IS GRANT
>> >
>> >--sys.dm_tran_locks (only the columns with info)
>> >resource_type resource_associated_entity_id request_mode request_type
>> >request_status request_owner_type request_owner_id
>> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>> >
>> >--sys.dm_tran_active_transactions (only the columns with info)
>> >transaction_id name transaction_begin_time transaction_type
>> >transaction_state transaction_status
>> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>>
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
> >
> >We have a problem that started with an index rebuild job getting hung and
> >blocking other users. After digging into the problem I found that there is a
> >lock being held on a table by a particular transaction id which doesn't seem
> >to be tied to a process (spid) any longer. By querying certain DMVs like
> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
> >kill a transaction that has no spid without recycling the SQL Server service
> >(this is a production box)? Any idea how this sort of thing happens? Below
> >is the output of some of the queries:
> >
> >--sp_lock (only columns with info)
> >spid dbid ObjId IndId Type Mode Status
> >0 5 377872513 0 TAB IS GRANT
> >0 5 73871430 0 TAB IS GRANT
> >0 5 9871202 0 TAB IS GRANT
> >
> >--sys.dm_tran_locks (only the columns with info)
> >resource_type resource_associated_entity_id request_mode request_type
> >request_status request_owner_type request_owner_id
> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
> >
> >--sys.dm_tran_active_transactions (only the columns with info)
> >transaction_id name transaction_begin_time transaction_type
> >transaction_state transaction_status
> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>> You could trying killing it by unit of work(UOW) ID instead
>> of spid.
>> You can get the UOW from sys.syslockinfo.
>> -Sue
>> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
>> <ChrisC@.discussions.microsoft.com> wrote:
>> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>> >
>> >We have a problem that started with an index rebuild job getting hung and
>> >blocking other users. After digging into the problem I found that there is a
>> >lock being held on a table by a particular transaction id which doesn't seem
>> >to be tied to a process (spid) any longer. By querying certain DMVs like
>> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>> >kill a transaction that has no spid without recycling the SQL Server service
>> >(this is a production box)? Any idea how this sort of thing happens? Below
>> >is the output of some of the queries:
>> >
>> >--sp_lock (only columns with info)
>> >spid dbid ObjId IndId Type Mode Status
>> >0 5 377872513 0 TAB IS GRANT
>> >0 5 73871430 0 TAB IS GRANT
>> >0 5 9871202 0 TAB IS GRANT
>> >
>> >--sys.dm_tran_locks (only the columns with info)
>> >resource_type resource_associated_entity_id request_mode request_type
>> >request_status request_owner_type request_owner_id
>> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>> >
>> >--sys.dm_tran_active_transactions (only the columns with info)
>> >transaction_id name transaction_begin_time transaction_type
>> >transaction_state transaction_status
>> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>>
Monday, February 13, 2012
2005 Full Text Error
I'm having problems creating a new Full Text index in 2005. My environment:
Windows 2003 SP1 Ent Cluster (Active/Active)
SQL 2000 SP4 on one side SQL 2005 on the other.
When I go to create a new index using the GUI, as soon as I click on "new
Full-Text Catalog..." I get this error:
Property DefaultPath is not available for FullTextService
'[LON-IMSSQL01\IMS]'. This property may not exist for this object, or may not
be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
But the screen does load and it does let me create the catalogue with no
problems.
Then I go to add a table and column. When I press OK I get this error:
Full-text crawl manager has not been initialized. Any crawl started before
the crawl manager was fully initialized will need to be restarted. Please
restart SQL Server and retry the command. You should also check the error log
to fix any failures that might have caused the crawl manager to fail.
(Microsoft SQL Server, Error: 7644)
Press OK to that error and the FT Cataloge screen closes and I get this error:
Index was out of range. Must be non-negative and less than the size of the
collection.
Parameter name: index (mscorlib)
If I go back into the catalogue screen The Eligilble colums box appears as a
white box with a nice big red cross over it.
There are no errors logged in the SQL event log or windows log. I have
rebooted, restarted, failed over and re-installed full text using:
start /wait D:\2005\servers\setup.exe /qb REINSTALL=SQL_FullText
INSTANCENAME=IMS REINSTALLMODE=M
But still I get the same problem. Can someone please help me!?!?
Thanks
Simon
Simon,
An interesting error - "Property DefaultPath is not available for
FullTextService This property may not exist for this object, or may not be
retrievable due to insufficient access rights". Could you post the file or
text from the SQFT log file (SQLFT0000900005.LOG) where 9 is the dbid and 5
the FTCatid under the \MSSQL.1\MSSQL\LOG directory where you have SQL Server
2005 installed? Did you have any problems installing SQL Server 2005? This
may be an installation problem or something else, so perhaps, more info will
be recorded in the SQLFT log file.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:065E5E9B-FC6E-4621-9CF8-10EB282BB633@.microsoft.com...
> I'm having problems creating a new Full Text index in 2005. My
> environment:
> Windows 2003 SP1 Ent Cluster (Active/Active)
> SQL 2000 SP4 on one side SQL 2005 on the other.
> When I go to create a new index using the GUI, as soon as I click on "new
> Full-Text Catalog..." I get this error:
> Property DefaultPath is not available for FullTextService
> '[LON-IMSSQL01\IMS]'. This property may not exist for this object, or may
> not
> be retrievable due to insufficient access rights.
> (Microsoft.SqlServer.Smo)
> But the screen does load and it does let me create the catalogue with no
> problems.
> Then I go to add a table and column. When I press OK I get this error:
> Full-text crawl manager has not been initialized. Any crawl started before
> the crawl manager was fully initialized will need to be restarted. Please
> restart SQL Server and retry the command. You should also check the error
> log
> to fix any failures that might have caused the crawl manager to fail.
> (Microsoft SQL Server, Error: 7644)
> Press OK to that error and the FT Cataloge screen closes and I get this
> error:
> Index was out of range. Must be non-negative and less than the size of the
> collection.
> Parameter name: index (mscorlib)
> If I go back into the catalogue screen The Eligilble colums box appears as
> a
> white box with a nice big red cross over it.
> There are no errors logged in the SQL event log or windows log. I have
> rebooted, restarted, failed over and re-installed full text using:
> start /wait D:\2005\servers\setup.exe /qb REINSTALL=SQL_FullText
> INSTANCENAME=IMS REINSTALLMODE=M
> But still I get the same problem. Can someone please help me!?!?
> Thanks
> Simon
|||I did have problems with the install. I couldn't install using the GUI,
though it worked from the command line. I also installed FT seperatly (from
command line) after the original install. I think the install problem was
being casued by installing DTS (I'll explain later).
Anyway, I unistalled both all 2005 components (again from command line as
the GUI errored). Then reinstalled SQL, and FT & DTS is one go from command
line, this failed - it was unable to start the scheduled task on the 2nd node
because of a permissions error - though looking through the logs I could see
setup.exe failed with a fatal exception.
So I tried the install again with just SQL and FT from command line - it
worked perfectly and FT appears to be fine now. I still can't install DTS -
but I guess I'll just have to live without it.
Unfortunatly I didn't keep the FT logs - I cleared all folders before
reinstalling. I do however have all my install logs (there's a lot of them).
But I think this will probably go down as "one of those things". After all
the product was only released yesterday.
Thanks for you help anyway.
Simon
"John Kane" wrote:
> Simon,
> An interesting error - "Property DefaultPath is not available for
> FullTextService This property may not exist for this object, or may not be
> retrievable due to insufficient access rights". Could you post the file or
> text from the SQFT log file (SQLFT0000900005.LOG) where 9 is the dbid and 5
> the FTCatid under the \MSSQL.1\MSSQL\LOG directory where you have SQL Server
> 2005 installed? Did you have any problems installing SQL Server 2005? This
> may be an installation problem or something else, so perhaps, more info will
> be recorded in the SQLFT log file.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:065E5E9B-FC6E-4621-9CF8-10EB282BB633@.microsoft.com...
>
>
|||You're welcome, Simon,
Great news! Although, its a shame that you don't have the FTS log file as
that could of been helpful info for helping others troubleshoot similar
problems in the future with SQL Server 2005 as I'm very positive there will
be more such situations! <G>
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:43E21A03-80DA-4AFA-B1E5-48B0BE52D2C7@.microsoft.com...[vbcol=seagreen]
>I did have problems with the install. I couldn't install using the GUI,
> though it worked from the command line. I also installed FT seperatly
> (from
> command line) after the original install. I think the install problem was
> being casued by installing DTS (I'll explain later).
> Anyway, I unistalled both all 2005 components (again from command line as
> the GUI errored). Then reinstalled SQL, and FT & DTS is one go from
> command
> line, this failed - it was unable to start the scheduled task on the 2nd
> node
> because of a permissions error - though looking through the logs I could
> see
> setup.exe failed with a fatal exception.
> So I tried the install again with just SQL and FT from command line - it
> worked perfectly and FT appears to be fine now. I still can't install
> DTS -
> but I guess I'll just have to live without it.
> Unfortunatly I didn't keep the FT logs - I cleared all folders before
> reinstalling. I do however have all my install logs (there's a lot of
> them).
> But I think this will probably go down as "one of those things". After all
> the product was only released yesterday.
> Thanks for you help anyway.
> Simon
>
>
> "John Kane" wrote:
Windows 2003 SP1 Ent Cluster (Active/Active)
SQL 2000 SP4 on one side SQL 2005 on the other.
When I go to create a new index using the GUI, as soon as I click on "new
Full-Text Catalog..." I get this error:
Property DefaultPath is not available for FullTextService
'[LON-IMSSQL01\IMS]'. This property may not exist for this object, or may not
be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
But the screen does load and it does let me create the catalogue with no
problems.
Then I go to add a table and column. When I press OK I get this error:
Full-text crawl manager has not been initialized. Any crawl started before
the crawl manager was fully initialized will need to be restarted. Please
restart SQL Server and retry the command. You should also check the error log
to fix any failures that might have caused the crawl manager to fail.
(Microsoft SQL Server, Error: 7644)
Press OK to that error and the FT Cataloge screen closes and I get this error:
Index was out of range. Must be non-negative and less than the size of the
collection.
Parameter name: index (mscorlib)
If I go back into the catalogue screen The Eligilble colums box appears as a
white box with a nice big red cross over it.
There are no errors logged in the SQL event log or windows log. I have
rebooted, restarted, failed over and re-installed full text using:
start /wait D:\2005\servers\setup.exe /qb REINSTALL=SQL_FullText
INSTANCENAME=IMS REINSTALLMODE=M
But still I get the same problem. Can someone please help me!?!?
Thanks
Simon
Simon,
An interesting error - "Property DefaultPath is not available for
FullTextService This property may not exist for this object, or may not be
retrievable due to insufficient access rights". Could you post the file or
text from the SQFT log file (SQLFT0000900005.LOG) where 9 is the dbid and 5
the FTCatid under the \MSSQL.1\MSSQL\LOG directory where you have SQL Server
2005 installed? Did you have any problems installing SQL Server 2005? This
may be an installation problem or something else, so perhaps, more info will
be recorded in the SQLFT log file.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:065E5E9B-FC6E-4621-9CF8-10EB282BB633@.microsoft.com...
> I'm having problems creating a new Full Text index in 2005. My
> environment:
> Windows 2003 SP1 Ent Cluster (Active/Active)
> SQL 2000 SP4 on one side SQL 2005 on the other.
> When I go to create a new index using the GUI, as soon as I click on "new
> Full-Text Catalog..." I get this error:
> Property DefaultPath is not available for FullTextService
> '[LON-IMSSQL01\IMS]'. This property may not exist for this object, or may
> not
> be retrievable due to insufficient access rights.
> (Microsoft.SqlServer.Smo)
> But the screen does load and it does let me create the catalogue with no
> problems.
> Then I go to add a table and column. When I press OK I get this error:
> Full-text crawl manager has not been initialized. Any crawl started before
> the crawl manager was fully initialized will need to be restarted. Please
> restart SQL Server and retry the command. You should also check the error
> log
> to fix any failures that might have caused the crawl manager to fail.
> (Microsoft SQL Server, Error: 7644)
> Press OK to that error and the FT Cataloge screen closes and I get this
> error:
> Index was out of range. Must be non-negative and less than the size of the
> collection.
> Parameter name: index (mscorlib)
> If I go back into the catalogue screen The Eligilble colums box appears as
> a
> white box with a nice big red cross over it.
> There are no errors logged in the SQL event log or windows log. I have
> rebooted, restarted, failed over and re-installed full text using:
> start /wait D:\2005\servers\setup.exe /qb REINSTALL=SQL_FullText
> INSTANCENAME=IMS REINSTALLMODE=M
> But still I get the same problem. Can someone please help me!?!?
> Thanks
> Simon
|||I did have problems with the install. I couldn't install using the GUI,
though it worked from the command line. I also installed FT seperatly (from
command line) after the original install. I think the install problem was
being casued by installing DTS (I'll explain later).
Anyway, I unistalled both all 2005 components (again from command line as
the GUI errored). Then reinstalled SQL, and FT & DTS is one go from command
line, this failed - it was unable to start the scheduled task on the 2nd node
because of a permissions error - though looking through the logs I could see
setup.exe failed with a fatal exception.
So I tried the install again with just SQL and FT from command line - it
worked perfectly and FT appears to be fine now. I still can't install DTS -
but I guess I'll just have to live without it.
Unfortunatly I didn't keep the FT logs - I cleared all folders before
reinstalling. I do however have all my install logs (there's a lot of them).
But I think this will probably go down as "one of those things". After all
the product was only released yesterday.
Thanks for you help anyway.
Simon
"John Kane" wrote:
> Simon,
> An interesting error - "Property DefaultPath is not available for
> FullTextService This property may not exist for this object, or may not be
> retrievable due to insufficient access rights". Could you post the file or
> text from the SQFT log file (SQLFT0000900005.LOG) where 9 is the dbid and 5
> the FTCatid under the \MSSQL.1\MSSQL\LOG directory where you have SQL Server
> 2005 installed? Did you have any problems installing SQL Server 2005? This
> may be an installation problem or something else, so perhaps, more info will
> be recorded in the SQLFT log file.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:065E5E9B-FC6E-4621-9CF8-10EB282BB633@.microsoft.com...
>
>
|||You're welcome, Simon,
Great news! Although, its a shame that you don't have the FTS log file as
that could of been helpful info for helping others troubleshoot similar
problems in the future with SQL Server 2005 as I'm very positive there will
be more such situations! <G>
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:43E21A03-80DA-4AFA-B1E5-48B0BE52D2C7@.microsoft.com...[vbcol=seagreen]
>I did have problems with the install. I couldn't install using the GUI,
> though it worked from the command line. I also installed FT seperatly
> (from
> command line) after the original install. I think the install problem was
> being casued by installing DTS (I'll explain later).
> Anyway, I unistalled both all 2005 components (again from command line as
> the GUI errored). Then reinstalled SQL, and FT & DTS is one go from
> command
> line, this failed - it was unable to start the scheduled task on the 2nd
> node
> because of a permissions error - though looking through the logs I could
> see
> setup.exe failed with a fatal exception.
> So I tried the install again with just SQL and FT from command line - it
> worked perfectly and FT appears to be fine now. I still can't install
> DTS -
> but I guess I'll just have to live without it.
> Unfortunatly I didn't keep the FT logs - I cleared all folders before
> reinstalling. I do however have all my install logs (there's a lot of
> them).
> But I think this will probably go down as "one of those things". After all
> the product was only released yesterday.
> Thanks for you help anyway.
> Simon
>
>
> "John Kane" wrote:
2005 ft index help
I have numerous vldb's that my mgmt want to have full text indexed. Usually
there are betwen two ad four columns per table that would need the FTI put on
them. FTI on sql2000 failed miserably as it was slow and my tables were
probably too large (smallest is 10 million rows and largest is 800 million
rows).
I would like to know if 2005 FTI, since its been incorporated into the sql
engine, has had its upper limits increased in regards to the size of db can
be indexed.
also, can you replicate a FTI to another server along with its table?
thanks!!!
I believe it was tested to 20 million rows, if not more.
Yes, you can replicate SQL Server 2005 Full Text Indexes and their tables.
Hilary
"Carl Henthorn" wrote:
> I have numerous vldb's that my mgmt want to have full text indexed. Usually
> there are betwen two ad four columns per table that would need the FTI put on
> them. FTI on sql2000 failed miserably as it was slow and my tables were
> probably too large (smallest is 10 million rows and largest is 800 million
> rows).
> I would like to know if 2005 FTI, since its been incorporated into the sql
> engine, has had its upper limits increased in regards to the size of db can
> be indexed.
> also, can you replicate a FTI to another server along with its table?
> thanks!!!
|||that is not very comforting. Why is FT not tested against larger databases?
Is there a max size to the catalog? is there a peformance issue?
"Hilary Cotter" wrote:
[vbcol=seagreen]
> I believe it was tested to 20 million rows, if not more.
> Yes, you can replicate SQL Server 2005 Full Text Indexes and their tables.
> Hilary
> "Carl Henthorn" wrote:
|||I am not sure what the limits that Microsoft has tested it against.
My statistic comes from here:
http://msdn.microsoft.com/library/de...05ftsearch.asp
In this sentence:
.. For example, on the same hardware, with the same data set, building a
full-text index on 20 million rows of character-based text data took roughly
14 days in SQL Server 2000, while in SQL Server 2005, the same index
required less than 10 hours.
But reading on I see this:
SQL Server 2005 full-text catalogs, by contrast, have been tested with and
can support up to 2,000,000,000 rows of data (based on the 4-byte internal
DocId). Further, the indexing process also scales up to that amount of data
on a larger number of CPUs. Scalability of the text engine at indexing time
over multiple CPUs has also improved significantly over previous releases
(the full-text engine scales well up to roughly 16 CPUs on a 32-bit
platform).
I take it that is not a typo and Andrew means 2 billion.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:0EF93B7D-A916-4BB3-B171-3DF9A29A48A7@.microsoft.com...[vbcol=seagreen]
> that is not very comforting. Why is FT not tested against larger
> databases?
> Is there a max size to the catalog? is there a peformance issue?
> "Hilary Cotter" wrote:
there are betwen two ad four columns per table that would need the FTI put on
them. FTI on sql2000 failed miserably as it was slow and my tables were
probably too large (smallest is 10 million rows and largest is 800 million
rows).
I would like to know if 2005 FTI, since its been incorporated into the sql
engine, has had its upper limits increased in regards to the size of db can
be indexed.
also, can you replicate a FTI to another server along with its table?
thanks!!!
I believe it was tested to 20 million rows, if not more.
Yes, you can replicate SQL Server 2005 Full Text Indexes and their tables.
Hilary
"Carl Henthorn" wrote:
> I have numerous vldb's that my mgmt want to have full text indexed. Usually
> there are betwen two ad four columns per table that would need the FTI put on
> them. FTI on sql2000 failed miserably as it was slow and my tables were
> probably too large (smallest is 10 million rows and largest is 800 million
> rows).
> I would like to know if 2005 FTI, since its been incorporated into the sql
> engine, has had its upper limits increased in regards to the size of db can
> be indexed.
> also, can you replicate a FTI to another server along with its table?
> thanks!!!
|||that is not very comforting. Why is FT not tested against larger databases?
Is there a max size to the catalog? is there a peformance issue?
"Hilary Cotter" wrote:
[vbcol=seagreen]
> I believe it was tested to 20 million rows, if not more.
> Yes, you can replicate SQL Server 2005 Full Text Indexes and their tables.
> Hilary
> "Carl Henthorn" wrote:
|||I am not sure what the limits that Microsoft has tested it against.
My statistic comes from here:
http://msdn.microsoft.com/library/de...05ftsearch.asp
In this sentence:
.. For example, on the same hardware, with the same data set, building a
full-text index on 20 million rows of character-based text data took roughly
14 days in SQL Server 2000, while in SQL Server 2005, the same index
required less than 10 hours.
But reading on I see this:
SQL Server 2005 full-text catalogs, by contrast, have been tested with and
can support up to 2,000,000,000 rows of data (based on the 4-byte internal
DocId). Further, the indexing process also scales up to that amount of data
on a larger number of CPUs. Scalability of the text engine at indexing time
over multiple CPUs has also improved significantly over previous releases
(the full-text engine scales well up to roughly 16 CPUs on a 32-bit
platform).
I take it that is not a typo and Andrew means 2 billion.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:0EF93B7D-A916-4BB3-B171-3DF9A29A48A7@.microsoft.com...[vbcol=seagreen]
> that is not very comforting. Why is FT not tested against larger
> databases?
> Is there a max size to the catalog? is there a peformance issue?
> "Hilary Cotter" wrote:
2005 EE: using full-text indexing
Hello,
I am learning SQL Server 2005 Expres Edition. I need to create full
text index on Books.Remarks column in my database. I tried:
Hi RAM,
Now I have installed Advanced Services. But full-text search does not
work... Could you help me please?|||On Wed, 19 Jul 2006 09:03:19 +0200, RAM wrote:
>
>Now I have installed Advanced Services. But full-text search does not
>work... Could you help me please?
Hi RAM,
>>
>>Now I have installed Advanced Services. But full-text search does not
>>work... Could you help me please?
>
>Hi RAM,
>
>I'm sorry. I know from googling that full-text is included in Advanced
>Services, but I've never worked with or installed full-text search in
>any edition of SQL Server.
>
>Maybe you should ask this in microsoft.public.sqlserver.fulltext. The
>experts there will probably be better equipped to help you.
I don't see the group microsoft.public.sqlserver.fulltext.|||On Thu, 27 Jul 2006 15:12:35 +0200, RAM <r_ahimsa_m@.poczta.onet.pl>
wrote:
It does exist:
I am learning SQL Server 2005 Expres Edition. I need to create full
text index on Books.Remarks column in my database. I tried:
CREATE FULLTEXT CATALOG FTC_Books AS DEFAULT
CREATE FULLTEXT INDEX ON Books (Remarks) KEY INDEX IX_Books_Remarks ON
FTC_Books
Unfortunately I got error:
Full-Text Search is not installed, or a full-text component cannot be
loaded.
I don't understand this because I marked all options during setup.
Please help
/RAM/On Mon, 10 Jul 2006 13:16:49 +0200, R.A.M. wrote:
Quote:
Originally Posted by
>Hello,
>I am learning SQL Server 2005 Expres Edition. I need to create full
>text index on Books.Remarks column in my database. I tried:
>
>CREATE FULLTEXT CATALOG FTC_Books AS DEFAULT
>CREATE FULLTEXT INDEX ON Books (Remarks) KEY INDEX IX_Books_Remarks ON
>FTC_Books
>
>Unfortunately I got error:
>
>Full-Text Search is not installed, or a full-text component cannot be
>loaded.
>
>I don't understand this because I marked all options during setup.
>Please help
>/RAM/
Hi RAM,
Which version of SQL Server Express did yoou download? The "normal"
ediition ("SQL Server 2005 Express Edition" or "SQL Server 2005 Express
Edition SP1"), or the extended version ("SQL Server 2005 Express Edition
with Advanced Services SP1")?
As yoou can see on the feature comparison chart
(http://www.microsoft.com/sql/prodin...-features.mspx),
Full-text Search is only available for SQL Server Express in the SQL
Server Express with Advanced Services download.
--
Hugo Kornelis, SQL Server MVP|||On Mon, 10 Jul 2006 23:15:36 +0200, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALIDwrote:
Quote:
Originally Posted by
>Full-text Search is only available for SQL Server Express in the SQL
>Server Express with Advanced Services download.
Now I have installed Advanced Services. But full-text search does not
work... Could you help me please?|||On Wed, 19 Jul 2006 09:03:19 +0200, RAM wrote:
Quote:
Originally Posted by
>On Mon, 10 Jul 2006 23:15:36 +0200, Hugo Kornelis
><hugo@.perFact.REMOVETHIS.info.INVALIDwrote:
>
Quote:
Originally Posted by
>>Full-text Search is only available for SQL Server Express in the SQL
>>Server Express with Advanced Services download.
>
>Now I have installed Advanced Services. But full-text search does not
>work... Could you help me please?
Hi RAM,
I'm sorry. I know from googling that full-text is included in Advanced
Services, but I've never worked with or installed full-text search in
any edition of SQL Server.
Maybe you should ask this in microsoft.public.sqlserver.fulltext. The
experts there will probably be better equipped to help you.
--
Hugo Kornelis, SQL Server MVP|||On Thu, 20 Jul 2006 23:06:51 +0200, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALIDwrote:
Quote:
Originally Posted by
>On Wed, 19 Jul 2006 09:03:19 +0200, RAM wrote:
>
Quote:
Originally Posted by
>>On Mon, 10 Jul 2006 23:15:36 +0200, Hugo Kornelis
>><hugo@.perFact.REMOVETHIS.info.INVALIDwrote:
>>
Quote:
Originally Posted by
>>>Full-text Search is only available for SQL Server Express in the SQL
>>>Server Express with Advanced Services download.
>>
>>Now I have installed Advanced Services. But full-text search does not
>>work... Could you help me please?
>
>Hi RAM,
>
>I'm sorry. I know from googling that full-text is included in Advanced
>Services, but I've never worked with or installed full-text search in
>any edition of SQL Server.
>
>Maybe you should ask this in microsoft.public.sqlserver.fulltext. The
>experts there will probably be better equipped to help you.
I don't see the group microsoft.public.sqlserver.fulltext.|||On Thu, 27 Jul 2006 15:12:35 +0200, RAM <r_ahimsa_m@.poczta.onet.pl>
wrote:
Quote:
Originally Posted by
>I don't see the group microsoft.public.sqlserver.fulltext.
It does exist:
http://groups.google.com/group/micr...server.fulltext
Not all news servers carry all the newsgroups. For the Microsoft
groups it is best to get them directly from the Microsoft news servers
at msnews.microsoft.com.
Roy Harvey
Beacon Falls, CT
Saturday, February 11, 2012
2005 BoL
Hi
Why is it when I search BoL or click on links on the index I keep getting SQL Server Mobile help pages rather than (for example) the Transact SQL help pages that I want. I can usually find a link at the bottom of the page but is there any way to filter this stuff out in the first place?
Ta!where are you searching? google? msdn? or BOL on your own computer?|||BoL on my pc|||For example - ALTER TABLE in the index - I get a link that, when clicked, takes me to ALTER TABLE (SQL Server Mobile). Down the bottom there is a link to ALTER TABLE (Transact SQL). I want that one first :)|||Ok - filter by database engine rather than SQL Server 2005 and I don't appear to get the mobile stuff. Not sure what else I might lose too though...|||maybe this will help:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=647689&SiteID=1|||Jezemine - thank you. Another concise and accurate post :)
Out of curiosity, did you know about that already or (to my shame) did you just google because I did not find that one... :o
Anyway - will try in work tomoz.|||i had a vague recollection of seeing the post, but I didn't have the link memorized. ;)
so I resorted to google.
fyi, here's the search I did: http://www.google.com/search?q=remove+mobile+from+bol|||fyi, here's the search I did: http://www.google.com/search?q=remove+mobile+from+bolOh....
Dear :o
N00bie Poots|||see what you get for http://www.google.com/search?q=turn+off+activex and http://www.google.com/search?q=turn+off+javascript
;)|||Forgive my ignorance - what is the relationship?|||nothing, it's just that i happened to somehow end up as #1 or #2 on the search results for both those queries, when you'd think a microsoft page might come up ahead of me
or maybe not
sorry for the dissonance|||Lol - I read the culminated-in-the-SQL-Consultant-moniker article some time ago.
Are you just showing off?
Anyway - ActiveX settings checked and they are OFF OFF OFF :D
BTW - familiar with cognitive dissonance - curiously never come across it before without the cognitive bit.|||Are you just showing off?ah caint hep it!!
Why is it when I search BoL or click on links on the index I keep getting SQL Server Mobile help pages rather than (for example) the Transact SQL help pages that I want. I can usually find a link at the bottom of the page but is there any way to filter this stuff out in the first place?
Ta!where are you searching? google? msdn? or BOL on your own computer?|||BoL on my pc|||For example - ALTER TABLE in the index - I get a link that, when clicked, takes me to ALTER TABLE (SQL Server Mobile). Down the bottom there is a link to ALTER TABLE (Transact SQL). I want that one first :)|||Ok - filter by database engine rather than SQL Server 2005 and I don't appear to get the mobile stuff. Not sure what else I might lose too though...|||maybe this will help:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=647689&SiteID=1|||Jezemine - thank you. Another concise and accurate post :)
Out of curiosity, did you know about that already or (to my shame) did you just google because I did not find that one... :o
Anyway - will try in work tomoz.|||i had a vague recollection of seeing the post, but I didn't have the link memorized. ;)
so I resorted to google.
fyi, here's the search I did: http://www.google.com/search?q=remove+mobile+from+bol|||fyi, here's the search I did: http://www.google.com/search?q=remove+mobile+from+bolOh....
Dear :o
N00bie Poots|||see what you get for http://www.google.com/search?q=turn+off+activex and http://www.google.com/search?q=turn+off+javascript
;)|||Forgive my ignorance - what is the relationship?|||nothing, it's just that i happened to somehow end up as #1 or #2 on the search results for both those queries, when you'd think a microsoft page might come up ahead of me
or maybe not
sorry for the dissonance|||Lol - I read the culminated-in-the-SQL-Consultant-moniker article some time ago.
Are you just showing off?
Anyway - ActiveX settings checked and they are OFF OFF OFF :D
BTW - familiar with cognitive dissonance - curiously never come across it before without the cognitive bit.|||Are you just showing off?ah caint hep it!!
Subscribe to:
Posts (Atom)