SQL Server Admin 2014 :: Possible To Find Table Size And In That Table Each Row Size
Jun 10, 2014It is possible to find table size and in that table each row size.
View 4 Replies
ADVERTISEMENT
SQL Server Admin 2014 :: Table Size Difference In Two Database
Jul 20, 2015I am having an issue in determining the correct size of a table.
I have a tableA in some DB on transaction server (Enterprise Edition), this table is being replicated in reporting server DB (Standard edition).
When I check the space used by this table in both the databases i see noticeable difference.
I am using EXEC sp_spaceused 'tableA' to determine the space.
Transaction Server
------------------------------------------------------------------------------
name rows reserveddata index_size unused
TableA1439999 695416 KB507048 KB182912 KB 5456 KB
Reporting Server
-------------------------------------------------------------------------------
name rows reserveddata index_size unused
TableA1439999 656904 KB483664 KB172680 KB 560 KB
So I wanted to know what could be the possible reasons for this difference ?
SQL Server 2014 :: Sudden Increase In Table Size?
May 5, 2015I save Table size and recs. no every day. and check it some days.
...
insert into @t
exec sp_msforeachtable 'exec sp_spaceused ''?'''
...
But Today I saw sudden increase size in a table. about 128 MB in a day. (Average Growth fro this table was 4 or 5 MB in a day)This growth was for Only 4222 Records. While for more number of records (about 7000) in yesterday we had only 2 MB GRowth!
This Table information (Now):
sp_spaceused 'Table1'
Result:
name ---Rows --reserved --data
Table1--1021319--460328 KB --283104 KBI Try to gess The reason. I copy These new records to another table.But The result was more strange : on new table the size of these record was : < 1 MB I copied All records to another table . The size was : 148 MB (while this is 283 MB in my real database)
SQL Server Admin 2014 :: 1MB Datafile Growth Size?
Aug 14, 2014I'm aware of the issues with sizing your logfile growth size too low (causing too many VLFs, etc). But I haven't seen much about the datafile side of it.
Are there any benchmarks specifically on setting datafile growth so low (on databases 1-100Gb in size)? Are there circumstances in well utilized servers where that might be warranted?
SQL Server Admin 2014 :: Modify Size For Datafile Clarification
Mar 25, 2014From BOL, I see these remarks with respect to the MODIFY FILE subcommand (my underline added):
Initializing Files
By default, data and log files are initialized by filling the files with zeros when you perform one of the following operations:
Create a database
Add files to an existing database
Increase the size of an existing file
Restore a database or filegroup
Which leads me to believe that expanding the size of a datafile will also wipe out (my definition of 'initialize') any existing data within that file.
I may be misunderstanding 'initialize', because when I tested it out, I found this wasn't the case - my table data written to the file was still there after a resize.
Need to clarify to what degree I'd be taking a risk by increasing the file size on a datafile which already has data in it.
SQL Server Admin 2014 :: Log File Size And Log Space Used (Percentage)
Jul 7, 2015While 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?
SQL Server Admin 2014 :: Maximum Size Of DB Hosted On Cloud Environment?
Feb 28, 2015what is the maximum size of a DB hosted on SQL 2012 cloud environment?
View 3 Replies View RelatedSQL Server Admin 2014 :: 10gb Size Of Index Rebuild When Space Left Overall 5gb
Mar 2, 2015I have 10 Gb index and disk space only left 5gb .
How can i rebuild index ?
SQL Server 2008 :: Way To Check To Find Number Of Rows And Size Of A Table
Apr 29, 2015How can we monitor the all tables in all databases and send notifications to the team.Is there a way to check to find the no of rows and size of a table last month and find out growth % now
View 4 Replies View RelatedFind Table Size
Jul 23, 2005Env: SQL Server 2000The following sql stmt seems to find a particular table's sizeprogrammatically:select top 1 [rows],rowcntfrom sysindexeswhere ID = object_id('aUserTable')and status = 0and used > 0However,a) I'm not 100% sure of its consistency;b) Both [rows] col and [rowcnt] col seems to produce same data, whichone is supposed to be more accurate (or more up to date)?TIA.
View 2 Replies View RelatedTable Size - Trying To Find Out Space Used
Jul 8, 2002I'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.
How To Find Column Size In A Table?
May 7, 2008
Is there any way to get size of the individual column in a table?
I know we can use sp_spaceused to get the size of the table. But my question is diiferent. I have a table with 50 columns and approx 2 million rows in it. I wanted to know which column is taking most of the space.
Thanks
SQL Server Admin 2014 :: Insert A Row To A Table Based On Table Values?
Jun 10, 2015Here is my table:
My question is: How can I insert a row for each unique TemplateId. So let's say I have templateIds like, 2,5,6,7... For each unique templateId, how can I insert one more row?
SQL Server Admin 2014 :: Few Record Loss In Table Primary Key Where Same Records Exists In Foreign Key Table?
Jun 21, 2015Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.
View 3 Replies View RelatedTable Size And Database Size
Mar 2, 2008Hi,
i use this script that show me the size of each table and do the sum of all the table size.
SELECT
X.[name],
REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],
REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],
REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],
REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],
REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]
FROM
(SELECT
CAST(object_name(id) AS varchar(50)) AS [name],
SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows],
SUM(CONVERT(bigint, reserved)) * 8 AS reserved,
SUM(CONVERT(bigint, dpages)) * 8 AS data,
SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size,
SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused
FROM sysindexes WITH (NOLOCK)
WHERE sysindexes.indid IN (0, 1, 255)
AND sysindexes.id > 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY sysindexes.id WITH ROLLUP) AS X
ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup.
example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when
i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
THX
SQL 2005 Resize Initial Log Size: MODIFY FILE Failed. Specified Size Is Less Than Current Size.
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?
SQL Server Admin 2014 :: Add New Column To A Table
Aug 24, 2015I am new in SQL Server, What to check ,what action do i need to take while adding a new column to a table.
View 5 Replies View RelatedSQL Server Admin 2014 :: Backup Of Indexes For A Particular Table?
Aug 9, 2014how to take backup of indexes for a particular table.
View 1 Replies View RelatedSQL Server Admin 2014 :: Backup Of Indexes For A Particular Table
Aug 9, 2014How to take backup of indexes for a particular table.
View 5 Replies View RelatedSQL Server Admin 2014 :: TDE Table Data Encryption?
Jun 8, 2015I'm having problems with the following code:
--DROP MASTER KEY
--GO
USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pass@word1';
GO
USE master;
[code]....
What am I missing? What do I have to do if I get in a situation where I need to back out and start over?
[URL]
SQL Server Admin 2014 :: Identify Who Dropped A Table
Jun 30, 2015I'm getting a replication error that an object (Table) was not found. Any script that can capture this information?
View 3 Replies View RelatedSQL Server Admin 2014 :: Filegroup By Table Category
Oct 14, 2015I'm being asked to create multiple filegroups for a new database based on the table type, transaction, lookup, misc... From what i'm reading this doesn't make sense. I'm reading either large tables get file groups, nonclustered indexes when they are about the same size of the data, or a few other reasons...
First of all, we are talking about the same disk (please don't ask me about how it is configured) and I'm not sure yet if restoring separate file groups is even going to be necessary.
So here are my questions (beyond, the test and see what happens) because in the end I'm going to probably have to do what i'm told. So this is for my professional knowledge.
1. Does file groups separated by table type make sense?
2. Should you put tables that are queried often together in the same or different file groups.
3. I'm pretty sure you can't restore single file group for write access, am I correct?
Limiting Size Of A Table In SQL Server
Aug 14, 2004hi,
im doing network monitoring app where basically i run a checks on servers every few minutes and log the data to a table. Naturally the table can get big, quite quickly. What I want is to be able to overwrite the table data at the start of each new day. Alternatively, rollup the data into a daily or weekly packets and then overwrite table data. How do i do this?
SQL Server Table Size /Fragmentation
Jun 8, 2000Hi!,
I have a process(Peoplesoft) in SQL Server which takes long time to finish. I am looking for is there any way to find out fragmentation of the table to defagment it or is there any way to allocate the size for particular table. Couple of users are running the process at same time but as SQL Server has table/page locks it locks and releases after the job is done. Can i make it row level lock by executing sp_tableoption procedure. I would appreciate for all your suggeations.
Thanks,
Mohana
SQL Server Admin 2014 :: Replicating Data To A Table Via A View
Aug 11, 2014I am trying to replicate data from a view in the publisher to a table in the subscriber (transaction replication). I do not need the view's base table, or the view itself, replicated to the subscriber. I only want to data from the view to feed a table in the subscriber.
Is this possible?
Running SQL Server 2008 R2 Enterprise.
SQL Server Admin 2014 :: Heap Table Fragmentation Is Higher Than 50
Dec 1, 2014I have bunch of heap tables and the fragmentation seems to be high, i am not sure whether i shall add index for them, as these tables are inserted and updated every day.
View 4 Replies View RelatedSQL Server Admin 2014 :: Restoring A Single Table From Backup
Dec 12, 2014With all the new functionality, can 2014 now restore a single table from a standard backup without using any third party tools? I have looked, but can't see this listed as a feature (though that doesn't mean it's not there, maybe I've just missed it).
View 6 Replies View RelatedSQL Server Admin 2014 :: Master Table With Insert Trigger
Feb 4, 2015I have a master table with after insert trigger on it.. When record is inserted into master table, the trigger fires and is captured in the backoffice table. In case the trigger fails, my record is neither in the master table nor in the back office table..
Is there anyway to capture the record either in the master table or in a separate table.
SQL Server Admin 2014 :: List Of Users That Accessed A Table
Jul 22, 2015I'm trying to find out what tables are being used in a Database.
I don't want the last User but the User and the Dates.
I have a script that return the last user but that is not going to work.
The following script returns the last user but not all users and the Login Name:
ITH LastActivity (ObjectID, LastAction) AS
(
SELECT object_id AS TableName,
last_user_seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
[Code] .....
SQL Server 2008 :: Estimate Size Of A Table
Feb 4, 2015I have a table like below,
CREATE TABLE Student
(
Id BIGINT not null
,Name NCHAR(20) not Null
,Branch NVARCHAR (64) null
)
The table contains : 100000 rows .
1)Number of rows in a data page
2)Total number of pages required for the table
3)Total Table size in KB or MB
4)Total file size in Kb or MB
SQL Server 2008 :: Table Size After Reindex
Feb 22, 2015I'm trying to understand what is happening to one large table.
In a DB SQL 2008R2, I'm trying to track a rapidly increasing DB size. It's due to one table recently added.
Despite the table's no rows increasing its size reduces after a scheduled re-index.
I've recorded the space used by this table by recording EXEC sp_spaceused 'tableName'
---
NoRows reserved data index_size unused
128864512300384 KB 2290928 KB9432 KB24 KB AFTER reindex
128864515406232 KB 5366184 KB39280 KB768 KBBEFORE reindex
N.B. The only thing I'm aware of happening in the time period is a reindex as part of scheduled task. I could be missing something else happening.
The table has only one Index the PK which is clustered, No Fill factor is specified. The Server Default fill factor =0.
I read fillfactor=0=100 Will always try and fill the pages so space used will be minimised?
After running the reindex the index fragmentation is v.low. I've not recorded the fragmentation before reindex.
I can see the Data is not added in Clustered index order.
SQL Server 2012 :: Getting Size Of Filtered Table?
Jun 18, 2015I would like to get the size of the tables in my database BUT as follows:
SIZE OF (SELECT * FROM myTable WHERE Section = 1)
...what is the fastest way to do it?
DB Engine :: How To Reduce Size Of Server Table
Jan 20, 2012I have a table with 99.9% of unused size. How do I reduce the size?I have tried those commands below but they do not work.
ALTER INDEX [VBDATA~0] ON qa2.VBDATA REBUILD
DBCC CLEANTABLE (QA2,"qa2.VBDATA", 0)
WITH NO_INFOMSGS;
GO
ALTER INDEX ALL ON qa2.VBDATA REBUILD