Determining the Space Used by all tables in a Database?

Determining the Space Used by all tables in a Database

Lets create one temporary table to store the output of stored procedure ‘SP_MSFOREACHTABLE’.

CREATE TABLE #tbl_SPACETABLE(NAME VARCHAR(100),ROWS INT,RESERVED VARCHAR(100),DATA VARCHAR(100),INDEXSIZE VARCHAR(100),UNSUSED VARCHAR(100))

Insert the stored procedure ‘SP_MSFOREACHTABLE ‘ results by using below command.

INSERT INTO #tbl_SPACETABLE EXEC SP_MSFOREACHTABLE @COMMAND1="EXEC SP_SPACEUSED '?' "

It will insert below columns in the table #tbl_SPACETABLE.

name	nvarchar(128)	Name of the object for which space usage information was requested. The schema name of the object is not returned. If the schema name is required, use the sys.dm_db_partition_stats or sys.dm_db_index_physical_stats dynamic management views to obtain equivalent size information.
rows	char(11)	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	varchar(18)	Total amount of reserved space for objname.
data	varchar(18)	Total amount of space used by data in objname.
index_size	varchar(18)	Total amount of space used by indexes in objname.
unused	varchar(18)	Total amount of space reserved for objname but no yet used.

SP_SPACEUSED stored procedure is used to find the space used by objects in

a database.

? is passed as argument for sp_spaceused stored procedure to retrieve space for all tables in a database.

Fetch the space used by all tables in a database by runnning below query

SELECT * FROM #tbl_SPACETABLE

If you need to fetch space for single table,then use below query.

SP_SPACEUSED 'TABLE_NAME'

Hope you will find the space of all tables easily.

---

Replication Error (Source: MSSQLServer, Error number: 2627).

Error in the Replication monitor:

Command attempted:

{CALL sp_MSins_dboXXXXX (2365938, '"Set PROCESSED=(2,-20) ON CREATEs WHERE SerID

already exists in [subscriberdatabase]" completed:', {ts '2011-09-18 08:07:56.537'}, {ts '2011-09-18 08:07:56.537'}, NULL, NULL)}

(Transaction sequence number: 0x00023911000035F3000300000000, Command ID: 1)

Error messages:

Violation of PRIMARY KEY constraint 'PK_tablename-table'. Cannot insert duplicate key in object

‘Tablename-table''. (Source: MSSQLServer, Error number: 2627)

Get help: http://help/2627

Resolutions Steps:

Go to subscriber database

Run below query.

1. Select * from [dbo].[Tablename-table] where rowid>=2365938

Then delete the records by executing below query.

delete * from [dbo].[Tablename-table] where rowid>=2365938

2. Finally restart the distribution job.

Replication was failing due to a "Primary Key violation error". We found certain entries in the subscriber, which were not marked as replicated in the publisher. Hence the entries were removed and replication was started again.

Replication is in synch now.