Database stored in .ldf file

Problem:
Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
Databases are set up in reverse. Data is stored in .ldf, and log files are
stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Kno
w
how to do this)
Can I shrink the logs even though they're in .mdf named files?
Is it ok to leave them alone in the reversed naming convention state?
Can I detach the databases, create a new .mdf, restore the database(.ldf)
from backup to the new .mdf file? If so, do I need to restore the log file
also, or can I start a new one from scratch?
Thanks for the help.Name is totally irrelevant to SQL Server, as well as extension. If you feel
it is OK, you can keep
the names. One option is to backup, detach (for safety - keep the files some
where else), and with
restore use the MOVE option to specify new file names. Or possibly only deta
ch, rename files and
attach specifying new correct file names (do this from QA not EM - QA gives
you full control over
the parameters used to sp_attach_db procedure).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick A" <Rick A@.discussions.microsoft.com> wrote in message
news:326AE016-3AAE-47B0-B216-E8EA16549B6A@.microsoft.com...
> Problem:
> Contractor migrated Great Plains databases to new server (Win2000, SQL2000
).
> Databases are set up in reverse. Data is stored in .ldf, and log files ar
e
> stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (K
now
> how to do this)
> Can I shrink the logs even though they're in .mdf named files?
> Is it ok to leave them alone in the reversed naming convention state?
> Can I detach the databases, create a new .mdf, restore the database(.ldf)
> from backup to the new .mdf file? If so, do I need to restore the log fil
e
> also, or can I start a new one from scratch?
> Thanks for the help.
>
>
>
>sql

Database stored in .ldf file

Problem:
Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
Databases are set up in reverse. Data is stored in .ldf, and log files are
stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
how to do this)
Can I shrink the logs even though they're in .mdf named files?
Is it ok to leave them alone in the reversed naming convention state?
Can I detach the databases, create a new .mdf, restore the database(.ldf)
from backup to the new .mdf file? If so, do I need to restore the log file
also, or can I start a new one from scratch?
Thanks for the help.
Name is totally irrelevant to SQL Server, as well as extension. If you feel it is OK, you can keep
the names. One option is to backup, detach (for safety - keep the files somewhere else), and with
restore use the MOVE option to specify new file names. Or possibly only detach, rename files and
attach specifying new correct file names (do this from QA not EM - QA gives you full control over
the parameters used to sp_attach_db procedure).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick A" <Rick A@.discussions.microsoft.com> wrote in message
news:326AE016-3AAE-47B0-B216-E8EA16549B6A@.microsoft.com...
> Problem:
> Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
> Databases are set up in reverse. Data is stored in .ldf, and log files are
> stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
> how to do this)
> Can I shrink the logs even though they're in .mdf named files?
> Is it ok to leave them alone in the reversed naming convention state?
> Can I detach the databases, create a new .mdf, restore the database(.ldf)
> from backup to the new .mdf file? If so, do I need to restore the log file
> also, or can I start a new one from scratch?
> Thanks for the help.
>
>
>
>

Database stored in .ldf file

Problem:
Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
Databases are set up in reverse. Data is stored in .ldf, and log files are
stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
how to do this)
Can I shrink the logs even though they're in .mdf named files?
Is it ok to leave them alone in the reversed naming convention state?
Can I detach the databases, create a new .mdf, restore the database(.ldf)
from backup to the new .mdf file? If so, do I need to restore the log file
also, or can I start a new one from scratch?
Thanks for the help.Name is totally irrelevant to SQL Server, as well as extension. If you feel it is OK, you can keep
the names. One option is to backup, detach (for safety - keep the files somewhere else), and with
restore use the MOVE option to specify new file names. Or possibly only detach, rename files and
attach specifying new correct file names (do this from QA not EM - QA gives you full control over
the parameters used to sp_attach_db procedure).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick A" <Rick A@.discussions.microsoft.com> wrote in message
news:326AE016-3AAE-47B0-B216-E8EA16549B6A@.microsoft.com...
> Problem:
> Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
> Databases are set up in reverse. Data is stored in .ldf, and log files are
> stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
> how to do this)
> Can I shrink the logs even though they're in .mdf named files?
> Is it ok to leave them alone in the reversed naming convention state?
> Can I detach the databases, create a new .mdf, restore the database(.ldf)
> from backup to the new .mdf file? If so, do I need to restore the log file
> also, or can I start a new one from scratch?
> Thanks for the help.
>
>
>
>

Database statistics

