Deleted half the rows from a table but it's still the same size

We have recently started archiving data in our database. With some of
the tables that I've archived I see a reduction in the physical size of
the table when I delete rows. In other words if I delete 50% of the
rows the amount of disk space the table uses also drop by close to 50%
But there are a few particularly lage table that don't decrease in
size. For example one of our largest tables had 115 million rows
consuming about 35 GB of disk space. After archiving some of the data
we're down to 70 million rows (about a 40% reduction if my poor math
skill are correct) but the table still takes up 35 GB.
I was thinking that this could be due to fragmentation. The table in
question has high extent fragmentation (80-90%) for all the indexes.
Would defragmenting help in this case?
ThanksFirst you need to determine whather the interesting part is the reserved ext
ends (which can have
unused pages on them) or if it is pages with little used space. SHOWCONTIG w
ill tell you. In any
case, yes, rebuilding will probably fix, and possibly also reorganizing (dep
ending on what type of
fragmentation you have).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1147372556.194478.32110@.j73g2000cwa.googlegroups.
com...
> We have recently started archiving data in our database. With some of
> the tables that I've archived I see a reduction in the physical size of
> the table when I delete rows. In other words if I delete 50% of the
> rows the amount of disk space the table uses also drop by close to 50%
> But there are a few particularly lage table that don't decrease in
> size. For example one of our largest tables had 115 million rows
> consuming about 35 GB of disk space. After archiving some of the data
> we're down to 70 million rows (about a 40% reduction if my poor math
> skill are correct) but the table still takes up 35 GB.
> I was thinking that this could be due to fragmentation. The table in
> question has high extent fragmentation (80-90%) for all the indexes.
> Would defragmenting help in this case?
> Thanks
>

Deleted half the rows from a table but it's still the same size

We have recently started archiving data in our database. With some of
the tables that I've archived I see a reduction in the physical size of
the table when I delete rows. In other words if I delete 50% of the
rows the amount of disk space the table uses also drop by close to 50%
But there are a few particularly lage table that don't decrease in
size. For example one of our largest tables had 115 million rows
consuming about 35 GB of disk space. After archiving some of the data
we're down to 70 million rows (about a 40% reduction if my poor math
skill are correct) but the table still takes up 35 GB.
I was thinking that this could be due to fragmentation. The table in
question has high extent fragmentation (80-90%) for all the indexes.
Would defragmenting help in this case?
ThanksFirst you need to determine whather the interesting part is the reserved extends (which can have
unused pages on them) or if it is pages with little used space. SHOWCONTIG will tell you. In any
case, yes, rebuilding will probably fix, and possibly also reorganizing (depending on what type of
fragmentation you have).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1147372556.194478.32110@.j73g2000cwa.googlegroups.com...
> We have recently started archiving data in our database. With some of
> the tables that I've archived I see a reduction in the physical size of
> the table when I delete rows. In other words if I delete 50% of the
> rows the amount of disk space the table uses also drop by close to 50%
> But there are a few particularly lage table that don't decrease in
> size. For example one of our largest tables had 115 million rows
> consuming about 35 GB of disk space. After archiving some of the data
> we're down to 70 million rows (about a 40% reduction if my poor math
> skill are correct) but the table still takes up 35 GB.
> I was thinking that this could be due to fragmentation. The table in
> question has high extent fragmentation (80-90%) for all the indexes.
> Would defragmenting help in this case?
> Thanks
>

Delete Leftover SQL Jobs

Hi,
We have a SQL server that we moved from one physical server to another.
After the move we found that there are some SQL job in there that we no
longer need. Since they were created on the old server, Server IDs have now
changes and we can no longer delete them. Does anyone know how to delete
these jobs?
Thank you.
The easy (but unsupported way) is to change the name of the server in msdb.dbo.sysjobs:
http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:OBx4TKO$FHA.2812@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have a SQL server that we moved from one physical server to another. After the move we found
> that there are some SQL job in there that we no longer need. Since they were created on the old
> server, Server IDs have now changes and we can no longer delete them. Does anyone know how to
> delete these jobs?
> Thank you.
>
|||Thank you Tibor. Excellent information. :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23A7LaxW$FHA.3804@.TK2MSFTNGP14.phx.gbl...
> The easy (but unsupported way) is to change the name of the server in
> msdb.dbo.sysjobs:
> http://www.karaszi.com/SQLServer/inf...erver_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dragon" <baadil_nospam@.hotmail.com> wrote in message
> news:OBx4TKO$FHA.2812@.TK2MSFTNGP09.phx.gbl...
>

Delete Leftover SQL Jobs

Hi,
We have a SQL server that we moved from one physical server to another.
After the move we found that there are some SQL job in there that we no
longer need. Since they were created on the old server, Server IDs have now
changes and we can no longer delete them. Does anyone know how to delete
these jobs?
Thank you.The easy (but unsupported way) is to change the name of the server in msdb.d
bo.sysjobs:
http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:OBx4TKO$FHA.2812@.TK2MSFTNGP09.phx
.gbl...
> Hi,
> We have a SQL server that we moved from one physical server to another. Af
ter the move we found
> that there are some SQL job in there that we no longer need. Since they we
re created on the old
> server, Server IDs have now changes and we can no longer delete them. Does
anyone know how to
> delete these jobs?
> Thank you.
>|||Thank you Tibor. Excellent information. :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23A7LaxW$FHA.3804@.TK2MSFTNGP14.phx.gbl...
> The easy (but unsupported way) is to change the name of the server in
> msdb.dbo.sysjobs:
> http://www.karaszi.com/SQLServer/in...server_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dragon" <baadil_nospam@.hotmail.com> wrote in message
> news:OBx4TKO$FHA.2812@.TK2MSFTNGP09.phx.gbl...
>

Delete Leftover SQL Jobs

Hi,
We have a SQL server that we moved from one physical server to another.
After the move we found that there are some SQL job in there that we no
longer need. Since they were created on the old server, Server IDs have now
changes and we can no longer delete them. Does anyone know how to delete
these jobs?
Thank you.The easy (but unsupported way) is to change the name of the server in msdb.dbo.sysjobs:
http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:OBx4TKO$FHA.2812@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have a SQL server that we moved from one physical server to another. After the move we found
> that there are some SQL job in there that we no longer need. Since they were created on the old
> server, Server IDs have now changes and we can no longer delete them. Does anyone know how to
> delete these jobs?
> Thank you.
>|||Thank you Tibor. Excellent information. :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23A7LaxW$FHA.3804@.TK2MSFTNGP14.phx.gbl...
> The easy (but unsupported way) is to change the name of the server in
> msdb.dbo.sysjobs:
> http://www.karaszi.com/SQLServer/info_change_server_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dragon" <baadil_nospam@.hotmail.com> wrote in message
> news:OBx4TKO$FHA.2812@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> We have a SQL server that we moved from one physical server to another.
>> After the move we found that there are some SQL job in there that we no
>> longer need. Since they were created on the old server, Server IDs have
>> now changes and we can no longer delete them. Does anyone know how to
>> delete these jobs?
>> Thank you.
>