How to free up the space ...

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 th
e
> data to the new table. Is there a command to free up the empty space?
> Thanks in advance!
>
>|||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 th
e
> 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...[vbcol=seagreen]
> 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:
>|||> 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 w
ith 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 t
able doesn't have a
clustered index, then you either have to create on (and possibly drop it), o
r 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 20
00, 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 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...[vbcol=seagreen]
> 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:
>|||> 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 w
ith 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 t
able doesn't have a
clustered index, then you either have to create on (and possibly drop it), o
r 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 20
00, 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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...
>|||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...
>sql

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 free up SQL 2000 transaction log space?

I have tried all of the Shrink database options provided by Enterprise
Manager to try and shrink my transaction log from its current size of 2Gig,
but no matter what I execute it stays 2Gig.
I selected the option to reduce to its minimum allowable size (which it
showed as about 300Meg), but even after that the file still shows as 2Gig.
Is there a different mechanism I need to do to free up this huge amount of
space? A stored procedure or other system proc?
Many thanks!
Marksee
http://www.nigelrivett.net/SQLAdmin...ileGrows_1.html
"Mark Findlay" wrote:

> I have tried all of the Shrink database options provided by Enterprise
> Manager to try and shrink my transaction log from its current size of 2Gig
,
> but no matter what I execute it stays 2Gig.
> I selected the option to reduce to its minimum allowable size (which it
> showed as about 300Meg), but even after that the file still shows as 2Gig.
> Is there a different mechanism I need to do to free up this huge amount of
> space? A stored procedure or other system proc?
> Many thanks!
> Mark
>|||Nigel explains on his site about LSN's. For a large database to have a 2 GI
G
log is not unheard of. For example, if a backup hasn't been performed for
several days and there is a rollback or a deadlock and the database has to
track forward from that time, the database log file can swell quickly. You
may want to look at the cause of the log size moreso than just trying to
shrink the log. If there is an unresolved transaction from a particular day
and the LSN's for a part of the log have overlapping values (ie LSN start of
Wednesday is less than LSN start for Tuesday and matches LSN end of Monday),
you may be able to simply take the database offline and run a restore
operation to clean up the log. Nigel's instruction page is very clear. I
hope it helps you with your problem.
--
Regards,
Jamie
"Mark Findlay" wrote:

> I have tried all of the Shrink database options provided by Enterprise
> Manager to try and shrink my transaction log from its current size of 2Gig
,
> but no matter what I execute it stays 2Gig.
> I selected the option to reduce to its minimum allowable size (which it
> showed as about 300Meg), but even after that the file still shows as 2Gig.
> Is there a different mechanism I need to do to free up this huge amount of
> space? A stored procedure or other system proc?
> Many thanks!
> Mark
>|||Thanks for the great help Jamie, that answered my question and more!
Thanks,
Mark
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:30313C61-ECCC-4294-8AC9-8EB6AD22FF78@.microsoft.com...
> Nigel explains on his site about LSN's. For a large database to have a 2
> GIG
> log is not unheard of. For example, if a backup hasn't been performed for
> several days and there is a rollback or a deadlock and the database has to
> track forward from that time, the database log file can swell quickly.
> You
> may want to look at the cause of the log size moreso than just trying to
> shrink the log. If there is an unresolved transaction from a particular
> day
> and the LSN's for a part of the log have overlapping values (ie LSN start
> of
> Wednesday is less than LSN start for Tuesday and matches LSN end of
> Monday),
> you may be able to simply take the database offline and run a restore
> operation to clean up the log. Nigel's instruction page is very clear. I
> hope it helps you with your problem.
> --
> Regards,
> Jamie
>
> "Mark Findlay" wrote:
>

How to Free SQL Server 7 Transaction Log Space

Hi Guys,
My company SQL Server has allocated 6G+ of space for the transaction log space. However, only about 300MB of them are used.
Is there any chance (ways) that I can free up these free space which are allocated for the transaction log?
Thanks in advance.
TangQ1 Is there any chance (ways) that I can free up these free space which are allocated for the transaction log?
A1 Yes. For example:

