Can I shrink databases etc using MSDE 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

MSDE (2000)

Can I shrink databases etc using MSDE 2000

Author

Topic

JonnyG
Starting Member

26 Posts

Posted - 2005-12-12 : 10:32:45

Is it wise to shrink database tables in order to keep them healthy, or are they ok without shrinking at all ever?

Is it possible to shrink the tables (remove wasted space) using the MSDE, if so, how?

thank you

Jon

Kristen
Test

22859 Posts

Posted - 2005-12-12 : 14:42:09

Don't do it!

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Why+is+my+LDF+Log+File+so+big

Kristen

JonnyG
Starting Member

26 Posts

Posted - 2005-12-12 : 14:49:58

So I shouldn't do it then? The reason I ask is that I have one table in particular that does the following:-

A user logs in via the application and the application writes to the table the name of the user that has just logged in.
When the user closes the application the application deletes the record of the logged-in user from the table.

There are probably somewhere in the region of 400 inserts and 400 deletes per day on this table (never more than about 50 records in the table at any time).

Will the size of this table grow uncontrollably as time goes by? What's the best way to handle this, or simply do nothing and just let it grow?

thanks

Jon

Kristen
Test

22859 Posts

Posted - 2005-12-12 : 15:25:33

"Will the size of this table grow uncontrollably as time goes by?"

Well it might - assuming that the users that log in are spread randomly throughout the table. However, if you DEFRAG, or REINDEX, your tables regularly the "freed-up" space will get reallocated to the available pool. So over time it will achieve acquiescence.

If you shrink it down then inserting new stuff will require an extension to the database - this take time to do, and cause fragmentation, and obviously reuse of the existing space would have been a better plan.

We do something similar to you - except that we don't delete the user's data when they log off, we keep it for around 5 days (in case we want to do some investigation of their session to see what went wrong etc.) So we have a routine that runs around 2AM that deletes any user data more than 5 days old. It deletes around 10,000,000 rows a day! and we DEFRAG the indexes and UPDATE the STATISICS daily, but we never shrink the database, and it seems to run OK

Kristen

JonnyG
Starting Member

26 Posts

Posted - 2005-12-12 : 19:04:51

So, using MSDE, how do I defrag a table?

I use an MSDE manager, I can run DBCC DBREINDEX(DATABASETABLE1) this seems to work. I tried DBCC DEFRAG(DATABASETABLE1) and this says it's not a valid command.

I'm using MSDE 2000.

thanks

Jon

JonnyG
Starting Member

26 Posts

Posted - 2005-12-12 : 19:45:36

OK, I've worked out the syntax now for indexdefrag.

So it would appear that DBCC DBREINDEX(DATABASETABLE1) works fine.

Now, what about the space used by the deleted records. Does dbreindex remove the wasted space or do I need to shrink the table to reclaim the wasted space?

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 02:02:34

Don't shrink the table unless you have done an exceptional delete. Just let the space be reused.

No, dbreindex is NOT going to "remove" the wasted space.

Kristen

JonnyG
Starting Member

26 Posts

Posted - 2005-12-13 : 04:12:16

One more question so I can get my head around this.

If I just use DBCC DBREINDEX(DATABASETABLE1) does that mean that deleted space in the database will be reused and therefore the database size will not grow?

Example:

Each day there are 400 writes to the table, and there are 400 deletes to the table. At the end of the day no records exist in the table. If I run DBCC DBREINDEX(DATABASETABLE1) does this mean that the deleted space in the table gets reused so that the table does not infinitely grow in megabytes???

Just need to be sure that I'm doing the right thing!

thank you

Jon

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 04:49:58

"If I just use DBCC DBREINDEX(DATABASETABLE1) does that mean that deleted space in the database will be reused and therefore the database size will not grow?"

My understanding is:

REINDEX (as distinct from DEFRAG) will copy the whole index to a "spare" part of the database. If there is not enough spare space the database will first be extended. For a clustered index this "copy" will be the whole of the index plus the whole of the data - which is quite a lot of space! but then the original space that that data took up will be available for reuse.

So in your example of "Add a bunch of stuff, and then delete it all, and then reindex" I reckon you will be back to ground zero

Kristen

JonnyG
Starting Member

26 Posts

Posted - 2005-12-13 : 05:37:12

That's excellent news. Just what I was wanting to hear.

and one final final final question if I may, is there a way to call DBCC DBREINDEX(DATABASETABLE1) via ADO, if so, does anyone know how to do it?

One other question while I think about it. My table has an id field that auto increments. What is the largest number the table can have?

thanks for all your help

Jon

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 06:29:05

"That's excellent news. Just what I was wanting to hear."

I hope I've got it right then!

"is there a way to call DBCC DBREINDEX(DATABASETABLE1) via ADO"

Don't see why not, but this is a scheduler in SQL Server, so you'd be better of to set it up as a "job" in SQL Server and let it run there.

"What is the largest number the table can have?"

Should be the largest value that the datatype can hold - See BooksOnLine "data types-SQL Server, described"

Kristen

JonnyG
Starting Member

26 Posts

Posted - 2005-12-13 : 08:05:09

what would be the T-SQL command (syntax) to call DBCC DBREINDEX(DATABASETABLE1)?

I can setup a schedule using MSDE Manager but I don't know what the syntax should be.

My database is called mydatabase
username is admin1
password is pass
tablename is databasetable1

any ideas?

thanks

Jon

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 09:07:05

AFAICR:

DBCC DBREINDEX ('MyTableName', 'MyIndexName') WITH NO_INFOMSGS

Have a look at Tara's blog for some ideas! :

http://weblogs.sqlteam.com/tarad/category/95.aspx

"username is admin1
password is pass"

A scheduled task, in SQL Server, will run "as" the user configured for SQL Agent - so that needs to have sufficient permission.

Kristen

JonnyG
Starting Member

26 Posts

Posted - 2006-01-06 : 08:01:09

Interestingly I ran a test over the christmas holiday period. I had a test application running on 12 computers. Every 5 seconds each pc created a record in my "login" table and then 5 seconds later it deleted it. Over the christmas period there were 2,200,000 new records created and 2,200,000 records deleted from the table. The database table did not grow in size at all...which is just what I wanted...so no need to reindex to get back wasted space.

jfk_lili
Starting Member

5 Posts

Posted - 2006-02-09 : 08:56:18

Hi, But the DEFRAG can be configured to performed automatically, just like the Auto SHRINK?

I am using the MSDE...And really dun want to use Auto SHRINK

Is there any related link in MSDE mentioned this?

Many Thanks for your input!!!

Kristen
Test

22859 Posts

Posted - 2006-02-09 : 11:46:04

AFAIK "DEFRAG" cannot be performed automatically, unless you build a task and schedule it.

If you want to build-your-own then Tara has some scripts which should get the job done:

http://weblogs.sqlteam.com/tarad/category/95.aspx

Note that AutoShrink (or any other method of invoking Shrink) is solving a different problem, and should be avoided in all normal circumstances)

Kristen

jfk_lili
Starting Member

5 Posts

Posted - 2006-02-10 : 04:09:07

Thanks very much!!! But for the MSDE, there is 2GB limit so the DEFRAG will still work in this situation?

I can not get any recommendation from MSDE. Do you any?

Kristen
Test

22859 Posts

Posted - 2006-02-10 : 06:41:16

"there is 2GB limit so the DEFRAG will still work in this situation"

Yes - but it isn't going to save you much [if any] space, just "tidy up" the structure of your indexes.

Maintenance of MSDE is no different to normal SQL Server - all the "good housekeeping" and Best Practice procedures will work the same.

Do you have a problem with the 2GB limit in MSDE that is causing you to be concerned?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources