SQL Database Shrink Question

[penfold]

Hi,

 

I need someadvice from some SQL experts. We have inheritted an ePO server which has been running an SQL database which has not been maintained since it was instaleld (about 5 years ish). As such it had grown to over 200GB in size. I have run some pruning of old data which is no longer required and the actual size is showing apx 15GB which is obvisouly a big difference. Now when I run the pre-check for McAfee ePO upgrade I am getting an error about disk space not being enough and problems with some tables. My question is I know it is not recommeneded to shrink database but what is the impact shrinking the db down compared to leaving it as it is. At the momenet each backup is ~200GB in size and basically fills the disk each time (db backup is cleared after it gets backed up). I was thinking that due to potential issues during the upgrade and ongoing performance of going through a Databse which is >200GB in size but only using ~ 15GB it would be a good idea to shrink the database and keep it in order so it doesn't grow so large?

 

Before I shrink the db I would like to know if I could cause more problems than it fixes. Any suggestions?

 

Thanks

[penfold]

Bump for morning readers...

[RobD]

Hello,

 

I'm no expert on SQL but I believe its to do with table fragmentation. The tables become fragmentented which you can fix with a index rebuild but that re-inflates the space which basically puts you back to where you were.

 

I'd suggest, performing a full backup with something so the logs truncate properly then just giving it more space if you can.

[penfold]

@RobD - that's what I understand, but I can't see how a reindex would inflate the db from 15GB to >200GB. But like you I'm no expert hence asking the questino

 

My concern is that the db is so big it will cause a problem with the upgrade.

[RobD]

Why dont you back it up to a .bak file then shrink and see if it breaks anything and then update.

 

At least you'll have a point in time backup?

[penfold]

Currently that is the plan - But if we backup the disk will be pretty much full (it is removed when it the file is backed up) so if we do this and need to revert there is a need to restore the file> then restore db which will obviously take time.

[googlemad]

No harm in trying to shrink via the built in options using SQL Management Studio, except perhaps stop the ePO services first so they aren't trying to hammer the database at the same time.

 

Our ePO has around 4000 devices in and that is using several McAfee products too so plenty of traffic to and from ePO and the database is only around 20GB so definetely something wrong with your size!

 

Sadly I would say however the size of the database is just the tip of the iceberg when it comes to things you'll have to tinker with to get an ePO upgrade to actually work successfully!

[penfold]

@googlemad - I know!! I've been playing around with it on some test machiens and have already sorted some issues that we will have in the upgrade (I hope) but the one thing that looks really wrong to me is the db size. We have about 6000 devices so I would expect our db to be something similar to yours - which it is with actual size in use.

[penfold]

Another question on this. I have got a backup of this SQL and I want to run a shirnk on the db. Can anyone clarify what the difference is between using Truncate or not? Do I understand correctly that I can run a truncate and it will release all the empty space at the end of the file? In this case it does not make any changes on the file? I have tested this on a test machine and it runs very quick( as opposed to running a shrink) I have also read that a truncate is not undoable but if it only drops empty space from the end of the file what is the downside of using truncate?

[PlantHead]

Use TRUNCATEONLY and you should avoid the pitfall of index fragmentation

 

USE <>

GO

DBCC SHRINKFILE (N'<>', <>, TRUNCATEONLY)

GO

 

Shrink the DB file down in stages if it is massive, or you might impact production - the operation is pretty hefty on I/O

 

Truncateonly basically means that no pages are moved within the DB during the shrink which will avoid fragmentation.

The catch is that it only works by shrinking the DB back to the last time it was allocated space - so only free space at the end of the file will be reclaimed.

 

If you use Notruncate SQL will move all the pages inside the file to the beginning freeing up space at the end but causing massive fragmentation

 

Basically what happens if you run Shrinkfile without any parameters is that first a Notruncate is run, freeing up space at the end of the file and causing fragmentation (the file stays the same size) - then it runs it again with Truncateonly and clears all that free space thus shrinking the actual file size but killing performance with fragmentation.

 

Shrinking a DB is bad generally unless you know that the DB is never going to grow to that size again. Shrink it gradually and rebuild the indexes as you go.

 

The correct solution is to create a new database with proper sizing in place and script the old database out but that can get quite scary

Edited September 11, 2019 by PlantHead

[PlantHead]

Also for SQL - just use the maintenance scripts here and forget about your SQL server

https://ola.hallengren.com/

[penfold]

@PlantHead - thanks. I'll try the truncateonly option and see how much space I can reclaim. If if does not reduce the size enough presumabley I can always run a nontruncate shrink to reclaim the space? As for the amount of times I want to do this.....I only intend to do it once and then keep the db from growing the way it has.

 

Thanks

[jinnantonnixx]

In a stable system, the database is backed up, the log file is truncated and the physical log file(s) are re-used.

 

Truncating marks areas of the log file for re-use. This normally takes place when you backup - the backup procedure truncates the log file at a particular time, allowing the log file up to that point to be re-used.

 

Truncating in itself does not shrink the log file. Shrink is a separate procedure. Doing this outside of a backup procedure breaks the log chain - you won't be able to use the log file for a restore to a point in time if you have a full recovery model.

 

As an aside, the 'log file' usually consists of several virtual log files (VLF) occupying real file(s) on disk. Many VLFs indicate logical fragmentation, which in turn might be fragmented physical files, but this isn't normally an issue. The log files are written sequentially, and are only usually read during rollbacks and system startup. This is why it's possible to have gigantic log files with little noticeable drop in performance - the log file is not read, it's written to in a sequential manner, with little overhead, unless your disk systems start choking up.

 

But if your log files are ever-expanding, it's an indication that the log file is not being truncated. The most common reason for non truncated log files this is that is that the database is not being backed up properly. This is a red flag.

 

Shrinking databases and log files might buy you time, but it won't address the underlying problems.

 

If you have access to SQL Server Manager, this SQL script will list all the databases on your SQL Server instance and when they were last backed up.

 

SET NOCOUNT ON

-- print the headers
print  'Database   backup_finish_date      Backup_Type  Days Ago   Size MB                        Size GB     '
print '---------- ----------------------- ------------ ---------- ------------------------------ -----------'

DECLARE @mydatabasename as varchar(100)


DECLARE MYCURSOR CURSOR FOR
SELECT name from sysdatabases	--all dataabases

OPEN MYCURSOR

FETCH NEXT FROM MYCURSOR INTO @mydatabasename

WHILE @@FETCH_STATUS=0

BEGIN


SELECT top(1) left(sysdb.name,10) ,bkup.backup_finish_date,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end
AS [backup Type],  
(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) AS [Days Ago],
ceiling(bkup.backup_size /1048576) AS [backup Size MB],
cast((bkup.backup_size /1073741824) as decimal (9,2)) AS [backup Size GB]
FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
where type='D'
AND sysdb.name = @mydatabasename
ORDER BY sysdb.name, bkup.backup_finish_date desc


FETCH NEXT FROM MYCURSOR INTO @mydatabasename
END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR

 

 

 

I'm unclear about your problem - is your database larger than you'd like, or your log files?

 

 

This SQL script will display all the files associated with your databases, their physical file size, and how much of that size is actually is use.

 

It would be interesting to see your results.

 

declare @SQL nvarchar(max)

select @SQL = COALESCE (@SQL,'') + '
USE ' + QUOTENAME(Name) + '
SELECT	''' + Name + '''AS [DB Name], a.Name AS [Logical Name], a.Filename AS [File Name],
CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],
CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,''SpaceUsed'')/128.000,2)) AS [space Used (MB)],
CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,''SpaceUsed''))/128.000,2)) AS [Available Space (MB)],
b.log_reuse_wait_desc AS [Log reuse]
FROM dbo.sysfiles a (NOLOCK), sys.databases b
where b.name = ''' + rtrim(Name) + ''''
from sys.databases where state = 0 AND name NOT IN ('master', 'tempdb', 'model', 'msdb')

--print @sql

exec (@sql)

Edited September 11, 2019 by jinnantonnixx

[penfold]

@jinnantonnixx - the problem we have is that the db is over 200GB but the actual size of data used is just over 10GB. Last Full (manual) Backup was 12 GB in size. The reason for the big difference is due to there never been any maintenance in place to prevent the growth in the first place. I need to run an update for McAfee and it is looking at the size of the db as over 200GB and therefore wants a huge amount of space to perform the upgrade - it is also highlighting large tables which could cause problems. I have purgred lots of entries from the database because we seemed to be logging everything and not deleting anything. I want to get the db down to a reasonable size before I perform the upgrade to reduce any issues when it is performed.

 

I have set maintenance tasks to regularly purge events to prevent the growth again, but my aim is to shrink the db once and then keep it smaller by managing it properly.

 

Edit - Full backups are being performed weekly so I dont think that is the issue. I think the issue is old and only just now being picked up

Edited September 12, 2019 by penfold

[penfold]

Just checked the log file and it is showing as 9GB in size but only apx 35MB in use. Log file does not seem to be an issue - just the actual DB size

[jinnantonnixx]

@jinnantonnixx - the problem we have is that the db is over 200GB but the actual size of data used is just over 10GB. Last Full (manual) Backup was 12 GB in size. The reason for the big difference is due to there never been any maintenance in place to prevent the growth in the first place. I need to run an update for McAfee and it is looking at the size of the db as over 200GB and therefore wants a huge amount of space to perform the upgrade - it is also highlighting large tables which could cause problems. I have purgred lots of entries from the database because we seemed to be logging everything and not deleting anything. I want to get the db down to a reasonable size before I perform the upgrade to reduce any issues when it is performed.

 

I have set maintenance tasks to regularly purge events to prevent the growth again, but my aim is to shrink the db once and then keep it smaller by managing it properly.

 

Edit - Full backups are being performed weekly so I don't think that is the issue. I think the issue is old and only just now being picked up

 

It certainly looks like you've got things under control.

 

In that case, a db shrink would be a sensible option. As mentioned earlier, it's essentially a defrag operation on the database - db pages are moved to a contiguous group and old page space is deallocated and returned to the filesystem.

 

In this case, you might want to size the database for an estimated sensible working size (e.g. 20 GB) and switch auto-grow to, say 500MB instead of a percentage. (Auto-grow is an emergency measure to prevent your database collapsing, not a management tool!) And don't use auto-shrink

 

 

Microsoft recommends rebuilding the indexes after a shrink. Some good advice here:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-database?view=sql-server-2017#Recommendations

Edited September 12, 2019 by jinnantonnixx