USE YourDB
GO
DBCC SHRINKFILE (YourDBLog, 600)|||This doesn't work.
The free spaced allocated to Transaction Log still remain.

I have attached an image of the space allocation. Is the Transaction Log free space shrinkable?

Originally posted by DBA
A1 Yes. For example:

USE YourDB
GO
DBCC SHRINKFILE (YourDBLog, 600)|||RE: This doesn't work.
The free spaced allocated to Transaction Log still remain.

I have attached an image of the space allocation. Is the Transaction Log free space shrinkable?

It is rarely instantaneous on a heavily used production DB. On such systems it can take a while (especially on large systems with large logs and lots of transactions that stay open for some time) eventually the VLF moves and it does shrink; though I have heard claims that several shrink statements are necessary. If it fails because the minnimum size is > what you are trying to shrink it to, it will fail.|||Thank you for your reply, I guess it is almost time to bring down the server for maintenance... :D

Originally posted by DBA
RE:

It is rarely instantaneous on a heavily used production DB. On such systems it can take a while (especially on large systems with large logs and lots of transactions that stay open for some time) eventually the VLF moves and it does shrink; though I have heard claims that several shrink statements are necessary. If it fails because the minnimum size is > what you are trying to shrink it to, it will fail.|||RE: Thank you for your reply, I guess it is almost time to bring down the server for maintenance... :D

You are welcome.

You may be interested to know that MS supposedly changed the shrink algorithm somewhat in Sql Server 2k to make it act noticeably more quickly (apparently many users do not like to wait).

Actually I think actions taken by impatient 7.0 users to find a "quicker way" caused frequent headaches at Microsoft, and that led to the speedier shrink performance in Sql Server 2k. ;)|||The following is from BOL:

Shrinking a log is dependent on first truncating the log. Log truncation does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log. A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size.

The unit of size reduction is a virtual log. For example, if you have a 600 MB log file that has been divided into six 100 MB virtual logs, the size of the log file can only be reduced in 100 MB increments. The file size can be reduced to sizes such as 500 MB or 400 MB, but it cannot be reduced to sizes such as 433 MB or 525 MB.

Virtual logs that hold part of the logical log cannot be freed. If all the virtual logs in a log file hold parts of the logical log, the file cannot be shrink until a truncation marks one or more of the virtual logs at the end of the physical log as inactive.

When any file is shrunk, the space freed must come from the end of the file. When a transaction log file is shrunk, enough virtual logs from the end of the file are freed to reduce the log to the size requested by the user. The target_size specified by the user is rounded to the next highest virtual log boundary.sql

How to force space allocation to a SS2000 DB?

I have to force a space allocation to a DB file in order to stop a Diagnostic Manager alert which states that the database is over 80% full. Now, I don't think this alert is rational, nor do I think it is useful in any way but that's the way it goes. My only option is to allocate more space to the DB so that the space used will fall below 80% so the alert will stop being issued.

So, can you tell me how to force a space allocation on an existing db file?

Thanks,

Michael

Hi Michael,

You have to define the initial size for your file as big as you need. You can do it with the MS SQL Server Manager Studio, Databases --> mydatabase --> right clic --> properties --> files

You can do it like this code sample

Code Snippet

USE [master]

GO

ALTER DATABASE [mydatabase] MODIFY FILE ( NAME = N'mydatabase_file_name', SIZE = 10240KB )

GO

Hope it helps.

Laurent

How to find which tables allocates disk space.

I just had truncate a table in a database that had grown too big, due to fawlty log settings. Now the database only hold 20 MB of data but the allocated size of the database is still 4 GB.
Is there any way I can find which one of the tables is responsible for allocating that amount of disk space? I haven't found any view that shows this. Nor did I find any interface to show any size properties regarding the system tables, like the "Task Pad" do for tables created by users.
please help me out here.
kind regards
sven
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...Sven
To reduce a physical size of the database perform DBCC SHRINKFILE command
Look at sp_spaceused stored procedure in the BOL.
"sven ekdahl" <sveek328@.student.liu.se> wrote in message
news:%23vS5wSZxEHA.3108@.TK2MSFTNGP14.phx.gbl...
> I just had truncate a table in a database that had grown too big, due to
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...|||Hi
Have a look at DBCC SHRINKDATABASE in BOL.
Regards
Mike
"sven ekdahl" wrote:
> I just had truncate a table in a database that had grown too big, due to fawlty log settings. Now the database only hold 20 MB of data but the allocated size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for allocating that amount of disk space? I haven't found any view that shows this. Nor did I find any interface to show any size properties regarding the system tables, like the "Task Pad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
>|||Mike
By using DBCC SHRINKDATABASE you cannot reduce a size of the database
smaller than this minimum size.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:A4C1B7E7-8C03-44C9-9BCD-406758D7937E@.microsoft.com...
> Hi
> Have a look at DBCC SHRINKDATABASE in BOL.
> Regards
> Mike
> "sven ekdahl" wrote:
> > I just had truncate a table in a database that had grown too big, due to
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.
> >
> > Is there any way I can find which one of the tables is responsible for
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.
> >
> > please help me out here.
> >
> > kind regards
> > sven
> >
> > **********************************************************************
> > Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> > Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
> >|||**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

How to find which tables allocates disk space.

I just had truncate a table in a database that had grown too big, due to fawlty log settings. Now the database only hold 20 MB of data but the allocated size of the database is still 4 GB.
Is there any way I can find which one of the tables is responsible for allocating that amount of disk space? I haven't found any view that shows this. Nor did I find any interface to show any size properties regarding the system tables, like the "Task Pad
" do for tables created by users.
please help me out here.
kind regards
sven
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Sven
To reduce a physical size of the database perform DBCC SHRINKFILE command
Look at sp_spaceused stored procedure in the BOL.
"sven ekdahl" <sveek328@.student.liu.se> wrote in message
news:%23vS5wSZxEHA.3108@.TK2MSFTNGP14.phx.gbl...
> I just had truncate a table in a database that had grown too big, due to
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
|||Hi
Have a look at DBCC SHRINKDATABASE in BOL.
Regards
Mike
"sven ekdahl" wrote:

> I just had truncate a table in a database that had grown too big, due to fawlty log settings. Now the database only hold 20 MB of data but the allocated size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for allocating that amount of disk space? I haven't found any view that shows this. Nor did I find any interface to show any size properties regarding the system tables, like the "Task P
ad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
>
|||Mike
By using DBCC SHRINKDATABASE you cannot reduce a size of the database
smaller than this minimum size.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:A4C1B7E7-8C03-44C9-9BCD-406758D7937E@.microsoft.com...[vbcol=seagreen]
> Hi
> Have a look at DBCC SHRINKDATABASE in BOL.
> Regards
> Mike
> "sven ekdahl" wrote:
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.[vbcol=seagreen]
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.[vbcol=seagreen]
ASP.NET resources...[vbcol=seagreen]
|||how do I call this SHRINKFILE procedure? What's meant with the logical file name of a database?
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||To find the logical file names you can use : sp_helpdb <dbanme>
"sven ekdahl" <sveek328@.student.liu.se> wrote in message
news:OIxjYiaxEHA.908@.TK2MSFTNGP11.phx.gbl...
> how do I call this SHRINKFILE procedure? What's meant with the logical
> file name of a database?
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
> ASP.NET resources...
|||************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

How to find which tables allocates disk space.

I just had truncate a table in a database that had grown too big, due to faw
lty log settings. Now the database only hold 20 MB of data but the allocated
size of the database is still 4 GB.
Is there any way I can find which one of the tables is responsible for alloc
ating that amount of disk space? I haven't found any view that shows this. N
or did I find any interface to show any size properties regarding the system
tables, like the "Task Pad
" do for tables created by users.
please help me out here.
kind regards
sven
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Sven
To reduce a physical size of the database perform DBCC SHRINKFILE command
Look at sp_spaceused stored procedure in the BOL.
"sven ekdahl" <sveek328@.student.liu.se> wrote in message
news:%23vS5wSZxEHA.3108@.TK2MSFTNGP14.phx.gbl...
> I just had truncate a table in a database that had grown too big, due to
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...|||Hi
Have a look at DBCC SHRINKDATABASE in BOL.
Regards
Mike
"sven ekdahl" wrote:

> I just had truncate a table in a database that had grown too big, due to f
awlty log settings. Now the database only hold 20 MB of data but the allocat
ed size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for allocating
that amount of disk space? I haven't found any view that shows this. Nor did I find
any interface to show any size properties regarding the system tables, like the "Tas
k P
ad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NE
T resources...
>|||Mike
By using DBCC SHRINKDATABASE you cannot reduce a size of the database
smaller than this minimum size.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:A4C1B7E7-8C03-44C9-9BCD-406758D7937E@.microsoft.com...[vbcol=seagreen]
> Hi
> Have a look at DBCC SHRINKDATABASE in BOL.
> Regards
> Mike
> "sven ekdahl" wrote:
>
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.[vbcol=seagreen]
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.[vbcol=seagreen]
ASP.NET resources...[vbcol=seagreen]|||how do I call this SHRINKFILE procedure? What's meant with the logical file
name of a database?
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||To find the logical file names you can use : sp_helpdb <dbanme>
"sven ekdahl" <sveek328@.student.liu.se> wrote in message
news:OIxjYiaxEHA.908@.TK2MSFTNGP11.phx.gbl...
> how do I call this SHRINKFILE procedure? What's meant with the logical
> file name of a database?
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
> ASP.NET resources...|||****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

How to find space used by transaction logs ?

