|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL 2005 unrestricted log file
Hello,
I'm running SQL 2005 on W2K3 server. When creating new database log file size gets limitation of 2097 MB automatically, despite selecting "Unrestricted file growth" option. How it can be changed? I mean that unrestricted really becomes unrestricted. Thanks, Gary Gary wrote:
> Hello, Are you sure you want to do this? It's not a good idea to enable> > I'm running SQL 2005 on W2K3 server. > When creating new database log file size gets limitation of 2097 MB > automatically, > despite selecting "Unrestricted file growth" option. > > > How it can be changed? > I mean that unrestricted really becomes unrestricted. > > Thanks, > Gary auto-grow in a production environment and with proper housekeeping and backups in place it shouldn't be necessary. Auto-growing the log can affect performance and availability. If you don't require log backups then use the Simple Recovery model to prevent excessive log growth. A new database will take on the settings of the Model database. Set unrestruicted growth by setting MAXSIZE = UNLIMITED. Example: ALTER DATABASE database_name MODIFY FILE (NAME = 'log_file_name', SIZE = 1024MB, FILEGROWTH = 100MB, MAXSIZE = UNLIMITED ); Hope this helps. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- Hello David,
Thanks for your prompt answer. I inderstand the consequences of setting autogrow to unlimited. Indeed, I need it to limited group of customers. However, the problem is that I'm unable to chage it to unlimited. The query you provided works, but doesn't really change anything. I still see the same value of 2097 MB. The model databse has this value too and i can't change it. Thanks, Gary Show quoteHide quote "David Portas" wrote: > Gary wrote: > > Hello, > > > > I'm running SQL 2005 on W2K3 server. > > When creating new database log file size gets limitation of 2097 MB > > automatically, > > despite selecting "Unrestricted file growth" option. > > > > > > How it can be changed? > > I mean that unrestricted really becomes unrestricted. > > > > Thanks, > > Gary > Are you sure you want to do this? It's not a good idea to enable > auto-grow in a production environment and with proper housekeeping and > backups in place it shouldn't be necessary. Auto-growing the log can > affect performance and availability. If you don't require log backups > then use the Simple Recovery model to prevent excessive log growth. > > A new database will take on the settings of the Model database. Set > unrestruicted growth by setting MAXSIZE = UNLIMITED. Example: > > ALTER DATABASE database_name MODIFY FILE > (NAME = 'log_file_name', > SIZE = 1024MB, > FILEGROWTH = 100MB, > MAXSIZE = UNLIMITED ); > > Hope this helps. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > > Gary,
Where do you see this value? Are you sure it isn't 2097 GB? That is the max size for a log file. And for some reason, that is the value reported by, for instance sp_helpdb (where a database file reports "unlimited, which has a max size of 32TB). Can you post the output from below (will create a drop a database named "test"): create database test GO exec sp_helpdb test ALTER DATABASE test MODIFY FILE (NAME = 'test_log', SIZE = 40MB, FILEGROWTH = 100MB, MAXSIZE = UNLIMITED ); GO exec sp_helpdb test GO drop database TEST -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Gary" <G***@discussions.microsoft.com> wrote in message news:01DDA3BB-B1B3-4168-A791-9C9BAA65DF4A@microsoft.com... > Hello David, > > Thanks for your prompt answer. > I inderstand the consequences of setting autogrow to unlimited. > Indeed, I need it to limited group of customers. > > However, the problem is that I'm unable to chage it to unlimited. > The query you provided works, but doesn't really change anything. > I still see the same value of 2097 MB. > > The model databse has this value too and i can't change it. > > Thanks, > Gary > > > "David Portas" wrote: > >> Gary wrote: >> > Hello, >> > >> > I'm running SQL 2005 on W2K3 server. >> > When creating new database log file size gets limitation of 2097 MB >> > automatically, >> > despite selecting "Unrestricted file growth" option. >> > >> > >> > How it can be changed? >> > I mean that unrestricted really becomes unrestricted. >> > >> > Thanks, >> > Gary >> Are you sure you want to do this? It's not a good idea to enable >> auto-grow in a production environment and with proper housekeeping and >> backups in place it shouldn't be necessary. Auto-growing the log can >> affect performance and availability. If you don't require log backups >> then use the Simple Recovery model to prevent excessive log growth. >> >> A new database will take on the settings of the Model database. Set >> unrestruicted growth by setting MAXSIZE = UNLIMITED. Example: >> >> ALTER DATABASE database_name MODIFY FILE >> (NAME = 'log_file_name', >> SIZE = 1024MB, >> FILEGROWTH = 100MB, >> MAXSIZE = UNLIMITED ); >> >> Hope this helps. >> >> -- >> David Portas, SQL Server MVP >> >> Whenever possible please post enough code to reproduce your problem. >> Including CREATE TABLE and INSERT statements usually helps. >> State what version of SQL Server you are using and specify the content >> of any error messages. >> >> SQL Server Books Online: >> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx >> -- >> >> Hi Gary
It does seem that you can't get away from this limit even if you specifically use unlimited in A CREATE DATABASE statement. The size is actually 2,097,152 MB which is 2TB. If you want to report this go to http://lab.msdn.microsoft.com/productfeedback/default.aspx John Show quoteHide quote "Gary" wrote: > Hello David, > > Thanks for your prompt answer. > I inderstand the consequences of setting autogrow to unlimited. > Indeed, I need it to limited group of customers. > > However, the problem is that I'm unable to chage it to unlimited. > The query you provided works, but doesn't really change anything. > I still see the same value of 2097 MB. > > The model databse has this value too and i can't change it. > > Thanks, > Gary > > > "David Portas" wrote: > > > Gary wrote: > > > Hello, > > > > > > I'm running SQL 2005 on W2K3 server. > > > When creating new database log file size gets limitation of 2097 MB > > > automatically, > > > despite selecting "Unrestricted file growth" option. > > > > > > > > > How it can be changed? > > > I mean that unrestricted really becomes unrestricted. > > > > > > Thanks, > > > Gary > > Are you sure you want to do this? It's not a good idea to enable > > auto-grow in a production environment and with proper housekeeping and > > backups in place it shouldn't be necessary. Auto-growing the log can > > affect performance and availability. If you don't require log backups > > then use the Simple Recovery model to prevent excessive log growth. > > > > A new database will take on the settings of the Model database. Set > > unrestruicted growth by setting MAXSIZE = UNLIMITED. Example: > > > > ALTER DATABASE database_name MODIFY FILE > > (NAME = 'log_file_name', > > SIZE = 1024MB, > > FILEGROWTH = 100MB, > > MAXSIZE = UNLIMITED ); > > > > Hope this helps. > > > > -- > > David Portas, SQL Server MVP > > > > Whenever possible please post enough code to reproduce your problem. > > Including CREATE TABLE and INSERT statements usually helps. > > State what version of SQL Server you are using and specify the content > > of any error messages. > > > > SQL Server Books Online: > > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > > -- > > > > Hello John,
Exactly! Does it seem to be a bug? Thanks, Gary Show quoteHide quote "John Bell" wrote: > Hi Gary > > It does seem that you can't get away from this limit even if you > specifically use unlimited in A CREATE DATABASE statement. The size is > actually 2,097,152 MB which is 2TB. > > If you want to report this go to > http://lab.msdn.microsoft.com/productfeedback/default.aspx > > John > > > > > > "Gary" wrote: > > > Hello David, > > > > Thanks for your prompt answer. > > I inderstand the consequences of setting autogrow to unlimited. > > Indeed, I need it to limited group of customers. > > > > However, the problem is that I'm unable to chage it to unlimited. > > The query you provided works, but doesn't really change anything. > > I still see the same value of 2097 MB. > > > > The model databse has this value too and i can't change it. > > > > Thanks, > > Gary > > > > > > "David Portas" wrote: > > > > > Gary wrote: > > > > Hello, > > > > > > > > I'm running SQL 2005 on W2K3 server. > > > > When creating new database log file size gets limitation of 2097 MB > > > > automatically, > > > > despite selecting "Unrestricted file growth" option. > > > > > > > > > > > > How it can be changed? > > > > I mean that unrestricted really becomes unrestricted. > > > > > > > > Thanks, > > > > Gary > > > Are you sure you want to do this? It's not a good idea to enable > > > auto-grow in a production environment and with proper housekeeping and > > > backups in place it shouldn't be necessary. Auto-growing the log can > > > affect performance and availability. If you don't require log backups > > > then use the Simple Recovery model to prevent excessive log growth. > > > > > > A new database will take on the settings of the Model database. Set > > > unrestruicted growth by setting MAXSIZE = UNLIMITED. Example: > > > > > > ALTER DATABASE database_name MODIFY FILE > > > (NAME = 'log_file_name', > > > SIZE = 1024MB, > > > FILEGROWTH = 100MB, > > > MAXSIZE = UNLIMITED ); > > > > > > Hope this helps. > > > > > > -- > > > David Portas, SQL Server MVP > > > > > > Whenever possible please post enough code to reproduce your problem. > > > Including CREATE TABLE and INSERT statements usually helps. > > > State what version of SQL Server you are using and specify the content > > > of any error messages. > > > > > > SQL Server Books Online: > > > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > > > -- > > > > > > Gary wrote:
> Hello John, No. It's a feature. If you take a look at the Maximum Capacity> > Exactly! > Does it seem to be a bug? > > Thanks, > Gary > Specifications topic in Books Online it says the maximum size of a log file is 2TB. Why do you want a log file larger than 2TB? Or are you still saying that in your case you find you are limited to 2GB rather than 2TB? -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- Hi Gary
I am pretty sure this is by design, you can always have multiple log files. The OS will only be able to cope with files of a certain size, therefore there has always been some limitation. John Show quoteHide quote "Gary" wrote: > Hello John, > > Exactly! > Does it seem to be a bug? > > Thanks, > Gary > > "John Bell" wrote: > > > Hi Gary > > > > It does seem that you can't get away from this limit even if you > > specifically use unlimited in A CREATE DATABASE statement. The size is > > actually 2,097,152 MB which is 2TB. > > > > If you want to report this go to > > http://lab.msdn.microsoft.com/productfeedback/default.aspx > > > > John > > > > > > > > > > > > "Gary" wrote: > > > > > Hello David, > > > > > > Thanks for your prompt answer. > > > I inderstand the consequences of setting autogrow to unlimited. > > > Indeed, I need it to limited group of customers. > > > > > > However, the problem is that I'm unable to chage it to unlimited. > > > The query you provided works, but doesn't really change anything. > > > I still see the same value of 2097 MB. > > > > > > The model databse has this value too and i can't change it. > > > > > > Thanks, > > > Gary > > > > > > > > > "David Portas" wrote: > > > > > > > Gary wrote: > > > > > Hello, > > > > > > > > > > I'm running SQL 2005 on W2K3 server. > > > > > When creating new database log file size gets limitation of 2097 MB > > > > > automatically, > > > > > despite selecting "Unrestricted file growth" option. > > > > > > > > > > > > > > > How it can be changed? > > > > > I mean that unrestricted really becomes unrestricted. > > > > > > > > > > Thanks, > > > > > Gary > > > > Are you sure you want to do this? It's not a good idea to enable > > > > auto-grow in a production environment and with proper housekeeping and > > > > backups in place it shouldn't be necessary. Auto-growing the log can > > > > affect performance and availability. If you don't require log backups > > > > then use the Simple Recovery model to prevent excessive log growth. > > > > > > > > A new database will take on the settings of the Model database. Set > > > > unrestruicted growth by setting MAXSIZE = UNLIMITED. Example: > > > > > > > > ALTER DATABASE database_name MODIFY FILE > > > > (NAME = 'log_file_name', > > > > SIZE = 1024MB, > > > > FILEGROWTH = 100MB, > > > > MAXSIZE = UNLIMITED ); > > > > > > > > Hope this helps. > > > > > > > > -- > > > > David Portas, SQL Server MVP > > > > > > > > Whenever possible please post enough code to reproduce your problem. > > > > Including CREATE TABLE and INSERT statements usually helps. > > > > State what version of SQL Server you are using and specify the content > > > > of any error messages. > > > > > > > > SQL Server Books Online: > > > > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > > > > -- > > > > > > > > Gary wrote:
> What edition of SQL Server? Express Edition is limited to a 4GB> The query you provided works, but doesn't really change anything. > I still see the same value of 2097 MB. > database so that might explain why the log size is constrained. Is the drive a FAT32 partition by any chance? I'm not certain but there may be additional size limitations associated with FAT32. Where do you see the figure 2097MB? Check the output of sp_helpfile rather than rely on the UI. You may find that sp_helpfile shows the maximum size specified for the log file is 2TB (2147483648 KB). According to Books Online 2TB is the maximum permitted size for a log file. If you think you need a log file size larger than 2TB then you are probably doing something badly wrong but if you wish to you can always add extra log files. Auto-growing the log at that size just has to be a really bad idea. If you've checked all the above and you still see a size of only 2097MB then you may want to open a case with Microsoft. It's normal to see the size slightly rounded up or down from that specified with ALTER but I haven't come across a case before where you can't grow over 2GB and I can't reproduce it. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- Thank you all for the valuable enquiries.
I checked it with sp_helpfile, it works out 2T. However, as it were with SQL 2000, when selecting "unrestricted" for log file growth, I anticipated to see grayed out box and not the numeric value. Now, I understand that value of 2,097,152 MB is actually means unrestricted since log file can not be larger by design. The described situation confused our customer and promoted me checking it. Thanks a lot, Gary Show quoteHide quote "Gary" wrote: > Hello, > > I'm running SQL 2005 on W2K3 server. > When creating new database log file size gets limitation of 2097 MB > automatically, > despite selecting "Unrestricted file growth" option. > > > How it can be changed? > I mean that unrestricted really becomes unrestricted. > > Thanks, > Gary Hi Gary
You may want to log a suggestion that UNLIMITED is changed to something else! If you want to suggest this go to http://lab.msdn.microsoft.com/productfeedback/default.aspx John Show quoteHide quote "Gary" wrote: > Thank you all for the valuable enquiries. > I checked it with sp_helpfile, it works out 2T. > > However, as it were with SQL 2000, when selecting "unrestricted" for log > file growth, I anticipated to see grayed out box and not the numeric value. > Now, I understand that value of 2,097,152 MB is actually means unrestricted > since log file can not be larger by design. > > The described situation confused our customer and promoted me checking it. > > Thanks a lot, > Gary > "Gary" wrote: > > > Hello, > > > > I'm running SQL 2005 on W2K3 server. > > When creating new database log file size gets limitation of 2097 MB > > automatically, > > despite selecting "Unrestricted file growth" option. > > > > > > How it can be changed? > > I mean that unrestricted really becomes unrestricted. > > > > Thanks, > > Gary  
Other interesting topics
SQL Server Enterprise Manager Error
SQL2K Function Print cpu up with SQL2005 - higher FullScans/sec, lwr IndexSearches/sec Error inserting into iSeries DB2 table with long name via SQL Server 2000 linked Server Trying to create a maintenance plan CLR integration & clustering....question MSDTC Error how to simplify what i'm doing (AS the solution?) Backup Move MS SQL 2005 Express Edition Data Path |
|||||||||||||||||||||||
