| Author |
Topic |
|
dboiler1
Starting Member
39 Posts |
 Posted - 2007-02-06 : 14:37:28
|
| Rather than posting twice, I thought I would put both issues I'm having in one. Our server is Windows Server 2003 and we're running SQL Server 2005.The first issue is this: We have several databases and I have scheduled their backups to run nightly which works just fine. A couple weeks ago, one of the databases .bak file grew from about 500MEG to 2GB overnight. Then, just a few days ago, it went from 2GB to 3.5GB. There is nothing unusual going on in the live db that would warrant such an increase in the .bak file. All the dbs are in the same backup job schedule but this is the only one affected. Additionally, I had autogrowth enabled on all the dbs but today disabled it for this particular db. Any ideas?The second issue is my tempdb.mdf file on my C drive. It will go from just a few hundred KB's to 4.5GB overnight consuming most of what is left on my C drive. I'm afraid I'm in for a system crash if it continues. I have to stop SQL Server and restart it to clear the size. Is there a way to move the location of the tempdb.mdf file to my F drive?I don't know if these two issues are related or not but certainly would like to hear from someone.Sorry, in advance, for the large post.Dave |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dboiler1
Starting Member
39 Posts |
Posted - 2007-02-06 : 14:43:34
|
| I am not appending. This only happens with the one db. The others are fine. In fact, this one was fine up until two weeks ago and then again a couple days ago. The .bak just decides to quadruple or double overnight. Thanks for the link Tara.Dave |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-06 : 14:49:04
|
| Run sp_spaceused and sp_databases for the database in question. Let's see what its size is. The backup file size will approximately equal the database size, that is the used portion of the data files.Tara Kizer |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-06 : 14:50:16
|
| Oh and I forgot to mention that the two issues are probably related, but I can't say for certain. Some query or queries are running to cause the database to grow which is causing the backup file size to grow. These queries might be using tempdb quite a bit.Tara Kizer |
|
|
|
dboiler1
Starting Member
39 Posts |
Posted - 2007-02-06 : 14:56:23
|
| well, it says the database_size is 3538MB, so that would make sense that the .bak is huge. What it doesn't explain is what made it grow from 2GB to 3.5GB overnight. We do not have that much activity going on. Thanks Tara.Dave |
|
|
|
dboiler1
Starting Member
39 Posts |
Posted - 2007-02-06 : 15:00:52
|
| Tara, is there a way to list the sizes of all the tables in this db without opening them individually? There are quite a few.Dave |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dboiler1
Starting Member
39 Posts |
Posted - 2007-02-06 : 15:27:44
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10932 works great!!! Thanks again.Dave |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2007-02-06 : 15:36:20
|
| Why not look at the jobs on the server and see if there is an import process that runs? Either that or run profiler and see when the autogrow occurs. That should give you an idea of when and how much it's growing. |
|
|
|
dboiler1
Starting Member
39 Posts |
Posted - 2007-02-06 : 15:52:34
|
| It looks like there is a view created by one of the programmers that joins other views and creates a new table. That new table is the culprit. The programmer is looking at it now. Thanks Dan and Tara. By the way, why the reference to Yak Warrior, Dan? Just curious because I raise Tibetan Yaks here in Wisconsin.Dave |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-06 : 15:58:39
|
Maybe something is copying an existing table to a new, permanent rather than Temp, table?In SQL 2000 "INFORMATION_SCHEMA.TABLES" does not give the create date, and I believe that SYSOBJECTS is not available in SQL 2005?In SQL 2000 I would have done:SELECT TOP 10 crdate, [name]FROM dbo.sysobjectsWHERE type = 'U'ORDER BY crdate DESC to detect recently-created tables.Kristen |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
|
|
dboiler1
Starting Member
39 Posts |
Posted - 2007-02-07 : 08:56:23
|
| Very nice. What was said about the efficiency of Yaks is true. Additionally, they are hardy and majestic animals, not to mention lean and tasty.Back to my issues posted here; Everyone was very helpful and my programmer was able to reduce the size of the affected table from over 2 million records, most of which were duplicates, to 35,000. However, the backup from last night is still at 3.5GB and now that the table is much smaller, shouldn't the .bak file be back to a normal size?Dave |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-02-07 : 09:04:29
|
Wow ! a Yak Breeder in the forum 
KH |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-02-07 : 09:29:37
|
| I think you'll have to shrink the database first....search here for info on same, especially that provided by members tkizer and/or nr.ps.....SMACK that developer and/or his(her) supervisor. It's time for the QA/Change Control process to be re-examined. Next time the effect may be even costlier!!! |
|
|
|
dboiler1
Starting Member
39 Posts |
Posted - 2007-02-07 : 09:42:13
|
| Thanks Andrew, I'll do the search. I don't think I'll smack her but I might grumble around her a lot. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-07 : 09:48:19
|
"shouldn't the .bak file be back to a normal size?"Well, yes. But not if your database is FULL Recovery Model - you will have to backup the TLog, and then that will shrink. But until then the backup will include all the "changes" too (Edit:Brain now back in gear!). (More likely to be slack space in the pages I think, see below)"I think you'll have to shrink the database first"Backup will only backup what is used - i.e. excluding any free space.The indexes/pages may be seriously fragmented now ... in which case you may have just as many Pages in the table as before, but now each one only has only one or two records-per-page, rather than the hundreds they had before (backup backs up all Used Pages (AFAIK) rather than just the "data within the page")So an Index Rebuild or Defrag might be called for.Maybe shrink would do that too? (Crunch the records in the pages?)Dunno the answer to that ... if there is a Clustered Index on the table then REINDEX will cruch both index and data. If there is no Clustered Index I don't know what crunches the slack space ?? Shrink (in Enterprise manager) has a "Move pages to front of file" thingie ... does that crunch records too?Kristen |
|
|
|
dboiler1
Starting Member
39 Posts |
Posted - 2007-02-07 : 09:58:56
|
| Thanks Kristen (also my daughters name, with the e). I'll have to wait until after business hours to try any of this as the db is live now and I don't want to chance bringing it down during the work day. I'll keep you informed. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-02-07 : 12:07:12
|
FYI..."Thanks Kristen......with the e"....is a he...with no s  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-07 : 12:31:24
|
| If your database is in FULL recovery model, the changes are stored in the transaction log until a BACKUP LOG is performed. This would not impact the full backup size. But what Kristen is mentioning about the fragmentation is most likely your issue. I'd schedule a DBCC DBREINEX if you have a lengthy slow period at night or a DBCC INDEXDEFRAG if you don't. DBCC DBREINEX shouldn't take too long on a database of this size though. You'll get better defragmentation levels using that one as compared to DBCC INEXDEFRAG.Tara Kizer |
|
|
|
Next Page
|
Massive .bak file