July 22, 2015 at 12:58 pm
Hi Folks,
I am aware of the issues that shrinking a data file may cause but we have to move forward on it. We have a business requirement to have a smaller size because development environment do not want to buy new hardware to have bigger sized database.
I tried to create a small project with T-SQL and powershell. Here is what it does:
1- Pick the latest backup from the production backup location
2- Restore the backup on an intermediate server
3- Disable non-clustered indexes to get some free space back
4- Purge some data
5- shrink the data files
6- Take the backup of this DB and give it to dev
The 5th step is an issue for me,I tried shrinking the data file in chunks of 3 GB. It works well for small databases in the range of 50-100 GB. It falls flat when the database size is 800 GB.
I call this T-SQL script to shrink using powershell invokesqlcmd. The powershell process hangs and and I do not see anything being done on SQL Server side in activity monitor.
Not sure why powershell not able to call the shrink well. I need to look at alternatives as shrinkfile is not predictable. Sometimes it errors out saying 'Any results should be discarded'
Any clues on how to reduce size for a scenario like this. If I was business manager, I would rather buy some hardware for development environment and use project time to build better applications but I am not:-)
Thanks
Chandan
As there are around 300 tables with relationships so can't use SSIS to
July 27, 2015 at 4:37 am
There's nothing special about how Powershell would issue the command, so I'd have to assume you have blocking or something going on. Look to sys.dm_exec_requests to see what's happening with the query, what resources it's waiting on, if it's being blocked, etc.
As far as shrinking a database for development, that's fine. I used to do it all the time. It just makes sense. It's repeated shrinks against production databases that is the real issue, not a one-off shrink, or a targeted shrink such as what you're doing. Those are fine.
"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
July 27, 2015 at 6:28 am
@Grant-
The code that I executed kept on running for more than 26 hours and appeared to be doing nothing. After putting in a lot of research and reading on shrink, I found that I do have some tables with data types XML, varbinary etc. so that might be causing the issue in not releasing space.
With no options left I am :
- Dropping all foreign key constraints
- Copying the data using 'select * into ' to another database
-Truncating the tables in my DB and doing a shrink
-Getting all the data back from temporary database
- Taking a backup and giving it to dev
Please suggest an alternate approach. I also thought of moving all tables with blob columns on a new filegroup and skipping that file while issuing shrink.
Thanks
Chandan
July 27, 2015 at 7:46 am
chandan_jha18 (7/27/2015)
@Grant-The code that I executed kept on running for more than 26 hours and appeared to be doing nothing. After putting in a lot of research and reading on shrink, I found that I do have some tables with data types XML, varbinary etc. so that might be causing the issue in not releasing space.
With no options left I am :
- Dropping all foreign key constraints
- Copying the data using 'select * into ' to another database
-Truncating the tables in my DB and doing a shrink
-Getting all the data back from temporary database
- Taking a backup and giving it to dev
Please suggest an alternate approach. I also thought of moving all tables with blob columns on a new filegroup and skipping that file while issuing shrink.
Thanks
Chandan
Appears to be doing nothing and is blocked or waiting on a resource are two very different things. I'd still try to understand what resources specifically it's waiting on. That will help you target your process better.
The process you're outlining seems excessive. It seems like it could be done in a single step. Copy the data over to a new database. Since that's effectively what you're doing, copying it, truncating, shrinking the empty database, copying the data back which grows the database to just the right size for the storage needed, you could just stop at the copy point and you should have the same size database as what you're getting through the process you're going through.
However, I'd still concentrate on why you're hanging and trying to address that. Having different storage for LOB data is a good idea.
"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
July 27, 2015 at 8:35 am
Grant Fritchey (7/27/2015)
chandan_jha18 (7/27/2015)
@Grant-The code that I executed kept on running for more than 26 hours and appeared to be doing nothing. After putting in a lot of research and reading on shrink, I found that I do have some tables with data types XML, varbinary etc. so that might be causing the issue in not releasing space.
With no options left I am :
- Dropping all foreign key constraints
- Copying the data using 'select * into ' to another database
-Truncating the tables in my DB and doing a shrink
-Getting all the data back from temporary database
- Taking a backup and giving it to dev
Please suggest an alternate approach. I also thought of moving all tables with blob columns on a new filegroup and skipping that file while issuing shrink.
Thanks
Chandan
Appears to be doing nothing and is blocked or waiting on a resource are two very different things. I'd still try to understand what resources specifically it's waiting on. That will help you target your process better.
The process you're outlining seems excessive. It seems like it could be done in a single step. Copy the data over to a new database. Since that's effectively what you're doing, copying it, truncating, shrinking the empty database, copying the data back which grows the database to just the right size for the storage needed, you could just stop at the copy point and you should have the same size database as what you're getting through the process you're going through.
However, I'd still concentrate on why you're hanging and trying to address that. Having different storage for LOB data is a good idea.
@Grant- Thanks for your valuable inputs. When the shrink was going on, the activity monitor did not show me a wait, and the task was always either suspended or in runnable state. I ran out of patience after 25 hours so did not wait for it. Actually I was incrementing it in chunks of 5 GB and printing command for each execution but noticed that same commands were being printed.
As far as the lengthy process I am taking, the problem is that there are a lot of stuff like keys, constraints, schemas etc etc so just moving data would not have helped me.
This way I can have the same database and just shuffle of data here and there.
Also, I figured out a lot after a lot of reading over this week.
- Tables with LOB columns cannot be moved to different filegroup with a clustered index rebuild. A new table with same data needs to be created on new filegroup, do your shrink and then get the data back
- I also read that even after we purge a lot of data from LOB tables, those pages still exist actually and while shrink they come in picture again. So we have to take a backup so that backup operation does not include those pages.
I am very very confused:w00t: Just trying to figure out how to get my data back from temporary database to my original ones as it has a of columns with identity and insert into Table select * from Table is not possible so I have to explicitly put column names. Figuring out how to do that automatically via t-sql for 500 tables:w00t:
Best Regards
C
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply