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!!! :)
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!
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.
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
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.
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
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.
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
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?
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.
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 RelatedI 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
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 RelatedI 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?
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 RelatedI 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
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.
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 RelatedI 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]...
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
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?
Hi!
I'm using replication with two database on SQL 2000,when begin, the log files size is 50mb and the data files size is 150mb. But now the log files size is 2Gb and the data files size is 4Gb. I would like to decrease the log files and the data files ??? How do i do this???
(I using Truncate and shrink doesn't change )
Thanks!!!
I have a report where in I have a combination of matrix ,table data regions.
The problem what I am facing is that the data tables don't remain fixed in their position and they tend to move down.
E.g. table 1 and table 2Â are on the same page in design time side by side (right and left)however during the runtime the table1 is pushed down and table2 is at its position .
Now how can I keep them all fixed in their same position. Most of the tables have fixed size rows and some who have high size of rows have been put at the end . What settings we can set?
Hello all. Before my arrival at my current employer, our consultantsphysically set up our MSSQL 7 server as follows:drive c: contains the mssql enginedrive d: contains the transaction logdrive e: contains the data filesNo filegroups were set up and the data files consist of only 1 largephysical file. Currently, our data file is >10GB. When I was trained onthe physical aspects of sqlserver, I was told to never create physical files[color=blue]> 2048MB each. If I did, I could expect inefficient physical storage of[/color]data and slower performance (due to the OS).Our server has 2 RAID-5 arrays. Drive c: and e: are located on the firstarray and drive d: on the second. We're running Windows 4.0 NT Server SP6with NTFS.Can someone comment on the use of 1 single large data file vs. more smallerdata files?
View 2 Replies View RelatedI have an MVC asp.net application that stores many records in a table on sql server, in its own system. Â used the system for 2 months, worked fine accessing, changing data.
Now that other users are logging in? there is cross coupling going on. Â one user gets the data from another users sql search.
In the mvc app it had used the get async method to read the ID record from the db, i set that to synchronous. Â no effect; Â the user makes their own login id but that does nt matter either.
hi
in sql server i set the datastype en size of the column messagetext to nvarchar and max
but how do i set max in the code below cause i only can set an int in the place where the datasize has to be
objCmd.Parameters.Add("@MessageText", SqlDbType.NVarChar, 50).Value = txtbericht.Text
tnx
i am really in need of help. i have a text file consiting of some data.i want to update my database from that text file periodically say 12 hours.the text file is being updated by another server program in every 12 hours can any one help me in this case? i am lost for this scenario?? help me please.....
View 1 Replies View RelatedHi,I have(had) an old Win2k Server server with about 30 web site databases(SQL 2000) that just went under due to hardware problems. Thankfully, Ihave backups of all the databases plus the MDF and LDF files from thehard drive.I want to move all of these sites and their data to a newer server(Win2003) running SQL2000.What's the best way to copy the database from the old server hard drive(now mounted as an extrnal drive to a local machine; I'm currentlyFTPing all of the web site directories from it to the new server)?Just upload the original data to the new server and then mount the MDFand LDF files within the new SQL server? Or do I restore the backupfiles in the new SQL2000?All of my previous data migrations have been DTS operations from onelive server to another, so no experience with either of the abovescenarios. I'll certainly have a lot more experience at one of them bythe time this weekend is through.Thanks for any help you can offer.
View 1 Replies View RelatedOne of our databases has the following sizes:
Data used : 4017.14MB (191.29%)
Indexes 35.48 mb (1.69%)
Unused : 5.27 MB (0.25 %)
Unreserved: -1957.89 mb (- 93.23 %)
Total Size 2100MB
looking at this the data size is 2100Mb and the data used is more at 4014.14MB.
How can I sort this out, do I simply need to create another device and expand the database or do I need to do something else?
Cheers
So which is the biggest database you have worked with ?
View 8 Replies View RelatedSELECT size_in_mb,used_size_in_mb,size_in_mb-used_size_in_mb as free_in_mb FROM (
SELECT cntr_value/1024 size_in_mb ,
(SELECT cntr_value/1024 FROM master..sysperfinfo WHERE counter_name='Log File(s) Used Size (KB)' AND instance_name='mydb') used_size_in_mb
FROM master..sysperfinfO WHERE counter_name='Log File(s) Size (KB)' AND INSTANCE_NAME='mydb'
) a
I need to store totalsize,usedsize,freesize of the datafiles in a table to get an average of how much my datafile has increased over a week.
The above query i am using is for logfile size. Can any one help me with datafile size plz.
I've checked sp_helpfile, sysfiles but couldn't find what i am lookin for(used and free space). EM in taskpad view for a database shows the statistics for the datafile. I've tried a trace to find out a stored procedure but couldn't!!!
May be i am unaware of a simple stored-procedure that can do this for me.
Howdy!
How can I find out the original size of the Data and Log files?TIARob
View 2 Replies View Relatedhi,
can anybody advice me how to find the size of the data replicated every minute. is there and procedure for getting the size of replicated data.
Thanks in advance
Jacx