Does SQL Server save any metadata in registry or home folder?

I have my SQL Server installation on drive c: and the databases on another
drive. My c drive got corrupted and I am planning to restore it from an old
backup. This means drive c will not be up to date.

Is there anything that SQL Server saves in the registry or in its home
folder that changes frequently? I am hoping that nothing does which means
the old backup will be good enough.

Although I think the default databases are in the c drive, I will try to
get them back from a recent backup.

John DalbergHi

SQL Server does use the registry, but the locations of the user databases
are not held there. If you don't have any databases on the C drive then you
could just install the software again, service pack it, and then re-attach
the mdf and ldf files see http://support.microsoft.com/kb/224071/ for more
information. If you don't restore master then you will need to recreate the
logins and then reconcile the orphaned users
http://support.microsoft.com/kb/274188/. If you don't restore msdb you will
need to re-create jobs, dts packages etc.

If you do restore the C drive, then you could use the installation option
that allows you to rebuild the registry to specify new locations.

John

"John Dalberg" <nospam@.nospam.sss> wrote in message
news:20060115041931.065$4x@.newsreader.com...
>I have my SQL Server installation on drive c: and the databases on another
> drive. My c drive got corrupted and I am planning to restore it from an
> old
> backup. This means drive c will not be up to date.
> Is there anything that SQL Server saves in the registry or in its home
> folder that changes frequently? I am hoping that nothing does which means
> the old backup will be good enough.
> Although I think the default databases are in the c drive, I will try to
> get them back from a recent backup.
> John Dalberg

Does SQL Server backup its Logins

It recently occurred to me that I should see if the Server backs up it's
logins somewhere/somehow. I make backups of my databases but not the logins
(I'm working on learing that export/import now). But, does SQL Server back u
p
its logins on its own, as part of its own maintenance?
--
Michael Hocksteinsystem databases
"michael" <howlinghound@.nospam.nospam> wrote in message
news:33514BD0-10D3-495E-AD7F-961BE209C322@.microsoft.com...
> It recently occurred to me that I should see if the Server backs up it's
> logins somewhere/somehow. I make backups of my databases but not the
> logins
> (I'm working on learing that export/import now). But, does SQL Server back
> up
> its logins on its own, as part of its own maintenance?
> --
> Michael Hockstein|||Logins are stored in master database, so you should back up the master
database to get a logins backup
"michael" <howlinghound@.nospam.nospam> escribi en el mensaje
news:33514BD0-10D3-495E-AD7F-961BE209C322@.microsoft.com...
> It recently occurred to me that I should see if the Server backs up it's
> logins somewhere/somehow. I make backups of my databases but not the
> logins
> (I'm working on learing that export/import now). But, does SQL Server back
> up
> its logins on its own, as part of its own maintenance?
> --
> Michael Hockstein|||Thanks.
Michael Hockstein
"David J. Cartwright" wrote:

> system databases
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:33514BD0-10D3-495E-AD7F-961BE209C322@.microsoft.com...
>
>|||It looks like the data I want to back up is specifically in dbo.sysxlogins.
I'll have to backup the master database.
Thanks
--
Michael Hockstein
"Antonio Soto" wrote:

> Logins are stored in master database, so you should back up the master
> database to get a logins backup
>
> "michael" <howlinghound@.nospam.nospam> escribió en el mensaje
> news:33514BD0-10D3-495E-AD7F-961BE209C322@.microsoft.com...
>
>

Does Report Builder support Analysis Services Databases?

The webcast on Report Builder mentioned that you can run reports that were
created in Report Designer in Report Builder. Is it possible?There are two questions in your post.
1. Does Report Builder support Analysis Services Databases?Yes, with
Analysis Services 2005. You can automatically generate the model from an
SSAS 2005 cube in the Management Studio.
2. Is it possible to open reports that were created in the Report Designer
in the Report Builder? Sort of. Some Report Designer features are not
supported in the Report Builder since the Report Designer is more advanced.
I am not aware about a document that lists all of these features. The
reverse should be OK -- you shoudl be able to open Report Builder reports in
the Report Designer.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Dan Krish" <DanKrish@.discussions.microsoft.com> wrote in message
news:41DCA968-5E67-4D8B-B66A-3E9A83E5E214@.microsoft.com...
> The webcast on Report Builder mentioned that you can run reports that were
> created in Report Designer in Report Builder. Is it possible?|||Hi,
Thanks for the info.
I am not able to open a Report Builder report iN Report Designer. When I go
to the preview screen of the report (.rdl), it throws an error that it cannot
find the Model. I guess we need to change the rdl definition to open it
properly. Do you have any idea how to correct the information and open Report
Builder report in Report Designer.
"Teo Lachev [MVP]" wrote:
> There are two questions in your post.
> 1. Does Report Builder support Analysis Services Databases?Yes, with
> Analysis Services 2005. You can automatically generate the model from an
> SSAS 2005 cube in the Management Studio.
> 2. Is it possible to open reports that were created in the Report Designer
> in the Report Builder? Sort of. Some Report Designer features are not
> supported in the Report Builder since the Report Designer is more advanced.
> I am not aware about a document that lists all of these features. The
> reverse should be OK -- you shoudl be able to open Report Builder reports in
> the Report Designer.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "Dan Krish" <DanKrish@.discussions.microsoft.com> wrote in message
> news:41DCA968-5E67-4D8B-B66A-3E9A83E5E214@.microsoft.com...
> > The webcast on Report Builder mentioned that you can run reports that were
> > created in Report Designer in Report Builder. Is it possible?
>
>|||1. Save the ad hoc report as a file.
2. Open it in your RS project.You shoudl be able to see its layout but not
to preview it.
3. Add a data source based on the Report Server Model. extension with a
connection string, such as
server=http://localhost/reportserver;datasource=/<Folder>/<your model name>
4. Link the report dataset to that data source.
Now you should be able to preview the report.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Dan Krish" <DanKrish@.discussions.microsoft.com> wrote in message
news:5D591912-0FD8-4812-A44F-8CB3FE312C84@.microsoft.com...
> Hi,
> Thanks for the info.
> I am not able to open a Report Builder report iN Report Designer. When I
> go
> to the preview screen of the report (.rdl), it throws an error that it
> cannot
> find the Model. I guess we need to change the rdl definition to open it
> properly. Do you have any idea how to correct the information and open
> Report
> Builder report in Report Designer.
>
> "Teo Lachev [MVP]" wrote:
>> There are two questions in your post.
>> 1. Does Report Builder support Analysis Services Databases?Yes, with
>> Analysis Services 2005. You can automatically generate the model from an
>> SSAS 2005 cube in the Management Studio.
>> 2. Is it possible to open reports that were created in the Report
>> Designer
>> in the Report Builder? Sort of. Some Report Designer features are not
>> supported in the Report Builder since the Report Designer is more
>> advanced.
>> I am not aware about a document that lists all of these features. The
>> reverse should be OK -- you shoudl be able to open Report Builder reports
>> in
>> the Report Designer.
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "Dan Krish" <DanKrish@.discussions.microsoft.com> wrote in message
>> news:41DCA968-5E67-4D8B-B66A-3E9A83E5E214@.microsoft.com...
>> > The webcast on Report Builder mentioned that you can run reports that
>> > were
>> > created in Report Designer in Report Builder. Is it possible?
>>|||You need to create a data source that uses the Report Server Model extension
with a connection string sth like:
server=http://localhost/reportserver;datasource=/<Folder where model is
located>/<model name>
Next, you need to bind the report dataset to that data source.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Dan Krish" <DanKrish@.discussions.microsoft.com> wrote in message
news:5D591912-0FD8-4812-A44F-8CB3FE312C84@.microsoft.com...
> Hi,
> Thanks for the info.
> I am not able to open a Report Builder report iN Report Designer. When I
> go
> to the preview screen of the report (.rdl), it throws an error that it
> cannot
> find the Model. I guess we need to change the rdl definition to open it
> properly. Do you have any idea how to correct the information and open
> Report
> Builder report in Report Designer.
>
> "Teo Lachev [MVP]" wrote:
>> There are two questions in your post.
>> 1. Does Report Builder support Analysis Services Databases?Yes, with
>> Analysis Services 2005. You can automatically generate the model from an
>> SSAS 2005 cube in the Management Studio.
>> 2. Is it possible to open reports that were created in the Report
>> Designer
>> in the Report Builder? Sort of. Some Report Designer features are not
>> supported in the Report Builder since the Report Designer is more
>> advanced.
>> I am not aware about a document that lists all of these features. The
>> reverse should be OK -- you shoudl be able to open Report Builder reports
>> in
>> the Report Designer.
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "Dan Krish" <DanKrish@.discussions.microsoft.com> wrote in message
>> news:41DCA968-5E67-4D8B-B66A-3E9A83E5E214@.microsoft.com...
>> > The webcast on Report Builder mentioned that you can run reports that
>> > were
>> > created in Report Designer in Report Builder. Is it possible?
>>|||Hi
> 1. Does Report Builder support Analysis Services Databases?Yes, with
> Analysis Services 2005. You can automatically generate the model from an
> SSAS 2005 cube in the Management Studio.
Where is that option located?
Thanks,
Andrés
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:e3zEGJs5FHA.2776@.TK2MSFTNGP15.phx.gbl...
> There are two questions in your post.
> 1. Does Report Builder support Analysis Services Databases?Yes, with
> Analysis Services 2005. You can automatically generate the model from an
> SSAS 2005 cube in the Management Studio.
> 2. Is it possible to open reports that were created in the Report
> Designer in the Report Builder? Sort of. Some Report Designer features are
> not supported in the Report Builder since the Report Designer is more
> advanced. I am not aware about a document that lists all of these
> features. The reverse should be OK -- you shoudl be able to open Report
> Builder reports in the Report Designer.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "Dan Krish" <DanKrish@.discussions.microsoft.com> wrote in message
> news:41DCA968-5E67-4D8B-B66A-3E9A83E5E214@.microsoft.com...
>> The webcast on Report Builder mentioned that you can run reports that
>> were
>> created in Report Designer in Report Builder. Is it possible?
>|||In the SQL Server Management Studio, connect to your RS instance. Then,
right-click on a data source that points to SSAS 2005 and select Generate
Model.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Andres" <andres.fontan@.alcuadrado.com> wrote in message
news:u1pSfXW8FHA.3504@.TK2MSFTNGP11.phx.gbl...
> Hi
>> 1. Does Report Builder support Analysis Services Databases?Yes, with
>> Analysis Services 2005. You can automatically generate the model from an
>> SSAS 2005 cube in the Management Studio.
> Where is that option located?
> Thanks,
> Andrés
>
>
> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> news:e3zEGJs5FHA.2776@.TK2MSFTNGP15.phx.gbl...
>> There are two questions in your post.
>> 1. Does Report Builder support Analysis Services Databases?Yes, with
>> Analysis Services 2005. You can automatically generate the model from an
>> SSAS 2005 cube in the Management Studio.
>> 2. Is it possible to open reports that were created in the Report
>> Designer in the Report Builder? Sort of. Some Report Designer features
>> are not supported in the Report Builder since the Report Designer is more
>> advanced. I am not aware about a document that lists all of these
>> features. The reverse should be OK -- you shoudl be able to open Report
>> Builder reports in the Report Designer.
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "Dan Krish" <DanKrish@.discussions.microsoft.com> wrote in message
>> news:41DCA968-5E67-4D8B-B66A-3E9A83E5E214@.microsoft.com...
>> The webcast on Report Builder mentioned that you can run reports that
>> were
>> created in Report Designer in Report Builder. Is it possible?
>>
>sql

Does Replication Agent locks the database?

Hi,
Does the first synchronization(Replication Agent) while setting up merge
replication locks both the publisher and subscriber databases or the
databases can be used while the first synchronization is taking place?
If first synchronization does not lock the databases, will the changes that
have taken place at both the ends be merged later on, when synchronization
is complete?
Thanks
Anukul
Anukul,
there is no exclusive lock on the database, if that is what you are
referring to. There is a shared lock on the database as there would be on
any connection. As far as I know, there aren't exclusive locks held on the
table during the snapshot generation in merge replication. There are
page-level shared locks which'll prevent updates while the snapshot of a
particular page is being generated, but concurrent reads are compatible. In
transactional there is the option to use concurrent snapshot processing
where concurrent changes can occur during the snapshot generation, but not
so in merge or snapshot replication.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul:
I am talking about Replication Agent, which runs after snapshot agent.
Replication Agnet does the syncronization.
Please suggest.
Thanks,
Anukul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OLV3bsFGFHA.3888@.TK2MSFTNGP12.phx.gbl...
> Anukul,
> there is no exclusive lock on the database, if that is what you are
> referring to. There is a shared lock on the database as there would be on
> any connection. As far as I know, there aren't exclusive locks held on the
> table during the snapshot generation in merge replication. There are
> page-level shared locks which'll prevent updates while the snapshot of a
> particular page is being generated, but concurrent reads are compatible.
In
> transactional there is the option to use concurrent snapshot processing
> where concurrent changes can occur during the snapshot generation, but not
> so in merge or snapshot replication.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||My understanding is that the merge agent works with
batches of records and each separate batch is treated as
a transaction, but the combined synchronization process
is not held under a global transaction. In this case, an
edit to a record on a batch already processed would be
acceptable and would enter MSmerge_contents.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Does not connect with Enterprise Manger

I am not been able to connect to SQL Server with Enterprise Manger as I
increased the number of databases upto 170 .Earlier i had no problem when I
had less of databases .Moreover there is no problem in connecting through
Query Analyser.
Could any one help please ?
Regards
Musharraf SultanHi,
Are you getting any errors or any timeouts? Can you try the
enterperisemanager from the SQL server machine and see as well.
Thanks
Hari
SQL Server MVP
"NEWS" <musharrafs@.yahoo.com> wrote in message
news:eM$2fOlzGHA.4228@.TK2MSFTNGP06.phx.gbl...
>I am not been able to connect to SQL Server with Enterprise Manger as I
>increased the number of databases upto 170 .Earlier i had no problem when I
>had less of databases .Moreover there is no problem in connecting through
>Query Analyser.
> Could any one help please ?
> Regards
> Musharraf Sultan
>
>|||Thanks a lot ...
As soon as I try to connect from Enterprise Manager an Hourglass appears
and after some time if I see from Task manager
it shows NOT RESPONDING. The same thing happens on the Server as well as
from remote machines
Regards
Musharraf
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ebZ7GopzGHA.4920@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Are you getting any errors or any timeouts? Can you try the
> enterperisemanager from the SQL server machine and see as well.
> Thanks
> Hari
> SQL Server MVP
> "NEWS" <musharrafs@.yahoo.com> wrote in message
> news:eM$2fOlzGHA.4228@.TK2MSFTNGP06.phx.gbl...
>|||Hi,
See the database option Autoclose is enabled. If yes use the SP_DBOPTION to
make the Autoclose disabled for all database.
Thanks
Hari
SQL Server MVP
"NEWS" <musharrafs@.yahoo.com> wrote in message
news:ePChVYB0GHA.2072@.TK2MSFTNGP06.phx.gbl...
> Thanks a lot ...
> As soon as I try to connect from Enterprise Manager an Hourglass appears
> and after some time if I see from Task manager
> it shows NOT RESPONDING. The same thing happens on the Server as well as
> from remote machines
> Regards
> Musharraf
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ebZ7GopzGHA.4920@.TK2MSFTNGP06.phx.gbl...
>

does linked server solve performance issue??

Hi,
I've this query using four tables of 3 different databases residing on
the same server
select top 5 * from dblezen.dbo.ads ta
left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
In the near future we'll propably put the 3 databases on 3 different
physical servers. So I'll have to create linked servers, meaning I've
got to execute something like this :
select top 5 * from server1.dblezen.dbo.ads ta
left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
First question:
Can I fix that the servernames server1, server2 and server3 are logical
names (different than the computername) all refering to the same local
server?
If this is possible I guess I can adapt all my stored procedures before
effectively put the 3 databases on 3 different servers, and if
necessary, I can go back to 1 server again afterwards, without having to
change my stored procedures again.
Second question:
If it can be done, will it have consequences on the execution time of
the queries? Will there be overhead 1) because SQL server is going to
use distributed transaction instead of local transactions or 2) because
SQL server has to translate the logical server names into physical
server names while it's actually not necessary when the databases
reside on the same server.
Third question:
Is moving the 3 databases to 3 different servers and start using linked
server the obvious best option to resolve the performance problem of our
database server?
At the moment we have one IIS-server, running ASP.NET and one database
server, running SQL Server, using 3 databases.
- dbingeven is mainly used to insert new rows
- dblezen is mainly used to read rows (full text indexed)
- dbalgemeen contains general data used by the other two (user data,
parameters, statistical data, ...)
Data is continuously inserted in dbingeven and continuously copied
(after processing) to dblezen.
All 3 databases contain stored procedures refering each other all the
time (joins as in the query above as well as calling each others stored
procedures).
Fourth question:
Is it predictable the gain of performance win (on CPU, and disk access)
by spreading the data will be lost on network traffic and distributed
transactions processes, meaning our problem will not be really solved?
Thanks in Advance,
Peter Van Wilrijk.
Why don't you replicate the data back to the server that you're query is run
on, and select from there?
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:N_Vte.126388$I62.7018547@.phobos.telenet-ops.be...
> Hi,
> I've this query using four tables of 3 different databases residing on the
> same server
> select top 5 * from dblezen.dbo.ads ta
> left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
> left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> In the near future we'll propably put the 3 databases on 3 different
> physical servers. So I'll have to create linked servers, meaning I've
> got to execute something like this :
> select top 5 * from server1.dblezen.dbo.ads ta
> left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid =
> tb.usr_id
> left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
> td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> First question:
> Can I fix that the servernames server1, server2 and server3 are logical
> names (different than the computername) all refering to the same local
> server?
> If this is possible I guess I can adapt all my stored procedures before
> effectively put the 3 databases on 3 different servers, and if necessary,
> I can go back to 1 server again afterwards, without having to change my
> stored procedures again.
> Second question:
> If it can be done, will it have consequences on the execution time of the
> queries? Will there be overhead 1) because SQL server is going to use
> distributed transaction instead of local transactions or 2) because SQL
> server has to translate the logical server names into physical server
> names while it's actually not necessary when the databases
> reside on the same server.
> Third question:
> Is moving the 3 databases to 3 different servers and start using linked
> server the obvious best option to resolve the performance problem of our
> database server?
> At the moment we have one IIS-server, running ASP.NET and one database
> server, running SQL Server, using 3 databases.
> - dbingeven is mainly used to insert new rows
> - dblezen is mainly used to read rows (full text indexed)
> - dbalgemeen contains general data used by the other two (user data,
> parameters, statistical data, ...)
> Data is continuously inserted in dbingeven and continuously copied (after
> processing) to dblezen.
> All 3 databases contain stored procedures refering each other all the time
> (joins as in the query above as well as calling each others stored
> procedures).
> Fourth question:
> Is it predictable the gain of performance win (on CPU, and disk access)
> by spreading the data will be lost on network traffic and distributed
> transactions processes, meaning our problem will not be really solved?
>
> Thanks in Advance,
> Peter Van Wilrijk.
>
>
>
>
>
|||ChrisR wrote:
> Why don't you replicate the data back to the server that you're query is run
> on, and select from there?
>
Thanks, Good question?
I surely must start checking out how to implement replication ... but I
guess it will not be an option for our website, because our users, while
surfing ... read, insert and update data in all three databases. I
guess this means we should replicate continuously in two directions, so
updates, deletes and inserts on server 2 must be immediately available
on server 1 and vice versa. Can replication do that?
Kind regards,
Peter Roothans.
|||Yes. Look up Transactional Replication in BOL. You will want to use the
Immediate Updating option.
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
> ChrisR wrote:
> Thanks, Good question?
> I surely must start checking out how to implement replication ... but I
> guess it will not be an option for our website, because our users, while
> surfing ... read, insert and update data in all three databases. I guess
> this means we should replicate continuously in two directions, so updates,
> deletes and inserts on server 2 must be immediately available on server 1
> and vice versa. Can replication do that?
> Kind regards,
> Peter Roothans.
>
|||Thanks ChrisR.
I just found out you can give 1 server multiple names as follows.
sp_addlinkedserver N'SRVDBI', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBL', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBA', ' ', N'SQLOLEDB', N'SRV-WEBDB'
So, this way I can move my database to any server without the need to
adapt all code. The only thing to do than, is to let refer the logical
name to another physical server.
I've read BOL concerning Transactional Replication. I'm certainly going
to try it out, but since I'm not familiair with it and since the
document warns for loopback detection when replicating multiple related
databases, I'll start with the linked server solution.
Thanks,
Kind regards,
Peter Van Wilrijk
ChrisR wrote:
> Yes. Look up Transactional Replication in BOL. You will want to use the
> Immediate Updating option.
>
> "Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
> news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
>
>

does linked server solve performance issue??

Hi,
I've this query using four tables of 3 different databases residing on
the same server
select top 5 * from dblezen.dbo.ads ta
left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
In the near future we'll propably put the 3 databases on 3 different
physical servers. So I'll have to create linked servers, meaning I've
got to execute something like this :
select top 5 * from server1.dblezen.dbo.ads ta
left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
First question:
Can I fix that the servernames server1, server2 and server3 are logical
names (different than the computername) all refering to the same local
server?
If this is possible I guess I can adapt all my stored procedures before
effectively put the 3 databases on 3 different servers, and if
necessary, I can go back to 1 server again afterwards, without having to
change my stored procedures again.
Second question:
If it can be done, will it have consequences on the execution time of
the queries? Will there be overhead 1) because SQL server is going to
use distributed transaction instead of local transactions or 2) because
SQL server has to translate the logical server names into physical
server names while it's actually not necessary when the databases
reside on the same server.
Third question:
Is moving the 3 databases to 3 different servers and start using linked
server the obvious best option to resolve the performance problem of our
database server'
At the moment we have one IIS-server, running ASP.NET and one database
server, running SQL Server, using 3 databases.
- dbingeven is mainly used to insert new rows
- dblezen is mainly used to read rows (full text indexed)
- dbalgemeen contains general data used by the other two (user data,
parameters, statistical data, ...)
Data is continuously inserted in dbingeven and continuously copied
(after processing) to dblezen.
All 3 databases contain stored procedures refering each other all the
time (joins as in the query above as well as calling each others stored
procedures).
Fourth question:
Is it predictable the gain of performance win (on CPU, and disk access)
by spreading the data will be lost on network traffic and distributed
transactions processes, meaning our problem will not be really solved'
Thanks in Advance,
Peter Van Wilrijk.Why don't you replicate the data back to the server that you're query is run
on, and select from there?
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:N_Vte.126388$I62.7018547@.phobos.telenet-ops.be...
> Hi,
> I've this query using four tables of 3 different databases residing on the
> same server
> select top 5 * from dblezen.dbo.ads ta
> left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
> left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> In the near future we'll propably put the 3 databases on 3 different
> physical servers. So I'll have to create linked servers, meaning I've
> got to execute something like this :
> select top 5 * from server1.dblezen.dbo.ads ta
> left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid =
> tb.usr_id
> left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
> td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> First question:
> Can I fix that the servernames server1, server2 and server3 are logical
> names (different than the computername) all refering to the same local
> server?
> If this is possible I guess I can adapt all my stored procedures before
> effectively put the 3 databases on 3 different servers, and if necessary,
> I can go back to 1 server again afterwards, without having to change my
> stored procedures again.
> Second question:
> If it can be done, will it have consequences on the execution time of the
> queries? Will there be overhead 1) because SQL server is going to use
> distributed transaction instead of local transactions or 2) because SQL
> server has to translate the logical server names into physical server
> names while it's actually not necessary when the databases
> reside on the same server.
> Third question:
> Is moving the 3 databases to 3 different servers and start using linked
> server the obvious best option to resolve the performance problem of our
> database server'
> At the moment we have one IIS-server, running ASP.NET and one database
> server, running SQL Server, using 3 databases.
> - dbingeven is mainly used to insert new rows
> - dblezen is mainly used to read rows (full text indexed)
> - dbalgemeen contains general data used by the other two (user data,
> parameters, statistical data, ...)
> Data is continuously inserted in dbingeven and continuously copied (after
> processing) to dblezen.
> All 3 databases contain stored procedures refering each other all the time
> (joins as in the query above as well as calling each others stored
> procedures).
> Fourth question:
> Is it predictable the gain of performance win (on CPU, and disk access)
> by spreading the data will be lost on network traffic and distributed
> transactions processes, meaning our problem will not be really solved'
>
> Thanks in Advance,
> Peter Van Wilrijk.
>
>
>
>
>|||ChrisR wrote:
> Why don't you replicate the data back to the server that you're query is r
un
> on, and select from there?
>
Thanks, Good question?
I surely must start checking out how to implement replication ... but I
guess it will not be an option for our website, because our users, while
surfing ... read, insert and update data in all three databases. I
guess this means we should replicate continuously in two directions, so
updates, deletes and inserts on server 2 must be immediately available
on server 1 and vice versa. Can replication do that?
Kind regards,
Peter Roothans.|||Yes. Look up Transactional Replication in BOL. You will want to use the
Immediate Updating option.
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
> ChrisR wrote:
> Thanks, Good question?
> I surely must start checking out how to implement replication ... but I
> guess it will not be an option for our website, because our users, while
> surfing ... read, insert and update data in all three databases. I guess
> this means we should replicate continuously in two directions, so updates,
> deletes and inserts on server 2 must be immediately available on server 1
> and vice versa. Can replication do that?
> Kind regards,
> Peter Roothans.
>|||Thanks ChrisR.
I just found out you can give 1 server multiple names as follows.
sp_addlinkedserver N'SRVDBI', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBL', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBA', ' ', N'SQLOLEDB', N'SRV-WEBDB'
So, this way I can move my database to any server without the need to
adapt all code. The only thing to do than, is to let refer the logical
name to another physical server.
I've read BOL concerning Transactional Replication. I'm certainly going
to try it out, but since I'm not familiair with it and since the
document warns for loopback detection when replicating multiple related
databases, I'll start with the linked server solution.
Thanks,
Kind regards,
Peter Van Wilrijk
ChrisR wrote:
> Yes. Look up Transactional Replication in BOL. You will want to use the
> Immediate Updating option.
>
> "Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
> news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
>
>
>

does linked server solve performance issue??

Hi,
I've this query using four tables of 3 different databases residing on
the same server
select top 5 * from dblezen.dbo.ads ta
left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
In the near future we'll propably put the 3 databases on 3 different
physical servers. So I'll have to create linked servers, meaning I've
got to execute something like this :
select top 5 * from server1.dblezen.dbo.ads ta
left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
First question:
Can I fix that the servernames server1, server2 and server3 are logical
names (different than the computername) all refering to the same local
server?
If this is possible I guess I can adapt all my stored procedures before
effectively put the 3 databases on 3 different servers, and if
necessary, I can go back to 1 server again afterwards, without having to
change my stored procedures again.
Second question:
If it can be done, will it have consequences on the execution time of
the queries? Will there be overhead 1) because SQL server is going to
use distributed transaction instead of local transactions or 2) because
SQL server has to translate the logical server names into physical
server names while it's actually not necessary when the databases
reside on the same server.
Third question:
Is moving the 3 databases to 3 different servers and start using linked
server the obvious best option to resolve the performance problem of our
database server'
At the moment we have one IIS-server, running ASP.NET and one database
server, running SQL Server, using 3 databases.
- dbingeven is mainly used to insert new rows
- dblezen is mainly used to read rows (full text indexed)
- dbalgemeen contains general data used by the other two (user data,
parameters, statistical data, ...)
Data is continuously inserted in dbingeven and continuously copied
(after processing) to dblezen.
All 3 databases contain stored procedures refering each other all the
time (joins as in the query above as well as calling each others stored
procedures).
Fourth question:
Is it predictable the gain of performance win (on CPU, and disk access)
by spreading the data will be lost on network traffic and distributed
transactions processes, meaning our problem will not be really solved'
Thanks in Advance,
Peter Van Wilrijk.Why don't you replicate the data back to the server that you're query is run
on, and select from there?
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:N_Vte.126388$I62.7018547@.phobos.telenet-ops.be...
> Hi,
> I've this query using four tables of 3 different databases residing on the
> same server
> select top 5 * from dblezen.dbo.ads ta
> left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
> left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> In the near future we'll propably put the 3 databases on 3 different
> physical servers. So I'll have to create linked servers, meaning I've
> got to execute something like this :
> select top 5 * from server1.dblezen.dbo.ads ta
> left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid => tb.usr_id
> left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid => td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> First question:
> Can I fix that the servernames server1, server2 and server3 are logical
> names (different than the computername) all refering to the same local
> server?
> If this is possible I guess I can adapt all my stored procedures before
> effectively put the 3 databases on 3 different servers, and if necessary,
> I can go back to 1 server again afterwards, without having to change my
> stored procedures again.
> Second question:
> If it can be done, will it have consequences on the execution time of the
> queries? Will there be overhead 1) because SQL server is going to use
> distributed transaction instead of local transactions or 2) because SQL
> server has to translate the logical server names into physical server
> names while it's actually not necessary when the databases
> reside on the same server.
> Third question:
> Is moving the 3 databases to 3 different servers and start using linked
> server the obvious best option to resolve the performance problem of our
> database server'
> At the moment we have one IIS-server, running ASP.NET and one database
> server, running SQL Server, using 3 databases.
> - dbingeven is mainly used to insert new rows
> - dblezen is mainly used to read rows (full text indexed)
> - dbalgemeen contains general data used by the other two (user data,
> parameters, statistical data, ...)
> Data is continuously inserted in dbingeven and continuously copied (after
> processing) to dblezen.
> All 3 databases contain stored procedures refering each other all the time
> (joins as in the query above as well as calling each others stored
> procedures).
> Fourth question:
> Is it predictable the gain of performance win (on CPU, and disk access)
> by spreading the data will be lost on network traffic and distributed
> transactions processes, meaning our problem will not be really solved'
>
> Thanks in Advance,
> Peter Van Wilrijk.
>
>
>
>
>|||ChrisR wrote:
> Why don't you replicate the data back to the server that you're query is run
> on, and select from there?
>
Thanks, Good question?
I surely must start checking out how to implement replication ... but I
guess it will not be an option for our website, because our users, while
surfing ... read, insert and update data in all three databases. I
guess this means we should replicate continuously in two directions, so
updates, deletes and inserts on server 2 must be immediately available
on server 1 and vice versa. Can replication do that?
Kind regards,
Peter Roothans.|||Yes. Look up Transactional Replication in BOL. You will want to use the
Immediate Updating option.
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
> ChrisR wrote:
>> Why don't you replicate the data back to the server that you're query is
>> run on, and select from there?
> Thanks, Good question?
> I surely must start checking out how to implement replication ... but I
> guess it will not be an option for our website, because our users, while
> surfing ... read, insert and update data in all three databases. I guess
> this means we should replicate continuously in two directions, so updates,
> deletes and inserts on server 2 must be immediately available on server 1
> and vice versa. Can replication do that?
> Kind regards,
> Peter Roothans.
>|||Thanks ChrisR.
I just found out you can give 1 server multiple names as follows.
sp_addlinkedserver N'SRVDBI', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBL', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBA', ' ', N'SQLOLEDB', N'SRV-WEBDB'
So, this way I can move my database to any server without the need to
adapt all code. The only thing to do than, is to let refer the logical
name to another physical server.
I've read BOL concerning Transactional Replication. I'm certainly going
to try it out, but since I'm not familiair with it and since the
document warns for loopback detection when replicating multiple related
databases, I'll start with the linked server solution.
Thanks,
Kind regards,
Peter Van Wilrijk
ChrisR wrote:
> Yes. Look up Transactional Replication in BOL. You will want to use the
> Immediate Updating option.
>
> "Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
> news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
>>ChrisR wrote:
>>Why don't you replicate the data back to the server that you're query is
>>run on, and select from there?
>>
>>Thanks, Good question?
>>I surely must start checking out how to implement replication ... but I
>>guess it will not be an option for our website, because our users, while
>>surfing ... read, insert and update data in all three databases. I guess
>>this means we should replicate continuously in two directions, so updates,
>>deletes and inserts on server 2 must be immediately available on server 1
>>and vice versa. Can replication do that?
>>Kind regards,
>>Peter Roothans.
>
>