I need to know the statistics of use of the databases in my sql server 2k to
make a report... How ca I do that?
When you say use I assume you mean items like:
How many users have logged on per day.
How many commands have been processed per database.
Average duration of a command
etc...
Well I hate to say it but sql server does not provide you this
automatically. Although you can do server side tracing to save trace
information to a sql server table. Then using the information saved from
your trace you can then produce some of these statistics. Here is an
article I wrote that might help:
http://www.dbazine.com/larsen7.shtml
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ricardo" <nomail@.terra.com.br> wrote in message
news:OSezecwhEHA.3264@.tk2msftngp13.phx.gbl...
> I need to know the statistics of use of the databases in my sql server 2k
to
> make a report... How ca I do that?
>
sql

Database statistics

I need to know the statistics of use of the databases in my sql server 2k to
make a report... How ca I do that?When you say use I assume you mean items like:
How many users have logged on per day.
How many commands have been processed per database.
Average duration of a command
etc...
Well I hate to say it but sql server does not provide you this
automatically. Although you can do server side tracing to save trace
information to a sql server table. Then using the information saved from
your trace you can then produce some of these statistics. Here is an
article I wrote that might help:
http://www.dbazine.com/larsen7.shtml
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ricardo" <nomail@.terra.com.br> wrote in message
news:OSezecwhEHA.3264@.tk2msftngp13.phx.gbl...
> I need to know the statistics of use of the databases in my sql server 2k
to
> make a report... How ca I do that?
>

Database statistics

I need to know the statistics of use of the databases in my sql server 2k to
make a report... How ca I do that?When you say use I assume you mean items like:
How many users have logged on per day.
How many commands have been processed per database.
Average duration of a command
etc...
Well I hate to say it but sql server does not provide you this
automatically. Although you can do server side tracing to save trace
information to a sql server table. Then using the information saved from
your trace you can then produce some of these statistics. Here is an
article I wrote that might help:
http://www.dbazine.com/larsen7.shtml
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ricardo" <nomail@.terra.com.br> wrote in message
news:OSezecwhEHA.3264@.tk2msftngp13.phx.gbl...
> I need to know the statistics of use of the databases in my sql server 2k
to
> make a report... How ca I do that?
>

Database Snapshots & Reporting

We are looking at mirroring some of our databases to a remote location
and snapshotting those databases in order to report off them.
One of Microsofts recommendations is to add a time suffix to the
snapshotname in order to identify the age of the snapshot.
Any reporting system is going to use a DSN to connect to the snapshot,
we intend to snapshot frequently in order to keep the data as fresh as
possible. Does this not mean that the DSN is going to need to change to
point to the latest snapshot database?
The only alternative I can think of is to sp_renamedb the existing
snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
the original name.
This is a superb feature and a great selling point for 2005.
Has anyone implemented this ? and if so how did you overcome this
problem.
Kind Regards & Thanks.
An update for anyone else who has this problem, we have found a
possible solution.
If your application uses DSNs
You can create a number of of file DSNs relevant to the snapshot name
e.g.
appdb_1200.dsn
appdb_1800.dsn
appdb_0000.dsn
appdb_0600.dsn
Have your application use a DSN named appdb.dsn, and after creating the
snapshot database, do an xp_cmdshell to copy the relevant file over the
top of the appdb.dsn on the application server.
This allows your application to use a consistent DSN Name, but you are
cycling the DSNs with regards to the snapshot.
You can do the same thing if you use system DSNs as these are stored in
the registry.
Check out the following page
http://www.microsoft.com/technet/scriptcenter/resources/qanda/nov04/hey1110.mspx
If you use a connection string hardcoded into the app, I guess you
could use the system views to determine the latest snapshot, or
populate a 'latest snapshot' table and build your connection string
dynamically based on that.
Hope this helps someone out there.
sqldood@.googlemail.com wrote:
> We are looking at mirroring some of our databases to a remote location
> and snapshotting those databases in order to report off them.
> One of Microsofts recommendations is to add a time suffix to the
> snapshotname in order to identify the age of the snapshot.
> Any reporting system is going to use a DSN to connect to the snapshot,
> we intend to snapshot frequently in order to keep the data as fresh as
> possible. Does this not mean that the DSN is going to need to change to
> point to the latest snapshot database?
> The only alternative I can think of is to sp_renamedb the existing
> snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
> the original name.
> This is a superb feature and a great selling point for 2005.
> Has anyone implemented this ? and if so how did you overcome this
> problem.
> Kind Regards & Thanks.

Database Snapshots & Reporting

We are looking at mirroring some of our databases to a remote location
and snapshotting those databases in order to report off them.
One of Microsofts recommendations is to add a time suffix to the
snapshotname in order to identify the age of the snapshot.
Any reporting system is going to use a DSN to connect to the snapshot,
we intend to snapshot frequently in order to keep the data as fresh as
possible. Does this not mean that the DSN is going to need to change to
point to the latest snapshot database?
The only alternative I can think of is to sp_renamedb the existing
snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
the original name.
This is a superb feature and a great selling point for 2005.
Has anyone implemented this ? and if so how did you overcome this
problem.
Kind Regards & Thanks.An update for anyone else who has this problem, we have found a
possible solution.
If your application uses DSNs
You can create a number of of file DSNs relevant to the snapshot name
e.g.
appdb_1200.dsn
appdb_1800.dsn
appdb_0000.dsn
appdb_0600.dsn
Have your application use a DSN named appdb.dsn, and after creating the
snapshot database, do an xp_cmdshell to copy the relevant file over the
top of the appdb.dsn on the application server.
This allows your application to use a consistent DSN Name, but you are
cycling the DSNs with regards to the snapshot.
You can do the same thing if you use system DSNs as these are stored in
the registry.
Check out the following page
http://www.microsoft.com/technet/scriptcenter/resources/qanda/nov04/hey1110.mspx
If you use a connection string hardcoded into the app, I guess you
could use the system views to determine the latest snapshot, or
populate a 'latest snapshot' table and build your connection string
dynamically based on that.
Hope this helps someone out there.
sqldood@.googlemail.com wrote:
> We are looking at mirroring some of our databases to a remote location
> and snapshotting those databases in order to report off them.
> One of Microsofts recommendations is to add a time suffix to the
> snapshotname in order to identify the age of the snapshot.
> Any reporting system is going to use a DSN to connect to the snapshot,
> we intend to snapshot frequently in order to keep the data as fresh as
> possible. Does this not mean that the DSN is going to need to change to
> point to the latest snapshot database?
> The only alternative I can think of is to sp_renamedb the existing
> snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
> the original name.
> This is a superb feature and a great selling point for 2005.
> Has anyone implemented this ? and if so how did you overcome this
> problem.
> Kind Regards & Thanks.

Database Snapshots & Reporting

We are looking at mirroring some of our databases to a remote location
and snapshotting those databases in order to report off them.
One of Microsofts recommendations is to add a time suffix to the
snapshotname in order to identify the age of the snapshot.
Any reporting system is going to use a DSN to connect to the snapshot,
we intend to snapshot frequently in order to keep the data as fresh as
possible. Does this not mean that the DSN is going to need to change to
point to the latest snapshot database?
The only alternative I can think of is to sp_renamedb the existing
snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
the original name.
This is a superb feature and a great selling point for 2005.
Has anyone implemented this ? and if so how did you overcome this
problem.
Kind Regards & Thanks.An update for anyone else who has this problem, we have found a
possible solution.
If your application uses DSNs
You can create a number of of file DSNs relevant to the snapshot name
e.g.
appdb_1200.dsn
appdb_1800.dsn
appdb_0000.dsn
appdb_0600.dsn
Have your application use a DSN named appdb.dsn, and after creating the
snapshot database, do an xp_cmdshell to copy the relevant file over the
top of the appdb.dsn on the application server.
This allows your application to use a consistent DSN Name, but you are
cycling the DSNs with regards to the snapshot.
You can do the same thing if you use system DSNs as these are stored in
the registry.
Check out the following page
[url]http://www.microsoft.com/technet/scriptcenter/resources/qanda/nov04/hey1110.mspx[/
url]
If you use a connection string hardcoded into the app, I guess you
could use the system views to determine the latest snapshot, or
populate a 'latest snapshot' table and build your connection string
dynamically based on that.
Hope this helps someone out there.
sqldood@.googlemail.com wrote:
> We are looking at mirroring some of our databases to a remote location
> and snapshotting those databases in order to report off them.
> One of Microsofts recommendations is to add a time suffix to the
> snapshotname in order to identify the age of the snapshot.
> Any reporting system is going to use a DSN to connect to the snapshot,
> we intend to snapshot frequently in order to keep the data as fresh as
> possible. Does this not mean that the DSN is going to need to change to
> point to the latest snapshot database?
> The only alternative I can think of is to sp_renamedb the existing
> snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
> the original name.
> This is a superb feature and a great selling point for 2005.
> Has anyone implemented this ? and if so how did you overcome this
> problem.
> Kind Regards & Thanks.sql

Database Sizing

I need to convert a hierarchical database and a Siebel
Relational database to SQL a Relational database. I
currently have two databases list below:
29,151,892 MB = hierarchical database
8,222,178 MB = Siebel Relational database
About what size of a database would this be in SQL?Hi,
Storage depends up on the way you are going to redesign when you move it to
SQL Server. If it is going to be Siebel to SQL Server direct
loading then the storage will be almost identical, but whn you create
indexes the storawill go high. So I recommend you to have atleast double the
size of
current database projecting the data growth as well.
For the Hierarchical database you might require a new relationa redesin.
This will re-use the storage and may not require more space. So probably you
can have 1.5 times of current size to hold the indexes and new data.
All these are just assumptions. To get it almost correct value you have to
do some small calculations based on the books online topic "Estimating the
Size of a Table".
Thanks
Hari
MCDBA
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2a07601c46542$92b74df0$a301280a@.phx
.gbl...
> I need to convert a hierarchical database and a Siebel
> Relational database to SQL a Relational database. I
> currently have two databases list below:
> 29,151,892 MB = hierarchical database
> 8,222,178 MB = Siebel Relational database
> About what size of a database would this be in SQL?
>

Database Sizing

I need to convert a hierarchical database and a Siebel
Relational database to SQL a Relational database. I
currently have two databases list below:
29,151,892 MB = hierarchical database
8,222,178 MB = Siebel Relational database
About what size of a database would this be in SQL?Hi,
Storage depends up on the way you are going to redesign when you move it to
SQL Server. If it is going to be Siebel to SQL Server direct
loading then the storage will be almost identical, but whn you create
indexes the storawill go high. So I recommend you to have atleast double the
size of
current database projecting the data growth as well.
For the Hierarchical database you might require a new relationa redesin.
This will re-use the storage and may not require more space. So probably you
can have 1.5 times of current size to hold the indexes and new data.
All these are just assumptions. To get it almost correct value you have to
do some small calculations based on the books online topic "Estimating the
Size of a Table".
--
Thanks
Hari
MCDBA
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2a07601c46542$92b74df0$a301280a@.phx.gbl...
> I need to convert a hierarchical database and a Siebel
> Relational database to SQL a Relational database. I
> currently have two databases list below:
> 29,151,892 MB = hierarchical database
> 8,222,178 MB = Siebel Relational database
> About what size of a database would this be in SQL?
>

Database Sizing

I need to convert a hierarchical database and a Siebel
Relational database to SQL a Relational database. I
currently have two databases list below:
29,151,892 MB = hierarchical database
8,222,178 MB = Siebel Relational database
About what size of a database would this be in SQL?
Hi,
Storage depends up on the way you are going to redesign when you move it to
SQL Server. If it is going to be Siebel to SQL Server direct
loading then the storage will be almost identical, but whn you create
indexes the storawill go high. So I recommend you to have atleast double the
size of
current database projecting the data growth as well.
For the Hierarchical database you might require a new relationa redesin.
This will re-use the storage and may not require more space. So probably you
can have 1.5 times of current size to hold the indexes and new data.
All these are just assumptions. To get it almost correct value you have to
do some small calculations based on the books online topic "Estimating the
Size of a Table".
Thanks
Hari
MCDBA
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2a07601c46542$92b74df0$a301280a@.phx.gbl...
> I need to convert a hierarchical database and a Siebel
> Relational database to SQL a Relational database. I
> currently have two databases list below:
> 29,151,892 MB = hierarchical database
> 8,222,178 MB = Siebel Relational database
> About what size of a database would this be in SQL?
>

Database Size Bloating after SP4.

