More on drive space

xp_fixeddrive works fine for getting the free space of a drive, but is there
a clean way to get the size of the drive? chkdsk and fsutil through
xp_cmdshell have been ruled out.There are couple of ways of doing that: You can use SMO/DMO based on SQL
Server version. You can use Powershell/VBScript and get a snapshot of "My
Computer"
Another ugly way of doing is (I dont' recommend) (You can run SRVINFO from
NT resource kit and spit the output in a textfile and get that info in SQL
using SP_ReadErrorLog)
However, there's a good script on how to get SQL Server data/log utilization
report from each drive @.
http://sqlcommunity.com/Default.aspx?grm2id=49&tabid=56
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Server Community)
SQL Tips, Scripts, Articles, Forums, Radio and a lot of SQL Server fun.
"Jay" wrote:
> xp_fixeddrive works fine for getting the free space of a drive, but is there
> a clean way to get the size of the drive? chkdsk and fsutil through
> xp_cmdshell have been ruled out.
>
>|||Well Saleem, nice little script except for a couple things.
- It doesn't pull the capicity of the drive.
- You posted a copy with errors in it.
Intresting site though.
"Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
news:A8CDF944-53AB-4EC1-9CFC-A27AE1F9E0FE@.microsoft.com...
> There are couple of ways of doing that: You can use SMO/DMO based on SQL
> Server version. You can use Powershell/VBScript and get a snapshot of "My
> Computer"
> Another ugly way of doing is (I dont' recommend) (You can run SRVINFO from
> NT resource kit and spit the output in a textfile and get that info in SQL
> using SP_ReadErrorLog)
> However, there's a good script on how to get SQL Server data/log
> utilization
> report from each drive @.
> http://sqlcommunity.com/Default.aspx?grm2id=49&tabid=56
> Thank you,
> Saleem Hakani
> HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Server Community)
> SQL Tips, Scripts, Articles, Forums, Radio and a lot of SQL Server fun.
>
> "Jay" wrote:
>> xp_fixeddrive works fine for getting the free space of a drive, but is
>> there
>> a clean way to get the size of the drive? chkdsk and fsutil through
>> xp_cmdshell have been ruled out.
>>|||Hi Jay,
I think this is what you are looking for:
Get the facts about your drives and SQL Server space utilization from SQL
Server: http://www.sqlcommunity.com/Default.aspx?grm2id=53&tabid=56
Hope this helps.
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Server Community)
SQLTips, Scripts, Discussions, Blogs, Articles, Radio and a lot of SQL
Server Fun.
"Jay" wrote:
> Well Saleem, nice little script except for a couple things.
> - It doesn't pull the capicity of the drive.
> - You posted a copy with errors in it.
> Intresting site though.
>
> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
> news:A8CDF944-53AB-4EC1-9CFC-A27AE1F9E0FE@.microsoft.com...
> > There are couple of ways of doing that: You can use SMO/DMO based on SQL
> > Server version. You can use Powershell/VBScript and get a snapshot of "My
> > Computer"
> >
> > Another ugly way of doing is (I dont' recommend) (You can run SRVINFO from
> > NT resource kit and spit the output in a textfile and get that info in SQL
> > using SP_ReadErrorLog)
> >
> > However, there's a good script on how to get SQL Server data/log
> > utilization
> > report from each drive @.
> > http://sqlcommunity.com/Default.aspx?grm2id=49&tabid=56
> >
> > Thank you,
> > Saleem Hakani
> > HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Server Community)
> > SQL Tips, Scripts, Articles, Forums, Radio and a lot of SQL Server fun.
> >
> >
> > "Jay" wrote:
> >
> >> xp_fixeddrive works fine for getting the free space of a drive, but is
> >> there
> >> a clean way to get the size of the drive? chkdsk and fsutil through
> >> xp_cmdshell have been ruled out.
> >>
> >>
> >>
>
>sql

More Bytes returned from Image Data Type

