CODEWITHSUNDEEP
sql-serversql-server-2000
drunkeneggs
drunkeneggs

Reputation: 11

SQL Server Index rebuild with no space

I've got a small DB (max size 2GB) on SQL Server 2000 (yes, I know).

The problem is that the database has hit its size limit and can't grow anymore.

Looking through the tables there is one which has indexes totalling about 950 MB, fragmentation over 30%, and I want to trim this down.

Having gone for an index rebuild (and re-organise - was desperate) I am getting error messages due to a lack of space in primary to create pages.

I think that with later versions of SQL Server I could disable the index and then rebuild, which shouldn't require any additional space, however using the version I'm on this is not available.

Any thoughts on how rebuild indexes in this scenario?

OK, took advice to drop and re-create. Only problem I had is with the creating...!

See below for statement used, SQL SMS kept throwing a syntax error for line 9 near a '(', don't think it likes the options, which is odd, seeing as I was lazy and got SMS to generate the script itself from the original index... As you may have noticed, I'm no DBA!

USE Database

GO

ALTER TABLE [db1].[_tbl]

ADD  CONSTRAINT [ck_unique] 

UNIQUE NONCLUSTERED 

(
    [DateID] ASC,
    [LRN] ASC,
    [Activity] ASC,
    [DateON] ASC
)

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 

ON [PRIMARY]

GO

Tell you what, looking at these options I think I might be able to get away without using them. Won't most of them apply by default?

Welcome to how I work! Have entered statement as above but without the 'with' section.

Now looking through the GUI on SQL Server Manageblah I can see that at least some of the options are OK.

PAD_INDEX is Off, set to recompute statistics on, sort in tempdb is off, so:

do I need to make some changes to reflect the other options? i.e.:

IGNORE_DUP_KEY=OFF, ONLINE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON

Really want to go pub->taxi->home->bed now!!!

Upvotes: 1

Views: 556

Answers (1)

Sushil
Sushil

Reputation: 196

If you are upto recreating index, you can use

USE 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 

DBCC DBREINDEX(@TableName,' ',90) 

FETCH NEXT FROM TableCursor INTO @TableName 

END 

CLOSE TableCursor 

DEALLOCATE TableCursor

If this completed with errors then try

dbcc checkdb(DatabaseName,repair_allow_data_loss)

I've been always using this to reindex my databases sized around 2 GB. Hope this helps

Upvotes: 1

Related Questions