SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Express Edition and Compact Edition (2005)
 Database Backup Size Growing Fast
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kristian.martin
Starting Member

USA
8 Posts

Posted - 11/29/2011 :  13:49:08  Show Profile  Reply with Quote
Hello,

I am new to Microsoft SQL Databases, so forgive me if my explanations are not as detailed as they should be. I work mainly end-user IT support for my company. Here is my problem.

Our database was fixed out of corruption about a month ago. The size of the backups before the fix was about 400MB. After the fix (and subsequent re-installation of the SQL Server 2005 Express and related software), the database has grown daily in size. It is now over 1.4GB! I learned about transaction logs and such, and was instructed by Deltek to do a shrink and re-index. That only saved me about 50-100MB of space. I was also told to set the backup to SIMPLE, but it was already set this way.

Now, we uploaded a copy of the database to a tech and he said that there wasn't a problem with the database at all, but suggested to try and run the re-index and shrink on a NEW installation of SQL. However, when I asked him if this would decrease the size of the database, he said that it would not decrease the size to the levels that they were before (~400-450MB). Perhaps I am missing something, but where is all this fluff coming from, if not from the transaction logs (which shouldn't be backed up anymore)?

Can anyone give some insight? I am about to install SQL2005EXP in a VM and perform a restore from a backup and run the shrink and re-index again, but since he said it wouldn't change the size, I think there isn't really a point. I have read on these forums not to use the auto-shrink as well.

Thank you very much in advance for all your patience and kind help.

- Kristian

--EDIT--
It is 1.4 GB, not TB. My panic changed a letter!

Edited by - kristian.martin on 12/01/2011 12:51:21

Kristen
Test

United Kingdom
22431 Posts

Posted - 11/30/2011 :  19:50:54  Show Profile  Reply with Quote
Is there perhaps some "stale" data which is not being deleted?

Maybe previouosly you had a scheduled task which deleted "stale" data, and that is no longer (since your reinstall) scheduled? - in which case the database keeps growing because that data is never removed.

"on a NEW installation of SQL"

It won't make any difference doing it on a new installation of SQL. So, sorry, but unless there is some additional reason for having a new installation that advice is bollocks !
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37471 Posts

Posted - 11/30/2011 :  20:19:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
What are your file sizes for that database? Mdf/ldf/ndf/whatever files you have configured for this database. And what does the disk usage report show for free space in that database? Or how about you post the output for sp_spaceused for this database?

It doesn't sound like the tech knows what he is doing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/01/2011 :  10:20:51  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

It doesn't sound like the tech knows what he is doing.


You're more polite than I am, Tara!!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/01/2011 :  12:10:58  Show Profile  Reply with Quote
Run this script to see whch database files are actually using all the space:
Get Server Database File Information
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

Then you can run this script to see which tables are using all the space:
Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762





CODO ERGO SUM
Go to Top of Page

kristian.martin
Starting Member

USA
8 Posts

Posted - 12/01/2011 :  12:40:08  Show Profile  Reply with Quote
Everyone,

Thank you so much for your responses. I will try and perform these things today and post results.
Go to Top of Page

kristian.martin
Starting Member

USA
8 Posts

Posted - 12/07/2011 :  17:03:41  Show Profile  Reply with Quote
Hello Everyone. The tech tried yesterday to "shrink" the database but couldn't get it done.

In regards to the scripts, where do I run it? If I go to the SQL Server Management Studio and go to my database "Advantage," do I right-click on that folder, and select "New Query" and paste the script in there? Thanks for the help in advance. Also, I believe it is the .log file that is taking up the space. I have attached a picture of the folder where the database files are being kept.



Additionally, I was looking around the knowledgebase on Deltek's site and I saw something about ODBC Tracing logs. I wonder if this is what is actually making the backups so large. Here is the link to the Microsoft Support article: http://support.microsoft.com/kb/268591

Upon further reading I discovered this is only for a very specific file, not X.log as I thought it was.

Edited by - kristian.martin on 12/07/2011 17:51:48
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/08/2011 :  05:11:54  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Shrinking is not something that should be done regularly or without a good reason. It's probably completely the wrong approach here.

Sounds like something is preventing the log space from being reused. Please read through this - http://www.sqlservercentral.com/articles/64582/ and this - http://www.sqlservercentral.com/articles/Transaction+Log/72488/

--
Gail Shaw
SQL Server MVP
Go to Top of Page

kristian.martin
Starting Member

