<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
How we dealocate the memory of a row
11 posts, Page 1 of 2
1
2
»»
How we dealocate the memory of a row
Rate Topic
Display Mode
Topic Options
Author
Message
chaudharydpk0
chaudharydpk0
Posted Tuesday, January 10, 2012 12:20 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 12, 2012 3:23 AM
Points: 4,
Visits: 6
How we dealocate the memory of a row in a table in sql 2008 R2. please show the query if exist.
Post #1232956
Mike John
Mike John
Posted Tuesday, January 10, 2012 12:25 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 8:16 AM
Points: 2,645,
Visits: 4,648
There is not normally a need to. What exactly makes you think you should need to do this - especially fo rone row?
Normally the space will be reused a new rows are added/deleted etc. Under rare situations such as a heap table and all new data being added then huge deletes of old data you can end up with a large number of empty pages. These will be used if new rows are added or you can create a clustered index on the table to restructure it.
Mike John
Post #1232957
GilaMonster
GilaMonster
Posted Tuesday, January 10, 2012 2:59 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 38,099,
Visits: 30,392
Can you explain further what you want to do?
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1233009
TheSQLGuru
TheSQLGuru
Posted Wednesday, January 11, 2012 6:48 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 3:22 PM
Points: 3,678,
Visits: 5,177
chaudharydpk0 (1/10/2012)
How we dealocate the memory of a row in a table in sql 2008 R2. please show the query if exist.
You cannot do this. But like Gail I want to know WHY you THINK you want to do this. There is likely something else at play here that we could address for you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1233938
Eric M Russell
Eric M Russell
Posted Wednesday, January 11, 2012 7:39 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 1,185,
Visits: 3,420
chaudharydpk0 (1/10/2012)
How we dealocate the memory of a row in a table in sql 2008 R2. please show the query if exist.
OK, I'll take a stab at interpreting your request literally. When you ask how to "dealocating the memory of a row", perhaps you're asking how to deallocate memory from the buffer pool where SQL Server caches pages. SQL Server caches data at the page level, not the row level.
DBCC DROPCLEANBUFFERS
http://msdn.microsoft.com/en-us/library/ms187762.aspx
"Winter Is Coming"
Post #1233980
chaudharydpk0
chaudharydpk0
Posted Thursday, January 12, 2012 1:07 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 12, 2012 3:23 AM
Points: 4,
Visits: 6
Sir, actually i delete 5000 rows in my table in sql 2008 r2 but after that the table size in unchanged.
Post #1234552
chaudharydpk0
chaudharydpk0
Posted Thursday, January 12, 2012 1:12 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 12, 2012 3:23 AM
Points: 4,
Visits: 6
Sir, actually i deleted 5000 rows in my table in sql 2008 r2 but after that the table size in unchanged. I daily need to delete thousand's record in a table and in next day fill up with new data either same size or less or more size.
Post #1234554
Suresh B.
Suresh B.
Posted Thursday, January 12, 2012 1:42 AM
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:54 AM
Points: 1,076,
Visits: 5,128
chaudharydpk0 (1/12/2012)
Sir, actually i deleted 5000 rows in my table in sql 2008 r2 but after that the table size in unchanged. I daily need to delete thousand's record in a table and in next day fill up with new data either same size or less or more size.
How did you check the table size?
Are are talking about datable files (.mdf/.ndf/.ldf) by any chance?
Post #1234562
roasdasdb 89asdasdasd013
roasdasdb 89asdasdasd013
Posted Thursday, January 12, 2012 1:54 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 12, 2012 7:08 AM
Points: 2,
Visits: 131
Sounds like you are expecting the data files to automaticallly shrink, which does not happen unless explicitly set at the database level or requested on an ad-hoc bases.
Try
DBCC SHRINKFILE
Use the TRUNCATEONLY option first, this should shrink the file providing the data you are deleting was more or less the last quantity of data that was inserted. Truncate will remove the free space from the end of the file and is instant, a normal shrink to a specific size will involve shuffle the data pages and this is very resource / time consuming.
For more info:
http://msdn.microsoft.com/en-us/library/ms189493.aspx
Rob
Post #1234567
GilaMonster
GilaMonster
Posted Thursday, January 12, 2012 2:44 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 38,099,
Visits: 30,392
chaudharydpk0 (1/12/2012)
Sir, actually i delete 5000 rows in my table in sql 2008 r2 but after that the table size in unchanged.
Nothing unusual there. The deleted rows are scattered throughout the table. Rebuild your clustered index and the table size will drop. Avoid shrink unless you absolutely must release the space to the OS. It's better to leave free space within the database for future use. Also, unless you first rebuild the index, there will be no free pages for shrink to release as the free space will be spread throughout the table.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1234594
« Prev Topic
|
Next Topic »
11 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
