To have an overview of the tables that can be moved across the database files in a database and see if they can be moved or not
My way of achieving it
Using tsql. The script is checking the edition of SQL Server and the biggest tables from our database and will check if these tables contains LOB data and how much from the table is LOB and how much is not. Tests showed that even if you have a column that is of LOB type, if the value can fit in the page then you can still move that page to other locations in the database files. I tried to find a way to map the table to database files but I couldn’t find a solution that will work in a fashionable time. Searching on internet revealed that I can use:
- DBCC IND(0,’tablename’,1)
- SELECT sys.fn_PhysLocFormatter(%%physloc%%) as rowlocation FROM tablename
The problem with first one is that for huge tables is taking a lot of time which makes it unusable while the second one although is faster for a 100GB table it is still takes minutes to run. I would have wanted to have this information in order to estimate how much space a table occupies on each of the database files. Also the script is ignoring the tables that are partitioned already.
The script is nothing more than a procedure that can help us to provide a quick answer to the question – Can I move some tables to other files or not?
Like with everything else from the internet please try it first and check if the information provided are accurate or not. Below is an example of the script output.
The script can be downloaded from here