In order to determine the space used by a database in SQL Server we use the following command
The above command, on execution gives two tables as output. First table shows the information on
database_name Name of the current database.
database_size Size of the current database in megabytes. database_size includes both data and log files.
unallocated space Space in the database that has not been reserved for database objects.
The second table gives the information on
reserved Total amount of space allocated by objects in the database
data Total amount of space used by data
index_size Total amount of space used by indexes
unused Total amount of space reserved for objects in the database, but not yet used
In case if we want to assess the space for any table in the database, we pass the name of the table as parameter. For example:
exec sp_spaceused @objname='test_nodrop.dbo.employee'
@objname is a qualified or nonqualified name of the table, indexed view, or queue for which space usage information is requested.
Output of the above command will be:
Name Name of the object for which space usage information was requested.
rows Number of rows existing in the table. If the object specified is a Service Broker queue, this column indicates the number of messages in the queue
reserved Total amount of reserved space for objname
data Total amount of space used by data in objname
index_size Total amount of space used by indexes in objname
unused Total amount of space reserved for objname but not yet used
Please note that finding the name of each table in the database would be a tiresome job. Instead, I use the following script, which will provide me the size used by all the tables in the database
declare @tabname varchar(400) ,@counts int ,@rowcounts int; declare @tab1 table( sno int identity(1,1) ,tablename varchar (200) ,schemaname varchar(200) ,twopartname varchar(400) ); declare @space_table table ( tablename varchar(200) , row_count int , reserved varchar(50) , data varchar(50) , index_size varchar(50) , unused varchar(50) ); insert into @tab1 (tablename, schemaname, twopartname) select st.name , ss.name , ss.name + '.' + st.name FROM sys.tables st INNER JOIN sys.schemas ss ON ( st.schema_id = ss.schema_id ); select @counts=count(sno) from @tab1 select @rowcounts=1 while (@rowcounts <= @counts ) begin select @tabname=twopartname from @tab1 where sno=@rowcounts select @tabname = replace(@tabname, '[',''); select @tabname = replace(@tabname, ']',''); insert into @space_table exec sp_spaceused @tabname, false ; select @rowcounts = @rowcounts + 1 end select t1.* , t2.schemaname from @space_table t1 inner join @tab1 t2 on (t1.tablename = t2.tablename ) order by schemaname,tablename;
I will keep posting all possible other details very soon
Explored Cloudagile testing andriod testing android bdd bdd specification Brocade CGW Citrix Citrix Receiver citrix xenapp citrix xendesktop Config Backup/restore cucumber specification DRAC esx Gesture Testing HSD Indexes Mobile Testing Challenges private root certificate registry secure gateway smartphone testing specification by example SQL 2008 Sql Server ssh ssh banner sshd SSL/TLS error test automation with specifications testing at thoughtworks thoughtworks testing VDA vmware WebInterface web interface WI windows7 Windows 2008 R2 xenapp xen app xen desktop xendesktop xenserver
Our Latest Tweets
Lets Discuss !!!
- September 2016 (1)
- April 2016 (1)
- October 2015 (1)
- July 2015 (2)
- March 2015 (2)
- December 2014 (1)
- April 2014 (5)
- December 2013 (1)
- July 2013 (1)
- May 2013 (1)
- April 2013 (1)
- February 2013 (1)
- October 2012 (2)
- September 2012 (1)
- August 2012 (1)
- June 2012 (1)
- May 2012 (1)
- April 2012 (3)
- March 2012 (4)
- February 2012 (1)
- December 2011 (1)
- November 2011 (2)
- October 2011 (2)
- September 2011 (8)
- August 2011 (3)
- June 2011 (3)
- February 2008 (1)