Hello,
I have this error after a select query (wich works for years).
Serveur_: Msg 3624, Niveau 20, tat 1, Ligne 1
Location: recbase.cpp:1374
Expression: m_nVars > 0
SPID: 51
Process ID: 1188
Connexion interrompue
I made a checkdb and everything is Ok.
does anyone has an idea ?Maybe these articles:
http://support.microsoft.com/defaul...kb;en-us;311104
http://support.microsoft.com/defaul...kb;en-us;317089
can help you?
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Alexandra Bishop" <boccara@.netvision.net.il> wrote in message
news:9e09323a.0402110606.33a4dc1d@.posting.google.com...
> Hello,
> I have this error after a select query (wich works for years).
> Serveur : Msg 3624, Niveau 20, tat 1, Ligne 1
> Location: recbase.cpp:1374
> Expression: m_nVars > 0
> SPID: 51
> Process ID: 1188
> Connexion interrompue
> I made a checkdb and everything is Ok.
> does anyone has an idea ?
Showing posts with label wich. Show all posts
Showing posts with label wich. Show all posts
Monday, March 26, 2012
recbase.cpp:1374
Hello,
I have this error after a select query (wich works for years).
Serveur : Msg 3624, Niveau 20, État 1, Ligne 1
Location: recbase.cpp:1374
Expression: m_nVars > 0
SPID: 51
Process ID: 1188
Connexion interrompue
I made a checkdb and everything is Ok.
does anyone has an idea ?Maybe these articles:
http://support.microsoft.com/default.aspx?scid=kb;en-us;311104
http://support.microsoft.com/default.aspx?scid=kb;en-us;317089
can help you?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Alexandra Bishop" <boccara@.netvision.net.il> wrote in message
news:9e09323a.0402110606.33a4dc1d@.posting.google.com...
> Hello,
> I have this error after a select query (wich works for years).
> Serveur : Msg 3624, Niveau 20, État 1, Ligne 1
> Location: recbase.cpp:1374
> Expression: m_nVars > 0
> SPID: 51
> Process ID: 1188
> Connexion interrompue
> I made a checkdb and everything is Ok.
> does anyone has an idea ?sql
I have this error after a select query (wich works for years).
Serveur : Msg 3624, Niveau 20, État 1, Ligne 1
Location: recbase.cpp:1374
Expression: m_nVars > 0
SPID: 51
Process ID: 1188
Connexion interrompue
I made a checkdb and everything is Ok.
does anyone has an idea ?Maybe these articles:
http://support.microsoft.com/default.aspx?scid=kb;en-us;311104
http://support.microsoft.com/default.aspx?scid=kb;en-us;317089
can help you?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Alexandra Bishop" <boccara@.netvision.net.il> wrote in message
news:9e09323a.0402110606.33a4dc1d@.posting.google.com...
> Hello,
> I have this error after a select query (wich works for years).
> Serveur : Msg 3624, Niveau 20, État 1, Ligne 1
> Location: recbase.cpp:1374
> Expression: m_nVars > 0
> SPID: 51
> Process ID: 1188
> Connexion interrompue
> I made a checkdb and everything is Ok.
> does anyone has an idea ?sql
Wednesday, March 21, 2012
Rebuild indexes
Hello,
Wich command DBCC can rebuild all indexes in my database?
Thanks
LeandroLSPlease see this script, which I got from www.sqlserverfaq.com
BEGIN
SET NOCOUNT ON
-- Basis of script taken from BOL
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
DECLARE tables_cur CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
OPEN tables_cur
DECLARE @.ObjectName sysname
FETCH NEXT FROM tables_cur INTO @.ObjectName
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC( 'DBCC UPDATEUSAGE( 0, [' + @.ObjectName + '] ) WITH COUNT_ROWS,
NO_INFOMSGS' )
INSERT #fraglist
EXEC ( 'DBCC SHOWCONTIG( [' + @.ObjectName + '] ) WITH
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' )
FETCH NEXT FROM tables_cur INTO @.ObjectName
END
CLOSE tables_cur
DEALLOCATE tables_cur
DECLARE defrag_cur CURSOR FOR
SELECT ObjectName,
IndexName
FROM #fraglist fo
WHERE LogicalFrag > 1 -- % fragmentation
AND IndexID BETWEEN 1 AND 254
AND ( IndexID = 1
OR NOT EXISTS (
SELECT * -- If clustered index to defrag then don't
do anything else (no point).
FROM #fraglist fi
WHERE fi.ObjectId = fo.ObjectId
AND fi.IndexID = 1
)
)
DECLARE @.IndexName sysname
DECLARE @.sql varchar(1000)
OPEN defrag_cur
FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Processing dbreindex on ' + RTRIM( @.ObjectName ) + ',
indexname=' + @.IndexName
SET @.sql = 'DBCC DBREINDEX( ''' + RTRIM( @.ObjectName ) + ''', ' +
@.IndexName + ')'
PRINT @.sql
EXEC( @.sql )
PRINT 'Complete.'
FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
END
DEALLOCATE defrag_cur
DROP TABLE #fraglist
END
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Hi,
Use the below script to reindex all tables in a database,
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT "Reindexing - " + @.TableName
DBCC DBREINDEX(@.TableName)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Thanks
Hari
MCDBA
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#6ODkDB4DHA.1804@.TK2MSFTNGP12.phx.gbl...
Here's another approach
EXEC sp_msForEachTable @.COMMAND1= 'DBCC DBREINDEX ( "?")'
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#6ODkDB4DHA.1804@.TK2MSFTNGP12.phx.gbl...
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
which will help you determine whether rebuilding or defragging is the right
way to go, and even whether your workload will benefit from either.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#6ODkDB4DHA.1804@.TK2MSFTNGP12.phx.gbl...
Wich command DBCC can rebuild all indexes in my database?
Thanks
LeandroLSPlease see this script, which I got from www.sqlserverfaq.com
BEGIN
SET NOCOUNT ON
-- Basis of script taken from BOL
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
DECLARE tables_cur CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
OPEN tables_cur
DECLARE @.ObjectName sysname
FETCH NEXT FROM tables_cur INTO @.ObjectName
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC( 'DBCC UPDATEUSAGE( 0, [' + @.ObjectName + '] ) WITH COUNT_ROWS,
NO_INFOMSGS' )
INSERT #fraglist
EXEC ( 'DBCC SHOWCONTIG( [' + @.ObjectName + '] ) WITH
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' )
FETCH NEXT FROM tables_cur INTO @.ObjectName
END
CLOSE tables_cur
DEALLOCATE tables_cur
DECLARE defrag_cur CURSOR FOR
SELECT ObjectName,
IndexName
FROM #fraglist fo
WHERE LogicalFrag > 1 -- % fragmentation
AND IndexID BETWEEN 1 AND 254
AND ( IndexID = 1
OR NOT EXISTS (
SELECT * -- If clustered index to defrag then don't
do anything else (no point).
FROM #fraglist fi
WHERE fi.ObjectId = fo.ObjectId
AND fi.IndexID = 1
)
)
DECLARE @.IndexName sysname
DECLARE @.sql varchar(1000)
OPEN defrag_cur
FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Processing dbreindex on ' + RTRIM( @.ObjectName ) + ',
indexname=' + @.IndexName
SET @.sql = 'DBCC DBREINDEX( ''' + RTRIM( @.ObjectName ) + ''', ' +
@.IndexName + ')'
PRINT @.sql
EXEC( @.sql )
PRINT 'Complete.'
FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
END
DEALLOCATE defrag_cur
DROP TABLE #fraglist
END
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Hi,
Use the below script to reindex all tables in a database,
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT "Reindexing - " + @.TableName
DBCC DBREINDEX(@.TableName)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Thanks
Hari
MCDBA
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#6ODkDB4DHA.1804@.TK2MSFTNGP12.phx.gbl...
quote:|||Leanardo
> Hello,
> Wich command DBCC can rebuild all indexes in my database?
> Thanks
> LeandroLS
>
Here's another approach
EXEC sp_msForEachTable @.COMMAND1= 'DBCC DBREINDEX ( "?")'
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#6ODkDB4DHA.1804@.TK2MSFTNGP12.phx.gbl...
quote:|||Before you do this, take a moment to read the whitepaper at
> Hello,
> Wich command DBCC can rebuild all indexes in my database?
> Thanks
> LeandroLS
>
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
which will help you determine whether rebuilding or defragging is the right
way to go, and even whether your workload will benefit from either.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#6ODkDB4DHA.1804@.TK2MSFTNGP12.phx.gbl...
quote:
> Hello,
> Wich command DBCC can rebuild all indexes in my database?
> Thanks
> LeandroLS
>
Tuesday, March 20, 2012
Rebuild indexes
Hello,
Wich command DBCC can rebuild all indexes in my database?
Thanks
LeandroLSPlease see this script, which I got from www.sqlserverfaq.com
BEGIN
SET NOCOUNT ON
-- Basis of script taken from BOL
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
DECLARE tables_cur CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
OPEN tables_cur
DECLARE @.ObjectName sysname
FETCH NEXT FROM tables_cur INTO @.ObjectName
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC( 'DBCC UPDATEUSAGE( 0, [' + @.ObjectName + '] ) WITH COUNT_ROWS,
NO_INFOMSGS' )
INSERT #fraglist
EXEC ( 'DBCC SHOWCONTIG( [' + @.ObjectName + '] ) WITH
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' )
FETCH NEXT FROM tables_cur INTO @.ObjectName
END
CLOSE tables_cur
DEALLOCATE tables_cur
DECLARE defrag_cur CURSOR FOR
SELECT ObjectName,
IndexName
FROM #fraglist fo
WHERE LogicalFrag > 1 -- % fragmentation
AND IndexID BETWEEN 1 AND 254
AND ( IndexID = 1
OR NOT EXISTS (
SELECT * -- If clustered index to defrag then don't
do anything else (no point).
FROM #fraglist fi
WHERE fi.ObjectId = fo.ObjectId
AND fi.IndexID = 1
)
)
DECLARE @.IndexName sysname
DECLARE @.sql varchar(1000)
OPEN defrag_cur
FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Processing dbreindex on ' + RTRIM( @.ObjectName ) + ',
indexname=' + @.IndexName
SET @.sql = 'DBCC DBREINDEX( ''' + RTRIM( @.ObjectName ) + ''', ' +
@.IndexName + ')'
PRINT @.sql
EXEC( @.sql )
PRINT 'Complete.'
FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
END
DEALLOCATE defrag_cur
DROP TABLE #fraglist
END
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Hi,
Use the below script to reindex all tables in a database,
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT "Reindexing - " + @.TableName
DBCC DBREINDEX(@.TableName)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Thanks
Hari
MCDBA
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#6ODkDB4DHA.1804@.TK2MSFTNGP12.phx.gbl...
> Hello,
> Wich command DBCC can rebuild all indexes in my database?
> Thanks
> LeandroLS
>|||Leanardo
Here's another approach
EXEC sp_msForEachTable @.COMMAND1= 'DBCC DBREINDEX ( "?")'
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#6ODkDB4DHA.1804@.TK2MSFTNGP12.phx.gbl...
> Hello,
> Wich command DBCC can rebuild all indexes in my database?
> Thanks
> LeandroLS
>|||Before you do this, take a moment to read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
which will help you determine whether rebuilding or defragging is the right
way to go, and even whether your workload will benefit from either.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#6ODkDB4DHA.1804@.TK2MSFTNGP12.phx.gbl...
> Hello,
> Wich command DBCC can rebuild all indexes in my database?
> Thanks
> LeandroLS
>
Wich command DBCC can rebuild all indexes in my database?
Thanks
LeandroLSPlease see this script, which I got from www.sqlserverfaq.com
BEGIN
SET NOCOUNT ON
-- Basis of script taken from BOL
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
DECLARE tables_cur CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
OPEN tables_cur
DECLARE @.ObjectName sysname
FETCH NEXT FROM tables_cur INTO @.ObjectName
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC( 'DBCC UPDATEUSAGE( 0, [' + @.ObjectName + '] ) WITH COUNT_ROWS,
NO_INFOMSGS' )
INSERT #fraglist
EXEC ( 'DBCC SHOWCONTIG( [' + @.ObjectName + '] ) WITH
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' )
FETCH NEXT FROM tables_cur INTO @.ObjectName
END
CLOSE tables_cur
DEALLOCATE tables_cur
DECLARE defrag_cur CURSOR FOR
SELECT ObjectName,
IndexName
FROM #fraglist fo
WHERE LogicalFrag > 1 -- % fragmentation
AND IndexID BETWEEN 1 AND 254
AND ( IndexID = 1
OR NOT EXISTS (
SELECT * -- If clustered index to defrag then don't
do anything else (no point).
FROM #fraglist fi
WHERE fi.ObjectId = fo.ObjectId
AND fi.IndexID = 1
)
)
DECLARE @.IndexName sysname
DECLARE @.sql varchar(1000)
OPEN defrag_cur
FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Processing dbreindex on ' + RTRIM( @.ObjectName ) + ',
indexname=' + @.IndexName
SET @.sql = 'DBCC DBREINDEX( ''' + RTRIM( @.ObjectName ) + ''', ' +
@.IndexName + ')'
PRINT @.sql
EXEC( @.sql )
PRINT 'Complete.'
FETCH NEXT FROM defrag_cur INTO @.ObjectName, @.IndexName
END
DEALLOCATE defrag_cur
DROP TABLE #fraglist
END
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Hi,
Use the below script to reindex all tables in a database,
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT "Reindexing - " + @.TableName
DBCC DBREINDEX(@.TableName)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Thanks
Hari
MCDBA
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#6ODkDB4DHA.1804@.TK2MSFTNGP12.phx.gbl...
> Hello,
> Wich command DBCC can rebuild all indexes in my database?
> Thanks
> LeandroLS
>|||Leanardo
Here's another approach
EXEC sp_msForEachTable @.COMMAND1= 'DBCC DBREINDEX ( "?")'
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#6ODkDB4DHA.1804@.TK2MSFTNGP12.phx.gbl...
> Hello,
> Wich command DBCC can rebuild all indexes in my database?
> Thanks
> LeandroLS
>|||Before you do this, take a moment to read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
which will help you determine whether rebuilding or defragging is the right
way to go, and even whether your workload will benefit from either.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#6ODkDB4DHA.1804@.TK2MSFTNGP12.phx.gbl...
> Hello,
> Wich command DBCC can rebuild all indexes in my database?
> Thanks
> LeandroLS
>
Subscribe to:
Posts (Atom)