Has anyone had any issues with a database doubling or tripling in size after
SP4 is installed? We have several databases that are structurely the same
however certain DB's have grown enormously in size without a large increase
in data. Whenever we try to shrink the Database it won't shrink to the
approximate size we are expecting. For example...
We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
The last one I understand.. it is where it should be.. However the first two
don't make any sense..
Can someone give me an idea.. I have tried defragging, shrinking.. etc.. Any
known problems that would cause this? Thanks for your help in advance!
You didn't say what sizes you log files are.
Have you looked at the DB Option 'trunc. log on chkpt.'? Is it on or off? If
it's off, then you log files could be growning and be the cause of your
large database sizes.
It's just a thought.
"Sam Davis" <SamDavis@.discussions.microsoft.com> wrote in message
news:CB4F5891-67A2-4535-BE1A-DAA3C0FE4FB1@.microsoft.com...
> Has anyone had any issues with a database doubling or tripling in size
> after
> SP4 is installed? We have several databases that are structurely the same
> however certain DB's have grown enormously in size without a large
> increase
> in data. Whenever we try to shrink the Database it won't shrink to the
> approximate size we are expecting. For example...
> We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
> We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
> We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
> The last one I understand.. it is where it should be.. However the first
> two
> don't make any sense..
> Can someone give me an idea.. I have tried defragging, shrinking.. etc..
> Any
> known problems that would cause this? Thanks for your help in advance!
|||Francis,
I have ran the shrinkfile however it returns a limited result. Such that I
can get the file down from 12GB to 10GB however it still seems like the
unused space that is reported isn't ever freed up..
"Francis" wrote:
[vbcol=seagreen]
> I have had SP4 running on 4 production servers and a couple of test servers
> for several months now without any of the issues that you mention. If you
> need to shrink your database and EM or the DBCC SHRINKDATABASE of DBCC
> SHRINKFILE isn't working then try this script:
> http://www.sqlservercentral.com/scri...p?scriptid=666
> I usuall find the DBCC SHRINKFILE works better than SHRINKDATABASE since I
> can control which file I want to schrink.
> Have you noticed that the database grows after doing a DBCC REINDEX? If so
> you may need extra free space, although 8-14 G seems excessive.
> "Sam Davis" wrote:
|||The log files are small 100-200 MB...
"Joe D" wrote:

> You didn't say what sizes you log files are.
> Have you looked at the DB Option 'trunc. log on chkpt.'? Is it on or off? If
> it's off, then you log files could be growning and be the cause of your
> large database sizes.
> It's just a thought.
>
> "Sam Davis" <SamDavis@.discussions.microsoft.com> wrote in message
> news:CB4F5891-67A2-4535-BE1A-DAA3C0FE4FB1@.microsoft.com...
>
>

Database Size Bloating after SP4.

Has anyone had any issues with a database doubling or tripling in size after
SP4 is installed? We have several databases that are structurely the same
however certain DB's have grown enormously in size without a large increase
in data. Whenever we try to shrink the Database it won't shrink to the
approximate size we are expecting. For example...
We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
The last one I understand.. it is where it should be.. However the first two
don't make any sense..
Can someone give me an idea.. I have tried defragging, shrinking.. etc.. Any
known problems that would cause this? Thanks for your help in advance!You didn't say what sizes you log files are.
Have you looked at the DB Option 'trunc. log on chkpt.'? Is it on or off? If
it's off, then you log files could be growning and be the cause of your
large database sizes.
It's just a thought.
"Sam Davis" <SamDavis@.discussions.microsoft.com> wrote in message
news:CB4F5891-67A2-4535-BE1A-DAA3C0FE4FB1@.microsoft.com...
> Has anyone had any issues with a database doubling or tripling in size
> after
> SP4 is installed? We have several databases that are structurely the same
> however certain DB's have grown enormously in size without a large
> increase
> in data. Whenever we try to shrink the Database it won't shrink to the
> approximate size we are expecting. For example...
> We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
> We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
> We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
> The last one I understand.. it is where it should be.. However the first
> two
> don't make any sense..
> Can someone give me an idea.. I have tried defragging, shrinking.. etc..
> Any
> known problems that would cause this? Thanks for your help in advance!|||Francis,
I have ran the shrinkfile however it returns a limited result. Such that I
can get the file down from 12GB to 10GB however it still seems like the
unused space that is reported isn't ever freed up..
"Francis" wrote:
[vbcol=seagreen]
> I have had SP4 running on 4 production servers and a couple of test server
s
> for several months now without any of the issues that you mention. If you
> need to shrink your database and EM or the DBCC SHRINKDATABASE of DBCC
> SHRINKFILE isn't working then try this script:
> http://www.sqlservercentral.com/scr...sp?scriptid=666
> I usuall find the DBCC SHRINKFILE works better than SHRINKDATABASE since I
> can control which file I want to schrink.
> Have you noticed that the database grows after doing a DBCC REINDEX? If s
o
> you may need extra free space, although 8-14 G seems excessive.
> "Sam Davis" wrote:
>|||The log files are small 100-200 MB...
"Joe D" wrote:

> You didn't say what sizes you log files are.
> Have you looked at the DB Option 'trunc. log on chkpt.'? Is it on or off?
If
> it's off, then you log files could be growning and be the cause of your
> large database sizes.
> It's just a thought.
>
> "Sam Davis" <SamDavis@.discussions.microsoft.com> wrote in message
> news:CB4F5891-67A2-4535-BE1A-DAA3C0FE4FB1@.microsoft.com...
>
>sql

