Balancing Size Of Data Files In Filegroup
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
ADVERTISEMENT
Jun 15, 2006
We are storing all our SQL 2000 databases on SAN LUNs, and one of our databases currently uses a single 40GB file which is approaching capacity. If we add further files using different LUNs, the data will start being added to these new files. My questions are these: if we were to add a number of new LUNs to this database, is there a way to redistribute the existing data so it is balanced across all files in order to gain the most benefit from having multiple files, rather than just dispersing the additional fragments across the new files?
Will the optimise feature of the maintenance plan do this automatically during the index rebuilds?
Is it better to add more files to the PRIMARY filegroup, or add a number of filegroups with single files in each? We aren't looking to use filegroups for fiddling with our backups by the way.
Many thanks for any recommendations offered.
View 2 Replies
View Related
May 7, 2014
A little background on what I am trying to achieve first. We are moving to Azure virtual machines and we will have 8 disks on the SQL Server box. I am adding more files to the primary file group and each file will go on its own drive. I am then rebalancing data across these files by rebuilding all of the indexes on the tables which is working fine. No problems so far all is good.
I now have an additional problem. If there is a lob or blob column on the table, rebuilding the clustered index and all the non clustered indexes doesn't rebalance the blob or lob data across the disks such as it does with in row data.
I cannot find any articles on rebalancing lob or blob data because all the articles say to move to a new file group. I do not want a new file group, I just want to use the primary file group where the data already resides, and just redistribute it evenly in the same way that I can in row data which is working fine.
One solution I thought about was to BCP data out of the table, truncate the table and then BCP back into the table which I imagine would have the desired effect of distributing the data evenly over the files.
View 2 Replies
View Related
Nov 12, 2014
I read , When sql server Database having multiple data files within single filegroup then sql server writes data in multiple proportional file algorithm where the amount of data written to a file is proportionate to the amount of free space in that file, compared to other files in the filegroup.
so if there is no filegroups created and multiple secondary files are attached in databse , is there same way data stored and writes data in multiple files by the same algorithm or any different way.
View 2 Replies
View Related
Sep 22, 2004
Hi!
I'm using SQL Server 2000!
How to know then Data files size and Log files size by Store Procedures or Sql query?
Thanks!
View 1 Replies
View Related
Aug 14, 2006
hi
just wondering if any expert out there can answer my question.
i got a database separate into 3 datafiles in 3 different drives.
i will name it A, B, C.
A datafile size 30G, B datafile size 15G, C datafile size 15G.
and the drive for datafile A is about full. so is there anyway i can more some of data from A datafile to other data file? or since A+B+C =60G can i make it all 20G for each one of them by any command? thanks!!! :)
View 4 Replies
View Related
Jul 23, 2005
Hello,I have been trying to figure out how to temporarily change the maxfilegroup 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 properlyarchive and remove some filegroups.Here is the scenario, my filegroup is set to 20. Each filegroup is setto grow to 4096.I currently have all 20 filegroups in use and filegroup number 20 ismore than 1/2 full.I have considered making the max size for each filegroup larger butbelieve 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 DATABASEIn 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 autoconfigured to grow as needed so this is new territory for me so pleasefeel free to dumb it down for a noob as I need baby steps.
View 2 Replies
View Related
Jul 25, 2001
Problem: I need to build several databases on a quarterly basis. The databases range in size from 30 GB to 250 GB. I want to keep each dtabase file <= 20 GB so my databases contain from 2 to 13 database files (I put these all in one filegroup. the filegroup is separate from the PRIMARY filegroup which contains only the system tables in my databases).
I would like to create the files for the databases asynchronously (I have four physical drive letters on which to create the files and would like to be building one file on each drive simultaneosly). I can acheive the asynchronous operation by creating a separate job for each of the drive letters and then callin sp_start_job for each of the jobs.
The problem is that the ALTER DATABASE command apparently locks the sysfiles table and three of the four processes are always blocked and I therfore end up build the files serially instaed of in parallel.
Is there a way to make these processes work in parallel?
View 1 Replies
View Related
Nov 20, 2007
I'm tryin to copy information from a fact table from one server to another server but when try using a DTS package to do this, an error pops up that says "the Filegroup <filegroup_name> has no files assigned to it."
what am i missing here?
View 4 Replies
View Related
Feb 28, 2005
Hi,
I have two sets of data i.e two instances of SQL server in two different cities. Data entry happens at both the places.I need to balance the data in two servers i.e. I need to Synchronize the data.
Currently I am Exchanging the data between two sides and using a Buffer database to update the incremental infromation.(Running a SQL script).
I have GUID columns in my database. I am too new to replication.I wanted to know whether I can implement repliaction and what changes i need to make?(e.g. Replacing GUID columns with Identity columns).
Permanent connectivity between two databases is required or Replication can be performed whenever connected?
Best Regards
Rohit
View 1 Replies
View Related
Aug 23, 2006
Hi, I have a problem importing data from SQL Server 2000 'text' columns to SQL Server 2005 nvarchar(max) columns. I get the following error when encountering a transfer of any column that matches the above.
The error is copied below,
Any help on this greatly appreciated...
ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unicode data is odd byte size for column 3. Should be even byte size.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)
Many thanks
View 5 Replies
View Related
Jul 5, 2013
My database .mdf file size is around 6 GB however .ldf file size is 200 GB.
Though I don't know exactly what .ldf file is, I believe maybe that is the reason why some of the stored procedures taking long time to run which were running good before.
Can I delete the .ldf file? If not, can I do something to reduce the file size?
The database (tables) are also replicated to another sql server in another computer.
View 7 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
Jul 21, 2004
Hi,
I want to retrive the images from SQL database and store into microsoft access database.
But only images file which is <than 500mb is allowed to retireve.How can i get the images files size if i just store the image file name into my sql database.(there is another folder to store images)
p@ywen.
View 3 Replies
View Related
Jun 25, 1999
I need to know how big SQL Server 7 backup files are in comparison to the database size. For example if I have a database that is 300 Mb and I do a complete backup to disk with SQL Server 7 will the backup file be about 300 Mb?
Thanks,
Mike
View 2 Replies
View Related
Aug 11, 1999
I have a 24gig DB that has 1 data file.
Is it possible to RESTORE the backup to a DB that has 2 files
(1 = 10gig 2 = 14gig).
Does one of the files on the the DB have to be at least 24gig to RESTORE?
View 1 Replies
View Related
Dec 26, 2006
Hi All,
I need to create a brand new db in prod and I was wondering if anybody has any recomendations for initial .mdf, .ndf, and .ldf file sizes.
Thanks.
View 2 Replies
View Related
Jul 23, 2005
Hi All,I want to shrink a transaction log using :USE MasterBACKUP Log test WITH truncate_onlyUSE testDBCC shrinkfile(test_log, 0)These commands will be executed from a workstation.After shrinking, I want to see the size of thetransaction log and database files. But, I do notknow what command should I use.Could anyone help me ?Thanks in advanceJohn S.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Oct 7, 2015
I have partitions that I have filled with data. I am not trying to figure out exactly how much data the partitions contain, and therefore I will be able to see if any of them are close to hitting their autogrow conditions. If I were looking at a single unpartitioned table, then I could maybe look at the table properties to determine data and index sizes, and compare that to the size of the mdf file size, but for partitions, then I am not sure how I would query this information out. Any pointers on how this information could be queried out of the system?
View 3 Replies
View Related
Jan 28, 2008
Hi all
I've found this problem working with a VLDB, six month ago when I install the DBMS (Win2k3 x64+sp2, SQL 2k5 x64 +sp2, 4 dual core processor and 12 GbRAM) I've got 10 disk (actually ten LUN from a Storage Area Network), each 50Gb.
I've put TempDB and Transaction Log on two separate 50 Gb disk and put the database on 8 different data file on the 8 disk; I've created each datafile with a size of 50 Gb (autogrowth disable), so my DB has 400Gb space in it's datafile.
After a while the datafile began to fill and we decide to add a couple more 50Gb disk where I decide to put to new datafile; now my db is around 430 Gb and I've got this strange situation:
The first 8 datafile now are almost full of data, and obviously they can't growth since they already occupy the whole disk.
The two additional datafile are relatively empty (about 15 Gb each).
As far as I understand now each time that SQL should write to the databse it writes only on the 2 new datafile, and I fear that this can affect performance.
I'd like to reorganize the space in order to have 10 datafile, each with 43Gb of data, but I didn't find any instruction/tool able to move data between datafile.
Anyone has any hint ?
Thank you in advance for any suggestion
Stefano
View 5 Replies
View Related
May 13, 2008
OK, I know this is out there all over and yes I did a search for this topic; however, I am confused about tables with an image data type and with moving text file group to another filegroup.
Here is what I have:
I have a table storing imaged documents and has become very large. I want to move the table to another filegroup. The table is created like this:
USE [PD51_Data]
GO
/****** Object: Table [dbo].[SCANNEDDOCUMENTS] Script Date: 05/13/2008 14:52:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SCANNEDDOCUMENTS](
[DocID] [int] IDENTITY(1,1) NOT NULL,
[CaseID] [int] NOT NULL,
[DocName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Doc] [image] NOT NULL,
[DocLocation] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TopicID] [int] NULL,
[ScannedDocumentsCheckSum] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,PRIMARY KEY CLUSTERED
(
[DocID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SCANNEDDOCUMENTS] WITH NOCHECK ADD CONSTRAINT [ISCANNEDDOCUMENTS2] FOREIGN KEY([TopicID])
REFERENCES [dbo].[TOPICS] ([TopicID])
GO
ALTER TABLE [dbo].[SCANNEDDOCUMENTS] CHECK CONSTRAINT [ISCANNEDDOCUMENTS2]
On a test DB, I moved the clustered and nonclustered indexes to a secondary filegroup no problem, but it still shows to be stored in the primary filegroup. I read an article about having to create a new table in the secondary in order to move the images and text file group. Has anyone come across this?
Do I need to drop the clustered index and FK to move to a secondary filegroup?
Or
Do I create a new table into the secondary filegroup and then add the Clustered index and constraints?
View 4 Replies
View Related
May 14, 2007
Hi,
I am getting below error while importing data in SQL 2005 Express:
"error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Could not allocate space for object 'dbo.HistoryLog'.'PK_HistoryLog' in database 'HistoryData' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.".
"
I have select :
Enable Autogrownth = Yes
Filegrowth = 1 MB
Maximum File Size = Unrestricted File growth
I don't know what else I am missing?
Please help
thanks
AA
View 8 Replies
View Related
Apr 20, 2006
I am wondering why deployed packages are so much smaller than built packages. What is removed and why isn't it removed when the packages are built?
I have a package that goes from 3Mb to 1.5Mb
View 5 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
Oct 28, 2014
I would like to add to it the actual file size in mb or gb of each file to the results.
select sd.name,mf.name as logical_name,mf.physical_name,
case
when dm.mirroring_state is null then 'No'
else 'Yes'
end as Mirrored
from sys.sysdatabases sd JOIN
sys.master_files mf on sd.dbid = mf.database_id
join sys.database_mirroring dm on sd.dbid = dm.database_id
The sp_spaceused procedure does a nice job on it's own giving me what I want (only one db though), plus a bonus allocated space column. How can I combine this sp with my other query, or is there a better way to ad this information?
View 2 Replies
View Related
Mar 27, 2014
I am on SQL Server 2008 R2. I have a table that contains a field called [Location]. In that field is a UNC path to the physical file on the repository. Is there a way in SQL Server that I can say give me the select sum([Location] UNC file) where criteria? I saw some posts about xp_filesize or xp_GetFileDetails, but I do not see them in master. I am unable to add anything and wondering if there is any native functionality that would allow me to accomplish this!?
View 4 Replies
View Related
Feb 7, 2000
I have a client with no backup of MASTERdb or Help_rev_devices, all I have is a couple of .dmp's of the production data. In order to run DISK REINIT I need to know the size of the database, is there any way of finding out the size of the database device, if all you have to go with is a backup dump file?
I know it won't be pretty.
`Ken
View 1 Replies
View Related
Feb 18, 2015
I've got a partitioned table where I am trying to switch the first partition into a staging table, merge the first boundary and later drop the file and the file group.
This is my starting point:
boundary_idBoundary_valuePartitionNumber
11/09/2012 0:002
21/10/2012 0:003
PartitionNumberPartitionRowsFileGroupName
1799AdtLog_Archive_201208
2300AdtLog_Archive_201209
After I switch partition 1 to a staging table I run:
ALTER PARTITION SCHEME My_ps NEXT USED AdtLog_Archive_201209;
and
ALTER PARTITION FUNCTION My_pf() MERGE RANGE ('2012-09-01 00:00:00.000');
I expect the 300 records from the former 2nd partition to stay in AdtLog_Archive_201209, however I get this:
PartitionNumberPartitionRowsFileGroupName
1300AdtLog_Archive_201208
2310AdtLog_Archive_201210
How do I make sure that the data stays in AdtLog_Archive_201209 file group?
View 1 Replies
View Related
Oct 14, 2015
Any good starting point to understand for a specific db, how many max VLFs are good to have so that it does not cause long startup or backup times?
Also, I need some calculation so that I can identify a best growth parameter I will setup for each database ?
I'm seeing the below msg in errorlog and curious to know the changes (right sizing/growth) to be done? As of now 100 MB of log file growth value is set (refer: [URL] ....)
Database BizTalkMsgBoxDb has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.
View 3 Replies
View Related
Nov 19, 2006
Hi,I am new to SQL express and try to solve the 4GB size limitation.Is there a possibility to create a new database file every time I getto the limit?How can I do that with C#? how can I create new database file everytime it gets full?Can I be connected to two database files at the same time (the full dband the new db)?thanks in advance,oren
View 1 Replies
View Related
Aug 18, 2015
I am trying to trace few events using profiler Via T SQL like below, the below scripts works fine, but once the file reaches 1GB , it is not rolling over to another file and start trace again.I tried using URL...once it reaches max file size provided between the time interval..
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
set @DateTime = '2015-08-18 16:00:00.000'------Stop Time 4 PM
set @maxfilesize = 1024 --1GB max File size
[code]...
View 2 Replies
View Related
Oct 15, 2015
I need to modify a table to reside on a new filegroup and also point TEXTIMAGE_ON to that filegroup instead of PRIMARY. Apparently in the past, the only way to achieve this via SQL is to create a new table, copy over data, drop the old table and rename the new table to the original name. I found this solution in the SQL Server 2005 forum.
Is there any other way to alter this table in order to point the TEXTIMAGE_ON to new filegroup using SQL Server 2014? We are on Standard edition. The technique I am using is the drop constraint (with move option) and add constraint (to new filegroup) commands. The data and indexes move, but not the text data (it still is in primary filegroup).
View 0 Replies
View Related
Jan 9, 2015
I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies
View Related
