SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table space usage 2


Table space usage 2

Author
Message
Iulian -207023
Iulian -207023
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2495 Visits: 1248
After doing the calculation I was tempted to answer the queston but to be sure I ran the script and got the error:
The statement has been terminated.
Msg 1101, Level 17, State 12, Line 2
Could not allocate a new page for database 'QOTD' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.



Now the script I ran is
Creating the database:


CREATE DATABASE [QOTD] ON PRIMARY
(
NAME = N'QOTD'
, FILENAME = N'...\QOTD.mdf'
, SIZE = 2097152KB
, MAXSIZE = UNLIMITED
, FILEGROWTH = 0
)
LOG ON
(
NAME = N'QOTD_log'
, FILENAME = N'...\QOTD_log.ldf'
, SIZE = 1024KB
, MAXSIZE = 2048GB
, FILEGROWTH = 10%
)
GO

ALTER DATABASE [QOTD] SET COMPATIBILITY_LEVEL = 100
GO



Then creating the table:

USE [QOTD]
GO

CREATE TABLE dbo.DemoTable
(DemoTableKey char(10) NOT NULL,
LargeNumber bigint NOT NULL,
ShortDescription char(80) NOT NULL,
LongDescription nchar(600) NULL,
Price money NOT NULL,
Counter1 int NOT NULL,
Counter2 bigint NOT NULL,
Counter3 smallint NOT NULL,
Counter4 int NOT NULL,
AddDate datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP),
ChgDate datetime NULL,
CONSTRAINT PK_DemoTable PRIMARY KEY (DemoTableKey),
CONSTRAINT CK_LargeNumber CHECK (LargeNumber > 0)
);

GO



Now filling the 1 mil records:


; WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 lines
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 lines
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 lines
E5(N) AS (SELECT 1 FROM E4 a, E2 b) --10E+6 or 1,000,000

INSERT INTO [dbo].[DemoTable]
([DemoTableKey]
,[LargeNumber]
,[ShortDescription]
,[LongDescription]
,[Price]
,[Counter1]
,[Counter2]
,[Counter3]
,[Counter4]
-- ,[AddDate]
,[ChgDate])
SELECT ROW_NUMBER() OVER (ORDER BY N)
, 1
, 'NONE'
, NULL
, 1
, 1
, 1
, 1
, 1
-- , -- DEFAULT
, NULL
FROM E5

GO



Everyting is OK so far, all ran successfuly.
Finaly rebuilding the index:


ALTER INDEX ALL ON dbo.DemoTable
REBUILD WITH (FILLFACTOR = 100,
DATA_COMPRESSION = NONE,
MAXDOP = 1);

-- TRUNCATE TABLE dbo.DemoTable



I am not worried about the lost point, but is there anything I have missed?
One mention I did not get the error after the INSERT statement but after running the ALTER INDEX statement.

The question is great, a strong question, I would like to see more like this.

Thank you,
Iulian
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18977 Visits: 12426
SanDroid (11/16/2011)
So now I am confused again.
Are the 47% that selected this answer to the question Right or Wrong? :-P

If I had the possibility to mark their answers correct and award back points, I would. But I don't have that option. And I don't think the software even caters for the possiblity of a question where two answers are marked correct (unless you require people to identify all correct answers).

However, technically speaking they are not really correct. The message you get when running out of space in an index rebuild is different from the message I gave in the answer options (which, if I recall correctly, I copied from SSMS after forcing SQL Server to run out of space while inserting rows). But given that there is only one answer in the "out of space" area and no "none of the above" option, I do believe that those 47% should have gotten their points.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18977 Visits: 12426
SanDroid (11/16/2011)
Obviously lots of people learned today, including you. Lets have some more. It could even be worth some of the stale fruit you might recieve. Hehe

No stale fruit today, but a very interesting discussion where we all learned. I definitely took away some things to consider for my next questions! There might be errors in those as well, but I'll do my very best to make sure that they will at least be NEW errors! ;-)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
SanDroid
SanDroid
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2448 Visits: 1046
Hugo Kornelis (11/16/2011)
SanDroid (11/16/2011)
So now I am confused again.
Are the 47% that selected this answer to the question Right or Wrong? :-P

If I had the possibility to mark their answers correct and award back points, I would. But I don't have that option. And I don't think the software even caters for the possiblity of a question where two answers are marked correct (unless you require people to identify all correct answers).

However, technically speaking they are not really correct. The message you get when running out of space in an index rebuild is different from the message I gave in the answer options (which, if I recall correctly, I copied from SSMS after forcing SQL Server to run out of space while inserting rows). But given that there is only one answer in the "out of space" area and no "none of the above" option, I do believe that those 47% should have gotten their points.


I am certain Steve will correct me if I am wrong, but I think the "software" has the ability to do exactly that. Maybe you should ask him... w00t
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18977 Visits: 12426
Iulian -207023 (11/16/2011)
I am not worried about the lost point, but is there anything I have missed?

No, that was my mistake. I added the index rebuild to the question to make sure there was no fragmentation, regardless of how the data was filled. But I forgot that rebuilding an index required SQL Server to temporarily duplicate it - so during the rebuild, you need twice as much space for the table. That's a little over 3 GB.

I should have tested with the given amount of data. But I wanted to save time, so I tested with one tenth and checked that all the calculation and formulas I had in my spreadsheet exactly predicted the amount of pages used. I was then confident that my date size calculations would be correct for the full million rows as well. Which in fact they were - if I had specified the size of the data file as 3.5GB or so, there would not have been any problem.

(Funny side story - just to be sure, I just now did run the tests for the full million rows. First with a 2GB data file, then with a 3.5GB data file. To my utter surprise, the index rebuild did NOT produce an error when I tested with the 2GB data file. It took me some time before I found the cause - a missing USE statement. I now have a very bloated master database... :WhistlingSmile


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
jeff.mason
jeff.mason
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2735 Visits: 2137
Hugo Kornelis (11/16/2011)

However, technically speaking they are not really correct. The message you get when running out of space in an index rebuild is different from the message I gave in the answer options (which, if I recall correctly, I copied from SSMS after forcing SQL Server to run out of space while inserting rows).


Now, see, I didn't even check for the correct error. I played by the rules and pulled up the calculator and got row size and got your first set of numbers (page size per row) and figured out how big the table was, and looked for "error" and stopped. I didn't even check that it was the exact error you'd get. So that'd be one on me then!
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18977 Visits: 12426
jeff.mason (11/16/2011)
Now, see, I didn't even check for the correct error. I played by the rules and pulled up the calculator and got row size and got your first set of numbers (page size per row) and figured out how big the table was, and looked for "error" and stopped. I didn't even check that it was the exact error you'd get. So that'd be one on me then!

To clarify my previous reply, I think you (and everyone else who selected the error option) deserve the point. Not getting a point for a nitty gritty difference in the error message is, in my opinion, not in the spirit of the QotD. The question should test your understanding of SQLL Server, not your reading skills.

FYI, I have sent Steve a PM requesting him to award points back to everyone who chose the error option, and to change the question to specify a 3.5 GB data file (so that now the error message is no longer correct).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Iulian -207023
Iulian -207023
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2495 Visits: 1248
Thank you Hugo, now I understand better not only the sql exercise of the qotd
but also the exercise of making a good question.
Well done! I hope to see more questions like this one.

Iulian
Iulian -207023
Iulian -207023
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2495 Visits: 1248
and I am sorry about your mater db :-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228854 Visits: 46344
Edit: Whatever...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search