I have a field set to Image data type, and store a long string(possible othe
r
objects)into it with size 5663, and returned with 5663+28 bytes.
These strings wil be deserialized after retrieving. However failde because
the bytes seems to include some extra bytes with \0 and 0x01 and have 28 mor
e
bytes ahead of the original string.
How could I deal with it? Just remove the first 28 bytes? It this method
formal? I had thought the returned bytes should have the same size with the
one I stored into, it seems not, am I right?
Thanks.BOL talks about "BLOBs and OLE Objects" and the normal process for images wa
s
to read in "chunks" (e.g. Sussman's ADO 2.6 Wrox book), but now should use
Stream object instead - check your doc [you omitted to say what
language/version you were using]
HTH
Dick
"zhaounknown" wrote:

> I have a field set to Image data type, and store a long string(possible ot
her
> objects)into it with size 5663, and returned with 5663+28 bytes.
> These strings wil be deserialized after retrieving. However failde because
> the bytes seems to include some extra bytes with \0 and 0x01 and have 28 m
ore
> bytes ahead of the original string.
> How could I deal with it? Just remove the first 28 bytes? It this method
> formal? I had thought the returned bytes should have the same size with th
e
> one I stored into, it seems not, am I right?
> Thanks.|||I am using C#, in .Net framework 1.1.
What documentation I shoudl refer to, since I can't find one addressing
this, partly because I am new to the BLOB data type.
However, how the underlying BLOB field is implemented doesn't matter to the
queried result for a BLOB field, is that right?
Please give more detailed guidance for this.
Thank you very much.
"Dick in UK" wrote:
> BOL talks about "BLOBs and OLE Objects" and the normal process for images
was
> to read in "chunks" (e.g. Sussman's ADO 2.6 Wrox book), but now should use
> Stream object instead - check your doc [you omitted to say what
> language/version you were using]
> HTH
> Dick
> "zhaounknown" wrote:
>

Monsterlog file - short term solution?

Hi,
Ive inherited admin of a database of which I was a user, so I'm new to this.
The log file has grown to an enormous size. This has happened in the past
(only a couple of times in a year) and the db admin has fixed it. From
reading other posts I think I will set the recovery to SIMPLE (it is a data
warehouse). (However whatever scheme was in put in place by the admin
normally worked OK, even with recovery set to FULL, so I am not sure what has
changed.)
So, I still have the problem right now of the huge log file and a database
that will not respond. Can I fix the problem without backing up the log file
(to be honest the log file is of no use)? There is not enough space on disk
media to backup the log file. Is there a way of "fooling" SQL Server into
thinking it has been backed up?
Any help appreciated. And yes I will read up on BOL
Les R
Sounds like you have your recovery plan set to FULL. If you
truly do not need the contents of the log file then you can
truncate it
backup log <your db> with truncate_only
go
This will delete the inactive portion of the log
You can then issue a DBCC SHRINKFILE command
to reclaim disk space the log may have requested from
the operating system in the course of it's growth:
use <your db>
go
dbcc shrinkfile(<log name>, <size>)
go
Make sure you allocate enough initial space to the log
doesn't have to keep autogrowing (assuming it's set
to autogrow).
If you're using the FULL recovery model, also set it
to SIMPLE.
Before you do anything, make sure that you in fact
do not need the log info. And read up on the
backup log and dbcc shrinkfile before you run
them so you understand them.
"Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
news:453CEC6E-4E5F-4FDB-BE4E-20AE8CB91C16@.microsoft.com...
> Hi,
> Ive inherited admin of a database of which I was a user, so I'm new to
this.
> The log file has grown to an enormous size. This has happened in the past
> (only a couple of times in a year) and the db admin has fixed it. From
> reading other posts I think I will set the recovery to SIMPLE (it is a
data
> warehouse). (However whatever scheme was in put in place by the admin
> normally worked OK, even with recovery set to FULL, so I am not sure what
has
> changed.)
> So, I still have the problem right now of the huge log file and a database
> that will not respond. Can I fix the problem without backing up the log
file
> (to be honest the log file is of no use)? There is not enough space on
disk
> media to backup the log file. Is there a way of "fooling" SQL Server into
> thinking it has been backed up?
> Any help appreciated. And yes I will read up on BOL
> Les R

Monsterlog file - short term solution?

Hi,
Ive inherited admin of a database of which I was a user, so I'm new to this.
The log file has grown to an enormous size. This has happened in the past
(only a couple of times in a year) and the db admin has fixed it. From
reading other posts I think I will set the recovery to SIMPLE (it is a data
warehouse). (However whatever scheme was in put in place by the admin
normally worked OK, even with recovery set to FULL, so I am not sure what ha
s
changed.)
So, I still have the problem right now of the huge log file and a database
that will not respond. Can I fix the problem without backing up the log fil
e
(to be honest the log file is of no use)? There is not enough space on disk
media to backup the log file. Is there a way of "fooling" SQL Server into
thinking it has been backed up?
Any help appreciated. And yes I will read up on BOL
Les RSounds like you have your recovery plan set to FULL. If you
truly do not need the contents of the log file then you can
truncate it
backup log <your db> with truncate_only
go
This will delete the inactive portion of the log
You can then issue a DBCC SHRINKFILE command
to reclaim disk space the log may have requested from
the operating system in the course of it's growth:
use <your db>
go
dbcc shrinkfile(<log name>, <size> )
go
Make sure you allocate enough initial space to the log
doesn't have to keep autogrowing (assuming it's set
to autogrow).
If you're using the FULL recovery model, also set it
to SIMPLE.
Before you do anything, make sure that you in fact
do not need the log info. And read up on the
backup log and dbcc shrinkfile before you run
them so you understand them.
"Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
news:453CEC6E-4E5F-4FDB-BE4E-20AE8CB91C16@.microsoft.com...
> Hi,
> Ive inherited admin of a database of which I was a user, so I'm new to
this.
> The log file has grown to an enormous size. This has happened in the past
> (only a couple of times in a year) and the db admin has fixed it. From
> reading other posts I think I will set the recovery to SIMPLE (it is a
data
> warehouse). (However whatever scheme was in put in place by the admin
> normally worked OK, even with recovery set to FULL, so I am not sure what
has
> changed.)
> So, I still have the problem right now of the huge log file and a database
> that will not respond. Can I fix the problem without backing up the log
file
> (to be honest the log file is of no use)? There is not enough space on
disk
> media to backup the log file. Is there a way of "fooling" SQL Server into
> thinking it has been backed up?
> Any help appreciated. And yes I will read up on BOL
> Les Rsql

Monsterlog file - short term solution?

Hi,
Ive inherited admin of a database of which I was a user, so I'm new to this.
The log file has grown to an enormous size. This has happened in the past
(only a couple of times in a year) and the db admin has fixed it. From
reading other posts I think I will set the recovery to SIMPLE (it is a data
warehouse). (However whatever scheme was in put in place by the admin
normally worked OK, even with recovery set to FULL, so I am not sure what has
changed.)
So, I still have the problem right now of the huge log file and a database
that will not respond. Can I fix the problem without backing up the log file
(to be honest the log file is of no use)? There is not enough space on disk
media to backup the log file. Is there a way of "fooling" SQL Server into
thinking it has been backed up?
Any help appreciated. And yes I will read up on BOL :)
Les RSounds like you have your recovery plan set to FULL. If you
truly do not need the contents of the log file then you can
truncate it
backup log <your db> with truncate_only
go
This will delete the inactive portion of the log
You can then issue a DBCC SHRINKFILE command
to reclaim disk space the log may have requested from
the operating system in the course of it's growth:
use <your db>
go
dbcc shrinkfile(<log name>, <size>)
go
Make sure you allocate enough initial space to the log
doesn't have to keep autogrowing (assuming it's set
to autogrow).
If you're using the FULL recovery model, also set it
to SIMPLE.
Before you do anything, make sure that you in fact
do not need the log info. And read up on the
backup log and dbcc shrinkfile before you run
them so you understand them.
"Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
news:453CEC6E-4E5F-4FDB-BE4E-20AE8CB91C16@.microsoft.com...
> Hi,
> Ive inherited admin of a database of which I was a user, so I'm new to
this.
> The log file has grown to an enormous size. This has happened in the past
> (only a couple of times in a year) and the db admin has fixed it. From
> reading other posts I think I will set the recovery to SIMPLE (it is a
data
> warehouse). (However whatever scheme was in put in place by the admin
> normally worked OK, even with recovery set to FULL, so I am not sure what
has
> changed.)
> So, I still have the problem right now of the huge log file and a database
> that will not respond. Can I fix the problem without backing up the log
file
> (to be honest the log file is of no use)? There is not enough space on
disk
> media to backup the log file. Is there a way of "fooling" SQL Server into
> thinking it has been backed up?
> Any help appreciated. And yes I will read up on BOL :)
> Les R

monitoring using sql server

Hi,

Could u pls tell as to how to find the size of a transaction or monitor it on a server using sql server 2005?

Regards

Rohit

I am not sure what u mean by Size of a transaction. U can use Profiler or Trace to monitor activities on a server

Refer : http://msdn2.microsoft.com/en-us/library/aa173918(SQL.80).aspx

Madhu

Monitoring the db size

Hi All,

I'm wonderring if someone has the script which can run on each server to get all dbs size , free space on this server ? Curently I am using the enterprise manager to check the db space usage manually, but this is very frustrated due to a server has many dbs located on it

Thanks,Hi All,

I'm wonderring if someone has the script which can run on each server to get all dbs size , free space on this server ? Curently I am using the enterprise manager to check the db space usage manually, but this is very frustrated due to a server has many dbs located on it

Thanks,

This (http://www.dbforums.com/t1006334.html) might be what you are looking for...

[Edit: some caveats]
1. Consider also using DBCC SHOWCONTIG WITH TABLERESULTS. You could embed this in the sp_MSForEachDB.
2. Note that the data may not match reality; you may need to run dbcc updateusage before you run the sizing extract.
3. YMMV

Regards,

hmscott|||Thanks a lot hmscott, I'll try it. I found a website which has a few scripts , they are very good, for people referecn if need auto manage db size, drive size and free space. It is:

http://www.sqlservercentral.com/columnists/mnash/monitoringdriveanddatabasefreespace.asp

Monitoring table size

We have a table whose size we wish to keep below 200 records. My thought is
to run a query every minute via SQL agent to get the record count. What I am
wondering is if it is possible to log the result of the query as a perfmon
counter so that we can both graph it realtime in perfmon and also alert off
it in Microsoft Operations Manager.
Thanks,
Mark
"Mark Murphy" <viosk@.newsgroup.nospam> schrieb im Newsbeitrag
news:7AE5DEC0-1F6C-4D60-AC99-FB50A13EFAD9@.microsoft.com...
> We have a table whose size we wish to keep below 200 records. My
thought is
> to run a query every minute via SQL agent to get the record count. What
I am
> wondering is if it is possible to log the result of the query as a
perfmon
> counter so that we can both graph it realtime in perfmon and also alert
off
> it in Microsoft Operations Manager.
And how will you react on this? If there's some action that lends itself
to automation, then a trigger is probably the most appropriate means.
That way you reduce load on the db and do the checks only when they are
necessary (i.e. on insertion).
Kind regards
robert
|||Hi Mark
In SQL Server 2000, there are user defined counters that can log any value
you send as a parameter. For example, if you had this statement: EXEC
sp_user_counter1 10, you could watch SQL Server User Counter 1 in a perfmon
graph, and see the value 10.
But you have to explicitly call the procedure to set the value, and you
could do that in a trigger every time the data in the table changes. The
trigger would do a select count(*), save the result into a variable, and
pass the variable to the sp_user_counter procedure. The trigger could also
check for the value being over your limit, and take the alerting action,
rather than using SQL Server's alert mechanism.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mark Murphy" <viosk@.newsgroup.nospam> wrote in message
news:7AE5DEC0-1F6C-4D60-AC99-FB50A13EFAD9@.microsoft.com...
> We have a table whose size we wish to keep below 200 records. My thought
> is
> to run a query every minute via SQL agent to get the record count. What I
> am
> wondering is if it is possible to log the result of the query as a perfmon
> counter so that we can both graph it realtime in perfmon and also alert
> off
> it in Microsoft Operations Manager.
> Thanks,
> Mark
|||Thanks Kalen and Robert,
Exactly the advice I was looking for. We have a sproc that needs to poll
the table for new records periodically (We know it's less efficient, but it's
beyond our control). I'll set the counter there.
-Mark

Monitoring Log Files Size

Dear All,
Is there any method to monitor the size of log files in SQL Server 2000?
If yes how?
Thanks
Robert Lie
Robert Lie wrote:
> Dear All,
> Is there any method to monitor the size of log files in SQL Server
> 2000? If yes how?
> Thanks
> Robert Lie
You can use sp_helpfile to get a listing of all files (data and log) in
the current database.
David Gugick
Imceda Software
www.imceda.com
|||Is there any way to send it the information to my email automatically?
David Gugick wrote:
> Robert Lie wrote:
>
> You can use sp_helpfile to get a listing of all files (data and log) in
> the current database.
>
|||hi,
Yes. You can send the information to your e-mail address automatically.
For that you need to register your e-mail address in the SQL Server.
Create a scheduled job that executes sp_helpfile and sedn the result to
desired e-mail address
I believe this answers your question
thanks and regards
Chandra
"Robert Lie" wrote:

> Is there any way to send it the information to my email automatically?
>
> David Gugick wrote:
>
|||Use SQL Agent Alerts to watch the Performance Monitor counters that track
file size, and when the log crosses a threshold that you define, SQL Server
Agent can take predetermined actions such as running a script to increase the
log size, sending you email, or shrinking the file.
"Robert Lie" wrote:

> Dear All,
> Is there any method to monitor the size of log files in SQL Server 2000?
> If yes how?
> Thanks
> Robert Lie
>
|||Use SQL Agent Alerts to watch the Performance Monitor counters that track
file size, and when the log crosses a threshold that you define, SQL Server
Agent can take predetermined actions such as running a script to increase the
log size, sending you email, or shrinking the file.
Sorry I posted this answer on the other question you asked about log files.
"Robert Lie" wrote:

> Dear All,
> Is there any method to monitor the size of log files in SQL Server 2000?
> If yes how?
> Thanks
> Robert Lie
>

Monitoring Log Files Size

Dear All,
Is there any method to monitor the size of log files in SQL Server 2000?
If yes how?
Thanks
Robert LieRobert Lie wrote:
> Dear All,
> Is there any method to monitor the size of log files in SQL Server
> 2000? If yes how?
> Thanks
> Robert Lie
You can use sp_helpfile to get a listing of all files (data and log) in
the current database.
--
David Gugick
Imceda Software
www.imceda.com|||Is there any way to send it the information to my email automatically?
David Gugick wrote:
> Robert Lie wrote:
>> Dear All,
>> Is there any method to monitor the size of log files in SQL Server
>> 2000? If yes how?
>> Thanks
>> Robert Lie
>
> You can use sp_helpfile to get a listing of all files (data and log) in
> the current database.
>|||hi,
Yes. You can send the information to your e-mail address automatically.
For that you need to register your e-mail address in the SQL Server.
Create a scheduled job that executes sp_helpfile and sedn the result to
desired e-mail address
I believe this answers your question
thanks and regards
Chandra
"Robert Lie" wrote:
> Is there any way to send it the information to my email automatically?
>
> David Gugick wrote:
> > Robert Lie wrote:
> >
> >> Dear All,
> >>
> >> Is there any method to monitor the size of log files in SQL Server
> >> 2000? If yes how?
> >>
> >> Thanks
> >>
> >> Robert Lie
> >
> >
> > You can use sp_helpfile to get a listing of all files (data and log) in
> > the current database.
> >
>|||Use SQL Agent Alerts to watch the Performance Monitor counters that track
file size, and when the log crosses a threshold that you define, SQL Server
Agent can take predetermined actions such as running a script to increase the
log size, sending you email, or shrinking the file.
"Robert Lie" wrote:
> Dear All,
> Is there any method to monitor the size of log files in SQL Server 2000?
> If yes how?
> Thanks
> Robert Lie
>|||Use SQL Agent Alerts to watch the Performance Monitor counters that track
file size, and when the log crosses a threshold that you define, SQL Server
Agent can take predetermined actions such as running a script to increase the
log size, sending you email, or shrinking the file.
Sorry I posted this answer on the other question you asked about log files.
"Robert Lie" wrote:
> Dear All,
> Is there any method to monitor the size of log files in SQL Server 2000?
> If yes how?
> Thanks
> Robert Lie
>

Monitoring Log Files Size

Dear All,
Is there any method to monitor the size of log files in SQL Server 2000?
If yes how?
Thanks
Robert LieRobert Lie wrote:
> Dear All,
> Is there any method to monitor the size of log files in SQL Server
> 2000? If yes how?
> Thanks
> Robert Lie
You can use sp_helpfile to get a listing of all files (data and log) in
the current database.
David Gugick
Imceda Software
www.imceda.com|||Is there any way to send it the information to my email automatically?
David Gugick wrote:
> Robert Lie wrote:
>
>
> You can use sp_helpfile to get a listing of all files (data and log) in
> the current database.
>|||hi,
Yes. You can send the information to your e-mail address automatically.
For that you need to register your e-mail address in the SQL Server.
Create a scheduled job that executes sp_helpfile and sedn the result to
desired e-mail address
I believe this answers your question
thanks and regards
Chandra
"Robert Lie" wrote:

> Is there any way to send it the information to my email automatically?
>
> David Gugick wrote:
>|||Use SQL Agent Alerts to watch the Performance Monitor counters that track
file size, and when the log crosses a threshold that you define, SQL Server
Agent can take predetermined actions such as running a script to increase th
e
log size, sending you email, or shrinking the file.
"Robert Lie" wrote:

> Dear All,
> Is there any method to monitor the size of log files in SQL Server 2000?
> If yes how?
> Thanks
> Robert Lie
>|||Use SQL Agent Alerts to watch the Performance Monitor counters that track
file size, and when the log crosses a threshold that you define, SQL Server
Agent can take predetermined actions such as running a script to increase th
e
log size, sending you email, or shrinking the file.
Sorry I posted this answer on the other question you asked about log files.
"Robert Lie" wrote:

> Dear All,
> Is there any method to monitor the size of log files in SQL Server 2000?
> If yes how?
> Thanks
> Robert Lie
>

Monitoring Data file size

I am looking to automate monitoring space used for each file in each
database on a SQL Server 2000. Does anybody have any SQL Scripts to do
this or to find the space used?I'm not aware of a good/easy way from TSQL. You can use sp_spaceused to
find out how much space is available in the database as a whole - since
MSSQL aims to use each file more or less evenly, that might be good
enough to give you an idea.

Using SQLDMO, however, the DBFile object has a SpaceAvailableInMB
property, which will tell you exactly how much space is free. Behind
the scenes, it calls DBCC SHOWFILESTATS, which is undocumented.

In any case, since MSSQL aims to use the files roughly equally, it's
often more important to know how much free space is in the database as
a whole (sp_spaceused), or in the transaction log (DBCC SQLPERF).

Simon

Monitor SQL data/log file % used

Is there a way to monitor the % (or size) of the data file used in a SQL
2005 database? I can use the Performance Monitor to track the log file (Log
file(s) used size (kb)), but I don't see the same option for the data file.
Am I jut missing something?
ThanksQuery select size from sys.database_files in SQL 2005 or select size from
sysfiles in SQL 2000.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Kelley" <tkelley@.company.com> wrote in message
news:O5Tb7pe0GHA.1040@.TK2MSFTNGP06.phx.gbl...
> Is there a way to monitor the % (or size) of the data file used in a SQL
> 2005 database? I can use the Performance Monitor to track the log file
> (Log file(s) used size (kb)), but I don't see the same option for the data
> file. Am I jut missing something?
> Thanks
>

Monitor SQL data/log file % used

Is there a way to monitor the % (or size) of the data file used in a SQL
2005 database? I can use the Performance Monitor to track the log file (Log
file(s) used size (kb)), but I don't see the same option for the data file.
Am I jut missing something?
ThanksQuery select size from sys.database_files in SQL 2005 or select size from
sysfiles in SQL 2000.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Kelley" <tkelley@.company.com> wrote in message
news:O5Tb7pe0GHA.1040@.TK2MSFTNGP06.phx.gbl...
> Is there a way to monitor the % (or size) of the data file used in a SQL
> 2005 database? I can use the Performance Monitor to track the log file
> (Log file(s) used size (kb)), but I don't see the same option for the data
> file. Am I jut missing something?
> Thanks
>

Monitor database file size

Is there a best practice for monitoring how much space is free in a database
file? I can get this info for the log file using the performance monitor
and send me a message when the free space is low. I don't want to have to
manually check this everyday, so if there is an automated means that would
be great.
Thanks.Hi,
Take a look into this article, will help you to automate to db space
monitoring.
http://www.sql-server-performance.c...with_dts_05.asp
Thanks
Hari
SQL Server MVP
"Tim Kelley" wrote:

> Is there a best practice for monitoring how much space is free in a databa
se
> file? I can get this info for the log file using the performance monitor
> and send me a message when the free space is low. I don't want to have to
> manually check this everyday, so if there is an automated means that would
> be great.
> Thanks.
>
>

Monitor database file size

Is there a best practice for monitoring how much space is free in a database
file? I can get this info for the log file using the performance monitor
and send me a message when the free space is low. I don't want to have to
manually check this everyday, so if there is an automated means that would
be great.
Thanks.Hi,
Take a look into this article, will help you to automate to db space
monitoring.
http://www.sql-server-performance.com/nn_monitor_with_dts_05.asp
Thanks
Hari
SQL Server MVP
"Tim Kelley" wrote:
> Is there a best practice for monitoring how much space is free in a database
> file? I can get this info for the log file using the performance monitor
> and send me a message when the free space is low. I don't want to have to
> manually check this everyday, so if there is an automated means that would
> be great.
> Thanks.
>
>

MOM Alert when tempdb size grows > 40GB

I am trying to setup a MOM Alert for when a tempdb grows in size above 40GB.
How can this be setup? thanks for any helpI have no idea what MOM is, but here is a code snipit that will get you the
raw data.
-- cut --
use tempdb
if object_id('#FileStats') is not null and
objectproperty(object_id('#FileStats'), 'IsTable') = 1
drop table #FileStats
DECLARE @.DB SYSNAME
DECLARE @.SQL NVARCHAR(4000)
CREATE TABLE #FileStats(
[FileId] INT,
[FileGroup] INT,
[TotalExtents] INT,
[UsedExtents] INT,
[Name] sysname,
[Filename] varchar(255)
)
SET @.DB = 'tempdb'
DELETE FROM #FileStats
SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
SHOWFILESTATS WITH NO_INFOMSGS '')'
EXEC (@.SQL)
SELECT 'Size of tempdb is',
TotalExtents * 64/1024.,
'MB'
FROM #FileStats
-- cut --
"alastairn" <alastairn@.discussions.microsoft.com> wrote in message
news:D0242787-B639-4C5B-9426-437E1234F9DF@.microsoft.com...
>I am trying to setup a MOM Alert for when a tempdb grows in size above
>40GB.
> How can this be setup? thanks for any help
>|||MOM is Microsoft Operations Manager - an application for monitoring windows
OS and various server applications (such as exchange, sql server, etc).
I would investigate what is available from the SQL Server management pack
for MOM.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Jay" <spam@.nospam.org> wrote in message
news:e2BWVfMEIHA.4684@.TK2MSFTNGP06.phx.gbl...
>I have no idea what MOM is, but here is a code snipit that will get you the
>raw data.
> -- cut --
> use tempdb
> if object_id('#FileStats') is not null and
> objectproperty(object_id('#FileStats'), 'IsTable') = 1
> drop table #FileStats
> DECLARE @.DB SYSNAME
> DECLARE @.SQL NVARCHAR(4000)
> CREATE TABLE #FileStats(
> [FileId] INT,
> [FileGroup] INT,
> [TotalExtents] INT,
> [UsedExtents] INT,
> [Name] sysname,
> [Filename] varchar(255)
> )
> SET @.DB = 'tempdb'
> DELETE FROM #FileStats
> SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
> SHOWFILESTATS WITH NO_INFOMSGS '')'
> EXEC (@.SQL)
> SELECT 'Size of tempdb is',
> TotalExtents * 64/1024.,
> 'MB'
> FROM #FileStats
> -- cut --
> "alastairn" <alastairn@.discussions.microsoft.com> wrote in message
> news:D0242787-B639-4C5B-9426-437E1234F9DF@.microsoft.com...
>>I am trying to setup a MOM Alert for when a tempdb grows in size above
>>40GB.
>> How can this be setup? thanks for any help
>>
>