MSSQL behaviour, DB occasionally release space

[Former Member]

Hi BASIS/MSSQL gurus,

We were doing daily checks on our database for the past 6 months. The database was growing healthy and steadily in size.

However we noticed that occasionally the database managed to free up a small bit of space. It's not very frequent, maybe once every 3 to 4 weeks and the space release is not really alot, perhaps 0.5% to 1% every occurance.

our MSSQL is SQL2008 R2 or a 64bit Windows OS.

Any glue what might this be and how we can mointor it's behavour in detail?

Thanks.

Regards,

Jansen

[Sriram2009]

Hi Jansen

1. Are you taking every day truncate log backup?

2. Could you share the transaction ST04 screen shot?

Regards

Sriram

[Former Member]

Sriram,

The behaviour is based on the daily readings of the available space in DB02. We record the available space everyday.(which is also available in the history tab) Which item you want to refer to in ST04?

I was thinking it more to be a SQL behavoiur rather than a SAP BASIS level behavoiur.

Rgds,

Jansen

[Sriram2009]

Jansen

In what way you are asking?  Could you explain little bit detail

[Former Member]

Basically my query is very simple.

- In DB02, Database space is used up steadily and the occupied space % is gradually increasing everyday.

- Every 3 to 4 weeks, there is a decrease in occupied space %. Around 0.5% to 1% is free up in the DB.

- Cycle continues.

note: I'm not talking about transactional log size, I'm talking about the main DB size.

I just want to know, what could have cause the space to be freed up in my Database.

It's not a problem or issue, it more for knowledge cos I'm very curious what did the database do at that point of time. (housekeeping? clearing buffer? what happen?)

[Sriram2009]

Hi Jansen

When your are deleting any data in MS Sql db by default its will create a log(Transaction log), once your are clearing the log you will get the space back. (MS Sql DB = Data & log files).

Could you share you SAP system version details?

1. To get the more space  if you are using the MS Sql 2008 R2 you can enable  the database & Index compression by using the SAP Notes

1488135: Database compression for SQL Server

1459005: Enabling index compression for SQL Server

2. Monthly once you have run the MS Sql DB consistence check by using the SAP Note

142731: DBCC checks of SQL Server

3. At SAP application level you have to define the retention policy to clear the unwanted log's. kindly refer the Data volume management based on you SAP system you can clear the data. kindly refer the SCN link

http://wiki.scn.sap.com/wiki/download/attachments/247267400/AppOps_DVM_Whitepaper_v1.2%20final.pdf?v...

For example In my environment we have SAP BI system. every week we are clearing the BI PSA, Change log & cube level compression are enabled, every tasks based on the retention policy clear the data

4. Over all the database performance you can refer the SAP Note

1744217: Basic requirements to improve the performance of a SQL Server database

I hope this will help you

Regards

Sriram

[Matt_Fraser]

This is not completely accurate. In DBACOCKPIT the Data size and Log size are shown separately. Jansen is talking about the Allocated [MB] field in the Data section of the Overview tab in the Space-Overview section of DBACOCKPIT (or DB02). This shows both an absolute (in MB) value and a percentage value of the amount of the database that has been used for data and indexes, and it shows how much free space remains in the database (this does not include remaining free space on disk outside the database). It does not include the transaction log, which shown separately.

You're correct that the SQL Server Enterprise Manager, in the Database Properties, combines the total on-disk size of the database and the transaction log to give a total amount of disk space consumed. However, Jansen is talking about the amount of space within the database that is actually used by data, and he is also referring to how that data is shown in DBACOCKPIT.

I have noticed the same phenomenon when investigating the DBACOCKPIT Database Size History, in that periodically the AllocSpace and the Data size (KB) values drop slightly, then creep up again over time. The overall trend is clearly upward, of course, but there are these jags downward. They are typically quite small decreases, happening once a month or so, and they don't seem to line up, timewise, with any of the database management or index or statistics reorganization jobs that run periodically. They absolutely have nothing to do with transaction log backups or truncations.

I don't know the answer for why this is happening, but I suspect there are some lower-level database management jobs that run without a specific SM37 or SQL Agent job associated with them. It should be possible with deeper digging to find them, but I think it's probably low-level processes reclaiming space from rows deleted by application processes, or reorganizations of application-level indexes (not SQL Server indexes), that sort of thing.

Regards,

Matt