Safely removing SQL DB whitespace

[Domo]

We have an SQL DB which is 500GBs but of that only 300GBs is data. In order to port it to a new host we have to reduce the size.

1. Will a backup and restore get rid of the whitespace (I'm assuming it won't)
2. If not, how can I safely reduce the DB size - i.e. what steps to I need to perform after shrinking? Most (if not all) articles mention the risks of fragmentation.
3. Can any of you recommend a process to do this which is safest?

Thanks.

 

[skyripper]

What database platform?

 

[d_brennen]

I'm no DBA but if you have current backups why not use the built in Shrink Database feature? I imagine all SQL database management programs have this by default...

 

[Domo]

There is a command. Casting my mind back, shrinking t-logs via DBCC carried no risk - but I'm concerned with the impact shrinking the datafile might have. RE platform it's SQL.

 

[Domo]

I think in the old days SQL did automatically shrink during the backup and restore process. Not anymore. SQL holds onto the space it grows into. The current situation suggests growth properties for said DB are wrong (probably set to auto).

 

[Vince]

I think in the old days SQL did automatically shrink during the backup and restore process. Not anymore. SQL holds onto the space it grows into. The current situation suggests growth properties for said DB are wrong (probably set to auto).

Still does if you are using a solution that is DB aware, take Veem for example, it's application aware settings will truncate logs, shrink DB's etc. Personally I don't use these as part of the backup and instead build maintenance plans into management studio which deal with this before the nightly backup.

 

[Domo]

We also run maintenance plans out of SSMS. Still need to shrink down this DB file though. This thread (and I'm not being critical of you guys ) is typical of content I've been reading online. It's like there's no clear answer or I'm framing the question incorrectly - again .

To cut to the chase, what harm can or will dbcc shrink database do and will it even do what I'm expecting it to? I.e. reduce the DB size from 500GBs to 300GBs.

 

[skyripper]

RE platform it's SQL.

That just told me it was a database.
Could have been Microsoft SQL Server, OpenSQL, Oracle, GawdKnowsWhat

Anyhow..

BACKUP LOG whatever WITH NO_LOG;
DBCC SHRINKDATABASE whichever;

Was my preferred method of clearing out all the unused space about 10 years ago on SQL server when disk space cost loadsa money.
Assuming I didn't also want a backup at the same time. Never had an issue with it. However that was 10 years ago.

Looks like there are options for the DBCC command - NOTRUNCATE , which "unfragment" the pages to the end of the datafiles, and doesnt shrink anything (for solving poor disk IO maybe!?) and TRUNCATEONLY - which just chops Logfiles down, not databases.

Without a handy experienced MSSQL DBA handy, I'd test it on a copy of the database before doing the real thing. (Or take the database offline, copy the mdf and ldf files somewhere)

 

[Vince]

We also run maintenance plans out of SSMS. Still need to shrink down this DB file though. This thread (and I'm not being critical of you guys ) is typical of content I've been reading online. It's like there's no clear answer or I'm framing the question incorrectly - again .

To cut to the chase, what harm can or will dbcc shrink database do and will it even do what I'm expecting it to? I.e. reduce the DB size from 500GBs to 300GBs.

Do you have a spare an server to test? I would be tempted to make a backup and restore to a test server in order to give it a blast. In theory there should be no negative effect but I wouldn't want to be the man that tells you to give it a pop while it nukes the thing.

If you do this and it works at least then you know. I assume you are on a San with something like vmware so cloning or snapshotting the server beforehand might also be a decent option (providing you have the storage tears to deal with it). I would love to know how you get on. Most of my sql servers are 2012 r2 with some 2016 in there for good measure and I run shrink every evening without negative affect on around probably something like 100 databases so I can only assume it should be tickety boo.

Fwiw the only time I have ever hit a white space issues it has never been sql, but always ese behind exchange. Which is offline defrag or mailbox moves to a new db. I appreciate this isn't really the best answer to your question but perhaps gives some other approaches to deal with it.

 

[Randomface74]

See https://docs.microsoft.com/en-us/sq...inkdatabase-transact-sql?view=sql-server-2017

And https://www.red-gate.com/simple-tal...rver-database-growth-and-autogrowth-settings/