December 6, 2014 at 7:36 am
Dear SQL enthusiasts/experts,
Thanks for your interest in my question.
I have recently inherited the administration responsibilities of a production database (SQL 2012) (around 350GB).
It only had one data file (size 350GB). I added another data file (10GB) as soon as I noticed this and thought that SQL server will use the second data file more and not use the first (350GB) one until the second file grows to a larger size.
1. Is this a right assumption?
2. So far, no complaints about performance issues on this database, But I am a little nervous about the large size of the data file. Is this a big issue?
3. What can I do to solve the problem here.
Thanks!
Siva.
December 6, 2014 at 12:10 pm
Siva Ramasamy (12/6/2014)
It only had one data file (size 350GB). I added another data file (10GB) as soon as I noticed this
Why, what made you think this is even the correct response? All files in the same filegroup should have same size, growth and max size.
Are the files on the same disk?
Siva Ramasamy (12/6/2014)
and thought that SQL server will use the second data file more and not use the first (350GB) one until the second file grows to a larger size.1. Is this a right assumption?
Nope, depending on the available free space in the primary file prportional fill will write the majority of extents to the primary file.
Siva Ramasamy (12/6/2014)
2. So far, no complaints about performance issues on this database, But I am a little nervous about the large size of the data file. Is this a big issue?
I've managed databases with single files much larger, are you aware of the maximum file size for a sql server database?
Siva Ramasamy (12/6/2014)
3. What can I do to solve the problem here.Thanks!
Siva.
Empty the file and remove it, then sit and design correctly how best to spread your database, if indeed you actually need to. Did you have a change control record for this action??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 6, 2014 at 12:31 pm
Siva Ramasamy (12/6/2014)
Dear SQL enthusiasts/experts,Thanks for your interest in my question.
I have recently inherited the administration responsibilities of a production database (SQL 2012) (around 350GB).
It only had one data file (size 350GB). I added another data file (10GB) as soon as I noticed this and thought that SQL server will use the second data file more and not use the first (350GB) one until the second file grows to a larger size.
1. Is this a right assumption?
What you've stated is true. What you've done probably wasn't necessary. As it is, I don't see any reason to have done this particular thing. It'll just make your life a bit more complicated down the road.
2. So far, no complaints about performance issues on this database, But I am a little nervous about the large size of the data file. Is this a big issue?
As always in SQL Server, "It Depends". I understand that there were no complaints concerning performance but, unless you check some of the system DMVs, you don't actually know. I think you may have jumped the gun a bit by creating a separate file.
My biggest concern, especially in light of the fact that there were no complaints concerning performance would be backups and restores. What's you're backup plan, RPO, and RTO? What are the largest 2 tables in the database used for? If they're "audit" or "log" tables where rows are written once and never modified, then you're backups are wasting time and your RTO is going to make sucking sounds and you might want to start considering "piecemeal" restores to "get back in business" more quickly should a DR restore be called for.
Thinking of performance, what is the maintenance plan that you have for indexes and stats on this database? I'd be more concerned about how much time and disk space that might take especially on the larger tables.
Adding files just because you're "nervous" isn't the way to go especially since I don't believe that you've bought yourself anything by adding the file. You might have even slowed things down a bit depending on the nature of the tables in the DB.
3. What can I do to solve the problem here.
The first step would be to stop shooting first and asking questions later. 😀 Determine what the backup, restore, and maintenance plans actually are and make sure they exist.
What can you do to "solve the problem here"? I don't believe that there actually was a "problem"... as least not one that you've clearly identified. You need to do that before you make any other changes. What I would do is...
Find out what your RPO and RTO is supposed to be along with the minimum size of a possible emergency restore box would be. If you don't know what "RPO" and "RTO" is, you really need to learn those. Google for "RPO RTO" and study up. They're very important. Find out if your daily maintenance for backups, indexes, and stats is or will become a problem in the future. Find out what the largest tables are and how they're actually being used. Are there any tables/indexes that are static in content? If so, do you really need to back them up every night? Any audit or log tables that have mostly static data in them? Is that static data immediately important to "get back in business" or can it be restored at leisure after the database is back up after a DR Restore. Then design a RESTORE plan based on the RPO and RTO and let THAT be your guide to handle the database and the underlying FileGroups and Files.
I agree that the database is starting to get a bit large and that you're "nervous". Nervous people make mistakes. The bottom line is, stop making changes without a plan and DO make a plan. That way, you won't be nervous at all. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 6, 2014 at 12:32 pm
Perry Whittle (12/6/2014)
Siva Ramasamy (12/6/2014)
It only had one data file (size 350GB). I added another data file (10GB) as soon as I noticed thisWhy, what made you think this is even the correct response? All files in the same filegroup should have same size, growth and max size.
Are the files on the same disk?
Siva Ramasamy (12/6/2014)
and thought that SQL server will use the second data file more and not use the first (350GB) one until the second file grows to a larger size.1. Is this a right assumption?
Nope, depending on the available free space in the primary file prportional fill will write the majority of extents to the primary file.
Siva Ramasamy (12/6/2014)
2. So far, no complaints about performance issues on this database, But I am a little nervous about the large size of the data file. Is this a big issue?I've managed databases with single files much larger, are you aware of the maximum file size for a sql server database?
Siva Ramasamy (12/6/2014)
3. What can I do to solve the problem here.Thanks!
Siva.
Empty the file and remove it, then sit and design correctly how best to spread your database, if indeed you actually need to. Did you have a change control record for this action??
+ 1000
--Jeff Moden
Change is inevitable... Change for the better is not.
December 7, 2014 at 7:11 am
I'm just going to pile on and suggest really strongly that you ensure you have backups in place, that you test your backups by running a restore to another server somewhere, that you set up monitoring to understand how your systems are behaving, that you make no changes to the system, at all, until you know for sure that you have an issue that needs addressing and then you address that issue directly, not with some random or vague guess.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 7, 2014 at 2:18 pm
Siva Ramasamy (12/6/2014)
Dear SQL enthusiasts/experts,Thanks for your interest in my question.
I have recently inherited the administration responsibilities of a production database (SQL 2012) (around 350GB).
It only had one data file (size 350GB). I added another data file (10GB) as soon as I noticed this and thought that SQL server will use the second data file more and not use the first (350GB) one until the second file grows to a larger size.
1. Is this a right assumption?
2. So far, no complaints about performance issues on this database, But I am a little nervous about the large size of the data file. Is this a big issue?
3. What can I do to solve the problem here.
Thanks!
Siva.
Quick question, what is the problem?
😎
December 8, 2014 at 9:30 am
Dear SQL Experts,
Thanks a lot for your time to answer my question.
Since I have never handled a database file over 350GB before, I was nervous and acted without consulting the experts.
But, fortunately I did not observe any performance or other issues for the last few days.
I am just going to leave it as it is for now.
I do weekly DBCC and Check for index fragmentation and rebuild if it is fragmented over 30% and also update statistics as well.
I will also check with the application team if there is any data that can be archived.
Thanks again.
December 8, 2014 at 10:24 am
You still need to find out about RPO, RTO, and what the Restore Plan is, though. While 380GB isn't a "HUGE" database, it can take a fair amount of time to Restore. Somewhere along the line, you're going to have to do a test restore of that database on a different box.
And, to reiterate, you need to make sure that's it's being properly backed up even if you don't know the RPO or RTO, yet, along with stats maintenance, index maintenance, integrity checks, etc.
Something else that you might want to do is get a copy of Brent Ozar's "sp_Blitz" store procedure and run it against that database. It'll give you a really good overview of things including (as Brent says) the privs of "people that could get the DBA fired". 😀
I appreciate the slightly shredded nerves you may have if you haven't had to work on a database that large if you've never done it before. Being nervous is actually a good thing. Move slowly and surely. If you aren't sure about something, don't do it until you've done a research deep dive on it.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 8, 2014 at 10:44 am
Siva Ramasamy (12/8/2014)
I am just going to leave it as it is for now.
Pointless in my opinion, remove it before it fills up too much and then gets cemented in as part of the database structure.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply