Expand Database to other drive

AK

Hi,

we are using SQL 2008 with a dedicated drive respectively for user db, temp db and the transaction log. Analysis shows that temp db is almost never used, while the drive of the user db sometimes has some nasty peaks in the queue, so I'd like to split the user db in half and put one part on the drive of temp db.

What would be the simplest way to do this?

Thanks
Alex

Slawek_Guzek

The trouble is that in SQL entire table (by default) is stored in single file (no matter how many files the database is spread across). Which file - SQL determines this at the time of table creation.

If you already have the database, adding new file won't do any good. New file will not be used, unless you add some new tables.

You cannot force table to use particular file, unless you create a separate filegroup, define a file in new filegroup, and then issuing some ALTER TABLE statement forcing it to be moved to the new filegroup. In theory you can do the same with indexes - put them to separate filegroup. But believe me - you do not want to do this

There is another technique called table partitioning, where you can split table across different files, but this again requires configuration files in separate filegroups. Again - not a simple thing to do (with NAV tables) and probably not worth the efforts to configure and administer to get some benefits.

The simplest solution I can think of is to backup database using native NAV backup, delete SQL database, create new one with two or more files (can be in the same filegroup), then restore database from native NAV backup.

According to my experience tempdb is used, and quite heavily, but it is not a constant load, but rather short and high spikes. It is rather hard to observe. But WHEN tempdb is used SQL can really push the storage where file is located to its limits.

If you really believe (or just know) your tempdb disk is not used to make some good use of it you may want to dump SQL backup onto it, if you don't already have another separate disk configured for that purpose. If data disk and tempdb disk are really separate physical disks this will speed up your backup significantly.

Slawek

AK

Thank you for confirming my assumptions and for sharing your thoughts. I was hoping there might be some kind of SQL server function for splitting up databases, but seems I'll have to go the long way.
BTW, we have very few write transactions, hence the low use of tempdb.

Slawek_Guzek

You are welcome.

I must correct myself:

Slawek Guzek wrote:

The simplest solution I can think of is to backup database using native NAV backup, delete SQL database, create new one with two or more files (can be in the same filegroup), then restore database from native NAV backup.

Actually all data files must be in the same default filegroup (Data Filegroup 1) if SQL is supposed to create tables in different files when Nav native backup is restored.

Regards,
Slawek

Slawek_Guzek

Yes, there is no difference between SQL2008 and SQL2005 in this area.

Regards,
Slawek

bbrown

You can move the datafile using DBCC SHRINKFILE with the EMPTYFILE argument.

bbrown

Slawek Guzek wrote:

The trouble is that in SQL entire table (by default) is stored in single file (no matter how many files the database is spread across). Which file - SQL determines this at the time of table creation.

Slawek

Not sure I agree with the above, as I work with a system that seems to contradict this. The database is around ~320 GB used and is split across 3 even-sized datafiles. The vast majority of data is contained within only a few files. Two tables alone account for 50% of the database. Based on the above statement I would expect the used portion of each file to be uneven. However when I check the used space,they are within 70 MB (.6%) of each other.

Slawek_Guzek

bbrown wrote:

You can move the datafile using DBCC SHRINKFILE with the EMPTYFILE argument.

Yes, but only if database is already spread across more than one files.

...I'd like to split the user db in half and put one part on the drive..

Since by default NAV database is created in two files, and all table data is held in one file I assumed that question was in fact how can you move one or more tables, to another file.

Regards,
Slawek

Slawek_Guzek

bbrown wrote:

Not sure I agree with the above, as I work with a system that seems to contradict this. The database is around ~320 GB used and is split across 3 even-sized datafiles. The vast majority of data is contained within only a few files. Two tables alone account for 50% of the database. Based on the above statement I would expect the used portion of each file to be uneven. However when I check the used space,they are within 70 MB (.6%) of each other.

320GB * 50% = 160 GB. (Two tables alone account for 50% of the database)

