The old shrinkfile issue

SQL Server Administration
1

First post! Let me start by saying I am a google-taught DBA only... :smile:

I am currently running 2012 Business Edition that has a RAID5 array for the data partition with 850G total disk space. The logs parition is a separate mirrored RAID drive. We are currently in the process of buying a new HP server that will have a 3TB ioDrive2 PCI card in it for storage.

I run the well known script every night that reorgs/rebuilds the indexes on all tables. We use simple recovery mode, with weekly full backups and nightly differential backups.

So here is the problem.... All the drive bays are full on the server, and the data partition is down to 20g free. I had originally planned to already be off the server by now, but procuring the new server has taken longer than planned. I put in some nightly scripts to delete old data from the version1 database. I have finished a big code redesign and have been migrating tables and stored procs into the version2 database. DBv2 is now starting to grow on its own and is slowly eating up the 20g free space left on the physical disk. DBv1 has about 300g free internally after I truncated and dropped some unneeded tables. So I need to get some space back from DBv1 so that DBv2 can have it.

Each DB is just one file on the data partition and one file on the log partition. I ran a full backup and then tried running shrinkfile with truncate only option, but recovered no space. The only info I can seem to find on Google about using shrinkfile is "DONT DO IT!", so I am a little freaked out about using it. Most of those pages are referring to the 2008 version it seems.

My plan is to shut down my major apps that insert a ton of data into the two DBs, perform another full backup, run shrinkfile on DBv1 using the "keep lowering the size in small chunks" method until I get about 200g free on the physical drive again. Then go thru all the tables and manually run reorg on all the indexes. Then full backup again, and turn everything back on...

What I want is someone more experienced than me to: 1) tell me this is going to work and 2) offer any advice on things I may have missed. I have been monitoring the file growth and unfortunately the server has about 10 days of disk space left and the new server is supposedly two weeks away, but I obviously can't risk it.

Thanks in advance!

2

Well, if you need shrink the file then you need to do it. My advice is that you don't make a habit of it as it generates lots of IO.

I'd suggest running without the truncate only. That will help you capture space in the file. Choose the second radio button and choose a reasonable size.

I always script it out and run it in a query window so I can cancel it if needed.

I recently took 100GB out of a 1TB database and it ran for two full days. Their IO system was a little slow. It just chugged away in the background over the weekend. But please disable any re indexing that might be scheduled during that time.