Transact SQL :: Manage Max Table Storage Space In Case Of Excess Data (size In GB)
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
ADVERTISEMENT
Aug 17, 2006
Dear all,
In my current database design, there is one table (PState) which has a Primary Key (int) and a few other fields.
During development, a pattern started to arise; for certain rows in PState, I wanted to specify an additional set of columns (over 10 of them with quite large lengths) for each row in PState. However, as these additional columns would only be required in approximately 20% of the rows of PState, there would be plenty of NULL values in PState if I would make this table wider than necessary. So, I decided to create a separate table with those optional columns (PStateWFI). In order to attach these additional columns in PStateWFI to PState in the cases they were needed, I would obviously have to create a Foreign Key constraint on the Primary Key of PStateWFI so that these optional rows would know which row in PState they would belong to.
However, the problem with this approach is that one could define multiple rows in PStateWFI referring to the same row in PState, which would not make sense. Thus, a UNIQUE index constraint added to the constrained ID column in PStateWFI would make sense to ensure that there could only be one set of optional columns added to each row in PState. But now, when adding the UNIQUE index, the FK constraint started to appear as a bidirectional key link in the Diagram; hence, new entries in PState would have to meet a FK constraint based on PStateWFI, which was not intended.
Hence, I had to create a quite awkward design to enforce the constraints:
1. The PState table has a Primary Key (PState.ParticleID, int, Identity Specification: Yes)
2. The PStateWFI table has a Primary Key (PstateWFIID, int, Identity Specification: Yes)
3. PStateWFI has field "PStateID" which has a FK constraint to PState.ParticleID (which is a one-way constraint operating in the correct way and does not constrain insertions in PState)
4. PStateWFI has an additional column ParticleIDIndex which has a UNIQUE Index attached to it.
5. There is a check constraint on PStateWFI enforcing PStateWFI.ParticleIDIndex = PStateWFI.ParticleID.
Although this structure does the job, it makes it necessary to add a redundant column in PStateWFI by duplicating the PStateWFI.ParticleID into PStateWFI.ParticleIDIndex, since I can't create a UNIQUE index on PStateWFI.ParticleID without constraining the PState table as well. So, insertions into this table would have to insert the same value into two columns. Not a big deal, but appears slightly ugly.
Basically I'd hope someone could explain why a bidirectional FK constraint has to be enforced on the primary key table in a relationship when the constrained column in the primary key table has a UNIQUE index attached on it. I have a few other cases where the above approach would benefit from a more clear structure.
Thanks in advance for any advice.
View 6 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
Jul 8, 2015
i've a server which is reside in remote area. my database store in drive E where disk capacity is 1000 GB and DB size reached 800GB , now disk free space showing 10 GB only . how to manage space.couple of week back i've tried to shrink the database but it is not completed successfully and since the time db size drastically increase. now i'm sitting in corner.can we perform partial shrink only on MDF. so that we can manage the space (starting from 10 gb only)
View 33 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
Jul 24, 2015
As I understood, if SPARSE is used on a column, which have many NULL marks, then the storage could be efficently used (we need less spaces to save NULL marks, hence a table which has many NULL marks with SPARSE property needs less storage than the same table, but without SPARSE. I created two table as follow:
/******* Table with Sparse ******/CREATE TABLE Sprstb(
unsprsid INT IDENTITY(1,1) NOT NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Tel int NOT NULL,
adress nvarchar(60) SPARSE NULL)/***** Table without Sparse*******/CREATE TABLE Unsprstb(unsprsid INT IDENTITY(1,1) NOT NULL,Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Tel int NOT NULL,
address nvarchar(60) NULL)
I have populated the Sprstb with 5 Milion records. It needs 509,961 MB storage. Then I have copied this table into Unsprstb
SET IDENTITY_INSERT [dbo].[Unsprstb] ON
Insert [dbo].[Unsprstb](unsprsid,Firstname,Lastname,Tel,adress)
SELECT unsprsid,Firstname,Lastname,Tel, adress FROM [dbo].[Sprstb]
SET IDENTITY_INSERT [dbo].[Unsprstb] OFF
The Unssprstb need only 466,031MB !
That means the Table with SPARSE column need more storage, Why?
By the way, in table Sprstb column address has 1666198 Null mark (from 5000000)
View 5 Replies
View Related
Oct 5, 2006
Hello, everybody,
I am in need of storing long pieces of text in database, length between 2000 and 4000 characters (nvarchar limit).
If I have a column in a table:
my_text nvarchar(2000)
does this mean that in database the size used will always be 2000*2=4000 bytes at least (+ rounding), independent on what the real length of the text stored is?
Anoter alternative - storing texts in files is expected to become unmanageable very soon, so I would like to keep to the db.
Or, would it be reasonable to have such a table:
my_text_1 nvarchar(500),
my_text_2 nvarchar(500),
my_text_3 nvarchar(500),
my_text_4 nvarchar(500)
Or declaration already means the space for a column is reserved even if it is nullable?
My DBs are MS SQL Server 2000/2005.
View 2 Replies
View Related
Sep 19, 2007
HI All
how to calculate the disk storage space of the database from the front using asp.net
Can anyone give suggestion
Thanks
View 6 Replies
View Related
Oct 27, 2015
I want to alter my all tables to change the name of all columns.
I need this because all column names were created with space and I want to remove the space for future work .
For example – In Table Customer there is a column name [Cust_Id ] and I want to change it with [Cust_Id]
View 11 Replies
View Related
Sep 14, 2015
I have a complex SSIS package which processes excel files, does some validation, fires off some SSRS reports which produce excel files, and finally sends out emails to groups of people with the appropriate file attached.It's been working fine, until recently. Now, when it reaches the send mail task, it raises an error:
Error: An error occured with the following error message: "Could not load file or assembly 'System.web, Versio 4.0.0.0, culture=neautral, publickeytoken= b0f5f7f11d50a3a' or one of its dependancies. Not enough storage space is available to process this command. (Exception from HRESULT: 0x80070008)".
Further down the report comes
Microsoft.SqlServer.Dts.Tasks.SendMailRask.SendMailTask, Microsoft.SqlServer.SendMailTask, Version=11.0.0.0, culture=neutral, PublicKeyToken=89845dcd8080cc91
This process completes normally when run in Visual Studio. It fails when run from SQL Server Agent.SQL Server Agent is currently running under my user credentials.
View 14 Replies
View Related
Jul 8, 2015
I have a field in a table that contains addresses e.g
15 Green Street
5F Brown Steet
127 Blue Street
1512 Red Road
I want to output the numbers into one column and the address to another column as i need to produce a report that only shows streets and roads but no numbers.
So basically no matter how many characters before the first space which can be numbers or letters i want these output into two columns.
View 6 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
Jun 30, 2015
If I make a search for instance
"Candy" in where statement I retrieve 12 hits however if I write "candy" I retrieve 0 hits.
Is it possible to make the database or table to be case insensitive?
View 2 Replies
View Related
Aug 22, 2007
I need to generate a report of DTS package results, i. e. succeed, fail, error, etc. What tables is information of this type stored for SQL 2000?
winniemax
View 3 Replies
View Related
May 4, 2015
I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?
View 7 Replies
View Related
Feb 12, 2008
Hi All ..
I have 2 disk drive
C and D
The SQL Server data and log files are stored in D: drive
I have only 575 MB free spaces in C: drive
I have a Sql data base name StoresDB and there is a table name CustTBL
There are almost 6555500 rows in that table
My issues are whenever I am querying the CustTbl from Query Analyzer
(Select * from CustTbl)
The C: space decreased to 40 kb
And the same time,I am not getting all the rows in the query result
In Query analyzer Message Tab it is showing (6555151 row(s) affected)
But In the Grid Tab it is showing Grid#1 :6318847 rows or sometime less rows....
What is the reason to for these two issues?
I have updated the index and statistics for CustTbl TABLE but not got any changes
..
When I closed the query window the C: drive again showing 575 MB free space
View 8 Replies
View Related
Apr 4, 2007
How can I query (using TSQL) the data space used by a table in SQL Server 2005?
This is possible using SQL Serve Management studio. I can right click on table name and check the proeprties. But I want to write a TSQL script to check disk space used by all the individual tables in the database. How can I do that?
View 3 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
Mar 5, 2014
I am looking for a function or way to return only results which does not include appended characters to order numbers.
For instance, below is a list of order numbers. I only want the order number that is SO-123456
OrderNumbers
SO-123456
SO-123456-01
SO-123456-2
SO-123457
SO-123457-1
SO-123457-02
SO-123458
I would like my query to only show the below results
SO-123456
SO-123457
SO-123458
What functions or query methods could achieve this?
I was hoping for something similar to RTRIM but that is only specific to white space.
View 9 Replies
View Related
Dec 11, 2007
Disk space is increasing at an alarming rate (about 500MB a day). Shrink of database seems to have no effect. It is a queueing table so data is inserted then a few minutes later it gets processed & deleted.
Here is info about the problem:
sp_spaceused ImporterModuleQueue
name rows reserved data index_size unused
------------------- ----------- ------------------ ------------------ ------------------ ------------------
ImporterModuleQueue 30 9469432 KB 9468280 KB 32 KB 1120 KB
This is about 9 GB
select sum(len(QueueContent)) from ImporterModuleQueue
49744918
select sum(len(QueueErrors)) from ImporterModuleQueue
43529
This is about 50 Mb
CREATE TABLE [dbo].[ImporterModuleQueue](
[ImporterModuleQueueId] [int] IDENTITY(1,1) NOT NULL,
[ImporterModuleId] [int] NOT NULL,
[StartedDateTime] [datetime] NULL,
[FinishedDateTime] [datetime] NULL,
[QueueContent] [varchar](max) NOT NULL,
[CreatedDateTime] [datetime] NOT NULL,
[QueueErrors] [varchar](max) NULL,
[QueueSourceId] [int] NOT NULL,
[QueueStatusId] [int] NOT NULL CONSTRAINT [DF_ImporterModuleQueue_IsProcessed] DEFAULT ((0)),
CONSTRAINT [PK_ImporterProcessQueue] PRIMARY KEY NONCLUSTERED
(
[ImporterModuleQueueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--
dbcc opentran
No active open transactions.
--
DBCC SHOWCONTIG scanning 'ImporterModuleQueue' table...
Table: 'ImporterModuleQueue' (2030070418); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 14
- Extents Scanned..............................: 10
- Extent Switches..............................: 9
- Avg. Pages per Extent........................: 1.4
- Scan Density [Best Count:Actual Count].......: 20.00% [2:10]
- Extent Scan Fragmentation ...................: 80.00%
- Avg. Bytes Free per Page.....................: 5714.1
- Avg. Page Density (full).....................: 29.40%
View 4 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
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
Feb 11, 2015
I have a table 300+GB. it holds 10 years of Data. I need to delete 5 years of data and put it to another server so I can have more space.
If I delete 5 years of data, Transaction log gets so huge and size of the database even gets bigger because of the .ldf file which even gets bigger! I think I can shrink the log file and the data file. Is this the best way to do it?
View 8 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
Jun 10, 2014
It is possible to find table size and in that table each row size.
View 4 Replies
View Related
Jun 17, 2003
Hi,
My database is case insensitive. However, the application is case sensitive for the data in the table.
I need to find out the data from the table where the data is stored in lower case.
Here is the sample data:
Table: inv_manager
column: sku varchar(5)
Data: 1134X
1135x
1123a
b145Y
I just need query to return row 2, 3, 4.
Here is my query:
select sku from inv_manager where sku like lower('%[a-z]%')
The problem is it returns all the rows from the table including first one.
How can I return just the data with lower case ?
Thanks
View 2 Replies
View Related
Sep 28, 2015
I've been quite excited about SQL Server MDS that should allow non-IT staff to easily maintain data. However maintaining data that have many-to-many relationships seems to be quite a pain. I believe the best way is:
Open up your MDS web interfaceGo to entities > product (for example)Add a new member and fill the details Click "product parts" in the bottom right "Related Entities" partAdd a new memberTry and find the product you just created from the dropdownlistSelect the first part and click OKAgain try and find the product you have created from the dropdownlistSelect the second part and click OKRepeat...Close the tab on your browser and finish your product entity.How I wish it worked:
Open up your MDS web interfaceGo to entities > product (for example)Add a new member and fill the detailsCheck a checkbox for each part visible under "product parts" in the bottom right "Related Entities" partFinish your product entity.
View 3 Replies
View Related
Jul 2, 2001
I 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
View 4 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 20, 2006
We are using SQL Server 2000 Standard ed, sp4 on Server 2000 Advanced.We have one table that is three times as large as the rest of the database.Most of the data is static after approximately 3-6 months, but we arerequired to keep it for 8 years. I would like to archive this table (A), butthere are complications.1. the only way to access the data is through the application (they areimages produced by the application-built on Power-Builder)2. there are multiple tables refrencing this table and vise-versa3. we restore the entire db to two other servers for testing and trainingregularly4. there might be more complications that have not been thought ofCurrently, our only plan is to setup a seperate server with a copy of this dbon it and the application. Leave only the tables necessary to access the data,and if this 'archive' works, remove from production the data from the table Aand all references to the table A from rows on the other tables.I mentioned #3 because someone mentioned a third party tool that may be ableto pull the data from the table, archive it elsewhere, and at the same time,place a 'pointer' in the table to the new storage location. The tool theymentioned only works on Oracle and we have not explored beyond that yet.I am ready to explore ideas and suggestions; I am still new to the DBA world,I am out of ideas.Thank you!--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200607/1
View 1 Replies
View Related
