Database size 1

[grabrail]

I need to run a T-SQL query to show the size of the Datasbe and Log file size. The results will then be outputted to a text file.

I know how to do the latter part, but what is the query I would need to get the results.

This is the script I have run so far:

use database
insert into dbsize (name,[size MB],date)
select filename,(size),getdate()
from {databasename}.dbo.sysfiles
go

This does not give me the database size, as the database is 68gb, however this output gives me 82gb.

Where am I going wrong.

Thanks in advance
Jade

 

[SQLBill]

First run the command DBCC UPDATEUSAGE on the database(s). Then run the stored procedure SP_SPACEUSED.

The first will update the data and the second will return the size of data, indexes, etc. You could also copy the sp_spaceused file and make changes to return only what you want.

Refer to the BOL for more information on those two commands.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[grabrail]

Thanks SQLBill

Thats cool but do you know why sysfiles table contains one size and the actual physical size of files And the size reported by sp_spaceused are different?

Thanks

Jade

 

[SQLBill]

Per Using Microsoft SQL Server by Kalen Delany...

The stored procedure sp_spaceused examines these values [from sysindexes] and reports the total size used by a table. Keep in mind that these values in sysindexed are not updated every time a table or index is modified. In particular, immediately after you create a table and after some bulk operations, sp_spaceused might not accurately reflect the total space because the values in sysindexes are not accurate.

You should always run DBCC UPDATEUSAGE or use the @updateusage parameter in the SP_SPACEUSED command.

-SQLBill