Determine SQL Server Database Size

Question

SQL Server 2005 2008 Express edition has the limitation of 4 GB per database  As far as I known the database engine considers data only  thus excluding log files  unused space  and index size   Getting the length of the MDF file should not give the correct database size in terms of SQL Server limitation  My question is how to get the database size

User · Answer

I always liked going after it directly   SELECT      DB NAME  dbid   AS DatabaseName       CAST    SUM  size     8       1024 0   1024 0   AS decimal  10  2     AS DbSizeGb  FROM      sys sysaltfiles  GROUP BY      DB NAME  dbid

User · Answer

According to SQL2000 help  sp spaceused includes data and indexes   This script should do   CREATE TABLE  t  name SYSNAME  rows CHAR 11   reserved VARCHAR 18    data VARCHAR 18   index size VARCHAR 18   unused VARCHAR 18    EXEC sp msforeachtable  INSERT INTO  t EXEC sp spaceused        -- SELECT   FROM  t ORDER BY name -- SELECT name  CONVERT INT  SUBSTRING data  1  LEN data -3   FROM  t ORDER BY name SELECT SUM CONVERT INT  SUBSTRING data  1  LEN data -3    FROM  t DROP TABLE  t

User · Answer

sp helpdb   no looping needed  unlike sp spaceused

User · Answer

In SQL Management Studio  right-click on a database and select  Properties  from the context menu  Look at the  Size  figure

User · Answer

You could use this old fashioned one as well     set ANSI NULLS ON set QUOTED IDENTIFIER ON go  DECLARE  iCount int   iMax int   DatabaseName varchar 200    SQL varchar  8000   Select NAME  DBID  crdate  filename  version  INTO  TEMP from MAster  SYSDatabASES   SELECT  iCount   Count DBID  FROM  TEMP  Select  SQL  Create Table   iFile1   DBName varchar  200  NULL  Fileid INT  FileGroup int  TotalExtents INT   USedExtents INT    Name varchar 100   vFile varchar  300   AllocatedSpace int NUll  UsedSpace int Null  PercentageFree int Null      char 10  exec   SQL    Create Table   iTotals   ServerName varchar 100   DBName varchar  200  NULL  FileType varchar 10  Fileid INT  FileGroup int  TotalExtents INT   USedExtents INT    Name varchar 100   vFile varchar  300   AllocatedSpace int NUll  UsedSpace int Null  PercentageFree int Null      WHILE  iCount gt 0 BEGIN         SELECT  iMax  Max dbid  FROM  TEMP     Select  DatabaseName   Name FROM  TEMP where dbid   iMax      SELECT  SQL    INSERT INTO   iFile1 Fileid   FileGroup   TotalExtents    USedExtents    Name   vFile       EXEC    USE       DatabaseName       DBCC showfilestats           char 10       Print    SQL      EXEC   SQL        SELECT  SQL    UPDATE   iFile1 SET DBName        DatabaseName      WHERE DBName IS NULL      EXEC    SQL        DELETE FROM  TEMP WHERE dbid   iMax     Select  iCount   iCount -1 END UPDATE   iFile1 SET AllocatedSpace    TotalExtents   64 0   1024 0    UsedSpace   USedExtents   64 0   1024 0    UPDATE   iFile1 SET PercentageFree   100-Convert float UsedSpace  Convert float AllocatedSpace      100 WHERE USEDSPACE gt 0  CREATE TABLE  logspace      DBName varchar  100      LogSize float     PrcntUsed float     status int      INSERT INTO  logspace EXEC   DBCC sqlperf  logspace       INSERT INTO   iTotals ServerName  DBName  FileType Name  vFile PercentageFree AllocatedSpace  select   ServerName  DBNAME    Data  as FileType Name  vFile  PercentageFree    AllocatedSpace from   iFile1 UNION select   ServerName  DBNAME   Log  as FileType  DBName    as vFile  PrcntUsed    LogSize from  logspace  Select   from   iTotals  select ServerName  DBNAME   FileType  Sum  AllocatedSpace  as AllocatedSpaceMB from   iTotals Group By  ServerName  DBNAME  FileType Order By  ServerName  DBNAME  FileType   select ServerName  DBNAME   Sum  AllocatedSpace  as AllocatedSpaceMB from   iTotals Group By  ServerName  DBNAME Order By  ServerName  DBNAME    drop table   iFile1 drop table  logspace drop table  TEMP drop table   iTotals

