Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

Table space usage 2 Expand / Collapse
Author
Message
Posted Wednesday, November 16, 2011 1:51 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:13 AM
Points: 995, Visits: 981
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

Post #1207117
Posted Wednesday, November 16, 2011 2:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 6,020, Visits: 8,289
SanDroid (11/16/2011)
So now I am confused again.
Are the 47% that selected this answer to the question Right or Wrong?

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
Post #1207136
Posted Wednesday, November 16, 2011 2:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 6,020, Visits: 8,289
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.

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
Post #1207137
Posted Wednesday, November 16, 2011 2:16 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
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?

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...

Post #1207138
Posted Wednesday, November 16, 2011 2:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 6,020, Visits: 8,289
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... )



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1207144
Posted Wednesday, November 16, 2011 2:36 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 11:34 AM
Points: 985, Visits: 1,826
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!
Post #1207150
Posted Wednesday, November 16, 2011 2:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 6,020, Visits: 8,289
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
Post #1207153
Posted Wednesday, November 16, 2011 2:49 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:13 AM
Points: 995, Visits: 981
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
Post #1207156
Posted Wednesday, November 16, 2011 2:53 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:13 AM
Points: 995, Visits: 981
and I am sorry about your mater db
Post #1207159
Posted Wednesday, November 16, 2011 2:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 39,977, Visits: 36,339
Edit: Whatever...


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1207163
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse