Calculating Row Size Including Blobs
Jul 20, 2005
I've seen plenty of posts regarding the estimation of table size,
usually in the processing of planning for server storage needs.
Well, I've got a different problem. I need to know how much data each
of our Customers are using in a Database. (1 SQL DB stores multiple
customers).
Basically, I want to be able to say: Customer A: 45.5 MB, Customer B:
655 MB.
So, how can I ask SQL Server how much data each Row in each table is
taking up? I want to be able to calculate nightly the total size, so I
would take each row in each table that belong to the customer, and add
all the sizes together. I want to take into account blobs that are
storing images and PDF files also.
Thanks in advance,
Jesse Wolgamott
View 1 Replies
ADVERTISEMENT
Aug 7, 2000
I would like to calculate the size of a table eg. 100 rows, 5 columns.
Any suggestions?? Thanks Vic
View 1 Replies
View Related
Aug 11, 2000
I need to calculate the size of a database. Is the following formula correct??
(a) The size of a record (i.e. sum of all the columns) divide by 8Kb.
(b) Divide number of records by answer from (a)
(c) Multiple (b) by 8Kb
(d) Sum up the tables
Thanks,
Vic
View 1 Replies
View Related
Feb 8, 2000
How do I calculate the Record size? I mean the Row size for each table.
I heard that there are formulas to do that and the calculation is different for Fixed lenght Columns and Variable length Columns.
I need to calculate the record size for nearly 500 tables.This will help me to decide on the DISk capacities.
Suggest me if any scipts or calculations already available.
Thanks in advance.
Samson
View 4 Replies
View Related
Aug 10, 2006
If I have a database with a list of tables is there a way to calculate the size of each table individually and
then calculate the size all the tables. If you have 1 table with say 10 rows and 3 columns and the width of the
columns are of variable length you could do something like
( column1width + column2width + column3width ) * No.of Rows = Tablesize
So my question is can I reference the column width of different columns in a
table using sql ?
Another issue is that some of the columns are different datatypes so I should be taking that
into consideration as well.
From searching the internet so far I have seen little on SQL showing how to
reference column width in a table.
View 5 Replies
View Related
Jan 1, 2004
Hi , everyone !
I need help . Is anybody can help me to finde transact-SQL code of calculating the database size.
Thanks in advnce , Alex
View 1 Replies
View Related
Jul 17, 2007
Hello,
If I have a report that includes a page header, the report viewer will render the report at the full width, but if I hide the page header and show only the body it will use the absolute width of the report.
I have a 7" wide report, with .75" borders set on each side.
Interactive & Print size are set to 8.5x11
Changing these sizes has no effect on the behavior of the viewer, which appears to ignore them.
When I view this report in the local viewer the contents fill the window if there is no page header.
If I enable the page header then the report is drawn at 8.5" wide, leaving a _big_ white border on the right side.
Any comments or workarounds that anyone knows? I know the local viewer is not a standard configuration (at least it seems) but it is what we need to use.Thanks,//Andrew
View 1 Replies
View Related
May 24, 2004
I have searched high and low for this answer, but still to no avail...
Does anyone have a reliable way of storing serialized data into a table which does not use direct SQL on the client side? I am using Stored Procedures and simply need to be able to read out of the table the object written in.
Inserting appears fine, but reading only seems to come back with 16 bytes of data and I just can't figure out why. Whats the recommended way of doing this?
Matt.
View 2 Replies
View Related
Mar 27, 2000
Are there any issues with BLOBs in SQL 7.0 ?
View 2 Replies
View Related
May 4, 2004
Hello to all,
Please help me to get a right decision:
storing BLOBs 5M in database or using URL. I saw a lot of articles about this matter and really cannot decide.
The database size is about 35 M, not too much DDL operations.
Advises?
View 3 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
Jan 4, 1999
Howdy,
Any one got any tricks on speeding up storing and retrieving image data? I have a typical 2MB image and I am trying to improve speed... it now takes 8 seconds to store the blob.
thanks
Dick Butler
View 1 Replies
View Related
Aug 3, 1998
Help! Where building a document mgmt system that will store MSword documents, etc in SQL Server.
When we BCP a table which contains image data to another copy of the table,
the image data seems to get clobbered and is not longer valid. Has anyone had this problem
View 1 Replies
View Related
Feb 15, 2007
We have a blob in one table that is storing pdf files. I need towrite a select query that will grab that column and write those *.pdffiles out to a location on my hard drive. Does anyone know how thatcan be done? All suggestions would be greatly appreciated....
View 2 Replies
View Related
Jul 8, 2007
Hi,
VS2005
SQl workgroup 2005
I have a DB where I store alot of Blobs, the blobs are tagged up with alot of id. My concern is deleting them automatically on a regular basis, too keep the DB size down. I will be deleting them based on based on age(date/time) and what category the blob is classed as.
- My concern is how to delete blobs sort of equally dispersed among the categories so no category ends up empty although it may have old pictures in it.
Any tips, links or code is much welcome.
(my app will be acessing the DB though ADO .NET 3.0 c++/cli)
Cheers,
Andreas
View 17 Replies
View Related
Aug 17, 2006
I want to store message database in SQLEv. Each message contains fixed header and a body. Body is UTF-16 encoded text. Average size of the body is about 700 symbols. Standard deviation about 1000 symbols. Maximum size - 200K symbols. Total count of the messages is up to 2 millions.
The only type I can use for body is text or binary blob. But blob larger than 256 bytes store in separate pages. Because of most message bodies lager than 256 bytes, each message take avg. PageSize / 2 not used for storing data, but occupied space.
For 2M messages this space will be 4096 / 2 * 2M = 4G. But SQLEv has 4G limit for database size!
Is it any way to store such data in SQLEv more efficiently?
View 5 Replies
View Related
Mar 4, 2004
I'm trying to store files such as pdfs in my SQL Server as blobs for a particular .NET application and I'm noticing a rather large performance hit when compared to if the pdf was simply stored in the web server's file system.
I have a pdf of 7MB. If this is on a web server it takes roughly 9 seconds to download. When I store the same pdf in my database and then write it onto the page using the code below it takes roughly 40 seconds to display. Can anyone help me to improve this performance difference? Should I not be using this method to store large files?
Dim PdfCol As Integer = 0 ' the column # of the BLOB field
Dim dr As SqlDataReader = cmd.ExecuteReader()
If dr.Read() Then
Dim mypdf(dr.GetBytes(PdfCol, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
dr.GetBytes(PdfCol, 0, mypdf, 0, mypdf.Length)
End If
Response.Clear()
Response.Buffer = True
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.BinaryWrite(mypdf)
Response.Flush()
Response.Close()
View 4 Replies
View Related
Dec 14, 2006
Hi
I wanted to ask a question in terms of design (Performance, scalability, maintenance, flexibility etc.) is it better to refernce a document on the actual file system (on a file server) i.e. from a database link to the actual file at a disk location
e.g. in the database have something like \fileserver2006 estewtext.txt or C: estewtext.txt
or is it better to store all the files in a database as blobs?
The Question i would like to ask is more in the context of desigining a document management system?
View 5 Replies
View Related
Jun 25, 2007
Hi geniuses,
I have a core database question in MS Access and SQL Server.
Assume the following table in MS Access or SQL Server:
Employees table:
EmpID, Name, Hiredate, Photo;
and the following query:
"SELECT EmpID, Name, Hiredate FROM Employees;"
The question is, "does the blob object (Photo) affect the performance of the query even when not included in the select statement?"
I mean, will I gain some additional performance when separating the blob fields to another table?
Thanks for the answer.
View 4 Replies
View Related
Oct 14, 1999
Greetings
I'm clear about the use of a DELETE trigger to "move" your deleted record to a second database as a sort of recycle bin.
But SS7 has the limitation, and it's mentioned in BOL, that it cannot reference your TEXT, NTEXT or IMAGE fields in the DELETED table. It says to join the original table with DELETED to get at those fields.
The only problem is the original table's record has been deleted! Even though the transaction has not yet been COMMITTED.
Here's my Trigger:
CREATE TRIGGER AuditTest ON Activity FOR DELETE AS
INSERT AuditDB.dbo.Activity
SELECT Activity.* FROM Activity INNER JOIN Deleted
ON Activity.ActivityID = Deleted.ActivityID
And for discussion, here's my Table:
ActivityID uniqueidentifier
OrgId uniqueidentifier
Title varchar(600
Active bit
Comments text
LastUpdate datetime
Any suggestions? Has anyone been able to implement a DELETE Audit
Trigger on a table with BLOBs?
Thanks,
-Rich
Richard Hundhausen
Stuttgart, Germany
View 3 Replies
View Related
Apr 15, 2004
Hey Guys I knwo this may sound impossible but lets say I have a number of fields one of which is a Long blob or long text
is there a way to have MYSQL search the blobs for keywords and then to extract them to other fields?
basically what I am asking is it possible to parse a long text blob for keywords and then grab data before or after those keywords?/
anyone know a way???
View 3 Replies
View Related
Apr 29, 2015
I currently have a lot of XML blobs being stored in several of the production databases that I look after. They're there as a application "logging" feature, so that the developers can investigate issues if they ever occur.
I want to get these out of my OLTP databases asap and am currently looking at options. I know Brent Ozar recently posted an article about Content Addressable Storage systems [URL] ....
How do you handle point in time recovery? Any other ways of storing this data?
View 9 Replies
View Related
Feb 5, 2004
I am taking over a Database set up where we have 2 databases. One is a SQL SERVER 2000 db and the other is an Oracle 9i db.
THe previous DBA had/has a problem with transferring CLOB and Blob datatypes From the ORACLE db to SQL SERVER. He says that the OLEDB driver SQL SERVER provides has limitations particularly on the CLOB data because it truncates data over 4000 characters in length.
For the Blob issue:
We basically want to store/move attachments (word documents etc)
into image datatypes (SS) from the blob datatype (9i).
I figured I could do this using DTS or even using the Linked server method. I figured worst case scenario I would just store the documents on the server and then use SQL SERVER's textcopy utility to upload the documents into the image datatype column of the SQL SERVER table.
For the Clob Issue I have no idea what to do, because I cant find anything online that mentions SQL SERVER having issues with importing CLOB data over 4000 charcters in length, afterall the text datatype accomdates more than 4000 characters.
Does anyone have any suggestions for moving the Blob and most importantly the Clob datatypes from 9i to SQL SERVER without any truncation in the data????
View 5 Replies
View Related
Jul 20, 2005
Hi folks,I want to be able to store and retrieve UNSAVED Word documents as BLOBs. Igot all the info for storing them if they're already saved on the filesystem. But what if they're not already saved? I could save them to a tempfile first and then make it a blob, but I'd rather not put them on the filesystem at all. I could use Document.Contents to get the Range object for thewhole doc and then BLOB that, but I'm not sure that would be quite the sameas a .doc file, maybe things like Styles and the like would be missing.TIA!Dave
View 2 Replies
View Related
May 28, 2008
I got this error yesterday running an SSIS package:
"The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions."
This same package was working fine on a different server, so I compared the configurations of the two boxes. It turned out that the service account that runs the package was in the Windows Administrators group on one box but not the other. I added the service account to Administrators on the box that had the problem and the problem went away.
I have looked around and have not found a list of the necessary Windows permissions that an account must have in order to run an SSIS package. This list would include write permissions to the path specified in BLOBTempStoragePath. Has anyone out there configured an account with minimal permissions to run a package that has a Data Flow?
Thanks,
Ron Rice
View 5 Replies
View Related
Aug 13, 2007
I'm trying to write SSIS packages to import DB2 6000 tables to SQL Server 2005 tables. The package vendor has defined CLOB and BLOB datatypes for columns in the DB2 tables, and going through the Data Flow tasks from OLE DB for DB2 to OLE DB for SQL Server, it tries to map CLOBs to TEXT columns, and BLOBs to IMAGE columns. The vendor has no conversion utility available, so I have to write the SSIS packages. However, the task generates a goodly number of errors just trying to open the OLE DB DB2 dataset. Errors like the following:
Error: 0xC0202009 at Set 5 1 2, DB2 vendor_master [7127]: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Error: 0xC0208265 at Set 5 1 2, DB2 vendor_master [7127]: Failed to retrieve long data for column "COMPETITOR_COMMENT".
Error: 0xC020901C at Set 5 1 2, DB2 vendor_master [7127]: There was an error with output column "COMPETITOR_COMMENT" (8866) on output "OLE DB Source Output" (7138). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Set 5 1 2, DB2 vendor_master [7127]: The "output column "COMPETITOR_COMMENT" (8866)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "COMPETITOR_COMMENT" (8866)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047038 at Set 5 1 2, DTS.Pipeline: The PrimeOutput method on component "DB2 vendor_master" (7127) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Set 5 1 2, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Set 5 1 2, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Set 5 1 2, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
Information: 0x40043008 at Set 5 1 2, DTS.Pipeline: Post Execute phase is beginning.
In this case, 'COMPTETITOR_COMMENT' is defined as a CLOB(2 M) column format in the DB2 database table, and the output format in the SQL Server 2005 table is defined as a TEXT column.
Any assistance would be appreciated.
View 1 Replies
View Related
Apr 24, 2008
I have a table with embedded files in it and also ftp server/user/pwd fields so that each record has all the data and information required and need to configure an SSIS package to to FTP them to the destination as specified in the record within the databaseAt the moment I have a data flow task which uses a Derived Column and an Export Column transformation which creates the files to a temp folder then it runs a ForEach loop container back on the control flow to FTP each file and to then move each file to an archive folder.
All I think I need to know how to do is to somehow control the FTP connection settings with the fields from the database within this foreach loop, but it is a for each file collection. I also need to update each record saying it has been FTP'd as well
Do I need to have an initial query outside of this foreach loop to populate an array which can be used for the 4 FTP connection fields (server/user/pwd/directory), if so how?
Any ideas as to how to correctly finish this package.
Thanks
View 2 Replies
View Related
Jan 30, 2008
Hi,I have a sql query like this select avg([mycolumn]) from data where date > '1/5/08' and date < '1/10/08' group by [mycolumn]order by [mycolumn] descIf all values within that average are numbers, I'm fine. If it's a 0 (not a null, a 0) it doesn't get averaged in. For instance, values 0,1,2 should produce an average of 1.(0+1+2)/3 = 1. But sql is returning a value as if my 0's were nulls and not factored in: (1+2)/2 = 1.5Does anyone know why this is happening and how to fix it?
View 5 Replies
View Related
Jan 30, 2008
Hi,
I have a sql query like this
select avg([mycolumn]) from data where date > '1/5/08' and date < '1/10/08'
group by [mycolumn]
order by [mycolumn] desc
If all values within that average are numbers, I'm fine. If it's a 0 (not a null, a 0) it doesn't get averaged in. For instance, values 0,1,2 should produce an average of 1.
(0+1+2)/3 = 1.
But sql is returning a value as if my 0's were nulls and not factored in:
(1+2)/2 = 1.5
Does anyone know why this is happening and how to fix it?
View 3 Replies
View Related
Dec 27, 2007
Hi,I'm trying to include a button control inside an iff where I am showing the results of a datalist. My aim is for each user listed, to show whether or not he is already a friend of the logged in user. To do this, I want to create an iff where if the user is already a friend, I will show the text "already a friend". If the user is not yet a friend, there will be a button saying "add as friend" which when clicked will execute an insert query to the database to add that user as a friend. I tried the code below, but it doesn't seem to recognize the button inside the iff. I really need a button to work if the result of the iff condition is DBNull.Value... Can anyone please help? <%#IIf(Container.DataItem("userNameIfFriend") Is DBNull.Value, "<asp:LinkButton id='addbuddy' runat='server' > Add as buddy</asp:LinkButton>", "Already buddy")%> I also tried the code below instead, but it doesn't work either, and I do not know what to include instead of true and false, since the button would only be enabled if the case was true. <asp:LinkButton id='addbuddy' runat='server' Enabled="<%#IIf(DataBinder.Eval(Container.DataItem, “userNameIfFriend�) is DBNull.Value, “True�, “False�)%>">Add as friend</asp:LinkButton> Thanks a lot.
View 6 Replies
View Related
Jun 15, 2007
Hi everybody
I have this sql which calculates the total
MY TABLE IS:
A B C
100 5 3
100 0 5
20 0 0
sql is
A - B + C as total
the result is it gives me only the total of the first row since it has values on the on the B AND C
the result is
A B C total
100 5 3 98
100 0 5
20 0 0
with no result for the last two rows.. I want to give a condition that if the value is zero or null go to the next...
thanks for any help
View 13 Replies
View Related
May 23, 2006
Collin writes "i have tried to do a query between 2 different tables that have different columns. within both these tables there is a column that has a column name different from the column name of the other table but the data within both columns is the same. i wanted a query that compares the data between the 2 tables.
Example is:
Table 1
Columns : firstname , age , id , grade.
Table 2
Columns : name, sex , school.
lets say that the data of column "firstname" of table 1 contains the same data as column "name" of table 2 and i would want to do a query that retrieves records for where data in the column "firstname" in table 1 is found in column "name" of table 2.
sql server 2000 with service pack 3 running on windows 2003 server service pack 1
Rgds,
Mr. C. "
View 2 Replies
View Related
Jun 28, 2006
I have a three server peer-to-peer replication setup that includes articles for tables and views. As I understand the BOL, scheme changes -- which I take to mean changes, amont other things, changes in the design of a table or view -- should automatically replicate to the other servers in the topology. Here are my quesitons:
When including a view as part of the publication, what is it, exactly, that is getting replicated? If all the tables supporting a given view are being replicated, and the view exists on all three boxes, whatelse, besides the view schema (and changes thereto) is being replicated?
Secondly, if in fact schema changes are replicated, why can't I modify a view that is part of a publication? When I try to make a change to such a view, I get a server timeout message, every single time. When I remove the view from the publication, I can make the modification with no trouble. What does replicating schema changes mean if I can't make changes to the schema?
Thanks for any enlightenment on this.
Randy
View 5 Replies
View Related
