hI
I have to database with more than 500 tables. Is there any way to find
number of rows in each tables from system tables. I want this result to
compare another database in different server.
Going table by table is practically time consuming process.
Can any one help me
Thanks
Kalyan
Try this:
select object_name(id) as TableName, rows
from sysindexes
where indid in (0, 1)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Kalyan" <Kalyan@.discussions.microsoft.com> wrote in message
news:01CB0D73-2553-4531-B7B5-ACAD5C186466@.microsoft.com...
> hI
> I have to database with more than 500 tables. Is there any way to find
> number of rows in each tables from system tables. I want this result to
> compare another database in different server.
> Going table by table is practically time consuming process.
> Can any one help me
> Thanks
> Kalyan
|||To add to Adam's response, the row column in sysindexes can be used as an
estimated row count but won't necessarily be accurate. You can use DBCC
UPDATEUSAGE beforehand to get a more accurate row count. The only way to
get a reliable accurate count is with SELECT COUNT(*).
Hope this helps.
Dan Guzman
SQL Server MVP
"Kalyan" <Kalyan@.discussions.microsoft.com> wrote in message
news:01CB0D73-2553-4531-B7B5-ACAD5C186466@.microsoft.com...
> hI
> I have to database with more than 500 tables. Is there any way to find
> number of rows in each tables from system tables. I want this result to
> compare another database in different server.
> Going table by table is practically time consuming process.
> Can any one help me
> Thanks
> Kalyan
|||Try this:
exec sp_msforeachtable "sp_spaceused '?' "
Tunji O
"Kalyan" <Kalyan@.discussions.microsoft.com> wrote in message news:01CB0D73-2553-4531-B7B5-ACAD5C186466@.microsoft.com...
hI
I have to database with more than 500 tables. Is there any way to find
number of rows in each tables from system tables. I want this result to
compare another database in different server.
Going table by table is practically time consuming process.
Can any one help me
Thanks
Kalyan
sql
Showing posts with label hii. Show all posts
Showing posts with label hii. Show all posts
Sunday, March 25, 2012
database size and raid configuration
Hi
I need to provide following information:
1. Size of all the current databases on all sql server (2000 and 2005). Is
these a query I can use to get this information.
2. Database size requirement for next three years.
3. Backup space requirements (I know which database require simple and which
transactional log database backups).
4. Test database space requirements (I know which databases require test
database).
5. New SQL server reporting services (Server space requirement), I know
which databases require a reporting server.
6. Recommendation for RAID for live and test databases including logging.
Thanks
ontario, canada
When i select size of files using sql server using
"select name,filename,size from sysaltfiles" I get size of files as
File one size: file1.mdf = 4976
File two size: file2.ldf = 2504
File three size file3.mdf = 1360
File four size file4.ldf = 13408
When I see the size of files in the disk using windows explorer I get
different size
File one size: 39804 kb
File two size: 20032 kb
File three size:10,880 kb
File four size: 107,264 KB
Why is that difference in file sizes?
ontario, canada
"db" wrote:
> Hi
> I need to provide following information:
> 1. Size of all the current databases on all sql server (2000 and 2005). Is
> these a query I can use to get this information.
> 2. Database size requirement for next three years.
> 3. Backup space requirements (I know which database require simple and which
> transactional log database backups).
> 4. Test database space requirements (I know which databases require test
> database).
> 5. New SQL server reporting services (Server space requirement), I know
> which databases require a reporting server.
> 6. Recommendation for RAID for live and test databases including logging.
> Thanks
> --
> ontario, canada
|||Thanks Tibor.
1. I am using sysaltfiles and sp_databases to get the Size of all the
current databases on all sql server (2000 and 2005). Looks like it works.
2. Database size requirement for next three years. For last 1.5 years size
of databases have increased by 50%. What do you think i should project for
next three years assuming no new applications?
3. Can I use a sql script to find the size of all backup files (.bak) for
the databases on the servers? If yes what?
4. Test database space requirements (I know which databases require test
database). What is the ideal size?
5. We will have new SQL server reporting server. How should i decide size
of the reporting server?
6. Recommendation for RAID for live and test databases. I would like to go
with maximum performance... ?
ontario, canada
"Tibor Karaszi" wrote:
> The unit for sysaltfiles is in pages (one page is 8KB).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:BCE5FEFC-57F7-44B8-B23F-10CED667E7FB@.microsoft.com...
>
|||2) I would plan on 50-75% growth per year based on your very limited
information.
3) I would use vbscript to scan directories and gather backup size
information. If you have never cleaned out msdb, you can find sizes for
backups there in one of the backupset... tables. See BOL for backupset and
it's related tables to get details.
4) We cannot guide you in this area without a good deal more information.
5) Again, need much more information.
6) Maximum performance would probably be RAID10, with lots of 15Krpm
spindles. You could perhaps get better read performance with RAID5, but
update/insert/delete performance will suffer. There is a LOT more to
disk/file configuration, btw!
BTW, I strongly recommend you hire an expert for a day or three to assist
you in your project. LOTS of ways to go astray here, and LOTS of variables
come into play.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:CDA4EAF9-424E-4E0F-8164-EBE289ECC407@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor.
> 1. I am using sysaltfiles and sp_databases to get the Size of all the
> current databases on all sql server (2000 and 2005). Looks like it works.
> 2. Database size requirement for next three years. For last 1.5 years size
> of databases have increased by 50%. What do you think i should project for
> next three years assuming no new applications?
> 3. Can I use a sql script to find the size of all backup files (.bak) for
> the databases on the servers? If yes what?
> 4. Test database space requirements (I know which databases require test
> database). What is the ideal size?
> 5. We will have new SQL server reporting server. How should i decide size
> of the reporting server?
> 6. Recommendation for RAID for live and test databases. I would like to go
> with maximum performance... ?
> --
> ontario, canada
>
> "Tibor Karaszi" wrote:
I need to provide following information:
1. Size of all the current databases on all sql server (2000 and 2005). Is
these a query I can use to get this information.
2. Database size requirement for next three years.
3. Backup space requirements (I know which database require simple and which
transactional log database backups).
4. Test database space requirements (I know which databases require test
database).
5. New SQL server reporting services (Server space requirement), I know
which databases require a reporting server.
6. Recommendation for RAID for live and test databases including logging.
Thanks
ontario, canada
When i select size of files using sql server using
"select name,filename,size from sysaltfiles" I get size of files as
File one size: file1.mdf = 4976
File two size: file2.ldf = 2504
File three size file3.mdf = 1360
File four size file4.ldf = 13408
When I see the size of files in the disk using windows explorer I get
different size
File one size: 39804 kb
File two size: 20032 kb
File three size:10,880 kb
File four size: 107,264 KB
Why is that difference in file sizes?
ontario, canada
"db" wrote:
> Hi
> I need to provide following information:
> 1. Size of all the current databases on all sql server (2000 and 2005). Is
> these a query I can use to get this information.
> 2. Database size requirement for next three years.
> 3. Backup space requirements (I know which database require simple and which
> transactional log database backups).
> 4. Test database space requirements (I know which databases require test
> database).
> 5. New SQL server reporting services (Server space requirement), I know
> which databases require a reporting server.
> 6. Recommendation for RAID for live and test databases including logging.
> Thanks
> --
> ontario, canada
|||Thanks Tibor.
1. I am using sysaltfiles and sp_databases to get the Size of all the
current databases on all sql server (2000 and 2005). Looks like it works.
2. Database size requirement for next three years. For last 1.5 years size
of databases have increased by 50%. What do you think i should project for
next three years assuming no new applications?
3. Can I use a sql script to find the size of all backup files (.bak) for
the databases on the servers? If yes what?
4. Test database space requirements (I know which databases require test
database). What is the ideal size?
5. We will have new SQL server reporting server. How should i decide size
of the reporting server?
6. Recommendation for RAID for live and test databases. I would like to go
with maximum performance... ?
ontario, canada
"Tibor Karaszi" wrote:
> The unit for sysaltfiles is in pages (one page is 8KB).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:BCE5FEFC-57F7-44B8-B23F-10CED667E7FB@.microsoft.com...
>
|||2) I would plan on 50-75% growth per year based on your very limited
information.
3) I would use vbscript to scan directories and gather backup size
information. If you have never cleaned out msdb, you can find sizes for
backups there in one of the backupset... tables. See BOL for backupset and
it's related tables to get details.
4) We cannot guide you in this area without a good deal more information.
5) Again, need much more information.
6) Maximum performance would probably be RAID10, with lots of 15Krpm
spindles. You could perhaps get better read performance with RAID5, but
update/insert/delete performance will suffer. There is a LOT more to
disk/file configuration, btw!
BTW, I strongly recommend you hire an expert for a day or three to assist
you in your project. LOTS of ways to go astray here, and LOTS of variables
come into play.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:CDA4EAF9-424E-4E0F-8164-EBE289ECC407@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor.
> 1. I am using sysaltfiles and sp_databases to get the Size of all the
> current databases on all sql server (2000 and 2005). Looks like it works.
> 2. Database size requirement for next three years. For last 1.5 years size
> of databases have increased by 50%. What do you think i should project for
> next three years assuming no new applications?
> 3. Can I use a sql script to find the size of all backup files (.bak) for
> the databases on the servers? If yes what?
> 4. Test database space requirements (I know which databases require test
> database). What is the ideal size?
> 5. We will have new SQL server reporting server. How should i decide size
> of the reporting server?
> 6. Recommendation for RAID for live and test databases. I would like to go
> with maximum performance... ?
> --
> ontario, canada
>
> "Tibor Karaszi" wrote:
Monday, March 19, 2012
Database Search
Hi
I would like to know whether any tools are there to search in a database. Ex. i am using sql server2005 and in my db, more than 1000 tables r there.
i want to search for a perticular column. This search should be on tables, sps, functions, triggers....etc.
If anybody aware of any tool for this or any code in dotnet to develop such tool, pls let me know.
Regards
Sanjay
There may be tools .. I dont know. But you can use System tables to seach for columns .. if you want to go with Plain SQL .. ( may be create your own Tool )
System Tables ..
- Sysobjects
- Sysindex
- Syscolumns ETC...
|||
Hi
As u said, using system tables is ti possible to search in stored procedures, functions, ...etc
Regards
Sanjay
|||Hi,
This may help you, please try this out.
select*from sysobjects s
innerjoin syscolumns con s.id= c.id
where c.namelike'%user%'--and s.xtype like 'p'
Thanks
Gaurang Majithiya
Sunday, March 11, 2012
DataBase Role
Hi
I want to add my database role to a server fixed role like other logins but
I can't.
Please help me.
Thanks
Mehdi> I want to add my database role to a server fixed role like other logins
> but
> I can't.
You can add only logins to fixed server roles.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||> I want to add my database role to a server fixed role like other logins
> but
> I can't.
Logins are server-level so you can add logins to server roles. Since
database roles are only recognized only within the scope of that database,
database roles can't be added to server roles.
What are your security requirements? Perhaps you can grant the desired
permissions directly to the database role.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:46F5EB48-DBE0-43EC-8D3C-501E02F496EA@.microsoft.com...
> Hi
> I want to add my database role to a server fixed role like other logins
> but
> I can't.
> Please help me.
> Thanks
> Mehdi
I want to add my database role to a server fixed role like other logins but
I can't.
Please help me.
Thanks
Mehdi> I want to add my database role to a server fixed role like other logins
> but
> I can't.
You can add only logins to fixed server roles.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||> I want to add my database role to a server fixed role like other logins
> but
> I can't.
Logins are server-level so you can add logins to server roles. Since
database roles are only recognized only within the scope of that database,
database roles can't be added to server roles.
What are your security requirements? Perhaps you can grant the desired
permissions directly to the database role.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:46F5EB48-DBE0-43EC-8D3C-501E02F496EA@.microsoft.com...
> Hi
> I want to add my database role to a server fixed role like other logins
> but
> I can't.
> Please help me.
> Thanks
> Mehdi
Wednesday, March 7, 2012
Database Resoration Error
Hi
I am getting the below error message when I restore the database.
Error Message
==========
Could not find stored procedure 'dbo_ss.dbo.sp_MSremovedbreplication'.
Could not adjust the replication state of database 'db_sss'. The database wa
s successfully
restored, however its replication state is inderminate. See the Troubleshoot
ing Replication secion in SQL Server Books Online.
RESTORE DATABASE successfully processed 10201 pages in 38.883 secounds(2.466
MB/sec
SQL Server Edition
============
MSDE with SP3
Operating System
============
Windows 2000 ProfessionalStupid question, but did you go through the troubleshooting section, as per
the error message recommendation? If that didn't help, I recommend you post
to the replication group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Balaji Prabhu.T" <anonymous@.discussions.microsoft.com> wrote in message
news:36AF45C6-BB29-411B-A6AD-9ADF6599E830@.microsoft.com...
> Hi
> I am getting the below error message when I restore the database.
> Error Message
> ==========
> Could not find stored procedure 'dbo_ss.dbo.sp_MSremovedbreplication'.
> Could not adjust the replication state of database 'db_sss'. The database
was successfully
> restored, however its replication state is inderminate. See the
Troubleshooting Replication secion in SQL Server Books Online.
> RESTORE DATABASE successfully processed 10201 pages in 38.883
secounds(2.466MB/sec
> SQL Server Edition
> ============
> MSDE with SP3
> Operating System
> ============
> Windows 2000 Professional
>
I am getting the below error message when I restore the database.
Error Message
==========
Could not find stored procedure 'dbo_ss.dbo.sp_MSremovedbreplication'.
Could not adjust the replication state of database 'db_sss'. The database wa
s successfully
restored, however its replication state is inderminate. See the Troubleshoot
ing Replication secion in SQL Server Books Online.
RESTORE DATABASE successfully processed 10201 pages in 38.883 secounds(2.466
MB/sec
SQL Server Edition
============
MSDE with SP3
Operating System
============
Windows 2000 ProfessionalStupid question, but did you go through the troubleshooting section, as per
the error message recommendation? If that didn't help, I recommend you post
to the replication group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Balaji Prabhu.T" <anonymous@.discussions.microsoft.com> wrote in message
news:36AF45C6-BB29-411B-A6AD-9ADF6599E830@.microsoft.com...
> Hi
> I am getting the below error message when I restore the database.
> Error Message
> ==========
> Could not find stored procedure 'dbo_ss.dbo.sp_MSremovedbreplication'.
> Could not adjust the replication state of database 'db_sss'. The database
was successfully
> restored, however its replication state is inderminate. See the
Troubleshooting Replication secion in SQL Server Books Online.
> RESTORE DATABASE successfully processed 10201 pages in 38.883
secounds(2.466MB/sec
> SQL Server Edition
> ============
> MSDE with SP3
> Operating System
> ============
> Windows 2000 Professional
>
Friday, February 24, 2012
Database Recovery Model Default value
Hi
I've been using SQL Server 2000 for quite some time. Every new database I
used to add, it would set the Database Recovery option to "Simple". Now
after shifting to SQL Server 2005, this option is being set to "Full" by
default for every new database. Can someone tell me where is this property
inherited from for every new database and can be changed so that each new
database may get default value of Recovery option to "Simple"
Thanks in advance
UsmanIt is inherited from the recovery mode of you "model" database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Usman" <usman@.advcomm.net> wrote in message news:u9mhHeJVGHA.5364@.tk2msftngp13.phx.gbl...[v
bcol=seagreen]
> Hi
> I've been using SQL Server 2000 for quite some time. Every new database I
> used to add, it would set the Database Recovery option to "Simple". Now
> after shifting to SQL Server 2005, this option is being set to "Full" by
> default for every new database. Can someone tell me where is this property
> inherited from for every new database and can be changed so that each new
> database may get default value of Recovery option to "Simple"
> Thanks in advance
> Usman
>[/vbcol]
I've been using SQL Server 2000 for quite some time. Every new database I
used to add, it would set the Database Recovery option to "Simple". Now
after shifting to SQL Server 2005, this option is being set to "Full" by
default for every new database. Can someone tell me where is this property
inherited from for every new database and can be changed so that each new
database may get default value of Recovery option to "Simple"
Thanks in advance
UsmanIt is inherited from the recovery mode of you "model" database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Usman" <usman@.advcomm.net> wrote in message news:u9mhHeJVGHA.5364@.tk2msftngp13.phx.gbl...[v
bcol=seagreen]
> Hi
> I've been using SQL Server 2000 for quite some time. Every new database I
> used to add, it would set the Database Recovery option to "Simple". Now
> after shifting to SQL Server 2005, this option is being set to "Full" by
> default for every new database. Can someone tell me where is this property
> inherited from for every new database and can be changed so that each new
> database may get default value of Recovery option to "Simple"
> Thanks in advance
> Usman
>[/vbcol]
Friday, February 17, 2012
database performance management
Hi
I am using SQL server database with asp as front end. I use asp command object to call sql stored procedure. The procedure runs a while loop for say 100,000 records and based on the IF condition calls particular stored procs which process the record.
I am running this app on a p4 IBM pc with win 2000 sever and IIS on same m/c . The cpu utilisation goes upto 98-99% and the process has started running very slow of late. Is this slow processing speed a hardware/OS problem or is it due to calls for stored proc within stored proc? how can i optimise the process. Each stored proc called does have if conditions,table scans etc.
please adviseYou might post your SP for more help, might try recompiling it as well.|||I have attatched the main stored proc(fetchobktotmp_SP_March08.txt) and the proc which is called most often(sot_Sp_March08.txt.. please have a look
regds|||Both SQL & ISS on same machine will have resource crunch and performance would be affected, try to seperate them or add more physical memory to the box. And also assign more memory to SQL itself.
As suggested recompile the SP, update stats the involved tables etc etc.|||Having SQL Server as well as IIS on the same machine leads to a perrformance crunch but inorder to really identify what is the issue you would need to check, RAM & Processor speed. They play a big part.
It is important to identify if this is processor issue or RAM issue, and you would need to check through performance monitor what are the CPU cycle consumption patterns of the IIS and SQL Server.
At the SQL Server level itself, you can check using the trace files if there are any other issues. But i guess, these kind of performance testing has to be done over a period of time and then analyse the results.|||Contribution- http://www.sql-server-performance.com/q&a45.asp|||Hi
currently the machine has 512MB RAM.. we hve observed that as no of records in the table increases (say 600000 or so) thge performance degrades.. for starters we hve decided to hve a serer grade machine for SQL server and iis both.. may be we wud even seperate them ..|||I feel the server is stressed out due to both at one place and SQL is not able to exeucte queries due to less available resources, such as memory which will have major affect for any performance.
I am using SQL server database with asp as front end. I use asp command object to call sql stored procedure. The procedure runs a while loop for say 100,000 records and based on the IF condition calls particular stored procs which process the record.
I am running this app on a p4 IBM pc with win 2000 sever and IIS on same m/c . The cpu utilisation goes upto 98-99% and the process has started running very slow of late. Is this slow processing speed a hardware/OS problem or is it due to calls for stored proc within stored proc? how can i optimise the process. Each stored proc called does have if conditions,table scans etc.
please adviseYou might post your SP for more help, might try recompiling it as well.|||I have attatched the main stored proc(fetchobktotmp_SP_March08.txt) and the proc which is called most often(sot_Sp_March08.txt.. please have a look
regds|||Both SQL & ISS on same machine will have resource crunch and performance would be affected, try to seperate them or add more physical memory to the box. And also assign more memory to SQL itself.
As suggested recompile the SP, update stats the involved tables etc etc.|||Having SQL Server as well as IIS on the same machine leads to a perrformance crunch but inorder to really identify what is the issue you would need to check, RAM & Processor speed. They play a big part.
It is important to identify if this is processor issue or RAM issue, and you would need to check through performance monitor what are the CPU cycle consumption patterns of the IIS and SQL Server.
At the SQL Server level itself, you can check using the trace files if there are any other issues. But i guess, these kind of performance testing has to be done over a period of time and then analyse the results.|||Contribution- http://www.sql-server-performance.com/q&a45.asp|||Hi
currently the machine has 512MB RAM.. we hve observed that as no of records in the table increases (say 600000 or so) thge performance degrades.. for starters we hve decided to hve a serer grade machine for SQL server and iis both.. may be we wud even seperate them ..|||I feel the server is stressed out due to both at one place and SQL is not able to exeucte queries due to less available resources, such as memory which will have major affect for any performance.
Subscribe to:
Posts (Atom)