2

I tried to enlarge a database size of database instance on my SQL Server 2012. I manually added a new .MDF file under database instance

enter image description here

After restart on shrink database dialog I can see available space

enter image description here

on shrink files dialog the available free space is added

enter image description here

but when I executed following SQL query the available space still small

select
    name
    , filename
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
   , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
from 
    dbo.sysfiles a

The database still see only two files ....

enter image description here

What I did wrong ?

[Edit] If just statistics shown wrong in query, it's OK for me. My question is the whole process to add space to the database made in the right way or I need to add something to finish the process? Sorry for editing later

Thanks for any help

| improve this question | |
  • 3
    You should not use sysfiles it is legacy view and MS does not recommends it to use it in SQL Server 2012. You should use sys.master_files or sys.database_files see BOL for more details. Change your query and include these two views and see what is result – Shanky Mar 24 '15 at 13:19
  • Select from sys.database_files shows two files only – Toren Mar 24 '15 at 13:30
1

Community Wiki answer generated from comments on the question by Kris Gruttemeyer

Please increase your autogrow increments. It shows you are incrementing by 1MB, this can be a nightmare for your storage system as I'm sure it's constantly expanding. You want to plan to give the DB plenty of room to grow based on it's usage and growth patterns so you avoid costly I/O operations every 1MB along the way.

Setting autogrow to 500MB will do the opposite of "killing the server", and will help as you will have more room each time you hit an autogrow event. Using 1 MB is doing much more harm than 500MB ever would. Think about it, you're growing your DB at 1MB. Every. Single. Time. That's a lot of work for your storage system and disks. Given your DB size, I'd start at anywhere between 1-5GB growth.

Here's a great article I recommend reading, specifically the first section:

I like to operate in the simplest mode possible. Unless there is a good reason to have multiple files (like tempDB, faster drives for certain DBs, etc), keep everything in one. There are plenty of reasons to have multiple files, but having them for the sake of having them will just give you a headache. Makes things much easier from a manageability standpoint as you can just expand the one data file, be done with it, and go find something else to do. :)

DB files are containers, you want to be proactive with sizing and give it room to grow without going overboard (50GB of data in a 2T DB file would be excessive and a waste of space). If I have a 2T DB file but it only actually has 1GB of data, SQL is only going to have to do work on the 1GB of data it needs, not the entire 2T data file.

| improve this answer | |
1

Referring to DBCC UPDATEUSAGE (Transact-SQL):

Update the stats - sysviews are out of sync.

It seems the system views are not always accurate, and sp_spaceused is notorious for this. A form of caching is applied to non-critical, but heavy process data collection functions - like measuring partitions (like what you just created another of) and allocations.

Examining sp_spaceused (Transact-SQL), we see this:

When updateusage is specified, the SQL Server Database Engine scans the data pages in the database and makes any required corrections to the sys.allocation_units and sys.partitions catalog views regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the space information for the table may not be current. updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.

Try running:

EXEC sp_spaceused @updateusage = N'TRUE';
| improve this answer | |
  • I executed DBCC UPDATEUSAGE(0) , but result remains same – Toren Mar 24 '15 at 14:04
  • if you execute sp_helpdb 'DBNAME' you should get some information - please share with us? – Alocyte Mar 24 '15 at 14:09
  • Given that the file is there; it is not necessarily used as yet. The filegroup was not consisting of two files when tables where created, so filled one file will be, and the other empty; try rebuild a clustered index for a large table (assuming not PROD!) and see if you have better luck polling for the file you added then. – Alocyte Mar 24 '15 at 14:12
1

I see everyone has jumped away from the obvious, the query result only show 2 files, it should show 3 files, 2 .mdf files and the .ldf , what has occurred here I think is the OP has run the query in the context of master database or in another database context hence showing the incorrect results.

this query need to be run under the context of the database you are looking to get the results from as the query refers to dbo.sysfiles.

put use [database_name] where database_name is the database which you are interested in finding the space used and available.

| improve this answer | |

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.