Does JOINs and VIEWs lose performance when used with more than one DataBase?

Hi,
My application consist of 2 big parts, which work together but are in some
way seperate.
In case I should store the 2 parts in different databases (but on the same
server), will I lose performance when doing a query that joins tables from
both databases? And what about views?
Any help our hints would be really appreciated.
Thanks a lot in advance,
Pieter
On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:

> Hi,
> My application consist of 2 big parts, which work together but are in some
> way seperate.
> In case I should store the 2 parts in different databases (but on the same
> server), will I lose performance when doing a query that joins tables from
> both databases? And what about views?
> Any help our hints would be really appreciated.
> Thanks a lot in advance,
> Pieter
Hello,
You will have no performance decrease. Try to always include the owner in
the object naming (dbo I hope), to ease the work of SQL Server.
What you physically loose splitting your model, is of course the DRIs, but
you can manage it with triggers.
Rudi Bruchez
MCDBA
|||ok! thanks a lot!
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:1goqk59i7hqzp$.146q0p9y2vigx$.dlg@.40tude.net. ..
> On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
>
> Hello,
> You will have no performance decrease. Try to always include the owner in
> the object naming (dbo I hope), to ease the work of SQL Server.
> What you physically loose splitting your model, is of course the DRIs, but
> you can manage it with triggers.
> --
> Rudi Bruchez
> MCDBA

Does JOINs and VIEWs lose performance when used with more than one DataBase?

Hi,
My application consist of 2 big parts, which work together but are in some
way seperate.
In case I should store the 2 parts in different databases (but on the same
server), will I lose performance when doing a query that joins tables from
both databases? And what about views?
Any help our hints would be really appreciated.
Thanks a lot in advance,
PieterOn Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:

> Hi,
> My application consist of 2 big parts, which work together but are in some
> way seperate.
> In case I should store the 2 parts in different databases (but on the same
> server), will I lose performance when doing a query that joins tables from
> both databases? And what about views?
> Any help our hints would be really appreciated.
> Thanks a lot in advance,
> Pieter
Hello,
You will have no performance decrease. Try to always include the owner in
the object naming (dbo I hope), to ease the work of SQL Server.
What you physically loose splitting your model, is of course the DRIs, but
you can manage it with triggers.
Rudi Bruchez
MCDBA|||ok! thanks a lot!
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:1goqk59i7hqzp$.146q0p9y2vigx$.dlg@.40tude.net...
> On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
>
> Hello,
> You will have no performance decrease. Try to always include the owner in
> the object naming (dbo I hope), to ease the work of SQL Server.
> What you physically loose splitting your model, is of course the DRIs, but
> you can manage it with triggers.
> --
> Rudi Bruchez
> MCDBA

Does it increase performance to use multiple datafiles for large databases

If you've large databases (above 200GB/file). Does it help to have multiple smaller files (For e.g four 50GB files)? Does it improve performance. Also if a single table (size 200gb) is alloted to a file group which has one datafile. Does it help to have such filegroup with multiple files (fore e.g 4 files of 50GB each). My thought was if SQL Server is looking for certain data (assuming the file is not very fragmented) it is better for SQL Server to load up only the specific file that corresponds to the data, instead of loading up an entire 200gb file.

Moving to engine forum|||In general, the size of the data files does not really matter that much, as SQL Server will not load an entire file. SQL Server uses an offset in a file to go to a particular page (8K chunk of data) that it needs to load.

What really matters is the placement of files on disks, and the type of disks you use. If you have a single large file that is backed up by a RAID array, it does not really matter much. However, if you use ordinary (non-RAID) disks, multiple files might help, as you can place different files on different disk. Especially, placing the non-clustered indexes on different disks than clustered indexes will help.

Also, placing tempdb on (one or more) different disks, and placing the log files on different disks will help performance.

If you need more information, could you explain the problem you are trying to solve in a litte bit more detail.

Thanks,

Marcel van der Holst
[MSFT]|||

A lot of this depends on what kind of storage you have, whether is direct attached storage or a SAN, and if its a SAN, what kind of SAN it is. Generally speaking, Microsoft recommends that you have one TempDB file per physical CPU (and dual-core CPUs count as physical CPUs here), and one data file for each physical CPU. Doing this can help with allocation bottlenecks. Some SANs (such as 3PAR) make this somewhat moot, since they spread a file among all of the physical disks. Having multiple small data files can make backups and restores easier to manage.

You want your data files on one set of disks, log files on another set of disks, and TempDB on another set of disks.

Does it increase performance to use multiple datafiles for large databases

If you've large databases (above 200GB/file). Does it help to have multiple smaller files (For e.g four 50GB files)? Does it improve performance. Also if a single table (size 200gb) is alloted to a file group which has one datafile. Does it help to have such filegroup with multiple files (fore e.g 4 files of 50GB each). My thought was if SQL Server is looking for certain data (assuming the file is not very fragmented) it is better for SQL Server to load up only the specific file that corresponds to the data, instead of loading up an entire 200gb file.

Moving to engine forum|||In general, the size of the data files does not really matter that much, as SQL Server will not load an entire file. SQL Server uses an offset in a file to go to a particular page (8K chunk of data) that it needs to load.

What really matters is the placement of files on disks, and the type of disks you use. If you have a single large file that is backed up by a RAID array, it does not really matter much. However, if you use ordinary (non-RAID) disks, multiple files might help, as you can place different files on different disk. Especially, placing the non-clustered indexes on different disks than clustered indexes will help.

Also, placing tempdb on (one or more) different disks, and placing the log files on different disks will help performance.

If you need more information, could you explain the problem you are trying to solve in a litte bit more detail.

Thanks,

Marcel van der Holst
[MSFT]|||

A lot of this depends on what kind of storage you have, whether is direct attached storage or a SAN, and if its a SAN, what kind of SAN it is. Generally speaking, Microsoft recommends that you have one TempDB file per physical CPU (and dual-core CPUs count as physical CPUs here), and one data file for each physical CPU. Doing this can help with allocation bottlenecks. Some SANs (such as 3PAR) make this somewhat moot, since they spread a file among all of the physical disks. Having multiple small data files can make backups and restores easier to manage.

You want your data files on one set of disks, log files on another set of disks, and TempDB on another set of disks.

Does Disk space affect Database performance

We have aroud 12 databases running on a live production server which is
segmented
into 3 physical drives.
The E drive is where data and logs are stored.
We have implemented a back up strategy over the past week. As a result , the
E
drive has shrunk in size.
Its a total of 135 GB but has 125 MB free.
Consequently we have noticed that various VB6 and MS Access applications are
suffering from slow performance.
But..the apps are merely querying data and bringing it back to the client.
My question is the lack of disk space affecting performance?
The lack of disk space will cause performance problems, iirc the MS
recommendation is to not exceed about 80% capacity on a disk/disk set.
Not sure what you mean by backing up causing the disk to shrink in
size? Also not a good idea to have log and data files on the same
physical drive.
ALI

Does Disk space affect Database performance

We have aroud 12 databases running on a live production server which is
segmented
into 3 physical drives.
The E drive is where data and logs are stored.
We have implemented a back up strategy over the past week. As a result , the
E
drive has shrunk in size.
Its a total of 135 GB but has 125 MB free.
Consequently we have noticed that various VB6 and MS Access applications are
suffering from slow performance.
But..the apps are merely querying data and bringing it back to the client.
My question is the lack of disk space affecting performance?The lack of disk space will cause performance problems, iirc the MS
recommendation is to not exceed about 80% capacity on a disk/disk set.
Not sure what you mean by backing up causing the disk to shrink in
size? Also not a good idea to have log and data files on the same
physical drive.
ALI

Does Disk space affect Database performance

We have aroud 12 databases running on a live production server which is
segmented
into 3 physical drives.
The E drive is where data and logs are stored.
We have implemented a back up strategy over the past week. As a result , the
E
drive has shrunk in size.
Its a total of 135 GB but has 125 MB free.
Consequently we have noticed that various VB6 and MS Access applications are
suffering from slow performance.
But..the apps are merely querying data and bringing it back to the client.
My question is the lack of disk space affecting performance?The lack of disk space will cause performance problems, iirc the MS
recommendation is to not exceed about 80% capacity on a disk/disk set.
Not sure what you mean by backing up causing the disk to shrink in
size? Also not a good idea to have log and data files on the same
physical drive.
ALI

Does DBCC SHRINKFILE on a data file cause the log to grow?

We have 60GB free of a 140GB data file in one of our databases in SQL
Server 2000 SP2. I've truncated the free space at the end, but it won't
shrink any further now. I tried running DBCC SHRINKFILE, but had to
cancel it and run a log backup when the transaction log grew to 40GB and
started threatening the free disk space. Is this expected behaviour.
If it is, can anybody recommend a way to shrink the data file without
growing the log - e.g. do I have to temporarily change the database
model to simple, or something like that?
Cheers,
MalcMalcolm
Make a long story shortly
1)Perfom BACKUP LOG file ( it removes all inactive transaction )
2)Pefrom DBCC SHRINKFILE to reduce physical size of the log file.
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Sometimes, if this fails, you have to put some transactions into the
database to roll the virtual log to the beginning of the physical log. I've
had success with:
1) Backup the log
2) Run DBCC SHRINKFILE
3) If step 2 does not work, create a temp table in the database and add 1000
rows.
4) Delete the rows and the temp table. This will create t-log entries that
will force the virtual log to roll to the frony of the physical file (See
BOL for details on t-log architecture).
5) Backup the log.
6) Run DBCC SHRINKFILE again. It should work this time. If not, repeat
from 3.
The process is documented in a Q article somewhere for SQL 7. You are not
supposed to need it for SQL 2000 but I havbe found it comes in handy.
Christian Smith
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Yes this is expected behavior. When you shrink a data file it has to
physically move all data at the end of the file towards the beginning and
each move is fully logged. I would shrink in smaller increments and backup
the log after each one to keep it in check.
Andrew J. Kelly
SQL Server MVP
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Guys,
Thanks for the advice, but I'm not trying to shrink the log file. I'm
trying to shrink the *DATA* file. However, whenever I try this, the log
file starts growing like crazy.
Please advice further,
Malc
Christian Smith wrote:

>Sometimes, if this fails, you have to put some transactions into the
>database to roll the virtual log to the beginning of the physical log. I'v
e
>had success with:
>1) Backup the log
>2) Run DBCC SHRINKFILE
>3) If step 2 does not work, create a temp table in the database and add 100
0
>rows.
>4) Delete the rows and the temp table. This will create t-log entries tha
t
>will force the virtual log to roll to the frony of the physical file (See
>BOL for details on t-log architecture).
>5) Backup the log.
>6) Run DBCC SHRINKFILE again. It should work this time. If not, repeat
>from 3.
>The process is documented in a Q article somewhere for SQL 7. You are not
>supposed to need it for SQL 2000 but I havbe found it comes in handy.
>Christian Smith
>"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
>message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
>
>
>|||Andrew J. Kelly wrote:

>Yes this is expected behavior. When you shrink a data file it has to
>physically move all data at the end of the file towards the beginning and
>each move is fully logged. I would shrink in smaller increments and backup
>the log after each one to keep it in check.
>
Andrew,
That's interesting. If I understand you correctly, when trying to
recover some of this 61,000MB of slack space, I should do the following:
DBCC SHRINKFILE (MyDb_Data, 140000)
-> Backup log
DBCC SHRINKFILE (MyDb_Data, 135000)
-> Backup log
DBCC ... etc.
rather than:
DBCC SHRINKFILE (MyDb_Data)
or
DBCC SHRINKFILE (MyDb_Data, 85000)
If I do this, you're saying it will consume less log space?
Cheers,
Malc|||yes. shrinking the data file(s) will fill up your log file. you could try
setting your recovery mode to simple before doing the shrinkfile. you'll st
ill
need some available space in your log file even with simple recovery.
Malcolm Ferguson wrote:

> Thanks for the advice, but I'm not trying to shrink the log file. I'm
> trying to shrink the *DATA* file. However, whenever I try this, the log
> file starts growing like crazy.|||What I am saying is it will allow you to control your log file by giving you
time to issue log backups in between the shrinks. That way your log file
won't grow on you. It also allows you to manage the process a bit better.
By the way you don't want to remove any where near all of your free space.
The database needs lots of free space to operate properly. The less free
space you have the more you risk fragmentating your tables when you reindex.
When you reindex a table you should ensure you have 1.2 to 1.5 times the
size of the table and indexes free and hopefully contiguous.
Andrew J. Kelly
SQL Server MVP
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:%23nlIL7j9DHA.3880@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
>
backup
> Andrew,
> That's interesting. If I understand you correctly, when trying to
> recover some of this 61,000MB of slack space, I should do the following:
> DBCC SHRINKFILE (MyDb_Data, 140000)
> -> Backup log
> DBCC SHRINKFILE (MyDb_Data, 135000)
> -> Backup log
> DBCC ... etc.
> rather than:
> DBCC SHRINKFILE (MyDb_Data)
> or
> DBCC SHRINKFILE (MyDb_Data, 85000)
> If I do this, you're saying it will consume less log space?
> Cheers,
> Malc
>|||Andrew J. Kelly wrote:

>What I am saying is it will allow you to control your log file by giving yo
u
>time to issue log backups in between the shrinks. That way your log file
>won't grow on you. It also allows you to manage the process a bit better.
>By the way you don't want to remove any where near all of your free space.
>The database needs lots of free space to operate properly. The less free
>space you have the more you risk fragmentating your tables when you reindex
.
>When you reindex a table you should ensure you have 1.2 to 1.5 times the
>size of the table and indexes free and hopefully contiguous.
>
Thanks all for you help and advice. I was able to remove 20GB using the
suggestions, which leaves plenty of slack space in the datafile, and
leaves the free disk space at a sane level.
Cheers,
Malc

Does DBCC SHRINKFILE on a data file cause the log to grow?

We have 60GB free of a 140GB data file in one of our databases in SQL
Server 2000 SP2. I've truncated the free space at the end, but it won't
shrink any further now. I tried running DBCC SHRINKFILE, but had to
cancel it and run a log backup when the transaction log grew to 40GB and
started threatening the free disk space. Is this expected behaviour.
If it is, can anybody recommend a way to shrink the data file without
growing the log - e.g. do I have to temporarily change the database
model to simple, or something like that?
Cheers,
MalcMalcolm
Make a long story shortly
1)Perfom BACKUP LOG file ( it removes all inactive transaction )
2)Pefrom DBCC SHRINKFILE to reduce physical size of the log file.
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Sometimes, if this fails, you have to put some transactions into the
database to roll the virtual log to the beginning of the physical log. I've
had success with:
1) Backup the log
2) Run DBCC SHRINKFILE
3) If step 2 does not work, create a temp table in the database and add 1000
rows.
4) Delete the rows and the temp table. This will create t-log entries that
will force the virtual log to roll to the frony of the physical file (See
BOL for details on t-log architecture).
5) Backup the log.
6) Run DBCC SHRINKFILE again. It should work this time. If not, repeat
from 3.
The process is documented in a Q article somewhere for SQL 7. You are not
supposed to need it for SQL 2000 but I havbe found it comes in handy.
Christian Smith
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Yes this is expected behavior. When you shrink a data file it has to
physically move all data at the end of the file towards the beginning and
each move is fully logged. I would shrink in smaller increments and backup
the log after each one to keep it in check.
--
Andrew J. Kelly
SQL Server MVP
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Guys,
Thanks for the advice, but I'm not trying to shrink the log file. I'm
trying to shrink the *DATA* file. However, whenever I try this, the log
file starts growing like crazy.
Please advice further,
Malc
Christian Smith wrote:
>Sometimes, if this fails, you have to put some transactions into the
>database to roll the virtual log to the beginning of the physical log. I've
>had success with:
>1) Backup the log
>2) Run DBCC SHRINKFILE
>3) If step 2 does not work, create a temp table in the database and add 1000
>rows.
>4) Delete the rows and the temp table. This will create t-log entries that
>will force the virtual log to roll to the frony of the physical file (See
>BOL for details on t-log architecture).
>5) Backup the log.
>6) Run DBCC SHRINKFILE again. It should work this time. If not, repeat
>from 3.
>The process is documented in a Q article somewhere for SQL 7. You are not
>supposed to need it for SQL 2000 but I havbe found it comes in handy.
>Christian Smith
>"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
>message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
>
>>We have 60GB free of a 140GB data file in one of our databases in SQL
>>Server 2000 SP2. I've truncated the free space at the end, but it won't
>>shrink any further now. I tried running DBCC SHRINKFILE, but had to
>>cancel it and run a log backup when the transaction log grew to 40GB and
>>started threatening the free disk space. Is this expected behaviour.
>>If it is, can anybody recommend a way to shrink the data file without
>>growing the log - e.g. do I have to temporarily change the database
>>model to simple, or something like that?
>>Cheers,
>>Malc
>>
>
>|||Andrew J. Kelly wrote:
>Yes this is expected behavior. When you shrink a data file it has to
>physically move all data at the end of the file towards the beginning and
>each move is fully logged. I would shrink in smaller increments and backup
>the log after each one to keep it in check.
>
Andrew,
That's interesting. If I understand you correctly, when trying to
recover some of this 61,000MB of slack space, I should do the following:
DBCC SHRINKFILE (MyDb_Data, 140000)
-> Backup log
DBCC SHRINKFILE (MyDb_Data, 135000)
-> Backup log
DBCC ... etc.
rather than:
DBCC SHRINKFILE (MyDb_Data)
or
DBCC SHRINKFILE (MyDb_Data, 85000)
If I do this, you're saying it will consume less log space?
Cheers,
Malc|||yes. shrinking the data file(s) will fill up your log file. you could try
setting your recovery mode to simple before doing the shrinkfile. you'll still
need some available space in your log file even with simple recovery.
Malcolm Ferguson wrote:
> Thanks for the advice, but I'm not trying to shrink the log file. I'm
> trying to shrink the *DATA* file. However, whenever I try this, the log
> file starts growing like crazy.|||What I am saying is it will allow you to control your log file by giving you
time to issue log backups in between the shrinks. That way your log file
won't grow on you. It also allows you to manage the process a bit better.
By the way you don't want to remove any where near all of your free space.
The database needs lots of free space to operate properly. The less free
space you have the more you risk fragmentating your tables when you reindex.
When you reindex a table you should ensure you have 1.2 to 1.5 times the
size of the table and indexes free and hopefully contiguous.
--
Andrew J. Kelly
SQL Server MVP
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:%23nlIL7j9DHA.3880@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
> >Yes this is expected behavior. When you shrink a data file it has to
> >physically move all data at the end of the file towards the beginning and
> >each move is fully logged. I would shrink in smaller increments and
backup
> >the log after each one to keep it in check.
> >
> >
> Andrew,
> That's interesting. If I understand you correctly, when trying to
> recover some of this 61,000MB of slack space, I should do the following:
> DBCC SHRINKFILE (MyDb_Data, 140000)
> -> Backup log
> DBCC SHRINKFILE (MyDb_Data, 135000)
> -> Backup log
> DBCC ... etc.
> rather than:
> DBCC SHRINKFILE (MyDb_Data)
> or
> DBCC SHRINKFILE (MyDb_Data, 85000)
> If I do this, you're saying it will consume less log space?
> Cheers,
> Malc
>|||Andrew J. Kelly wrote:
>What I am saying is it will allow you to control your log file by giving you
>time to issue log backups in between the shrinks. That way your log file
>won't grow on you. It also allows you to manage the process a bit better.
>By the way you don't want to remove any where near all of your free space.
>The database needs lots of free space to operate properly. The less free
>space you have the more you risk fragmentating your tables when you reindex.
>When you reindex a table you should ensure you have 1.2 to 1.5 times the
>size of the table and indexes free and hopefully contiguous.
>
Thanks all for you help and advice. I was able to remove 20GB using the
suggestions, which leaves plenty of slack space in the datafile, and
leaves the free disk space at a sane level.
Cheers,
Malc

