.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Post New Web Links

DBCC Reindex

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

Dear All,


i am facing a problem in sql when i reindex on a table my table fragmentation still same.there is no improvement i want to know why this happned. i share you i am using sql server 2005 and table contains only 108  records and its index size is only 16 KB.


View Complete Post

More Related Resource Links

Stack Dump generated on the db and SQL tipped over DBCC CHECKDB came out clean

We have Profile db for sharepoint 2010 and the SQL tipped over just before which it generated the below dump. The DBCC CHECKDB on the database came out clean. The SQL Server is SQL 2008 SP1 CU5 and the memory on the server is 32GB with max. for SQL Server being 26 GB.  What could have caused this? There is nothing in the error log prior to this dump. name                                minimum     maximum     config_value run_value ----------------------------------- ----------- ----------- ------------ ----------- access check cache bucket count     0           65536       0            0 access check cache quota            0           2147483647  0            0 Ad Hoc Distributed Queries          0           1           0

Should DR testing include DBCC CheckDB

We have EMC storage and use SRDFa to replicate production data to another location.  In the coming weeks we'll be doing some DR testing that will involve interrupting replication, then mounting the 'DR' storage on our staging servers.  I'll attach the databases and testing will begin.  I'm wondering if standard practice should include running DBCC CheckDB against the 'recovered' databases before turning them over to the users. [We're running SQL 2005 SP2 Enterprise (64-bit) on a 6 node Active-Passive cluster.  The OS is Win 2003.]

Dbcc inputbuffer

Hi, Can anyone assist in troubleshooting one issue at my end. Using dbcc inputbuffer (spid) will give the current command that the process is executing. My question is how exactly we can find the exact query that the user has submitted to the sql server. The reason i was looking at it was because of some heavy blockings on the server, i used sp_who2 and found an spid which is causing huge blocking on the server. But when i used dbcc inputbuffer it is showing as an Insert command (Might be an inserts into the tempDB) and it is not the exact Insert command because the application where user's use to connect has no insert function on it. Is there any way to find the exact t-sql that the user has issued. Regards, Sandhya


    Do I have to run the DBCC CHECKDB('DB Name', REPAIR_REBUILD) in single user mode, have no users using the DB?  Not sure. If I have no users using the DB, can I still run without putting the server in single user mode?      Thanks for help   

Database temporary corruption - DBCC CHECKDB error

I've run into a very strange and frustrating recurring error. I have a Single instance SQL Server 2005 DBMS on Win2K3 Server x86.  The problem occurs with a *single* database on the system, the other databases are unaffected.  We primarily access this database for editing through an Access 2007 data project, though it's accessed (select only) from many places using odbc or oledb. Basically some sort of corruption (or apparent corruption) occurs after a server update and reboot (usually unrelated to the DB software).  After that, the database is fine sometimes, other times it is fine until we try to access it via the ADP.  At that point it becomes inaccesssible by the ADP, though it can still be accessed through all other methods.  Running DBCC CHECKDB (databasename) returns: Msg 211, Level 23, State 51, Line 1 Possible schema corruption. Run DBCC CHECKCATALOG. Running DBCC CHECKCATALOG (databasename) returns no errors. Restarting the database service (from inside SSMS typically) will fix it *eventually* after a few restarts.  I can find no rhyme or reason to the apparent corruption.  I'll be glad to look and post any other log entries that might be relevant.  I should note that the database was originally created way back in SQL Server 7, though it's got no real customizations, triggers, and very few constraints on it.  It's

DBCC CHECKDB Blocking Itself SQL 2008 MSCRM

Hello I've got a nightly job that runs DBCC CHECKDB WITH TABLERESULTS for all databases, using sp_msforeachdb, and then saves the results into a table, tidies them up, and then disappears off. On my test MSCRM database, the DBCC CHECKDB command is blocking itself. The only way to resolve this self-blocking seems to be to restart SQL. sp_who2 won't execute, but sp_who does. DBCC INPUTBUFFER tells me that it's the stored procedure I'm executing causing the blocking.  A bit more searching into sys.dm_os_waiting_tasks and and sys.dm_exec_connections suggests that this piece of code is causing the problem: DECLARE @BlobEater VARBINARY(8000)   SELECT @BlobEater = CheckIndex (ROWSET_COLUMN_FACT_BLOB)  FROM { IRowset 0xE093909D00000000 }  GROUP BY ROWSET_COLUMN_FACT_KEY  >> WITH ORDER BY    ROWSET_COLUMN_FACT_KEY,    ROWSET_COLUMN_SLOT_ID,    ROWSET_COLUMN_COMBINED_ID,    ROWSET_COLUMN_FACT_BLOB  OPTION (ORDER GROUP)  with a wait_type of LCK_M_IX the resource type is objectlock lockPartition=0 objid=34 subresource=FULL dbid=2 id=lock8defbd80 mode=SIX associatedObjectId=34 Trying to rollback the transaction doesn't help: it just sits in a rollback sort of a state. Trying to stop SQL doesn't work too well: going into Task Manager on the Server and killing the sqlserver.exe pr

DBCC physical_only can be done online?

DBCC physical_only can be done online? Is that a good practice to do this before you do your backups?


Hi, Is there any good link that descibes how to understand the output of DBCC SQLPERF (SPINLOCKSTATS). Regards, Zainu    

DBCC OPENTRAN does not report known explicitly open transactions

Using query analyzer, I can do simple select after BEGIN TRANSACTION, and in a separate or same session execute DBCC OPENTRAN and I get reported " no active transactions."  Tried on SS_2000 as well as SS_2008. Not sure what I am missing !   Any ideas ?      Thanks ! 

DBCC error and Log Files

Running SQL Server 2008 Standard - I am getting a DBCC error from Symantec Backup Exec.   What is the best place to start figuring how to fix this?   Also, I want to check the log files to be certain they are being truncated during backup.  Notes on when this was installed do not show where the log files are located.  How can I tell what drive and folder they are located.  
NC Beach Bum

Does DBCC CHECKDB without additional arguments perform repairs if there is a consistency problem?

Does DBCC CHECKDB without additional arguments perform repairs if there is a consistency problem?

Mr Shaw




When I am checking my Activity Monitor it show that one my sessions is running "DBCC INPUTBUFFER" and try to kill it but Nothing changed. What do I have to do?

Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.



does  DBCC CHECKDB  actually repair the SQL Database??

if so, could someone give me code on using the command?



 I am using the Express version of SQL Server, 2008.




Hi! Is it really necessary to use DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE when using SET STATISTICS IO ON and SET STATISTICS TIME ON for better results in assessing the performance of a query? I want to optimize some stored procedures using these tools. Any suggestions?

Running Dbcc CheckDB on Production Server databases.


Hi Guys 

I am new to SQL Server Administration. I have set up the Backup jobs according to the requirements from the Application team. Also I have set tip the  update stats, Reindexing Job every 2 weeks as far as the maintenance as maintenance is concerned. I am using  SQL Scripts in the SQL Agent to perform all these jobs

But i have not scheduled any Integrity Check (CheckDB ) Jobs on any of the database in any of the servers. I have databases of Size 1Gb-50 GB and few from 100-600 Gb. 

I want to set up the integrity jobs on all the databases. Can you please suggest me some best ways how I can schedule jobs every 2 or 3 weeks on small databases and on big databases like 100 - 600 GB in SQL Server 2005,2008. 




SQL Scheduled Job Defrag and Reindex


I had a question as I started planning this.  In SQL 2005, I hear that we can not do a reindex while users are in the database, but you can 'get away' with defragging while users are in there.

Is this correct?

And, are the SQL 2008 reindex/defrag conditions the same?

Thanks folks.

How can we reindex single table in s sql server 2008?



I have a requirment truncate and reindex the single table,how can we reindex a table?

If we truncate the table,is it necessary to reindex the table(if busines needs).

What is the code for reindex the table in sqlserver2008?




ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend