Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Manually Grow w/Multi files.


Manually Grow w/Multi files.

Author
Message
digdave7
digdave7
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 298
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
SQLQuest29
SQLQuest29
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 4310
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 !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor :-)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47090 Visits: 44340
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, 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


digdave7
digdave7
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 298
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.
SQLQuest29
SQLQuest29
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 4310
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 !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor :-)
paul.knibbs
paul.knibbs
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1972 Visits: 6211
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?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47090 Visits: 44340
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, 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


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47090 Visits: 44340
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, 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


digdave7
digdave7
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 298
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47090 Visits: 44340
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, 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