Documentation

What information should I be documenting about a server (SQL) and about the databases found on that server. I was task with this job. Please help.
Thanks
LystraNumber one item would be the 'sa' password if your job is to manage the server. Since SQL Server does not allow you to see passwords, only change
them, this would be the most critical item. Most everything else can be gleaned from looking at the existing database(s) and processes.|||I would document out your backup/recovery/tape backup/disaster recovery as completely as possible.

It's always nice to have as part of your data dicitionary what the general purpose is of each database as well. In addition, I like to keep a "server log" that describles what's on it, when patches were applied, etc. This is all more for management thought than anything else.

Were you given any details about what they're expecting? Your description is rather vague.

Documentation

I'd like to go through and document the databases I'm responsible for...it's probably a good practice and I'm sure there a several approaches to doing so.

Anybody have comments, recommendations, or possibly a nice word template that links everything up?

Thanks for the input.

AlexIf you don't want to invest into third-party tools some of which may be quite pricy, I'd start with data diagrams available FOR FREE. With this functionality you can create a modularized set of graphical documentation for every database. It may be quite tedious, but is worth while having.

documentation

hello everyone,
I have to document about four databases, has anyone got any specific format or tool for documenting a database?
Any inputs would be very helpful
regards,
Harshal.I've used this tool (http://www.sql-server-performance.com/total_sql_analyzer.asp) earlier.|||Originally posted by Satya
I've used this tool (http://www.sql-server-performance.com/total_sql_analyzer.asp) earlier.

thanks for the quick reply,
but I have to write in details about the use of the table and the logic used in the stored procs also.
I'll go thru the software, is there any specific format for documenting?

Regards,
Harshal.|||I think with the help of VISIO also you can accomplish the task.
As of I know there is no specific format as such, it depends how easy you document.|||Originally posted by Satya
As of I know there is no specific format as such, it depends how easy you document.

yeah i guess it is so.

but really, documentation is third grade torture. :mad:|||Shame is no compatible tools available to document the database, rather we should depend upon third party supplied tools.:o|||Originally posted by Satya
Shame is no compatible tools available to document the database, rather we should depend upon third party supplied tools.:o

I beg to god for an :D intellegent tool :D which would also document all my procedures and their logic :D|||You will have to wait for next version of SQL Server after Yukon .. they might add it by then :)|||Documentation, is documentation.

It is painful.

If the code's not clean, it's even more painful.

Sorry...been there, done that.

PROC NAME:

Purpose:

Inputs:

Outputs:

Process flow:

And that's just the sprocs...

Do you need to a data model as well?

What about an application process model?

And what did you do wrong to get this assignment?|||documentation is already there, basic, maybe even primitive, but is there, in diagrams. if one was too lazy to write it as the app was being developed, doing it when everything else is done is going to be a project in itself. when this is the situation, you can use something as simple as copy con from dos prompt, or as sophisticated as robohelp office or er/studio or visio. the tool does not make any difference for as long as features of the tool will adequately describe what you got.|||Originally posted by Brett Kaiser
Documentation, is documentation.

It is painful.

If the code's not clean, it's even more painful.

Sorry...been there, done that.

PROC NAME:

Purpose:

Inputs:

Outputs:

Process flow:

And that's just the sprocs...

Do you need to a data model as well?

What about an application process model?

And what did you do wrong to get this assignment?

:rolleyes:

yeah the format is jsut similar to what i am currently using.
The real thing is in the process flow:mad: at places the code is worst this part is driving me crazy.
The application process model is to be documented by another guy :D
but yeah i do have to have the data flow diagrams :(

what i do wrong?
well that is a question i am been thinking of since yesterday
:D :p|||Well, in all honesty, walking in to a piece of sh-t system with no dco, is what a hired gun is all about.

If you can figure the sh-t out, you can do anything.

And yes, I always start with a data flow digram...

Gives a very clear picture of what's going on...

It's a higher level, but it gives you everything you need to then focus on the details...

Make sure that the diagram identifies the data, a process and the output of that process

There is no need to define the process in detail, just a descriptive name for a process...

It's a good way to define a road map to attack the details...

The devil is, as they say, in the details

That will come soon enough

But until you get a picture, it's a forect through the trees kinda thing

oh, and a big MOO|||Brett does diagrams...