DBAgent Issue handling pages

Information about space reduction

Probable causes of space reduction:

• Organic growth (yeah it's a no-brainer but still...): often data is gathered, produced, enriched etc. That results in an ever expanding set of data.
  It's advisable to develop cleaning and archiving strategies to prevent growing out of all space while you're data possibly gets outdated;
• Occasional or periodical actions that do massive transactions;
• Non-Sql related events: is the server dedicated to SQL Server or are other processes consuming space?
• Excessive indexing; indexes offer you better performance, if they're set up right. Remember that there's always a downside: they cost performance also because it takes time to maintain them and they also use space, often lots of it. It is not uncommon that the size of the indexes exceeds that of the data itself in particular cases.

Remember that deleting data and/or dropping objects do not release disk space by itself, depending on how your SQL Server is configured. There probably will be free space inside the database but to release that to the OS you often need to perform shrink actions.

Links about space consumption that could be useful:

• https://blog.sqlauthority.com/2013/08/02/sql-server-disk-space-monitoring-detecting-low-disk-space-on-server/
• https://www.sqlshack.com/how-to-monitor-total-sql-server-indexes-size/
• https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-volume-stats-transact-sql

Links about shrinking that could be useful:

• https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql
• https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql
• https://dba.stackexchange.com/questions/41215/shrinking-the-log-file-does-not-reduce-size
• https://www.brentozar.com/archive/2017/12/whats-bad-shrinking-databases-dbcc-shrinkdatabase/
• https://www.eukhost.com/kb/how-to-reduceshrink-the-sql-server-database-size/

> Home >

updated 2018-10-16 11:04 PM