Limit table size

Is there a way to limit a tables size in a database?

Davids Learning

hi,

table size, or database size, is physicaly limited by storage systems.. but SQLExpress has another software upper limit, and a database can not exceed 4gb in the data files sum...

but you can not provide a "limit" for a table... you can perhaps define some constraint to limit the row number stored by the table in a particular range, but the actual "size" of the table will depend on the table design, on the index(es) implementation and the like..

what are you trying to do?

regards

|||

I am wanting to limit how many entries are in this log, i would like to keep the last 200 entries from current to back

Cant that be done?

Davids Learning

|||

you can define a stored procedure, and perform deletetion if there are more rows then you like to be stored, or, a trigger FOR INSERT.. both methods as

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.TestLog (

Id int NOT NULL IDENTITY PRIMARY KEY,

Data varchar(10) NOT NULL

);

GO

CREATE PROCEDURE dbo.Add2Log (

@.Data varchar(10)

)

AS BEGIN

INSERT INTO dbo.TestLog VALUES (@.Data);
DECLARE @.r int;

SELECT @.r = COUNT(*) FROM dbo.TestLog;

IF @.r > 5 BEGIN

PRINT 'Deleting older data';

DELETE FROM dbo.TestLog

WHERE Id NOT IN (SELECT TOP 5 Id FROM dbo.TestLog ORDER BY Id DESC);

END;

END;

GO

PRINT 'controlling via stored procedure';

DECLARE @.i int, @.Data varchar(10);

SET @.i = 1;

WHILE @.i < 21 BEGIN

SET @.Data = CONVERT(varchar, @.i);

EXEC dbo.Add2Log @.Data = @.Data;

SET @.i = @.i + 1;

END;

SELECT * FROM dbo.TestLog;

GO

DROP PROCEDURE dbo.Add2Log

GO

CREATE TRIGGER tr_I_TestLog ON dbo.TestLog

FOR INSERT

AS BEGIN

IF @.@.ROWCOUNT = 0 RETURN;

DECLARE @.r int;

SELECT @.r = COUNT(*) FROM dbo.TestLog;

IF @.r > 5 BEGIN

PRINT 'Deleting older data';

DELETE FROM dbo.TestLog

WHERE Id NOT IN (SELECT TOP 5 Id FROM dbo.TestLog ORDER BY Id DESC);

END;

END;

GO

PRINT 'controlling via INSERT trigger';

DECLARE @.i int;

SET @.i = 100;

WHILE @.i < 121 BEGIN

INSERT INTO dbo.TestLog VALUES (CONVERT(varchar, @.i));

SET @.i = @.i + 1;

END;

SELECT * FROM dbo.TestLog;

GO

DROP TABLE dbo.TestLog;

regards