MDF file increase abnormally - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

MDF file increase abnormally

Author

Topic

Sharif Rashidul Hasan
Starting Member

1 Post

Posted - 2011-11-19 : 05:03:41

I am using SQL server 2005. recently found that mdf and ldf file increasing abnormally. I have shrink it. afetr that ldf file does not increase , but mdf file increasing abnormally though there is no transaction made.

anyone can help me in this issue so that the mdf file does not increase abnormally? the file size should not be maximum 400-500 mb,where as it is getting increased 35GB.

james_wells
Yak Posting Veteran

55 Posts

Posted - 2011-11-19 : 18:27:04

From your comments is hard to gage what the issue is.

Check ghost records are being cleared down should be done by a back ground process.

Check table usage in case there are tables storing such things as NTEXT fields. Low number of records but huge volumes of data is a good start.

Have known databases not to shrink due to the lack of clustered indexes and primary keys. If there is a large free space in the database and the shrink file does not release the space then this is the issue described above.

Had this problem myself added clusterd indexes to heaps without primary keys , shrunk the file and then removed the indexes.
Every other attempt to claim the free space failed.

--------------------------------------------------------
-- ghost records
--------------------------------------------------------

SELECT
DB_NAME (database_id) AS database_name,
--OBJECT_NAME (a.object_id) AS object_name,
b.name,
a.index_type_desc AS index_type,
a.alloc_unit_type_desc AS alloc_unit_type,
a.page_count,record_count,ghost_record_count,
a.version_ghost_record_count,forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID ('<DatabaseName>'),
OBJECT_ID ('<TableName>'), NULL, NULL, 'DETAILED') as a
join sys.objects b on a.object_id = b.object_id
where a.ghost_record_count > 0 or
a.forwarded_record_count > 0

------------------------------------------------
-- table usage
------------------------------------------------

USE DATABASE
GO

BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE @tab1 TABLE
(
tablename VARCHAR (500) collate database_default,
schemaname VARCHAR(500) collate database_default
);

DECLARE @temp_table TABLE
(
tablename sysname ,
row_count INT ,
reserved VARCHAR(50) collate database_default,
data VARCHAR(50) collate database_default,
index_size VARCHAR(50) collate database_default,
unused VARCHAR(50) collate database_default
);

INSERT INTO @tab1
SELECT
t1.name,
t2.name
FROM sys.tables t1
INNER JOIN sys.schemas t2
ON ( t1.schema_id = t2.schema_id );

DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name
FROM sys.tables t1
INNER JOIN sys.schemas t2
ON ( t1.schema_id = t2.schema_id );

OPEN c1;
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');

-- make sure the object exists before calling sp_spacedused

IF EXISTS(SELECT OBJECT_ID FROM sys.objects
WHERE OBJECT_ID = OBJECT_ID(@table_name))
BEGIN
INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
END

FETCH NEXT FROM c1 INTO @table_name;

END;

CLOSE c1;
DEALLOCATE c1;

SELECT
t1.tablename,
t1.row_count,
-- t1.reserved,
-- t1.data,
-- t1.index_size,
-- t1.unused,
cast(replace(t1.reserved, 'kb', '') as decimal(10,2)) as 'Reserved',
cast(replace(t1.data, 'kb', '') as decimal(10,2)) as 'Data',
cast(replace(t1.index_size, 'kb', '') as decimal(10,2)) as 'Index',
cast(replace(t1.unused, 'kb', '') as decimal(10,2)) as 'Unused',
t2.schemaname
FROM @temp_table t1
INNER JOIN @tab1 t2
ON (t1.tablename = t2.tablename )
ORDER by cast(replace(t1.data, 'kb', '') as decimal(10,2)) desc

--ORDER BY schemaname,tablename;

END try

BEGIN catch
SELECT
-100 AS l1,
ERROR_NUMBER() AS tablename,
ERROR_SEVERITY() AS row_count,
ERROR_STATE() AS reserved,
ERROR_MESSAGE() AS data,
1 AS index_size,
1 AS unused,
1 AS schemaname
END catch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources