How to free up the space ...

Hi all,
I have a table with column A char(1000) and then alter the table to
varchar(1000) and update tableA set A = rtrim(A)
I know a stupid way to free up the space by create a new table and move the
data to the new table. Is there a command to free up the empty space?
Thanks in advance!Hi
DBCC DBREINDEX (if your table has indexes) , or DBCC SHRINKFILE
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:eVFE1vPkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> I have a table with column A char(1000) and then alter the table to
> varchar(1000) and update tableA set A = rtrim(A)
> I know a stupid way to free up the space by create a new table and move
> the data to the new table. Is there a command to free up the empty space?
> Thanks in advance!
>|||Hi,
The update using rtrim was not needed here as SQL Server does not store
spaces after the data. If you insert 'Pink Floyd ' SQL Server only
stores 'Pink Floyd'. Changing to varchar was enough.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Atenza" wrote:
> Hi all,
> I have a table with column A char(1000) and then alter the table to
> varchar(1000) and update tableA set A = rtrim(A)
> I know a stupid way to free up the space by create a new table and move the
> data to the new table. Is there a command to free up the empty space?
> Thanks in advance!
>
>|||Hi Ben,
I have done few tests on char and varchar. I found that insert 'Pink Floyd
' is different from ''Pink Floyd' into varchar. SQL Server does not store
spaces after the data applied to SQLSever 2005? coz i am using SQLSever
2000, is this the reason?
In CASE 1, use char(100)
In CASE 2, use varchar(100) by insert 'Pink Floyd '
In CASE 3, use varchar(100) by insert 'Pink Floyd'
In CASE 4, use char(100) and alter to varchar(100)
In CASE 5, use char(100) and move to new table varchar(100)
In CASE 4, for new create data, i think it should be saved spaces. But for
the old data, it seems that spaces cannot be released.
It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution or
i have misunderstand something?
Here is my test result:
CASE 1:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
db size 16128KB
CASE 2:
CREATE TABLE dbo.Table2
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table2 values('Pink Floyd ')
while (select count(*) from table2) < 100000
insert into table2 select * from table2
sp_spaceused table2
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 4552 KB 4488 KB 8 KB
56 KB
db size 5504KB
CASE 3:
CREATE TABLE dbo.Table3
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table3 values('Pink Floyd')
while (select count(*) from table3) < 100000
insert into table3 select * from table3
sp_spaceused table3
name rows reserved data index_size
unused
-- -- -- -- --
--
Table3 131072 3144 KB 3136 KB 8 KB
0 KB
db size 4096KB
CASE 4:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
COMMIT
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 15240 KB 15200 KB 8 KB
32 KB
CASE 5:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
CREATE TABLE dbo.Table3
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table3 select rtrim(col1) from table1
sp_spaceused table3
name rows reserved data index_size
unused
-- -- -- -- --
--
Table3 131072 3144 KB 3136 KB 8 KB
0 KB
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
> Hi,
> The update using rtrim was not needed here as SQL Server does not store
> spaces after the data. If you insert 'Pink Floyd ' SQL Server
> only
> stores 'Pink Floyd'. Changing to varchar was enough.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Atenza" wrote:
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move
>> the
>> data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>|||> SQL Server does not store
> spaces after the data applied to SQLSever 2005?
For char, SQL Server always store the specified length. It pads the string with spaces.
For varchar, SQL Server by default store trailing spaces. Whether or not to do this depends on the
setting of ANSI_PADDING when the table/column is *created*.
Rebuilding the indexes on the table should give you back the space. If the table doesn't have a
clustered index, then you either have to create on (and possibly drop it), or export/import.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atenza" <Atenza@.mail.hongkong.com> wrote in message news:utQ2DbRkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Ben,
> I have done few tests on char and varchar. I found that insert 'Pink Floyd ' is different from
> ''Pink Floyd' into varchar. SQL Server does not store
> spaces after the data applied to SQLSever 2005? coz i am using SQLSever 2000, is this the reason?
> In CASE 1, use char(100)
> In CASE 2, use varchar(100) by insert 'Pink Floyd '
> In CASE 3, use varchar(100) by insert 'Pink Floyd'
> In CASE 4, use char(100) and alter to varchar(100)
> In CASE 5, use char(100) and move to new table varchar(100)
> In CASE 4, for new create data, i think it should be saved spaces. But for the old data, it seems
> that spaces cannot be released.
> It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution or i have misunderstand
> something?
>
> Here is my test result:
> CASE 1:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> db size 16128KB
>
> CASE 2:
> CREATE TABLE dbo.Table2
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table2 values('Pink Floyd ')
> while (select count(*) from table2) < 100000
> insert into table2 select * from table2
> sp_spaceused table2
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 4552 KB 4488 KB 8 KB 56 KB
> db size 5504KB
>
> CASE 3:
> CREATE TABLE dbo.Table3
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table3 values('Pink Floyd')
> while (select count(*) from table3) < 100000
> insert into table3 select * from table3
> sp_spaceused table3
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
> db size 4096KB
>
> CASE 4:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_Table1
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.Table1)
> EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
> SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
> GO
> DROP TABLE dbo.Table1
> GO
> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
> GO
> COMMIT
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 15240 KB 15200 KB 8 KB 32 KB
>
>
> CASE 5:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> CREATE TABLE dbo.Table3
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table3 select rtrim(col1) from table1
> sp_spaceused table3
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>
>
>
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
>> Hi,
>> The update using rtrim was not needed here as SQL Server does not store
>> spaces after the data. If you insert 'Pink Floyd ' SQL Server only
>> stores 'Pink Floyd'. Changing to varchar was enough.
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "Atenza" wrote:
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move the
>> data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>
>|||thank you very much!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:utEoaQQkGHA.4672@.TK2MSFTNGP02.phx.gbl...
> Hi
> DBCC DBREINDEX (if your table has indexes) , or DBCC SHRINKFILE
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:eVFE1vPkGHA.3816@.TK2MSFTNGP02.phx.gbl...
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move
>> the data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>
>|||thank you very much! it works!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uKxJ6QSkGHA.4652@.TK2MSFTNGP05.phx.gbl...
>> SQL Server does not store
>> spaces after the data applied to SQLSever 2005?
> For char, SQL Server always store the specified length. It pads the string
> with spaces.
> For varchar, SQL Server by default store trailing spaces. Whether or not
> to do this depends on the setting of ANSI_PADDING when the table/column is
> *created*.
> Rebuilding the indexes on the table should give you back the space. If the
> table doesn't have a clustered index, then you either have to create on
> (and possibly drop it), or export/import.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:utQ2DbRkGHA.3816@.TK2MSFTNGP02.phx.gbl...
>> Hi Ben,
>> I have done few tests on char and varchar. I found that insert 'Pink
>> Floyd ' is different from ''Pink Floyd' into varchar. SQL Server does not
>> store
>> spaces after the data applied to SQLSever 2005? coz i am using SQLSever
>> 2000, is this the reason?
>> In CASE 1, use char(100)
>> In CASE 2, use varchar(100) by insert 'Pink Floyd '
>> In CASE 3, use varchar(100) by insert 'Pink Floyd'
>> In CASE 4, use char(100) and alter to varchar(100)
>> In CASE 5, use char(100) and move to new table varchar(100)
>> In CASE 4, for new create data, i think it should be saved spaces. But
>> for the old data, it seems that spaces cannot be released.
>> It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution
>> or i have misunderstand something?
>>
>> Here is my test result:
>> CASE 1:
>> CREATE TABLE dbo.Table1
>> (
>> col1 char(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table1 values('Pink Floyd ')
>> while (select count(*) from table1) < 100000
>> insert into table1 select * from table1
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
>> db size 16128KB
>>
>> CASE 2:
>> CREATE TABLE dbo.Table2
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table2 values('Pink Floyd ')
>> while (select count(*) from table2) < 100000
>> insert into table2 select * from table2
>> sp_spaceused table2
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 4552 KB 4488 KB 8 KB 56 KB
>> db size 5504KB
>>
>> CASE 3:
>> CREATE TABLE dbo.Table3
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table3 values('Pink Floyd')
>> while (select count(*) from table3) < 100000
>> insert into table3 select * from table3
>> sp_spaceused table3
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>> db size 4096KB
>>
>> CASE 4:
>> CREATE TABLE dbo.Table1
>> (
>> col1 char(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table1 values('Pink Floyd ')
>> while (select count(*) from table1) < 100000
>> insert into table1 select * from table1
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
>> BEGIN TRANSACTION
>> SET QUOTED_IDENTIFIER ON
>> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
>> SET ARITHABORT ON
>> SET NUMERIC_ROUNDABORT OFF
>> SET CONCAT_NULL_YIELDS_NULL ON
>> SET ANSI_NULLS ON
>> SET ANSI_PADDING ON
>> SET ANSI_WARNINGS ON
>> COMMIT
>> BEGIN TRANSACTION
>> CREATE TABLE dbo.Tmp_Table1
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> GO
>> IF EXISTS(SELECT * FROM dbo.Table1)
>> EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
>> SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
>> GO
>> DROP TABLE dbo.Table1
>> GO
>> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
>> GO
>> COMMIT
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 15240 KB 15200 KB 8 KB 32 KB
>>
>>
>> CASE 5:
>> CREATE TABLE dbo.Table1
>> (
>> col1 char(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table1 values('Pink Floyd ')
>> while (select count(*) from table1) < 100000
>> insert into table1 select * from table1
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
>> CREATE TABLE dbo.Table3
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table3 select rtrim(col1) from table1
>> sp_spaceused table3
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>>
>>
>>
>> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
>> news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
>> Hi,
>> The update using rtrim was not needed here as SQL Server does not store
>> spaces after the data. If you insert 'Pink Floyd ' SQL Server
>> only
>> stores 'Pink Floyd'. Changing to varchar was enough.
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "Atenza" wrote:
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move
>> the
>> data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>
>>
>

How to find Data Space Used?

This feels like a stupid question as it seems very basic, but I have
been unable to find an answer to it!
Basically all I want is a SQL command to return the % data space used
in a particular database. I can find it for the log space with the
following command,
DBCC perflog
but not one that returns the % of the data space used. Does one
exist? I am using SQL 2000 BTW, but would be interested in all
versions of SQL...
Many thanks!sp_spaceused '
<nielsonj1976@.yahoo.co.uk> wrote in message
news:1188894429.748919.35510@.d55g2000hsg.googlegroups.com...
> This feels like a stupid question as it seems very basic, but I have
> been unable to find an answer to it!
> Basically all I want is a SQL command to return the % data space used
> in a particular database. I can find it for the log space with the
> following command,
> DBCC perflog
> but not one that returns the % of the data space used. Does one
> exist? I am using SQL 2000 BTW, but would be interested in all
> versions of SQL...
> Many thanks!
>|||sp_spaceused is unreliable in 2000 and doesn't account for all pages in
2005.
This code is a little sloppy, but it works. Just note, it is counting the
64KB extents allocated/used.
Oh, and though I wrote it, this is really as much Tibor's code as it is mine
:)
DECLARE @.DB sysname
DECLARE @.SQL nvarchar(255)
if exists ( select * from tempdb..sysobjects where name LIKE
'#FileStats__%' ) drop table
#FileStats
CREATE TABLE #FileStats(
[FileId] INT,
[FileGroup] INT,
[TotalExtents] INT,
[UsedExtents] INT,
[Name] sysname,
[Filename] varchar(255)
)
DECLARE @.FileStats TABLE (
[FileId] INT,
[FileGroup] INT,
[TotalExtents] INT,
[UsedExtents] INT,
[Name] sysname,
[Filename] varchar(255)
)
DECLARE cDatabases CURSOR FOR
SELECT sdb.name
FROM master.dbo.sysdatabases sdb
WHERE status & 32 != 32
AND status & 64 != 64
AND status & 128 != 128
AND status & 256 != 256
AND status & 512 != 512
AND status & 1024 != 1024
AND status & 4096 != 4096
AND status & 32768 !=32768
OPEN cDatabases
FETCH FROM cDatabases INTO @.DB
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
DELETE FROM #FileStats
SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
SHOWFILESTATS'')'
EXEC (@.SQL)
UPDATE #FileStats SET name = @.DB
INSERT INTO @.FileStats SELECT * FROM #FileStats
FETCH FROM cDatabases INTO @.DB
END
CLOSE cDatabases
DEALLOCATE cDatabases
SELECT
[Name]
,[TotalExtents]*64/1024. AS TotalExtInMB
,[UsedExtents]*64/1024. AS UsedExtInMB
,([TotalExtents] - [UsedExtents]) / 16. AS UnAllocExtInMB --* 64 / 1024. AS
UnAllocExtInMB
FROM @.FileStats
--exec sp_spaceused
DBCC sqlperf(logspace)
<nielsonj1976@.yahoo.co.uk> wrote in message
news:1188894429.748919.35510@.d55g2000hsg.googlegroups.com...
> This feels like a stupid question as it seems very basic, but I have
> been unable to find an answer to it!
> Basically all I want is a SQL command to return the % data space used
> in a particular database. I can find it for the log space with the
> following command,
> DBCC perflog
> but not one that returns the % of the data space used. Does one
> exist? I am using SQL 2000 BTW, but would be interested in all
> versions of SQL...
> Many thanks!
>|||I like the output, Jay.
Anything you like to share with the public? If you have a blog or a page somewhere, I can link to it
from my blog...
(
How about adding something like below to the first SELECT column list?
...
,CASE WHEN CAST([UsedExtents] AS decimal(12,2))/TotalExtents > 0.7 THEN 1 ELSE 0 END AS "Almost
Full"
FROM @.FileStats
)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:OMN8UQ77HHA.5796@.TK2MSFTNGP05.phx.gbl...
> sp_spaceused is unreliable in 2000 and doesn't account for all pages in 2005.
> This code is a little sloppy, but it works. Just note, it is counting the 64KB extents
> allocated/used.
> Oh, and though I wrote it, this is really as much Tibor's code as it is mine :)
> DECLARE @.DB sysname
> DECLARE @.SQL nvarchar(255)
> if exists ( select * from tempdb..sysobjects where name LIKE '#FileStats__%' ) drop table
> #FileStats
> CREATE TABLE #FileStats(
> [FileId] INT,
> [FileGroup] INT,
> [TotalExtents] INT,
> [UsedExtents] INT,
> [Name] sysname,
> [Filename] varchar(255)
> )
> DECLARE @.FileStats TABLE (
> [FileId] INT,
> [FileGroup] INT,
> [TotalExtents] INT,
> [UsedExtents] INT,
> [Name] sysname,
> [Filename] varchar(255)
> )
> DECLARE cDatabases CURSOR FOR
> SELECT sdb.name
> FROM master.dbo.sysdatabases sdb
> WHERE status & 32 != 32
> AND status & 64 != 64
> AND status & 128 != 128
> AND status & 256 != 256
> AND status & 512 != 512
> AND status & 1024 != 1024
> AND status & 4096 != 4096
> AND status & 32768 !=32768
> OPEN cDatabases
> FETCH FROM cDatabases INTO @.DB
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> DELETE FROM #FileStats
> SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC SHOWFILESTATS'')'
> EXEC (@.SQL)
> UPDATE #FileStats SET name = @.DB
> INSERT INTO @.FileStats SELECT * FROM #FileStats
> FETCH FROM cDatabases INTO @.DB
> END
> CLOSE cDatabases
> DEALLOCATE cDatabases
> SELECT
> [Name]
> ,[TotalExtents]*64/1024. AS TotalExtInMB
> ,[UsedExtents]*64/1024. AS UsedExtInMB
> ,([TotalExtents] - [UsedExtents]) / 16. AS UnAllocExtInMB --* 64 / 1024. AS UnAllocExtInMB
> FROM @.FileStats
> --exec sp_spaceused
> DBCC sqlperf(logspace)
> <nielsonj1976@.yahoo.co.uk> wrote in message
> news:1188894429.748919.35510@.d55g2000hsg.googlegroups.com...
>> This feels like a stupid question as it seems very basic, but I have
>> been unable to find an answer to it!
>> Basically all I want is a SQL command to return the % data space used
>> in a particular database. I can find it for the log space with the
>> following command,
>> DBCC perflog
>> but not one that returns the % of the data space used. Does one
>> exist? I am using SQL 2000 BTW, but would be interested in all
>> versions of SQL...
>> Many thanks!
>|||I don't have a blog, but I've been toying with creating one of those fee web
pages, like geocities, or something as I have a few scripts I would like to
share.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O1vWA%2377HHA.1188@.TK2MSFTNGP04.phx.gbl...
>I like the output, Jay.
> Anything you like to share with the public? If you have a blog or a page
> somewhere, I can link to it from my blog...
> (
> How about adding something like below to the first SELECT column list?
> ...
> ,CASE WHEN CAST([UsedExtents] AS decimal(12,2))/TotalExtents > 0.7 THEN 1
> ELSE 0 END AS "Almost Full"
> FROM @.FileStats
> )
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:OMN8UQ77HHA.5796@.TK2MSFTNGP05.phx.gbl...
>> sp_spaceused is unreliable in 2000 and doesn't account for all pages in
>> 2005.
>> This code is a little sloppy, but it works. Just note, it is counting the
>> 64KB extents allocated/used.
>> Oh, and though I wrote it, this is really as much Tibor's code as it is
>> mine :)
>> DECLARE @.DB sysname
>> DECLARE @.SQL nvarchar(255)
>> if exists ( select * from tempdb..sysobjects where name LIKE
>> '#FileStats__%' ) drop table
>> #FileStats
>> CREATE TABLE #FileStats(
>> [FileId] INT,
>> [FileGroup] INT,
>> [TotalExtents] INT,
>> [UsedExtents] INT,
>> [Name] sysname,
>> [Filename] varchar(255)
>> )
>> DECLARE @.FileStats TABLE (
>> [FileId] INT,
>> [FileGroup] INT,
>> [TotalExtents] INT,
>> [UsedExtents] INT,
>> [Name] sysname,
>> [Filename] varchar(255)
>> )
>> DECLARE cDatabases CURSOR FOR
>> SELECT sdb.name
>> FROM master.dbo.sysdatabases sdb
>> WHERE status & 32 != 32
>> AND status & 64 != 64
>> AND status & 128 != 128
>> AND status & 256 != 256
>> AND status & 512 != 512
>> AND status & 1024 != 1024
>> AND status & 4096 != 4096
>> AND status & 32768 !=32768
>> OPEN cDatabases
>> FETCH FROM cDatabases INTO @.DB
>> WHILE (@.@.FETCH_STATUS = 0)
>> BEGIN
>> DELETE FROM #FileStats
>> SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
>> SHOWFILESTATS'')'
>> EXEC (@.SQL)
>> UPDATE #FileStats SET name = @.DB
>> INSERT INTO @.FileStats SELECT * FROM #FileStats
>> FETCH FROM cDatabases INTO @.DB
>> END
>> CLOSE cDatabases
>> DEALLOCATE cDatabases
>> SELECT
>> [Name]
>> ,[TotalExtents]*64/1024. AS TotalExtInMB
>> ,[UsedExtents]*64/1024. AS UsedExtInMB
>> ,([TotalExtents] - [UsedExtents]) / 16. AS UnAllocExtInMB --* 64 / 1024.
>> AS UnAllocExtInMB
>> FROM @.FileStats
>> --exec sp_spaceused
>> DBCC sqlperf(logspace)
>> <nielsonj1976@.yahoo.co.uk> wrote in message
>> news:1188894429.748919.35510@.d55g2000hsg.googlegroups.com...
>> This feels like a stupid question as it seems very basic, but I have
>> been unable to find an answer to it!
>> Basically all I want is a SQL command to return the % data space used
>> in a particular database. I can find it for the log space with the
>> following command,
>> DBCC perflog
>> but not one that returns the % of the data space used. Does one
>> exist? I am using SQL 2000 BTW, but would be interested in all
>> versions of SQL...
>> Many thanks!
>>
>