I'm in the process of moving our database server to new hardware, but when I
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
--
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
Showing posts with label release. Show all posts
Showing posts with label release. Show all posts
Monday, March 26, 2012
How to release user connections?
I'm in the process of moving our database server to new hardware, but when I
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?
exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/
|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>
|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>
|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.
|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?
exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/
|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>
|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>
|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.
|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
How to release user connections?
I'm in the process of moving our database server to new hardware, but when I
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
How to release the memory allocated to SQL Server?
The task manager shows my SQL Server use 1.7G memory. However, it returns
130M if I stop and start the service and it will keep this amount quite a
while. Anyway to "release" the memory without stop the server?
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
Set server option "max memory".
INF: SQL Server Memory Usage
http://www.support.microsoft.com/?id=321363
AMB
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
|||You can set the Max memory option so that sql server won't take more than
that memory...normally sql server release the memory if other processes
requires more memory...do u have any particular reason to reduce the sql
server memory usage?
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
|||SQL Server does not release memory unless the OS asks for it. This is the
intended behavior.
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
Andrew J. Kelly SQL MVP
"nick" <nick@.discussions.microsoft.com> wrote in message
news:6251FBB2-9DCE-4990-A03E-54A0197B7E06@.microsoft.com...
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
[vbcol=seagreen]
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
[vbcol=seagreen]
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
130M if I stop and start the service and it will keep this amount quite a
while. Anyway to "release" the memory without stop the server?
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
Set server option "max memory".
INF: SQL Server Memory Usage
http://www.support.microsoft.com/?id=321363
AMB
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
|||You can set the Max memory option so that sql server won't take more than
that memory...normally sql server release the memory if other processes
requires more memory...do u have any particular reason to reduce the sql
server memory usage?
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
|||SQL Server does not release memory unless the OS asks for it. This is the
intended behavior.
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
Andrew J. Kelly SQL MVP
"nick" <nick@.discussions.microsoft.com> wrote in message
news:6251FBB2-9DCE-4990-A03E-54A0197B7E06@.microsoft.com...
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
[vbcol=seagreen]
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
[vbcol=seagreen]
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
How to release the memory allocated to SQL Server?
The task manager shows my SQL Server use 1.7G memory. However, it returns
130M if I stop and start the service and it will keep this amount quite a
while. Anyway to "release" the memory without stop the server?> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
Set server option "max memory".
INF: SQL Server Memory Usage
http://www.support.microsoft.com/?id=321363
AMB
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||You can set the Max memory option so that sql server won't take more than
that memory...normally sql server release the memory if other processes
requires more memory...do u have any particular reason to reduce the sql
server memory usage?
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||SQL Server does not release memory unless the OS asks for it. This is the
intended behavior.
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
Andrew J. Kelly SQL MVP
"nick" <nick@.discussions.microsoft.com> wrote in message
news:6251FBB2-9DCE-4990-A03E-54A0197B7E06@.microsoft.com...
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
[vbcol=seagreen]
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
>|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
[vbcol=seagreen]
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
>sql
130M if I stop and start the service and it will keep this amount quite a
while. Anyway to "release" the memory without stop the server?> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
Set server option "max memory".
INF: SQL Server Memory Usage
http://www.support.microsoft.com/?id=321363
AMB
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||You can set the Max memory option so that sql server won't take more than
that memory...normally sql server release the memory if other processes
requires more memory...do u have any particular reason to reduce the sql
server memory usage?
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||SQL Server does not release memory unless the OS asks for it. This is the
intended behavior.
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
Andrew J. Kelly SQL MVP
"nick" <nick@.discussions.microsoft.com> wrote in message
news:6251FBB2-9DCE-4990-A03E-54A0197B7E06@.microsoft.com...
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
[vbcol=seagreen]
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
>|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
[vbcol=seagreen]
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
>sql
How to release the memory allocated to SQL Server?
The task manager shows my SQL Server use 1.7G memory. However, it returns
130M if I stop and start the service and it will keep this amount quite a
while. Anyway to "release" the memory without stop the server?> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
Set server option "max memory".
INF: SQL Server Memory Usage
http://www.support.microsoft.com/?id=321363
AMB
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||You can set the Max memory option so that sql server won't take more than
that memory...normally sql server release the memory if other processes
requires more memory...do u have any particular reason to reduce the sql
server memory usage?
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||SQL Server does not release memory unless the OS asks for it. This is the
intended behavior.
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
--
Andrew J. Kelly SQL MVP
"nick" <nick@.discussions.microsoft.com> wrote in message
news:6251FBB2-9DCE-4990-A03E-54A0197B7E06@.microsoft.com...
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
> > The task manager shows my SQL Server use 1.7G memory. However, it returns
> > 130M if I stop and start the service and it will keep this amount quite a
> > while. Anyway to "release" the memory without stop the server?|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
> > The task manager shows my SQL Server use 1.7G memory. However, it returns
> > 130M if I stop and start the service and it will keep this amount quite a
> > while. Anyway to "release" the memory without stop the server?
130M if I stop and start the service and it will keep this amount quite a
while. Anyway to "release" the memory without stop the server?> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
Set server option "max memory".
INF: SQL Server Memory Usage
http://www.support.microsoft.com/?id=321363
AMB
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||You can set the Max memory option so that sql server won't take more than
that memory...normally sql server release the memory if other processes
requires more memory...do u have any particular reason to reduce the sql
server memory usage?
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||SQL Server does not release memory unless the OS asks for it. This is the
intended behavior.
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
--
Andrew J. Kelly SQL MVP
"nick" <nick@.discussions.microsoft.com> wrote in message
news:6251FBB2-9DCE-4990-A03E-54A0197B7E06@.microsoft.com...
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
> > The task manager shows my SQL Server use 1.7G memory. However, it returns
> > 130M if I stop and start the service and it will keep this amount quite a
> > while. Anyway to "release" the memory without stop the server?|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
> > The task manager shows my SQL Server use 1.7G memory. However, it returns
> > 130M if I stop and start the service and it will keep this amount quite a
> > while. Anyway to "release" the memory without stop the server?
How to release Table unused space
Hi all
I am having some disk space issue, and I notice that I have some tables with
a lot of GB of unused space.
Is it a way to claim and release that unused space?
To give you a better picture, I have just one table with below information
Rows = 131977895
Reserved =146270344 KB
Index_Size = 2767616 KB
Data = 70372568 KB
Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
This table is reindexed every sunday (yesterday was the last reindex) and it
does not look to have fragmentation issues, when I ran a DBCC Showcontig I
get these results:
DBCC SHOWCONTIG scanning 'Sales' table...
Table: 'Sales' (1228791685); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned........................: 8796978
- Extents Scanned.......................: 1101956
- Extent Switches.......................: 1101956
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
- Logical Scan Fragmentation ..............: 0.04%
- Extent Scan Fragmentation ...............: 9.80%
- Avg. Bytes Free per Page................: 647.0
- Avg. Page Density (full)................: 92.01%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Could someone give me an idea on how to release the 70 GB of unused space?
Thanks a lot
JuanDo you have Text or image columns? Did you drop any recently?
--
Andrew J. Kelly SQL MVP
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Juan,
Your numbers don't match.
The first data you mention is 132 million rows, and 50% unused space.
But then the DBCC SHOWCONTIG output reports 1,228 millions rows and 10%
unused space.
Gert-Jan
Juan wrote:
> Hi all
> I am having some disk space issue, and I notice that I have some tables with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> This table is reindexed every sunday (yesterday was the last reindex) and it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan|||Did you get your numbers below from sp_spaceused? You cannot rely on this
for accurate information. DBCC updateusage will help.
Note that from showcontig output your pages are mostly full --> not much
unused space.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Ey, you are right, I ran a DBCC updateusage and it fix the numbers, I am
getting now just 300 KB of unused space, this makes sense now.
Thanks a lot, I'll keep on mind to run a DBCC UpdateUsage next time I check
the sizes of my tables.
Juan
"TheSQLGuru" wrote:
> Did you get your numbers below from sp_spaceused? You cannot rely on this
> for accurate information. DBCC updateusage will help.
> Note that from showcontig output your pages are mostly full --> not much
> unused space.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juan" <Juan@.discussions.microsoft.com> wrote in message
> news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> > Hi all
> >
> > I am having some disk space issue, and I notice that I have some tables
> > with
> > a lot of GB of unused space.
> >
> > Is it a way to claim and release that unused space?
> >
> > To give you a better picture, I have just one table with below information
> > Rows = 131977895
> > Reserved =146270344 KB
> > Index_Size = 2767616 KB
> > Data = 70372568 KB
> > Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> >
> >
> > This table is reindexed every sunday (yesterday was the last reindex) and
> > it
> > does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> > get these results:
> > DBCC SHOWCONTIG scanning 'Sales' table...
> > Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> > TABLE level scan performed.
> > - Pages Scanned........................: 8796978
> > - Extents Scanned.......................: 1101956
> > - Extent Switches.......................: 1101956
> > - Avg. Pages per Extent..................: 8.0
> > - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> > - Logical Scan Fragmentation ..............: 0.04%
> > - Extent Scan Fragmentation ...............: 9.80%
> > - Avg. Bytes Free per Page................: 647.0
> > - Avg. Page Density (full)................: 92.01%
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> >
> > Could someone give me an idea on how to release the 70 GB of unused space?
> >
> > Thanks a lot
> >
> > Juan
> >
>
>
I am having some disk space issue, and I notice that I have some tables with
a lot of GB of unused space.
Is it a way to claim and release that unused space?
To give you a better picture, I have just one table with below information
Rows = 131977895
Reserved =146270344 KB
Index_Size = 2767616 KB
Data = 70372568 KB
Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
This table is reindexed every sunday (yesterday was the last reindex) and it
does not look to have fragmentation issues, when I ran a DBCC Showcontig I
get these results:
DBCC SHOWCONTIG scanning 'Sales' table...
Table: 'Sales' (1228791685); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned........................: 8796978
- Extents Scanned.......................: 1101956
- Extent Switches.......................: 1101956
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
- Logical Scan Fragmentation ..............: 0.04%
- Extent Scan Fragmentation ...............: 9.80%
- Avg. Bytes Free per Page................: 647.0
- Avg. Page Density (full)................: 92.01%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Could someone give me an idea on how to release the 70 GB of unused space?
Thanks a lot
JuanDo you have Text or image columns? Did you drop any recently?
--
Andrew J. Kelly SQL MVP
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Juan,
Your numbers don't match.
The first data you mention is 132 million rows, and 50% unused space.
But then the DBCC SHOWCONTIG output reports 1,228 millions rows and 10%
unused space.
Gert-Jan
Juan wrote:
> Hi all
> I am having some disk space issue, and I notice that I have some tables with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> This table is reindexed every sunday (yesterday was the last reindex) and it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan|||Did you get your numbers below from sp_spaceused? You cannot rely on this
for accurate information. DBCC updateusage will help.
Note that from showcontig output your pages are mostly full --> not much
unused space.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Ey, you are right, I ran a DBCC updateusage and it fix the numbers, I am
getting now just 300 KB of unused space, this makes sense now.
Thanks a lot, I'll keep on mind to run a DBCC UpdateUsage next time I check
the sizes of my tables.
Juan
"TheSQLGuru" wrote:
> Did you get your numbers below from sp_spaceused? You cannot rely on this
> for accurate information. DBCC updateusage will help.
> Note that from showcontig output your pages are mostly full --> not much
> unused space.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juan" <Juan@.discussions.microsoft.com> wrote in message
> news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> > Hi all
> >
> > I am having some disk space issue, and I notice that I have some tables
> > with
> > a lot of GB of unused space.
> >
> > Is it a way to claim and release that unused space?
> >
> > To give you a better picture, I have just one table with below information
> > Rows = 131977895
> > Reserved =146270344 KB
> > Index_Size = 2767616 KB
> > Data = 70372568 KB
> > Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> >
> >
> > This table is reindexed every sunday (yesterday was the last reindex) and
> > it
> > does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> > get these results:
> > DBCC SHOWCONTIG scanning 'Sales' table...
> > Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> > TABLE level scan performed.
> > - Pages Scanned........................: 8796978
> > - Extents Scanned.......................: 1101956
> > - Extent Switches.......................: 1101956
> > - Avg. Pages per Extent..................: 8.0
> > - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> > - Logical Scan Fragmentation ..............: 0.04%
> > - Extent Scan Fragmentation ...............: 9.80%
> > - Avg. Bytes Free per Page................: 647.0
> > - Avg. Page Density (full)................: 92.01%
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> >
> > Could someone give me an idea on how to release the 70 GB of unused space?
> >
> > Thanks a lot
> >
> > Juan
> >
>
>
How to release Table unused space
Hi all
I am having some disk space issue, and I notice that I have some tables with
a lot of GB of unused space.
Is it a way to claim and release that unused space?
To give you a better picture, I have just one table with below information
Rows = 131977895
Reserved =146270344 KB
Index_Size = 2767616 KB
Data = 70372568 KB
Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
This table is reindexed every sunday (yesterday was the last reindex) and it
does not look to have fragmentation issues, when I ran a DBCC Showcontig I
get these results:
DBCC SHOWCONTIG scanning 'Sales' table...
Table: 'Sales' (1228791685); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned........................: 8796978
- Extents Scanned.......................: 1101956
- Extent Switches.......................: 1101956
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
- Logical Scan Fragmentation ..............: 0.04%
- Extent Scan Fragmentation ...............: 9.80%
- Avg. Bytes Free per Page................: 647.0
- Avg. Page Density (full)................: 92.01%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Could someone give me an idea on how to release the 70 GB of unused space?
Thanks a lot
Juan
Do you have Text or image columns? Did you drop any recently?
Andrew J. Kelly SQL MVP
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>
|||Juan,
Your numbers don't match.
The first data you mention is 132 million rows, and 50% unused space.
But then the DBCC SHOWCONTIG output reports 1,228 millions rows and 10%
unused space.
Gert-Jan
Juan wrote:
> Hi all
> I am having some disk space issue, and I notice that I have some tables with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> This table is reindexed every sunday (yesterday was the last reindex) and it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
|||Did you get your numbers below from sp_spaceused? You cannot rely on this
for accurate information. DBCC updateusage will help.
Note that from showcontig output your pages are mostly full --> not much
unused space.
TheSQLGuru
President
Indicium Resources, Inc.
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>
|||Ey, you are right, I ran a DBCC updateusage and it fix the numbers, I am
getting now just 300 KB of unused space, this makes sense now.
Thanks a lot, I'll keep on mind to run a DBCC UpdateUsage next time I check
the sizes of my tables.
Juan
"TheSQLGuru" wrote:
> Did you get your numbers below from sp_spaceused? You cannot rely on this
> for accurate information. DBCC updateusage will help.
> Note that from showcontig output your pages are mostly full --> not much
> unused space.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juan" <Juan@.discussions.microsoft.com> wrote in message
> news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
>
>
I am having some disk space issue, and I notice that I have some tables with
a lot of GB of unused space.
Is it a way to claim and release that unused space?
To give you a better picture, I have just one table with below information
Rows = 131977895
Reserved =146270344 KB
Index_Size = 2767616 KB
Data = 70372568 KB
Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
This table is reindexed every sunday (yesterday was the last reindex) and it
does not look to have fragmentation issues, when I ran a DBCC Showcontig I
get these results:
DBCC SHOWCONTIG scanning 'Sales' table...
Table: 'Sales' (1228791685); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned........................: 8796978
- Extents Scanned.......................: 1101956
- Extent Switches.......................: 1101956
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
- Logical Scan Fragmentation ..............: 0.04%
- Extent Scan Fragmentation ...............: 9.80%
- Avg. Bytes Free per Page................: 647.0
- Avg. Page Density (full)................: 92.01%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Could someone give me an idea on how to release the 70 GB of unused space?
Thanks a lot
Juan
Do you have Text or image columns? Did you drop any recently?
Andrew J. Kelly SQL MVP
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>
|||Juan,
Your numbers don't match.
The first data you mention is 132 million rows, and 50% unused space.
But then the DBCC SHOWCONTIG output reports 1,228 millions rows and 10%
unused space.
Gert-Jan
Juan wrote:
> Hi all
> I am having some disk space issue, and I notice that I have some tables with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> This table is reindexed every sunday (yesterday was the last reindex) and it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
|||Did you get your numbers below from sp_spaceused? You cannot rely on this
for accurate information. DBCC updateusage will help.
Note that from showcontig output your pages are mostly full --> not much
unused space.
TheSQLGuru
President
Indicium Resources, Inc.
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>
|||Ey, you are right, I ran a DBCC updateusage and it fix the numbers, I am
getting now just 300 KB of unused space, this makes sense now.
Thanks a lot, I'll keep on mind to run a DBCC UpdateUsage next time I check
the sizes of my tables.
Juan
"TheSQLGuru" wrote:
> Did you get your numbers below from sp_spaceused? You cannot rely on this
> for accurate information. DBCC updateusage will help.
> Note that from showcontig output your pages are mostly full --> not much
> unused space.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juan" <Juan@.discussions.microsoft.com> wrote in message
> news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
>
>
How to release Table unused space
Hi all
I am having some disk space issue, and I notice that I have some tables with
a lot of GB of unused space.
Is it a way to claim and release that unused space?
To give you a better picture, I have just one table with below information
Rows = 131977895
Reserved =146270344 KB
Index_Size = 2767616 KB
Data = 70372568 KB
Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
This table is reindexed every sunday (yesterday was the last reindex) and it
does not look to have fragmentation issues, when I ran a DBCC Showcontig I
get these results:
DBCC SHOWCONTIG scanning 'Sales' table...
Table: 'Sales' (1228791685); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned........................: 8796978
- Extents Scanned.......................: 1101956
- Extent Switches.......................: 1101956
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.79% [1099623:110
1957]
- Logical Scan Fragmentation ..............: 0.04%
- Extent Scan Fragmentation ...............: 9.80%
- Avg. Bytes Free per Page................: 647.0
- Avg. Page Density (full)................: 92.01%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Could someone give me an idea on how to release the 70 GB of unused space?
Thanks a lot
JuanDo you have Text or image columns? Did you drop any recently?
Andrew J. Kelly SQL MVP
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1
101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Juan,
Your numbers don't match.
The first data you mention is 132 million rows, and 50% unused space.
But then the DBCC SHOWCONTIG output reports 1,228 millions rows and 10%
unused space.
Gert-Jan
Juan wrote:
> Hi all
> I am having some disk space issue, and I notice that I have some tables wi
th
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> This table is reindexed every sunday (yesterday was the last reindex) and
it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1
101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan|||Did you get your numbers below from sp_spaceused? You cannot rely on this
for accurate information. DBCC updateusage will help.
Note that from showcontig output your pages are mostly full --> not much
unused space.
TheSQLGuru
President
Indicium Resources, Inc.
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1
101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Ey, you are right, I ran a DBCC updateusage and it fix the numbers, I am
getting now just 300 KB of unused space, this makes sense now.
Thanks a lot, I'll keep on mind to run a DBCC UpdateUsage next time I check
the sizes of my tables.
Juan
"TheSQLGuru" wrote:
> Did you get your numbers below from sp_spaceused? You cannot rely on this
> for accurate information. DBCC updateusage will help.
> Note that from showcontig output your pages are mostly full --> not much
> unused space.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juan" <Juan@.discussions.microsoft.com> wrote in message
> news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
>
>
I am having some disk space issue, and I notice that I have some tables with
a lot of GB of unused space.
Is it a way to claim and release that unused space?
To give you a better picture, I have just one table with below information
Rows = 131977895
Reserved =146270344 KB
Index_Size = 2767616 KB
Data = 70372568 KB
Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
This table is reindexed every sunday (yesterday was the last reindex) and it
does not look to have fragmentation issues, when I ran a DBCC Showcontig I
get these results:
DBCC SHOWCONTIG scanning 'Sales' table...
Table: 'Sales' (1228791685); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned........................: 8796978
- Extents Scanned.......................: 1101956
- Extent Switches.......................: 1101956
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.79% [1099623:110
1957]
- Logical Scan Fragmentation ..............: 0.04%
- Extent Scan Fragmentation ...............: 9.80%
- Avg. Bytes Free per Page................: 647.0
- Avg. Page Density (full)................: 92.01%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Could someone give me an idea on how to release the 70 GB of unused space?
Thanks a lot
JuanDo you have Text or image columns? Did you drop any recently?
Andrew J. Kelly SQL MVP
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1
101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Juan,
Your numbers don't match.
The first data you mention is 132 million rows, and 50% unused space.
But then the DBCC SHOWCONTIG output reports 1,228 millions rows and 10%
unused space.
Gert-Jan
Juan wrote:
> Hi all
> I am having some disk space issue, and I notice that I have some tables wi
th
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> This table is reindexed every sunday (yesterday was the last reindex) and
it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1
101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan|||Did you get your numbers below from sp_spaceused? You cannot rely on this
for accurate information. DBCC updateusage will help.
Note that from showcontig output your pages are mostly full --> not much
unused space.
TheSQLGuru
President
Indicium Resources, Inc.
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1
101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Ey, you are right, I ran a DBCC updateusage and it fix the numbers, I am
getting now just 300 KB of unused space, this makes sense now.
Thanks a lot, I'll keep on mind to run a DBCC UpdateUsage next time I check
the sizes of my tables.
Juan
"TheSQLGuru" wrote:
> Did you get your numbers below from sp_spaceused? You cannot rely on this
> for accurate information. DBCC updateusage will help.
> Note that from showcontig output your pages are mostly full --> not much
> unused space.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juan" <Juan@.discussions.microsoft.com> wrote in message
> news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
>
>
How to release large unused/allocated space
Hi,
I have 6 filegroups that contain over 30 gigs of data. I manage the transaction log space efficiently by using a script and 'shrink database'. But, the allocated unused space of the data files are very large, over 30 gigs.
The configuration is standard for all files:
- Automatically grow file by percent
- Maximum file size - Unrestricted
How do I release the unused space?
Thanks,
- gshaf
Hi Gshaf,
You can issue the DBCC SHRINKDATABASE statement with TRUNCATEONLY. This will shrink the database the maximum amount possible, and release the space back to windows. If you want to control how much space in the db left for growth, you'll need to issue DBCC SHRINKFILE and specify the amount (MB) of space you want left:
DBCC SHRINKFILE(MyDataFileNameHere, 15000)
The above command will attempt to shrink the datafile "MyDataFileNameHere" to 15GB.
Cheers,
Rob
sqlHow to release large unused/allocated space
Hi,
I have 6 filegroups that contain over 30 gigs of data. I manage the transaction log space efficiently by using a script and 'shrink database'. But, the allocated unused space of the data files are very large, over 30 gigs.
The configuration is standard for all files:
- Automatically grow file by percent
- Maximum file size - Unrestricted
How do I release the unused space?
Thanks,
- gshaf
Hi Gshaf,
You can issue the DBCC SHRINKDATABASE statement with TRUNCATEONLY. This will shrink the database the maximum amount possible, and release the space back to windows. If you want to control how much space in the db left for growth, you'll need to issue DBCC SHRINKFILE and specify the amount (MB) of space you want left:
DBCC SHRINKFILE(MyDataFileNameHere, 15000)
The above command will attempt to shrink the datafile "MyDataFileNameHere" to 15GB.
Cheers,
Rob
Subscribe to:
Posts (Atom)