January 12, 2012 at 2:44 am
I am administering an application with a web front-end and a back-end SQL Server database.
Every user session is recorded in a table called "sessions". Every few hours, finished session rows are deleted from that table. This works fine, but the space in that table is not being reclaimed. The no. of rows in the table varies during the day between 0 (at 4am) and 300+ during peak times. However, the size of the data in the table is 3.2GB and growing!
Last night, I scheduled the following run-once job to run at 3.30am:
Use [i]DatabaseName[/i]
DBCC SHRINKDATABASE (0)
WITH NO_INFOMSGS
When I got in this morning, the log file had shrunk greatly, but the data file was as big as ever.
I am now a bit stumped and would appreciate some helpful advice.
January 12, 2012 at 2:54 am
All shrink does is release free space within the data file to the OS. If there are no free pages within the data file, it can't do anything.
Deleting rows (unless you delete significant portion of the table) probably won't result in free pages, it'll result in pages with very low page density (very little data on the 8k page). Shrink can't do anything to that as it can only release whole pages.
You probably need to rebuild the clustered index on the table (well, after the shrink you definitely need to). That will recreate the table in a nice compact, ordered form and the free space will now be free pages within the database. You can shrink, if you know that space won't be reused in a reasonable amount of time (4 months is usually my aim), otherwise just leave the free space within the data file, it does no harm and SQL does need some free space in order to work correctly without repeatedly having to grow the data file. Also note that shrink fragments all indexes and you should rebuild all indexes after a shrink
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
January 12, 2012 at 3:04 am
Try DBCC SHRINKFILE against the data file if you feel you really need to.
Will the datafile grow again, therefore requiring another shrink ? Repetition of these actions will fragment your datafile so I'd avoid at all costs, plus you've got the performance hit of shrinking & re-growing the file.
Cheers
Vultar
January 12, 2012 at 3:12 am
Hi Gail,
Many thanks for your prompt answer to my question.
The database supports a key application. Can I safely rebuild the clustered index without damaging performance for the web users? Or should I wait and schedule it for the middle of the night?
Many thanks
Mark
January 12, 2012 at 3:17 am
Hi Vultar,
We shrank it about 2 months ago, but it grows and grows.
I know the arguments against using SHRINKFILE and I don't want to use it often.
But we have a table which never contains more than 400 rows at any one time, but takes up 3.2GB of filespace and growing. In contrast, the rest of the DB is slightly over 1GB in size.
I'm thinking of scheduling a monthly SHRINKFILE as it seems the least bad option... but I am very open to better suggestions!
January 12, 2012 at 3:34 am
I'm thinking of scheduling a monthly SHRINKFILE as it seems the least bad option... but I am very open to better suggestions!
I'd give Gails suggestion a go.. I didn't think about rebuidling the clustered index which is a good idea!
January 12, 2012 at 3:56 am
MarkThornton (1/12/2012)
I'm thinking of scheduling a monthly SHRINKFILE as it seems the least bad option... but I am very open to better suggestions!
If the size of the table is due to low page density, shrink will do nothing at all for the space used.
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
January 12, 2012 at 3:57 am
MarkThornton (1/12/2012)
Or should I wait and schedule it for the middle of the night?
Yes. The table will be inaccessible while the index rebuilds.
You should have regularly scheduled index rebuilds as part of your maintenance routines.
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
January 12, 2012 at 4:02 am
The problem seems to be caused by a single ntext field in the table (horrible, isn't it!)
The stats for LOB_DATA in the Primary Key index are as follows:
Page_Count > 414,283
avge Space used per page = 0.47%
Ghost Record Count = 567,048
This explains my problem.... unfortunately, rebuilding the index had no effect!
I wouldn't be too surprised if the coders of the "Rebuild Index" functionality just forgot about dealing with ntext.
So.... I now have a much better understanding of my problem, but still no nice solution.
January 12, 2012 at 4:08 am
I am completely agree with Gail without creating the Cluster Index you will not be able to release size from table even you will have to REORG the index then only shrink database will help to release space from table.
sp_spaceused 'Table_Name
ALTER INDEX <INDEXNAME> ON <TABLENAME> REORGANIZE
DBCC SHRINKDATABASE
also perform DBCC CLEANTABLE command on table.
[url= http://msdn.microsoft.com/en-us/library/ms174418.aspx%5D
http://msdn.microsoft.com/en-us/library/ms174418.aspx%5B/url%5D
January 12, 2012 at 4:14 am
MarkThornton (1/12/2012)
This explains my problem.... unfortunately, rebuilding the index had no effect!I wouldn't be too surprised if the coders of the "Rebuild Index" functionality just forgot about dealing with ntext.
I'll pass your comment on to Paul Randal, who lead the team that wrote that code. 😉
Seriously though, rebuild doesn't touch LOB columns, and that's by design. ALTER INDEX ... REORGANISE with the LOB_COMPACTION option however.....
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
January 12, 2012 at 4:18 am
sharmaamard (1/12/2012)
sp_spaceused 'BILL_PRODUCT_DETAILS'
ALTER INDEX <INDEXNAME> ON <TABLENAME> REORGANIZE
DBCC SHRINKDATABASE
also perform DBCC CLEANTABLE command on table.
columns [/b]have been dropped from the table, not for when rows have been deleted. And please don't recommend shrink (file or database) without mentioning the impact of it.
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
January 12, 2012 at 4:19 am
We do have regular index maintenance - a weekly scheduled job, each sunday.
My current theory is that this isn't working, due to the presence of an ntext field in the table.
January 12, 2012 at 4:24 am
Hi Gila,
User already performing shrinkdatabase command and it was not giving proper solution to him that's why he posted the problem so he might have already know the advantage and disadvantage of the functionality.
There is not need to explain him what is the advantage and disadvantage of the functionality.
January 12, 2012 at 4:28 am
MarkThornton (1/12/2012)
We do have regular index maintenance - a weekly scheduled job, each sunday.My current theory is that this isn't working, due to the presence of an ntext field in the table.
You're deleting large numbers of rows with NTEXT columns?
I seem to remember a post around this that Paul Randall wrote, the ghost record cleanup utility will not release space for deleted pages immediately. For performance reasons the ghost record delete utility runs in small batches
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply