?
Solved

Convert SQL 2000 DB/LOG from autogrow/autoshrink to fixed size

Posted on 2012-08-18
8
Medium Priority
?
678 Views
Last Modified: 2012-08-22
Hello,

I have a SQL 2000 Server where all the databases have been set to autogrow and autoshrink. I am trying to eek every last drop of performance out of the box so I want to change the databases and logs to a fixed size.

Can anyone help me with the procedure to do this? I have plenty of disk space, OS sits on a RAID1, Logs sit on a RAID1 and the DB's sit on a RAID10. The app was coded so long ago that it is impossilbe to upgrade it over to 2005, 2008 or 20012.

Thanks much!
0
Comment
Question by:hoffmanconsultingllc
  • 4
  • 2
  • 2
8 Comments
 
LVL 71

Expert Comment

by:Qlemo
ID: 38308538
Setting fixed sizes is dangerous, because you always set it too small, and DB writes fail, sometimes without you getting notice of it quick enough. I cannot recommend that.

Of course you can (and should) enlarge the file size to something reasonable if you think there is some growth to expect. Setting the autogrowth to a bigger number is what you should do. My chunks for data files are usually 512 MB (but 64 MB is ok, too). Log files may grow by e.g. 64 MB. That way you do not get many expansion requests, leading to low fragmentation, while the expansion will not last too long (blocking any further operation while performing).

An absolute no-go for a productive database is autoshrink. Never ever set that up if the database is perpetual written and appended to. Files will get highly fragmented, data moved across the data files, putting them into reversed (!) order, and so on.
If you have to shrink files, do it manually. If you shrink data files with moving pages, recreate indexes.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 38308752
If you have planty of disk space why are you concern about the growth? Anyway to do that set the growth to ... 0.

This will bite you because sooner than later the log will need to grow, unless you make the log file size so big that will never be filled.
0
 
LVL 1

Author Comment

by:hoffmanconsultingllc
ID: 38308758
Neither of the above comments provide direction or assistance on how to accomplish this. I want to set the databases and log files to be big enough to handle several years of growth as I have plenty of disk space. I am concerned about the ongoing fragmentation of the DB and Logs that comes with using autogrow and autoshrink.

Can anyone help me with the procedures to do this?

Thanks.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 27

Expert Comment

by:Zberteoc
ID: 38308771
Ok, make it as big as you can and then set the size of growth to 1meg. That should do it. If is big enough it will never need to grow but if it will still grow will be very little. Anyway your goal will be achieved by the fact that the big size of the log file will make the growth event extremely rare, if it will eve happen.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 38308774
Actually if the log file size is big enough the size of the growth it will be irrelevant as it will probably never get to that point.
0
 
LVL 1

Author Comment

by:hoffmanconsultingllc
ID: 38308776
do i need to do anything to the db after this is done to defrag it and or reclaim space? My end goal is to increase performance.
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 1000 total points
ID: 38308786
Schedule a reindex job every week if necessary, which is equivalent to a defrag for the database file. The frequency you choose depends of how often the deletes and inserts are performed. Updates don't affect data. So fi you have frequent inserts/deletes once per week should do it, if not you can stretch t to 2 week or even once a month.

Schedule the job sometimes during a week-end night when the least activity is expected. The reindex locks the tables, one at a time, and it could affect resources and performance if the are big tables involved.
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 1000 total points
ID: 38309001
I agree but in one point. After setting up the starting size of each DB, perform a Rebuild Index maintenance task. The regular task can be reindex (Rebuild Index) if time needed and avoiding lock contemption is important, or Rebuild Index else (which is better as the index is built from scratch each time).

If you want to set up the size of each DB via SQL:
ALTER DATABASE [YourDB] MODIFY FILE ( NAME = N'YourDB_dat', SIZE = 4096MB , FILEGROWTH = 1024MB );

Open in new window

where YourDB_dat is the logical name of your MDF file, which as default consist of the DB name and "_dat" appended. It's similar for the transaction log.
But since this is a one-timer, you might be better off with doing it manually in Enterprise Manager or Management Studio.

Don't expect anything from setting this up - your main performance boost will come from the first Rebuild Index you perform, as your DBs are likely to be very fragmented at this time.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question