Database Size Bloating after SP4.

Has anyone had any issues with a database doubling or tripling in size after
SP4 is installed? We have several databases that are structurely the same
however certain DB's have grown enormously in size without a large increase
in data. Whenever we try to shrink the Database it won't shrink to the
approximate size we are expecting. For example...
We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
The last one I understand.. it is where it should be.. However the first two
don't make any sense..
Can someone give me an idea.. I have tried defragging, shrinking.. etc.. Any
known problems that would cause this? Thanks for your help in advance!I have had SP4 running on 4 production servers and a couple of test servers
for several months now without any of the issues that you mention. If you
need to shrink your database and EM or the DBCC SHRINKDATABASE of DBCC
SHRINKFILE isn't working then try this script:
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=666
I usuall find the DBCC SHRINKFILE works better than SHRINKDATABASE since I
can control which file I want to schrink.
Have you noticed that the database grows after doing a DBCC REINDEX? If so
you may need extra free space, although 8-14 G seems excessive.
"Sam Davis" wrote:
> Has anyone had any issues with a database doubling or tripling in size after
> SP4 is installed? We have several databases that are structurely the same
> however certain DB's have grown enormously in size without a large increase
> in data. Whenever we try to shrink the Database it won't shrink to the
> approximate size we are expecting. For example...
> We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
> We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
> We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
> The last one I understand.. it is where it should be.. However the first two
> don't make any sense..
> Can someone give me an idea.. I have tried defragging, shrinking.. etc.. Any
> known problems that would cause this? Thanks for your help in advance!|||You didn't say what sizes you log files are.
Have you looked at the DB Option 'trunc. log on chkpt.'? Is it on or off? If
it's off, then you log files could be growning and be the cause of your
large database sizes.
It's just a thought.
"Sam Davis" <SamDavis@.discussions.microsoft.com> wrote in message
news:CB4F5891-67A2-4535-BE1A-DAA3C0FE4FB1@.microsoft.com...
> Has anyone had any issues with a database doubling or tripling in size
> after
> SP4 is installed? We have several databases that are structurely the same
> however certain DB's have grown enormously in size without a large
> increase
> in data. Whenever we try to shrink the Database it won't shrink to the
> approximate size we are expecting. For example...
> We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
> We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
> We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
> The last one I understand.. it is where it should be.. However the first
> two
> don't make any sense..
> Can someone give me an idea.. I have tried defragging, shrinking.. etc..
> Any
> known problems that would cause this? Thanks for your help in advance!|||Francis,
I have ran the shrinkfile however it returns a limited result. Such that I
can get the file down from 12GB to 10GB however it still seems like the
unused space that is reported isn't ever freed up..
"Francis" wrote:
> I have had SP4 running on 4 production servers and a couple of test servers
> for several months now without any of the issues that you mention. If you
> need to shrink your database and EM or the DBCC SHRINKDATABASE of DBCC
> SHRINKFILE isn't working then try this script:
> http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=666
> I usuall find the DBCC SHRINKFILE works better than SHRINKDATABASE since I
> can control which file I want to schrink.
> Have you noticed that the database grows after doing a DBCC REINDEX? If so
> you may need extra free space, although 8-14 G seems excessive.
> "Sam Davis" wrote:
> > Has anyone had any issues with a database doubling or tripling in size after
> > SP4 is installed? We have several databases that are structurely the same
> > however certain DB's have grown enormously in size without a large increase
> > in data. Whenever we try to shrink the Database it won't shrink to the
> > approximate size we are expecting. For example...
> >
> > We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
> > We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
> > We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
> >
> > The last one I understand.. it is where it should be.. However the first two
> > don't make any sense..
> >
> > Can someone give me an idea.. I have tried defragging, shrinking.. etc.. Any
> > known problems that would cause this? Thanks for your help in advance!|||The log files are small 100-200 MB...
"Joe D" wrote:
> You didn't say what sizes you log files are.
> Have you looked at the DB Option 'trunc. log on chkpt.'? Is it on or off? If
> it's off, then you log files could be growning and be the cause of your
> large database sizes.
> It's just a thought.
>
> "Sam Davis" <SamDavis@.discussions.microsoft.com> wrote in message
> news:CB4F5891-67A2-4535-BE1A-DAA3C0FE4FB1@.microsoft.com...
> > Has anyone had any issues with a database doubling or tripling in size
> > after
> > SP4 is installed? We have several databases that are structurely the same
> > however certain DB's have grown enormously in size without a large
> > increase
> > in data. Whenever we try to shrink the Database it won't shrink to the
> > approximate size we are expecting. For example...
> >
> > We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
> > We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
> > We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
> >
> > The last one I understand.. it is where it should be.. However the first
> > two
> > don't make any sense..
> >
> > Can someone give me an idea.. I have tried defragging, shrinking.. etc..
> > Any
> > known problems that would cause this? Thanks for your help in advance!
>
>

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:

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, canadaWhen 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|||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...
> 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...
> > 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
>|||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...
> 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...
>> > 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, canadasql

