Sharepoint Forum

Ask Question   UnAnswered
Home » Forum » Sharepoint       RSS Feeds

Size of: STSADM Backup .DAT File VS. SQL Database

  Asked By: Domonique    Date: Dec 07    Category: Sharepoint    Views: 9236

The backup of my Sharepoint Site Collection using STSADM.exe is 18GB.

The actual size of the 'Disk Space Used' within SQL on the Content
Database is 38 GB. I am viewing this information within SQL Server
Management Studio SP2, in the `Disk Spaced Used Report'. Total Space
used for the Database is 52 GB, for the transaction logs are 15 GB, but
used space is 38 GB.

I restored the 18 GB backup using STSADM and verified all sites,
subsites, and content is intact. I am very confused as to why there is
such a difference in size between the STSADM backup file and the
SQL .MDF file.
The content database in question has a single Site Collection.

I understand the SQL Database would also contain search information,
however this does not explain the difference in size between the 18 GB
stsadm backup file and the 36 GB SQL database.



7 Answers Found

Answer #1    Answered By: Jason Anderson     Answered On: Dec 07

The 52 = 38 GB Data + 15 GB Transaction logs.

The 38 GB is your 18 GB site  Collection plus potentially other site
collections and white space. Databases wisely add white space  to their
files as they know those databases will grow.

Two things to check, in Central Administration > Application Management
> Content Databases. That will tell you how many site collections are
stored in that Content Database. If there's more than one, that's part
of where that extra 20 GB is coming from. Second check SQL white space.
Go into SQL Management Studio. Click the database  in question, right
click and check properties. On the General tab you'll see Size, which
should be 38 GB, and how much of that space isn't being used.

Answer #2    Answered By: Uttam Vanjare     Answered On: Dec 07

There are no other site  collections in the database. I did walk
through the steps you outlined to indeed verify this. I also went
into 'Site Collection' List to view the specific site collection  in
the given database. (basically a cross check)
In looking at the database  it does not contain white space. Out of
the space  reserved (38.2gb – in the data file) 37.75 is allocated to
data & indexes. So, unless there is underlying "white space" that
Sharepoint creates/ only sees, to SQL it appears as data space.
Is anyone else seeing this???? I cannot make any sense of the
additional 20 GB of disk  used in SQL, knowing my STSADM backup  file
is 18 GB.
Any additional thoughts on this?

Answer #3    Answered By: Jack Carroll     Answered On: Dec 07

Possibly the change log. How old is the DB?

Answer #4    Answered By: Chandrabhan Agarkar     Answered On: Dec 07

The database  is about 1.5 years old. I am not familar with the
concept of a change log. In researching this, you are referring to a
sharepoint change log, not a SQL change log correct? How can I get
better insight into the sharepoint  change log? There is no much
information out there on this.

Answer #5    Answered By: Lonnie Cohen     Answered On: Dec 07

I wanted to provide an update on this issue.
The additional 20 GB of data in the SQL database  was auditing
We were evaluating a 3rd party auditing software, which in turn
enables Sharepoint auditing. All audit options were enabled (view,
touch, delete, etc), and within 4 months audting consumed 20 GB of
data in SQL.

We have since ran the stsadm.exe command 'trimauditlog' to remove
these items and disabled auditing:

Answer #6    Answered By: Ruchir Satam     Answered On: Dec 07

I always forget about the auditing information.

Answer #7    Answered By: Suresh Chindarkar     Answered On: Dec 07

Interesting. I'm not sure what the issue is then. Have you had any other
site collections that might have been in that content database  and
gotten orphaned? Any restores that have failed or something like that?
You might try running "Stsadm -o databaserepair" on that database and
see what it says.

Didn't find what you were looking for? Find more on Size of: STSADM Backup .DAT File VS. SQL Database Or get search suggestion and latest updates.