SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How we dealocate the memory of a row


How we dealocate the memory of a row

Author
Message
chaudharydpk0
chaudharydpk0
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 6
How we dealocate the memory of a row in a table in sql 2008 R2. please show the query if exist.
Mike John
Mike John
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4862 Visits: 5995
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



GilaMonster
GilaMonster
SSC Guru
SSC Guru (556K reputation)SSC Guru (556K reputation)SSC Guru (556K reputation)SSC Guru (556K reputation)SSC Guru (556K reputation)SSC Guru (556K reputation)SSC Guru (556K reputation)SSC Guru (556K reputation)

Group: General Forum Members
Points: 556062 Visits: 47769
Can you explain further what you want to do?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77713 Visits: 8881
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 on googles mail service
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63053 Visits: 12821
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


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
chaudharydpk0
chaudharydpk0
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 6
Sir, actually i delete 5000 rows in my table in sql 2008 r2 but after that the table size in unchanged.
chaudharydpk0
chaudharydpk0
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 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.
Suresh B.
Suresh B.
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12244 Visits: 5330
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?
roasdasdb 89asdasdasd013
roasdasdb 89asdasdasd013
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (556K reputation)SSC Guru (556K reputation)SSC Guru (556K reputation)SSC Guru (556K reputation)SSC Guru (556K reputation)SSC Guru (556K reputation)SSC Guru (556K reputation)SSC Guru (556K reputation)

Group: General Forum Members
Points: 556062 Visits: 47769
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search