Hi All
Ho do I go about determining a list of all columns in a table that have an
index on them?
Thanks
Hi David
For starters,
EXEC sp_helpindex <tablename>
will tell you all the indexes and what their key columns are.
If you need an actual list of columns, please specify what version this is
for.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> Hi All
> Ho do I go about determining a list of all columns in a table that have an
> index on them?
> Thanks
|||Hi Kalen
Thanks for the promt responce.
I am running SQL Server 2000 and I need an actual list of columns.
Thanks
"Kalen Delaney" wrote:
> Hi David
> For starters,
> EXEC sp_helpindex <tablename>
> will tell you all the indexes and what their key columns are.
> If you need an actual list of columns, please specify what version this is
> for.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
>
>
|||David
SELECT OBJECT_NAME(id) AS table_name,
name AS ind_name
FROM sysindexes
WHERE OBJECTPROPERTY(id,'IsUserTable')=1
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id,name, 'IsHypothetical') = 0
ORDER BY table_name
"David" <David@.discussions.microsoft.com> wrote in message
news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...[vbcol=seagreen]
> Hi Kalen
> Thanks for the promt responce.
> I am running SQL Server 2000 and I need an actual list of columns.
> Thanks
> "Kalen Delaney" wrote:
|||Hi David,
Please try the below mentioned SP and let me know if it solve ur problem: -
--EXEC dbo.ColumnsIndexed
Create PROCEDURE dbo.ColumnsIndexed
AS
SET NOCOUNT ON
DECLARE @.sTableName SYSNAME
DECLARE @.Tablename VARCHAR(50)
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
If exists (select object_name(id) from sysobjects where name='tblResults')
DROP TABLE tblResults
-- Create the temporary table...
CREATE TABLE tblResults
(
[name] nvarchar(50),
status int,
indid int,
OrigFillFactor int,
IndCol1 nvarchar(20),
IndCol2 nvarchar(20),
IndCol3 nvarchar(20),
IndCol4 nvarchar(20),
IndCol5 nvarchar(20),
IndCol6 nvarchar(20),
IndCol7 nvarchar(20),
IndCol8 nvarchar(20),
IndCol9 nvarchar(20),
IndCol10 nvarchar(20),
IndCol11 nvarchar(20),
IndCol12 nvarchar(20),
IndCol13 nvarchar(20),
IndCol14 nvarchar(20),
IndCol15 nvarchar(20),
IndCol16 nvarchar(20),
SegName nvarchar(20),
FullTextKey int,
Descending int,
Computed int ,
IsTable int
)
-- Populate the temp table...
INSERT @.t_TableNames_Temp
select name from sysobjects where xtype in ('S','U')order by name
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
Select @.tablename = rtrim(table_name) from @.t_TableNames_Temp
--PRINT @.tablename
INSERT INTO tblResults
(name,status,indid,OrigFillFactor,IndCol1,IndCol2, IndCol3,IndCol4,IndCol5,IndCol6,IndCol7,IndCol8,In dCol9,IndCol10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndCol16,SegNa me,FullTextKey,Descending,Computed,IsTable)
Exec ('SP_MSHelpindex ' + @.tablename)
DELETE FROM @.t_TableNames_Temp WHERE @.tablename = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
--RETURN 0
-- Return the results...
--select * from tblresults
select distinct object_name(id) as Table_name
,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5,IndCol6,I ndCol7,IndCol8,IndCol9,IndCol10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndCol16
from sysindexes JOIN tblResults on
sysindexes.name=tblResults.name
SET NOCOUNT OFF
Regards
Manu Jaidka
"Uri Dimant" wrote:
> David
> SELECT OBJECT_NAME(id) AS table_name,
> name AS ind_name
> FROM sysindexes
> WHERE OBJECTPROPERTY(id,'IsUserTable')=1
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
> INDEXPROPERTY(id,name, 'IsHypothetical') = 0
> ORDER BY table_name
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...
>
>
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Tuesday, March 20, 2012
Check if Column has an index
Hi All
Ho do I go about determining a list of all columns in a table that have an
index on them?
ThanksHi David
For starters,
EXEC sp_helpindex <tablename>
will tell you all the indexes and what their key columns are.
If you need an actual list of columns, please specify what version this is
for.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> Hi All
> Ho do I go about determining a list of all columns in a table that have an
> index on them?
> Thanks|||Hi Kalen
Thanks for the promt responce.
I am running SQL Server 2000 and I need an actual list of columns.
Thanks
"Kalen Delaney" wrote:
> Hi David
> For starters,
> EXEC sp_helpindex <tablename>
> will tell you all the indexes and what their key columns are.
> If you need an actual list of columns, please specify what version this is
> for.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
>
>|||David
SELECT OBJECT_NAME(id) AS table_name,
name AS ind_name
FROM sysindexes
WHERE OBJECTPROPERTY(id,'IsUserTable')=1
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id,name, 'IsHypothetical') = 0
ORDER BY table_name
"David" <David@.discussions.microsoft.com> wrote in message
news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...[vbcol=seagreen]
> Hi Kalen
> Thanks for the promt responce.
> I am running SQL Server 2000 and I need an actual list of columns.
> Thanks
> "Kalen Delaney" wrote:
>|||Hi David,
Please try the below mentioned SP and let me know if it solve ur problem: -
--EXEC dbo.ColumnsIndexed
Create PROCEDURE dbo.ColumnsIndexed
AS
SET NOCOUNT ON
DECLARE @.sTableName SYSNAME
DECLARE @.Tablename VARCHAR(50)
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
If exists (select object_name(id) from sysobjects where name='tblResults')
DROP TABLE tblResults
-- Create the temporary table...
CREATE TABLE tblResults
(
[name] nvarchar(50),
status int,
indid int,
OrigFillFactor int,
IndCol1 nvarchar(20),
IndCol2 nvarchar(20),
IndCol3 nvarchar(20),
IndCol4 nvarchar(20),
IndCol5 nvarchar(20),
IndCol6 nvarchar(20),
IndCol7 nvarchar(20),
IndCol8 nvarchar(20),
IndCol9 nvarchar(20),
IndCol10 nvarchar(20),
IndCol11 nvarchar(20),
IndCol12 nvarchar(20),
IndCol13 nvarchar(20),
IndCol14 nvarchar(20),
IndCol15 nvarchar(20),
IndCol16 nvarchar(20),
SegName nvarchar(20),
FullTextKey int,
Descending int,
Computed int ,
IsTable int
)
-- Populate the temp table...
INSERT @.t_TableNames_Temp
select name from sysobjects where xtype in ('S','U')order by name
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
Select @.tablename = rtrim(table_name) from @.t_TableNames_Temp
--PRINT @.tablename
INSERT INTO tblResults
(name,status,indid,OrigFillFactor,IndCol
1,IndCol2,IndCol3,IndCol4,IndCol5,In
dCol6,IndCol7,IndCol8,IndCol9,IndCol10,I
ndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndC
ol16,SegName,FullTextKey,Descending,
Computed,IsTable)
Exec ('SP_MSHelpindex ' + @.tablename)
DELETE FROM @.t_TableNames_Temp WHERE @.tablename = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
--RETURN 0
-- Return the results...
--select * from tblresults
select distinct object_name(id) as Table_name
,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5
,IndCol6,IndCol7,IndCol8,IndCol9,Ind
Col10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndC
ol16
from sysindexes JOIN tblResults on
sysindexes.name=tblResults.name
SET NOCOUNT OFF
Regards
Manu Jaidka
"Uri Dimant" wrote:
> David
> SELECT OBJECT_NAME(id) AS table_name,
> name AS ind_name
> FROM sysindexes
> WHERE OBJECTPROPERTY(id,'IsUserTable')=1
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
> INDEXPROPERTY(id,name, 'IsHypothetical') = 0
> ORDER BY table_name
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...
>
>
Ho do I go about determining a list of all columns in a table that have an
index on them?
ThanksHi David
For starters,
EXEC sp_helpindex <tablename>
will tell you all the indexes and what their key columns are.
If you need an actual list of columns, please specify what version this is
for.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> Hi All
> Ho do I go about determining a list of all columns in a table that have an
> index on them?
> Thanks|||Hi Kalen
Thanks for the promt responce.
I am running SQL Server 2000 and I need an actual list of columns.
Thanks
"Kalen Delaney" wrote:
> Hi David
> For starters,
> EXEC sp_helpindex <tablename>
> will tell you all the indexes and what their key columns are.
> If you need an actual list of columns, please specify what version this is
> for.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
>
>|||David
SELECT OBJECT_NAME(id) AS table_name,
name AS ind_name
FROM sysindexes
WHERE OBJECTPROPERTY(id,'IsUserTable')=1
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id,name, 'IsHypothetical') = 0
ORDER BY table_name
"David" <David@.discussions.microsoft.com> wrote in message
news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...[vbcol=seagreen]
> Hi Kalen
> Thanks for the promt responce.
> I am running SQL Server 2000 and I need an actual list of columns.
> Thanks
> "Kalen Delaney" wrote:
>|||Hi David,
Please try the below mentioned SP and let me know if it solve ur problem: -
--EXEC dbo.ColumnsIndexed
Create PROCEDURE dbo.ColumnsIndexed
AS
SET NOCOUNT ON
DECLARE @.sTableName SYSNAME
DECLARE @.Tablename VARCHAR(50)
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
If exists (select object_name(id) from sysobjects where name='tblResults')
DROP TABLE tblResults
-- Create the temporary table...
CREATE TABLE tblResults
(
[name] nvarchar(50),
status int,
indid int,
OrigFillFactor int,
IndCol1 nvarchar(20),
IndCol2 nvarchar(20),
IndCol3 nvarchar(20),
IndCol4 nvarchar(20),
IndCol5 nvarchar(20),
IndCol6 nvarchar(20),
IndCol7 nvarchar(20),
IndCol8 nvarchar(20),
IndCol9 nvarchar(20),
IndCol10 nvarchar(20),
IndCol11 nvarchar(20),
IndCol12 nvarchar(20),
IndCol13 nvarchar(20),
IndCol14 nvarchar(20),
IndCol15 nvarchar(20),
IndCol16 nvarchar(20),
SegName nvarchar(20),
FullTextKey int,
Descending int,
Computed int ,
IsTable int
)
-- Populate the temp table...
INSERT @.t_TableNames_Temp
select name from sysobjects where xtype in ('S','U')order by name
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
Select @.tablename = rtrim(table_name) from @.t_TableNames_Temp
--PRINT @.tablename
INSERT INTO tblResults
(name,status,indid,OrigFillFactor,IndCol
1,IndCol2,IndCol3,IndCol4,IndCol5,In
dCol6,IndCol7,IndCol8,IndCol9,IndCol10,I
ndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndC
ol16,SegName,FullTextKey,Descending,
Computed,IsTable)
Exec ('SP_MSHelpindex ' + @.tablename)
DELETE FROM @.t_TableNames_Temp WHERE @.tablename = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
--RETURN 0
-- Return the results...
--select * from tblresults
select distinct object_name(id) as Table_name
,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5
,IndCol6,IndCol7,IndCol8,IndCol9,Ind
Col10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndC
ol16
from sysindexes JOIN tblResults on
sysindexes.name=tblResults.name
SET NOCOUNT OFF
Regards
Manu Jaidka
"Uri Dimant" wrote:
> David
> SELECT OBJECT_NAME(id) AS table_name,
> name AS ind_name
> FROM sysindexes
> WHERE OBJECTPROPERTY(id,'IsUserTable')=1
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
> INDEXPROPERTY(id,name, 'IsHypothetical') = 0
> ORDER BY table_name
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...
>
>
Check if Column has an index
Hi All
Ho do I go about determining a list of all columns in a table that have an
index on them?
ThanksHi David
For starters,
EXEC sp_helpindex <tablename>
will tell you all the indexes and what their key columns are.
If you need an actual list of columns, please specify what version this is
for.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> Hi All
> Ho do I go about determining a list of all columns in a table that have an
> index on them?
> Thanks|||Hi Kalen
Thanks for the promt responce.
I am running SQL Server 2000 and I need an actual list of columns.
Thanks
"Kalen Delaney" wrote:
> Hi David
> For starters,
> EXEC sp_helpindex <tablename>
> will tell you all the indexes and what their key columns are.
> If you need an actual list of columns, please specify what version this is
> for.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> > Hi All
> >
> > Ho do I go about determining a list of all columns in a table that have an
> > index on them?
> >
> > Thanks
>
>|||David
SELECT OBJECT_NAME(id) AS table_name,
name AS ind_name
FROM sysindexes
WHERE OBJECTPROPERTY(id,'IsUserTable')=1
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id,name, 'IsHypothetical') = 0
ORDER BY table_name
"David" <David@.discussions.microsoft.com> wrote in message
news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...
> Hi Kalen
> Thanks for the promt responce.
> I am running SQL Server 2000 and I need an actual list of columns.
> Thanks
> "Kalen Delaney" wrote:
>> Hi David
>> For starters,
>> EXEC sp_helpindex <tablename>
>> will tell you all the indexes and what their key columns are.
>> If you need an actual list of columns, please specify what version this
>> is
>> for.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
>> > Hi All
>> >
>> > Ho do I go about determining a list of all columns in a table that have
>> > an
>> > index on them?
>> >
>> > Thanks
>>|||Hi David,
Please try the below mentioned SP and let me know if it solve ur problem: -
--EXEC dbo.ColumnsIndexed
Create PROCEDURE dbo.ColumnsIndexed
AS
SET NOCOUNT ON
DECLARE @.sTableName SYSNAME
DECLARE @.Tablename VARCHAR(50)
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
If exists (select object_name(id) from sysobjects where name='tblResults')
DROP TABLE tblResults
-- Create the temporary table...
CREATE TABLE tblResults
(
[name] nvarchar(50),
status int,
indid int,
OrigFillFactor int,
IndCol1 nvarchar(20),
IndCol2 nvarchar(20),
IndCol3 nvarchar(20),
IndCol4 nvarchar(20),
IndCol5 nvarchar(20),
IndCol6 nvarchar(20),
IndCol7 nvarchar(20),
IndCol8 nvarchar(20),
IndCol9 nvarchar(20),
IndCol10 nvarchar(20),
IndCol11 nvarchar(20),
IndCol12 nvarchar(20),
IndCol13 nvarchar(20),
IndCol14 nvarchar(20),
IndCol15 nvarchar(20),
IndCol16 nvarchar(20),
SegName nvarchar(20),
FullTextKey int,
Descending int,
Computed int ,
IsTable int
)
-- Populate the temp table...
INSERT @.t_TableNames_Temp
select name from sysobjects where xtype in ('S','U')order by name
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
Select @.tablename = rtrim(table_name) from @.t_TableNames_Temp
--PRINT @.tablename
INSERT INTO tblResults
(name,status,indid,OrigFillFactor,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5,IndCol6,IndCol7,IndCol8,IndCol9,IndCol10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndCol16,SegName,FullTextKey,Descending,Computed,IsTable)
Exec ('SP_MSHelpindex ' + @.tablename)
DELETE FROM @.t_TableNames_Temp WHERE @.tablename = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
--RETURN 0
-- Return the results...
--select * from tblresults
select distinct object_name(id) as Table_name
,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5,IndCol6,IndCol7,IndCol8,IndCol9,IndCol10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndCol16
from sysindexes JOIN tblResults on
sysindexes.name=tblResults.name
SET NOCOUNT OFF
Regards
Manu Jaidka
"Uri Dimant" wrote:
> David
> SELECT OBJECT_NAME(id) AS table_name,
> name AS ind_name
> FROM sysindexes
> WHERE OBJECTPROPERTY(id,'IsUserTable')=1
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
> INDEXPROPERTY(id,name, 'IsHypothetical') = 0
> ORDER BY table_name
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...
> > Hi Kalen
> >
> > Thanks for the promt responce.
> >
> > I am running SQL Server 2000 and I need an actual list of columns.
> >
> > Thanks
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi David
> >>
> >> For starters,
> >> EXEC sp_helpindex <tablename>
> >>
> >> will tell you all the indexes and what their key columns are.
> >>
> >> If you need an actual list of columns, please specify what version this
> >> is
> >> for.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://sqlblog.com
> >>
> >>
> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> >> > Hi All
> >> >
> >> > Ho do I go about determining a list of all columns in a table that have
> >> > an
> >> > index on them?
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>
Ho do I go about determining a list of all columns in a table that have an
index on them?
ThanksHi David
For starters,
EXEC sp_helpindex <tablename>
will tell you all the indexes and what their key columns are.
If you need an actual list of columns, please specify what version this is
for.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> Hi All
> Ho do I go about determining a list of all columns in a table that have an
> index on them?
> Thanks|||Hi Kalen
Thanks for the promt responce.
I am running SQL Server 2000 and I need an actual list of columns.
Thanks
"Kalen Delaney" wrote:
> Hi David
> For starters,
> EXEC sp_helpindex <tablename>
> will tell you all the indexes and what their key columns are.
> If you need an actual list of columns, please specify what version this is
> for.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> > Hi All
> >
> > Ho do I go about determining a list of all columns in a table that have an
> > index on them?
> >
> > Thanks
>
>|||David
SELECT OBJECT_NAME(id) AS table_name,
name AS ind_name
FROM sysindexes
WHERE OBJECTPROPERTY(id,'IsUserTable')=1
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id,name, 'IsHypothetical') = 0
ORDER BY table_name
"David" <David@.discussions.microsoft.com> wrote in message
news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...
> Hi Kalen
> Thanks for the promt responce.
> I am running SQL Server 2000 and I need an actual list of columns.
> Thanks
> "Kalen Delaney" wrote:
>> Hi David
>> For starters,
>> EXEC sp_helpindex <tablename>
>> will tell you all the indexes and what their key columns are.
>> If you need an actual list of columns, please specify what version this
>> is
>> for.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
>> > Hi All
>> >
>> > Ho do I go about determining a list of all columns in a table that have
>> > an
>> > index on them?
>> >
>> > Thanks
>>|||Hi David,
Please try the below mentioned SP and let me know if it solve ur problem: -
--EXEC dbo.ColumnsIndexed
Create PROCEDURE dbo.ColumnsIndexed
AS
SET NOCOUNT ON
DECLARE @.sTableName SYSNAME
DECLARE @.Tablename VARCHAR(50)
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
If exists (select object_name(id) from sysobjects where name='tblResults')
DROP TABLE tblResults
-- Create the temporary table...
CREATE TABLE tblResults
(
[name] nvarchar(50),
status int,
indid int,
OrigFillFactor int,
IndCol1 nvarchar(20),
IndCol2 nvarchar(20),
IndCol3 nvarchar(20),
IndCol4 nvarchar(20),
IndCol5 nvarchar(20),
IndCol6 nvarchar(20),
IndCol7 nvarchar(20),
IndCol8 nvarchar(20),
IndCol9 nvarchar(20),
IndCol10 nvarchar(20),
IndCol11 nvarchar(20),
IndCol12 nvarchar(20),
IndCol13 nvarchar(20),
IndCol14 nvarchar(20),
IndCol15 nvarchar(20),
IndCol16 nvarchar(20),
SegName nvarchar(20),
FullTextKey int,
Descending int,
Computed int ,
IsTable int
)
-- Populate the temp table...
INSERT @.t_TableNames_Temp
select name from sysobjects where xtype in ('S','U')order by name
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
Select @.tablename = rtrim(table_name) from @.t_TableNames_Temp
--PRINT @.tablename
INSERT INTO tblResults
(name,status,indid,OrigFillFactor,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5,IndCol6,IndCol7,IndCol8,IndCol9,IndCol10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndCol16,SegName,FullTextKey,Descending,Computed,IsTable)
Exec ('SP_MSHelpindex ' + @.tablename)
DELETE FROM @.t_TableNames_Temp WHERE @.tablename = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
--RETURN 0
-- Return the results...
--select * from tblresults
select distinct object_name(id) as Table_name
,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5,IndCol6,IndCol7,IndCol8,IndCol9,IndCol10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndCol16
from sysindexes JOIN tblResults on
sysindexes.name=tblResults.name
SET NOCOUNT OFF
Regards
Manu Jaidka
"Uri Dimant" wrote:
> David
> SELECT OBJECT_NAME(id) AS table_name,
> name AS ind_name
> FROM sysindexes
> WHERE OBJECTPROPERTY(id,'IsUserTable')=1
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
> INDEXPROPERTY(id,name, 'IsHypothetical') = 0
> ORDER BY table_name
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...
> > Hi Kalen
> >
> > Thanks for the promt responce.
> >
> > I am running SQL Server 2000 and I need an actual list of columns.
> >
> > Thanks
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi David
> >>
> >> For starters,
> >> EXEC sp_helpindex <tablename>
> >>
> >> will tell you all the indexes and what their key columns are.
> >>
> >> If you need an actual list of columns, please specify what version this
> >> is
> >> for.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://sqlblog.com
> >>
> >>
> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> >> > Hi All
> >> >
> >> > Ho do I go about determining a list of all columns in a table that have
> >> > an
> >> > index on them?
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>
Sunday, March 11, 2012
Check Data and Index Linkage FAILED
Hi,
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode?
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc Ferguson
The message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode? Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson
|||Don't check the "fix minor problems" in the maint plan. If it turns out you do have a problem, you
don't want an automated routine to fox this (I have an article on this on my website,
www.karaszi.com, but the site seems to be down at the moment, so I can't give exact UTL).
If you do check this option, the database indeed need to be in single user mode. Main plan tries to
set it to single user, but that will fail if any user is in the database, which seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users are logged in, it must
> have put the database in multi-user mode? Or is it because I have my Enterprise Manager open? I
> don't have the live database extended. I normally work on our test database and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson
|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You can
> then try to repair any single table at a later data during your maintenance
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>
|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
See my response. This is indeed what I was referring to. So you are set now. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. What is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode?
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc Ferguson
The message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode? Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson
|||Don't check the "fix minor problems" in the maint plan. If it turns out you do have a problem, you
don't want an automated routine to fox this (I have an article on this on my website,
www.karaszi.com, but the site seems to be down at the moment, so I can't give exact UTL).
If you do check this option, the database indeed need to be in single user mode. Main plan tries to
set it to single user, but that will fail if any user is in the database, which seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users are logged in, it must
> have put the database in multi-user mode? Or is it because I have my Enterprise Manager open? I
> don't have the live database extended. I normally work on our test database and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson
|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You can
> then try to repair any single table at a later data during your maintenance
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>
|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
See my response. This is indeed what I was referring to. So you are set now. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. What is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>
Check Data and Index Linkage FAILED
Hi,
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode'
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc FergusonThe message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode' Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson|||Don't check the "fix minor problems" in the maint plan. If it turns out you do have a problem, you
don't want an automated routine to fox this (I have an article on this on my website,
www.karaszi.com, but the site seems to be down at the moment, so I can't give exact UTL).
If you do check this option, the database indeed need to be in single user mode. Main plan tries to
set it to single user, but that will fail if any user is in the database, which seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users are logged in, it must
> have put the database in multi-user mode' Or is it because I have my Enterprise Manager open? I
> don't have the live database extended. I normally work on our test database and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You can
> then try to repair any single table at a later data during your maintenance
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
See my response. This is indeed what I was referring to. So you are set now. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. What is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
>> The message implies you were not just checking for problems but you were checking and trying to
>> repair any problems.
>> If you are doing a DBCC CHECKTABLE with one of the following optional parameters then the DB must
>> be in Single User mode :-
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Suggest running DBCC CHECKDB, this will report any errors it finds. You can then try to repair
>> any single table at a later data during your maintenance windows.
>> To put your db into single_user mode :-
>> ALTER DATABASE dbname SET Single_user with rollback immediate
>>
>>|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
--
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
>> The message implies you were not just checking for problems but you were
>> checking and trying to repair any problems.
>> If you are doing a DBCC CHECKTABLE with one of the following optional
>> parameters then the DB must be in Single User mode :-
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Suggest running DBCC CHECKDB, this will report any errors it finds. You
>> can then try to repair any single table at a later data during your
>> maintenance windows.
>> To put your db into single_user mode :-
>> ALTER DATABASE dbname SET Single_user with rollback immediate
>>
>>|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>> From your response, I assumed that it's the Integrity tab that I
>> should modify. So I unchecked "Attempt to repair any minor
>> problems". Is that correct or am I way off. Thanks again.
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode'
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc FergusonThe message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode' Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson|||Don't check the "fix minor problems" in the maint plan. If it turns out you do have a problem, you
don't want an automated routine to fox this (I have an article on this on my website,
www.karaszi.com, but the site seems to be down at the moment, so I can't give exact UTL).
If you do check this option, the database indeed need to be in single user mode. Main plan tries to
set it to single user, but that will fail if any user is in the database, which seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users are logged in, it must
> have put the database in multi-user mode' Or is it because I have my Enterprise Manager open? I
> don't have the live database extended. I normally work on our test database and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You can
> then try to repair any single table at a later data during your maintenance
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
See my response. This is indeed what I was referring to. So you are set now. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. What is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
>> The message implies you were not just checking for problems but you were checking and trying to
>> repair any problems.
>> If you are doing a DBCC CHECKTABLE with one of the following optional parameters then the DB must
>> be in Single User mode :-
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Suggest running DBCC CHECKDB, this will report any errors it finds. You can then try to repair
>> any single table at a later data during your maintenance windows.
>> To put your db into single_user mode :-
>> ALTER DATABASE dbname SET Single_user with rollback immediate
>>
>>|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
--
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
>> The message implies you were not just checking for problems but you were
>> checking and trying to repair any problems.
>> If you are doing a DBCC CHECKTABLE with one of the following optional
>> parameters then the DB must be in Single User mode :-
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Suggest running DBCC CHECKDB, this will report any errors it finds. You
>> can then try to repair any single table at a later data during your
>> maintenance windows.
>> To put your db into single_user mode :-
>> ALTER DATABASE dbname SET Single_user with rollback immediate
>>
>>|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>> From your response, I assumed that it's the Integrity tab that I
>> should modify. So I unchecked "Attempt to repair any minor
>> problems". Is that correct or am I way off. Thanks again.
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>
Check Data and Index Linkage FAILED
Hi,
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode'
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc FergusonThe message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode' Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson|||Don't check the "fix minor problems" in the maint plan. If it turns out you
do have a problem, you
don't want an automated routine to fox this (I have an article on this on my
website,
www.karaszi.com, but the site seems to be down at the moment, so I can't giv
e exact UTL).
If you do check this option, the database indeed need to be in single user m
ode. Main plan tries to
set it to single user, but that will fail if any user is in the database, wh
ich seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our
logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine
. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
are logged in, it must
> have put the database in multi-user mode' Or is it because I have my Ente
rprise Manager open? I
> don't have the live database extended. I normally work on our test databa
se and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You ca
n
> then try to repair any single table at a later data during your maintenanc
e
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unch
ecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks
again.
See my response. This is indeed what I was referring to. So you are set now.
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. W
hat is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should mo
dify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off.
Thanks again.
> Marc F.
> Ryan wrote:|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode'
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc FergusonThe message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode' Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson|||Don't check the "fix minor problems" in the maint plan. If it turns out you
do have a problem, you
don't want an automated routine to fox this (I have an article on this on my
website,
www.karaszi.com, but the site seems to be down at the moment, so I can't giv
e exact UTL).
If you do check this option, the database indeed need to be in single user m
ode. Main plan tries to
set it to single user, but that will fail if any user is in the database, wh
ich seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our
logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine
. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
are logged in, it must
> have put the database in multi-user mode' Or is it because I have my Ente
rprise Manager open? I
> don't have the live database extended. I normally work on our test databa
se and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You ca
n
> then try to repair any single table at a later data during your maintenanc
e
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unch
ecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks
again.
See my response. This is indeed what I was referring to. So you are set now.
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. W
hat is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should mo
dify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off.
Thanks again.
> Marc F.
> Ryan wrote:|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>
Thursday, March 8, 2012
Check Constraint
In our database we have an indexed field that is using unique values, so in the index we turned on the unique property. Now with some changes we made in the application this value can be Null (could not be Null in the past). When we have more as 1 value having value Null we get an exception of unique key violation.
Therefor we want to make an check constraint the checks if the value allready exists when the value is not Null.
Is this possible and how can it be done?
You're going to need to use a trigger to check the values in the way you described.|||Hi,I'm getting the same error.
When I try to add a new user I receive this errror:
ystem.Data.ConstraintException: Column 'User ID' is constrained to be unique. Value '{37525b24-d982-470d-9d1c-e3b3c7536958}' is already present.
at System.Data.UniqueConstraint.CheckConstraint(DataRow row, DataRowAction action)
at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent)
Unfortunately I have only one entry on my db with this User ID value. Moreover I have try to delete all my users with BU.
Can anybody help me?
Thanks a lot,
F.T
Tuesday, February 14, 2012
Char(4) or integer for Year / Quarter Field
Hello,
what's best for year resp. quarter Field, char(4) resp. char(1), integer or other?
Both are part of a composite index.
Thanks
Silaswhat does "resp." mean?
best for a numeric year like 1937, 2007, etc. is SMALLINT
best for quarter (1-4) is TINYINT|||Why not combine them both?
SMALLINT as 20074 (yyyyq)
this will work upto year 3276 and quarter 4.|||why not? because how would you pull out the rows for 2007 that way?
that's why not combine them :)|||why not? because how would you pull out the rows for 2007 that way?
that's why not combine them :)
That's why :-)
resp. = respectively , isn't it?|||I thought this could pull all records for 2007
Col1 >= 20071 And Col1 < 20081
but then again, I might be wrong.|||yes, that works, but it's clumsy
now show me how to get all the rows for the 3rd quarter only
clumsier and clumsier!!
;)
what's best for year resp. quarter Field, char(4) resp. char(1), integer or other?
Both are part of a composite index.
Thanks
Silaswhat does "resp." mean?
best for a numeric year like 1937, 2007, etc. is SMALLINT
best for quarter (1-4) is TINYINT|||Why not combine them both?
SMALLINT as 20074 (yyyyq)
this will work upto year 3276 and quarter 4.|||why not? because how would you pull out the rows for 2007 that way?
that's why not combine them :)|||why not? because how would you pull out the rows for 2007 that way?
that's why not combine them :)
That's why :-)
resp. = respectively , isn't it?|||I thought this could pull all records for 2007
Col1 >= 20071 And Col1 < 20081
but then again, I might be wrong.|||yes, that works, but it's clumsy
now show me how to get all the rows for the 3rd quarter only
clumsier and clumsier!!
;)
Subscribe to:
Posts (Atom)