database size and design questions

I have a question regarding database design and size .
I am migrating from several DB2 databases to SQL server. I was going to
create different databases based on application dept or business units
(the way it has been in db2). But my application folks says, they
cannot connect to multiple database or join tables accross databases,
so have all the tables in one database.
If I do that( i hate to do it), the database size will easily be 200 -
250 GB.
1. Is having all the tables in 1 database a good idea, what are the
pros and cons ?
2. If I create this huge database, how can i do maintainance on it ? Is
there a way, I can backup quickly. My guestimate for backing up a 250
GB database is around 1-2 hrs, which is not feasible.
Any input is greatly appreciated.
Thanks
Roger1st of all, your application folks dont know what they'r talking about.
You CAN do multi-db joins, and they should be able to connect to multiple
DBs. (are they writing in VB, C++, C#, VB.NET, etc or what ?)
If they dont know how to do that, then they might want to go take a class or
something as it's pretty "101" stuff.
Multiple databases on the same server is not a bad option at all.
further, you should look at this site and learn about large Databases and
maintenance, etc:
Cheers
Greg Jackson
PDX, Oregon|||man I was so aggravated, I forgot to paste my link
http://www.microsoft.com/sql/techin...scalability.asp
GAJ|||Thanks Greg, Those guys are coding in COBOL, using ES-MTO (a
microfocus engine)- this is a mainframe conversion project. I showed
them that you can add the database name in front of the table name to
do multi database queries (am i right) . But they keep saying they
cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Thanks for link Greg...i appreciate it|||> cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Ideally, their external code would call stored procedures. Then they don't
have to know how you implement the database side. It could be one database
or 200, and you could have a job switch it back and forth between the two
architectures every other Thursday.
Let the developers write the code. This is why you have database people on
staff. :-)|||AMEN My Brother...!
Furthermore if they use ADO.NET and SQL Providers, they can do all the joins
they need.
but I'm not going to even gonna go down that road.
I like Aarons solution much better anyway.
GAJ|||"sql rookie" <anytasks@.gmail.com> wrote in message
news:1112294686.285175.58040@.o13g2000cwo.googlegroups.com...
> 2. If I create this huge database, how can i do maintainance on it ? Is
> there a way, I can backup quickly. My guestimate for backing up a 250
> GB database is around 1-2 hrs, which is not feasible.
Since no on addressed this:
Backup time should not generally be the criteria here. Recovery time should
be.
As you can do online backups, you can do backups w/o downtime.
Moreover, you can do other things to help with recovery.
Look at filegroup backups... i.e. backup only parts of the DB and recover
parts as required. (BTW, SQL 2005 Enterprise handles this in a BEAUTIFUL
manner...)
Also look at perhaps a weekly full backup and then daily differentials with
transaction log backups as required.

> Any input is greatly appreciated.
> Thanks
> Roger
>

database size and design questions

