Max Filegroup Size Set To 20 How Do I Change It?
Jul 23, 2005
Hello,
I have been trying to figure out how to temporarily change the max
filegroup size of a SQL 2000 database I "inherited" when our DBA quit.
I just need some breathing room until I can figure out how to properly
archive and remove some filegroups.
Here is the scenario, my filegroup is set to 20. Each filegroup is set
to grow to 4096.
I currently have all 20 filegroups in use and filegroup number 20 is
more than 1/2 full.
I have considered making the max size for each filegroup larger but
believe this is treating the symptom not fixing the problem.
I would RTFM if I knew how to ask the question in 5 words or less.
I have plenty of room to grow in terms of disk space.
In Query Analyzer I suspect I have to use an ALTER DATABASE
In Gui mode I suspect I have to right click on the database properties,
filegoups,,,,
In the past I have setup my databases with 1 filegroup and auto
configured to grow as needed so this is new territory for me so please
feel free to dumb it down for a noob as I need baby steps.
View 2 Replies
ADVERTISEMENT
Apr 26, 2008
Due to SQL's proportional fill algorithm I'd like to have the amount of data in my data files as close to equal as possible.
File sizes before:
dat 482,895 MB 0% free
2dat 436,927 MB 3% free
I made the max size of each file 600,000 MB and added a third file 3dat also 600,000 MB. I rebuilt all the clustered indexes (and nonclustered for good measure) and unfortunately the re-balancing wasn't quite right.
File sizes after:
dat 464,761 MB 77% free
2dat 443,234 MB 73% free
3dat 309,568 MB 51% free
I only have a handful of heap tables that take up <100MB total so they're not the issue. I did do an ONLINE index rebuild. I'm not sure if an offline rebuild would have been better. I will not be able to try and offline for a few weeks though as it's time consuming and I have other tasks I need to run on this test server now.
I did a FULLSCAN rebuild on any column statistics not updated by the index rebuild but that didn't help either.
View 3 Replies
View Related
Dec 5, 2014
I create a db by following code. Now I want to change name of FILEGROUP. How can I do it?
CREATE DATABASE MyDb
ON
PRIMARY ( NAME = MyDat,
FILENAME = 'c:datamydat.mdf'),
FILEGROUP MyGroup CONTAINS FILESTREAM( NAME = My,
FILENAME = 'c:datamy1')
[Code] .....
View 1 Replies
View Related
Dec 5, 2014
I create a db by following code. now i want to change name of FILEGROUP. how can i do it?
CREATE DATABASE MyDb
ON
PRIMARY ( NAME = MyDat,
FILENAME = 'c:datamydat.mdf'),
FILEGROUP MyGroup CONTAINS FILESTREAM( NAME = My,
[Code] .....
View 1 Replies
View Related
May 10, 2007
Hi There
I am running SQL Server 2005 Enterprise Edition, i want to split my data and indexes on different drives.
In 2000 i had to recreate clustered indexes and non clustered indexes on the correct filegroups to accomplish this.
In 2005 i see there is a ALTER TABLE MOVE TO Filegroup option, thats cool.
Does this effectively do the same as rebuilding the clustered index on the new filegroup? Will this leave the other indexes of the table on the primay filegroup or move them as well ?
If i wanted to also move the non clustered indexes is there a better way to move them that drop and re-create on the new filegroup in 2005, i see the ALTER INDEX statement does not support a move to filegroup option.
In a nutshell what is the best/easiest way to move exisitng table data and indexes to new file groups in Sql Server 2005 Enterprise Edition?
Thanx
View 10 Replies
View Related
May 21, 2008
I've create a partition function and a partion scheme for my database.
Now I would like to change an existing table to use these partition.
The table is replicated.
How can I do this?
Thanks
Markus
View 2 Replies
View Related
May 21, 2015
I installed sql server 2012 on windows2012R2 when I creat a new filegroup and try to change like a default give me this message error:
cannot change the properties of empty filegroup the filegroup must contain at least one file (Microsoft sql server, error:5050)
View 3 Replies
View Related
Jul 1, 2006
Hi
I am using one Publisher with many subscribers for Merge replication of a database.
I wanted to change size one column in a table. So I added a dummy column (through Filter column).
Copied data, dropped old column (through Filter column), added new column, copied data, and dropped dummy column.
On subscriber A, replication is OK. On subscriber B, I get the following message.
--------------------
Initializing
Connecting to Publisher 'REPLIC'
Retrieving publication information
Retrieving subscription information
A column was added to or dropped from the replicated table.
The schema script 'exec sp_repladdcolumn '[dbo].[Items]','C1',[NVARCHAR(1000) NULL], '%', 1' could not be propagated to the subscriber.
The schema script 'exec sp_repladdcolumn '[dbo].[Items]','C1',[NVARCHAR(1000) NULL], '%', 1' could not be propagated to the subscriber.
A column was added to or dropped from the replicated table.
--------------------
I made a new snapshot, reintialized subscription with upload, and tried to sychronize again.
But same message.
Question 1. Is there anyway to make replication?
Question 2. If not, can I at least upload data changes before dropping and making new publication?
Imtaar
View 1 Replies
View Related
May 3, 2007
Hi, all experts here,
Thanks a lot for your kind attention.
I am on the layout page, and click on the body, going to the body property dialogue, there is a size property there, I changed its width and height, but it is not changed at all? I mean once I save it, it is back to its orginal size? Why is that? Any advices for that?
Hope my question is clear.
Thanks a lot in advance for your help.
And I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 1 Replies
View Related
Apr 11, 2007
Hi all,
I tried to change the size of a field of my table using the query :-
ALTER TABLE test MODIFY id varchar(50)
Initially the size of id was set to 30 .Is there any other way or any error in my query.Please help me soon.
joshymraj
View 2 Replies
View Related
Jul 20, 2005
Howdy!Does any of you good folk know how to change window size of the DTSImport/Export Wizard window?Darn thing is very small and inconvinient to work with, no apparent way tochange it's size,may be there is a registry tweak or something.Thanks in advance,I.B.
View 1 Replies
View Related
Feb 17, 2008
We have 300+ databases on one sinlge server. If I need to change log size to "unlimited" for all of them, is there any way to do so? Please advice.
-Julie
View 9 Replies
View Related
Oct 23, 2015
I have a database I need to copy from a Prod server to a Dev server. There is not enough space on the Dev server. In looking at the size of the files on the Prod server, the Initial Size property for the transaction log on the Prod server is set to 100,000 MB though the log is using nowhere near that.
This is a mirrored database so the recovery model is "full". I know that to change the initial log size, I have to put the database in 'simple" recovery model. Is this possible? Can I just:
1. Pause the mirror
2. Switch recovery model to simple
3. Change the initial size property to something smaller.
4. Shrink the transaction log
5. Change the recovery model back to full and resume the mirror?
I honestly don't know if the transaction log is needed on the Dev server. Meaning I may just be able to restore the transaction log to a different location on the server and delete it so that new one is created.
View 2 Replies
View Related
Sep 4, 2006
I have H Drive with 12 GB size.
The Transaction log file of my Database is given in H drive.
i.e H:Krishna_log.LDF.
My database initial setting for the Krishna_log.LDF is 10 GB.
so currently I can see 10 gb file occupied in the H Drive.
very soon my database Krishna_log.LDF is going to increase 10%.
Even though If do Backup/Shrink, it will reduce to the initial to 10 GB.
So all I need is, change the initial size of the
Krishna_log.LDF from 10 GB to 5 GB.
Pls give your suggetions...(I dont mind to shutdown,Restart the Sql server do these changes to achive this Task).
Thanx in advance.
Regards,
Krishna
View 5 Replies
View Related
Feb 24, 2007
I have a report that has a matrix. That matrix can have from 2 to 16 columns dependinging on the dataset result. Right now I am forced to place this matrix on the left side of the report and make a column layout pretty narrow. When dataset has more than 13 or so columns it looks OK, but when dataset has only two or three columns it looks weird with a matrix sitting in the left corner with two or three narrow columns and a lot of empty space to the right.
Is it possible programmatically change the width of the columns depending on their number in the dataset?
Is it possible to move the location of the matrix (horizontally) depending on the number of columns in the dataset?
Thank you,
Simon.
View 11 Replies
View Related
Jan 3, 2008
I have a 14GB database whose data content is legacy and is described as static. The log file is significantly large and continues to change size mostly increasing by 2-5GB a day (~60GB now) I have observed over the past two days; it shrank once unexpectly by a few GB. The instance is hosting other databases such as: EnterpriseVaultDirectory, EnterpriseVaultMonitoring, EnterpriseVaultStore, and NetPerfMon - might these seemingly unrelated data sources be involved?
I am trying to a trace to find traffic against the tables, no such luck.
Web applications are playing against it for queries but there should be no UPDATEs beign applied. I can only suspect that other unknown applications are performing operations but have yet to find unexplained connections.
Are there any other reasons why this type of log file activity would happen merely due to queries or stored procedure calls?
Lets also state, "mirroring, indexing, replication" are not at play. I know logging "Full" is not necessary as "Simple" should suffice but I am still hunting down why UPDATEs might be getting through. I realize I might adjust the migrated SQL 2000 security model to deny updates to find what breaks but would rather not take that iniative yet.
The installation is a fresh SQL 2005 Standard setup with SP2 applied; the databases were upgraded.
View 6 Replies
View Related
Sep 4, 2007
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
View 1 Replies
View Related
Jun 4, 2015
How to change column size dynamically depends on content of the column.
View 2 Replies
View Related
Jun 15, 2006
I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message
native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.
I'll try to post the full log in a new post.
View 11 Replies
View Related
Mar 16, 2007
Hi folks,Can anyone enlighten me here? I'm trying to use a SPROC which, when supplied with an int, looks up the table and returns certain columns from it. I'm using a SqlCommand, here's my codebehind: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SqlCommand dataSource = new SqlCommand("retrieveData", new SqlConnection(dbConnString)); dataSource .CommandType = CommandType.StoredProcedure; dataSource .Parameters.AddWithValue("id", poid); dataSource .Parameters.AddWithValue("title", title).Direction = ParameterDirection.Output; dataSource .Parameters.AddWithValue("creator", creator).Direction = ParameterDirection.Output; dataSource .Parameters.AddWithValue("assignee", assignee).Direction = ParameterDirection.Output; etc, etc... And the SPROC:------------------------------------------------------------------------------------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[retrieveData] @id int, @title varchar(50) OUTPUT, @creator varchar(50) OUTPUT, @assignee varchar(50) OUTPUT, @contact varchar(50) OUTPUT, @deliveryCost numeric(18,2) OUTPUT, @totalCost numeric(18,2) OUTPUT, @status tinyint OUTPUT, @project smallint OUTPUT, @supplier smallint OUTPUT, @creationDateTime datetime OUTPUT, @amendedDateTime datetime OUTPUT, @locked bit OUTPUT AS /**SET NOCOUNT ON; **/ SELECT [title] AS [@title], [datetime] AS [@creationDateTime], [creator] AS [@creator], [assignee] as [@assignee], [supplier] as [@supplier], [contact] AS [@contact], [delivery_cost] AS [@deliveryCost], [total_cost] AS [@totalCost], [amended_timestamp] AS [@amendedDateTime], [locked] AS [@locked] FROM purchase_orders WHERE [id] = @id; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ The id being passed in is definately not null, and is set to a value of an item I know exists. The resulting error is:
Exception Details: System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.Line 63: retrievePODetails.Connection.Open();Line 64: retrievePODetails.ExecuteNonQuery();[InvalidOperationException: String[1]: the Size property has an invalid size of 0.] System.Data.SqlClient.SqlParameter.Validate(Int32 index) +717091... ... Can anyone see anything I'm missing? Thanks,Ally
View 1 Replies
View Related
Nov 14, 2007
Using C#, SQL Server 2005, ASP.NET 2, in a web app, I've tried removing the size from parameters of type NCHAR, NVARCHAR, and VARCHAR. I'd rather just send a string and let the size of the parameter in the SP truncate any extra chars if need be. I began getting the error below, and eventually realized it happened only with output parameters, as in the code snippet below.String[3]: the Size property has an invalid size of 0. par = new SqlParameter("@BusinessEntity", SqlDbType.NVarChar); par.Direction = ParameterDirection.Output; cmd.Parameters.Add(par); cmd.ExecuteNonQuery();What's the logic behind this? Is there any way around it other than either finding out what the size should be, or assigning a size larger than would ever be needed? ThanksMike Thomas
View 6 Replies
View Related
Jul 25, 2007
I have one db test with one .mdf and .ldf file...mdf file size is 100mb and for some reson i removed all the tablesfrom that .mdf file and transfer it into new secondary file so all thetables moved into secondary file now i want to reduce the first .mdffile from 100 mb to 50mb is that possible,it's showing 90mb is free.Please reply
View 1 Replies
View Related
Jan 24, 2006
I want to know encrypted data's size for designing database field size.
For example, cardnumber varchar(20) Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.
I think the size does not depend to PassPhrase char length.
Regards,
Yoshihiro Kawabata
View 3 Replies
View Related
Dec 11, 2007
I am getting error to run stored procedure using executenonquery method. The Stored Procedure is having OUTPUT parameter.
ExecuteNonQuery statement is called using SqlHelper.
Error : String[18]: the Size property has an invalid size of 0
View 1 Replies
View Related
Jul 23, 2005
Just wanted to know what is a general rule of thumb when determining log file space against a database's data file.We allow our data file for our database to grow 10%, unlimited. We do not allow our log file to autogrow due to a specific and poorly written process (which we are in a three month process of remove) that can balloon the log file size.Should it be 10% of the Data file, i.e. if the Date file size is 800MB the log file should be 8MB?I realize there are a myraid of factors that go against file size but a general starting point would be nice.ThanksJeff--Message posted via http://www.sqlmonster.com
View 4 Replies
View Related
Apr 20, 2015
The Tabular model is showing 19 GB on disk, but it is acquiring around 40 GB in memory.
View 3 Replies
View Related
May 12, 2015
Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data
View 1 Replies
View Related
Feb 22, 2001
hello,
ihave atablelike this -
z uniqueidentifier
a varchar(40)
b varchar(40)
c varchar(40)
d varchar(40)
e varchar(40)
f text
g image
I am asked to split this table into 2 filegroups and move text and images on to one and other stuff on one.
I am new to MS-SQL 7.0.
Can anyone suggest me how to go for it.
Thanking you.
View 2 Replies
View Related
Jun 5, 2006
i understand that the filegroup for a table can be specified in the CREATE Table statement. just out of curiosity, is there any way to move a table to a different filegroup, that too, if the table contains data.
View 2 Replies
View Related
Aug 14, 2006
Hi,
There is a table which is about 50GB is size.
I am thinking of placing the database of this table into a separate .ndf on a separate drive on the server.
Is it possible to place this particular table into a searate drive to increase the performance even more?
Basically my question is: is it possible to place tables into separate .ndf files?
Thanks
View 4 Replies
View Related
Aug 6, 2007
Hi,
I have created a database with two filegroups called FG_GroupData, FG_GroupHistory.
FG_GroupData is set as default.
FG_GroupData contains two secondary data files i.e. GroupData1.ndf and GroupData2.ndf
I can create a table so that it is stored in FG_GroupHistory. i.e.
CREATE TABLE dbo.OrdersHistory
(
OrderID int NOT NULL,
ProductID int NOT NULL,
CustomerID int NOT NULL,
UnitPrice money NOT NULL,
OrderQty smallint NOT NULL
)
ON FG_GroupHistory
Questions:
1)
How do I add tables to each .ndf file inside a group i.e. FG_GroupData
For example, how do I add a table to GroupData1.ndf and one to GroupData2.ndf ?
2)
I guess there is no need to place the file name i.e. .ndf inside the select query
Thanks
View 3 Replies
View Related
Aug 14, 2007
What is the advantage of having two secondary files inside a filegroup.
For example, I can create a table and place it inside a filegroup which obviously will use the secondary files.
Not sure why some DBAs create just one but otherscreate more than one secondar files in a filegroup.
I have read the booksonline but can not figure out the advantages.
Thanks
View 1 Replies
View Related
