Hey all,
Got a little problem. have 2 matching tables on different servers with the EXACT same column layout and data (the tables are being replicated with MSSQL7) and one table is 200MB while the other is 2000MB. I'm running MSSQL7 SP2. Any ideas???
-Marc
Got a question,
still new to sql server express, been playing with it for a while, kinda enjoying it.
But
If you were designing a database that had many tables with the possibility of a large amount of data, would you keep them all in one database or would you disperse them into multiple databases. There will be some relations used in some of the tables, but not all.
I believe I will end up with about 21 different tables, 9 of them have the potential for 1,000's of records.
I do appreciate anyone's thoughts or concerns
Davids Learning
How do I get a list of table names and their sizes, sorted by sizes? The command sp_spaceused only lists one table at a time.
Thanks for your help.
Lan.
Hi all..I have two tables such as cisco and ciscocom. and i wan to compare eachrow of ciscocom with cisco having same column values. i wan to get thecount of matching columns for each row in cisco...eg:Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco hascolumns:fw,ports,sec,des,tput etc. i wan the number of matching columfor each row in ciscocom. please provide me with the procedure....Waiting for your response....
View 2 Replies View RelatedHi,
I am new to SQL and these forums, and have a quick simple question.
I am trying to view the records that do not match in 2 tables. I have tried a few different ways but keep getting results in the hundreds of thousands and the table only has 36 thousand reocrds.
Here is an example of what the two tables contain.
Table 1
customer_no eaddress_no name address (36000 records)
Table2
customer_no eaddress_no email (17000 records)
I need to find out which customers are not in table 2 by linking the eaddress_no numbers.
This is one of the scripts i ran that gives me the large results with a ton of dups:
SELECT *
FROM T_EADDRESS inner JOIN
T_CUST_LOGIN ON T_EADDRESS.eaddress_no
!= T_CUST_LOGIN.eaddress_no
Thanks in advance for any help with this.
I have a situation where I want to pull strings from one table of a SQL 2000database and find matches for it in other tables of the same database andhave those values returned. i.e. In one table I have prospects and I wantto match their names to a table that stores the names of prospects turnedinto customers. I want to write a query that looks through every entry andreturns a match for each corresponding value (from prospects to customers).So if "Smith" is found in prospects I want SQL to return "Smith" incustomers with full contact info.Any pointers on getting started on this is greatly appreciated. Or if youcould just point me to a reference. Obviously, I need to do some kind ofparsing. I just need to be pointed in the right direction.Thx.
View 2 Replies View RelatedI have a temperature table with a column that shows the how many hours old a newborn was when his/her temperature was taken.
Example lets say once per hour.
I want to join to a table called Weight that records the newborns weight at any given time.
Example lets say 3x during the day.
1 @ 8:45am
2 @ 11:15am
3 @ 4:30pm
I want to figure out which weight recording is the closest to a given temperature recording and return that one row.
Let's say I have a list of IDs called EntryID and each EntryID can belong to ONE table out of a group of six, what is the best way to get a listing of these?
For example:
select r.*
from #Reminders r
left join mytable1 mt1 on (r.EntryID = mt1.EntryID)
left join mytable2 mt2 on (r.EntryID = mt2.EntryID)
left join mytable3 mt3 on (r.EntryID = mt3.EntryID)
left join mytable4 mt4 on (r.EntryID = mt4.EntryID)
As you can see, #Reminders has one field called EntryID (and many rows).
In my example above, only ONE of those tables will actually be able to join but I have no idea which one has the matching EntryID.
What is the best way for me to do this? I want to grab "ReportStatus" from the corresponding "mytable"... (each "mytable" has a ReportStatus column)
I have 3 table
table_A
table_B
table_C
TABLE_A
SNO NAME ID
1 RAJU 070491
2 VAMSHI 089767
3 ARUNA 068908
TABLE_B
SNO NAME ID
2 RAJU 070491
4 JKLKJ 098766
I WANT COMPARE TWO TABLES(TABLE_A & TABLE_B) SELECT TABLE_A MATCHING VALUES COPY IN TABLE_C
EX-
SNO NAME ID
1 RAJU 070491
Two tables:
table #1 = timestamp, servername1(never changes), value(int)
Table #2 = timestamp, servername2(never changes), value(int)
What I need to do is merge the two tables into a single table based on the matching timestamp:
timestamp, servername1, servername2 as headers then values under
"1/1/1-1:10:1" , "10" , "10" - as an example
"1/1/1-1:20:1" , "20" , "21" - as an example
"1/1/1-1:30:1" , "1" , "5" - as an example
Powershell or MSSQL 2008 queries work.
I have a powershell script atm that can create the table and then throw it back into the database but its very slow because of the amount of records.
The table is for some performance stat collection app.
Table #1 records a timestamp, the servername (which is the same over and over in the table) and the value of the performance object.
Table #2 is the same collection but for a different server.
The layout in the database is terrible but this is what I get to work with.
Hi All
I have a strange request that might not be possible based on the laws of relational databases but I thought I'd give it a try.
I have three tables which for simplicity I will call A, B and C. Table A contains my master records, Table B contains user details and the final table contains some extra data
In my initial search when joining A and B, I return 100 records. I then need to search in table C for these 100 records based on a criteria. the expected result should return all 100 rows for the ones that match and also the ones that do not match. The problem is that in Table C, not all the 100 IDs exist, so there will not be a corresponding record. Unfortunately, our users still want to see all 100 records in the output. Is this possible
As always any help or direction would be appreciated.
I need to generate a script where it should check the tables of a database from one server to another server(If server 1 database XYZ has 10 tables then server 2 database XYZshould match 10 tables). If the tables matches then truncate tables data and insert the data from one server 1 database XYZ to server 2 database XYZ. If tables does not matches, then it should check and create that tables which are not matching.
View 8 Replies View RelatedAnyone here with a ready to go sqlscript that lists all db's, files, sizes, owner etc? I guess it's a combination of sp_databases, sp_helpdb and sp_helpdb [db].
View 2 Replies View Related
hi,
i'm planning to create a DB of about 1TB of text.
can SQL EXPRESS handle this sizes of data? what about SQL 2005?
does it matter if i put all the data in 1 table?
Thanks.
Hello,
I was told a while ago that your Log should be 2 times the size as your data. Is this a good rule of thumb?
Lee
Does anyone know of a quick way to find out what the largest indexes on a database are? I have a number of tables and was wondering if there's a stored proc or query that I can execute that will list the indexes and their size in order by size? Thanks
View 1 Replies View RelatedDoes anyone know of a quick way to find out what the largest indexes on a database are? I have a number of tables and was wondering if there's a stored proc or query that I can execute that will list the indexes and their size in order by size? Thanks
View 1 Replies View RelatedHi there
I hope this isn't a totally stupid question, but I was wondering how to check the physical size the DBs take up on disks...
Does MSSQL save DBs as a specific file-extension? Is there an SQL command I can run that will give extensive info on DB sizes?
Any help welcome :)
Thanks!
When you have the autogrowth turned on for log files. What happens when you put a max file size on it? Will just overwrite the old logs to keep the file at the max size or will it just create a new file every time it hits the max size?
Thanks!
I'm putting together a manual system that tracks data growth in a certain database. I was going to use sp_spaceused as a part of it, but then realized the datatypes for size are CHAR, not INT or BIGINT. I was going to do counts, averages, etc. on those columns but that wouldn't work against a CHAR field obviously. I could easily write a little something to strip out the KB, but was hoping there was another way to get those figures.
Secondly...has anynoe seen a stored procedure/code/etc. that just calculates the largest/smallest/average row size for a table? I haven't been able to find anything anywhere...
Any insight would be greatly appreciated...
Thanks!
Hello again everyone....
I have another question for everyone....
I am currently cleaning up my database to get its total size down and am not sure how nvarchar and varchar work exactly.
When defining the length of a varchar or nvarchar in enterprise manager, will that effect the size of the entry (as far as data size) no matter what the length of the entry? In other words, will there be a difference in Data Size for an entry with the length of 4 characters with a definition of varchar(4) versus an entry with the length of 4 characters with a definition of varchar(50).
****If there is no difference, is there any reason in trying to best guess the size to give nvarchar or varchar columns? It would seem easier to just define the lengths of columns which need variable lengths to 200 or 400 just to save time in not trying to best guess what the size might be...*****
Thanks ahead for any help...
-Alec
Hi,
I am looking to runa query to get the sizes of the tables in my SQL 7 DB.
I know I can access the info in Enterprise Manager, under "Tables & Indexes".
But I need to get this info via a query.
I need rows and size.
I figured out how to get rows through the sys tables:
select sysobjects.name, sysindexes.rows
from sysobjects,sysindexes
where sysobjects.name = sysindexes.name
and xtype = 'U'
Is the size of each table stored in a sys table as well? I can't find it.
Hi,
my log files are growing like anything. One of my log file size is 20GB.
How i have to reduce the log file size.
If i run DBCC command is it come backs...
Pls tell me the way how i have to find the free space and reduce logsizes.
After taking backups also my log file sizes are not reducing.
Thanks!
Kavira
I have inherited a number of databases which were substantially over sized when they were set up. I'd like to reduce both the log and database files to be smaller than their original sizes, what's the easiest way to do this? If anyone has any experience of doing this please reply.
View 1 Replies View RelatedWe are looking at installing a new Oracle server for a client but have been told that they have used Oracle in the past but had a lot of problems with slow response even though the bandwidth on the WAN was barely being used. He says that this was due to the fact that Oracle sends out very small packets across the network meaning that there are hundreds of packets being sent out. This caused a problem on the routers being used as it was killing the processors. Is this still the case and have you had other reports of slow response of this nature?
View 1 Replies View RelatedOk, I have a new one. Several of my devices are showing with negative sizes when viewed in edit in enterprise manager. I cannot edit them as the change now button is grayed-out. Oddly enough they are all located on the same drive. The master (on C drive), and the tempdb (on D drive) both show as the default device. I am very confused. User access to the information is fine. What gives?
View 2 Replies View RelatedHi All,
I ran the following query to get the log file sizes for all databases:
select (size*8.00)/1024,filename from master..sysaltfiles
When I compared the results from this query with the transaction log properties in EM, not all of the sizes match.
For example, EM shows the transaction log size for tempdb to be 2 MB but the results of the query shows that transaction log size for tempdb is .5 MB. Which query can I run to get the numbers that would match between two? Thanks.
Wonder if anyone can help me out here.
I'm trying to set up a job to run overnight that mails me the size of all the databases on SQL Server.
The way I'm getting the size of the db's is by running the following in SQL Query Analyzer...
EXEC sp_MSforeachdb @command1="print '?' select cast(name as varchar(32)), round(size * 8 / 1.024,3) from ?..sysfiles"
Can anyone suggest a way that I can export the results of this to a text file that can then be mailed to me as part of a scheduled job.
Or can anyone suggest a better/easier way of doing this??
Thanks in advance. :)
Admin is kind of new to me. Could someone recommend how to reduce these file sizes?
In Dev, my MDF is 16 MB, LDF .5 MB.
I run a script to recreate the schema and insert rows on the Host.
When downloading a db backup from the Host and restoring it to Dev, my MDF is 24 MB, LDF 25 MB.
The same dataset grew by ~35 MB!!!
Any advice is welcome. Thank you. Rick
Can any one direct me to sources for best practices of field types and sizesto use for commonly used information such as address, names, city, businessnames ....Thanks, Brian
View 3 Replies View RelatedI have reporting services installed on a windows 2003 server. If i open a report directly on the server using Internet Explorer 7 and reportviewer and click the print button on the report viewer control. The print document lines up perfectly on the output page and prints beautifully. I have a winforms app that calls the same report as an IMAGE and then prints it. The size of the output on the print page is way off and the font is almost triple. I have it working in another clients environment with similar serve setup and same versions. I have tried different print drivers and different workstations but I think it has something to do with reporting services config. Can someone please help. I have set all margins in the .rdl file to 0 and when i create the PrinterSettings object I set all margins to zero again.
Thanks in advance.
Mike
How can I get the table sizes for data and transaction logs just like we had in SQL Server 7.0 on the first screen of the Entreprise Manager?
I remember having a bar showing used space in blue and unused in magenta. I bet there are a couple of functions that can be added in a script that will retreive this info.
How can I get the table sizes for data and transaction logs just like we had in SQL Server 7.0 on the first screen of the Entreprise Manager?
I remember having a bar showing used space in blue and unused in magenta. I bet there are a couple of functions that can be added in a script that will retreive this info.