Hello all,
how can I find space in use in transaction log file of a database
at given moment of time ? ( programmatically)
( not using dbcc sqlperf(logspace)
thank you
jagdishHere is the code :

declare @.log_size float(53)
declare @.log_space_used float(53)
declare @.total decimal(20,4)

select @.log_size = cntr_value from master.dbo.sysperfinfo where instance_name = 'pubs' and counter_name ='Log File(s) Size (KB)'
select @.log_space_used = cntr_value from master.dbo.sysperfinfo where instance_name = 'pubs' and counter_name ='Log File(s) Used Size (KB)'

set @.total = (@.log_space_used/@.log_size) * 100

select 'Using ' + convert(varchar(20),@.total) + '% (' + convert(varchar(20),@.log_space_used) + ' MB) of ' + convert(varchar(20),@.log_size) + ' MB' as 'Report'

.................
note : this is script against pubs database ,so change the name !

Cheers .
srdjan|||Thank you for your help

How to find out how much space is being used in a file

Can anyone tell me how to find out how much space is being used in single
file on a database ?
Thank You,
-Nags
Try;
select
fileproperty ('MyFile', 'SpaceUsed')
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Nags" <nags@.DontSpamMe.com> wrote in message
news:Oo6tq7SLEHA.2068@.TK2MSFTNGP11.phx.gbl...
Can anyone tell me how to find out how much space is being used in single
file on a database ?
Thank You,
-Nags
|||how about
select [size]* 8 as [Size in KB],[name] from database..sysfiles
Don P
|||It's generally bad practice to query system tables directly, since they can
be altered at any time, e.g. service pack, hotfix, etc.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Don P" <dpfister@.noemail.noemail> wrote in message
news:uLfM0MTLEHA.3904@.TK2MSFTNGP09.phx.gbl...
how about
select [size]* 8 as [Size in KB],[name] from database..sysfiles
Don P
|||This would give the size occupied by the file on the file system, not the
space used in that file. There is a possibility that the file was
preallocated, say upto 1 Gig, but the data in the file is only 10 MB.
-Nags
"Don P" <dpfister@.noemail.noemail> wrote in message
news:uLfM0MTLEHA.3904@.TK2MSFTNGP09.phx.gbl...
> how about
> select [size]* 8 as [Size in KB],[name] from database..sysfiles
> Don P
>

How to find out how much space is being used in a file

Can anyone tell me how to find out how much space is being used in single
file on a database ?
Thank You,
-NagsTry;
select
fileproperty ('MyFile', 'SpaceUsed')
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Nags" <nags@.DontSpamMe.com> wrote in message
news:Oo6tq7SLEHA.2068@.TK2MSFTNGP11.phx.gbl...
Can anyone tell me how to find out how much space is being used in single
file on a database ?
Thank You,
-Nags|||how about
select [size]* 8 as [Size in KB],[name] from database..sysfiles
Don P|||It's generally bad practice to query system tables directly, since they can
be altered at any time, e.g. service pack, hotfix, etc.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Don P" <dpfister@.noemail.noemail> wrote in message
news:uLfM0MTLEHA.3904@.TK2MSFTNGP09.phx.gbl...
how about
select [size]* 8 as [Size in KB],[name] from database..sysfiles
Don P|||This would give the size occupied by the file on the file system, not the
space used in that file. There is a possibility that the file was
preallocated, say upto 1 Gig, but the data in the file is only 10 MB.
-Nags
"Don P" <dpfister@.noemail.noemail> wrote in message
news:uLfM0MTLEHA.3904@.TK2MSFTNGP09.phx.gbl...
> how about
> select [size]* 8 as [Size in KB],[name] from database..sysfile
s
> Don P
>

How to find out how much space is being used in a file

Can anyone tell me how to find out how much space is being used in single
file on a database ?
Thank You,
-NagsTry;
select
fileproperty ('MyFile', 'SpaceUsed')
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Nags" <nags@.DontSpamMe.com> wrote in message
news:Oo6tq7SLEHA.2068@.TK2MSFTNGP11.phx.gbl...
Can anyone tell me how to find out how much space is being used in single
file on a database ?
Thank You,
-Nags|||how about
select [size]* 8 as [Size in KB],[name] from database..sysfiles
Don P|||It's generally bad practice to query system tables directly, since they can
be altered at any time, e.g. service pack, hotfix, etc.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Don P" <dpfister@.noemail.noemail> wrote in message
news:uLfM0MTLEHA.3904@.TK2MSFTNGP09.phx.gbl...
how about
select [size]* 8 as [Size in KB],[name] from database..sysfiles
Don P|||This would give the size occupied by the file on the file system, not the
space used in that file. There is a possibility that the file was
preallocated, say upto 1 Gig, but the data in the file is only 10 MB.
-Nags
"Don P" <dpfister@.noemail.noemail> wrote in message
news:uLfM0MTLEHA.3904@.TK2MSFTNGP09.phx.gbl...
> how about
> select [size]* 8 as [Size in KB],[name] from database..sysfiles
> Don P
>

How to find out free space on a server ?any Script for this?

HI ,
I have a problem ,i am using solaries OS..,and i need to know how to
findout free space on a server ,which includes several databases .
Thanks
Suresh .
I assume you didn't mean to find some sort of free space on Solaris.
Anyway, it would help if you can be more spcific on what free space you were
referring to. Free space in a database, or free space on a given drive on the
server, etc?
Linchi
"kamma.venkatasuresh@.gmail.com" wrote:

> HI ,
> I have a problem ,i am using solaries OS..,and i need to know how to
> findout free space on a server ,which includes several databases .
> Thanks
> Suresh .
>

How to find out free space on a server ?any Script for this?

HI ,
I have a problem ,i am using solaries OS..,and i need to know how to
findout free space on a server ,which includes several databases .
Thanks
Suresh .I assume you didn't mean to find some sort of free space on Solaris.
Anyway, it would help if you can be more spcific on what free space you were
referring to. Free space in a database, or free space on a given drive on the
server, etc?
Linchi
"kamma.venkatasuresh@.gmail.com" wrote:
> HI ,
> I have a problem ,i am using solaries OS..,and i need to know how to
> findout free space on a server ,which includes several databases .
> Thanks
> Suresh .
>

How to find Free disk space available from SQL query

Hi all,
I have to find free disc space in a drive from SQL Query and i am using MSSQL200. Does any body know the command for the same.

Also if i want to find hard disk space of a different machine on network, it is possible to get the data??

Also can i get the CPU/Memory usage data of our machine by some SQL/C++ commands??

Please let me know if u know any of the answer...

thanks
Alokxp_fixeddrives for free disk space...looking for others...

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!
>>
>