Size of Database is Extremely large 1

[JohnBates]

Hello SQL Server experts -

My new year is going to start out the wrong way if I can't
find out what is taking up so much space in my SQL Server 7
user database.

My frigdare database is on Server1 and is replicated to
Server2.... so it has always been approx. the same size on both servers until now.

In Enterprise Managager:
For Server1, database properties show that
frigdare.mdf has 412 MB allocated and 124 MB free
frigdare.ldf (transaction log) is using 9MB


For Server2, database properties show that
frigdare.mdf has 1379 MB allocated and 86 MB free
frigdare.ldf (transaction log) is using 7MB

**********************************************
So, on Server2, frigdare.mdf is approx. 1.34 GB
which is about 900 MB more than it should be.
**********************************************
Backups (the .BAK files) are written to another drive so they are not part of the user db.

The Tables and Indexes are approx. the same size
on both servers. Row counts match.

Sometimes, it is the Transaction log that is eating up space but that is not so in this case.

The frigdare Properties just don't indicate where the problem is, but there is big hole somewhere and I need to find it soon.

Again, SQL Server 7, using replication.

I really appreciate any tips you can give me as to where to look for the problem.
Thanks, John

 

[RickCole]

Try running DBCC DBREINDEX on all your tables to ensure you have no fragmentation within your databases.

Rick.

 

[SQLBill]

Since you say the row counts and table sizes on both databases match, but that the 'allocated' space is different....my first thought would be that the Data Files - File Growth size is set different for the two tables. This would cause one database to expand and allocate a different amount of space than the other database. Check this using Enterprise Manager, expand down to your database, mouse right on your database and select 'Properties'. Then go to the 'Data Files' tab and check 'File Growth' size. This should be the same for both of your databases. Check this on the 'Transaction Log' tab also.

-Bill

 

[JohnBates]

Thanks RickCole and SQLBill -

Rick, I will run the DBREINDEX this weekend, when system is not in use.

Bill the File Growth is set to 10 Percent (rather than a number of records)on both servers.

I'm convinced the big black hole is related to replication...

On the server being replication TO (the one that is nearly
out of space):
msmerge_tombstone has 5 million rows (the other server
has 1 million)

*************************************************8
I do not have AutoShrink enabled but do perform a DBCC
SHRINKDATABASE each weekend.

The Retention Period is 60 days. I want to change that to 30 days, then run the SHRINKDATABASE again - think that will
free up alot of disk space.

NEW QUESTION: When I run the sp_changemergepublication (to change the Retention Period to 30 days),
I'm not sure how to format the syntax.. my publication nam is 'frigdare' and the property I want to cahng is 'retention' with value being '30'

Would this be correct...?
exec sp_changemergepublication @publication = 'frigdare',
@property = 'retention', @value = '30'

*****************************************

Thanks for your assistance ! John

 

[JohnBates]

Here's an update on where things stand now:


I was able to run the sp_changemergepublication stored proc and get the Retention Period changed to 30 days.

"Try running DBCC DBREINDEX on all your tables to ensure you have no fragmentation within your databases."....

When I run DBCC DBREINDEX on the user tables to eliminate fragmentation, will that also reduce the amount of space the db is using... or will it simply make the db perform more efficiently? According to SQL Server tools I used, this database is 51 % fragmented, so the DBREINDEX definitely needs to be done. Also I think I read in BooksOnLine that you cannot run DBREINDEX on system tables such as sysmerge_tombstone (which has 5 million rows in this db)...is this correct ?

I've trimmed it down abit, but still looking for a trick that will recover another 100mb or so.

Tahnks for everyone's help. John

 

[RickCole]

As well as making your db more efficient the REINDEX will reduce the space being used within the db but not the total space allocated. Once you have reduced the space being used you can modify the total space allocated with Enterprise Manager or the ALTER DATABASE statement.

In addition your right in one sense that DBCC DBREINDEX can not be run on system tables. However a quirk with looking at the tables via Enterprise Manager is that it does not always tell you the full story.

If you say look at the tables in the master database via EM and order by name you will see (as an example) the table MSreplication_options. Even though EM states this is a system table DBCC DBREINDEX will quite happily run on this table. However on something like sysobjects it will not.

Easiest thing is to run the REINDEX on your table and the system will tell you if it’s not possible. If you can’t, and sysmerge_tombstone is fragmented. You can do the following although not having done much replication I would make sure I would have all necessary backups before attempting this:

Script the full table definition of syserge_tombstone and create a new table with a different name.

Insert the data from sysmerge_tombstone into the new table.

Drop the table sysmerge_tombstone and rename your new table to sysmerge_tombstone. This is the part I would be concerned about. But this will de-frag your sysmerge_tombstone table.

Rick.

 

[JohnBates]

OK Rick, I performed DBREINDEX on a few of the system tables that are related to merge-replication....
MSmerge_contents, MSmerge_genhistory and MSmerge_tombstone
After doing this and a few other things, the drive had only 10 MB of free space. I don't know how NT and SQL Server
were able to keep running....

Then I followed up with a SHRINKDATABASE step. The job failed after running 25 minutes due to 'Disk is full' condition.
Restarted the SHRINKDATABASE step; this time it completed
successfully.

RESULTS: The db is now only 400 MB in size; now have 1 GB
of avail. space on the drive.

Life is good again.

So now I will add the DBREINDEX step to my Shrink db job
which runs weekly.

Thanks to everyone that submitted ideas. John