Informational Queries

Hello,
Are there any commands that would give infomation on all DBs on a SQL
server? (IE size, location of files, name, that sort of thing?) I have just
been given about twenty SQLservers and due to a person leaving I am hoping to
find ot some information about what is on them
I apprecitate the help.
jj
declare @.dbname sysname
if object_id('tempdb..#dbs')is not null drop table #dbs
select
[name]
into
#dbs
from
master..sysdatabases
while (select count(*) from #dbs)>0 begin
select top 1 @.dbname=[name] from #dbs
exec ('sp_helpdb [' + @.dbname + ']')
delete #dbs where [name]=@.dbname
end
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"John Jarrett" wrote:

> Hello,
> Are there any commands that would give infomation on all DBs on a SQL
> server? (IE size, location of files, name, that sort of thing?) I have just
> been given about twenty SQLservers and due to a person leaving I am hoping to
> find ot some information about what is on them
> I apprecitate the help.
> --
> jj
|||The system tables will provide you with the appropiate information, but....
the best thing is to query them via the predefined stored procedures, read
about the sp_help% procedures in BOL, thatll help you.
For example sp_helpdb, sp_helpfiles
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"John Jarrett" <JohnJarrett@.discussions.microsoft.com> schrieb im
Newsbeitrag news:11569D6F-1381-4E14-9F1E-C0EFB2ABA943@.microsoft.com...
> Hello,
> Are there any commands that would give infomation on all DBs on a SQL
> server? (IE size, location of files, name, that sort of thing?) I have
> just
> been given about twenty SQLservers and due to a person leaving I am hoping
> to
> find ot some information about what is on them
> I apprecitate the help.
> --
> jj
|||See sps sp_helpdb and sp_helpfile in BOL.
AMB
"John Jarrett" wrote:

> Hello,
> Are there any commands that would give infomation on all DBs on a SQL
> server? (IE size, location of files, name, that sort of thing?) I have just
> been given about twenty SQLservers and due to a person leaving I am hoping to
> find ot some information about what is on them
> I apprecitate the help.
> --
> jj
sql

Information on RAID, Table size calculation

Where can I find some info on RAID 1 and RAID 5. Can we implement both on
One machine. To do so how many harddrives are needed along with RAID
controllers(1 or 2)?
What's the best way to calculate table space/db size requirement with
varchar, bigint, image and text fields. Can I find a spreadsheet etc on the
net?
Also can some one post scripts to backup and restore (Point in Time) ? Any
good web site on backup, restore and tracing?
Thanks
BVRPlease read http://www.baarf.com/ before deploying any database on top of
RAID 5.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Uhway" <Uhway@.discussions.microsoft.com> wrote in message
news:0B3439D9-DBD6-4BEF-B4B3-27B37FFD8529@.microsoft.com...
> Where can I find some info on RAID 1 and RAID 5. Can we implement both on
> One machine. To do so how many harddrives are needed along with RAID
> controllers(1 or 2)?
> What's the best way to calculate table space/db size requirement with
> varchar, bigint, image and text fields. Can I find a spreadsheet etc on
> the
> net?
> Also can some one post scripts to backup and restore (Point in Time) ?
> Any
> good web site on backup, restore and tracing?
> Thanks
> BVR

Information on RAID, Table size calculation

Where can I find some info on RAID 1 and RAID 5. Can we implement both on
One machine. To do so how many harddrives are needed along with RAID
controllers(1 or 2)?
What's the best way to calculate table space/db size requirement with
varchar, bigint, image and text fields. Can I find a spreadsheet etc on the
net?
Also can some one post scripts to backup and restore (Point in Time) ? Any
good web site on backup, restore and tracing?
Thanks
BVRPlease read http://www.baarf.com/ before deploying any database on top of
RAID 5.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Uhway" <Uhway@.discussions.microsoft.com> wrote in message
news:0B3439D9-DBD6-4BEF-B4B3-27B37FFD8529@.microsoft.com...
> Where can I find some info on RAID 1 and RAID 5. Can we implement both on
> One machine. To do so how many harddrives are needed along with RAID
> controllers(1 or 2)?
> What's the best way to calculate table space/db size requirement with
> varchar, bigint, image and text fields. Can I find a spreadsheet etc on
> the
> net?
> Also can some one post scripts to backup and restore (Point in Time) ?
> Any
> good web site on backup, restore and tracing?
> Thanks
> BVR

Information on RAID, Table size calculation

Where can I find some info on RAID 1 and RAID 5. Can we implement both on
One machine. To do so how many harddrives are needed along with RAID
controllers(1 or 2)?
What's the best way to calculate table space/db size requirement with
varchar, bigint, image and text fields. Can I find a spreadsheet etc on the
net?
Also can some one post scripts to backup and restore (Point in Time) ? Any
good web site on backup, restore and tracing?
Thanks
BVR
Please read http://www.baarf.com/ before deploying any database on top of
RAID 5.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Uhway" <Uhway@.discussions.microsoft.com> wrote in message
news:0B3439D9-DBD6-4BEF-B4B3-27B37FFD8529@.microsoft.com...
> Where can I find some info on RAID 1 and RAID 5. Can we implement both on
> One machine. To do so how many harddrives are needed along with RAID
> controllers(1 or 2)?
> What's the best way to calculate table space/db size requirement with
> varchar, bigint, image and text fields. Can I find a spreadsheet etc on
> the
> net?
> Also can some one post scripts to backup and restore (Point in Time) ?
> Any
> good web site on backup, restore and tracing?
> Thanks
> BVR

Inequality Comparision on Dimension members

Hi,

Take foodmart sales cube as an example, I want to find out store sizes that are bigger than 35000. I have tried.

1. select filter([Store Size in SQFT].[Store Sqft].members, STRTOVALUE([Store Size in SQFT].currentmember.name) > 35000) on columns from sales

Output: An MDX expression was expected. An empty expression was specified.

2.

select filter([Store Size in SQFT].[Store Sqft].members, [Store Size in SQFT].currentmember.name > '35000')

on columns from sales

Output: lexicological comparison, not what I want.

3.

select filter([Store Size in SQFT].[Store Sqft].members, [Store Size in SQFT].currentmember > 30000) on columns from sales

Output: comparing with default measure instead.

I guess I am still having the SQL mindset. Any help is really appreciated.

Hi

you should use function Val in order to convert string property into number.

select
filter([Store Size in SQFT].[Store Sqft].members, Val([Store Size in SQFT].currentmember.name) > 35000)
on columns
from sales

sql

Indexing image - file size limit?

According to the Books Online information on full-text indexing, there
are limits on the size of a file that can be indexed in an image
column: 16MB filesize, 256 KB of filtered text. I've exceeded those
limits in my testing (with Word docs), and still appear to be able to
access information in those files with CONTAINS. Is the documentation
out of date? Are there only certain conditions under which those limits
apply? The word I'm searching for appears only at the end of the test
document, so it's not indexing only the first part of the file...
Since it seems to be a common question here, this is my @.@.version:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
And, just for clarity, I don't have any problem with SQL Server
indexing more than I had planned on, I just don't want any surprises
down the road.
Thanks for any ideas you have,
Joel
Last time I tested, when the hard limit was exceeded the remaining content
was not indexed.
So if you index a document containing more than 256k of text, and then put
the word rats at the end, and then tried to search on the word rats, you
would not get hits to this row, if the word rats did not occur in the first
256k of text.
One question for you is did these word docs contains any images? Images will
not be indexed, and can swell the document size, without pushing you over
the 256 k limit.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
<nospamforjoel@.yahoo.com> wrote in message
news:1105371120.743181.194610@.c13g2000cwb.googlegr oups.com...
> According to the Books Online information on full-text indexing, there
> are limits on the size of a file that can be indexed in an image
> column: 16MB filesize, 256 KB of filtered text. I've exceeded those
> limits in my testing (with Word docs), and still appear to be able to
> access information in those files with CONTAINS. Is the documentation
> out of date? Are there only certain conditions under which those limits
> apply? The word I'm searching for appears only at the end of the test
> document, so it's not indexing only the first part of the file...
> Since it seems to be a common question here, this is my @.@.version:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
> Edition on Windows NT 5.2 (Build 3790: )
> And, just for clarity, I don't have any problem with SQL Server
> indexing more than I had planned on, I just don't want any surprises
> down the road.
> Thanks for any ideas you have,
> Joel
>
|||No, the documents that are confusing me did not have any images. They
were just a bunch of text, pasted repeatedly. I ran them through
filtdump, to make sure they really did have more than 256K of text. The
test you describe is exactly what I did--I put words at the very end of
the document that I was sure weren't in the document before, and once
the catalog rebuilt, I searched for them, and found them.
Thanks,
Joel
|||Let me try this myself. I did try this several years ago so this may have
changed with a recent sp.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
<nospamforjoel@.yahoo.com> wrote in message
news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
> No, the documents that are confusing me did not have any images. They
> were just a bunch of text, pasted repeatedly. I ran them through
> filtdump, to make sure they really did have more than 256K of text. The
> test you describe is exactly what I did--I put words at the very end of
> the document that I was sure weren't in the document before, and once
> the catalog rebuilt, I searched for them, and found them.
> Thanks,
> Joel
>
|||Joel,
Q. Is the documentation out of date?
A. Actually, it is wrong as there is a DOC bug filed for this limited in the
BOL title "Filtering Supported File Types" - "Note For full-text indexing,
a document must be less than 16 megabytes (MB) in size and must not contain
more than 256 kilobytes (KB) of filtered text" and this limit can be
over-ridden via KB article: 308771 (Q308771) "PRB: A Full-Text Search May
Not Return Any Hits If It Fails to Index a File" at
http://support.microsoft.com/default...;en-us;308771. and the
FilterProcessMemoryQuota registry key value. However, you should be careful
in making adjustments to this registry key and incrementally increase it
based upon your server's memory and avg. file sizes.
Q. Are there only certain conditions under which those limits apply?
A. Not specific conditions, but you should ensure that you have enough disk
free space (at least always 15% free) at all times where you have your FT
Catalog folder located as temp. files are written out as needed for the
processing of large files at the same location.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<nospamforjoel@.yahoo.com> wrote in message
news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
> No, the documents that are confusing me did not have any images. They
> were just a bunch of text, pasted repeatedly. I ran them through
> filtdump, to make sure they really did have more than 256K of text. The
> test you describe is exactly what I did--I put words at the very end of
> the document that I was sure weren't in the document before, and once
> the catalog rebuilt, I searched for them, and found them.
> Thanks,
> Joel
>
|||I'm not entirely sure I'm clear. If I'm reading that article right, it
looks like there is still some point at which indexing a document will
fail due to lack of memory. However, that point cannot be determined by
examining the file size of the document. Is that accurate?
Thanks,
Joel
John Kane wrote:
> Joel,
> Q. Is the documentation out of date?
> A. Actually, it is wrong as there is a DOC bug filed for this limited
in the
> BOL title "Filtering Supported File Types" - "Note For full-text
indexing,
> a document must be less than 16 megabytes (MB) in size and must not
contain
> more than 256 kilobytes (KB) of filtered text" and this limit can be
> over-ridden via KB article: 308771 (Q308771) "PRB: A Full-Text Search
May
> Not Return Any Hits If It Fails to Index a File" at
> http://support.microsoft.com/default...;en-us;308771. and
the
> FilterProcessMemoryQuota registry key value. However, you should be
careful
> in making adjustments to this registry key and incrementally increase
it
> based upon your server's memory and avg. file sizes.
> Q. Are there only certain conditions under which those limits apply?
> A. Not specific conditions, but you should ensure that you have
enough disk
> free space (at least always 15% free) at all times where you have
your FT
> Catalog folder located as temp. files are written out as needed for
the[vbcol=seagreen]
> processing of large files at the same location.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> <nospamforjoel@.yahoo.com> wrote in message
> news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
They[vbcol=seagreen]
The[vbcol=seagreen]
end of[vbcol=seagreen]
once[vbcol=seagreen]
|||You're welcome, Joel,
Yea, the RESOLUTION section states "Unfortunately, there is no way to
calculate directly from the size of the document to be full-text indexed how
much memory the filter process needs. The memory quota only exists to
protect against badly written filters, and they do spike to large amounts if
some bogus size contains a negative number. The quota itself can be made
larger, as long as it is finite. "
While no upper limit size for documents to be FT Indexed is documented, you
can increase the amount of text to be indexed by modifying the
FilterProcessMemoryQuota registry key value and you need to test your
documents on your server to get a feel for what is the "finite" limit and
monitor the server's application event log for "Microsoft Search" source
events for very large files that fail.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<nospamforjoel@.yahoo.com> wrote in message
news:1105452953.633306.318410@.f14g2000cwb.googlegr oups.com...
> I'm not entirely sure I'm clear. If I'm reading that article right, it
> looks like there is still some point at which indexing a document will
> fail due to lack of memory. However, that point cannot be determined by
> examining the file size of the document. Is that accurate?
> Thanks,
> Joel
> John Kane wrote:
> in the
> indexing,
> contain
> May
> the
> careful
> it
> enough disk
> your FT
> the
> They
> The
> end of
> once
>
|||I just tried it again. I indexed a 32 Mg text and a 16 Mg word doc and have
confirmed that at least first 256 k of extracted text is indexed, but that
tokens at the end of the documents are not. Any textual data after this 256k
boundary appears to be ignored.
I have the same version of SQL Server as you, only I am running on Win2k.
Let me try with Win2003.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u6I6P509EHA.3376@.TK2MSFTNGP12.phx.gbl...
> Let me try this myself. I did try this several years ago so this may have
> changed with a recent sp.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> <nospamforjoel@.yahoo.com> wrote in message
> news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
>

Indexes degrade too fast

(Win2003, SQL Server 2000 SP4)

I have a database of about 5Gb of size. Some queries where taking more than 1 minute to complete execution (all of them are stored procedures). Because of that lack of performance, I call the command DBREINDEX for each table, executed the sp_updatestats system stored procedure and finally I executed the sp_recompile system stored procedure for each sp in my database.

After all this task, queries completed in a matter of a few seconds instead of minutes. Strange enough is that some hours later (about 6 hrs), after normal use (this database belong to a Client/Server information system), the problem appeared again: Queries started to take too long to complete.

I am assuming that indexes are degrading too fast so that they required another ReIndex, but I am not sure.

Any thoughts? How can I prevent this behaviour?

Thank a lot in advanced.Have a look at the execution plan before and after the re-indexing routine. This will give you the "good" plan, and the "bad" plan. If the plans are identical, then the reindexing helped (doubtful but possible on a 5Gb system). More likely, the sp_recompile bumped the plan back from the "bad" plan to the "good" plan. By now, you are wondering if I just like putting "good" and "bad" in quotes. Well, no, I am not particularly fond of it, but it does remind you that not all plans are created equal. Can these procedures take in parameters that can determine a range? If so, then the "bad" plan may be good for some ranges, and the "good" plan may be bad for the same range.|||Thanks. What I really find confusing is why the performance of the database degrade in a matter of just 6 hours or so.

Of all the customers that I have distributed the same DB design, this one is the only one that presents this problem; and the size of the database is not the issue: I have other customer that has a 15 Gb database with the same DB design, then same DBMS version but with a not-as-good hardware. However, this customer does not have the same problem.|||What does DBCC SHOWCONTIG show you on the tables in question once the slow down restarts? Do you see high fragmentation numbers and low density numbers? Then yes your indices have degraded. have you traced the 2 systems. Does the one that is experiencing the issue have more insert and updates goinng on? Is it growing at a faster rate?|||Thanks. I will check that out. Just in case: Which DBCC command allow me to defrag all the tables of a database? I am planning to run that command and later run a DBCC REINDEX command for each index of every table. I suppose that this would produce a clean database, am I right?

Thanks in advance.|||There is no command to rebuild all of the tables/indexes at once. You can create or google for scripts that will do that for you, however.

indexes

Does anyone know how to easily calculate an index? please
dont give me that site "Estimating the size of a table
with an index" because it's complicated to read much less
understand...PP,
if you want a non-analytical way, then empirically is the easiest, and often
the most accurate. Simply create the index and bulk load the table with 1000
rows then note the index sixe. Add another 1000 rows and note the size. Do
this several times then you can extrapolate to any number of rows to know
the index size.
HTH,
Paul Ibison

indexes

Does anyone know how to easily calculate an index? please
dont give me that site "Estimating the size of a table
with an index" because it's complicated to read much less
understand...PP,
if you want a non-analytical way, then empirically is the easiest, and often
the most accurate. Simply create the index and bulk load the table with 1000
rows then note the index sixe. Add another 1000 rows and note the size. Do
this several times then you can extrapolate to any number of rows to know
the index size.
HTH,
Paul Ibison

indexes

I am dealing with a database that has not had its indexes worked on in quite some time. Would the size of the db shrink if the indexes were rebuilt and re-organized? Or should the size remain the same?

Depending on the workload and volatility of the tables that the indexes are on, you probably will have quite a bit of internal and external fragmentation on your indexes. Doing an index defrag or rebuild will reduce or eliminate this fragmentation, which will reduce the size used by the indexes and thereby the size of the database somewhat.

Rebuilding the index will do a much better job than index defrag. Setting a lower fill factor when you rebuild the index will help reduce fragmentation in the future, but will make the index larger.

indexes

Does anyone know how to easily calculate an index? please
dont give me that site "Estimating the size of a table
with an index" because it's complicated to read much less
understand...
PP,
if you want a non-analytical way, then empirically is the easiest, and often
the most accurate. Simply create the index and bulk load the table with 1000
rows then note the index sixe. Add another 1000 rows and note the size. Do
this several times then you can extrapolate to any number of rows to know
the index size.
HTH,
Paul Ibison