SQL2K: MDF file size growing fast

[junOOni]

Hello,

I have got another annoying problem. The MDF file size on one of the machines is growing really fast. We zip the mdf/ldf files every day from all the machines in the dataentry dept. On this particular machine, the mdf file size is growing by about 1GB per day. However, when the file is zipped, the zipped file size comes closer to the zipped files from the other machines.

I have tried doing this:

http://www.sql-server-performance.co...sql_server.asp

on it as well, but didn't solve my problem.

Any ideas as to what it might be? and how to solve this problem?

Thanks in Advance.
J!

[skhanal]

First find out whether MDF or LDF is growing. LDF growth may be due to long running transaction and infrequent log backup. MDF growth means you are adding data and there is no way to compress it if you are not deleting data.

You can run dbcc showcontig on all tables and see if how pages are used. If there are lots of fragmented tables then you can rebuild the clustered index, then shrink the database.

You should not zip ldf/mdf files of live database, SQL Server will not recognize the zipped files.

[rmiao]

Why don't backup db then zip backup file?

[Owais]

I think it could be the deleted transaction which still occupies the space in mdf. The deleted space can be released by using the shrink command. Can someone calarify that.

Thanks

[Owais]

Why don't backup db then zip backup file?

Not a bad idea.

However I think Light speed can also be used to compress the backup. Did some one had experience with that?

[rmiao]

Yes but you have to pay for it.

[junOOni]

The reason why we just zip the mdf/ldf is that restoring from .bak files take a bit longer. However, with unzipping it doesn't take that long, and once unzipped the db is just attached.

I have shrunk the db as well. It did give me some space. However, it didn't really reduce to the size that it should reduce to. i.e. The problematic file was about 8 gb, and when shrunk, it came down to 7.5gb. However, the file should be in 1-2 gb range tops.

I ran DBCC Showcontig on this one. The tables are not that fragmented. Better than the tables in the "ok" database. I still rebuilt the clustered indexes and shrunk the database again. Still no gain!.

[rmiao]

What's fill factor? By the way, you can zip backup file.

[junOOni]

I ran the following query against the databases:

SELECT sysobjects.name,
sysindexes.name,
INDEXPROPERTY(OBJECT_ID(sysobjects.name),sysindexe s.name,'IndexFillFactor')
FROM sysobjects INNER JOIN
sysindexes ON sysobjects.id = sysindexes.id
WHERE xtype = 'U'

found:

Fillfactor on one of the "ok" databases: 90
Fillfactor on problematic database: 50

[rmiao]

Change it if don't have that many new rows between rebuild indexes.

[junOOni]

So, having smaller fillfactor will take more space?

How do i change the fillfactor?

Also, can i just change the fillfactor just like that? do i have to then reorganize the pages or something?

Thanks,
J

[rmiao]

Fill factor 50 means leave 50% free space on every index page, while 90 means leave 10% free space.