| Author: chandramohan 30 Jun 2008 | Member Level: Gold | Rating:  Points: 1 |
statement without using identity fieldGet Rows where float column values is not a whole number »Finding Total Space Used and Free Space in Each Data File of a database
If you want to find the size of each data file you database is using then you can use the command
DBCC SHOWFILESTATS;
you will get one row for each of your data file for the current database. Look for the column TotalExtents and multiply its value by 64 to get the total file size in KB. Reason for 64 is that each Extent consists of 8 pages and size of each page is of 8KB on disk.
Another column UsedExtents displays the number of extents used. Get its value in KB by multiplying it by 64 and then subtract the result from total file size calculated above to get free space left.
Following is the example code which creates a TestDatabase first and then show its file statistic, it then drops the newly created database.
Use master;
Go
CREATE DATABASE [TestDatabase] ON PRIMARY
( NAME = N‘TestDatabase1', FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDatabase1.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),
( NAME = N‘TestDatabase2', FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDatabase2.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N‘TestDatabase_log’, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDatabase_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)
Go
Use TestDatabase;
Go
DBCC SHOWFILESTATS;
Go
Use master;
Go
drop database TestDatabase;
Go
|
| Author: www.DotNetVJ.com 30 Jun 2008 | Member Level: Diamond | Rating: Points: 6 |
Hi
SQL Server has a handy little system stored procedure named sp_spaceused that will return the space used by a database or by a particular table. To determine the size used by the database, simply run:
EXEC sp_spaceused
This will return two result sets, the first one containing the database name, size, and unallocated space and the second containing a breakdown of the database's size into how much size is reserved and how much of that is taken up by data, how much by indexes, and how much remains unused.
Check out the below link for more information
http://www.4guysfromrolla.com/webtech/032906-1.shtml
Thanks -- Vj
http://dotnetvj.blogspot.com
Thanks -- Vijaya Kadiyala
http://www.DotNetVJ.com
Microsoft MVP
Me & My Little Techie
|
| Author: Bunty 01 Jul 2008 | Member Level: Diamond | Rating: Points: 3 |
Hi,
You can use the system defined stored procedure to calculate the size of the database.
EXEC sp_spaceused
Execute the above query and you will get the desired ouput.
Thanks and Regards
S.S.Bajoria
Thanks & Regards
S.S.Bajoria
|
| Author: Sriram 01 Jul 2008 | Member Level: Gold | Rating: Points: 0 |
exec sp_spaceused
|
| Author: Ratheesh 01 Jul 2008 | Member Level: Gold | Rating: Points: 1 |
you will get one row for each of your data file for the current database. Look for the column TotalExtents and multiply its value by 64 to get the total file size in KB. Reason for 64 is that each Extent consists of 8 pages and size of each page is of 8KB on disk.
Another column UsedExtents displays the number of extents used. Get its value in KB by multiplying it by 64 and then subtract the result from total file size calculated above to get free space left.
Following is the example code which creates a TestDatabase first and then show its file statistic, it then drops the newly created database.
Use master;
Go
CREATE DATABASE [TestDatabase] ON PRIMARY
( NAME = N‘TestDatabase1', FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDatabase1.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),
( NAME = N‘TestDatabase2', FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDatabase2.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N‘TestDatabase_log’, FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDatabase_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)
Go
Use TestDatabase;
Go
DBCC SHOWFILESTATS;
Go
Use master;
Go
drop database TestDatabase;
Go
|