September 28, 2017 at 4:01 am
Our SQL Server sends out a lot of emails. The msdb.data file grew up to 46 GB and there is no much room left on that drive. I tried to move it to a bigger drive but it didn't work. Cleaning up the data is not simple because the msdb transaction log grows up in size quickly and takes all available space.
I am thinking about setting the current data file to a fixed size and adding another data file to msdb on the bigger drive. I hope when the SQL Server will reach the limit on the first file, it will start writing to the second file. Is that a correct assumption? Are there any downsides in doing that?
September 28, 2017 at 4:28 am
Suggest you move the msdb, check out this article on the subject.
😎
September 28, 2017 at 4:40 am
Eliyahu - Thursday, September 28, 2017 4:01 AMOur SQL Server sends out a lot of emails. The msdb.data file grew up to 46 GB and there is no much room left on that drive. I tried to move it to a bigger drive but it didn't work. Cleaning up the data is not simple because the msdb transaction log grows up in size quickly and takes all available space.
You're going to have to address this eventually, and the longer you leave it, the more difficult it'll get. Start by deleting the mail data for one hour. If that doesn't blow the transaction log, do six hours. If that also behaves itself, do one day, then one week, and so on.
I am thinking about setting the current data file to a fixed size and adding another data file to msdb on the bigger drive. I hope when the SQL Server will reach the limit on the first file, it will start writing to the second file. Is that a correct assumption? Are there any downsides in doing that?
Eirikur is right - just move the whole thing to another disk. If that's not practical, the answer to your first question is no. If you add a file to an existing filegroup, data will be added to each file in a round-robin fashion (more detailed description here). If you create a new filegroup and add the file to that, you'll have to choose what tables or indexes will go in which filegroup. I can't find anything that says you can't add files to msdb, but I've never done it, so I don't know whether there are any pitfalls.
John
September 28, 2017 at 7:10 am
Thanks for the replies.
In the past, I tried both things you suggested and they didn't work for me.
Moving the file didn't work. The SQL Server didn't recognize the file in the new location. I don't remember the details, but whatever I tried didn't work for my size.
Deleting the data in chunks - I didthat. I spent hours to free some 20 GB, which is how the database is alive today. This is a very time-consuming process for both me and the database downtime. The database is essentially down since the msdb transaction log takes all the space.
So, it is not practical. But, John, I disagree with you that the answer to my question is no. The source you bring, http://sqlmag.com/blog/rebalancing-data-across-files-filegroup, says exactly opposite.
The proportional fill does exactly what I need:
" if you add a new data file to a filegroup that has mostly full data files, the proportional fill weightings will be such that the new file will be the one where allocations come from until it fills up to the same level as the older files. The new file in essence becomes an allocation hot spot."
This is what I want. I want all allocation to be from the new file. The article presents is as a bad thing because they look at it from performance perspective. I look at it from allocation perspective and it is a good thing for me.
Thank you!
September 28, 2017 at 7:34 am
You must have done something wrong, then. There's loads of stuff out there that tells you how to move a system database file. It's not the same process as for moving a user database file.
Deleting the data in chunks - I didthat. I spent hours to free some 20 GB, which is how the database is alive today. This is a very time-consuming process for both me and the database downtime. The database is essentially down since the msdb transaction log takes all the space.
Then you didn't do it in small enough chunks. Yes, it's very tedious and time-consuming. But once you get the database down to a manageable size, you can put in a housekeeping job that stops it from growing out of control again.
This is what I want. I want all allocation to be from the new file.
It won't be, though. Whenever you get a bit of free space in the original file, for example when job history is automatically aged out, the space in the original file will be used. But if that works for you, I'm glad we got you to where you want to be!
John
September 28, 2017 at 7:35 am
Eliyahu - Thursday, September 28, 2017 7:10 AMMoving the file didn't work. The SQL Server didn't recognize the file in the new location. I don't remember the details, but whatever I tried didn't work for my size.
Did you run the necessary ALTER DATABASE? Move the file? Set folder permissions?
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
September 28, 2017 at 9:17 am
Eliyahu - Thursday, September 28, 2017 7:10 AMIn the past, I tried both things you suggested and they didn't work for me.
Moving the file didn't work. The SQL Server didn't recognize the file in the new location.
Suggest you read the article and follow the instructions, this is a trivial task!
😎
September 28, 2017 at 4:33 pm
Eliyahu - Thursday, September 28, 2017 4:01 AMOur SQL Server sends out a lot of emails. The msdb.data file grew up to 46 GB and there is no much room left on that drive. I tried to move it to a bigger drive but it didn't work. Cleaning up the data is not simple because the msdb transaction log grows up in size quickly and takes all available space.I am thinking about setting the current data file to a fixed size and adding another data file to msdb on the bigger drive. I hope when the SQL Server will reach the limit on the first file, it will start writing to the second file. Is that a correct assumption? Are there any downsides in doing that?
Do you have to keep all that email info? Also, are you doing things like purging old job history, etc?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply