Cascading Blocking ?

We have a situation that occurs every so often with blocking of
various databases on one server (Win200 SQL7). It appears to happen at
random, so I'm assuming it originates from something a user does and
not a regularily run process.

We've examined the data available to us and used the very helpful
blocking code on http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html
(Thanks Erland).

Were getting closer to finding the problem, but need some advice on
what to look for.

This is a bit of guesswork, but we suspect that we get into a
situation where blocking takes places, and this then cascades to other
processes which then block others in turn. The original culprit then
finishes, but the blocks continue as the newer processes are holding
something else up. A bit like dominoes. It seems to take a while to
free this up.

The problem we have is determining the start of this process. Once we
are made aware of blocking issues, we can find out who is doing what,
but almost always get a different answer/user and think we're getting
to it a little late.

Ideally, I want to log the blocking somewhere so I can examine the
files when this occurs and can therefore establish a pattern etc...

Any ideas or suggestions would be welcome.Take a look at
http://support.microsoft.com/defaul...kb;EN-US;251004.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Ryan" <ryanofford@.hotmail.com> wrote in message
news:7802b79d.0310170116.c4b64ed@.posting.google.co m...
> We have a situation that occurs every so often with blocking of
> various databases on one server (Win200 SQL7). It appears to happen at
> random, so I'm assuming it originates from something a user does and
> not a regularily run process.
> We've examined the data available to us and used the very helpful
> blocking code on
http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html
> (Thanks Erland).
> Were getting closer to finding the problem, but need some advice on
> what to look for.
> This is a bit of guesswork, but we suspect that we get into a
> situation where blocking takes places, and this then cascades to other
> processes which then block others in turn. The original culprit then
> finishes, but the blocks continue as the newer processes are holding
> something else up. A bit like dominoes. It seems to take a while to
> free this up.
> The problem we have is determining the start of this process. Once we
> are made aware of blocking issues, we can find out who is doing what,
> but almost always get a different answer/user and think we're getting
> to it a little late.
> Ideally, I want to log the blocking somewhere so I can examine the
> files when this occurs and can therefore establish a pattern etc...
> Any ideas or suggestions would be welcome.

Care and Feeding of Databases

I've been wrestling with some questions about databases.
1. I allow my databases to grow 10% and I find that I will have tons of
excess space according to EM database properties. I try to shrink but
it doesn't. I try a full databack up and transaction backup, but the
shrink still doens't help. (I understand that it may be bad form to
allow the database to automaticly grow, however I wish to learn of
whats going on here. Changing the option to prevent growth wont teach
me anything) ;)
2. Because of the above situation, I've found a brute force way: Create
a smaller database (the original size) and use Import/Export to copy
all the data and objects. However, many times this process will fail
with vague errors, (when I check for details about the error, it just
restates it had an error, nothing I could sink my teeth into).

Anyone have links or info that speaks to these situations, or "Do's and
Dont's" about database maintance?
TIA1. It's not really clear (to me) what the exact problem is. Do you mean
that you 'successfully' shrank the database, but the file sizes didn't
change? If so, how did you shink it - with Enterprise Manager, or with
DBCC SHRINKDATABASE/SHRINKFILE (see Books Online)? Did you get any
error messages? How big is the database, and what size are you trying
to shrink it to?

2. No idea - what exactly are you doing, and what are the errors?

You might find these links useful:

http://support.microsoft.com/defaul...kb;en-us;315512
http://support.microsoft.com/defaul...kb;en-us;272318
http://support.microsoft.com/defaul...kb;en-us;324432
http://www.sql-server-performance.c...se_settings.asp

Finally, have you installed the latest servicepack? There are a number
of fixes documented in the Knowledge Base related to shrinking
databases.

Simon|||Thanks Simon,
As an example: Databae DS_V5_TARGET is 23,328MB with 16,086 MB free. I
think the original size that I created was 2,048MB. I try to shrink it,
no change in size. I then learned that I have to backup the
transactions so that they can be flagged as clearable (not sure the
exact terms). So I did a backup via EM, shrink still wont work (via
EM). I dont get errors, i just dont get the files to shrink in size. I
will say, that I have shrunk the databases in the past succesfully, it
just seems hit or miss.
I know point 2 is very vague, I'll try to recreate and post follow ups.
running with SP4.
Thanks for the links
Rob|||Simon,
The third article you listed is esentially what I've been doing...
Thanks!
Rob|||"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1119367627.676850.223550@.g44g2000cwa.googlegr oups.com...
> Thanks Simon,
> As an example: Databae DS_V5_TARGET is 23,328MB with 16,086 MB free. I
> think the original size that I created was 2,048MB. I try to shrink it,
> no change in size. I then learned that I have to backup the
> transactions so that they can be flagged as clearable (not sure the
> exact terms). So I did a backup via EM, shrink still wont work (via
> EM). I dont get errors, i just dont get the files to shrink in size. I
> will say, that I have shrunk the databases in the past succesfully, it
> just seems hit or miss.

First question:
Why are you letting it grow in the first place. You're better off
keeping it one size. (i.e. not growing and shrinking it.)

Second question: Where is most of the space, in the DB file or the log file?
If it's the log file most likely the "virtual log" is at the end of the
physical log file. (I thought SQL 2000 "fixed" this issue but I haven't
really looked into it.)

Suggestion:
Don't let DB grow by 10% if you do insist on using autogrowth. Use a fixed
amount. Otherwise each time it grows it'll grow by a larger amount each
time.

> I know point 2 is very vague, I'll try to recreate and post follow ups.
> running with SP4.
> Thanks for the links
> Rob

Capturing database size on a schedule and graphing?

I'm wondering if there are any other programmers/DBA's out there that have
lots of databases that they need to routinely monitor its file growth over
time. I'm looking for any VB code or scripts that accomplish this.
We have about 500 SQL Server databases on one of our servers and they extend
daily.
I would like to capture their size and save the data so it can be graphed in
Excel or something to show the growth rate of each database.
If anyone has any sample code or idea how I can do some of this -- I would
appreciate it greatly.There are multiple ways you could do this.
One would be a scheduled job in SQL Server, which uses either the
undocumented sp_MSForEachDB or a cursor, loops through the databases, and
logs the result of sp_helpfile. This can be useful if you want to leave out
irrelevant databases using the where clause for the cursor or an if
conditional.
Another way would be a windows scheduled task that calls a VBS script, using
FileSystemObject to loop through all the MDF/NDF files and logs their size
property. This can be useful if all of your relevant databases are in a
specific location, separate from the system databases and/or other databases
you are not interested in logging.
If you can wait a day or two, I will whip something up that should be a bit
more concrete than the above... in the meantime, you could take a crack at
it, and post here if you have specific issues.
Let's narrow the discussion groups down though, okay?
http://www.aspfaq.com/
(Reverse address to reply.)
"DavidM" <spam@.spam.net> wrote in message
news:uBE#1Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> I'm wondering if there are any other programmers/DBA's out there that have
> lots of databases that they need to routinely monitor its file growth over
> time. I'm looking for any VB code or scripts that accomplish this.
> We have about 500 SQL Server databases on one of our servers and they
extend
> daily.
> I would like to capture their size and save the data so it can be graphed
in
> Excel or something to show the growth rate of each database.
> If anyone has any sample code or idea how I can do some of this -- I would
> appreciate it greatly.
>
>|||Hi
You may want to check out the code in sp_spaceused and adapt it to suit your
purposes.
John
"DavidM" <spam@.spam.net> wrote in message
news:uBE%231Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> I'm wondering if there are any other programmers/DBA's out there that have
> lots of databases that they need to routinely monitor its file growth over
> time. I'm looking for any VB code or scripts that accomplish this.
> We have about 500 SQL Server databases on one of our servers and they
> extend daily.
> I would like to capture their size and save the data so it can be graphed
> in Excel or something to show the growth rate of each database.
> If anyone has any sample code or idea how I can do some of this -- I would
> appreciate it greatly.
>
>|||...also sp_databases would probably do this if you don't want to split up
log and data files.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:eR1GFfb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hi
> You may want to check out the code in sp_spaceused and adapt it to suit
> your purposes.
> John
> "DavidM" <spam@.spam.net> wrote in message
> news:uBE%231Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
>|||There are some code can be used on this web site:
http://www.sqlservercentral.com/scr...ibutions/31.asp
I tried it, seems very nice.
Good luck
"DavidM" <spam@.spam.net> wrote in message
news:uBE%231Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> I'm wondering if there are any other programmers/DBA's out there that have
> lots of databases that they need to routinely monitor its file growth over
> time. I'm looking for any VB code or scripts that accomplish this.
> We have about 500 SQL Server databases on one of our servers and they
extend
> daily.
> I would like to capture their size and save the data so it can be graphed
in
> Excel or something to show the growth rate of each database.
> If anyone has any sample code or idea how I can do some of this -- I would
> appreciate it greatly.
>
>|||If I run a query on the .sysfiles table, the size column shows 1704. Is
this in pages? How do I convert to bytes or megabytes?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:eR1GFfb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hi
> You may want to check out the code in sp_spaceused and adapt it to suit
> your purposes.
> John
> "DavidM" <spam@.spam.net> wrote in message
> news:uBE%231Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
>|||> If I run a query on the .sysfiles table, the size column shows 1704. Is
> this in pages? How do I convert to bytes or megabytes?
SELECT
[Filename],
[SIZE IN KB] = size*8
FROM sysfiles|||Thanks for the reply. I was able to find
http://www.databasejournal.com/feat...cle.php/3339681 which
looks promising.
I got this to work but its a bit kludgy. Since I have a VB application that
we run daily, I'd like to incorporate the collection of stats within this
program.
Next question is, what is the best way to graph this data using Excel? Can
I have Excel read the database/table directory from SQL? If so, that is
what I want to do rather than create a .CSV file from SQL.
Opinions?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OG7fWJb7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> There are multiple ways you could do this.
> One would be a scheduled job in SQL Server, which uses either the
> undocumented sp_MSForEachDB or a cursor, loops through the databases, and
> logs the result of sp_helpfile. This can be useful if you want to leave
> out
> irrelevant databases using the where clause for the cursor or an if
> conditional.
> Another way would be a windows scheduled task that calls a VBS script,
> using
> FileSystemObject to loop through all the MDF/NDF files and logs their size
> property. This can be useful if all of your relevant databases are in a
> specific location, separate from the system databases and/or other
> databases
> you are not interested in logging.
> If you can wait a day or two, I will whip something up that should be a
> bit
> more concrete than the above... in the meantime, you could take a crack at
> it, and post here if you have specific issues.
> Let's narrow the discussion groups down though, okay?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "DavidM" <spam@.spam.net> wrote in message
> news:uBE#1Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> extend
> in
>|||> Next question is, what is the best way to graph this data using Excel?
Have you considered using Reporting Services?|||or you might wish to setup a sql agent job to run at the end of each busines
s
day to collect the information and popuate a base table
-- try the simple but useful approach below
/** the following can be quite useful **/
select instance_name,cntr_value 'Size in (Kb)' from
master..sysperfinfo(nolock)
where object_name like '%databases%'
and counter_name = 'Data File(s) size (Kb)'
and instance_name not in ('_total') -- can include total to get a server
based overview
"DavidM" wrote:

> I'm wondering if there are any other programmers/DBA's out there that have
> lots of databases that they need to routinely monitor its file growth over
> time. I'm looking for any VB code or scripts that accomplish this.
> We have about 500 SQL Server databases on one of our servers and they exte
nd
> daily.
> I would like to capture their size and save the data so it can be graphed
in
> Excel or something to show the growth rate of each database.
> If anyone has any sample code or idea how I can do some of this -- I would
> appreciate it greatly.
>
>

Capturing database size on a schedule and graphing?

I'm wondering if there are any other programmers/DBA's out there that have
lots of databases that they need to routinely monitor its file growth over
time. I'm looking for any VB code or scripts that accomplish this.
We have about 500 SQL Server databases on one of our servers and they extend
daily.
I would like to capture their size and save the data so it can be graphed in
Excel or something to show the growth rate of each database.
If anyone has any sample code or idea how I can do some of this -- I would
appreciate it greatly.There are multiple ways you could do this.
One would be a scheduled job in SQL Server, which uses either the
undocumented sp_MSForEachDB or a cursor, loops through the databases, and
logs the result of sp_helpfile. This can be useful if you want to leave out
irrelevant databases using the where clause for the cursor or an if
conditional.
Another way would be a windows scheduled task that calls a VBS script, using
FileSystemObject to loop through all the MDF/NDF files and logs their size
property. This can be useful if all of your relevant databases are in a
specific location, separate from the system databases and/or other databases
you are not interested in logging.
If you can wait a day or two, I will whip something up that should be a bit
more concrete than the above... in the meantime, you could take a crack at
it, and post here if you have specific issues.
Let's narrow the discussion groups down though, okay?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DavidM" <spam@.spam.net> wrote in message
news:uBE#1Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> I'm wondering if there are any other programmers/DBA's out there that have
> lots of databases that they need to routinely monitor its file growth over
> time. I'm looking for any VB code or scripts that accomplish this.
> We have about 500 SQL Server databases on one of our servers and they
extend
> daily.
> I would like to capture their size and save the data so it can be graphed
in
> Excel or something to show the growth rate of each database.
> If anyone has any sample code or idea how I can do some of this -- I would
> appreciate it greatly.
>
>|||Hi
You may want to check out the code in sp_spaceused and adapt it to suit your
purposes.
John
"DavidM" <spam@.spam.net> wrote in message
news:uBE%231Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> I'm wondering if there are any other programmers/DBA's out there that have
> lots of databases that they need to routinely monitor its file growth over
> time. I'm looking for any VB code or scripts that accomplish this.
> We have about 500 SQL Server databases on one of our servers and they
> extend daily.
> I would like to capture their size and save the data so it can be graphed
> in Excel or something to show the growth rate of each database.
> If anyone has any sample code or idea how I can do some of this -- I would
> appreciate it greatly.
>
>|||...also sp_databases would probably do this if you don't want to split up
log and data files.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:eR1GFfb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hi
> You may want to check out the code in sp_spaceused and adapt it to suit
> your purposes.
> John
> "DavidM" <spam@.spam.net> wrote in message
> news:uBE%231Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
>> I'm wondering if there are any other programmers/DBA's out there that
>> have lots of databases that they need to routinely monitor its file
>> growth over time. I'm looking for any VB code or scripts that accomplish
>> this.
>> We have about 500 SQL Server databases on one of our servers and they
>> extend daily.
>> I would like to capture their size and save the data so it can be graphed
>> in Excel or something to show the growth rate of each database.
>> If anyone has any sample code or idea how I can do some of this -- I
>> would appreciate it greatly.
>>
>|||There are some code can be used on this web site:
http://www.sqlservercentral.com/scripts/contributions/31.asp
I tried it, seems very nice.
Good luck
"DavidM" <spam@.spam.net> wrote in message
news:uBE%231Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> I'm wondering if there are any other programmers/DBA's out there that have
> lots of databases that they need to routinely monitor its file growth over
> time. I'm looking for any VB code or scripts that accomplish this.
> We have about 500 SQL Server databases on one of our servers and they
extend
> daily.
> I would like to capture their size and save the data so it can be graphed
in
> Excel or something to show the growth rate of each database.
> If anyone has any sample code or idea how I can do some of this -- I would
> appreciate it greatly.
>
>|||If I run a query on the .sysfiles table, the size column shows 1704. Is
this in pages? How do I convert to bytes or megabytes?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:eR1GFfb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hi
> You may want to check out the code in sp_spaceused and adapt it to suit
> your purposes.
> John
> "DavidM" <spam@.spam.net> wrote in message
> news:uBE%231Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
>> I'm wondering if there are any other programmers/DBA's out there that
>> have lots of databases that they need to routinely monitor its file
>> growth over time. I'm looking for any VB code or scripts that accomplish
>> this.
>> We have about 500 SQL Server databases on one of our servers and they
>> extend daily.
>> I would like to capture their size and save the data so it can be graphed
>> in Excel or something to show the growth rate of each database.
>> If anyone has any sample code or idea how I can do some of this -- I
>> would appreciate it greatly.
>>
>|||> If I run a query on the .sysfiles table, the size column shows 1704. Is
> this in pages? How do I convert to bytes or megabytes?
SELECT
[Filename],
[SIZE IN KB] = size*8
FROM sysfiles|||Thanks for the reply. I was able to find
http://www.databasejournal.com/features/mssql/article.php/3339681 which
looks promising.
I got this to work but its a bit kludgy. Since I have a VB application that
we run daily, I'd like to incorporate the collection of stats within this
program.
Next question is, what is the best way to graph this data using Excel? Can
I have Excel read the database/table directory from SQL? If so, that is
what I want to do rather than create a .CSV file from SQL.
Opinions?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OG7fWJb7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> There are multiple ways you could do this.
> One would be a scheduled job in SQL Server, which uses either the
> undocumented sp_MSForEachDB or a cursor, loops through the databases, and
> logs the result of sp_helpfile. This can be useful if you want to leave
> out
> irrelevant databases using the where clause for the cursor or an if
> conditional.
> Another way would be a windows scheduled task that calls a VBS script,
> using
> FileSystemObject to loop through all the MDF/NDF files and logs their size
> property. This can be useful if all of your relevant databases are in a
> specific location, separate from the system databases and/or other
> databases
> you are not interested in logging.
> If you can wait a day or two, I will whip something up that should be a
> bit
> more concrete than the above... in the meantime, you could take a crack at
> it, and post here if you have specific issues.
> Let's narrow the discussion groups down though, okay?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "DavidM" <spam@.spam.net> wrote in message
> news:uBE#1Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
>> I'm wondering if there are any other programmers/DBA's out there that
>> have
>> lots of databases that they need to routinely monitor its file growth
>> over
>> time. I'm looking for any VB code or scripts that accomplish this.
>> We have about 500 SQL Server databases on one of our servers and they
> extend
>> daily.
>> I would like to capture their size and save the data so it can be graphed
> in
>> Excel or something to show the growth rate of each database.
>> If anyone has any sample code or idea how I can do some of this -- I
>> would
>> appreciate it greatly.
>>
>|||> Next question is, what is the best way to graph this data using Excel?
Have you considered using Reporting Services?|||or you might wish to setup a sql agent job to run at the end of each business
day to collect the information and popuate a base table
-- try the simple but useful approach below
/** the following can be quite useful **/
select instance_name,cntr_value 'Size in (Kb)' from
master..sysperfinfo(nolock)
where object_name like '%databases%'
and counter_name = 'Data File(s) size (Kb)'
and instance_name not in ('_total') -- can include total to get a server
based overview
"DavidM" wrote:
> I'm wondering if there are any other programmers/DBA's out there that have
> lots of databases that they need to routinely monitor its file growth over
> time. I'm looking for any VB code or scripts that accomplish this.
> We have about 500 SQL Server databases on one of our servers and they extend
> daily.
> I would like to capture their size and save the data so it can be graphed in
> Excel or something to show the growth rate of each database.
> If anyone has any sample code or idea how I can do some of this -- I would
> appreciate it greatly.
>
>|||Is that something I have to buy?
I'm on a zero budget and I need something quick to monitor all my databases.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OJ7n$Uc7EHA.2676@.TK2MSFTNGP12.phx.gbl...
>> Next question is, what is the best way to graph this data using Excel?
> Have you considered using Reporting Services?
>|||>> Have you considered using Reporting Services?
> Is that something I have to buy?
No. If memory servers, it comes with the license of SQL Server.|||"DavidM" <spam@.spam.net> wrote in message
news:OtfnEne7EHA.2516@.TK2MSFTNGP09.phx.gbl...
> Is that something I have to buy?
> I'm on a zero budget and I need something quick to monitor all my
databases.
Nope.
Go to www.microsoft.com and you'll find it there.
I've only started to play with it, but the SQL Reports you can download for
it are cool.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OJ7n$Uc7EHA.2676@.TK2MSFTNGP12.phx.gbl...
> >> Next question is, what is the best way to graph this data using Excel?
> >
> > Have you considered using Reporting Services?
> >
> >
>|||Can you give me exact URL. I'm not sure what product or component your
referring to and I cannot seem to find anything on MS website.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:CPHAd.79614$Uf.15857@.twister.nyroc.rr.com...
> "DavidM" <spam@.spam.net> wrote in message
> news:OtfnEne7EHA.2516@.TK2MSFTNGP09.phx.gbl...
>> Is that something I have to buy?
>> I'm on a zero budget and I need something quick to monitor all my
> databases.
> Nope.
> Go to www.microsoft.com and you'll find it there.
> I've only started to play with it, but the SQL Reports you can download
> for
> it are cool.
>
>>
>> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:OJ7n$Uc7EHA.2676@.TK2MSFTNGP12.phx.gbl...
>> >> Next question is, what is the best way to graph this data using Excel?
>> >
>> > Have you considered using Reporting Services?
>> >
>> >
>>
>|||> Can you give me exact URL. I'm not sure what product or component your
> referring to and I cannot seem to find anything on MS website.
Geez, did you try using the little "search" tool? It's on the top right of
the page, in case you ever need anything from microsoft.com again...
http://www.microsoft.com/sql/reporting/default.asp
--
http://www.aspfaq.com/
(Reverse address to reply.)

Capturing database size on a schedule and graphing?

I'm wondering if there are any other programmers/DBA's out there that have
lots of databases that they need to routinely monitor its file growth over
time. I'm looking for any VB code or scripts that accomplish this.
We have about 500 SQL Server databases on one of our servers and they extend
daily.
I would like to capture their size and save the data so it can be graphed in
Excel or something to show the growth rate of each database.
If anyone has any sample code or idea how I can do some of this -- I would
appreciate it greatly.
There are multiple ways you could do this.
One would be a scheduled job in SQL Server, which uses either the
undocumented sp_MSForEachDB or a cursor, loops through the databases, and
logs the result of sp_helpfile. This can be useful if you want to leave out
irrelevant databases using the where clause for the cursor or an if
conditional.
Another way would be a windows scheduled task that calls a VBS script, using
FileSystemObject to loop through all the MDF/NDF files and logs their size
property. This can be useful if all of your relevant databases are in a
specific location, separate from the system databases and/or other databases
you are not interested in logging.
If you can wait a day or two, I will whip something up that should be a bit
more concrete than the above... in the meantime, you could take a crack at
it, and post here if you have specific issues.
Let's narrow the discussion groups down though, okay?
http://www.aspfaq.com/
(Reverse address to reply.)
"DavidM" <spam@.spam.net> wrote in message
news:uBE#1Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> I'm wondering if there are any other programmers/DBA's out there that have
> lots of databases that they need to routinely monitor its file growth over
> time. I'm looking for any VB code or scripts that accomplish this.
> We have about 500 SQL Server databases on one of our servers and they
extend
> daily.
> I would like to capture their size and save the data so it can be graphed
in
> Excel or something to show the growth rate of each database.
> If anyone has any sample code or idea how I can do some of this -- I would
> appreciate it greatly.
>
>
|||Hi
You may want to check out the code in sp_spaceused and adapt it to suit your
purposes.
John
"DavidM" <spam@.spam.net> wrote in message
news:uBE%231Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> I'm wondering if there are any other programmers/DBA's out there that have
> lots of databases that they need to routinely monitor its file growth over
> time. I'm looking for any VB code or scripts that accomplish this.
> We have about 500 SQL Server databases on one of our servers and they
> extend daily.
> I would like to capture their size and save the data so it can be graphed
> in Excel or something to show the growth rate of each database.
> If anyone has any sample code or idea how I can do some of this -- I would
> appreciate it greatly.
>
>
|||...also sp_databases would probably do this if you don't want to split up
log and data files.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:eR1GFfb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hi
> You may want to check out the code in sp_spaceused and adapt it to suit
> your purposes.
> John
> "DavidM" <spam@.spam.net> wrote in message
> news:uBE%231Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
>
|||There are some code can be used on this web site:
http://www.sqlservercentral.com/scri...butions/31.asp
I tried it, seems very nice.
Good luck
"DavidM" <spam@.spam.net> wrote in message
news:uBE%231Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> I'm wondering if there are any other programmers/DBA's out there that have
> lots of databases that they need to routinely monitor its file growth over
> time. I'm looking for any VB code or scripts that accomplish this.
> We have about 500 SQL Server databases on one of our servers and they
extend
> daily.
> I would like to capture their size and save the data so it can be graphed
in
> Excel or something to show the growth rate of each database.
> If anyone has any sample code or idea how I can do some of this -- I would
> appreciate it greatly.
>
>
|||If I run a query on the .sysfiles table, the size column shows 1704. Is
this in pages? How do I convert to bytes or megabytes?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:eR1GFfb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hi
> You may want to check out the code in sp_spaceused and adapt it to suit
> your purposes.
> John
> "DavidM" <spam@.spam.net> wrote in message
> news:uBE%231Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
>
|||> If I run a query on the .sysfiles table, the size column shows 1704. Is
> this in pages? How do I convert to bytes or megabytes?
SELECT
[Filename],
[SIZE IN KB] = size*8
FROM sysfiles
|||Thanks for the reply. I was able to find
http://www.databasejournal.com/featu...le.php/3339681 which
looks promising.
I got this to work but its a bit kludgy. Since I have a VB application that
we run daily, I'd like to incorporate the collection of stats within this
program.
Next question is, what is the best way to graph this data using Excel? Can
I have Excel read the database/table directory from SQL? If so, that is
what I want to do rather than create a .CSV file from SQL.
Opinions?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OG7fWJb7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> There are multiple ways you could do this.
> One would be a scheduled job in SQL Server, which uses either the
> undocumented sp_MSForEachDB or a cursor, loops through the databases, and
> logs the result of sp_helpfile. This can be useful if you want to leave
> out
> irrelevant databases using the where clause for the cursor or an if
> conditional.
> Another way would be a windows scheduled task that calls a VBS script,
> using
> FileSystemObject to loop through all the MDF/NDF files and logs their size
> property. This can be useful if all of your relevant databases are in a
> specific location, separate from the system databases and/or other
> databases
> you are not interested in logging.
> If you can wait a day or two, I will whip something up that should be a
> bit
> more concrete than the above... in the meantime, you could take a crack at
> it, and post here if you have specific issues.
> Let's narrow the discussion groups down though, okay?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "DavidM" <spam@.spam.net> wrote in message
> news:uBE#1Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> extend
> in
>
|||> Next question is, what is the best way to graph this data using Excel?
Have you considered using Reporting Services?
|||or you might wish to setup a sql agent job to run at the end of each business
day to collect the information and popuate a base table
-- try the simple but useful approach below
/** the following can be quite useful **/
select instance_name,cntr_value 'Size in (Kb)' from
master..sysperfinfo(nolock)
where object_name like '%databases%'
and counter_name = 'Data File(s) size (Kb)'
and instance_name not in ('_total') -- can include total to get a server
based overview
"DavidM" wrote:

