Mfcttrf

How would two worlds first establish an exchange rate between their currencies

Is BitLocker useful in the case of stolen laptop?

Where does the expression "triple-A" comes from?

I see your BIDMAS and raise you a BADMIS

How to create a list of dictionaries from a dictionary with lists of different lengths

Are programming languages necessary/useful for operations research practitioner?

How do I politely hint customers to leave my store, without pretending to need leave store myself?

CBP interview, how serious should I take it?

Are there any space probes or landers which regained communication after being lost?

Two different colors in an Illustrator stroke / line

Are Democrats more likely to believe Astrology is a science?

Webpage with a preload animation using JavaScript setTimeout

Is there a basic list of ways in which a low-level Rogue can get advantage for sneak attack?

Writing a love interest for my hero

How was Carlo's plan supposed to work?

Should I use my toaster oven for slow roasting?

Do any aircraft carry boats?

On the origin of "casa"

Why are some Mac apps not available on AppStore?

Do Milankovitch Cycles fully explain climate change?

Are there take-over requests in aviation?

How important is the DUP support (at the moment) to the current ruling party in UK?

Is there a sentence that begins with “them”?

Why should I always enable compiler warnings?

SQL Server table with 4,000,000 rows is 40GB

Slow Performance Inserting Few Rows Into Huge TablePAD_INDEX and FILLFACTOR on clustered Identity indexParent-Child Tree Hierarchical ORDERMigrating from SQL Server to MySQL using MySQL Workbench toolgood strategy for big tables and archiving in sql server 2012Is Primary Key Non Clustered recommended?Update Table from SP in SSISdeteriorating stored procedure running timesWhat are the current best practices concerning varchar sizing in SQL Server?SQL Server 2012 Aggressive Over-Indexing when i run sp_BlitzIndex

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

3

I have been trying to figure out why our production database has grown so much recently and have found the cause to be an audit table. We are going to archive the table to fix the problem but I just wanted to check if the size of the table looked normal for a large SQL server table like this.
It has 4,000,000 rows with the following structure...

Name Type Nullable
AuditLogId bigint no
UserName nvarchar no
TimeOfChange datetime no
ObjectName nvarchar no
ChangeName nvarchar no
RecordId int yes
OriginalValues nvarchar yes
ResultingValues nvarchar no

With a primary key on the AuditLogId

ALTER TABLE [dbo].[AuditLog] ADD CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED 
(
 [AuditLogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

There is also a foreign key to the record that was updated on RecordId.
The nvarchar fields seem to have up to 12,0000 characters with an average of around 8000 characters.
The table is currently 40GB in size, does that seem right? I can provide more information on the table if that question can’t be answered with what I have posted above.

Thanks

sql-server performance database-size

share|improve this question

asked 9 hours ago

HoodyHoody

13533 bronze badges

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Have you checked index fragmentation? Does this table have lots of inserts and deletes? If you are not maintaining the indexes and lots of data gets inserted and later deleted, the index structure can become rather lopsided. Rebuilding the indexes as a whole to properly distribute the data could be helpful. I don't see any index information in your post so I didn't post this as an answer in case this is not your problem.
  
  – G.Smith
  9 hours ago
  
  

  
  
  
  

add a comment | 

3

I have been trying to figure out why our production database has grown so much recently and have found the cause to be an audit table. We are going to archive the table to fix the problem but I just wanted to check if the size of the table looked normal for a large SQL server table like this.
It has 4,000,000 rows with the following structure...

Name Type Nullable
AuditLogId bigint no
UserName nvarchar no
TimeOfChange datetime no
ObjectName nvarchar no
ChangeName nvarchar no
RecordId int yes
OriginalValues nvarchar yes
ResultingValues nvarchar no

With a primary key on the AuditLogId

ALTER TABLE [dbo].[AuditLog] ADD CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED 
(
 [AuditLogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

There is also a foreign key to the record that was updated on RecordId.
The nvarchar fields seem to have up to 12,0000 characters with an average of around 8000 characters.
The table is currently 40GB in size, does that seem right? I can provide more information on the table if that question can’t be answered with what I have posted above.

Thanks

sql-server performance database-size

share|improve this question

asked 9 hours ago

HoodyHoody

13533 bronze badges

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Have you checked index fragmentation? Does this table have lots of inserts and deletes? If you are not maintaining the indexes and lots of data gets inserted and later deleted, the index structure can become rather lopsided. Rebuilding the indexes as a whole to properly distribute the data could be helpful. I don't see any index information in your post so I didn't post this as an answer in case this is not your problem.
  
  – G.Smith
  9 hours ago
  
  

  
  
  
  

add a comment | 

I have been trying to figure out why our production database has grown so much recently and have found the cause to be an audit table. We are going to archive the table to fix the problem but I just wanted to check if the size of the table looked normal for a large SQL server table like this.
It has 4,000,000 rows with the following structure...

Name Type Nullable
AuditLogId bigint no
UserName nvarchar no
TimeOfChange datetime no
ObjectName nvarchar no
ChangeName nvarchar no
RecordId int yes
OriginalValues nvarchar yes
ResultingValues nvarchar no

With a primary key on the AuditLogId

ALTER TABLE [dbo].[AuditLog] ADD CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED 
(
 [AuditLogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

There is also a foreign key to the record that was updated on RecordId.
The nvarchar fields seem to have up to 12,0000 characters with an average of around 8000 characters.
The table is currently 40GB in size, does that seem right? I can provide more information on the table if that question can’t be answered with what I have posted above.

Thanks

sql-server performance database-size

share|improve this question

asked 9 hours ago

HoodyHoody

13533 bronze badges

Name Type Nullable
AuditLogId bigint no
UserName nvarchar no
TimeOfChange datetime no
ObjectName nvarchar no
ChangeName nvarchar no
RecordId int yes
OriginalValues nvarchar yes
ResultingValues nvarchar no

ALTER TABLE [dbo].[AuditLog] ADD CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED 
(
 [AuditLogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

share|improve this question

asked 9 hours ago

HoodyHoody

13533 bronze badges

share|improve this question

asked 9 hours ago

HoodyHoody

13533 bronze badges

asked 9 hours ago

HoodyHoody

13533 bronze badges

asked 9 hours ago

HoodyHoody

13533 bronze badges

1 Answer
1

active

oldest

votes

11

You have 4,000,000 rows and one of the columns averages 8,000 characters (16,000 bytes, I assume).

SELECT CONVERT(bigint,4000000) * /* b */ 16000 / /*kb*/ 1024 / /*mb*/ 1024;
------
61,035

If your stats are accurate, I'd expect this table to be 61 GB. (I wonder if you mean 8,000 bytes, not 8,000 characters, in which case I'd expect > 30 GB.)

Other factors include:

• data in other columns I didn't account for in the above calculation


• additional indexes


• fill factor > 0 and < 100


• fragmentation and space still occupied by deleted rows or page splits

share|improve this answer

edited 9 hours ago

answered 9 hours ago

Aaron Bertrand♦Aaron Bertrand

161k1919 gold badges322322 silver badges527527 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you Aaron, that explains the size perfectly
  
  – Hoody
  9 hours ago
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f247347%2fsql-server-table-with-4-000-000-rows-is-40gb%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

11

You have 4,000,000 rows and one of the columns averages 8,000 characters (16,000 bytes, I assume).

SELECT CONVERT(bigint,4000000) * /* b */ 16000 / /*kb*/ 1024 / /*mb*/ 1024;
------
61,035

If your stats are accurate, I'd expect this table to be 61 GB. (I wonder if you mean 8,000 bytes, not 8,000 characters, in which case I'd expect > 30 GB.)

Other factors include:

• data in other columns I didn't account for in the above calculation


• additional indexes


• fill factor > 0 and < 100


• fragmentation and space still occupied by deleted rows or page splits

share|improve this answer

edited 9 hours ago

answered 9 hours ago

Aaron Bertrand♦Aaron Bertrand

161k1919 gold badges322322 silver badges527527 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you Aaron, that explains the size perfectly
  
  – Hoody
  9 hours ago
  

  
  
  
  

add a comment | 

11

You have 4,000,000 rows and one of the columns averages 8,000 characters (16,000 bytes, I assume).

SELECT CONVERT(bigint,4000000) * /* b */ 16000 / /*kb*/ 1024 / /*mb*/ 1024;
------
61,035

If your stats are accurate, I'd expect this table to be 61 GB. (I wonder if you mean 8,000 bytes, not 8,000 characters, in which case I'd expect > 30 GB.)

Other factors include:

• data in other columns I didn't account for in the above calculation


• additional indexes


• fill factor > 0 and < 100


• fragmentation and space still occupied by deleted rows or page splits

share|improve this answer

edited 9 hours ago

answered 9 hours ago

Aaron Bertrand♦Aaron Bertrand

161k1919 gold badges322322 silver badges527527 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you Aaron, that explains the size perfectly
  
  – Hoody
  9 hours ago
  

  
  
  
  

add a comment | 

You have 4,000,000 rows and one of the columns averages 8,000 characters (16,000 bytes, I assume).

SELECT CONVERT(bigint,4000000) * /* b */ 16000 / /*kb*/ 1024 / /*mb*/ 1024;
------
61,035

If your stats are accurate, I'd expect this table to be 61 GB. (I wonder if you mean 8,000 bytes, not 8,000 characters, in which case I'd expect > 30 GB.)

Other factors include:

• data in other columns I didn't account for in the above calculation


• additional indexes


• fill factor > 0 and < 100


• fragmentation and space still occupied by deleted rows or page splits

share|improve this answer

edited 9 hours ago

answered 9 hours ago

Aaron Bertrand♦Aaron Bertrand

161k1919 gold badges322322 silver badges527527 bronze badges

SELECT CONVERT(bigint,4000000) * /* b */ 16000 / /*kb*/ 1024 / /*mb*/ 1024;
------
61,035

share|improve this answer

edited 9 hours ago

answered 9 hours ago

Aaron Bertrand♦Aaron Bertrand

161k1919 gold badges322322 silver badges527527 bronze badges

answered 9 hours ago

Aaron Bertrand♦Aaron Bertrand

161k1919 gold badges322322 silver badges527527 bronze badges

answered 9 hours ago

Aaron Bertrand♦Aaron Bertrand

161k1919 gold badges322322 silver badges527527 bronze badges