November 16, 2008 at 11:59 am
I have noticed my production database was around 60 GB night before and now around 107GB also Full backup was around 47GB before and now 106GB.
I think it was not a user / developer issue. If I go back and see my jobs history, I suspect one job that I run on weekly basis.
USE XXXXXX
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 40)"
GO
Now how could I shrink the database back to <50GB so I could have some space for the busy week ahead ?
and also appreciate if you guys could throw some more light on alternate way of reindexing without killing all the space.
thanks for your time in advance.
apps dba
November 16, 2008 at 12:00 pm
forgot to add the following :
Clustered(single node) SQL Server 2005 SP2 64-bit on a Windows 2003 SP1.
November 16, 2008 at 12:18 pm
Not totally sure why you are "suspicious" of that reindexing job; perhaps you have some information you haven't shared yet?
Could you please post space usage as follows?...
Data space
-- Data
-- Indexes
-- Unused
-- Unreserved
Transaction log
-- Used
-- Unused
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 16, 2008 at 12:50 pm
Hope this helps :
total_space: 106764.88
total_db_space: 106725.06
db_can_grow: Yes
total_log_space: 39.82
log_can_grow: Yes
total_space_used: 102041.31
total_db_space_used: 102040.30
total_log_space_used: 1.01
total_free_space: 4723.57
total_free_db_space: 4684.76
total_free_log_space: 38.81
percent_db_used: 95.61
percent_log_used: 2.54
Database Usage
Data 93921.02 MB (88.00 %)
Indexes 9346.75 MB (8.76 %)
Unused 142.45 MB (0.13 %)
Unreserved 3314.84 MB (3.11 %)
Total Database Size 106725.06 MB
Transaction Log Usage
Transaction Log Used 0.00 (0.00 %)
Transaction Log Unused 39.82 (100.00 %)
Total Transaction Log Size 39.82 MB
November 16, 2008 at 1:10 pm
I am suspecting my job as all my research pointing to that one job that was running around that time. Also my hourly log backups around the time of this job were 30mb, 42mb, 6mb, 7mb, 1mb, 22gb, 24gb, 3mb, 6mb.
job started around 1mb and ended just before 3mb.
as always, thanks for your time and help.
November 16, 2008 at 2:39 pm
Hope i read it correctly but the Log size didnt grew it only 39 mg so one of the tables grew 50 GIG...?
Oh the reindex
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 40)" isn't this making
every table 40% fill so that each table has 40% of space before it page splits isn't this rather a big amount.
November 16, 2008 at 10:07 pm
Tracey,
Thanks for your response. Yes, you are right.
Now I only have 4.5gb space left for data files. if I run it again with fill factor let's say 100, would it free up some space ? I am not sure whether 4.5gb free space is sufficient enough even to finish this job again.
how could I bring it back to 50gb ball park ? I could add another data file as a work around as long as if you guys can suggest me some thing to bring it back. I think I am out of ideas but still exploring the best way to do it.
appreciate all your time and help.
November 16, 2008 at 10:26 pm
Reindex again with a higher fill factor. Do you have a reason for wanting the data pages to be only 40% full after the reindex?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2008 at 6:12 am
I would do it for say 80.....not the 100.
Then once you have done this you can do the shrink on the data files. (not the log as it not the log that filled up).
The only thing now is that the reindex will not do the tables you did the other day at 40% as they will not be considered fragmented so you need to do the tables one by one using the command with the table names.
November 17, 2008 at 11:45 am
thanks guys.
appreciate all your help.
I will probably try for big 20 tables one by one and then try to generate some script for the rest of the tables.
will post the outcome tonight.
November 17, 2008 at 12:04 pm
tried for one big table and it failed as the rest of the space 4.5gb also occupied. I have added another data file on a different drive with 10gb more and tried again and that one too filled and statement failed again.
Now I am db at 120gb 🙁
November 17, 2008 at 3:42 pm
BOL is an important resource.
reference
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/6e929d09-ccb5-4855-a6af-b616022bc8f6.htm
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead.
also,
FILLFACTOR
Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. fillfactor must be an integer value from 1 to 100. The default is 0.
Note: [SQL2005]
Fill factor values 0 and 100 are the same in all respects. [There is a subtle difference in SQL 2000 you might like to research]
CodeOn
😛
November 20, 2008 at 12:02 pm
Thanks a bunch to all of you guys for your help and time.
issue has been resolved finally and now every thing back to normal. Learned the lesson.
November 20, 2008 at 12:10 pm
Curious, and others may have a similiar problem, but what was done to resolve the problem?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply