Problem In Row Size Space Allocation
Feb 15, 2007
Hi
I am using DTS to transfer tables from Oracle 9i to SQL Server 2000 sitting in a shared environment and managed to migrate
a lot of tables without glitch..
When I was migration a table <XYZ> from Oracle to SQL Server..The table was created in the SQL Server whilst the DTS threw an error that read when it was copying data and 0 rows were copied with the error message being
"Cannot create a row of size 8387 which is greater than the allowed maximum of 8060"
Incidentally the have a table in the Oracle DB that has 152 Rows of Data with 94 Columns..
Does any change needs do be done on the Admin side of the SQL Server to resolve this problem and faciliate effective transfer of data from the DB's?
Thanks in Advance
KI
View 1 Replies
ADVERTISEMENT
Feb 15, 2007
Hi
I am using DTS to transfer tables from Oracle 9i to SQL Server 2000 sitting in a shared environment and managed to migrate
a lot of tables without glitch..
When I was migration a table <XYZ> from Oracle to SQL Server..The table was created in the SQL Server whilst the DTS threw an error that read when it was copying data and 0 rows were copied with the error message being
"Cannot create a row of size 8387 which is greater than the allowed maximum of 8060"
Incidentally the have a table in the Oracle DB that has 152 Rows of Data with 94 Columns..
Does any change needs do be done on the Admin side of the SQL Server to resolve this problem and faciliate effective transfer of data from the DB's?
Sorry Had I started this topic in the wrong forum.
Thanks in Advance
KI
View 1 Replies
View Related
Sep 14, 2000
Hi,
I need to create a 5GB database with 4GB for data and 1GB for log in v7.0. I know that in v6.5 I would have created five 1GB devices - to go easy on the backups.
Could someone please advise on how I should distibute allocation of space.
Should I allocate 1GB to the primary files and 1GB each to 3 secondary files?
Should I just allocate 4GB to primary?
I would really really appreciate any reponse? If there are articles I would appreciate links.
Thanks
kira
View 1 Replies
View Related
Jan 16, 2001
I have a small data warehouse which periodically has old data deleted.
However after a delete the free space within the database is not released. If I copy the tables, drop them, recreate, and copy back, the space is there.
Any ideas what is causing this anyone ?
Thanks
View 1 Replies
View Related
Dec 15, 2007
Hi,
can some one throw some light on how the DBAs calucaulate the space allocations?
For example I have 30000 records which has 30 columns each defined as varchar(100) and if the db is full and wants to increase the space. Then how much extrac space should be allocated??
View 5 Replies
View Related
Aug 13, 2002
Hello ,
I wanted to know on what basis the disk space allocation for the databases is planned . Suppose if we plan 60 GB for data files ( mdf )for a given database then what should be the space allocation for the log files ( ldf ) and the tempdb ( both mdf and ldf files ).
Is there any thumb rule or any defined ratio for the same ?
Thanks
View 1 Replies
View Related
Jul 1, 2004
Hey all, first time poster here.
Cheers!
Enterprise Edition SQL2k sp3a on Win2k3 Enterprise Edition server.
Using BCP to load ~4 million records.
Largest record width is ~350 bytes.
When finished i have over 6 million pages reserved and only about 800 thousand actually used.
Which puts me 'round about 3 rows per page and WAY too much empty space.
YIKES!
Any thoughts as to why this is happening?
Or insight into how BCP handles page allocations that may be different than the typical extent based page allocations for table data?
Thanks!
View 14 Replies
View Related
Nov 19, 2007
I was trying to find out how much space is available in a 2000 db for allocation to tables and indexes. I am trying to find the amount of space that has to be used-up before another allocation is automatically made to the database. I looked at sp_spaceused but BOL is rather sketchy at defining what the numbers it returns really mean. Is the "unallocated space" the value I am looking for?
Thanks,
Michael
View 1 Replies
View Related
Sep 17, 2007
I have to force a space allocation to a DB file in order to stop a Diagnostic Manager alert which states that the database is over 80% full. Now, I don't think this alert is rational, nor do I think it is useful in any way but that's the way it goes. My only option is to allocate more space to the DB so that the space used will fall below 80% so the alert will stop being issued.
So, can you tell me how to force a space allocation on an existing db file?
Thanks,
Michael
View 1 Replies
View Related
Oct 20, 1999
I have a quick question maybe someone can answer me:
Suppose I have a database with data and log on same device, by default,
3 segments are created: system,default and logsegment.
Is the allocation , or any limit, among these segments(i.e. for system table, other table and indexes , log) done by the system ? or will the space required be allocated when they are needed ?
(i.e. I can have a situation of 10% database space used for data, 90% used for logging and I can also have 90% used for data, 10% used for log ?)
Is there any control us we can make ?
Of course, for recovery and performance reason, it is always recommended to have data and log on 2 diff. device.
View 1 Replies
View Related
May 6, 2004
I've faced with this problem in my DB:
There are some tables that suffer inserts and deletes daily. These tables have 3 nonclustered indexes including the pk.
I perceived that the space used for these tables are growing day after day even ocurring daily deletes.
These inserts and deletes follow the keys of the pk in ascent order.
I ran DBCC SHOWCONTIG on the tables and got results like this:
- Avg. Bytes Free per Page.....................: 7996.3
When I transform the pk to clustered this problem doesn't happen.
You realized the consequence of this: the users complains because the DB is without space, but it's not true!
Anybody could help me to understand why the extents are not being deallocated?
Thank's for help!
View 8 Replies
View Related
Mar 20, 2007
This is a question that has always intrigued me: what is the ideal File Allocation Unit Size for a disk holding only data or index pages on a server running SQL Server? It seems to me that 8,192 would be the ideal size as it would enable the system to gobble up an entire page in one go. Any ideas?
View 18 Replies
View Related
Jul 12, 2007
I have a Db that is 1.7 gigs. The table data takes approximately 200megs. The transaction logs were truncated. Where else can this large size be coming from and how can I confirm?
DB is generally small. ~25 tables, 100 SPs, 10 views, etc.
Note:
I have 4 queues using SQL Notifications, but when selecting from them results in no data.
Thanks
Scott
View 1 Replies
View Related
Dec 7, 2005
We are planning hardware purchases (more is better). One of our databases is 131 gigs in size and has 45 gigs of 'space available'. I'm not a very experienced SQL Server person, but this seems like quite a bit of 'space available'
1) Is there a way to regulate the amount of 'space available'?
2) are there any rules of thumb for how 'space available' there should be?
Appreciate any feedback or help.
Ray
View 5 Replies
View Related
May 7, 2008
Hi,
I have encountered a query which takes lots of temp tablespace and it fails later.
Initially the temp tablespace was 28GB, we made it to 56 GB but still it fails.
There is just this single process that is running on database.
I tried putting index on the table in query ,but no help.
Can suggest a solution to get size of temp space required or reduce its usage?
View 4 Replies
View Related
Jul 8, 2002
I'm trying to determine how much space some tables use (SQL2000), and I found 2 suggestions posted earlier, but can't get them to work for me.
The first was
"Right click on the DB in Enterprise Manager, select view then taskpad."
When I try that, I get some of the tables displayed with the info I want, but I can't see them all and can't scroll down beyond the first 22 tables in the database.
The second suggestion:
exec sp_msforeachtable @command1="print '?'",@command2="sp_spaceused '?'"
It says "command copleted successfully, but where does the output to this go ?? Is there something other than "print" I should use ?
The grid pane is empty.
View 2 Replies
View Related
Jun 2, 2004
How to find rowcount and size(space used) for all tables in a db? Can any one give me the script please?
Thanks,
View 2 Replies
View Related
Sep 19, 2006
We can know easily a database disk size, but can we konw a table in SQL 2005 database possess size? and more, how can I know the records in SQL 2005 Table possess size space?
View 8 Replies
View Related
Feb 20, 2008
Hi,
I was running out of space and thus deleted some rows from a table. To my surprise the db size increased. I then shrunk it to bring it back to what it was earlier.
When i deleted some 5000 rows, some space must have been released. Where did the space go and why did the db size increase after deleting the records?
I thght it might be log files..but db is set to Simple Recovery which does not utilize a Log File.
Any reasons?
View 6 Replies
View Related
Dec 3, 2007
What is the easiest way to calculate the space (size in bytes) used by a BLOB column that is already stored in a particular row?
Thanks.
View 3 Replies
View Related
Jul 7, 2015
While i execute dbcc sqlperf(logspace); I get following values.
Database NameLog Size (MB)Log Space Used (%)
master 16.17969 13.30275
tempdb 7.429688 61.7245
model 0.7421875 45.78947
msdb 5.554688 25.87904
distribution 2808.93 0.8172179
BANKDB 23438.87 48.20037
WSMIRSDB 109.7422 4.839111
For database BANKDB , Log Space used(%) is 48.83% and Log size is about 23438.87 where as my database size of BANKDB is 60 GB. FULL database and Log back is done every day night one time. My database is performing slow now.
Do we need to take log backup frequently like once a 1 hour so that Log space used will be less. Same query is taking more time to execute than before in same database is it because of log file has increased.
I do index organize and rebuild once a week and stats apply nightly.
Is it correct once log space size is increasing more than 10%. Do we need to take log backup?
View 4 Replies
View Related
Aug 26, 2015
I executed the below query and getting the capacity values only for master database.All other DB shows NULL values for spaceused. I'm actually looking for a query to get all the capacity information other than using temp table and the procedures. Is there any way using SQL query ONLY.
select
db.[dbid] as 'DB ID',
db.[name] as 'Database Name',
af.[name] as 'Logical Name',
convert(decimal(12,2),round(size/128.000,2)) as FileSizeMB,Â
convert(decimal(12,2),round(fileproperty(db.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
from sys.sysdatabases db
inner join sys.sysaltfiles af
on db.dbid = af.dbid
Below is the output
DB ID Database Name
Logical Name FileSizeMB
SpaceUsedMB
1 master
master 4.00
3.44
1 master
mastlog 2.00
[Code] ....
View 5 Replies
View Related
Mar 2, 2015
I have 10 Gb index and disk space only left 5gb .
How can i rebuild index ?
View 4 Replies
View Related
Apr 23, 2015
I am using sql server 2008 r2 on my end. I have created a database named testDB. I have a lot of tables with some log tables in this. some tables have contain lack of records in log table.
So my purpose is that I want to fix the table size of those tables(log tables) and want to move records in other database table placed on another location. So my database has no problem.
is there any way to make such above steps which I want for my database?
Is there already built any such functionality in sql server?
View 2 Replies
View Related
Nov 13, 2007
-- Initialize Control Mechanism
DECLARE@Drive TINYINT,
@SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area
DECLARE@Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @Drive <= 122
BEGIN
SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives
(
Info
)
EXEC(@SQL)
UPDATE@Drives
SETDrive = CHAR(@Drive)
WHEREDrive IS NULL
SET@Drive = @Drive + 1
END
-- Show the expected output
SELECTDrive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM(
SELECTDrive,
Info
FROM@Drives
WHEREInfo LIKE 'Total # of %'
) AS d
GROUP BYDrive
ORDER BYDrive
E 12°55'05.25"
N 56°04'39.16"
View 16 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
Nov 14, 2007
Hi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
View 1 Replies
View Related
Feb 23, 2007
I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005.
The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions
1) Replacing spaces with €œ €?
2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
View 1 Replies
View Related
Nov 26, 2015
I am using the below script to get space alerts  and now i am interested in sending alerts  if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %Â
Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList  VARCHAR(4000);
CREATE TABLE #tbldiskSpace
[Code] ....
View 3 Replies
View Related
Mar 2, 2005
Hi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.
Method 1: I used veritas to backup log file with truncate
Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)
After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?
View 1 Replies
View Related
Jul 20, 2005
This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
View 2 Replies
View Related
Feb 11, 2002
Hi,
Can someone give me some help on find out the memory limit for SQL Server 7.0 and how to limit the memory being used by the SQL Server?
Thank you very much in advance!
Denise
View 1 Replies
View Related
Nov 4, 2002
I was wondering, is there a way to allocate processors to SQL2000. I have a server that has 4 processors, I would like to leave one just for the operating system and have SQL2000 use the other 3. Is this possible and do you think it would be recomended to do this? Or should i just leave the 4 processors for everything?
thanks in advance
View 1 Replies
View Related