160GB / 2 = 80GB (per table)

320GB /3 = 120GB (The database is around ~320 GB used and is split across 3 even-sized datafiles)

80GB (table size) < 120 GB (data file size)

It looks for me that single table can perfectly fit in single data file... Even without using data compressions.

SQL does not warrant you that all files will be filled equally. When you create new table SQL server simply picks least used data file. Once table is created in some file all table data is pushed to that file.

The example you've given doesn't contradict, but either doesn't prove anything.. It is just one of many possible cases in my opinion.

Regards,
Slawek

bbrown

Slawek Guzek wrote:

bbrown wrote:

You can move the datafile using DBCC SHRINKFILE with the EMPTYFILE argument.

Yes, but only if database is already spread across more than one files.

...I'd like to split the user db in half and put one part on the drive..

Since by default NAV database is created in two files, and all table data is held in one file I assumed that question was in fact how can you move one or more tables, to another file.

Regards,
Slawek

You can also use it to spread a database from 1 to multiple files.

1. Create the additional files (same filegroup)
2. Run DBCC SHRINKFILE with the EMPTYFILE argument onoriginal file
3. Drop original file.

bbrown

Slawek Guzek wrote:

bbrown wrote:

Not sure I agree with the above, as I work with a system that seems to contradict this. The database is around ~320 GB used and is split across 3 even-sized datafiles. The vast majority of data is contained within only a few files. Two tables alone account for 50% of the database. Based on the above statement I would expect the used portion of each file to be uneven. However when I check the used space,they are within 70 MB (.6%) of each other.

320GB * 50% = 160 GB. (Two tables alone account for 50% of the database)

160GB / 2 = 80GB (per table)

320GB /3 = 120GB (The database is around ~320 GB used and is split across 3 even-sized datafiles)

80GB (table size) < 120 GB (data file size)

It looks for me that single table can perfectly fit in single data file... Even without using data compressions.

SQL does not warrant you that all files will be filled equally. When you create new table SQL server simply picks least used data file. Once table is created in some file all table data is pushed to that file.

The example you've given doesn't contradict, but either doesn't prove anything.. It is just one of many possible cases in my opinion.

Regards,
Slawek

Each data file is 125 GB with 103.19 GB, 103.14, and 103.21 used respectively. The two files in question are 90 GB and 92 GB respectively.

For arguments sake lets' say that each of these tables is in its own file (based on size and what you've said above) and that the remianing tables are mostly in the third file. Given this situation would you not expect system I/O activity to be uneven across the three files?
It's not.

From BOL:

Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.

bbrown

My problem is that I've heard both sides of this argument, and from reliable sources. So here's a little experiment I am thinking about.

I have a slightly smaller copy of the DB mentioned above. It's 3 files have 99.36 GB, 99.31 GB, and 99.36 GB used. If I were to drop one of the larger tables, would we then expect to see all (or most) of the new free space gained in one file?

Thoughts?

bbrown

Here's the results of my little experiment:

1. Prior to starting the used space on each file was 99.36 GB, 99.31 GB, and 99.36 GB respectively.
2. I then dropped the indexed views for the table.
3. Checking used space it was now 99.31 GB, 99.26 GB, and 99.31 GB respectively

4. I then dropped the table
5. Checking used space it was now 71.05 GB, 71.01 GB, and 71.07 GB respectively

Thoughts?

Slawek_Guzek

You're right. Your experiment proves that data from the same table is spread across database files in same filegroup.

I was pretty sure that I've read some documents about SQL I/O engine, stating that pages are allocated in extents from the same file. I couldn't find it unfortunately, but it looks like the document was incorrect, or, most likely, I remembered it incorrectly.

In that case simply adding second file, and rebuilding clustered indexes on biggest (or all) tables should spread data (at least some of it) across two files. And definitely adding two or more files and emptying first one (DBCC shirinkfile(.. EMPTYFILE)) will also do the trick

Kind regards,
Slawek