USA
8 Posts

Posted - 12/14/2011 :  02:35:10  Show Profile  Reply with Quote
The output from sp_spaceused is:

database_name: Advantage
database_size: 1966.63 MB
unallocated space: 169.23 MB


reserved: 429912 KB
data: 248208 KB
index_size: 113640 KB
unused: 68064 KB
Go to Top of Page

kristian.martin
Starting Member

USA
8 Posts

Posted - 12/14/2011 :  02:43:51  Show Profile  Reply with Quote
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37471 Posts

Posted - 12/14/2011 :  02:49:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
Looks like you can shrink by 169MB. Have you looked into fill factor settings?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

kristian.martin
Starting Member

USA
8 Posts

Posted - 12/14/2011 :  03:22:05  Show Profile  Reply with Quote
quote:
Originally posted by Michael Valentine Jones

Run this script to see whch database files are actually using all the space:
Get Server Database File Information
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

Then you can run this script to see which tables are using all the space:
Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762





CODO ERGO SUM



Here are the outputs from the scripts:

Get Server Database File Information Script Output:
https://docs.google.com/spreadsheet/ccc?key=0Ah9vi84SLyr8dHJzZkoxUmNaOWJvT0pvZ1ByeGpJUFE

Table Space Usage:
https://docs.google.com/spreadsheet/ccc?key=0Ah9vi84SLyr8dGlHTGlfREJlWkh1ek43dGRYaDYtcnc
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/14/2011 :  04:07:22  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Did you read the articles I recommended?

--
Gail Shaw
SQL Server MVP
Go to Top of Page

kristian.martin
Starting Member

USA
8 Posts

Posted - 12/14/2011 :  19:21:27  Show Profile  Reply with Quote
quote:
Originally posted by GilaMonster

Did you read the articles I recommended?



Hello Gail,

Yes, I did read through the articles you linked me to; thank you for that information. I have come to the conclusion that before the re-installation of our SQL Server, we were using the Simple Recovery method. The difference now is the transaction logs are not being backed up. I have come to understand there is a difference between backing up the database and the transaction log, which seems to be the cause of the growing size of the "database," as I had referred to the backups of the database before.

So what I would like to do is use the simple recovery model and set it so that the transaction log is backed up regularly or every time the database is backed up so that the size of each backup is the smallest it can be. I do not need to be able to restore between backups. I am thinking I need to backup the transaction log to make the backup smaller. Is that correct and possible?

Edited by - tkizer on 12/14/2011 19:26:44
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37471 Posts

Posted - 12/14/2011 :  19:30:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
That's not how it works. If you switch to simple recovery model, you wouldn't perform tlog backups as it isn't allowed. SQL Server will handle truncating the log when transactions complete. So you would only do full backups and potentially differentials if you need more recovery points.

I am rather confused by this new information that you previously were using SIMPLE and now aren't. Your original post says you already are using SIMPLE.

Just make the switch to SIMPLE and then do your shrink.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/15/2011 :  03:59:45  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
quote:
Originally posted by kristian.martin

So what I would like to do is use the simple recovery model and set it so that the transaction log is backed up regularly or every time the database is backed up so that the size of each backup is the smallest it can be. I do not need to be able to restore between backups. I am thinking I need to backup the transaction log to make the backup smaller. Is that correct and possible?


No. Please re-read the first article a little more carefully. It's simple recovery OR log backups.

Also, a reinstall of SQL won't have changed the database's recovery model, that's part of the properties of the database, not the install.

Are you absolutely sure that if the DB fails 10 minutes before a full backup would run you're completely OK with losing all data added since the last full backup

--
Gail Shaw
SQL Server MVP
Go to Top of Page

kristian.martin
Starting Member

USA
8 Posts

Posted - 05/01/2012 :  18:18:10  Show Profile  Reply with Quote
Hello again everyone.

I am forced to revisit this thread and update my progress since my last post because the size of the backups has now exceeded 8GB. After re-reading the articles which GilaMonster pointed out, I made a mistake in talking about which recovery model I was using, because I was basing it on the way that the database was behaving. I am in fact running in SIMPLE recovery. I have also learned that the database is in REPLICATION, and I believe this is what is causing the backup to be so large. At the time of this writing, there are no open transactions. If this makes sense, please chime in. I would like to disable transactional replication and am in the process of researching the pros and cons in a simple recovery model database.

The person running making backups understands it is his responsibility to make backups to save his work.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000