| Author |
Topic |
|
kristian.martin
Starting Member
8 Posts |
 Posted - 2011-11-29 : 13:49:08
|
| 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! |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-30 : 19:50:54
|
| 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 ! |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-01 : 10:20:51
|
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!! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
kristian.martin
Starting Member
8 Posts |
Posted - 2011-12-01 : 12:40:08
|
| Everyone,Thank you so much for your responses. I will try and perform these things today and post results. |
|
|
|
kristian.martin
Starting Member
8 Posts |
Posted - 2011-12-07 : 17:03:41
|
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: [url]http://support.microsoft.com/kb/268591[/url]Upon further reading I discovered this is only for a very specific file, not X.log as I thought it was. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-08 : 05:11:54
|
| 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 - [url]http://www.sqlservercentral.com/articles/64582/[/url] and this - http://www.sqlservercentral.com/articles/Transaction+Log/72488/--Gail ShawSQL Server MVP |
|
|
|
kristian.martin
Starting Member
8 Posts |
Posted - 2011-12-14 : 02:35:10
|
| The output from sp_spaceused is:database_name: Advantage database_size: 1966.63 MBunallocated space: 169.23 MB reserved: 429912 KB data: 248208 KBindex_size: 113640 KBunused: 68064 KB |
|
|
|
kristian.martin
Starting Member
8 Posts |
Posted - 2011-12-14 : 02:43:51
|
 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kristian.martin
Starting Member
8 Posts |
Posted - 2011-12-14 : 03:22:05
|
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 Informationhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058Then you can run this script to see which tables are using all the space:Script to analyze table space usagehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762CODO ERGO SUM
Here are the outputs from the scripts:Get Server Database File Information Script Output:[url]https://docs.google.com/spreadsheet/ccc?key=0Ah9vi84SLyr8dHJzZkoxUmNaOWJvT0pvZ1ByeGpJUFE[/url]Table Space Usage:[url]https://docs.google.com/spreadsheet/ccc?key=0Ah9vi84SLyr8dGlHTGlfREJlWkh1ek43dGRYaDYtcnc[/url] |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-14 : 04:07:22
|
| Did you read the articles I recommended?--Gail ShawSQL Server MVP |
|
|
|
kristian.martin
Starting Member
8 Posts |
Posted - 2011-12-14 : 19:21:27
|
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? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-12-14 : 19:30:56
|
| 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-15 : 03:59:45
|
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 ShawSQL Server MVP |
|
|
|
kristian.martin
Starting Member
8 Posts |
Posted - 2012-05-01 : 18:18:10
|
| 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. |
|
|
|
|
Database Backup Size Growing Fast