I have a question regarding database design and size .
I am migrating from several DB2 databases to SQL server. I was going to
create different databases based on application dept or business units
(the way it has been in db2). But my application folks says, they
cannot connect to multiple database or join tables accross databases,
so have all the tables in one database.
If I do that( i hate to do it), the database size will easily be 200 -
250 GB.
1. Is having all the tables in 1 database a good idea, what are the
pros and cons ?
2. If I create this huge database, how can i do maintainance on it ? Is
there a way, I can backup quickly. My guestimate for backing up a 250
GB database is around 1-2 hrs, which is not feasible.
Any input is greatly appreciated.
Thanks
Roger
1st of all, your application folks dont know what they'r talking about.
You CAN do multi-db joins, and they should be able to connect to multiple
DBs. (are they writing in VB, C++, C#, VB.NET, etc or what ?)
If they dont know how to do that, then they might want to go take a class or
something as it's pretty "101" stuff.
Multiple databases on the same server is not a bad option at all.
further, you should look at this site and learn about large Databases and
maintenance, etc:
Cheers
Greg Jackson
PDX, Oregon
|||man I was so aggravated, I forgot to paste my link
http://www.microsoft.com/sql/techinf...calability.asp
GAJ
|||Thanks Greg, Those guys are coding in COBOL, using ES-MTO (a
microfocus engine)- this is a mainframe conversion project. I showed
them that you can add the database name in front of the table name to
do multi database queries (am i right) . But they keep saying they
cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Thanks for link Greg...i appreciate it
|||> cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Ideally, their external code would call stored procedures. Then they don't
have to know how you implement the database side. It could be one database
or 200, and you could have a job switch it back and forth between the two
architectures every other Thursday.
Let the developers write the code. This is why you have database people on
staff. :-)
|||AMEN My Brother...!
Furthermore if they use ADO.NET and SQL Providers, they can do all the joins
they need.
but I'm not going to even gonna go down that road.
I like Aarons solution much better anyway.
GAJ
|||"sql rookie" <anytasks@.gmail.com> wrote in message
news:1112294686.285175.58040@.o13g2000cwo.googlegro ups.com...
> 2. If I create this huge database, how can i do maintainance on it ? Is
> there a way, I can backup quickly. My guestimate for backing up a 250
> GB database is around 1-2 hrs, which is not feasible.
Since no on addressed this:
Backup time should not generally be the criteria here. Recovery time should
be.
As you can do online backups, you can do backups w/o downtime.
Moreover, you can do other things to help with recovery.
Look at filegroup backups... i.e. backup only parts of the DB and recover
parts as required. (BTW, SQL 2005 Enterprise handles this in a BEAUTIFUL
manner...)
Also look at perhaps a weekly full backup and then daily differentials with
transaction log backups as required.

> Any input is greatly appreciated.
> Thanks
> Roger
>

database size and design questions

I have a question regarding database design and size .
I am migrating from several DB2 databases to SQL server. I was going to
create different databases based on application dept or business units
(the way it has been in db2). But my application folks says, they
cannot connect to multiple database or join tables accross databases,
so have all the tables in one database.
If I do that( i hate to do it), the database size will easily be 200 -
250 GB.
1. Is having all the tables in 1 database a good idea, what are the
pros and cons ?
2. If I create this huge database, how can i do maintainance on it ? Is
there a way, I can backup quickly. My guestimate for backing up a 250
GB database is around 1-2 hrs, which is not feasible.
Any input is greatly appreciated.
Thanks
Roger1st of all, your application folks dont know what they'r talking about.
You CAN do multi-db joins, and they should be able to connect to multiple
DBs. (are they writing in VB, C++, C#, VB.NET, etc or what ?)
If they dont know how to do that, then they might want to go take a class or
something as it's pretty "101" stuff.
Multiple databases on the same server is not a bad option at all.
further, you should look at this site and learn about large Databases and
maintenance, etc:
Cheers
Greg Jackson
PDX, Oregon|||man I was so aggravated, I forgot to paste my link
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
GAJ|||Thanks Greg, Those guys are coding in COBOL, using ES-MTO (a
microfocus engine)- this is a mainframe conversion project. I showed
them that you can add the database name in front of the table name to
do multi database queries (am i right) . But they keep saying they
cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Thanks for link Greg...i appreciate it|||> cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Ideally, their external code would call stored procedures. Then they don't
have to know how you implement the database side. It could be one database
or 200, and you could have a job switch it back and forth between the two
architectures every other Thursday.
Let the developers write the code. This is why you have database people on
staff. :-)|||AMEN My Brother...!
Furthermore if they use ADO.NET and SQL Providers, they can do all the joins
they need.
but I'm not going to even gonna go down that road.
I like Aarons solution much better anyway.
GAJ|||"sql rookie" <anytasks@.gmail.com> wrote in message
news:1112294686.285175.58040@.o13g2000cwo.googlegroups.com...
> 2. If I create this huge database, how can i do maintainance on it ? Is
> there a way, I can backup quickly. My guestimate for backing up a 250
> GB database is around 1-2 hrs, which is not feasible.
Since no on addressed this:
Backup time should not generally be the criteria here. Recovery time should
be.
As you can do online backups, you can do backups w/o downtime.
Moreover, you can do other things to help with recovery.
Look at filegroup backups... i.e. backup only parts of the DB and recover
parts as required. (BTW, SQL 2005 Enterprise handles this in a BEAUTIFUL
manner...)
Also look at perhaps a weekly full backup and then daily differentials with
transaction log backups as required.
> Any input is greatly appreciated.
> Thanks
> Roger
>