<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Manually Grow w/Multi files.
Manually Grow w/Multi files.
Rate Topic
Display Mode
Topic Options
Author
Message
digdave7
digdave7
Posted Wednesday, June 23, 2010 5:31 PM
Grasshopper
Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 20,
Visits: 242
We have a huge database with auto grow on, I am trying to setup a job to manually grow the database because the databse is always growing and locking things out. (not my setup)
Because of the size, there are two database files everytime I execute a command to grow the db I get this error:
Msg 1842, Level 16, State 1, Line 1
The file size, max size cannot be greater than 2147483647 in units of a page size. The file growth cannot be greater than 2147483647 in units of both page size and percentage.
Searching online showed nothing, only 3 or 4 pages from the net, most errors related to Sharepoint.
from sp_spaceused: The database_size: 29427824.94 MB unallocated space:506.73 MB
and from the sysfiles tables:
Size Grow Name:
2147481600 0 VSD
4380792 12800 VSD_log
1614899200 12800 VSD2
What does this mean, and how can I grow a database this large through a TSQL statement..?
Thanks,
-David
Post #942145
SQLQuest29
SQLQuest29
Posted Wednesday, June 23, 2010 7:29 PM
Say Hey Kid
Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 713,
Visits: 2,974
Hello,
Can you let us know what are you trying to achieve by growing db ? What TSQL are you executing?
There is an option for growing your db in chuncks of MB or as percentage.
Let us know to help you better.
Cheers !
HTH,
Cheers !
"Never take life too seriously, nobody gets out of it anyways !
When your love and skills unite, expect a masterpiece !"
Post #942166
GilaMonster
GilaMonster
Posted Thursday, June 24, 2010 2:04 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 38,089,
Visits: 30,383
digdave7 (6/23/2010)
Because of the size, there are two database files everytime I execute a command to grow the db I get this error:
Msg 1842, Level 16, State 1, Line 1
The file size, max size cannot be greater than 2147483647 in units of a page size. The file growth cannot be greater than 2147483647 in units of both page size and percentage.
What command are you running?
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 #942289
digdave7
digdave7
Posted Thursday, June 24, 2010 7:30 PM
Grasshopper
Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 20,
Visits: 242
I was using
ALTER DATABASE [XXXX] MODIFY FILE ( NAME = N'VSD2', SIZE = NumberBiggerThatbeforeKB )
Technically they are in filegroups, right now we can't even open the database properties because we get the conversaion failure error.
Post #942785
SQLQuest29
SQLQuest29
Posted Thursday, June 24, 2010 8:21 PM
Say Hey Kid
Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 713,
Visits: 2,974
digdave7 (6/24/2010)
I was using
ALTER DATABASE [XXXX] MODIFY FILE ( NAME = N'VSD2', SIZE = NumberBiggerThatbeforeKB )
Technically they are in filegroups, right now we can't even open the database properties because we get the conversaion failure error.
The error message itself says "Msg 1842, Level 16, State 1, Line 1
The file size, max size cannot be greater than 2147483647 in units of a page size. The file growth cannot be greater than 2147483647 in units of both page size and percentage."
You are trying to grow the file size greater than 2147483647 which is a limitation.
Ref: http://www.sql-server-performance.com/faq/max_size_cannot_be_greater_than_2147483647_p1.aspx
HTH,
Cheers !
HTH,
Cheers !
"Never take life too seriously, nobody gets out of it anyways !
When your love and skills unite, expect a masterpiece !"
Post #942787
paul.knibbs
paul.knibbs
Posted Friday, June 25, 2010 1:14 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 1,272,
Visits: 4,309
If my calculations are correct, that means the file size limit is 16Tb (assuming 8kb pages). If that's the case, how has the OP's database file ended up at a smidgeon over 28Tb?
Post #942841
GilaMonster
GilaMonster
Posted Friday, June 25, 2010 1:24 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 38,089,
Visits: 30,383
digdave7 (6/24/2010)
I was using
ALTER DATABASE [XXXX] MODIFY FILE ( NAME = N'VSD2', SIZE = NumberBiggerThatbeforeKB )
With exactly what value for NumberBiggerThatbeforeKB ?
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 #942843
GilaMonster
GilaMonster
Posted Friday, June 25, 2010 1:27 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 38,089,
Visits: 30,383
digdave7 (6/23/2010)
from sp_spaceused: The database_size: 29427824.94 MB unallocated space:506.73 MB
Is that a correct output? Is the sum total of all the database files 29 427 824 MB? (29 427 GB, 29.4 TB)
If not, please run DBCC UPDATEUSAGE and see if the output of sp_spaceused changes?
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 #942844
digdave7
digdave7
Posted Tuesday, June 29, 2010 7:53 PM
Grasshopper
Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 20,
Visits: 242
Hi Gail,
I ran
ALTER DATABASE [VSD] MODIFY FILE ( NAME = N'VSD', SIZE = 29864824 MB )
and this is the error again:
Msg 1842, Level 16, State 1, Line 1
The file size, max size cannot be greater than 2147483647 in units of a page size. The file growth cannot be greater than 2147483647 in units of both page size and percentage.
Oddly Enough the max size is close to the size of the first database file.
2147481600 VSD
1669644800 VSD2
Post #945151
GilaMonster
GilaMonster
Posted Wednesday, June 30, 2010 1:04 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 38,089,
Visits: 30,383
digdave7 (6/29/2010)
Hi Gail,
I ran
ALTER DATABASE [VSD] MODIFY FILE ( NAME = N'VSD', SIZE = 29864824 MB )
29 Terabytes? That's what the alter database you have there sets the file size to. Is that really the size that you want the file?
The maximum size of a database file is 16 terabytes, so I would fully expect that alter database to fail. If you really do have a database that's many terabytes in size, you'll have to add more files to the existing filegroups rather than grow existing files, as the hard limit on the size of a single file is 16 TB (16777216 MB)
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 #945239
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