> I'm wondering if there are any other programmers/DBA's out there that have
> lots of databases that they need to routinely monitor its file growth over
> time. I'm looking for any VB code or scripts that accomplish this.
> We have about 500 SQL Server databases on one of our servers and they extend
> daily.
> I would like to capture their size and save the data so it can be graphed in
> Excel or something to show the growth rate of each database.
> If anyone has any sample code or idea how I can do some of this -- I would
> appreciate it greatly.
>
>

Capacity of DB

Hi,
We have an old version (well one year) of MSDE in which all databases are
limited to a capacity of 2 gig (per database). If have heard that there might
be a new version of MSDE in which the Databases capacity would be at 8 gig
(per database).
My question is : Is this a fact or a rumor. And if it is true that the newer
version of MSDE gives the posibility to have 8 gig databases. What is the
version of MSDE that gives such a capacity for batabases.
Thanks for your help
Stéphane Pelletier
SQL Express is based on SQL 2005 and is a replacement for MSDE. It will be
limited to 4gb per database and should be released some time late this
summer.
MSDE 2000 is limited to 2gb per database.
-Andrew
"Stphane Pelletier" <Stphane Pelletier@.discussions.microsoft.com> wrote in
message news:F0F18AA2-F738-4415-81C6-54E89EB2796D@.microsoft.com...
> Hi,
> We have an old version (well one year) of MSDE in which all databases are
> limited to a capacity of 2 gig (per database). If have heard that there
might
> be a new version of MSDE in which the Databases capacity would be at 8 gig
> (per database).
> My question is : Is this a fact or a rumor. And if it is true that the
newer
> version of MSDE gives the posibility to have 8 gig databases. What is the
> version of MSDE that gives such a capacity for batabases.
> Thanks for your help
> Stphane Pelletier
|||Many thanks
"Andrew Robinson" wrote:

> SQL Express is based on SQL 2005 and is a replacement for MSDE. It will be
> limited to 4gb per database and should be released some time late this
> summer.
> MSDE 2000 is limited to 2gb per database.
> -Andrew
> "Stéphane Pelletier" <Stphane Pelletier@.discussions.microsoft.com> wrote in
> message news:F0F18AA2-F738-4415-81C6-54E89EB2796D@.microsoft.com...
> might
> newer
>
>

Cant't see the database

When I create a database with the administrator acount on the server in SQL 2005, it adds and I can see it (and the system databases too).

Then I added a user accout (Windows Auth), and connect to the database from the new accout, but I can't see the database I just created myself with the administrator accout (on the server) just the 'System Databases'.

I even tried to make a database with the new user account but get permission denied, even if I set dbcreator permission on the user. I get the error

"CREATE DATABASE permission denied in database 'master'".

Any ides?

Oh, i solved it by Connect, then choose the Options>> button,

And then in "Connect to database:" I choose my database.

Can't uninstall previous version of AdventureWorks database

I want to install the new (February 2007) sample databases. The readme says any previous version must be removed by dropping the database., then running Remove from Add or Remove Programs. I've detached the database, but when I try to uninstall it I get the following message:

"Error 1309.Error reading from file: pathandfilename.mdf. Verify that the file exists and that you can access it."

What should I do.

Barry

It sounds like the uninstall is expecting the file to exisit and you much have deleted it? If you run a repair first it should replace the file then you can run an uninstall.

Thanks

Michelle

|||Both the AdventureWorksDB mdf and ldf files are there. All I've done is detached them first. I tried "Repair"ing the installation first. This ran successfully, but I still couldn't uninstall afterwards.|||Once you have detached the databases then it means that is uninstalled, try to reinstall by applying from the Feb.2007 update.|||Deleting the two database files (after detaching of course!), then running the Uninstall from Add/Remove programs worked!

Can't uninstall or reinstall nonfunctioning sql server

In previous post in the Getting Started section, I discussed problems trying to run queries on multiple databases:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299240#299240&SiteID=1

Nobody could help with the problem. I eventually got a query to run after several attempts at deleting and recreating databases. Then, I was hit with the following error message:

Class does not support aggregation (or class object is remote) (Exception from HRESULT: 0x80040110 (CLASS_E_NOGGREGATION)) (Microsoft.SqlServer.SqlTools.VSIntegration)

Nobody could help with that either but based on posts with the same message, I tried uninstalling sql server. It wouldn't uninstall nor can I repair or reinstall. Uninstalling thru Add or Remove Programs only resulted in the program listing being removed from Add or Remove Programs. Most of the sql server programs are still installed. They can be run but continue to malfunction. When I try a new installation, I get a message that the programs are already installed.

Is a complete reinstall of windows xp, programs and data the only solution? I hate to believe it; but, at this point, I see no other choice.

If your machine is of x86, please email me so I can forward a script to you to clean those unwanted SQL Server 2005 Components. What you can do is as follows.

1. In Add/Remove Program, remove all visible SQL Server 2005 components.

2. Run command sc, kill all unwanted SQL Server services such as SQL, OLAP, RS, Agent, ...

3. Run command regedit, remove all of those registry keys related to those unwanted SQL Server 2005 components. Of course, this operation is time-consuming and error-prone. That is why the script is created.

4. Reboot your machine and rerun SQL Server 2005 setup.

|||Thanks for the info! I sure it will come in handy in the future. Unfortunately, I've already done a complete reinstall of winxp and sql server. Hopefully, it will run better this time.|||I also had same problem , Could you give me the SQL 2005 uninstall script ? My server is x86, Please sent to jeffery_jean@.msn.com
|||

I'm having a similar problem and would like to get the script please.

-morten

|||I'm trying to cleanly uninstall SQL Server 2003 (I think) and .NET 1.1. Is there a script for this or uninstall series of steps that need be taken? Please advise.|||I also have problem with removing SQL Server2005. Using Add/Remove Programs does not remove all files.Now I have problem with reinstalling SQL - <Workstation components, Books on line and Development Tools> stage fails with the message: 'There is a problem with Windows Installer package . A program run as part of the setup did not finish as expected.'
Can you forward me the script to completely remove all SQL Server files and registry references? There is not also a way to uninstall SQL Express.
My email: jurekba@.optusnet.com.au
It's a pity that Microsoft again did not do its job to deliver working uninstall software for SQL Server 2005.

|||

this is a problem related to the Windows installer MSI database. It believes that the install is part-finished and gets itself a little screwed up. I'd recommend downloading the Windows Installer CleanUp Utility from here http://support.microsoft.com/default.aspx?scid=kb;en-us;290301 and removing the relevant SQL Server database entries.

Following this retry the install, it should go through fine.

|||I also had same problem after using Remote Desktop to install/Uniinstall SQL 2005 , Could you give me the SQL 2005 uninstall script ? My server is x86, Please sent to andy.seymour@.bigroup.co.uk asap please. regards Andy.|||I'm another one that needs the script. I tried using the MS Uninstall cleanup utility but it still didn't work. The SQLServer 2005 install thinks I have several components installed -- most notably the database itself -- and fails when it can't start the database engine. I don wonder why MS couldn't have accounted for this in the original install program. Oh well.
Thanks
rchrismon@.patmedia.net
|||I am running an x86 install that I need to remove can you send me the script to vaparicio@.cpisolutions.com
thx.
|||I also am having the same problem. Please email the script to James@.azmiller.com|||

Hi,

Can you please send me that cleaning script, I'm having the same problem.

thanks

Bob

|||I have same problem at hand, Could you kindly give me the SQL 2005 uninstall script ? My server is x86, Win XP SP2 Please send to tmp74in at yahoo.com
|||I think microsoft should supply this, since so many people need it! Could you send it to me too? I've tried all of the above and it still won't install.

Can't uninstall or reinstall nonfunctioning sql server

In previous post in the Getting Started section, I discussed problems trying to run queries on multiple databases:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299240#299240&SiteID=1

Nobody could help with the problem. I eventually got a query to run after several attempts at deleting and recreating databases. Then, I was hit with the following error message:

Class does not support aggregation (or class object is remote) (Exception from HRESULT: 0x80040110 (CLASS_E_NOGGREGATION)) (Microsoft.SqlServer.SqlTools.VSIntegration)

Nobody could help with that either but based on posts with the same message, I tried uninstalling sql server. It wouldn't uninstall nor can I repair or reinstall. Uninstalling thru Add or Remove Programs only resulted in the program listing being removed from Add or Remove Programs. Most of the sql server programs are still installed. They can be run but continue to malfunction. When I try a new installation, I get a message that the programs are already installed.

Is a complete reinstall of windows xp, programs and data the only solution? I hate to believe it; but, at this point, I see no other choice.

If your machine is of x86, please email me so I can forward a script to you to clean those unwanted SQL Server 2005 Components. What you can do is as follows.

1. In Add/Remove Program, remove all visible SQL Server 2005 components.

2. Run command sc, kill all unwanted SQL Server services such as SQL, OLAP, RS, Agent, ...

3. Run command regedit, remove all of those registry keys related to those unwanted SQL Server 2005 components. Of course, this operation is time-consuming and error-prone. That is why the script is created.

4. Reboot your machine and rerun SQL Server 2005 setup.

|||Thanks for the info! I sure it will come in handy in the future. Unfortunately, I've already done a complete reinstall of winxp and sql server. Hopefully, it will run better this time.|||I also had same problem , Could you give me the SQL 2005 uninstall script ? My server is x86, Please sent to jeffery_jean@.msn.com
|||

I'm having a similar problem and would like to get the script please.

-morten

|||I'm trying to cleanly uninstall SQL Server 2003 (I think) and .NET 1.1. Is there a script for this or uninstall series of steps that need be taken? Please advise.|||I also have problem with removing SQL Server2005. Using Add/Remove Programs does not remove all files.Now I have problem with reinstalling SQL - <Workstation components, Books on line and Development Tools> stage fails with the message: 'There is a problem with Windows Installer package . A program run as part of the setup did not finish as expected.'
Can you forward me the script to completely remove all SQL Server files and registry references? There is not also a way to uninstall SQL Express.
My email: jurekba@.optusnet.com.au
It's a pity that Microsoft again did not do its job to deliver working uninstall software for SQL Server 2005.

|||

this is a problem related to the Windows installer MSI database. It believes that the install is part-finished and gets itself a little screwed up. I'd recommend downloading the Windows Installer CleanUp Utility from here http://support.microsoft.com/default.aspx?scid=kb;en-us;290301 and removing the relevant SQL Server database entries.

Following this retry the install, it should go through fine.

|||I also had same problem after using Remote Desktop to install/Uniinstall SQL 2005 , Could you give me the SQL 2005 uninstall script ? My server is x86, Please sent to andy.seymour@.bigroup.co.uk asap please. regards Andy.|||I'm another one that needs the script. I tried using the MS Uninstall cleanup utility but it still didn't work. The SQLServer 2005 install thinks I have several components installed -- most notably the database itself -- and fails when it can't start the database engine. I don wonder why MS couldn't have accounted for this in the original install program. Oh well.
Thanks
rchrismon@.patmedia.net
|||I am running an x86 install that I need to remove can you send me the script to vaparicio@.cpisolutions.com
thx.
|||I also am having the same problem. Please email the script to James@.azmiller.com|||

Hi,

Can you please send me that cleaning script, I'm having the same problem.

thanks

Bob

|||I have same problem at hand, Could you kindly give me the SQL 2005 uninstall script ? My server is x86, Win XP SP2 Please send to tmp74in at yahoo.com
|||I think microsoft should supply this, since so many people need it! Could you send it to me too? I've tried all of the above and it still won't install.