How to change the datafile and log file size of an existing database

Question

How to change the datafile and log file size of an existing database

vamshikrishnaeee

SSCrazy

Points: 2519
More actions
November 26, 2010 at 12:50 am

#142756

HI,
An involuntary DBA has created a database on production with default settings like
datafile size----7 MB with an autogrowth of 1 MB
logfile size------6 MB with an autogrowth of 10%
Because of this the files are fragemented. Now the database size is 12 Gb with datafile 4gb and logfile 8 gb
There are nearly 1300 virtual log files for this database now.
Now I want to change these settings and preallocate sizes for datafiles and log files.
For this some one suggested me to create an empty database and allocate estimate sizes to that database and then restore the backup of the original database on this new database so that the datafiles and logfiles are not fragmented now ..But I am unable to restore the original database on the newly created database its throwing me error like backup set holds the backup of other database.
Please help me on this..
Thanks,
Vamsy

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

1) backup set holds the backup of other database.

this error is because of version confliction or product confliction. What I mean is, if backup was taken in 2008 ent. edition then you cnat restore it on professional edition OR backup is taken via tools like litespeed and you restoring it using t-sql or SSMS.

2) Is your data/log file not configured to auto grow?

----------
Ashish

vamshikrishnaeee SSCrazy Points: 2519 More actions · Answer 2

@@version--Its the same version(2008)

@@autogrow-- Now the database has autogrowth enabled but the initial size was to small sizes due to which it goes on autogrowing which leads to fragmentation.. To prevent this I want to change the initial database file sizes to some estimate size proactively and will enable auto growth for unexpected increase in the sizes of the files.

Thanks

crazy4sql SSCoach Points: 19590 More actions · Answer 3

have you checked the edition? I was referring to same edition. You will get such error in backup restore even though you have same version but idfferent edition

----------
Ashish

vamshikrishnaeee SSCrazy Points: 2519 More actions · Answer 4

Its not the problem with the edition or version because I am backing up and restoring the database in to the same server.

crazy4sql SSCoach Points: 19590 More actions · Answer 5

ok, so you saying that you restoring the backup on blank database and you not able to restore it?

Instead of restoring on newly created database, why not restore it with new name?

----------
Ashish

Adiga One Orange Chip Points: 27284 More actions · Answer 6

@vamshikrishnaeee,

To reduce the number of VLFs the easy approach is to

1) Take the transaction log backup

2) Shrink the transaction log to the minimum size possible

3) Increase the transaction log file size to the desired new size.

Please read this good article on VLFs in SSC.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

vamshikrishnaeee SSCrazy Points: 2519 More actions · Answer 7

Thanks for your reply pradeep.

It will work for t-log but how to change the size of the datafile which was previously set to default value. I want to set to some estimated size for the datafile.

Please let me know whether the restore what I am talking about in my earlier post will work or not?

Adiga One Orange Chip Points: 27284 More actions · Answer 8

Regarding the restore error, please run RESTORE HEADERONLY FROM DISK='YourFileName'. This would give you the database name to which the backup file belongs to.

But I feel it would be better to use ALTER DATABASE MODIFY FILE and set a "good SIZE" for the data file. Also you can modify the Auto Growth to a reasonable value. For me restoring the database to modify the data file setting is too much of overhead.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

vamshikrishnaeee SSCrazy Points: 2519 More actions · Answer 9

And also I want that ,eventhough anyone shrink the log file and the datafile it don't reduce its size because of this I am thinking of the resotre.

Thanks

Adiga One Orange Chip Points: 27284 More actions · Answer 10

vamshikrishnaeee (11/26/2010)
And also I want that ,eventhough anyone shrink the log file and the datafile it don't reduce its size because of this I am thinking of the resotre.

If DBCC SHRINKFILE does not reduce the Transaction Log file size, there must be an open transaction on the transaction log. Once that transaction completes, transaction log would be shrunk and you will get free space released to the OS.

Shrinking the data file is not a good practice. However, for one time if you want to reclaim space you can go ahead and SHRINK it. Note that when you rebuild the indexes, the size of the data file would again increase.

It is my opinion to favor ALTER DATABASE over RESTORE in this scenario. If you want to RESTORE database anyways, I have already mentioned how to resolve the error that you are getting while trying to RESTORE the database.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga