Checking my knowledge
-
Forum,
I have a database that I am running out of space on the file system drives. It is currently taking up all of the space on 2 of the data drives. I have some space on the backup drive and some left on the tempdb drive (4 drives in all). The database shows the size is 4822297.38MB and Space Available is 1115624.55MB. The database is comprised of 3 datafiles and a log file. The files are in the following locations:
File1 - E drive (no space left on drive)
File2 - H drive (no space left on drive)
File3 - G drive
Log - F drive
Integrity checks have been failing because no space.The only thing I can think to do is to increase File3 on G drive and distribute the data from E and H to the G drive. My dilemma is if I do that I believe I should be able to defrag the data in the database to reclaim the data. Then I can shrink File1 and File2 to manageable sizes to at least get some breathing room. Can someone double check my thinking out there.
John LaSpada
-
Looking at the totals isn't necessarily helpful, have you confirmed what files the 1115624 MB space available is located in? This query will show you all the files and their space used for the database it is run in:
SELECT DB_NAME() AS database_name, f.file_id, f.name AS file_name, f.type_desc,
f.physical_name, f.size/128 AS size_MB,
FILEPROPERTY(f.name, 'SpaceUsed')/128 AS used_MB,
f.size/128 - FILEPROPERTY(f.name, 'SpaceUsed')/128 AS available_MB,
CASE WHEN f.is_percent_growth = 1 THEN f.growth ELSE f.growth / 128 END AS growth,
f.is_percent_growth
FROM sys.database_files fAlso, do you know the rate of growth for the tables in this database? Knowing how many MB per month each table grows would be useful in determining which tables and indexes to put on which drive.
One other thing, are these direct attached storage or are they provisioned from a SAN? If on a SAN, it may be better to increase the space allocated to the drives than doing the analysis and work to move terabytes of data objects around.
- Chris Harshman - Tuesday, August 21, 2018 2:51 PM
John LaSpada
-
If it's some type of SAN, work with the admins there to get more space on the drives as Chris already suggested. Otherwise, yeah, you'll need to move some stuff around. My suggestion for an easier move (note, not easy), would be to move nonclustered indexes from the existing locations to the new drives. There's just less work around moving the location of nonclustered index storage as opposed to moving the tables (heaps or clustered indexes). Managing disk space requires first that you set up monitoring on it so that you can track how it's growing over time. You should be able to largely automate all that so you don't find yourself in this circumstance again.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Grant Fritchey - Wednesday, August 22, 2018 5:08 AM
Unfortunately this is a system I was given recently and there is no more SAN storage I am told. Any strategy you would recommend around moving the non-clustered indexes. I thought it would have been better moving the primaries using the DROP_EXISTING. Hence this blurb from mssqltips.com:
https://www.mssqltips.com/sqlservertip/1362/efficiently-rebuild-sql-server-clustered-indexes-with-dropexisting
To efficiently rebuild a clustered index, the CREATE INDEX command provides the DROP_EXISTING option. This option can rebuild the clustered index in a single atomic step and re-creates the non-clustered indexes of the table only once depending on the index definition. This sets each index leaf level page pointer to the clustered index key while eliminating the overhead of setting the index page leaf pointer to a direct address first. On very large tables, the resource savings can be immense.
I was planning on creating a filegroup (do I have to?)
Then determining the largest indexes and moving those to File3 or filegroup? Do I have to create filegroups at this time with this strategy?
ThanksJohn LaSpada
-
You can simply add files to existing file groups and you can specify storage of an object to a file. However, management is a lot easier through filegroups, so yeah, I'd add one. Call it nonclusteredindexes or something.
The reason I suggest the nonclustered indexes instead of the data is because generally they'll be a little smaller, a little quicker, and less likely to have large scale issues when they move, unlike the data itself. However, you can move the data. But the idea here is to reclaim some space, not rearrange all storage, yes? If, the goal is to rearrange all storage, then move the data, but you probably need to think that through quite a bit more. For example, you have space on the F drive, but it's holding your log files now. I wouldn't suggest mixing data/indexes with logs if you can avoid it. So that means moving to the G drive... but what to move? Do you have discrete behaviors in the data that would be enhanced by separating one bit of storage from another? Then move that data to the G drive and leave the other in place.
This gets really thick very quickly. So, are we looking to reclaim a bit of space so we can do maintenance and get the servers back under control, or are we rearranging all storage? If the latter, you need to plan this out more than simply "Move the big things. WIN!".
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Grant Fritchey - Wednesday, August 22, 2018 8:36 AM
Unfortunately I only have space on the backup/tempdb drive or the logs drive. Nothing is available elsewhere at this time. See my dilemma. What is the worse of 2 evils? Thanks for you help thus far.
John LaSpada
-
From the results of the query you posted, it looks like even though the physical drive E: is nearly full, the data file on that drive has about a terabyte unused in it, so that drive may not be a problem for now. The next step though is to figure out what objects are in what files. Here's a query to get you started on that:
SELECT OBJECT_NAME(i.object_id) AS table_name, i.index_id, i.name AS index_name, i.type_desc,
i.data_space_id, f.name AS file_group, d.physical_name
FROM sys.indexes i
INNER JOIN sys.filegroups f ON f.data_space_id = i.data_space_id
INNER JOIN sys.database_files d ON f.data_space_id = d.data_space_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY d.physical_name, f.name, OBJECT_NAME(i.object_id); - Chris Harshman - Wednesday, August 22, 2018 9:46 AM
So when I run this and determine the size using the following
select SUM([indexsize(KB)]) as IndexSize, t.physical_name from (I get the following:
-- Gives index_name, size, index_type, file_group, and physical_location
SELECT OBJECT_NAME(i.object_id) AS table_name, i.index_id, i.name AS index_name, 8 * SUM(a.used_pages) AS 'Indexsize(KB)', i.type_desc,
i.data_space_id, f.name AS file_group, d.physical_name
FROM sys.indexes i
INNER JOIN sys.filegroups f ON f.data_space_id = i.data_space_id
INNER JOIN sys.database_files d ON f.data_space_id = d.data_space_id
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 and i.index_id <> 1
group by OBJECT_NAME(i.object_id), i.index_id, i.name,i.type_desc,
i.data_space_id, f.name, d.physical_name
)t group by t.physical_name;IndexSize Physical Name
1097294296 E:\SQLData\MSSQLSERVER\P_ODS.mdf
1097294304 G:\SQLData\MSSQLSERVER\P_ODS_2.ndf
1097294296 H:\SQLData\MSSQLSERVER\P_ODS_1.ndfJohn LaSpada
- john_laspada - Wednesday, August 22, 2018 9:12 AMGrant Fritchey - Wednesday, August 22, 2018 8:36 AM
Unfortunately I only have space on the backup/tempdb drive or the logs drive. Nothing is available elsewhere at this time. See my dilemma. What is the worse of 2 evils? Thanks for you help thus far.
Everyone hits a "do what you have to do, damn best practices" moment (or moments) sooner or later. The key is, getting past that point and then regrouping. So, do what you have to do now to get beyond the current situation where your drives are full and you can't run a consistency check (you know you can restore the database and run a consistency check if there's another server somewhere). However, you can't simply slap this over to the G drive and then wipe your hands off "Done"! You've filled seven terrabytes of data. You're adding less than one. You only have a little tiny bit of growth available right now. You need to address that long term.
Hey, just thought of something. Does the backup represent all the databases and all the data that is present in your two data drives? If so, you have a TON of wasted space and should be able to reclaim all of it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Grant Fritchey - Wednesday, August 22, 2018 10:32 AMjohn_laspada - Wednesday, August 22, 2018 9:12 AMGrant Fritchey - Wednesday, August 22, 2018 8:36 AM
Unfortunately I only have space on the backup/tempdb drive or the logs drive. Nothing is available elsewhere at this time. See my dilemma. What is the worse of 2 evils? Thanks for you help thus far.
Everyone hits a "do what you have to do, damn best practices" moment (or moments) sooner or later. The key is, getting past that point and then regrouping. So, do what you have to do now to get beyond the current situation where your drives are full and you can't run a consistency check (you know you can restore the database and run a consistency check if there's another server somewhere). However, you can't simply slap this over to the G drive and then wipe your hands off "Done"! You've filled seven terrabytes of data. You're adding less than one. You only have a little tiny bit of growth available right now. You need to address that long term.
Hey, just thought of something. Does the backup represent all the databases and all the data that is present in your two data drives? If so, you have a TON of wasted space and should be able to reclaim all of it.
Grant,
I have 683GB of backups which I could then limit to one day retention instead of two, possibly even move to Log drive for a few days until I can add a separate filegroup to File3 or make a new ndf file and add the Nonclustered FG to it. Then look at how to move the other components to Filegroups throughout the file system.John LaSpada
-
Why not just buy another drive so that you have some headroom to archive/delete a lot of the junk you know you don't need and maybe rebuild some indexes that you know are consuming too much space?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - john_laspada - Wednesday, August 22, 2018 10:48 AM
Right, but, is that all the databases? If so, you have a lot of space you can reclaim. Backups are a page by page copy of the database, plus some logs & log information. So they really closely map to the actual size of the data within the database. That means your database files might simply be too large, not that you have literally filled the drives up with data. Heck, before we get too far down all this path, I'd try a shrink, in place. A bunch of them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Grant Fritchey - Wednesday, August 22, 2018 11:57 AM
Just a reminder that if the backups are compressed, they are no longer 1:1 representative of the size of the data.
As a bit of a sidebar, if folks are able to compress backups and aren't, they need to have a good serious talk to themselves about why not. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Wednesday, August 22, 2018 12:21 PM
D'oh!
Right. Head clearly where it doesn't belong.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply