DBCC dbreindex

Hello All,
Can I use dbcc dbreindex in a live database or do i need to put it
offline first?
thanx,
joel| Hello All,
|
| Can I use dbcc dbreindex in a live database or do i need to put it
| offline first?
--
Hi Joel,
You can use dbreindex live and you don't need to put the database in
single-user mode. Of course you should run this command during off-peak
hours so your users will not get a performance hit.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||thanks eric,
also i want to know the fillfactor to use in my database which is 3GB in
size and doing a lot of INSERt and UPDATE.
Eric Cardenas wrote:
> | Hello All,
> |
> | Can I use dbcc dbreindex in a live database or do i need to put it
> | offline first?
> --
> Hi Joel,
> You can use dbreindex live and you don't need to put the database in
> single-user mode. Of course you should run this command during off-peak
> hours so your users will not get a performance hit.
> Hope this helps,
> --
> Eric Cárdenas
> SQL Server support
>|||| thanks eric,
| also i want to know the fillfactor to use in my database which is 3GB in
| size and doing a lot of INSERTs and UPDATEs.
--
SQL Server Books Online says:
Note Even for an application oriented for many insert and update
operations, the number of database reads typically outnumber database
writes by a factor of 5 to 10. Therefore, specifying a fill factor other
than the default can degrade database read performance by an amount
inversely proportional to the fill factor setting. For example, a fill
factor value of 50 percent can cause database read performance to degrade
by two times.
It is useful to set the fill factor option to another value only when a new
index is created on a table with existing data, and then only when future
changes in that data can be accurately predicted.
Hope this helps,
--
Eric Cárdenas
SQL Server support

DBCC CHECKDB question

Apologies if this is in the wrong section....That said...

We had an extended power outtage last week that took a MS SQL server offline, and while it was on a UPS that shut it down, we had some problems with the DB afterwards that may have also been related to the disk getting so full that a maintenance backup/etc. couldn't run. The database is now marked suspect.

I'd like to repair it if possible, and after some research I came across the DBCC CHECKDB procedure. I ran:

DBCC CHECKDB
( sgmsdb , REPAIR_REBUILD )
WITH ALL_ERRORMSGS

and received

Server: Msg 926, Level 10, State 1, Line 1
Database 'sgmsdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

It sounded like this procedure was one that might help with suspect databases. Does that mean that the DB is beyond repair with this tool, and is there another procedure other than restoring that I might use to repair a database?

Sine you cannot access your db currrently as it is marked suspect, try to bring it normal as follows,

1.execute this ssms sp_resetstatus 'dbname' and after that you need to restart sql services........if this method fails,

2.try to detach the db and then again attach it it should come online but if you cannot attach the db after detaching it try as follows,

(a) create a database with the same name in another directory as the one you're trying to attach
(b) re-create all filesgroups and files as necessary
(c) shutdown the server
(d) swap in the old mdf file and any ndf files
(e) bring up the server and let the database attempt to be recovered and then go into suspect mode
(f) put the database in single_user and emergency modes
(g) run DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) which will rebuild the log and run full repair
(h) return database to online, multi_user mode

3.if you fail in steps 1 and 2 you need to put the db into emergency mode

ALTER DATABASE DBNAME SET EMERGENCY and after that you can export the datas to another db using import export option..........

|||

These resource may help:

Corrupt or Suspect Database –Recommended Actions
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
http://www.myitforum.com/articles/18/view.asp?id=7381

|||

Refer these links and possitively post back if u could solve the issue. I have seen lot of solutions to this suspect database issue. But never seen a consistant one.

Refer

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1648300&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1902831&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1225944&SiteID=1

Madhu