March 24, 2002 at 5:51 am
Hello everyone,
Well, for some of you this is probably a lil off topic since it's *simple?!* stuff, but I really would like to know and thought this would be the best place to post it.
I recently installed SQL Server2000 from ms, converted a db from ms access and everything goes fine, except there are two things I don't get as of yet.
First off, there are many tables created in the database that I've never heard of before, *automatically generated*, as well stored procedures etc. etc. (like table: systypes, sysusers etc. etc.) should I keep those?, or can I delete those?
when I view the properties of the database, I have some problems understanding the basics of the file allocated, my access db was only 1.2 mb, this database is after importing 12 MB, and the transaction Log also lik 15 MB. After I made changes to the tablefields (ie nvarchar from 100 to 50) the database seems to grow BIGGER then smaller, it has an option to say restrict file growth, but how and when do you use that?, are there any reference urls where I can read more about this
Thank you for your time
Yours Truly,
Evan Kozel
March 24, 2002 at 7:32 am
Please ask questions any time. All we can ask is that you take a shot at figuring things out first!
Don't delete those extra tables. They are "system" tables and hold all the information that describe the tables, views, etc. Access has a very similar set of tables only it hides them by default. If you look around in Options you'll see something about "show system objects" - enable that to see them in Access.
The behavior you're seeing with regard to file sizes is normal, and again, very similar to Access. In many cases when you modify a table it creates a "temp" table with the new structure, copies all the data into it, deletes the original, then renames the "temp" table to the original name. So your db has become larger by the size of the table (or more). In Access you would do a repair and compact operation to get rid of unused objects, deleted records, etc. With SQL you remove the excess by using dbcc shrinkfile. It'll take a while to master everything involved in the file size issue, for now I recommend that you walk through the DB Maintenance Wizard and set it to rebuild indexes, back, shrink the db, etc - basically check an option on each tab. That will keep your db from getting out of hand while you learn.
Whether to restrict file growth depends on your school of thought. If you set a limit and the db reaches that limit, no one can do anything until the DBA increases the limit. On the other hand if you set it to expand by a certain percentage or MB then there is a chance that it could expand to fill the drive, stopping operations until you free up some disk space.
http://www.microsoft.com/sql is a good place to start, or http://www.msdn.microsoft.com. You should probably considering buying an "intro" book to SQL Server to help you get started. Lot's of great articles here and we like to answer questions too!
Andy
March 24, 2002 at 4:29 pm
Great advice from Andy. One note, if you use a maintenance plan, DO NOT repair problems. It causes issues.
Steve Jones
March 24, 2002 at 5:01 pm
Fair point. I think the only issue with repair with prior to current service packs it would at times leave your db in single user mode. I believe SQL7 SP3 or SQK2K SP1 or SP2 no longer exhibit that particular symptom. Because I like the maintenance plans, I used to just run a job after maint completed that would reset any db that was in single user mode back to multi user. Were there other issues?
Andy
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply