I am writing a script that would give me databases of all servers. I also
need to know whats the database size fo each database on each server. The
way i get teh first one working was set up linked server to all databases
that i need the information from. Then for each server get the databases
from sysdatabases. I run into problem when i need to know the database
size. sp_spaceused returns 2 rows for a database is there any procedure
that i can use in msforeachdb to get the database size. Or is there nay
other means to do this. any help will be greatly appreciated.
TIA
this is the text for my existing proc.
CREATE PROCEDURE dbo.PR_ALLDBSOFALLSERVERS
AS
DECLARE @.ServerName varchar(100),
@.return int,
@.sql nvarchar(1000)
SET NOCOUNT ON
if exists (select * from master.dbo.sysobjects where name = 'Details')
BEGIN
truncate table master..Details
end
else
begin
Create Table Master..Details
( ServerName Char(100),
DatabaseName Char(100))
end
DECLARE server_cursor CURSOR
FOR
SELECT srvname FROM master..sysservers
where srvname not in ('repl_distributor')
OPEN server_cursor
FETCH NEXT FROM server_cursor INTO @.ServerName
WHILE (@.@.fetch_status <> -1)
BEGIN
SELECT @.sql = 'INSERT INTO master..Details SELECT ''[' + rtrim(@.ServerName)
+']'', name from [' + rtrim(@.Servername)+'].master.dbo.sysdatabases'
--select @.sql
exec sp_executesql @.sql
FETCH NEXT FROM server_cursor INTO @.ServerName
END
DEALLOCATE server_cursor
SELECT * from master..Details
SET NOCOUNT OFF
GO
Message posted via http://www.webservertalk.comKilobytes of all files used by the current database (data and log files):
SELECT SUM([size]) * 8 FROM dbo.sysfiles
sysfiles.size is the number of pages used by the db; each page is 8 kb; so
size * 8 = kb. multiply by 1024 to get bytes, or divide by 1024 to get mb,
etc.
"ishaan99 via webservertalk.com" wrote:
> I am writing a script that would give me databases of all servers. I also
> need to know whats the database size fo each database on each server. The
> way i get teh first one working was set up linked server to all databases
> that i need the information from. Then for each server get the databases
> from sysdatabases. I run into problem when i need to know the database
> size. sp_spaceused returns 2 rows for a database is there any procedure
> that i can use in msforeachdb to get the database size. Or is there nay
> other means to do this. any help will be greatly appreciated.
> TIA
> this is the text for my existing proc.
> CREATE PROCEDURE dbo.PR_ALLDBSOFALLSERVERS
>
> AS
> DECLARE @.ServerName varchar(100),
> @.return int,
> @.sql nvarchar(1000)
> SET NOCOUNT ON
> if exists (select * from master.dbo.sysobjects where name = 'Details')
> BEGIN
> truncate table master..Details
> end
> else
> begin
>
> Create Table Master..Details
> ( ServerName Char(100),
> DatabaseName Char(100))
> end
> DECLARE server_cursor CURSOR
> FOR
> SELECT srvname FROM master..sysservers
> where srvname not in ('repl_distributor')
> OPEN server_cursor
> FETCH NEXT FROM server_cursor INTO @.ServerName
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> SELECT @.sql = 'INSERT INTO master..Details SELECT ''[' + rtrim(@.ServerName
)
> +']'', name from [' + rtrim(@.Servername)+'].master.dbo.sysdatabases'
> --select @.sql
> exec sp_executesql @.sql
> FETCH NEXT FROM server_cursor INTO @.ServerName
> END
> DEALLOCATE server_cursor
> SELECT * from master..Details
> SET NOCOUNT OFF
> GO
> --
> Message posted via http://www.webservertalk.com
>sql
Showing posts with label size. Show all posts
Showing posts with label size. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Script to enumerate user databases on a remote server
Hello there
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanksHi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanksHi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>
Script to enumerate user databases on a remote server
Hello there
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanksHi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanksHi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>
Script to enumerate user databases on a remote server
Hello there
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanks
Hi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanks
Hi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>
Script to enumerate user databases on a remote server
Hello there
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanksHi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanksHi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>
Script to enumerate user databases on a remote server
Hello there
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanksHi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>|||Hari Prasad wrote:[vbcol=seagreen]
> *Hi,
> You could use the system stored procedures
> sp_helpdb
> or
> sp_databases
> You use could use OSQL and execute this stored procedure in all
> server and
> insert the data into one common table and then use that table in
> displaying the web page.
> Thanks
> Hari
> SQL Server MVP
>
> "Simo Sentissi" <msentissi@.rightnow.com> wrote in message
> news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> can give
> on the
> that
> then it
Have you found an answer to this yet? If not I have script for you.
gills
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1768228.htmlsql
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanksHi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>|||Hari Prasad wrote:[vbcol=seagreen]
> *Hi,
> You could use the system stored procedures
> sp_helpdb
> or
> sp_databases
> You use could use OSQL and execute this stored procedure in all
> server and
> insert the data into one common table and then use that table in
> displaying the web page.
> Thanks
> Hari
> SQL Server MVP
>
> "Simo Sentissi" <msentissi@.rightnow.com> wrote in message
> news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> can give
> on the
> that
> then it
Have you found an answer to this yet? If not I have script for you.
gills
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1768228.htmlsql
Tuesday, March 20, 2012
Script or Stored Procedure that Collects Information from a Database
Information such as:
1. Database Name
2. Users
3. Database Properties (e.g., size, number of tables, etc.)
4. Other information of interest to a DBA (e.g., Maintenance Plan and
if the last job ran successfully)
I know there are umteen utilities out there that can derive this
information. I would like the product to derive its own information
using scripts or stored procs.
Thanks for any suggestions!
RBollinger
If you run profiler while you look through the GUI's for this info, you can
see the tsql they are generating. This can help you build your own tools.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"robboll" <robboll@.hotmail.com> wrote in message
news:3ffe6464-ae36-47f9-94c4-14fb02266d23@.60g2000hsy.googlegroups.com...
> Information such as:
> 1. Database Name
> 2. Users
> 3. Database Properties (e.g., size, number of tables, etc.)
> 4. Other information of interest to a DBA (e.g., Maintenance Plan and
> if the last job ran successfully)
> I know there are umteen utilities out there that can derive this
> information. I would like the product to derive its own information
> using scripts or stored procs.
>
> Thanks for any suggestions!
> RBollinger
1. Database Name
2. Users
3. Database Properties (e.g., size, number of tables, etc.)
4. Other information of interest to a DBA (e.g., Maintenance Plan and
if the last job ran successfully)
I know there are umteen utilities out there that can derive this
information. I would like the product to derive its own information
using scripts or stored procs.
Thanks for any suggestions!
RBollinger
If you run profiler while you look through the GUI's for this info, you can
see the tsql they are generating. This can help you build your own tools.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"robboll" <robboll@.hotmail.com> wrote in message
news:3ffe6464-ae36-47f9-94c4-14fb02266d23@.60g2000hsy.googlegroups.com...
> Information such as:
> 1. Database Name
> 2. Users
> 3. Database Properties (e.g., size, number of tables, etc.)
> 4. Other information of interest to a DBA (e.g., Maintenance Plan and
> if the last job ran successfully)
> I know there are umteen utilities out there that can derive this
> information. I would like the product to derive its own information
> using scripts or stored procs.
>
> Thanks for any suggestions!
> RBollinger
Script or Stored Procedure that Collects Information from a Database
Information such as:
1. Database Name
2. Users
3. Database Properties (e.g., size, number of tables, etc.)
4. Other information of interest to a DBA (e.g., Maintenance Plan and
if the last job ran successfully)
I know there are umteen utilities out there that can derive this
information. I would like the product to derive its own information
using scripts or stored procs.
Thanks for any suggestions!
RBollingerIf you run profiler while you look through the GUI's for this info, you can
see the tsql they are generating. This can help you build your own tools.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"robboll" <robboll@.hotmail.com> wrote in message
news:3ffe6464-ae36-47f9-94c4-14fb02266d23@.60g2000hsy.googlegroups.com...
> Information such as:
> 1. Database Name
> 2. Users
> 3. Database Properties (e.g., size, number of tables, etc.)
> 4. Other information of interest to a DBA (e.g., Maintenance Plan and
> if the last job ran successfully)
> I know there are umteen utilities out there that can derive this
> information. I would like the product to derive its own information
> using scripts or stored procs.
>
> Thanks for any suggestions!
> RBollinger
1. Database Name
2. Users
3. Database Properties (e.g., size, number of tables, etc.)
4. Other information of interest to a DBA (e.g., Maintenance Plan and
if the last job ran successfully)
I know there are umteen utilities out there that can derive this
information. I would like the product to derive its own information
using scripts or stored procs.
Thanks for any suggestions!
RBollingerIf you run profiler while you look through the GUI's for this info, you can
see the tsql they are generating. This can help you build your own tools.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"robboll" <robboll@.hotmail.com> wrote in message
news:3ffe6464-ae36-47f9-94c4-14fb02266d23@.60g2000hsy.googlegroups.com...
> Information such as:
> 1. Database Name
> 2. Users
> 3. Database Properties (e.g., size, number of tables, etc.)
> 4. Other information of interest to a DBA (e.g., Maintenance Plan and
> if the last job ran successfully)
> I know there are umteen utilities out there that can derive this
> information. I would like the product to derive its own information
> using scripts or stored procs.
>
> Thanks for any suggestions!
> RBollinger
Saturday, February 25, 2012
Script
Hi All,
I need to write a script that would check the size of the db file and the size of transaction log file and it they are getting bigger than a certain amount I get notified. Any suggestions?
Thanks.start with sysfiles. the size number might not be totally accurate, you may want use sp_spaceused with the updateuseage argument first.|||Do you have a sample script?|||In fact I do have something somewhere that just goes and tallies the size of the database and log files. It does not however update usage because I was just looking for a quick and dirty number. This however will not meet your needs. I suggest you read up on sp_spaceused and take a look at the system catalog starting with sysfiles. This is an easy thing and it is all layed out for you in BOL if you know where to look.
In BOL, go to Contents\Transact SQL reference and than take a look at system stored procedure and system tables.|||Why wouldn't you just use alerts?|||We don't have a necessary software to do that. To get notified I use mailsend command.
I need to write a script that would check the size of the db file and the size of transaction log file and it they are getting bigger than a certain amount I get notified. Any suggestions?
Thanks.start with sysfiles. the size number might not be totally accurate, you may want use sp_spaceused with the updateuseage argument first.|||Do you have a sample script?|||In fact I do have something somewhere that just goes and tallies the size of the database and log files. It does not however update usage because I was just looking for a quick and dirty number. This however will not meet your needs. I suggest you read up on sp_spaceused and take a look at the system catalog starting with sysfiles. This is an easy thing and it is all layed out for you in BOL if you know where to look.
In BOL, go to Contents\Transact SQL reference and than take a look at system stored procedure and system tables.|||Why wouldn't you just use alerts?|||We don't have a necessary software to do that. To get notified I use mailsend command.
Subscribe to:
Posts (Atom)