Crazy file size on SQL Server

DenSter

I have a customer whose database file is about 700 MB when restoring into a C/SIDE database file. When I use the same backup file to create a new SQL Server database, that same database file size is about 1.5 GB, so about twice as big. The kicker is that my customer's production database, the one that they created the backup from, is 28 GB!! He says he ran the optimization on all tables, so there's nothing to be gained there.

Does anyone know how this might have happened, and possibly how to reduce the size back to a manageable size? They are moving their production database over to a new hosted server, and if they can recuce the production database size to the 1.5 GB, the cost is going to be about half of what the current size would cost.

ara3n

what are the size of
ldf
ndf
mdf

and how much free space does each have?

They could do a navision backup, and restore it in a new db.

I'm guessing the log file could be the reason, or they have a maintance job that changes the large filfactor on index nightly?

If it's the same database that they went live on, they probably had test companies etc that they deleted afterwards.

Marije_Brummel

I would also have a look at the empty space of the SQL files and the default fill factor.

Are there manualy created (large) SQL tables or indexes in the database that Navision would not backup?

DenSter

Guys... it's me... I checked that already The 28GB is the used part of the NDF file. The used part of the new NDF is about 1.5GB. They don't have anything but NAV data in the database. I doubt that they created indexes manually, since they needed my help to create a new database.

The thing is, they are going to create a new database on 25GB drive space (I told them to use 100GB but that is too expensive), so in my mind it's only a matter of time before they grow out of that space. If I can figure out why it grew and what to do about it, I can help them keep control of the file sizes.

What could have caused the growth? How can I fix it?

Marije_Brummel

Is there any customistation in the database that does a RENAME of a record? This can cause a huge transaction to roll-back.

DenSter

Wouldn't that be in the TL though? The TL is another 17GB, and that gets cleared when they do a full backup.

anilkumar

hi! DenSter,

what is the size of log file - ?, may be that is one reason or try to shrink database, then check the size of database.


Anil Kumar.

Waldo

Just a long shot:
What about the "zero sift records"? Did you check these with the toolkit?

You say that the 28 Gb is the used part of the NDF file, so shrinking the database won't give much of a difference.

For analysing the data, this can be useful:
To get a list of tables with largest rowcount:

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC

You can use this list of tables, and analyse the size of the tables with the statement:

exec sp_spaceused 'tablename'

And before you do that, I would use this statement to update the usages. It will make sure you get correct information from the "sp_spaceused"-stored procedure:

DBCC UPDATEUSAGE

Miklos_Hollender

Sorry if it is stupid but why not just stop the server after business hours, create a backup and restore it to the new server? Shouldn't take more than a few hours.

ara3n

I think the question was asked to seek the cause for the big file size. I think Denster already mentioned there are several solution to fix it.

DenSter

Miklos Hollender wrote:

Sorry if it is stupid but why not just stop the server after business hours, create a backup and restore it to the new server? Shouldn't take more than a few hours.

Because that doesn't solve the problem. If there is a reason within the database, then restoring to a new server initially will start with a small file, but will then expand rapidly anyway, and you end up with the same size issue.

Your "solution" does not address the problem, so therefore it is not a solution. Over here we call that a band-aid. You're treating a symptom, not the problem.

Waldo

DenSter wrote:

Miklos Hollender wrote:

Sorry if it is stupid but why not just stop the server after business hours, create a backup and restore it to the new server? Shouldn't take more than a few hours.

Because that doesn't solve the problem. If there is a reason within the database, then restoring to a new server initially will start with a small file, but will then expand rapidly anyway, and you end up with the same size issue.

Your "solution" does not address the problem, so therefore it is not a solution. Over here we call that a band-aid. You're treating a symptom, not the problem.

Just curious, but did you analyse the data on usage per table?

ara3n

Hello. I ran into the same problem today for one of our client. The db is 3 gig, but the log file was 68 gig. This is 4.0 sp2. SQL 2K5.
I did notice that in the log file the unrestricted growth was unchecked and the max size was set to 60 gig.

hmmm. I didn't have time to look into detail. So i had to do a back and restore.
I tried to shrink the log file, but nothing.
This could be a sql bug.

Marije_Brummel

Are Denster and you both using the same add-on or component from Tectura?

There might be some piece of code doing a crazy change on the database

Waldo

ara3n wrote:

Hello. I ran into the same problem today for one of our client. The db is 3 gig, but the log file was 68 gig. This is 4.0 sp2. SQL 2K5.
I did notice that in the log file the unrestricted growth was unchecked and the max size was set to 60 gig.

hmmm. I didn't have time to look into detail. So i had to do a back and restore.
I tried to shrink the log file, but nothing.
This could be a sql bug.

Shrinking the log can result into nothing ... I usually always have success with putting the recocvery model to "simple", then shrinking, then putting back to "full"... after full backup off course...

ara3n

Mark Brummel wrote:

Are Denster and you both using the same add-on or component from Tectura?

There might be some piece of code doing a crazy change on the database

Nope no addon.

If you have setup a nightly plan to reindex the keys, are they written to log file?

Marije_Brummel

Your maintenance should run on a simple recoverymodel.

This is one of the default options in the SQL Perform tools.

Waldo

You can do it with some T-SQL statements as well

ALTER DATABASE [Your_DB] SET RECOVERY SIMPLE WITH NO_WAIT

--Do your thing

ALTER DATABASE [Your_DB] SET RECOVERY FULL WITH NO_WAIT

Marije_Brummel

Yes, that is true, but after the maintenanceplan you want to restore the previous value which can be different per customer.

DenSter

ara3n wrote:

Hello. I ran into the same problem today for one of our client. The db is 3 gig, but the log file was 68 gig. This is 4.0 sp2. SQL 2K5.
I did notice that in the log file the unrestricted growth was unchecked and the max size was set to 60 gig.

hmmm. I didn't have time to look into detail. So i had to do a back and restore.
I tried to shrink the log file, but nothing.
This could be a sql bug.

No that is not the same problem. My customer's size issue is not in the Transaction Log file.

To truncate the log file you either do a TL backup or you truncate it manually, like this:

backup log MyDatabase with truncate_only

After this, the TL file should be mostly empty. I would personally not shrink it, because that will only frgment the file, and it's going to grow anyway.

To automatically have the log truncated you either set up a log backup in a maintenance plan (if you're running on full recovery model) or a full backup should take care of it (if you're running on simple recovery model).

Slawek_Guzek

Hi,

I would suspect some customization involving new or modified index with SIFT fields, including one or more DATETIME variables, and without properly set SIFT Levels to maintain.

In such a case every DATETIME field gives you 7 SIFT levels, so on some big table can gives you HUGE SIFT table.

But in this case just after restoring backup the database should be quite big. On the other side frequent insert or modifications on such a table causes many many pages in SQL file do get dirty, so database grows quite fast.

Regards,
Slawek

Waldo

I really wonder what the sizes are of the tables ... if you could find the time to investigate that, you'd have a lead on where to find it in your customizations ...

DenSter

You know I could spend days trying to find that out, and it would be much less expensive for the customer to just get a couple of extra disks...

Waldo

Oh ... OK :-k

Administrator

[Topic moved from Navision to SQL General forum]

Waldo

Just FYI.

You can easily check the file size per table via a built-in report in SQL2005.

In SQL Server Management Studio, select the database in the object explorer. Click "Report" in the summary. In that report, expand "Disk Space Used by Tables".

Hope it's useful.

DenSter

Thanks I had found that one too. This customer has moved to a new server, and has just come back around to me with the same issue, so I'll have the opportunity to check again. I'll come back with what I found.