Database Growth

I would like to know followings:

I want to see every day or weekly Database growth (%) save on table

I have some SP which will give me one time run and see the growth. which is ...

Please advice any other way to find out and save on a location ...

create procedure sp_growth as

set ansi_warnings off

declare @.l_db_name varchar(50)
,@.l_sql_string varchar(1000)

set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth

create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))

declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases

open db_name_cursor

fetch next from db_name_cursor into
@.l_db_name

While (@.@.fetch_status = 0)
begin
select @.l_sql_string = 'select ' + '''' + @.l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @.l_db_name + '].dbo.sysfiles'
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@.l_sql_string)

fetch next from db_name_cursor into
@.l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return

GO

Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/

Check for responses to your duplicate post in the Transact-SQL forum.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

Database Growth

I would like to know followings:

I want to see every day or weekly Database growth (%) save on table

I have some SP which will give me one time run and see the growth. which is ...

Please advice any other way to find out and save on a location ...

create procedure sp_growth as

set ansi_warnings off

declare @.l_db_name varchar(50)
,@.l_sql_string varchar(1000)

set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth

create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))

declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases

open db_name_cursor

fetch next from db_name_cursor into
@.l_db_name

While (@.@.fetch_status = 0)
begin
select @.l_sql_string = 'select ' + '''' + @.l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @.l_db_name + '].dbo.sysfiles'
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@.l_sql_string)

fetch next from db_name_cursor into
@.l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return

GO

Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/

Check for responses to your duplicate post in the Transact-SQL forum.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

|||

I have never receive solution.

Thanks

Database Growth

I would like to know followings:

I want to see every day or weekly Database growth (%) save on table

I have some SP which will give me one time run and see the growth. which is ...

Please advice any other way to find out and save on a location ...

create procedure sp_growth as

set ansi_warnings off

declare @.l_db_name varchar(50)
,@.l_sql_string varchar(1000)

set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth

create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))

declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases

open db_name_cursor

fetch next from db_name_cursor into
@.l_db_name

While (@.@.fetch_status = 0)
begin
select @.l_sql_string = 'select ' + '''' + @.l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @.l_db_name + '].dbo.sysfiles'
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@.l_sql_string)

fetch next from db_name_cursor into
@.l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return

GO

Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/

Check for responses to your duplicate post in the Transact-SQL forum.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

|||

I have never receive solution.

Thanks

Database Growth

I would like to know followings:

I want to see every day or weekly Database growth (%) save on table

I have some SP which will give me one time run and see the growth. which is ...

Please advice any other way to find out and save on a location ...

create procedure sp_growth as

set ansi_warnings off

declare @.l_db_name varchar(50)
,@.l_sql_string varchar(1000)

set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth

create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))

declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases

open db_name_cursor

fetch next from db_name_cursor into
@.l_db_name

While (@.@.fetch_status = 0)
begin
select @.l_sql_string = 'select ' + '''' + @.l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @.l_db_name + '].dbo.sysfiles'
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@.l_sql_string)

fetch next from db_name_cursor into
@.l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return

GO

Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/

Hi Faiz,

You could use the following. Used in SQL 2000 and I use it for SQL 2005.

Code Snippet

use database_name

go

dbcc showfilestats

go

For log file sizes:

Code Snippet

dbcc SQLperf(logspace)

go

more over sysfiles, only shows the file size allocated, not being used.

regards

Jag

database diagrammer bug??

This morning I spent 3 hours drawing a diagram for 75 tables, had it looking
very nice, was saving on a regular basis, but then I couldn't save data
structure changes to a table until I closed the diagram out, which also
wanted to save one last time. So when I opened it back up, nearly all of my
work was gone. Tables that I had removed are now back. Objects are not
arranged like I had them, they're all in a straight line. All relationships
except for 4 or 5 are gone. I pretty much wasted all that time. Is this a
bug?
I am having other Enterprise Manager problems too, like I cannot see bit
fields in my available fields list while creating indexes. I would think
that I should reinstall Enterprise Manager but I've reinstalled twice with
the client tools on sql2k and still have the bit field problem.
thank you,
CoryI have not used diagraming in sql server enterprise manager as extensively a
s
you have. In general it is not a good tool for creating the database diagram
.
It is good for a few tables.
You should consider using a tool that was properly built for diagraming.
Like ErWin, I even Use Visio Professional Edition.
It is possibly a bug, but I would definatelly advise closing the diagram
prior to making table changes.
As far as bit fields not showing up when creating indexes.
This is not a bug. you cannot create an index on a bit.
Indexes are only valuable when there is a varied amount of data. For a bit
there are only 2 possible values 0 and 1, so in the best case scenario the
chance of a record being selected is 50%. The selectivity of the data of a
column with a bit data type is not great enough to justify the cost of havin
g
to look at the index.
"Cory Harrison" wrote:

> This morning I spent 3 hours drawing a diagram for 75 tables, had it looki
ng
> very nice, was saving on a regular basis, but then I couldn't save data
> structure changes to a table until I closed the diagram out, which also
> wanted to save one last time. So when I opened it back up, nearly all of
my
> work was gone. Tables that I had removed are now back. Objects are not
> arranged like I had them, they're all in a straight line. All relationshi
ps
> except for 4 or 5 are gone. I pretty much wasted all that time. Is this
a
> bug?
> I am having other Enterprise Manager problems too, like I cannot see bit
> fields in my available fields list while creating indexes. I would think
> that I should reinstall Enterprise Manager but I've reinstalled twice with
> the client tools on sql2k and still have the bit field problem.
>
> thank you,
> Cory
>
>|||Thanks for the advice regarding the software tools.
As for the indexes, yes, you can have bits in indexes but only with sql2k.
I understand the reasoning behind not indexing a column based on a bit, but
it would speed things up in a covering situation if you could put your more
selective fields first then put the bit field last because sql server would
not have to do a bookmark lookup, which is all I want it for. I have some
situations where multiple fields are used in a query but a bookmark lookup
is needed because of that one stupid bit field. I can write scripts that
add bits to indexes, I just cannot do it in Enterprise Manager. I have
confirmed this with Kimberly Tripp, and if she says it must be a bug, I
doubt many of us should argue, I just don't know what to do about it. I was
ok with just writing scripts as a workaround until I lost 3 hours worth of
work earlier and thought that it may be related.
thanks,
Cory
"rmartinez" <rmartinez@.discussions.microsoft.com> wrote in message
news:8E0B6BE8-CCB0-478D-A0C1-9256B8753281@.microsoft.com...
>I have not used diagraming in sql server enterprise manager as extensively
>as
> you have. In general it is not a good tool for creating the database
> diagram.
> It is good for a few tables.
> You should consider using a tool that was properly built for diagraming.
> Like ErWin, I even Use Visio Professional Edition.
> It is possibly a bug, but I would definatelly advise closing the diagram
> prior to making table changes.
> As far as bit fields not showing up when creating indexes.
> This is not a bug. you cannot create an index on a bit.
> Indexes are only valuable when there is a varied amount of data. For a bit
> there are only 2 possible values 0 and 1, so in the best case scenario the
> chance of a record being selected is 50%. The selectivity of the data of a
> column with a bit data type is not great enough to justify the cost of
> having
> to look at the index.
> "Cory Harrison" wrote:
>

Database diagram

Hi,
Is it possible to save a database diagram made in MSSQL2000 as a picture? As
it is now, I can only print it, but I also want is a picture.
Thanks,
Mats-Lennart
Not that I am aware of one.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Mats-Lennart Hansson" <ap_skallen@.hotmail.com> wrote in message
news:%23GHH$0YOEHA.1388@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is it possible to save a database diagram made in MSSQL2000 as a picture?
As
> it is now, I can only print it, but I also want is a picture.
> Thanks,
> Mats-Lennart
>
|||Although you could use PrintScreen to capture the screen, paste into
something like Microsoft Photo Editor, & then crop to the bit you want...
Or you could print it to a pdf file?
Cheers,
James Goodman
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:OJHq9PZOEHA.3300@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not that I am aware of one.
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
> "Mats-Lennart Hansson" <ap_skallen@.hotmail.com> wrote in message
> news:%23GHH$0YOEHA.1388@.TK2MSFTNGP09.phx.gbl...
picture?
> As
>
|||Thats an option you have anyways. I am referring to direct saving to a file.
And I am not aware of one for sure.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"James" <jamesATnorton-associates.co.ukREMOVE> wrote in message
news:OFBL3XZOEHA.3312@.tk2msftngp13.phx.gbl...
> Although you could use PrintScreen to capture the screen, paste into
> something like Microsoft Photo Editor, & then crop to the bit you want...
> Or you could print it to a pdf file?
> --
> Cheers,
> James Goodman
> "Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
> news:OJHq9PZOEHA.3300@.TK2MSFTNGP09.phx.gbl...
> picture?
>
|||You can also use Visio (Enterprise Architect?) to reverse-engineer the
database and produce an even more-detailed diagram.
I believe that this is what many developers actually do.
-G
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:uPDb57aOEHA.3052@.TK2MSFTNGP12.phx.gbl...
> Thats an option you have anyways. I am referring to direct saving to a
file.[vbcol=seagreen]
> And I am not aware of one for sure.
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
> "James" <jamesATnorton-associates.co.ukREMOVE> wrote in message
> news:OFBL3XZOEHA.3312@.tk2msftngp13.phx.gbl...
want...
>

Database design

I have got a design issue.I have got 4 tables,having relationships.Now,user will edit records and save them,but I want to save the values which were exixsting before they were edited and saved.Implying,if a record is edited 50 times,then the values of each edit needs to be saved.How best ,can I achieve this.I can see 2 answers, there may be more.
1. Fire a trigger on the before update that would collect the data and populate it some where else.
2. Use Stored procedure to do all updating and inserting then you can add code to each procedure that would grap the values before it updates.

I would go with the SP Method as triggers can get busy.|||Indeed ... create a history table where each time an update happens you will save a record|||Here is a nice Generic audit trail trigger code:

http://www.nigelrivett.net/AuditTrailTrigger.html