SQL Server 2005 File Sizes 2

[TheVillageIdiot27]

Hi

I have a problem with with a fairly new installation of SQL 2005. There are a number of databases which are essentially copies of each other used for different purposes, Live, Training, Development etc each with on log file and data file.

The problem is the physical files have become enormous - data files in the region of 3.5GB and log files less of a problem at around 50MB.

On the data files the autogrowth is 1MB Unristricted and on the log files the autogrowth is 10% Unristricted - I am not sure how relevent that is but the 1MB doesn't seem like a a very sensible figure on files that size to me.

I have tried to use DBCC SHRINKFILE but haven't managed to get it do what I assumed it did.

Any advice on this matter gratefully appreciated.

 

[ptheriault]

How much free space do you have in the database files? you can't shrink the file if it is full. Also, 1MB is to small for your auto grow setting. You may want to change that to 200 MB. You should never use percentage as it will grow by a larger number each time. 10% of 3.5 gb isn't bad but 10% of 350GB could run you out of disk space.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[TheVillageIdiot27]

I have just checked - and there isn't any free space in the database files which at least explains why the shrinkfile bit wasn't working, and have found from our database suppliers that perhaps the database size isn't that unusual.

We will probably take your advice on the 200MB. Given the kind of file sizes we have, what would you suggest as suitable for the log file?

 

[ptheriault]

The log files size depends on how transactional you database is. I can't say how large it should be.
You can leave the growth rate of that to 10%.
Also,
What is the recovery model of the database. If it FULL make sure you are backing up your transaction logs or they will grow till they use up all your disk space.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[TheVillageIdiot27]

It is full but we are backing up the transaction logs. I have made the change to 200MB and am now reading up on SQL server database management.

 

[Crowley16]

Is it necessary to have your training/dev databases contain the same data as your live database?

If this isn't the case, then you can just copy the database structure/objects which will not be anywhere near as big...

--------------------
Procrastinate Now!

 

[TheVillageIdiot27]

That's a fairly good point - we should probably look at doing that. Thanks for the thought.