shrinking a large data file
-
Hi All,
One of our drive got filled (E: drive) and we decided to shrink the data file from 1.6TB to 500GB.
While trying to shrink the file with didnt work.
USE [dbname]
GO
DBCC SHRINKFILE (N'db_name_dat' , 0, TRUNCATEONLY)
GO
it completed successfully but it didnt release any space to OS.
--then, tried below. its running for more than 2 hours
USE [dbname]
GO
DBCC SHRINKFILE (N'db_name_dat', 512050)
GO
Am I missing anything, what is the best way to shrink or release free space to OS? Previously, we used to have a log table which has taken up all the data and we truncated the table hoping shrink file will release free space to OS but its not.
Please let me know if you have any other ideas to release some space to OS. Do let me know in case if you need any information from me. This exercise we are doing it in our QA env. During the weekend, we have to do it on prod. In Prod, one variable is, the database is part of AG.
Thanks,
Sam
-
The first command might not shrink the file at all, because of the "TRUNCATEONLY" option.
The second command should work, but shrinks can take a long time.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
A shrink that large will take a long time, especially if there's a fair bit of LOBs in the data. Worse yet, you won't be able to see any progress until it's done even if you stop it. It's better to shrink in much smaller steps in a loop because that will also allow you to monitor progress. I typically use steps of only 10GB. The code should be written to be interruptible and restartable.
You can also write the code to do a shrink step and then try to do a truncate to see if you can "get lucky" between steps... especially for something as big as what you're doing.
--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) -
There's not a lot of data left in the file, so I'm rather surprised it would take that long to shrink it, since SQL wouldn't need to move that much data around.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Does it make any difference with the on-going open transactions to the database? is it better to do during maintenance windows when there are no incoming connections from the application? In QA env, we have restarted SQL Server and shrink the file to 500GB. It ran successfully as expected release space to OS and took ~30 mins.
- vsamantha35 wrote:
Does it make any difference with the on-going open transactions to the database? is it better to do during maintenance windows when there are no incoming connections from the application? In QA env, we have restarted SQL Server and shrink the file to 500GB. It ran successfully as expected release space to OS and took ~30 mins.
Yes... things will most certainly move more slowly on a busy system because locks on pages need to be acquired before the pages can be moved, etc. The other issue is the way the data is distributed on the file. The other issue is that the data distribution in the actual file is likely going to be very different than on a QA system.
--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) -
Suggest you create a new file and file group, then move the objects from the old to the new filegroup. this way you won't have all the negative impacts introduced by the notoriously, horrible, almost useless shrink file operation.
😎
A hint, you might want to introduce table compression in the process 😉
-
Just a thought you need to check the number of VLF's and which is the active one, this will indicate whether a shrink is actually possible.
...
- HappyGeek wrote:
Just a thought you need to check the number of VLF's and which is the active one, this will indicate whether a shrink is actually possible.
VLFs are Virtual Log Files that do not relate to the data files, some clarifications might be needed!
😎
Questions to ask are
- What is the recovery model?
- what are the backup and maintenance processes?
- Any other details to share?
Responses need to be Evidence-Based! 😉
-
I stand corrected I was thinking log as I recently had the issue. Apols to OP for confusion.
...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply