February 7, 2011 at 1:42 am
I have a mdf file size of 2GB limits which has already reach to its peak.Any tools or methods which I can increase the size limits to 4GB size to facilitate my ever increasing size?
Any inputs are welcome to help my request...:w00t:
February 7, 2011 at 1:52 am
Yes, u can do it. When u right click on database and select Properties , then select Filegroup,it will show you the mdf and ldf file size in MB.
Consider ur mdf file size is of 2 GB then the figure out there in the size text box will be 2048 MB. Now when u increase it to 4 GB ie u want to add extra 2 GB then the figure should be 4096 MB instead of 2048 MB.
Please do it when the hits on the database is less, i would recommend normally during non peak hrs.
Cheers,
Satnam
February 7, 2011 at 1:54 am
you should have configured your databafile with autogrowth setting.
Go to your database properties and change the files setting growth with Autogrowth in percentage or FixedSize(in MB).
As well dont forget to select unrestricted file growth.
----------
Ashish
February 7, 2011 at 2:00 am
Thanks Satnam, may you advice me further on this properties and filegroup that will help me to do it?Sorry I'm not expert in database or SQL conversion stuff..
1. Do I need to go to ODBC program or something like that? I have checked that the mdf and ldf files are existed in this application.
2. Do I need any third party software to support this increase of size limits?:hehe:
February 7, 2011 at 2:09 am
Normally, u can set the Autogrowth feature as ON as suggested by Ashish, but in my past experience I have noticed that when the Autogrowth feature is set ON it has some impact on the performance of the database when it tries to increase the size of the data file, so i wrote a script which alerted me as soon as the free space available for the mdf file of the particular database was below the threshold limit of 5 GB.
In ur case what u can do is Right Click on database, Select Properties and in the left pane of the window u will have an option named Filegroup, as soon as u click on Filegroup u will be able to view 2 files one will be data and the other will be log, u will clearly be able to differentiate. For the data file just change the numeric values in the size box from 2048 MB to 4096 MB.
I hope this helps.
Cheers,
Satnam
February 7, 2011 at 8:59 am
any reason why you can't create additional files and put data there as well?
February 7, 2011 at 9:29 am
You can just set a new size for the .mdf files with ALTER Database
USE master;
GO
ALTER DATABASE AdventureWorks2008R2
MODIFY FILE
(NAME = test1dat3,
SIZE = 20MB);
GO
Or you can use SSMS. Right click the database, select Properties, then on the Files tab, just type a new size.
You can add a new filegroup or file if needed to get data on another drive, but it's not necessarily that simple as tables and other objects typically can't span filegroups. So you would need to better understand how that works, and what your situation is, before I would recommend that.
Autogrow is a good idea, but for emergencies. This can fragment your files and slow down the system when things grow. You would be better off watching space every month, and increasing it in discrete amounts when you need to.
February 7, 2011 at 5:03 pm
You posted this in the SQL Server 2005 forum, so I am going to assume you are running SQL Server 2005 Express Edition.
Please verify the edition of SQL Server you are using. If you have reached the maximum size allowed for the database, and you are using an Express Edition - the only option you have is to upgrade.
You can either upgrade to the Standard Edition, or you can upgrade to an express version that allows for larger database sizes.
Since you stated the limitation that you have reached is 2GB, I am assuming that you are really using MSDE and not SQL Server 2005 Express. SQL Server 2005 Express has a limitation of 4GB, and 2008 Express increases that to 10GB.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply