1

There is something I don't understand with the MSSQLServer mdf files. I have been asked to replace a file with its previous version (the database has been modified between the two versions), but when I seek it on my backup (made with Bacula), the file size is the same !

My question is : has the file a pre-defined size which dynamically grows as it lacks of space (huh, am I clear ?), so my backups have the same size but not the same content ?

Sorry, but I'm not familiar with SQL Server....

Thanks for your help,

Jérémie

0

Sql databases are a bit like big excel spreadsheets.

There are columns and lines.

When you remove data it doesn't shrink the database because it doesn't remove line and data , only the values (and it store the log file the changes if you are in full log mode).

You have to backup the database to reclaim space on log and storage file . (log and mdf) .

1

(Aside: I hope you're also running SQL Server backups. As in, from inside SQL Server. Moving right along....)

This depends partly on the actual data types you use in your tables and they types of queries you more commonly run. For example, an integer column uses 4 bytes to store data no matter which integer you store there. A datetime uses 4 bytes to store data no matter which date you store there. Uniqueidentifiers (GUIDs) are 16 bytes, no matter what. Update away, the storage size remains the same.

Variable data types, like nvarchar, are variable (duh). Nvarchar, for example, is twice the number of characters entered, in bytes, size-wise.

Also, your database size is pre-allocated when created, either with a value you enter or the default values for your server (which is set in the model database). So it's possible that it hasn't filled up the pre-allocated space yet, especially if you do more updates than inserts or it isn't very busy. (And whether it autogrows depends on your settings.)

Here's a reference for the various data types.

  • "Also, your database size is pre-allocated when created, either with a value you enter or the default values for your server (which is set in the model database). " just as I thought, viewing the differents restoration points in SQL manager... I will learn more about SQL backups, though I use Windows' snapshots on files + Bacula for daily backups. – jpicaude Feb 20 '15 at 11:55
  • 1
    "Also, your database size is pre-allocated when created, either with a value you enter or the default values for your server (which is set in the model database). So it's possible that it hasn't filled up the pre-allocated space yet, especially if you do more updates than inserts or it isn't very busy. (And whether it autogrows depends on your settings.)" : just as I thought, viewing the different points of restoration with same file size... I will learn more about SQL Server Backups, though I use Windows' snapshots + Bacula for daily backups. Thanks ! – jpicaude Feb 20 '15 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.