March 10, 2012 at 12:08 am
Hi Experts,
On daily db monitoring i found some of my databases showing Database file size 100% utilized, we are using "dbcc showfilestats with no_infomsgs" command to find the database size. I manipulated this and got the result,
For example :
TotalExtentsUsedExtentsNameFileName
7167TESTC:\DATA\TEST.mdf
total_mb = sum(71)*64/1024 = 404375
used_mb = sum(67)*64/1024 = 4.1875
free_mb = 404375 - 4.1875 = 0.25
After this result i increase the size of the database file, upto 2GB.
I read the msdn in 1mb have 16 extents and each extents having 8kb pages.
As per the 2GB it will be,
No. Extents in 2GB = 2048mb * 16 = 32768 extents
I run the same command to check the size of my database, it is still showing the same result.
TotalExtentsUsedExtentsNameFileName
7167TESTC:\DATA\TEST.mdf
Pls help in this regards, why its not showing the updated size?
Thanks in Advance.
________________________________________
M.I.
[font="Times New Roman"]
March 11, 2012 at 9:31 am
I think the update data will be shown only when database use those space. You have allocated the space but still db is not using that space.
March 11, 2012 at 10:35 am
Use this query:
SELECT
DB_NAME() As DBNAME
, DB_ID() AS DBID
, SUM(size / 128) AS 'Total Size in MB'
, SUM(size / 128 - CAST(FILEPROPERTY(name , 'SpaceUsed') AS int) / 128) AS 'Available Space In MB'
FROM
sys.database_files
WHERE
type_desc = 'ROWS'
March 11, 2012 at 5:26 pm
MSQLDBA (3/10/2012)
After this result i increase the size of the database file, upto 2GB.
Did you increase the datafile size or did you allow SQL Server to extend datafile up to 2 Gig when needed?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 12, 2012 at 9:03 pm
PaulB-TheOneAndOnly (3/11/2012)
MSQLDBA (3/10/2012)
After this result i increase the size of the database file, upto 2GB.Did you increase the datafile size or did you allow SQL Server to extend datafile up to 2 Gig when needed?
I increase the datafile size, first it was suppose fixed size of 4GB so i just increased it to 8GB (means i increased more 4GB), and i checked again free size of the database its still showing the same.
________________________________________
M.I.
[font="Times New Roman"]
March 13, 2012 at 2:34 am
What edition of SQL is this on? Express? Std? Ent?
March 13, 2012 at 9:14 am
anthony.green (3/13/2012)
What edition of SQL is this on? Express? Std? Ent?
Its Ent edition.
________________________________________
M.I.
[font="Times New Roman"]
March 13, 2012 at 9:38 am
Suresh B. (3/11/2012)
Use this query:
SELECT
DB_NAME() As DBNAME
, DB_ID() AS DBID
, SUM(size / 128) AS 'Total Size in MB'
, SUM(size / 128 - CAST(FILEPROPERTY(name , 'SpaceUsed') AS int) / 128) AS 'Available Space In MB'
FROM
sys.database_files
WHERE
type_desc = 'ROWS'
Hi Suresh,
After running your script result:
DBNAMEDBIDTotal Size in MBAvailable Space In MB
TEST1040
and run sp_spaceused command result,
database_namedatabase_sizeunallocated space
TEST6.44 MB0.38 MB
As per my knowledge, if i increase the .mdf file size, it will increase the extents as per the rule of extents size for per mb. So why its not increasing here.
Required all suggestions.
Thanks in advance.
________________________________________
M.I.
[font="Times New Roman"]
March 13, 2012 at 9:59 am
MSQLDBA (3/13/2012)
After running your script result:DBNAMEDBIDTotal Size in MBAvailable Space In MB
TEST1040
and run sp_spaceused command result,
database_namedatabase_sizeunallocated space
TEST6.44 MB0.38 MB
Is this database originally from SQL 2000 version? If you run DBCC UPDATEUSAGE.
Any way run DBCC CHECKDB to see whether there is any issues in the DB.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply