calculate the size of a database and backup it.

Hi all,
please show me the way how to calculate the size of a database in SQL 2000. Whith this size we have already calculated, how much space need to make a backup file for this database.
thanks.Easiest way is through Enterprise manager - via the View - TaskPad options. This will show you size of data file(s) and log file(s).

Backup size will be the sum of these sizes approx.

You can also get it from the sysfiles table.|||Originally posted by thanhtung2003
Hi all,
please show me the way how to calculate the size of a database in SQL 2000. Whith this size we have already calculated, how much space need to make a backup file for this database.

thanks.
If you want to find out programaticaly use sp_spaceused|||Originally posted by smasanam
If you want to find out programaticaly use sp_spaceused

Hi,
please, tell me how to use it ?|||Please consult the Microsoft Transact-SQL Reference, specifically 'System Stored Procedures'|||create table #DbSize (name nvarchar(30),
rows char(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18))

exec <your database name>..sp_msforeachtable @.command1 = 'insert into #DbSize exec dbs..sp_spaceused [?]'

select sum(cast(replace(Reserved, 'KB','')as int)) From #DbSize

drop table #DbSize

Calculate row size

Hello everybody:
How can I do to calculate the record size in bytes, and the table size in
bytes? Does exist any stored procedure to do that '
Thanks
Richard_SQLTable size:
EXEC sp_spaceused 'table_name'
For row size, you can average by the above result / SELECT COUNT(*) FROM
table_name
For individual rows, this gets a little trickier because there is overhead
for certain datatypes, and whether the data is nullable and/or is null.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> Hello everybody:
> How can I do to calculate the record size in bytes, and the table size in
> bytes? Does exist any stored procedure to do that '
> Thanks
> Richard_SQL|||Richard,
Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
Also, for more exact calculations concerning table and row size, see
"Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
related topics for calculating table size for a heap table and a clustered
table.
Ron
--
Ron Talmage
SQL Server MVP
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> Table size:
> EXEC sp_spaceused 'table_name'
> For row size, you can average by the above result / SELECT COUNT(*) FROM
> table_name
> For individual rows, this gets a little trickier because there is overhead
> for certain datatypes, and whether the data is nullable and/or is null.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
> news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> > Hello everybody:
> >
> > How can I do to calculate the record size in bytes, and the table size
in
> > bytes? Does exist any stored procedure to do that '
> >
> > Thanks
> >
> > Richard_SQL
>|||Thanks a lot for your help.
Richard
"Ron Talmage" wrote:
> Richard,
> Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
> Also, for more exact calculations concerning table and row size, see
> "Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
> related topics for calculating table size for a heap table and a clustered
> table.
> Ron
> --
> Ron Talmage
> SQL Server MVP
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> > Table size:
> >
> > EXEC sp_spaceused 'table_name'
> >
> > For row size, you can average by the above result / SELECT COUNT(*) FROM
> > table_name
> >
> > For individual rows, this gets a little trickier because there is overhead
> > for certain datatypes, and whether the data is nullable and/or is null.
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
> > news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> > > Hello everybody:
> > >
> > > How can I do to calculate the record size in bytes, and the table size
> in
> > > bytes? Does exist any stored procedure to do that '
> > >
> > > Thanks
> > >
> > > Richard_SQL
> >
> >
>
>

Calculate row size

Hello everybody:
How can I do to calculate the record size in bytes, and the table size in
bytes? Does exist any stored procedure to do that '
Thanks
Richard_SQLTable size:
EXEC sp_spaceused 'table_name'
For row size, you can average by the above result / SELECT COUNT(*) FROM
table_name
For individual rows, this gets a little trickier because there is overhead
for certain datatypes, and whether the data is nullable and/or is null.
http://www.aspfaq.com/
(Reverse address to reply.)
"Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> Hello everybody:
> How can I do to calculate the record size in bytes, and the table size in
> bytes? Does exist any stored procedure to do that '
> Thanks
> Richard_SQL|||Richard,
Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
Also, for more exact calculations concerning table and row size, see
"Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
related topics for calculating table size for a heap table and a clustered
table.
Ron
--
Ron Talmage
SQL Server MVP
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> Table size:
> EXEC sp_spaceused 'table_name'
> For row size, you can average by the above result / SELECT COUNT(*) FROM
> table_name
> For individual rows, this gets a little trickier because there is overhead
> for certain datatypes, and whether the data is nullable and/or is null.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
> news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
in[vbcol=seagreen]
>|||Thanks a lot for your help.
Richard
"Ron Talmage" wrote:

> Richard,
> Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
> Also, for more exact calculations concerning table and row size, see
> "Estimating the Size of a Table" in SQL Server 2000 Books Online. There ar
e
> related topics for calculating table size for a heap table and a clustered
> table.
> Ron
> --
> Ron Talmage
> SQL Server MVP
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> in
>
>

Calculate row size

Hello everybody:
How can I do to calculate the record size in bytes, and the table size in
bytes? Does exist any stored procedure to do that ?
Thanks
Richard_SQL
Table size:
EXEC sp_spaceused 'table_name'
For row size, you can average by the above result / SELECT COUNT(*) FROM
table_name
For individual rows, this gets a little trickier because there is overhead
for certain datatypes, and whether the data is nullable and/or is null.
http://www.aspfaq.com/
(Reverse address to reply.)
"Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> Hello everybody:
> How can I do to calculate the record size in bytes, and the table size in
> bytes? Does exist any stored procedure to do that ?
> Thanks
> Richard_SQL
|||Richard,
Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
Also, for more exact calculations concerning table and row size, see
"Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
related topics for calculating table size for a heap table and a clustered
table.
Ron
Ron Talmage
SQL Server MVP
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Table size:
> EXEC sp_spaceused 'table_name'
> For row size, you can average by the above result / SELECT COUNT(*) FROM
> table_name
> For individual rows, this gets a little trickier because there is overhead
> for certain datatypes, and whether the data is nullable and/or is null.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
> news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
in
>
|||Thanks a lot for your help.
Richard
"Ron Talmage" wrote:

> Richard,
> Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
> Also, for more exact calculations concerning table and row size, see
> "Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
> related topics for calculating table size for a heap table and a clustered
> table.
> Ron
> --
> Ron Talmage
> SQL Server MVP
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> in
>
>

Caclulate database size and free space left

Dear all,
Is that possible to calculate the database size and free space left by t-sql
script?
Ivan
Ivan
sp_helpdb 'northwind'
There is an article in the BOL for this subject
"Ivan" <ivan@.microsoft.com> wrote in message
news:ug9q%233L8FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Dear all,
> Is that possible to calculate the database size and free space left by
> t-sql script?
> Ivan
>
|||It had the db size and file size. However, I need the space available in the
file.
The most similar function is sp_space_used.
But I found the space left is not match with the one I saw in Enterprise
Manager.
Do you know how to calculate the space left in enterprise manager?
Ivan
"Uri Dimant" <urid@.iscar.co.il> glsD:e%23JXCgM8FHA.3752@.tk2msftngp13.phx .gbl...
> Ivan
> sp_helpdb 'northwind'
> There is an article in the BOL for this subject
>
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:ug9q%233L8FHA.1028@.TK2MSFTNGP11.phx.gbl...
>
|||Ivan
sp_spaceused in the BOL
"Ivan" <ivan@.microsoft.com> wrote in message
news:%23FcO0QO8FHA.2304@.TK2MSFTNGP10.phx.gbl...
> It had the db size and file size. However, I need the space available in
> the file.
> The most similar function is sp_space_used.
> But I found the space left is not match with the one I saw in Enterprise
> Manager.
> Do you know how to calculate the space left in enterprise manager?
> Ivan
>
> "Uri Dimant" <urid@.iscar.co.il>
> glsD:e%23JXCgM8FHA.3752@.tk2msftngp13.phx .gbl...
>
|||I once wrote a post for that:
http://groups.google.de/group/comp.d...94eacf442935d5
HTH, jens SUessmeyer.
|||Hi Ivan,
I use the below mentioned script for this purpose. This provides me separate
result set for Data Files and Log Files. The only possible issue with this
script is that it is using undocumented SP (DBCC SHOWFILESTATS). The positive
side is that it gives the sizes as shown in EM.
--Script to calculate information about the Data Files
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
DECLARE @.dbname varchar(50)
declare @.string varchar(250)
set @.string = ''
create table #datafilestats
(Fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 dec (8, 2),
UsedExtents1 dec (8, 2),
[Name] varchar(50),
[FileName] sysname )
create table #dbstats
(dbname varchar(50),
FileGroupId tinyint,
FileGroupName varchar(25),
TotalSizeinMB dec (8, 2),
UsedSizeinMB dec (8, 2),
FreeSizeinMB dec (8, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @.dbname
WHILE (@.@.fetch_status = 0)
BEGIN
set @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS'
insert into #datafilestats exec (@.string)
insert into #dbstats (dbname, FileGroupId, TotalSizeinMB, UsedSizeinMB)
select @.dbname, FileGroup1, sum(TotalExtents1)*65536.0/1048576.0,
sum(UsedExtents1)*65536.0/1048576.0
from #datafilestats group by FileGroup1
set @.string = 'use ' + @.dbname + ' update #dbstats set FileGroupName =
sysfilegroups.groupname from #dbstats, sysfilegroups where
#dbstats.FileGroupId = sysfilegroups.groupid and #dbstats.dbname =''' +
@.dbname + ''''
exec (@.string)
update #dbstats set FreeSizeinMB = TotalSizeinMB - UsedSizeinMB where
dbname = @.dbname
truncate table #datafilestats
FETCH NEXT FROM dbnames_cursor INTO @.dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
drop table #datafilestats
select * from #dbstats
drop table #dbstats
--Script to calculate information about the Log Files
set nocount on
create table #LogUsageInfo
( db_name varchar(50),
log_size dec (8, 2),
log_used_percent dec (8, 2),
status dec (7, 1) )
insert #LogUsageInfo exec ('dbcc sqlperf(logspace) with no_infomsgs')
select * from #LogUsageInfo
drop table #LogUsageInfo
"Ivan" wrote:

> Dear all,
> Is that possible to calculate the database size and free space left by t-sql
> script?
> Ivan
>
>
|||I have another new task for finding free space left.
Before, I need get the free space left in SQL 2000 databse.
Now, I need get the free space left in SQL 6.5
Need help~~~~~~~~~
Ivan
"Uri Dimant" <urid@.iscar.co.il> glsD:uu74eVO8FHA.1188@.TK2MSFTNGP12.phx.g bl...
> Ivan
> sp_spaceused in the BOL
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:%23FcO0QO8FHA.2304@.TK2MSFTNGP10.phx.gbl...
>
|||See my post below.
HTH, Jens Suessmeyer.
|||Any SQL 6.5 version?
there's no sysfiles in sql 6.5
Ivan
"Jens" <Jens@.sqlserver2005.de>
??:1132912972.019598.201170@.g47g2000cwa.googl egroups.com...
> See my post below.
> HTH, Jens Suessmeyer.
>

Caclulate database size and free space left

Dear all,
Is that possible to calculate the database size and free space left by t-sql
script?
IvanIvan
sp_helpdb 'northwind'
There is an article in the BOL for this subject
"Ivan" <ivan@.microsoft.com> wrote in message
news:ug9q%233L8FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Dear all,
> Is that possible to calculate the database size and free space left by
> t-sql script?
> Ivan
>|||It had the db size and file size. However, I need the space available in the
file.
The most similar function is sp_space_used.
But I found the space left is not match with the one I saw in Enterprise
Manager.
Do you know how to calculate the space left in enterprise manager?
Ivan
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:e%23JXCgM8FHA.3752@.tk2msftngp13.phx.gbl...
> Ivan
> sp_helpdb 'northwind'
> There is an article in the BOL for this subject
>
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:ug9q%233L8FHA.1028@.TK2MSFTNGP11.phx.gbl...
>> Dear all,
>> Is that possible to calculate the database size and free space left by
>> t-sql script?
>> Ivan
>|||Ivan
sp_spaceused in the BOL
"Ivan" <ivan@.microsoft.com> wrote in message
news:%23FcO0QO8FHA.2304@.TK2MSFTNGP10.phx.gbl...
> It had the db size and file size. However, I need the space available in
> the file.
> The most similar function is sp_space_used.
> But I found the space left is not match with the one I saw in Enterprise
> Manager.
> Do you know how to calculate the space left in enterprise manager?
> Ivan
>
> "Uri Dimant" <urid@.iscar.co.il>
> ¼¶¼g©ó¶l¥ó·s»D:e%23JXCgM8FHA.3752@.tk2msftngp13.phx.gbl...
>> Ivan
>> sp_helpdb 'northwind'
>> There is an article in the BOL for this subject
>>
>>
>> "Ivan" <ivan@.microsoft.com> wrote in message
>> news:ug9q%233L8FHA.1028@.TK2MSFTNGP11.phx.gbl...
>> Dear all,
>> Is that possible to calculate the database size and free space left by
>> t-sql script?
>> Ivan
>>
>|||I once wrote a post for that:
http://groups.google.de/group/comp.databases.ms-sqlserver/browse_frm/thread/7494eacf442935d5
HTH, jens SUessmeyer.|||Hi Ivan,
I use the below mentioned script for this purpose. This provides me separate
result set for Data Files and Log Files. The only possible issue with this
script is that it is using undocumented SP (DBCC SHOWFILESTATS). The positive
side is that it gives the sizes as shown in EM.
--Script to calculate information about the Data Files
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
DECLARE @.dbname varchar(50)
declare @.string varchar(250)
set @.string = ''
create table #datafilestats
( Fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 dec (8, 2),
UsedExtents1 dec (8, 2),
[Name] varchar(50),
[FileName] sysname )
create table #dbstats
( dbname varchar(50),
FileGroupId tinyint,
FileGroupName varchar(25),
TotalSizeinMB dec (8, 2),
UsedSizeinMB dec (8, 2),
FreeSizeinMB dec (8, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @.dbname
WHILE (@.@.fetch_status = 0)
BEGIN
set @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS'
insert into #datafilestats exec (@.string)
insert into #dbstats (dbname, FileGroupId, TotalSizeinMB, UsedSizeinMB)
select @.dbname, FileGroup1, sum(TotalExtents1)*65536.0/1048576.0,
sum(UsedExtents1)*65536.0/1048576.0
from #datafilestats group by FileGroup1
set @.string = 'use ' + @.dbname + ' update #dbstats set FileGroupName =sysfilegroups.groupname from #dbstats, sysfilegroups where
#dbstats.FileGroupId = sysfilegroups.groupid and #dbstats.dbname =''' +
@.dbname + ''''
exec (@.string)
update #dbstats set FreeSizeinMB = TotalSizeinMB - UsedSizeinMB where
dbname = @.dbname
truncate table #datafilestats
FETCH NEXT FROM dbnames_cursor INTO @.dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
drop table #datafilestats
select * from #dbstats
drop table #dbstats
--
--Script to calculate information about the Log Files
set nocount on
create table #LogUsageInfo
( db_name varchar(50),
log_size dec (8, 2),
log_used_percent dec (8, 2),
status dec (7, 1) )
insert #LogUsageInfo exec ('dbcc sqlperf(logspace) with no_infomsgs')
select * from #LogUsageInfo
drop table #LogUsageInfo
---
"Ivan" wrote:
> Dear all,
> Is that possible to calculate the database size and free space left by t-sql
> script?
> Ivan
>
>|||I have another new task for finding free space left.
Before, I need get the free space left in SQL 2000 databse.
Now, I need get the free space left in SQL 6.5
Need help~~~~~~~~~
Ivan
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:uu74eVO8FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Ivan
> sp_spaceused in the BOL
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:%23FcO0QO8FHA.2304@.TK2MSFTNGP10.phx.gbl...
>> It had the db size and file size. However, I need the space available in
>> the file.
>> The most similar function is sp_space_used.
>> But I found the space left is not match with the one I saw in Enterprise
>> Manager.
>> Do you know how to calculate the space left in enterprise manager?
>> Ivan
>>
>> "Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:e%23JXCgM8FHA.3752@.tk2msftngp13.phx.gbl...
>> Ivan
>> sp_helpdb 'northwind'
>> There is an article in the BOL for this subject
>>
>>
>> "Ivan" <ivan@.microsoft.com> wrote in message
>> news:ug9q%233L8FHA.1028@.TK2MSFTNGP11.phx.gbl...
>> Dear all,
>> Is that possible to calculate the database size and free space left by
>> t-sql script?
>> Ivan
>>
>>
>|||See my post below.
HTH, Jens Suessmeyer.|||Any SQL 6.5 version?
there's no sysfiles in sql 6.5
Ivan
"Jens" <Jens@.sqlserver2005.de>
'?:1132912972.019598.201170@.g47g2000cwa.googlegroups.com...
> See my post below.
> HTH, Jens Suessmeyer.
>

Caclulate database size and free space left

Dear all,
Is that possible to calculate the database size and free space left by t-sql
script?
IvanIvan
sp_helpdb 'northwind'
There is an article in the BOL for this subject
"Ivan" <ivan@.microsoft.com> wrote in message
news:ug9q%233L8FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Dear all,
> Is that possible to calculate the database size and free space left by
> t-sql script?
> Ivan
>|||It had the db size and file size. However, I need the space available in the
file.
The most similar function is sp_space_used.
But I found the space left is not match with the one I saw in Enterprise
Manager.
Do you know how to calculate the space left in enterprise manager?
Ivan
"Uri Dimant" <urid@.iscar.co.il> glsD:e%23JXCgM8FHA.3752@.tk2msftngp13.phx.gbl...[vb
col=seagreen]
> Ivan
> sp_helpdb 'northwind'
> There is an article in the BOL for this subject
>
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:ug9q%233L8FHA.1028@.TK2MSFTNGP11.phx.gbl...
>[/vbcol]|||Ivan
sp_spaceused in the BOL
"Ivan" <ivan@.microsoft.com> wrote in message
news:%23FcO0QO8FHA.2304@.TK2MSFTNGP10.phx.gbl...
> It had the db size and file size. However, I need the space available in
> the file.
> The most similar function is sp_space_used.
> But I found the space left is not match with the one I saw in Enterprise
> Manager.
> Do you know how to calculate the space left in enterprise manager?
> Ivan
>
> "Uri Dimant" <urid@.iscar.co.il>
> glsD:e%23JXCgM8FHA.3752@.tk2msftngp13.phx.gbl...
>|||I once wrote a post for that:
http://groups.google.de/group/comp... />
cf442935d5
HTH, jens SUessmeyer.|||Hi Ivan,
I use the below mentioned script for this purpose. This provides me separate
result set for Data Files and Log Files. The only possible issue with this
script is that it is using undocumented SP (DBCC SHOWFILESTATS). The positiv
e
side is that it gives the sizes as shown in EM.
--Script to calculate information about the Data Files
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
DECLARE @.dbname varchar(50)
declare @.string varchar(250)
set @.string = ''
create table #datafilestats
( Fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 dec (8, 2),
UsedExtents1 dec (8, 2),
[Name] varchar(50),
[FileName] sysname )
create table #dbstats
( dbname varchar(50),
FileGroupId tinyint,
FileGroupName varchar(25),
TotalSizeinMB dec (8, 2),
UsedSizeinMB dec (8, 2),
FreeSizeinMB dec (8, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @.dbname
WHILE (@.@.fetch_status = 0)
BEGIN
set @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS'
insert into #datafilestats exec (@.string)
insert into #dbstats (dbname, FileGroupId, TotalSizeinMB, UsedSizeinMB)
select @.dbname, FileGroup1, sum(TotalExtents1)*65536.0/1048576.0,
sum(UsedExtents1)*65536.0/1048576.0
from #datafilestats group by FileGroup1
set @.string = 'use ' + @.dbname + ' update #dbstats set FileGroupName =
sysfilegroups.groupname from #dbstats, sysfilegroups where
#dbstats.FileGroupId = sysfilegroups.groupid and #dbstats.dbname =''' +
@.dbname + ''''
exec (@.string)
update #dbstats set FreeSizeinMB = TotalSizeinMB - UsedSizeinMB where
dbname = @.dbname
truncate table #datafilestats
FETCH NEXT FROM dbnames_cursor INTO @.dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
drop table #datafilestats
select * from #dbstats
drop table #dbstats
--
--Script to calculate information about the Log Files
set nocount on
create table #LogUsageInfo
( db_name varchar(50),
log_size dec (8, 2),
log_used_percent dec (8, 2),
status dec (7, 1) )
insert #LogUsageInfo exec ('dbcc sqlperf(logspace) with no_infomsgs')
select * from #LogUsageInfo
drop table #LogUsageInfo
---
"Ivan" wrote:

> Dear all,
> Is that possible to calculate the database size and free space left by t-s
ql
> script?
> Ivan
>
>|||I have another new task for finding free space left.
Before, I need get the free space left in SQL 2000 databse.
Now, I need get the free space left in SQL 6.5
Need help~~~~~~~~~
Ivan
"Uri Dimant" <urid@.iscar.co.il> glsD:uu74eVO8FHA.1188@.TK2MSFTNGP12.phx.gbl...[vbco
l=seagreen]
> Ivan
> sp_spaceused in the BOL
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:%23FcO0QO8FHA.2304@.TK2MSFTNGP10.phx.gbl...
>[/vbcol]|||See my post below.
HTH, Jens Suessmeyer.|||Any SQL 6.5 version?
there's no sysfiles in sql 6.5
Ivan
"Jens" <Jens@.sqlserver2005.de>
'?:1132912972.019598.201170@.g47g2000cwa.googlegroups.com...
> See my post below.
> HTH, Jens Suessmeyer.
>

Caching doesn't seem to be working

I have a small Cube (about 100MB size) with about 10 dimensions.

I have created YTD calculated members (few of them) like this:

SUM
( PeriodsToDate (
[end_date].[Financial].[FYear],
TAIL (EXISTING [end_date].[Financial].[FYYYYMM].Members, 1).item(0)
),
[Measures].[LOS]
)

Now if I do a simple MDX like this:

SELECT

NON EMPTY ([end_date].[FYYYYMM Attribute].[200708]

) ON COLUMNS ,

NON EMPTY ([specialty].[SubSpecialtyDesc Attribute].allmembers,

[purchase_unit].[PUC].allmembers)

ON ROWS

FROM [cube_contract_reporting]

WHERE (

[Measures].[ActualRawVolumeYTD],

[purchase_unit].[WIES_PU].[WIES PU].&,

[end_date].[Financial].[FYear].&[2007],

[purchaser].[PHFundedFlag Attribute].&

)

It returns the first run result in 8 minutes. Which is long, however second run takes 50 seconds, which makes Okay.

if I run something like this:

SELECT

NON EMPTY ([end_date].[FYYYYMM Attribute].allmembers

) ON COLUMNS ,

NON EMPTY ([specialty].[SubSpecialtyDesc Attribute].allmembers,

[purchase_unit].[PUC].allmembers)

ON ROWS

FROM [cube_contract_reporting]

WHERE (

[Measures].[ActualRawVolumeYTD],

[purchase_unit].[WIES_PU].[WIES PU].&,

[end_date].[Financial].[FYear].&[2007],

[purchaser].[PHFundedFlag Attribute].&

)

it will take few hours for the first run and about an hour for the second run...

is this a caching problem? or am I doing something wrong?

I have created so many aggregations, and tried every trick I can think of or found online.... nothing changed the performance!

Actually the caching does appear to be working as the second runs are all noticably faster than the initial run. It appears to be the calculation that is slowing things down. There are some things that can be done to tune YTD style calculations, but they ususally rely on having a natural date hierarchy. We need to know a few things about your cube to see how best to tune it.

Is [LOS] a base measure or a calculation?

How fast do the queries run if you use a base measure instead of the YTD calculation?

Does the [end_date].[FYYYYMM Attribute] attribute have a relationship to the [end_date].[FYear] attribute?

Do the [puchase_unit].[WIES_PU] and [purchase_unit].[PUC] attributes have a relationship defined between them?

|||

Darren Gosbell wrote:

Actually the caching does appear to be working as the second runs are all noticably faster than the initial run. It appears to be the calculation that is slowing things down. There are some things that can be done to tune YTD style calculations, but they ususally rely on having a natural date hierarchy. We need to know a few things about your cube to see how best to tune it.

Is [LOS] a base measure or a calculation?

How fast do the queries run if you use a base measure instead of the YTD calculation?

Does the [end_date].[FYYYYMM Attribute] attribute have a relationship to the [end_date].[FYear] attribute?

Do the [puchase_unit].[WIES_PU] and [purchase_unit].[PUC] attributes have a relationship defined between them?

I haven't received the email notification so I didn't know of you reply, sorry for being late.

[LOS] is a base measure, however LOSYTD is calculated memeber (among about 20 YTD fields)

How fast on base measures : Very Fast, 6 seconds for the very same query with LOS instead of LOSYTD

There is a Financial Hierarchy that holds the fiscal year dates FYear-> FQTR->FYYYYMM->Date

No relationship exists between Weis_PU and PUC.

Also one "interesting" note. I noticed that if I have only ONE YTD calculation in the cube then the first run takes long (say 50 min.) and the second run takes about 50 seconds! (which is long but bearable).

Thank you for helping me out.

|||

Yes, there seems to have been an issue with the notifications. I got none for a number of days and then over 20 of them came through today.

The number of YTD calculations in the cube should not really make a difference, I can't think why this should impact on performance as calculations are only executed as they are referenced. This is a bit of a concern, but I can't think what could be causing this behaviour.

Seeing that you do have relationships (and presumably a user hierarchy) defined on the Financial date, you might be able to optimize it using the following technique http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!111.entry. This basically "walks" up the date hierarchy, so that if you are on month 11, rather than adding up 11 months, it adds up 3 quarters and 2 months, which can significantly speed up your calculation. (although SP2 might do this without a code change)

Are you on SP2? There were some performance enhancements particularly aimed at running sum and YTD style calculations that my help significantly (see: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/17/rsum_performance.aspx)

If "feels" as if there are not appropriate aggregations at the month level and that the code is being forced to go down to the leaf level. The SP2 fix mentioned above may help here as may running the usage based optimisation wizard.

Also, just looking back at your calculation. Do you really need the EXISTING operator in there? Neither of your sample queries appeared to require it and it would probably run much faster with a reference to currentmember.

Code Snippet

SUM
( PeriodsToDate (
[end_date].[Financial].[FYear],
[end_date].[Financial].[FYYYYMM].CurrentMember
),
[Measures].[LOS]
)

This of course gives you a relative YTD, but if you want to replicate the fixed year to date that I think you probably get with the the Existing function then you could do something like the following. I don't think this would handle the ALL member properly, but you could fix that with an appropriate SCOPE statement to handle that case.

Code Snippet

SUM
( PeriodsToDate (
[end_date].[Financial].[FYear],
Tail([end_date].[Financial].[FYYYYMM].CurrentMember.Siblings,1).item(0)
),
[Measures].[LOS]
)

|||

Hi,

Thank you for your help. I will certainly push for SP2 as it seems to be a good solution.

The reason behind using "existing" is to allow the user to multi-select. In a common scenario, a business user would say what was the YTD for a particular month in a particular year, and then try another month and another...etc...

The first code you used would fail on multisets because it can't determine the current member, and I suppose the second will too, but I'm not sure.

I don't know what is happening BUT I did the following:

1. Removed a dimension with a huge set of records that we used for testing only (that is not used in the MDX AT ALL)

2. Partitioned the data based on financial year.

The same MDX (no change) is doing the first run in 30 min. and the second in 1 min.

I will see if SP2 further brings this down and let you know. Thanks for your help!

|||

No, neither of the calcs I suggested in the previous post will work in a multi select scenario. They will work with multiple members on the axis, but not if you have multiple months in a set in the where clause. It was just that your sample query showed selection by year, so I though maybe the multi-select capability might not be strictly required.

Adding partitioning makes some sense as your queries were only hitting a single year, so this would cause the storage engine to have to scan less data. Even though SSAS does not require you to set the data slice for a partition, there has been some evidence recently that doing so can improve performance so you might want to make sure you are doing this.

Removing a large dimension that was not part of the query would also reduce the overall size of the cube, but may also point to the fact that the query might not be finding appropriate aggregations. The updated samples with SP2 includes an aggregation designer and while you would want to be really careful about manually designing your own aggregations, this tool does a good job of presenting the existing aggregations in a GUI which helps you easily see which attributes are included in the designed aggregations.

One final suggestion that I just thought of was that sometimes breaking your calculation into smaller pieces can help as the individual bits can be cached separately by the formula engine. So possibly breaking out the finding of the last month member from the actual periods to date sum would speed things up too. It's hard to know for sure if this will have much of an impact or not, but it should be fairly easy for you to test

Code Snippet

CREATE MEMBER CURRENTCUBE.[end_date].[Financial].[CurrentEndMonth]

AS TAIL (EXISTING [end_date].[Financial].[FYYYYMM].Members, 1).item(0)

SUM
( PeriodsToDate (

[end_date].[Financial].[FYear],
[end_date].[Financial].[CurrentEndMonth]

),
[Measures].[LOS]
)

Caching doesn't seem to be working

I have a small Cube (about 100MB size) with about 10 dimensions.

I have created YTD calculated members (few of them) like this:

SUM
( PeriodsToDate (
[end_date].[Financial].[FYear],
TAIL (EXISTING [end_date].[Financial].[FYYYYMM].Members, 1).item(0)
),
[Measures].[LOS]
)

Now if I do a simple MDX like this:

SELECT

NON EMPTY ([end_date].[FYYYYMM Attribute].[200708]

) ON COLUMNS ,

NON EMPTY ([specialty].[SubSpecialtyDesc Attribute].allmembers,

[purchase_unit].[PUC].allmembers)

ON ROWS

FROM [cube_contract_reporting]

WHERE (

[Measures].[ActualRawVolumeYTD],

[purchase_unit].[WIES_PU].[WIES PU].&,

[end_date].[Financial].[FYear].&[2007],

[purchaser].[PHFundedFlag Attribute].&

)

It returns the first run result in 8 minutes. Which is long, however second run takes 50 seconds, which makes Okay.

if I run something like this:

SELECT

NON EMPTY ([end_date].[FYYYYMM Attribute].allmembers

) ON COLUMNS ,

NON EMPTY ([specialty].[SubSpecialtyDesc Attribute].allmembers,

[purchase_unit].[PUC].allmembers)

ON ROWS

FROM [cube_contract_reporting]

WHERE (

[Measures].[ActualRawVolumeYTD],

[purchase_unit].[WIES_PU].[WIES PU].&,

[end_date].[Financial].[FYear].&[2007],

[purchaser].[PHFundedFlag Attribute].&

)

it will take few hours for the first run and about an hour for the second run...

is this a caching problem? or am I doing something wrong?

I have created so many aggregations, and tried every trick I can think of or found online.... nothing changed the performance!

Actually the caching does appear to be working as the second runs are all noticably faster than the initial run. It appears to be the calculation that is slowing things down. There are some things that can be done to tune YTD style calculations, but they ususally rely on having a natural date hierarchy. We need to know a few things about your cube to see how best to tune it.

Is [LOS] a base measure or a calculation?

How fast do the queries run if you use a base measure instead of the YTD calculation?

Does the [end_date].[FYYYYMM Attribute] attribute have a relationship to the [end_date].[FYear] attribute?

Do the [puchase_unit].[WIES_PU] and [purchase_unit].[PUC] attributes have a relationship defined between them?

|||

Darren Gosbell wrote:

Actually the caching does appear to be working as the second runs are all noticably faster than the initial run. It appears to be the calculation that is slowing things down. There are some things that can be done to tune YTD style calculations, but they ususally rely on having a natural date hierarchy. We need to know a few things about your cube to see how best to tune it.

Is [LOS] a base measure or a calculation?

How fast do the queries run if you use a base measure instead of the YTD calculation?

Does the [end_date].[FYYYYMM Attribute] attribute have a relationship to the [end_date].[FYear] attribute?

Do the [puchase_unit].[WIES_PU] and [purchase_unit].[PUC] attributes have a relationship defined between them?

I haven't received the email notification so I didn't know of you reply, sorry for being late.

[LOS] is a base measure, however LOSYTD is calculated memeber (among about 20 YTD fields)

How fast on base measures : Very Fast, 6 seconds for the very same query with LOS instead of LOSYTD

There is a Financial Hierarchy that holds the fiscal year dates FYear-> FQTR->FYYYYMM->Date

No relationship exists between Weis_PU and PUC.

Also one "interesting" note. I noticed that if I have only ONE YTD calculation in the cube then the first run takes long (say 50 min.) and the second run takes about 50 seconds! (which is long but bearable).

Thank you for helping me out.

|||

Yes, there seems to have been an issue with the notifications. I got none for a number of days and then over 20 of them came through today.

The number of YTD calculations in the cube should not really make a difference, I can't think why this should impact on performance as calculations are only executed as they are referenced. This is a bit of a concern, but I can't think what could be causing this behaviour.

Seeing that you do have relationships (and presumably a user hierarchy) defined on the Financial date, you might be able to optimize it using the following technique http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!111.entry. This basically "walks" up the date hierarchy, so that if you are on month 11, rather than adding up 11 months, it adds up 3 quarters and 2 months, which can significantly speed up your calculation. (although SP2 might do this without a code change)

Are you on SP2? There were some performance enhancements particularly aimed at running sum and YTD style calculations that my help significantly (see: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/17/rsum_performance.aspx)

If "feels" as if there are not appropriate aggregations at the month level and that the code is being forced to go down to the leaf level. The SP2 fix mentioned above may help here as may running the usage based optimisation wizard.

Also, just looking back at your calculation. Do you really need the EXISTING operator in there? Neither of your sample queries appeared to require it and it would probably run much faster with a reference to currentmember.

Code Snippet

SUM
( PeriodsToDate (
[end_date].[Financial].[FYear],
[end_date].[Financial].[FYYYYMM].CurrentMember
),
[Measures].[LOS]
)

This of course gives you a relative YTD, but if you want to replicate the fixed year to date that I think you probably get with the the Existing function then you could do something like the following. I don't think this would handle the ALL member properly, but you could fix that with an appropriate SCOPE statement to handle that case.

Code Snippet

SUM
( PeriodsToDate (
[end_date].[Financial].[FYear],
Tail([end_date].[Financial].[FYYYYMM].CurrentMember.Siblings,1).item(0)
),
[Measures].[LOS]
)

|||

Hi,

Thank you for your help. I will certainly push for SP2 as it seems to be a good solution.

The reason behind using "existing" is to allow the user to multi-select. In a common scenario, a business user would say what was the YTD for a particular month in a particular year, and then try another month and another...etc...

The first code you used would fail on multisets because it can't determine the current member, and I suppose the second will too, but I'm not sure.

I don't know what is happening BUT I did the following:

1. Removed a dimension with a huge set of records that we used for testing only (that is not used in the MDX AT ALL)

2. Partitioned the data based on financial year.

The same MDX (no change) is doing the first run in 30 min. and the second in 1 min.

I will see if SP2 further brings this down and let you know. Thanks for your help!

|||

No, neither of the calcs I suggested in the previous post will work in a multi select scenario. They will work with multiple members on the axis, but not if you have multiple months in a set in the where clause. It was just that your sample query showed selection by year, so I though maybe the multi-select capability might not be strictly required.

Adding partitioning makes some sense as your queries were only hitting a single year, so this would cause the storage engine to have to scan less data. Even though SSAS does not require you to set the data slice for a partition, there has been some evidence recently that doing so can improve performance so you might want to make sure you are doing this.

Removing a large dimension that was not part of the query would also reduce the overall size of the cube, but may also point to the fact that the query might not be finding appropriate aggregations. The updated samples with SP2 includes an aggregation designer and while you would want to be really careful about manually designing your own aggregations, this tool does a good job of presenting the existing aggregations in a GUI which helps you easily see which attributes are included in the designed aggregations.

One final suggestion that I just thought of was that sometimes breaking your calculation into smaller pieces can help as the individual bits can be cached separately by the formula engine. So possibly breaking out the finding of the last month member from the actual periods to date sum would speed things up too. It's hard to know for sure if this will have much of an impact or not, but it should be fairly easy for you to test

Code Snippet

CREATE MEMBER CURRENTCUBE.[end_date].[Financial].[CurrentEndMonth]

AS TAIL (EXISTING [end_date].[Financial].[FYYYYMM].Members, 1).item(0)

SUM
( PeriodsToDate (

[end_date].[Financial].[FYear],
[end_date].[Financial].[CurrentEndMonth]

),
[Measures].[LOS]
)

Cache size lookup transformation

Hi,

I have to perform a lookup in a table based on a query like:

"... where ? = [RefTable].fieldID and ? between [RefTable].AnotherFieldValue and [RefTable].AThirdFieldValue"

So, SSIS has put the CacheType to none. As I really need to speed up the job I want to set the CacheType to partial (full isn't an option due to the custom query I use here).

But here it comes: when using partial CacheType, one has to set the cache size manually - and I really don't know what value I should assign to it - is there a guideline on this topic?

I work on a Win2003 server platform with sql server 2005 - 2 processors - 2Gb Ram - enough disc space

Thanks in advance,
Tom

Tom De Cort wrote:

Hi,

I have to perform a lookup in a table based on a query like:

"... where ? = [RefTable].fieldID and ? between [RefTable].AnotherFieldValue and [RefTable].AThirdFieldValue"

So, SSIS has put the CacheType to none. As I really need to speed up the job I want to set the CacheType to partial (full isn't an option due to the custom query I use here).

But here it comes: when using partial CacheType, one has to set the cache size manually - and I really don't know what value I should assign to it - is there a guideline on this topic?

I work on a Win2003 server platform with sql server 2005 - 2 processors - 2Gb Ram - enough disc space

Thanks in advance,
Tom

Tom,

The only person that can answer this question for sure is yourself. Test and measure, test and measure, test and measure...

Differrent scenarios call for different settings so I doubt there are guidelines anywhere.

-Jamie

|||

ok, I will

Thanks Jamie

cache in sql server 2000

There is a command or option that let me change or manipulate
the size of procedure and data cache in order to obtain better
performance ratios in sql server 2000?
Thanks
No there is not one for the procedure cache. You can set the MAX size the
memory pool uses with MAX Server Memory but you can't actually control the
individual sizes. Your best bet is to make sure you optimize the calls so
that the plans are reused and the proc cache will stay small and manageable.
Andrew J. Kelly SQL MVP
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:7944D92A-F413-49ED-8DED-6F7B50968889@.microsoft.com...
> There is a command or option that let me change or manipulate
> the size of procedure and data cache in order to obtain better
> performance ratios in sql server 2000?
> --
> Thanks
|||Just to add to Andrew's reply, please write to sqlwish@.microsoft.com and let
them know if this is something you'd like to see... It would be nice to put
some pressure on the SQL Server team to get this feature in.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:7944D92A-F413-49ED-8DED-6F7B50968889@.microsoft.com...
> There is a command or option that let me change or manipulate
> the size of procedure and data cache in order to obtain better
> performance ratios in sql server 2000?
> --
> Thanks
|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ui4$dIv5FHA.2560@.TK2MSFTNGP12.phx.gbl...
> Just to add to Andrew's reply, please write to sqlwish@.microsoft.com and
> let them know if this is something you'd like to see... It would be nice
> to put some pressure on the SQL Server team to get this feature in.
>
I think it's a philosophy thing. It's best to have that managed
automatically. You have one knob to turn (total server memory). Other than
that tune your application, not the database server.
David
|||If you want that "feature," go back to version 6.5 or earlier. Better yet,
convert to Oracle, then you can spend all day twiddling knobs, EVERY DAY.
That will be the only work you will ever get to do.
As for me, I would rather spend more of my time helping the developers build
better designs, access methods, and scaling our architecture.
Sincerely,
Anthony Thomas

"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:u11NMOv5FHA.1420@.TK2MSFTNGP09.phx.gbl...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ui4$dIv5FHA.2560@.TK2MSFTNGP12.phx.gbl...
> I think it's a philosophy thing. It's best to have that managed
> automatically. You have one knob to turn (total server memory). Other
than
> that tune your application, not the database server.
> David
>
|||If you work on an enterprise-level application you'll quickly find that
certain "automatic" management features just don't do a good enough job.
They're great for small to medium applications, but it's nice to tweak
things for larger setups.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uNrSwBw5FHA.444@.TK2MSFTNGP11.phx.gbl...
> If you want that "feature," go back to version 6.5 or earlier. Better
> yet,
> convert to Oracle, then you can spend all day twiddling knobs, EVERY DAY.
> That will be the only work you will ever get to do.
> As for me, I would rather spend more of my time helping the developers
> build
> better designs, access methods, and scaling our architecture.
>
|||Yea, well, I currently work on more than 200 of those applications across
more than 50 SQL Server installations, with 6 or more of those on
medium-scaled clustered configurations.
So, I would push SQLWISH to keep driving at making those autonomic features
more reliable so there would be less need for the missing knobs.
We run Oracle in this shop too, and that is all I see those poor guys do all
day long.
Sincerely,
Anthony Thomas

"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ueZqAxw5FHA.2716@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> If you work on an enterprise-level application you'll quickly find that
> certain "automatic" management features just don't do a good enough job.
> They're great for small to medium applications, but it's nice to tweak
> things for larger setups.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:uNrSwBw5FHA.444@.TK2MSFTNGP11.phx.gbl...
DAY.
>
|||Let's not confuse "automation" with "defaults." I'll agree with Adam in
that as you get into larger and more critical systems, the system
configuration and database object defaults become less and less useful;
however, the concept that the system parameters are dynamically set and
"automatically" managed remains valid. I would go as far as to say that the
dynamic configuration management becomes even more critical with larger
scale systems.
The whole point of computing platforms and solutions is the drive to
systematically apply logical algorithms to manual processes in an automation
mechanism. The DBMS is no less critical to this function: manual, labor
intensive processes are automated freeing up time to redirect human
resources to higher level, abstracted tasks and functions such as design and
architecture.
Sincerely,
Anthony Thomas

"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8uq99w5FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Yea, well, I currently work on more than 200 of those applications across
> more than 50 SQL Server installations, with 6 or more of those on
> medium-scaled clustered configurations.
> So, I would push SQLWISH to keep driving at making those autonomic
features
> more reliable so there would be less need for the missing knobs.
> We run Oracle in this shop too, and that is all I see those poor guys do
all
> day long.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ueZqAxw5FHA.2716@.TK2MSFTNGP11.phx.gbl...
> DAY.
>

cache in sql server 2000

There is a command or option that let me change or manipulate
the size of procedure and data cache in order to obtain better
performance ratios in sql server 2000?
--
ThanksNo there is not one for the procedure cache. You can set the MAX size the
memory pool uses with MAX Server Memory but you can't actually control the
individual sizes. Your best bet is to make sure you optimize the calls so
that the plans are reused and the proc cache will stay small and manageable.
Andrew J. Kelly SQL MVP
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:7944D92A-F413-49ED-8DED-6F7B50968889@.microsoft.com...
> There is a command or option that let me change or manipulate
> the size of procedure and data cache in order to obtain better
> performance ratios in sql server 2000?
> --
> Thanks|||Just to add to Andrew's reply, please write to sqlwish@.microsoft.com and let
them know if this is something you'd like to see... It would be nice to put
some pressure on the SQL Server team to get this feature in.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:7944D92A-F413-49ED-8DED-6F7B50968889@.microsoft.com...
> There is a command or option that let me change or manipulate
> the size of procedure and data cache in order to obtain better
> performance ratios in sql server 2000?
> --
> Thanks|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ui4$dIv5FHA.2560@.TK2MSFTNGP12.phx.gbl...
> Just to add to Andrew's reply, please write to sqlwish@.microsoft.com and
> let them know if this is something you'd like to see... It would be nice
> to put some pressure on the SQL Server team to get this feature in.
>
I think it's a philosophy thing. It's best to have that managed
automatically. You have one knob to turn (total server memory). Other than
that tune your application, not the database server.
David|||If you want that "feature," go back to version 6.5 or earlier. Better yet,
convert to Oracle, then you can spend all day twiddling knobs, EVERY DAY.
That will be the only work you will ever get to do.
As for me, I would rather spend more of my time helping the developers build
better designs, access methods, and scaling our architecture.
Sincerely,
Anthony Thomas
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:u11NMOv5FHA.1420@.TK2MSFTNGP09.phx.gbl...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ui4$dIv5FHA.2560@.TK2MSFTNGP12.phx.gbl...
> I think it's a philosophy thing. It's best to have that managed
> automatically. You have one knob to turn (total server memory). Other
than
> that tune your application, not the database server.
> David
>|||If you work on an enterprise-level application you'll quickly find that
certain "automatic" management features just don't do a good enough job.
They're great for small to medium applications, but it's nice to tweak
things for larger setups.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uNrSwBw5FHA.444@.TK2MSFTNGP11.phx.gbl...
> If you want that "feature," go back to version 6.5 or earlier. Better
> yet,
> convert to Oracle, then you can spend all day twiddling knobs, EVERY DAY.
> That will be the only work you will ever get to do.
> As for me, I would rather spend more of my time helping the developers
> build
> better designs, access methods, and scaling our architecture.
>|||Yea, well, I currently work on more than 200 of those applications across
more than 50 SQL Server installations, with 6 or more of those on
medium-scaled clustered configurations.
So, I would push SQLWISH to keep driving at making those autonomic features
more reliable so there would be less need for the missing knobs.
We run Oracle in this shop too, and that is all I see those poor guys do all
day long.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ueZqAxw5FHA.2716@.TK2MSFTNGP11.phx.gbl...
> If you work on an enterprise-level application you'll quickly find that
> certain "automatic" management features just don't do a good enough job.
> They're great for small to medium applications, but it's nice to tweak
> things for larger setups.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:uNrSwBw5FHA.444@.TK2MSFTNGP11.phx.gbl...
DAY.[vbcol=seagreen]
>|||Let's not confuse "automation" with "defaults." I'll agree with Adam in
that as you get into larger and more critical systems, the system
configuration and database object defaults become less and less useful;
however, the concept that the system parameters are dynamically set and
"automatically" managed remains valid. I would go as far as to say that the
dynamic configuration management becomes even more critical with larger
scale systems.
The whole point of computing platforms and solutions is the drive to
systematically apply logical algorithms to manual processes in an automation
mechanism. The DBMS is no less critical to this function: manual, labor
intensive processes are automated freeing up time to redirect human
resources to higher level, abstracted tasks and functions such as design and
architecture.
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8uq99w5FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Yea, well, I currently work on more than 200 of those applications across
> more than 50 SQL Server installations, with 6 or more of those on
> medium-scaled clustered configurations.
> So, I would push SQLWISH to keep driving at making those autonomic
features
> more reliable so there would be less need for the missing knobs.
> We run Oracle in this shop too, and that is all I see those poor guys do
all
> day long.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ueZqAxw5FHA.2716@.TK2MSFTNGP11.phx.gbl...
> DAY.
>

cache in sql server 2000

There is a command or option that let me change or manipulate
the size of procedure and data cache in order to obtain better
performance ratios in sql server 2000?
--
ThanksNo there is not one for the procedure cache. You can set the MAX size the
memory pool uses with MAX Server Memory but you can't actually control the
individual sizes. Your best bet is to make sure you optimize the calls so
that the plans are reused and the proc cache will stay small and manageable.
--
Andrew J. Kelly SQL MVP
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:7944D92A-F413-49ED-8DED-6F7B50968889@.microsoft.com...
> There is a command or option that let me change or manipulate
> the size of procedure and data cache in order to obtain better
> performance ratios in sql server 2000?
> --
> Thanks|||Just to add to Andrew's reply, please write to sqlwish@.microsoft.com and let
them know if this is something you'd like to see... It would be nice to put
some pressure on the SQL Server team to get this feature in.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:7944D92A-F413-49ED-8DED-6F7B50968889@.microsoft.com...
> There is a command or option that let me change or manipulate
> the size of procedure and data cache in order to obtain better
> performance ratios in sql server 2000?
> --
> Thanks|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ui4$dIv5FHA.2560@.TK2MSFTNGP12.phx.gbl...
> Just to add to Andrew's reply, please write to sqlwish@.microsoft.com and
> let them know if this is something you'd like to see... It would be nice
> to put some pressure on the SQL Server team to get this feature in.
>
I think it's a philosophy thing. It's best to have that managed
automatically. You have one knob to turn (total server memory). Other than
that tune your application, not the database server.
David|||If you want that "feature," go back to version 6.5 or earlier. Better yet,
convert to Oracle, then you can spend all day twiddling knobs, EVERY DAY.
That will be the only work you will ever get to do.
As for me, I would rather spend more of my time helping the developers build
better designs, access methods, and scaling our architecture.
Sincerely,
Anthony Thomas
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:u11NMOv5FHA.1420@.TK2MSFTNGP09.phx.gbl...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ui4$dIv5FHA.2560@.TK2MSFTNGP12.phx.gbl...
> > Just to add to Andrew's reply, please write to sqlwish@.microsoft.com and
> > let them know if this is something you'd like to see... It would be nice
> > to put some pressure on the SQL Server team to get this feature in.
> >
> I think it's a philosophy thing. It's best to have that managed
> automatically. You have one knob to turn (total server memory). Other
than
> that tune your application, not the database server.
> David
>|||If you work on an enterprise-level application you'll quickly find that
certain "automatic" management features just don't do a good enough job.
They're great for small to medium applications, but it's nice to tweak
things for larger setups.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uNrSwBw5FHA.444@.TK2MSFTNGP11.phx.gbl...
> If you want that "feature," go back to version 6.5 or earlier. Better
> yet,
> convert to Oracle, then you can spend all day twiddling knobs, EVERY DAY.
> That will be the only work you will ever get to do.
> As for me, I would rather spend more of my time helping the developers
> build
> better designs, access methods, and scaling our architecture.
>|||Yea, well, I currently work on more than 200 of those applications across
more than 50 SQL Server installations, with 6 or more of those on
medium-scaled clustered configurations.
So, I would push SQLWISH to keep driving at making those autonomic features
more reliable so there would be less need for the missing knobs.
We run Oracle in this shop too, and that is all I see those poor guys do all
day long.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ueZqAxw5FHA.2716@.TK2MSFTNGP11.phx.gbl...
> If you work on an enterprise-level application you'll quickly find that
> certain "automatic" management features just don't do a good enough job.
> They're great for small to medium applications, but it's nice to tweak
> things for larger setups.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:uNrSwBw5FHA.444@.TK2MSFTNGP11.phx.gbl...
> > If you want that "feature," go back to version 6.5 or earlier. Better
> > yet,
> > convert to Oracle, then you can spend all day twiddling knobs, EVERY
DAY.
> > That will be the only work you will ever get to do.
> >
> > As for me, I would rather spend more of my time helping the developers
> > build
> > better designs, access methods, and scaling our architecture.
> >
>|||Let's not confuse "automation" with "defaults." I'll agree with Adam in
that as you get into larger and more critical systems, the system
configuration and database object defaults become less and less useful;
however, the concept that the system parameters are dynamically set and
"automatically" managed remains valid. I would go as far as to say that the
dynamic configuration management becomes even more critical with larger
scale systems.
The whole point of computing platforms and solutions is the drive to
systematically apply logical algorithms to manual processes in an automation
mechanism. The DBMS is no less critical to this function: manual, labor
intensive processes are automated freeing up time to redirect human
resources to higher level, abstracted tasks and functions such as design and
architecture.
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8uq99w5FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Yea, well, I currently work on more than 200 of those applications across
> more than 50 SQL Server installations, with 6 or more of those on
> medium-scaled clustered configurations.
> So, I would push SQLWISH to keep driving at making those autonomic
features
> more reliable so there would be less need for the missing knobs.
> We run Oracle in this shop too, and that is all I see those poor guys do
all
> day long.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ueZqAxw5FHA.2716@.TK2MSFTNGP11.phx.gbl...
> > If you work on an enterprise-level application you'll quickly find that
> > certain "automatic" management features just don't do a good enough job.
> > They're great for small to medium applications, but it's nice to tweak
> > things for larger setups.
> >
> >
> > --
> > Adam Machanic
> > Pro SQL Server 2005, available now
> > http://www.apress.com/book/bookDisplay.html?bID=457
> > --
> >
> >
> > "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> > news:uNrSwBw5FHA.444@.TK2MSFTNGP11.phx.gbl...
> > > If you want that "feature," go back to version 6.5 or earlier. Better
> > > yet,
> > > convert to Oracle, then you can spend all day twiddling knobs, EVERY
> DAY.
> > > That will be the only work you will ever get to do.
> > >
> > > As for me, I would rather spend more of my time helping the developers
> > > build
> > > better designs, access methods, and scaling our architecture.
> > >
> >
> >
>

c# Stored Procedure / OpenXML Failure - Can Anyone Help..??

Hi,

I have some c# code which calls a SP which is erroring Basically I pass in a XML string which can be upto 5 MB is size (not sure about overflow issues here), which then calls a SP which inserts the data into a SQL table.

The c# code is as follows:

---C#-------

SqlConnection conn =new SqlConnection(DBConn);

using(StreamReader sr =new StreamReader(xmlLocationString))

{

try

{

string @.xmlInput = sr.ReadToEnd();

SqlCommand cmd =new SqlCommand();

cmd.Connection=conn;

cmd.CommandText = "[AddArgentinaTrades]";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@.xmlInput", SqlDbType.Text,5120000));

cmd.Parameters["@.xmlInput"].Direction = ParameterDirection.Output;

conn.Open();

cmd.ExecuteNonQuery();

}

catch(SqlException SqlExp)

{

Console.WriteLine(SqlExp.Message);

}

finally

{

conn.Close();

sr.Close();

}

}

------Stored Proc--------

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'AddArgentinaTrades' AND Type ='P')
DROP PROCEDURE AddArgentinaTrades
GO

CREATE PROCEDURE AddArgentinaTrades
@.xmlInput as text
AS

Declare @.idoc int

EXEC master.dbo.sp_xml_preparedocument @.idoc OUTPUT, @.xmlInput

INSERT INTO MarketRiskdev.dbo.Import_Argentina
SELECT un_cid, tnum, snum, cid, entityid, ctype, why, comp, oc, bs, ae, cp, trd_date, set_date, mat_date, val_date,
trader, famt, price, coupon, next_coupon, last_coupon, cpnfreq, cpnrate, cpntype, daycounttype, exch_notion,
contract_spot, base_cur, year_basis, buy_currency, buy_amount, sell_currency, sell_amount, [timestamp] FROM OPENXML(@.idoc, 'ArgentinaInputFile/Data',2)
WITH (un_cid varchar(50), tnum nvarchar(50), snum nvarchar(50), cid varchar(50), entityid varchar(50), ctype varchar(50),
why varchar(50),
comp varchar(50),
oc varchar(50),
bs varchar(50),
ae varchar(50),
cp varchar(50),
trd_date datetime,
set_date datetime,
mat_date datetime,
val_date datetime,
trader varchar(50),
famt float(8),
price float(8),
coupon float(8),
next_coupon datetime,
last_coupon datetime,
cpnfreq int,
cpnrate float,
cpntype int,
daycounttype smallint,
exch_notion smallint,
contract_spot float(8),
base_cur varchar(50),
year_basis int,
buy_currency varchar(50),
buy_currency varchar(50),
buy_amount float(8),
sell_currency varchar(50),
sell_amount float(8),
[timestamp] varchar(50))

EXEC master.dbo.sp_xml_removedocument @.idoc

GO

Error Msg:

A severe error occurred on the current command. The results, if any, should be
discarded.

Can anyone help here as I have no idea. I have tried reducing the size of XML to 5KB and still get the same error??

I don't think it is the size of your xml file, try the link below to modify your SQL statement. Hope this helps.

http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx

bytes used per datatype

Trying to calculate the row size and wanted to know if theres a bettter way
to do it than just finding how much each datatype sizes and add accordingly.
I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
the size of these datatypes
Thanks
Look here:
http://groups.google.de/groups?q=row...oft.com&rnum=3
select sum(CHARACTER_MAXIMUM_LENGTH)
from information_schema.columns
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = 'authors'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:O0J4XGLRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Trying to calculate the row size and wanted to know if theres a bettter
> way
> to do it than just finding how much each datatype sizes and add
> accordingly.
> I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
> the size of these datatypes
> Thanks
>

bytes used per datatype

Trying to calculate the row size and wanted to know if theres a bettter way
to do it than just finding how much each datatype sizes and add accordingly.
I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
the size of these datatypes
ThanksLook here:
http://groups.google.de/groups?q=rowsize+sql+server&hl=de&lr=&selm=udj18LFHAHA.259%40cppssbbsa02.microsoft.com&rnum=3
select sum(CHARACTER_MAXIMUM_LENGTH)
from information_schema.columns
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = 'authors'
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:O0J4XGLRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Trying to calculate the row size and wanted to know if theres a bettter
> way
> to do it than just finding how much each datatype sizes and add
> accordingly.
> I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
> the size of these datatypes
> Thanks
>

bytes used per datatype

Trying to calculate the row size and wanted to know if theres a bettter way
to do it than just finding how much each datatype sizes and add accordingly.
I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
the size of these datatypes
ThanksLook here:
%40cppssbbsa02.microsoft.com&rnum=3" target="_blank">http://groups.google.de/groups?q=ro...soft.com&rnum=3
select sum(CHARACTER_MAXIMUM_LENGTH)
from information_schema.columns
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = 'authors'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:O0J4XGLRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Trying to calculate the row size and wanted to know if theres a bettter
> way
> to do it than just finding how much each datatype sizes and add
> accordingly.
> I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
> the size of these datatypes
> Thanks
>