How Much Space In A Db Is Available For Allocation To Tables And Indexes?
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
ADVERTISEMENT
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
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
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
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
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
Jan 24, 2000
We have a table with 48 million rows. The table should only be approximately
28 bytes long for each row. This table is clustered primary key consisting of
an integer key and a calculated float value. We have found that the size of the table
is doubled before statistics are gathered. There is a foreign key that references the
primary on another table. There are no other indexes. Sp_spaceused shows the
size changes in the data portion. I had worked on another DBMS system that
had no benefit on collecting statistics on a clustered value. Is this not correct for
SQL Server? Does anyone have an explaination for the size change?
View 1 Replies
View Related
Jul 20, 2001
This database is running on SQL sever 6.5.
We are running an home-created stored procedure that includes several rounds of dropping and recreating indexes in a few tables, i.e., after dropping indexes, the tables are truncated, then a lot of records will be inserted into the table, and the indexes are recreated.
we are getting the following error in turns:
1. ODBC error: Microsoft] ODBC SQL Server Driver] SQL Server]Can't
allocate space for object '-841' in database 'tempdb' because the
'system' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE...
We then used EM to expand the tempdb, when reran the procedure, we got the following message:
2. ODBC error: Microsoft] ODBC SQL Server Driver] SQL Server] Cannot
drop the index 'dbo.ItemBalamce_CCB_Id',because it doesn't exist in the
system catalogs.
However, when I check the mentioned index, it is certainly there.
How could this happen?
After several rounds of running theis procedure, these two error messages appeared to occur alternatively.
If you have an answer or suggestion, please let me know ASAP.
Many thanks.
View 2 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
Mar 19, 2015
Is it possible to reclaim space after rebuilding indexes(shrinking is not an option).
View 9 Replies
View Related
Dec 18, 2007
hi all
i am under impression that indexes also stored seperately from data and need extra space . when we check in EM Table Info, size over there is just data size or sum of data and indexes. if it is just for data then is there is any space used to store indexes and that space is counted in space used by data base or else where.............. please clear my confusion.............. i am quite new in administartion of SQL Server
View 2 Replies
View Related
Sep 26, 2007
Hi all,
When I am rebuilding the indexes on the tables, I am getting lot of free space( unallocated) on the database.
Before rebuilding the indexes , the size of the database = 385 Gb
After rebuilding the indexes, the size jumps to = 572 Gb (i.e.) This means 187 Gb of unallocated space .
The Command use to rebuild indexes is:
USE [databasename]
GO
ALTER INDEX [PK_index] ON [dbo].[tablename] REBUILD WITH
( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF )
GO
So, every time we rebuild indexes, we have to shrink the database
(or)
Is there anything else ,I should be doing.
Thanks.
View 4 Replies
View Related
Jul 9, 2015
I am using SQL Server 2008 (RTM) Standard Edition.
In my environment, one of my Database size is 75 gb and I have to create a plan for index rebuild using maintenance plan.But when we rebuild indexes, it requires some space on data and log files of database.how can we calculate disk space requirement for index rebuild process ?
View 4 Replies
View Related
Jun 7, 2001
How do I move a table (test1) to a physical file group, and the table(test1) indexes to another file group. One clustered index and 3 nonclustered indexes. Thanks!!!!
View 4 Replies
View Related
Oct 9, 2001
Hello List,
I've a customer table and It has two indexes only. But when I view from the query analyser it list ten indexes with the following name:
"_WA_Sys_status_01EAB64E"
I do not know, what does the above index does and i remember, I did not created that. When trying to drop that, It says, Its not an index. I dont know, what to do. Would somebody please advice me on this.
Thanks,
View 3 Replies
View Related
Mar 17, 2006
Hi,
Can someone point me to where I can find out if an SQL server table has an index ? I was in enterprise manager and brought up properties for any given table but was unable to find the information.
Thanks,
Jeff
View 7 Replies
View Related
Apr 17, 2008
Is there any way to know which ones are the biggest (In Mb) tables and indexes in my database?
thanks
View 1 Replies
View Related
Jun 17, 2007
Hi,
I have 2 tables with this design: one has ArticleID as primary key and multiple other fields and one has GroupID as primary key and multiple other fields. Each article can belong in multiple groups so I created a new table called articleGroups with only 2 fields: ArticleID and GroupID to show the groups associated with each article. There is a relation between this table and each of the main 2 tables. My question is, in the articleGroup table, does it make any sense to create an Index on ArticleID, GroupID or both? Since the group is needed for each article the Groups will always be queried everytime the article is queried. So, I am not sure if an index is needed?
Thank you,
Regards,
Iulian
View 5 Replies
View Related
Apr 17, 2008
Is there any way to know which ones are the biggest (In Mb) tables and indexes in my database?
thanks
View 4 Replies
View Related
Jun 16, 2003
Hi,
I want to list the table names in a database "mydev_db".What would be the query ?.
I want to run a similar query to find out the indexes,views,stored_procs etc.
Regards,
Copernicus
View 3 Replies
View Related
Dec 19, 2001
I need to drop and recreate indexes in some of my tables that are currently been replicated. I am not sure how this will affect my ongoing replication. Will this cause a problem for me? Please help
Bright
View 2 Replies
View Related
May 22, 2007
I have a pretty large database that has tables that will contain millions of rows of records. I will predominantly be using Views just to select the data. (I will not be performing any updates or inserts). I propose creating indexes on the views. My question is - if I create indexes on my views, do I have to create them on the tables as well? Is it good practice to create indexes on tables by default even if I am not going to be performing select statements directly on my tables but via my indexed views? Any advice is appreciated.
Thanks
Ran
View 4 Replies
View Related
Oct 22, 2007
In SQL Server 2000 one could DBReindex every index that exists in a given database. You can do the same in SQL Server 2005. But how can this be done with the new Alter Index command? It does not allow me to pass in a variable for the object. Any ideas on how to get this done in with Alter Index in 2005? Thanks!
This I can't get to work:
DECLARE
@TableName nvarchar(100)
SET @TableName = 'Account'
USE database;
GO
ALTER INDEX ALL ON @TableName
REBUILD
GO
USE RZTQ5OL02
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT Name from sys.sysobjects where type = 'U'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
View 3 Replies
View Related
Apr 26, 2000
Anyone know how to place additional indexes on system tables?
View 1 Replies
View Related
Nov 19, 1998
hi, if exists (select * from sysobjects where id = object_id('dbo.MRDD_FINAL') and sysstat & 0xf = 3)
drop table dbo.MRDD_FINAL
This code was generated when I used the create a script to build a table from an existing table.
is there a way to check if a a table contains data or not,
The whole idea is to check if table A contains data, I need to truncate the table,otherwise I do nothing...
regards
Ali
View 4 Replies
View Related
Jul 7, 2015
I have come across a database system which isn't designed to work optimally. It is fairly large (~400GB) and performance of loading and querying is degrading (improper data types, fragmented indexes, non unique clustering key and other problems). So, I have quite a task in front of me, but I am up for the challenge. I figure this is not a unique situation, many of us would have come across this before. I have done this before too, but only for smaller databases, some of the operations here I expect to take a couple of hours or more to complete (depending on load/infrastructure speed etc, I know).
My plan is thus:
+ Take a full backup of the database
+ Set the recovery model of the DB to simple
+ Drop non clustered indexes
+ Drop clustered indexes
+ Remove PKs (wrong data types, too large!)
+ Narrow data types (add new column, update column in batches to old value, rename new column to old column)
+ Add PKs, which will create clustered indexes automatically based on PK ID
+ Create non clustered indexes
+ Run a SHRINKDB (normal operations I would never do this, but this is a special case, ensure log file is truncated to a logical size especially after all those table modifications...)
+ Set the recovery model of the DB to Full
+ Ensure everything works OK or better
View 9 Replies
View Related
Nov 13, 2006
The DTS Task Copy Server Objects is PAINFULLY slow.
The Copy Table Wizard is fast but generates an unmanagable DTS and does not bring over the indexes.
Any tips or tricks to copy tables, data and indexes and a reasonable speed?
Thanks,
Carl
View 1 Replies
View Related
Jan 16, 2006
I would like to create an index on a table from the Database Explorer.
I am using Microsoft Visual C# 2005 Express Edition.
I downloaded Microsoft SQL Server Management Studio Express with the intention of using it to create an index, but it does not seem to find the database that I created from the Database Explorer.
Thanks!
View 1 Replies
View Related
