November 22, 2019 at 12:16 pm
For some reason I'm unable to create another log file. Ideas?
ALTER DATABASE [DBNAME] ADD LOG FILE ( NAME = N'DBNAME_log_2', FILENAME = N'G:\MSSQL10\DBNAME_log_2.ldf' , SIZE = 65536000KB, MAXSIZE = UNLIMITED , FILEGROWTH = 65536KB )
Msg 5009, Level 16, State 1, Line 1
One or more files listed in the statement could not be found or could not be initialized.
Query was cancelled by user.
November 22, 2019 at 12:46 pm
Do you actually have 65GB remaining on the drive to create the file? And, I've got to ask, are you sure the path is correct?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2019 at 12:58 pm
lol. thanks. I think I'm going to get some coffee.
November 22, 2019 at 1:10 pm
I don't think it'd a good idea to have more than one log file for a database:
https://www.sqlskills.com/blogs/paul/multiple-log-files-and-why-theyre-bad/
November 22, 2019 at 2:12 pm
I don't think it'd a good idea to have more than one log file for a database:
https://www.sqlskills.com/blogs/paul/multiple-log-files-and-why-theyre-bad/
there's one exception - your log file grows stupidly big and you run out of disk space and need to complete the transaction (because of business requirements) - I've often found myself putting a cheeky extra log file on another drive when I know we have something big coming in
MVDBA
November 22, 2019 at 3:23 pm
Jonathan AC Roberts wrote:I don't think it'd a good idea to have more than one log file for a database:
https://www.sqlskills.com/blogs/paul/multiple-log-files-and-why-theyre-bad/
there's one exception - your log file grows stupidly big and you run out of disk space and need to complete the transaction (because of business requirements) - I've often found myself putting a cheeky extra log file on another drive when I know we have something big coming in
Yes, if you're out of space you need might need to create one on a different disk. I think some people incorrectly think adding log files will increase performance in the same way as adding additional data files to a database does.
November 22, 2019 at 3:34 pm
MVDBA (Mike Vessey) wrote:Jonathan AC Roberts wrote:I don't think it'd a good idea to have more than one log file for a database:
https://www.sqlskills.com/blogs/paul/multiple-log-files-and-why-theyre-bad/
there's one exception - your log file grows stupidly big and you run out of disk space and need to complete the transaction (because of business requirements) - I've often found myself putting a cheeky extra log file on another drive when I know we have something big coming in
Yes, if you're out of space you need might need to create one on a different disk. I think some people incorrectly think adding log files will increase performance in the same way as adding additional data files to a database does.
problem is we often forget to remove the log file, as per one of steve's editorials, I've started leaving sticky notes on my monitor to clear it down (netops get grumpy about disk usage) - we've recently started using stretch and my postit note says "Monday - reclaim space on Q: "
MVDBA
November 22, 2019 at 3:49 pm
Do you actually have 65GB remaining on the drive to create the file? And, I've got to ask, are you sure the path is correct?
And even if Snargables has 65 GB, it takes some time to zero out that much space. The line "Query was cancelled by the user", indicates that Snargables pressed the red button. The error message may come as a result of that the operation was cancelled.
Whatever, coffee could help.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply