changing the table reserved size
-
Hi All,
I got a table that logs the user actions (action table). The problem is that the table is getting too big and I'm running out of space. I created an alert job to notify me if the database size is near the max size.
When I get this alert, I delete the old data from the action table.
I used sp_spaceused 'tablename' to get the size
name rows reserved data index size unused actions 101864 121672 KB 18976 KB 8760 KB 93936 KB Now, My alert job still report to me that the database size is too big, even tho I delete a lot of rows because the reserved size only change a little.
Is there any way to modify the reserved size or set it depending on the data size?
Any help would be appriciated.
Thanks
-
One thing to do would be to run dbcc updateusage. Maybe you're alright but some stat is not up to date.
Also might I suggest that you create a job that runs every day, or week or month. This job would delete the data from the actions table where the data is older than (whatever delay you need). That way you'll always have roughly the same amount of data in the database.
-
Thanks for your reply Ninja,
I already use dbcc updateusage ('database_name','table_name') or sp_spaceused 'table_name', 'true' and little has change
My alert gets the database size and max size from sp_helpfile.
I assumed that I need to lower the reserved size before the database size goes down. I think the database size is calculated by adding up all the reserved size and index, of all the tables in that database.
-
Do you have any text, ntext or image column in that table?
-
Have you tried rebuilding the indexes on that table since the delete? (specifically the clustered index)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Yup, I tried to recreate the indexes or Defragmenting an Index with no luck.
I a web developer, so I'm not that good at database admin stuff
Is it possible to change the reserved size? I been looking around all morning and can't find anything on this subject.
-
Is it possible to change the reserved size?
Not directly. Afaik, it's a measure of how many pages/extents are allocated to the table. Not something you can change manually.
Is there a clustered index on the table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
but why would you want to reduce the reserved size, and if you're just a web developer what are you doing messing around inside sql server ? No offence intended. I wouldn't even bother about this personally , but -- two things spring to mind - if your table doesn't have a clustered index then you may get bloat due to leaf level fragmentation and/or fill factors would alter these figures.
I assume we've discounted text and blob data already?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I don't own the database so I got a limited space. Since the company I'm working for is small, I deal with almost everything.
The table contains only int and varchar(255).
-
Sorry, I forgot the table has a clustered index
-
Hmmm. Wierd....
Can you run the following and post the output please
DBCC SHOWCONTIG('<table name>')
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
DBCC SHOWCONTIG scanning 'actions' table...
Table: 'actions' (199671759); index ID: 1, database ID: 76
TABLE level scan performed.
- Pages Scanned................................: 2396
- Extents Scanned..............................: 1208
- Extent Switches..............................: 1489
- Avg. Pages per Extent........................: 2.0
- Scan Density [Best Count:Actual Count].......: 20.13% [300:1490]
- Logical Scan Fragmentation ..................: 7.01%
- Extent Scan Fragmentation ...................: 50.99%
- Avg. Bytes Free per Page.....................: 127.3
- Avg. Page Density (full).....................: 98.43%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
-
Hmm. Very wierd
Try rebuilding the clustered index - DBCC DBREINDEX ('Actions') - and then do an update usage. See if that has any effect.
If not, then I'm really stumped.
From the show contig, it looks like there's lots of empty space within the extents (average pages per extent = 2. It should be 8 if the index has just been rebuilt)
Since extents are allocated to objects, not individual pages, that may well be your missing space.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
How big is the server/database that this data is in. That table is only 120mg worth of data, really not that much. It seems you would be better off finding other items to clean up then this table, unless this is a 500mg harddrive. I agree with Gail, that index has not been rebuilt recently or inserts are that intense. I would run dbcc dbreindex ('actions','',80) to set the default fill factor back to 80.
Tom
-
A brute force way could be to create a new table, move the data you wish to keep into it, drop the old table and then rename the new table and recreate links, indices, etc. The suggestions from other people are probably worth trying before this though as your system might be off the air for a few seconds whilst the data shuffle occurs. To minimise this you could
1. Create the new table
2. Drop FKs on the old one
3. Rename the old table and the new table (so the new table takes on its permanent name) - new inserts can go in to the new/empty table
4. Move the data
5. Drop the old table
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply