August 7, 2008 at 7:58 am
Two days following a hardware upgrade & SQL 2005 64 bit upgrade the database jumped dramatically in size. From 7GB to 65GB. Is there a setting I am missing here?
Any help you can provide would be appreciated.
Thanks,
PF
August 7, 2008 at 8:19 am
Is it the log file? Maybe you're in Full Recovery mode and not running log backups?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 7, 2008 at 8:27 am
what do you get when you run the following commands:
DBCC SQLPERF(logspace)
DBCC LOGINFO
August 7, 2008 at 8:53 am
DBCC SQLPERF(logspace)
master 0.492187542.857140
tempdb 46.4921930.373050
model 0.492187577.777780
msdb 17.2421966.425010
ReportServer 0.742187537.302630
ReportServerTempD 0.742187559.605260
campus6 1.24218834.984280
distribution2.74218840.028490
DBCC LOGINFO
2253952819223421280
22621442621442330640
August 7, 2008 at 9:01 am
Looks like its not your log file size. I'd look for any maintenance plans that do index rebuilds or reorgs and dont have the SORT IN TEMPDB option set. If its not set it will use your data file to do the sort operation which can cause a dramatic increase in its size.
Also, look at the database options and see if you don't have something like Date Correlation Optimization Enabled. With this option, SQL Server will create indexed views for correlated date foreign keys which can also dramatically increase the size of the database.
August 7, 2008 at 9:48 am
I would like for you to do the following if it is ok
--run
exec sp_helpdb [name of db you are talking about growing so large]
go
--we need to see how fast your db .mdf is growing It may be at too high. If it is 10% or higher, lower to 3-4%
if the size and the actual usage are extremely different, shrink the file.
you can do this through the Manager under ALL tasks. Shrink file.
It will let you know how much you can shrink the file to.
--are you clustered?
--how many CPUs
--what version are you on? @@version
--one other item - do you run BPA for 2005.
it can help you out with any performance issues you have.
I hope this helps.
August 7, 2008 at 10:15 am
Be careful when shrinking a data file. You'll potentially introduce heavy fragmentation.
Any time you shrink a data file you'll want to either rebuild or reorg any fragmented indexes. And as I mentioned above, if you don't do the SORT IN TEMPDB option when you do, you'll increase the size of your data file because this is where SQL Server will create the sort table during the operation.
You could potentially get yourself back into the situation where your data file is back to being huge again 🙂
August 7, 2008 at 10:28 am
let's get an answer back before we do anything. Need to know information before moving forward.
you never gave us this information
1.
select name, recovery_model_desc, page_verify_option from sys.databases
2. What was the upgrade from - what version?
August 7, 2008 at 12:30 pm
I have been asked by pfiricano to keep an eye on this posting. We are currently waiting to hear back on the sp_helpdb results.
This is what I received back about the Maint Plans:
There may have been a maintenance plan with the settings ( do index rebuilds or reorgs and don't have the SORT IN TEMPDB option set.) you mentioned but I deleted all of the plans earlier this week to try to get rid of the double backups...... Right now I do not have rebuilds turned on...
Also the Date Correlation Optimization Enabled is False
Thank you
August 7, 2008 at 12:43 pm
I requested the sp_helpdb [each db in question]
but have not received any answer yet.
August 8, 2008 at 12:49 pm
Here are results from the sp_helpdb
campus6 54123.25 MBsa7Jul 25 200880Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoShrink, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsRecursiveTriggersEnabled, IsPublished
1D:\mssql\data\campus6.mdf PRIMARY55421184 KBUnlimited10%data only
2D:\mssql\data\campus6_log.ldfNULL1024 KBUnlimited10%log only
August 8, 2008 at 1:26 pm
pfiricano (8/8/2008)
Here are results from the sp_helpdbcampus6 54123.25 MBsa7Jul 25 200880Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoShrink, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsRecursiveTriggersEnabled, IsPublished
1D:\mssql\data\campus6.mdf PRIMARY55421184 KBUnlimited10%data only
2D:\mssql\data\campus6_log.ldfNULL1024 KBUnlimited10%log only
First, turn off auto shrink option. Second, review whether or not you have enabled replication (IsPublished), and if so - make sure replication is working.
Finally, review your index rebuild jobs. If you are using the SSMS Rebuild task in a maintenance plan, make sure you did not set the option 'Change free space to' to 90% (thinking that is the fill factor it will use, because it is reversed). If you set it to 90% - the fill factor will actually be 10.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 8, 2008 at 5:11 pm
did anything work for you?
let's see what the space is really like
run
exec sp_spaceused @updateusage = 'TRUE'
August 11, 2008 at 6:38 am
We think we found part of the problem. The client was not at Service Pack 2. They installed SP2 and then are going to re-index to see if that releases the space. Will let you know asap.
Thank you!!
August 11, 2008 at 8:46 am
awesome!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply