One 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
idp data: 55% full (Total: 4600 MB Used: 2515 MB Free: 2085 MB)
idp log: 88% full (Total: 3500 MB Used: 3073 MB Free: 427 MB)
Normally the "data" is bigger than the "log" would you say this is a problem and how can one reverse the problem?
Microsoft OLE DB Provider for ODBC Drivers error ' 80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Updated or inserted row is bigger than maximum size (1962 bytes) allowed for this table.
database:microsoft 6.5 SQL
How can I solve this problem
thanks,shay
I'm importing about 15 million rows of data from an access file to an MSSQL database. Some of the fields in the Access file are of DataType "text". The destination fields in the SQL DB are of type varchar(50), and none of the text fields in the access file actually use anything other then English characters. I put in a "data conversion" item to handle the switch from "text" (which usually trys conversion to nvarchar by default) to varchar.
The import works, and the resulting table ends up weighing about 1.2 gigs. HOWEVER, the log itself is a crazy 7-8 gigs heavy. I have no idea why the log size bloats this much. I can backup/shrink later in the package, but this 8 gig could easily push the hard drive over its limit at some point before completion and I'm looking for a better alternative.
Database is on "simple" recovery mode. The combined size of the db, before the operation, log + data, is maybe around 5 meg.
Incidentally, I tried with out the intermediate data conversion step, and a similar thing happened - log finishes up about 7 gig, table is 1.2
Seems ridiculous that the log should grow faster then the table. Any ideas why??
---------------------------------------------------------
SSRS Kills Kittens.
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
Hi all,
I have a serious problem:
I created 1 database with 2 file group : 1 primary and 1 index.
- Primary file group includes 1 data file (*.mdf): store all tables
- Index file group includes 1 index file (*.ndf): store all indexes
Most of indexes are non-cluster indexes
After a short time using, data file is 2GB but index file is 12 GB.
I do not know what problem happened in my database.
I have some questions:
1/ How so I reduce size of index file ?
2/ How to know what is stored in index file?
3/ How to trace all impact to index file?
4/ How to limit size growing of index file?
Thanks in advanced
Thi Nguyen
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?
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
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 Relatedhi
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
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!!! :)
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
Does SQL Server 2005 Enterprise 64bit can handle 250 - 300 terabyte of data considering my DBA is guru.
Thank you - Ashok
Hi
I'm trying to figure out how to retrieve the size of the data used per record, not the allocated size per row.
Any pointers would be helpfull.
Thanks in advance!
Rgds,
Worf
Hi, all here,
Thank you very much for your kind attention.
I am wondering if it is possible to use SSIS to sample data set to training set and test set directly to my data mining models without saving them somewhere as occupying too much space? Really need guidance for that.
Thank you very much in advance for any help.
With best regards,
Yours sincerely,
how can i change the initial size of the data and log file size ??? in my database properties it shows that my data file size is 81 mb and log file size is 985 mb! but my database only contains some tables and stored procedures with few rows of data in each tableand i checked that the actual mdf and ldf files are really that big... i tried to change it but it didn't work...can someone please teach me how to change it thanks!
View 2 Replies View RelatedI am trying to find a way to look at the size of each table in a database and the last time each table was updated/accessed by a user. I was just given control over a DB that is VERY BADLY maintained and I want to look at what I can get rid of in it. I want to start deleating by size and last used. I can find creation dates for the tables and row counts but not total size and last update/access of the tables. Does anyone know how to get this information?
Thanks,
Nathan
Hello Guys,
Just wondering if someone can help me decrease the size of mdf and ldf files. In the past production database "NewUniverse" had been allocated space of 100 GB for mdf file and 8 GB of ldf file. However the data file has only used 30 GB of data. But now due to disk space related reason, I tried to decrease the datafile size from 100 GB to 40 GB. But I am not able to do it.
Any help in this regard would be appreciated.
I am trying to compute the actual size of data and indexes in my database.
I have used DBArtisan,Desktop DBA and SEM , they all gave me different results.
Does any body now a valide , correct way of determining the size and the utilization of the database.
-Also I am trying to come up with archive/purge procedures , is their publications,white papers or ideas about this issue.
Thanks in Advance
Elias Akouri
Hi
I installed " Web Wiz Forum ASP SQL 2000 DB "
it work fine but
when i added some data in the forum for example my db size is 1.45 MB
after i delete those data the db size will not decrease
is there any code that i must enter on the sql server setup file
Excuse me i asked this question in the web wix forum site but they don`t
answer me
if know what i must to do plz tell me
Thanks
I need to store images in MS SQL. I have the upload procedures and stuff but I'm missing the point about the image data type size.
It is supposed to be able to store up to 2Gb!!! but when I declare the data field image I can't specify the max size for the field and by default is 16 !!
16 bytes!! what can I do with that?
How can I insert a file?
Please help
Against my better judgement, we are using fixed allocations of tempdb on some of our servers. This is to deal with specific limitations of our applicaitons and hardware configuration that I'm not allowed to discuss in much detail.
The problem that I have is that the present plan is to configure the data file at around 18 Gb and the log file at around 2 Gb. This seems just plain wrong to me, but I haven't been able to find a formal recommendation that gives any relative sizing. I would expect to have about twice as much log as data space, especially for tempdb.
Does anyone know of a formal citation (preferably from Microsoft) that discusses this?
-PatP
Hi all,
little background,
When I need an amount column I usually declare a numeric(9,2). Anything bigger then that ends up taking 9 bytes instead of 5 bytes and for the most part (9,2) is more then enough. Also I usually don't use (5, 2), (6, 2), etc... since they end up taking the same space as a (9,2), that is unless I want to specifically restrict the amount value to something smaller. Occasionally I will use smallmoney for smaller amounts, however I pretty much never use money data type since it's 8 bytes... that is unless I need more digits after the dot...
so enough background, here is the question,
Let's say you've got a numeric(5, 2) and a numeric(9, 2), as far performance goes are the two fields the same? Both columns have a size of 5 bytes so I assume that as far storage and I/O goes there is no performance difference.... Fair assumption?
But what about CPU and other things, are there any performance gain in using a smaller numeric column?
Hi All,
Is it possible to increase the size of data file in SQL Server 2005 Express Edition.
I think the licensed limit is 4096MB whcih i am unable to increase.
Could anyone let if is it possible to increase the data file size and if yes then how?
Thanks,
Varun
I'm new to the DBA world, and have no one else in the company to look up to. Does anyone know what I might need to check out or do when the Data File Size is 204% full? Or is this not necessarily a bad thing?
I'm getting this from a Diagnostic tool I have.
The number of tables is 148
Data file size 35,941 MB
Data Size 26,549.92 MB
Index Size 177,130.02 MB
Log File Size 5.05 MB
Thanks,
I am looking to automate monitoring space used for each file in eachdatabase on a SQL Server 2000. Does anybody have any SQL Scripts to dothis or to find the space used?
View 1 Replies View RelatedI'm using ODBC to interface a Microsoft SQL Server 2000. One of theoperations involves placing files within BLOBs. I'm using the imagedata type for this purpose. Most of the time this works okey, but whentrying to add a 21,3 MB file I get an error. The error code is 22001,which means "String right-truncation". But why? Does this mean thatthe field cannot accepts BLOBs with this size?
View 1 Replies View RelatedI've got a few VLDB's that we want to make smaller. Since the tables are running on legacy stuff, all of it's basically made with int's and char's and it's horriably inefficant.
The problem that I came across is when I made a new table with the best data types and copied the data from the old table, the table size was the exact size (excluding the index size). It was estimated that a total of ~20 GB would be saved with this change. As it turned out, 0 bytes of data were saved with the data types chagnes.
Why are the two tables the same, even though one has much more efficant data types?
If you want more information about the table I'm using:
391 columns.
50,147,035 rows.
65,295.625 MB in size.
Hi,
How can I get the Size of the image(binary) data in the datatable?
How can I get the size of the database through Query ?
Hi All,
Is it possible to increase the data file size in SQL Server 2005 Express edition?
If yes then how?
Thanks,
Varun