Right or wrong in index creation......

Hi,

like I said in other posts, I'm new to sql server, worked with informix for a long time. Right now I got into an "argument" with the present "dba", the indexes for all the tables in the database are being rebuilt, he wanted to start a monthly process, I told him that he can't do processing because when an index is built the table is locked, now, since I'm new at sql server I would like to know from the experts.... can you run process in sql server against tables when:

- indexes are being created
- the structure of a table is being modified
- the database is being shrunk

If we had been talking about informix then I would have probably slapped him if he tried to "teach" me about how things work, I'm pretty sure its the same in SQL but I think its better to have complete assureness of what I'm talking about before the slapping starts :)

Thanks in advance

Luis TorresThe ever-popular answer: It depends.

During a schema change, all bets are off. Only the process modifying the table structure has any access to the table at all.

During indexing, read operations are often allowed (depending on session settings), but write operations are usually blocked (although there are some creative ways to work around this too). In general, I recommend that you let the box have its way with the table while indexing, then you can have your turn later.

While shrinking the log, there is no material impact on access to the tables. While shrinking the data device(s), performance goes down a rat-hole, but blocking doesn't often occur. I'd still suggest letting the machine have its way with the tables while this is going on, but that's because I'm paranoid and usually have lots of other things to entertain me... If push came to shove, I'm pretty sure that this isn't a problem.

-PatP|||I know you can not insert or delete (and 99% sure you can not update) a table that is currently being indexed. SQL Server in its present form does not keep a "redo area" like Oracle to keep track of changes.

As for the others, shrinking the database is not really advised for a production environment. It tends to be costly, and since data pages have to be moved around, you are likely to see exclusive locking as a result. The major problem is, you will not be able to predict what tables will be locked.

Edit: Sniped again. Pat, are you on course for 4,000?|||Thanks for your replies, gives me better understanding of what I'm doing and talking about :)|||Edit: Sniped again. Pat, are you on course for 4,000?I might just make it. I've been so busy at the orifice that I don't always know which way is up (and they keep re-definiing just what "up" means constantly). I think I'll make it though!

...gives me better understanding of what I'm doing and talking aboutAlways a good thing in my book. I like it when I've got a strong clue what I'm talking about!

-PatP|||And speaking of books..you should probably go get one asap...did you get the client side tools installed?

Have you looked at books online?

EDIT: And, if the DBA is just reindexing everything, just because, that's not a good idea if they don't need to be done.

They need to check the amount of fragmentation.

I mean, why Reindex a code table?

EDIT AGAIN: You might also want to look at DBCC INDEXDEFRAG|||Brett,

I have definetly started reading books, specially BOL. I started training myself at the beginning of the year and now I'm allowed to "play" with the production database. I've been a dba for many years (close to 10) so I'm more than comfortable around them, just need to get used to the way SQLServer works.

I would have been able to get the answer to the questions that I asked from BOL but something that time hasn't finished teaching me is patience and I when the actual "dba" started "teaching" me (he is new to databases) about things I knew or had a very accurate idea of how they work then I got pissed and wanted to get an answer as fast as I could from the best source I could find (which is of course the experience of other dbas). Something that time HAS taught me is to not open my mouth until I'm completely sure of what's coming out of it (because of my natural impatience I tended to do a lot of goofups when I was younger).

Thanks again for al your coments and have an excelent end of day :)

Luis Torres