Posts

Showing posts from October, 2011

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.
namenvarchar(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.rowschar(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.r…

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 …