Video Screencast Help

BE 2012 - Rebuild index database

Created: 18 Sep 2013 • Updated: 18 Sep 2013 | 6 comments

Hi all,

 

I wanna know if there is a script that can create from scratch all the index present on the database, because my DB has reached 4GB on Sql express 2008 and i can't afford a sql licence for this.

 

What i can see is that the indexes are huge like 600Mb on Belog table as so the data like 2GB. I need 3665 days of log, that also could be an issue.

So my idea is to delete the indexes, defrag the tables, shrink DB and recreate the indexes again.

At the moment even the BE 2012 schedule database routine gives errors with so much data on it.

Operating Systems:

Comments 6 CommentsJump to latest comment

Rahul Kumar1's picture

Hi,

Note: Make sure you stop the SQLSERVER( BKUPEXEC) service and take a copy of the DATA folder before performing this.

Also you need to manually serach for the table having millions of entries by going to properties of each table.

 

The following SQL script can be used to delete multiple rows of data from tables with excessive database to provide additional space for the BEDB. 

  1. Open SQL Management Studio
     
  2. Check the properties of the tables in BEDB to see which table has millions of rows. Most common are "JobHistoryDetailInfo" and "Alerts"
     
  3. Right-click BEDB and select "New Query"
     
  4. Copy and paste the script below to delete 1 million rows
  • "TABLENAME" in the scrip below should be replaced with the name of the table you want to delete rows from. Most common are "JobHistoryDetailInfo" and "Alerts"
     
  • Do not delete more than 1 million rows at a time, SQL will hang (once the query completes, you can simply run it again to delete more rows)
     
  • Starts deleting the oldest data first 

Delete Top (200000) From TABLENAME
DBCC Shrinkdatabase (BEDB)
Delete Top (200000) From TABLENAME
DBCC Shrinkdatabase (BEDB)
Delete Top (200000) From TABLENAME
DBCC Shrinkdatabase (BEDB)
Delete Top (200000) From TABLENAME
DBCC Shrinkdatabase (BEDB)
Delete Top (200000) From TABLENAME
DBCC Shrinkdatabase (BEDB)

 

RobRodrigues's picture

One little thing.

My BE datamaintenance to the DB, has begun to give errors like the script or query has took more than 10 minutes or so.

In my sql i have wait forever to cpmpete the query.

Is BE using some kind of control to quit the querys if they took more than 10 minutes execution.

Rahul Kumar1's picture

BE does not use any control to quit quries

RobRodrigues's picture

only to finish here is the result details of BE maintenance:

Summary of database maintenance activity:

* Performed database consistency check for BEDB database
* Saved contents of BEDB database
* Optimized BEDB database size from 3418.00 MB to 3183.69 MB
* Deleted expired data for BEDB database:
     0 empty legacy backup-to-disk folders were deleted
     0 expired reports were deleted
     2785 expired job histories were deleted
     0 expired alert histories were deleted
     0 expired job logs were deleted

Maintenance has failed: -536837662, -536837662: :: -2147217871:O tempo limite da consulta expirou
Total elapsed time: 00:07:11

But the job completed sucessfuly.

 

I really have 13 Millions of lines in Belog, my sql of course gives errors....

Rahul Kumar1's picture

Rob,

          The query which I ent you above. Did you tried that on BELog table. Please follow the steps mentioned above to fix this.

Thanks