User · Answer

According to SQL2000 help  sp spaceused includes data and indexes   This script should do   CREATE TABLE  t  name SYSNAME  rows CHAR 11   reserved VARCHAR 18    data VARCHAR 18   index size VARCHAR 18   unused VARCHAR 18    EXEC sp msforeachtable  INSERT INTO  t EXEC sp spaceused        -- SELECT   FROM  t ORDER BY name -- SELECT name  CONVERT INT  SUBSTRING data  1  LEN data -3   FROM  t ORDER BY name SELECT SUM CONVERT INT  SUBSTRING data  1  LEN data -3    FROM  t DROP TABLE  t

User · Answer

You could use this old fashioned one as well     set ANSI NULLS ON set QUOTED IDENTIFIER ON go  DECLARE  iCount int   iMax int   DatabaseName varchar 200    SQL varchar  8000   Select NAME  DBID  crdate  filename  version  INTO  TEMP from MAster  SYSDatabASES   SELECT  iCount   Count DBID  FROM  TEMP  Select  SQL  Create Table   iFile1   DBName varchar  200  NULL  Fileid INT  FileGroup int  TotalExtents INT   USedExtents INT    Name varchar 100   vFile varchar  300   AllocatedSpace int NUll  UsedSpace int Null  PercentageFree int Null      char 10  exec   SQL    Create Table   iTotals   ServerName varchar 100   DBName varchar  200  NULL  FileType varchar 10  Fileid INT  FileGroup int  TotalExtents INT   USedExtents INT    Name varchar 100   vFile varchar  300   AllocatedSpace int NUll  UsedSpace int Null  PercentageFree int Null      WHILE  iCount gt 0 BEGIN         SELECT  iMax  Max dbid  FROM  TEMP     Select  DatabaseName   Name FROM  TEMP where dbid   iMax      SELECT  SQL    INSERT INTO   iFile1 Fileid   FileGroup   TotalExtents    USedExtents    Name   vFile       EXEC    USE       DatabaseName       DBCC showfilestats           char 10       Print    SQL      EXEC   SQL        SELECT  SQL    UPDATE   iFile1 SET DBName        DatabaseName      WHERE DBName IS NULL      EXEC    SQL        DELETE FROM  TEMP WHERE dbid   iMax     Select  iCount   iCount -1 END UPDATE   iFile1 SET AllocatedSpace    TotalExtents   64 0   1024 0    UsedSpace   USedExtents   64 0   1024 0    UPDATE   iFile1 SET PercentageFree   100-Convert float UsedSpace  Convert float AllocatedSpace      100 WHERE USEDSPACE gt 0  CREATE TABLE  logspace      DBName varchar  100      LogSize float     PrcntUsed float     status int      INSERT INTO  logspace EXEC   DBCC sqlperf  logspace       INSERT INTO   iTotals ServerName  DBName  FileType Name  vFile PercentageFree AllocatedSpace  select   ServerName  DBNAME    Data  as FileType Name  vFile  PercentageFree    AllocatedSpace from   iFile1 UNION select   ServerName  DBNAME   Log  as FileType  DBName    as vFile  PrcntUsed    LogSize from  logspace  Select   from   iTotals  select ServerName  DBNAME   FileType  Sum  AllocatedSpace  as AllocatedSpaceMB from   iTotals Group By  ServerName  DBNAME  FileType Order By  ServerName  DBNAME  FileType   select ServerName  DBNAME   Sum  AllocatedSpace  as AllocatedSpaceMB from   iTotals Group By  ServerName  DBNAME Order By  ServerName  DBNAME    drop table   iFile1 drop table  logspace drop table  TEMP drop table   iTotals

User · Answer

According to SQL2000 help  sp spaceused includes data and indexes   This script should do   CREATE TABLE  t  name SYSNAME  rows CHAR 11   reserved VARCHAR 18    data VARCHAR 18   index size VARCHAR 18   unused VARCHAR 18    EXEC sp msforeachtable  INSERT INTO  t EXEC sp spaceused        -- SELECT   FROM  t ORDER BY name -- SELECT name  CONVERT INT  SUBSTRING data  1  LEN data -3   FROM  t ORDER BY name SELECT SUM CONVERT INT  SUBSTRING data  1  LEN data -3    FROM  t DROP TABLE  t

User · Answer

sp spaceused

User · Answer

In SQL Management Studio  right-click on a database and select  Properties  from the context menu  Look at the  Size  figure

User · Answer

According to SQL2000 help  sp spaceused includes data and indexes   This script should do   CREATE TABLE  t  name SYSNAME  rows CHAR 11   reserved VARCHAR 18    data VARCHAR 18   index size VARCHAR 18   unused VARCHAR 18    EXEC sp msforeachtable  INSERT INTO  t EXEC sp spaceused        -- SELECT   FROM  t ORDER BY name -- SELECT name  CONVERT INT  SUBSTRING data  1  LEN data -3   FROM  t ORDER BY name SELECT SUM CONVERT INT  SUBSTRING data  1  LEN data -3    FROM  t DROP TABLE  t

User · Answer

sp spaceused

User · Answer

The best solution is maybe to calculate the size of each database file  using the sys sysfiles view  considering a size of 8 KB for each page  as follows   USE  myDatabase  GO  SELECT      size    8        filename  FROM sysfiles   The  field  column represents the size of the file  in pages  MSDN Reference to sysfiles    You would see there will be at least two files  MDF and LDF   the sum of these files will give you the correct size of the entire database

User · Answer

The best solution is maybe to calculate the size of each database file  using the sys sysfiles view  considering a size of 8 KB for each page  as follows   USE  myDatabase  GO  SELECT      size    8        filename  FROM sysfiles   The  field  column represents the size of the file  in pages  MSDN Reference to sysfiles    You would see there will be at least two files  MDF and LDF   the sum of these files will give you the correct size of the entire database

User · Answer

In SQL Management Studio  right-click on a database and select  Properties  from the context menu  Look at the  Size  figure

User · Answer

sp spaceused

User · Answer

sp helpdb   no looping needed  unlike sp spaceused

User · Answer

Common Query To Check Database Size in SQL Server that supports both Azure and On-Premises-  Method 1     Using    sys database files    System View  SELECT     DB NAME   AS  database name       CONCAT CAST SUM          CAST   size   8 0 1024  AS DECIMAL 15 2          AS VARCHAR 20     MB   AS  database size  FROM sys database files    Method 2     Using    sp spaceused    System Stored Procedure  EXEC sp spaceused

User · Answer

sp spaceused

User · Answer

I always liked going after it directly   SELECT      DB NAME  dbid   AS DatabaseName       CAST    SUM  size     8       1024 0   1024 0   AS decimal  10  2     AS DbSizeGb  FROM      sys sysaltfiles  GROUP BY      DB NAME  dbid

User · Answer

In SQL Management Studio  right-click on a database and select  Properties  from the context menu  Look at the  Size  figure

User · Answer

Common Query To Check Database Size in SQL Server that supports both Azure and On-Premises-  Method 1     Using    sys database files    System View  SELECT     DB NAME   AS  database name       CONCAT CAST SUM          CAST   size   8 0 1024  AS DECIMAL 15 2          AS VARCHAR 20     MB   AS  database size  FROM sys database files    Method 2     Using    sp spaceused    System Stored Procedure  EXEC sp spaceused

[sql-server] Determine SQL Server Database Size

Examples related to sql-server

Examples related to tsql