We planning to setup a database and use the Windows 2003 NLB features to
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be able
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?
See inline
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)
> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.
> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...
> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)
> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)
Showing posts with label windows. Show all posts
Showing posts with label windows. Show all posts
Wednesday, March 28, 2012
Load balancing for read-only databases
We planning to setup a database and use the Windows 2003 NLB features to
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be able
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?See inline
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)
> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.
> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...
> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)
> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be able
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?See inline
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)
> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.
> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...
> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)
> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)
Load balancing for read-only databases
We planning to setup a database and use the Windows 2003 NLB features to
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be abl
e
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?See inline
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)
> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.
> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...
> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)
> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)sql
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be abl
e
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?See inline
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)
> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.
> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...
> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)
> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)sql
Load balancing
Hi,
Is it possible to use windows NLB for Sql 2000 enterprise edition servers.
We are using 300gb database in one server and the performance is very slow.
Any how we can't get load balancing in SQL clustering, If it is possible
with NLB please let me know how to configure it.
We are planning to put the database in SAN ,so both the servers will use
central database instead of individual one.
Thanks
Murali
"Space is not available in TempDB"
<SpaceisnotavailableinTempDB@.discussions.microsoft .com> wrote in message
news:FB6ABB56-D877-47E4-8C44-B79BD593CA20@.microsoft.com...
> Hi,
> Is it possible to use windows NLB for Sql 2000 enterprise edition servers.
No.
> We are using 300gb database in one server and the performance is very
slow.
> Any how we can't get load balancing in SQL clustering, If it is possible
> with NLB please let me know how to configure it.
> We are planning to put the database in SAN ,so both the servers will use
> central database instead of individual one.
This wont' work. Each instance of SQL server needs access to its own copy
of the database files.
What you can try is log-shipping or replication from one server to another.
> Thanks
> Murali
|||Hi,
Thanks for your response .
log shipping may not suitable to our requirement.
we are using on CRM application , from the application server through odbc
it will contact the sql server.
We can't specify multiple servers in odbc connection for sqlserver even if
we use log shipping.
Please give some other resolution which will distribute the load between 2
servers.
Thanks
Murali
"Greg D. Moore (Strider)" wrote:
> "Space is not available in TempDB"
> <SpaceisnotavailableinTempDB@.discussions.microsoft .com> wrote in message
> news:FB6ABB56-D877-47E4-8C44-B79BD593CA20@.microsoft.com...
> No.
> slow.
> This wont' work. Each instance of SQL server needs access to its own copy
> of the database files.
> What you can try is log-shipping or replication from one server to another.
>
>
>
|||Buy a bigger server. NLB will split the query load between several servers,
but there is no way to coalesce the changes on the back end. Each server
instance has a unique, independant set of databases that are only accessable
through that instance. In a clustered environment, eacn instance can move
between hosts, but can only live on one host at a time. SQL scales up, not
out.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Space is not available in TempDB"
<SpaceisnotavailableinTempDB@.discussions.microsoft .com> wrote in message
news:90501899-D509-4B25-AB9F-8021E0F90350@.microsoft.com...[vbcol=seagreen]
> Hi,
> Thanks for your response .
> log shipping may not suitable to our requirement.
> we are using on CRM application , from the application server through odbc
> it will contact the sql server.
> We can't specify multiple servers in odbc connection for sqlserver even if
> we use log shipping.
> Please give some other resolution which will distribute the load between 2
> servers.
> Thanks
> Murali
> "Greg D. Moore (Strider)" wrote:
servers.[vbcol=seagreen]
possible[vbcol=seagreen]
use[vbcol=seagreen]
copy[vbcol=seagreen]
another.[vbcol=seagreen]
Is it possible to use windows NLB for Sql 2000 enterprise edition servers.
We are using 300gb database in one server and the performance is very slow.
Any how we can't get load balancing in SQL clustering, If it is possible
with NLB please let me know how to configure it.
We are planning to put the database in SAN ,so both the servers will use
central database instead of individual one.
Thanks
Murali
"Space is not available in TempDB"
<SpaceisnotavailableinTempDB@.discussions.microsoft .com> wrote in message
news:FB6ABB56-D877-47E4-8C44-B79BD593CA20@.microsoft.com...
> Hi,
> Is it possible to use windows NLB for Sql 2000 enterprise edition servers.
No.
> We are using 300gb database in one server and the performance is very
slow.
> Any how we can't get load balancing in SQL clustering, If it is possible
> with NLB please let me know how to configure it.
> We are planning to put the database in SAN ,so both the servers will use
> central database instead of individual one.
This wont' work. Each instance of SQL server needs access to its own copy
of the database files.
What you can try is log-shipping or replication from one server to another.
> Thanks
> Murali
|||Hi,
Thanks for your response .
log shipping may not suitable to our requirement.
we are using on CRM application , from the application server through odbc
it will contact the sql server.
We can't specify multiple servers in odbc connection for sqlserver even if
we use log shipping.
Please give some other resolution which will distribute the load between 2
servers.
Thanks
Murali
"Greg D. Moore (Strider)" wrote:
> "Space is not available in TempDB"
> <SpaceisnotavailableinTempDB@.discussions.microsoft .com> wrote in message
> news:FB6ABB56-D877-47E4-8C44-B79BD593CA20@.microsoft.com...
> No.
> slow.
> This wont' work. Each instance of SQL server needs access to its own copy
> of the database files.
> What you can try is log-shipping or replication from one server to another.
>
>
>
|||Buy a bigger server. NLB will split the query load between several servers,
but there is no way to coalesce the changes on the back end. Each server
instance has a unique, independant set of databases that are only accessable
through that instance. In a clustered environment, eacn instance can move
between hosts, but can only live on one host at a time. SQL scales up, not
out.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Space is not available in TempDB"
<SpaceisnotavailableinTempDB@.discussions.microsoft .com> wrote in message
news:90501899-D509-4B25-AB9F-8021E0F90350@.microsoft.com...[vbcol=seagreen]
> Hi,
> Thanks for your response .
> log shipping may not suitable to our requirement.
> we are using on CRM application , from the application server through odbc
> it will contact the sql server.
> We can't specify multiple servers in odbc connection for sqlserver even if
> we use log shipping.
> Please give some other resolution which will distribute the load between 2
> servers.
> Thanks
> Murali
> "Greg D. Moore (Strider)" wrote:
servers.[vbcol=seagreen]
possible[vbcol=seagreen]
use[vbcol=seagreen]
copy[vbcol=seagreen]
another.[vbcol=seagreen]
Load Balancing
Hi,
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
Floris
SQL Server does not support load balancing of the databases with NLB.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.itsummitseries.com/experts/fournier.htm - Cluster Summit
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message
news:OyIKCVSjFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available
> with Windows Server 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our
> sql servers?
> And i will create a replication on the background between the 2 sql
> servers so they will both serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>
sql
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
Floris
SQL Server does not support load balancing of the databases with NLB.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.itsummitseries.com/experts/fournier.htm - Cluster Summit
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message
news:OyIKCVSjFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available
> with Windows Server 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our
> sql servers?
> And i will create a replication on the background between the 2 sql
> servers so they will both serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>
sql
Load Balancing
Hi,
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
Floris
Load balancing when you own data (like a DBMS does) and you also want to modify that data is far
from trivial.
I don't think any system does that transparently (you either use shared disk, where you till only
have one copy of data or you do shared nothing where you only have a set of data on one location).
First determine what modifications are to be performed. Easiest is of you can separate read-only
applications (reports etc). If so, you can replicate (using built-in transactional replication) from
the server where you do modifications to the read-only server.
If you cannot separate read-only applications, you can look into merge replication. However, if you
modify the same data at the same time on two nodes, one will win and the other modification will be
lost. Unacceptable in many scenarios.
I suggest you read up in Books Online about Replication (there are different types of replications)
as well as "federated servers" (also known as distributed partitioned views).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available with Windows Server
> 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our sql servers?
> And i will create a replication on the background between the 2 sql servers so they will both
> serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>
|||Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMficaSjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Load balancing when you own data (like a DBMS does) and you also want to
> modify that data is far from trivial.
> I don't think any system does that transparently (you either use shared
> disk, where you till only have one copy of data or you do shared nothing
> where you only have a set of data on one location).
> First determine what modifications are to be performed. Easiest is of you
> can separate read-only applications (reports etc). If so, you can
> replicate (using built-in transactional replication) from the server where
> you do modifications to the read-only server.
> If you cannot separate read-only applications, you can look into merge
> replication. However, if you modify the same data at the same time on two
> nodes, one will win and the other modification will be lost. Unacceptable
> in many scenarios.
> I suggest you read up in Books Online about Replication (there are
> different types of replications) as well as "federated servers" (also
> known as distributed partitioned views).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Floris" <meNOSPAM@.here_my_place.nl> wrote in message
> news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
>
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
Floris
Load balancing when you own data (like a DBMS does) and you also want to modify that data is far
from trivial.
I don't think any system does that transparently (you either use shared disk, where you till only
have one copy of data or you do shared nothing where you only have a set of data on one location).
First determine what modifications are to be performed. Easiest is of you can separate read-only
applications (reports etc). If so, you can replicate (using built-in transactional replication) from
the server where you do modifications to the read-only server.
If you cannot separate read-only applications, you can look into merge replication. However, if you
modify the same data at the same time on two nodes, one will win and the other modification will be
lost. Unacceptable in many scenarios.
I suggest you read up in Books Online about Replication (there are different types of replications)
as well as "federated servers" (also known as distributed partitioned views).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available with Windows Server
> 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our sql servers?
> And i will create a replication on the background between the 2 sql servers so they will both
> serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>
|||Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMficaSjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Load balancing when you own data (like a DBMS does) and you also want to
> modify that data is far from trivial.
> I don't think any system does that transparently (you either use shared
> disk, where you till only have one copy of data or you do shared nothing
> where you only have a set of data on one location).
> First determine what modifications are to be performed. Easiest is of you
> can separate read-only applications (reports etc). If so, you can
> replicate (using built-in transactional replication) from the server where
> you do modifications to the read-only server.
> If you cannot separate read-only applications, you can look into merge
> replication. However, if you modify the same data at the same time on two
> nodes, one will win and the other modification will be lost. Unacceptable
> in many scenarios.
> I suggest you read up in Books Online about Replication (there are
> different types of replications) as well as "federated servers" (also
> known as distributed partitioned views).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Floris" <meNOSPAM@.here_my_place.nl> wrote in message
> news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
>
Load Balancing
Hi,
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
FlorisLoad balancing when you own data (like a DBMS does) and you also want to modify that data is far
from trivial.
I don't think any system does that transparently (you either use shared disk, where you till only
have one copy of data or you do shared nothing where you only have a set of data on one location).
First determine what modifications are to be performed. Easiest is of you can separate read-only
applications (reports etc). If so, you can replicate (using built-in transactional replication) from
the server where you do modifications to the read-only server.
If you cannot separate read-only applications, you can look into merge replication. However, if you
modify the same data at the same time on two nodes, one will win and the other modification will be
lost. Unacceptable in many scenarios.
I suggest you read up in Books Online about Replication (there are different types of replications)
as well as "federated servers" (also known as distributed partitioned views).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available with Windows Server
> 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our sql servers?
> And i will create a replication on the background between the 2 sql servers so they will both
> serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>|||Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMficaSjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Load balancing when you own data (like a DBMS does) and you also want to
> modify that data is far from trivial.
> I don't think any system does that transparently (you either use shared
> disk, where you till only have one copy of data or you do shared nothing
> where you only have a set of data on one location).
> First determine what modifications are to be performed. Easiest is of you
> can separate read-only applications (reports etc). If so, you can
> replicate (using built-in transactional replication) from the server where
> you do modifications to the read-only server.
> If you cannot separate read-only applications, you can look into merge
> replication. However, if you modify the same data at the same time on two
> nodes, one will win and the other modification will be lost. Unacceptable
> in many scenarios.
> I suggest you read up in Books Online about Replication (there are
> different types of replications) as well as "federated servers" (also
> known as distributed partitioned views).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Floris" <meNOSPAM@.here_my_place.nl> wrote in message
> news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> In windows 2003 server you have NLB (Network Load Balancing).
>> Can i use this to load balance my traffic to our sql server?
>> I know you also have "Microsoft Cluster Service" but that's only
>> available with Windows Server 2003 Enterprise Edition.
>> So is it possible to load balance my traffic from our webservers to our
>> sql servers?
>> And i will create a replication on the background between the 2 sql
>> servers so they will both serve the same data.
>> I hope someone has an answer for me.
>> Thanks in advance.
>> Floris
>sql
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
FlorisLoad balancing when you own data (like a DBMS does) and you also want to modify that data is far
from trivial.
I don't think any system does that transparently (you either use shared disk, where you till only
have one copy of data or you do shared nothing where you only have a set of data on one location).
First determine what modifications are to be performed. Easiest is of you can separate read-only
applications (reports etc). If so, you can replicate (using built-in transactional replication) from
the server where you do modifications to the read-only server.
If you cannot separate read-only applications, you can look into merge replication. However, if you
modify the same data at the same time on two nodes, one will win and the other modification will be
lost. Unacceptable in many scenarios.
I suggest you read up in Books Online about Replication (there are different types of replications)
as well as "federated servers" (also known as distributed partitioned views).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available with Windows Server
> 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our sql servers?
> And i will create a replication on the background between the 2 sql servers so they will both
> serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>|||Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMficaSjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Load balancing when you own data (like a DBMS does) and you also want to
> modify that data is far from trivial.
> I don't think any system does that transparently (you either use shared
> disk, where you till only have one copy of data or you do shared nothing
> where you only have a set of data on one location).
> First determine what modifications are to be performed. Easiest is of you
> can separate read-only applications (reports etc). If so, you can
> replicate (using built-in transactional replication) from the server where
> you do modifications to the read-only server.
> If you cannot separate read-only applications, you can look into merge
> replication. However, if you modify the same data at the same time on two
> nodes, one will win and the other modification will be lost. Unacceptable
> in many scenarios.
> I suggest you read up in Books Online about Replication (there are
> different types of replications) as well as "federated servers" (also
> known as distributed partitioned views).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Floris" <meNOSPAM@.here_my_place.nl> wrote in message
> news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> In windows 2003 server you have NLB (Network Load Balancing).
>> Can i use this to load balance my traffic to our sql server?
>> I know you also have "Microsoft Cluster Service" but that's only
>> available with Windows Server 2003 Enterprise Edition.
>> So is it possible to load balance my traffic from our webservers to our
>> sql servers?
>> And i will create a replication on the background between the 2 sql
>> servers so they will both serve the same data.
>> I hope someone has an answer for me.
>> Thanks in advance.
>> Floris
>sql
Load Balancing
Hi,
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
FlorisLoad balancing when you own data (like a DBMS does) and you also want to mod
ify that data is far
from trivial.
I don't think any system does that transparently (you either use shared disk
, where you till only
have one copy of data or you do shared nothing where you only have a set of
data on one location).
First determine what modifications are to be performed. Easiest is of you ca
n separate read-only
applications (reports etc). If so, you can replicate (using built-in transac
tional replication) from
the server where you do modifications to the read-only server.
If you cannot separate read-only applications, you can look into merge repli
cation. However, if you
modify the same data at the same time on two nodes, one will win and the oth
er modification will be
lost. Unacceptable in many scenarios.
I suggest you read up in Books Online about Replication (there are different
types of replications)
as well as "federated servers" (also known as distributed partitioned views)
.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx
.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server
> 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our sq
l servers?
> And i will create a replication on the background between the 2 sql server
s so they will both
> serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>|||Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMficaSjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Load balancing when you own data (like a DBMS does) and you also want to
> modify that data is far from trivial.
> I don't think any system does that transparently (you either use shared
> disk, where you till only have one copy of data or you do shared nothing
> where you only have a set of data on one location).
> First determine what modifications are to be performed. Easiest is of you
> can separate read-only applications (reports etc). If so, you can
> replicate (using built-in transactional replication) from the server where
> you do modifications to the read-only server.
> If you cannot separate read-only applications, you can look into merge
> replication. However, if you modify the same data at the same time on two
> nodes, one will win and the other modification will be lost. Unacceptable
> in many scenarios.
> I suggest you read up in Books Online about Replication (there are
> different types of replications) as well as "federated servers" (also
> known as distributed partitioned views).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Floris" <meNOSPAM@.here_my_place.nl> wrote in message
> news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
>
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
FlorisLoad balancing when you own data (like a DBMS does) and you also want to mod
ify that data is far
from trivial.
I don't think any system does that transparently (you either use shared disk
, where you till only
have one copy of data or you do shared nothing where you only have a set of
data on one location).
First determine what modifications are to be performed. Easiest is of you ca
n separate read-only
applications (reports etc). If so, you can replicate (using built-in transac
tional replication) from
the server where you do modifications to the read-only server.
If you cannot separate read-only applications, you can look into merge repli
cation. However, if you
modify the same data at the same time on two nodes, one will win and the oth
er modification will be
lost. Unacceptable in many scenarios.
I suggest you read up in Books Online about Replication (there are different
types of replications)
as well as "federated servers" (also known as distributed partitioned views)
.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx
.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server
> 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our sq
l servers?
> And i will create a replication on the background between the 2 sql server
s so they will both
> serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>|||Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMficaSjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Load balancing when you own data (like a DBMS does) and you also want to
> modify that data is far from trivial.
> I don't think any system does that transparently (you either use shared
> disk, where you till only have one copy of data or you do shared nothing
> where you only have a set of data on one location).
> First determine what modifications are to be performed. Easiest is of you
> can separate read-only applications (reports etc). If so, you can
> replicate (using built-in transactional replication) from the server where
> you do modifications to the read-only server.
> If you cannot separate read-only applications, you can look into merge
> replication. However, if you modify the same data at the same time on two
> nodes, one will win and the other modification will be lost. Unacceptable
> in many scenarios.
> I suggest you read up in Books Online about Replication (there are
> different types of replications) as well as "federated servers" (also
> known as distributed partitioned views).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Floris" <meNOSPAM@.here_my_place.nl> wrote in message
> news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
>
Load Balancing
Hi,
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
Floris
No, SQL Server is not Load Balance aware. Even with clustering does not
provide Load Balancing.
You could have load balance your web servers, and have individual web
servers accessing different dedicated SQL Servers.
SQL Server 2005 does provide Peer to Peer replication which will keep a
group of SQL Servers aggregated for scale out read and write performance in
sync. But it does not provide load balancing, but a piece of the
functionality required by it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message
news:OG3UGVSjFHA.2904@.tk2msftngp13.phx.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available
> with Windows Server 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our
sql
> servers?
> And i will create a replication on the background between the 2 sql
servers
> so they will both serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
Floris
No, SQL Server is not Load Balance aware. Even with clustering does not
provide Load Balancing.
You could have load balance your web servers, and have individual web
servers accessing different dedicated SQL Servers.
SQL Server 2005 does provide Peer to Peer replication which will keep a
group of SQL Servers aggregated for scale out read and write performance in
sync. But it does not provide load balancing, but a piece of the
functionality required by it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message
news:OG3UGVSjFHA.2904@.tk2msftngp13.phx.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available
> with Windows Server 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our
sql
> servers?
> And i will create a replication on the background between the 2 sql
servers
> so they will both serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>
Monday, March 26, 2012
Live server running out of space - Can't reduce Transaction Log!
Hi,
I have just come across a bit of a problem of my Windows 200 server running
SQL Server 2000....I am running out of space!
I have aout 2GB left. I have know this for some time, and for technical
reasons just found out I can't add a new disk...but that's another story.
I have looked at what I can reduce to increase the space, and it seems one
of the smaller database I have on the server, i.e. 16MB, has a log file of
4.8GB!
I am not sure why this has occured, possibly due to not setting a checkpoint
during backup or something!?!?!
...anyway I am desperately tying to reduce the size of this file, and have
tried running the following:
USE Comms
GO
DBCC SHRINKFILE (Comms_log, 10)
and it just comes back and tells me:
- CurrentSize 608616
- Minimum Size 63
- UsedPages 608616
- EstimatedPages 56
with no reduction in LogFile size.
I also tried backing up just the databse and then restoring it under a
different name (so I can completely delete the old one and it's log!), but
when I try to restore it the system just comes back and tells me it hasn't
got enough space, even though it has just over 2GB (presumably it is trying,
or thinks it is trying to restore the log file as well, but goodness knows
where it's getting it from?!).
Any ideas?
Cheers, desperate Mike.
Mike,
What is the database recovery model? Is the log space unrestricted? Do you
take log backups?
If this is a Full recovery model DB then take a log backup. If the log in
unrestricted filegrowth give it a maximum size, just bigger than it is now.
Then you need to follow this: - http://support.microsoft.com/kb/873235/en-us
Chris
"Mike Owen" <whatnospam@.nospam.nospam> wrote in message
news:8CFFFB57-6B03-4DA3-95E7-BFD6B27C95A3@.microsoft.com...
> Hi,
> I have just come across a bit of a problem of my Windows 200 server
> running
> SQL Server 2000....I am running out of space!
> I have aout 2GB left. I have know this for some time, and for technical
> reasons just found out I can't add a new disk...but that's another story.
> I have looked at what I can reduce to increase the space, and it seems one
> of the smaller database I have on the server, i.e. 16MB, has a log file of
> 4.8GB!
> I am not sure why this has occured, possibly due to not setting a
> checkpoint
> during backup or something!?!?!
> ...anyway I am desperately tying to reduce the size of this file, and have
> tried running the following:
> USE Comms
> GO
> DBCC SHRINKFILE (Comms_log, 10)
> and it just comes back and tells me:
> - CurrentSize 608616
> - Minimum Size 63
> - UsedPages 608616
> - EstimatedPages 56
> with no reduction in LogFile size.
> I also tried backing up just the databse and then restoring it under a
> different name (so I can completely delete the old one and it's log!), but
> when I try to restore it the system just comes back and tells me it hasn't
> got enough space, even though it has just over 2GB (presumably it is
> trying,
> or thinks it is trying to restore the log file as well, but goodness knows
> where it's getting it from?!).
> Any ideas?
> Cheers, desperate Mike.
>
|||Hi Mike,
Just do a transaction log backup and then try the shrink operation again.
If that does not work check if you have transactions or jobs running for a
long time.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
> Hi,
> I have just come across a bit of a problem of my Windows 200 server running
> SQL Server 2000....I am running out of space!
> I have aout 2GB left. I have know this for some time, and for technical
> reasons just found out I can't add a new disk...but that's another story.
> I have looked at what I can reduce to increase the space, and it seems one
> of the smaller database I have on the server, i.e. 16MB, has a log file of
> 4.8GB!
> I am not sure why this has occured, possibly due to not setting a checkpoint
> during backup or something!?!?!
> ...anyway I am desperately tying to reduce the size of this file, and have
> tried running the following:
> USE Comms
> GO
> DBCC SHRINKFILE (Comms_log, 10)
> and it just comes back and tells me:
> - CurrentSize 608616
> - Minimum Size 63
> - UsedPages 608616
> - EstimatedPages 56
> with no reduction in LogFile size.
> I also tried backing up just the databse and then restoring it under a
> different name (so I can completely delete the old one and it's log!), but
> when I try to restore it the system just comes back and tells me it hasn't
> got enough space, even though it has just over 2GB (presumably it is trying,
> or thinks it is trying to restore the log file as well, but goodness knows
> where it's getting it from?!).
> Any ideas?
> Cheers, desperate Mike.
>
|||Thanks for you reply Ben, and you Chris. I don't think I can take a log file
backup because I can oly do it on the local server and I haven't the space,
i.e. 2GB space and 4GB+ log file.
I have got a netwrok drive to a different server with space, but the SQL
server backp program doesn't see netwrok drives.
Any other ideas or a way I can backup to the network drive?
Cheers, Mike.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Mike,
> Just do a transaction log backup and then try the shrink operation again.
> If that does not work check if you have transactions or jobs running for a
> long time.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
|||If you do not have enough disk space then use the TRUNCATE_ONLY option like
in
backup log my_db with truncate_only
We are supposing you are not running in the SIMPLE recovery model. Is this
correct?
If that works, you must do a database backup at the end because by using
TRUNCATE_ONLY you are breaking the log chain.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
[vbcol=seagreen]
> Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> backup because I can oly do it on the local server and I haven't the space,
> i.e. 2GB space and 4GB+ log file.
> I have got a netwrok drive to a different server with space, but the SQL
> server backp program doesn't see netwrok drives.
> Any other ideas or a way I can backup to the network drive?
>
> Cheers, Mike.
> "Ben Nevarez" wrote:
|||Excuse my ignorance - It knows no bounds!
How do I know if I am using the SIMPLE recovery model?
I am quite happy to use: backup log my_db with truncate_only
The database isn't used very much, and I only backed it up about 1 1/2 hours
ago, so as long as I can recover that should something go wrong that would be
fine....but perhaps that isn't possible with the backup I have?!!?
Cheers, Mike.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> If you do not have enough disk space then use the TRUNCATE_ONLY option like
> in
> backup log my_db with truncate_only
> We are supposing you are not running in the SIMPLE recovery model. Is this
> correct?
> If that works, you must do a database backup at the end because by using
> TRUNCATE_ONLY you are breaking the log chain.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
|||Looks like you are not using the SIMPLE recovery model but try this
select databasepropertyex('my_db', 'recovery')
And also try the backup log with truncate_only and then shrink the log files.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
[vbcol=seagreen]
> Excuse my ignorance - It knows no bounds!
> How do I know if I am using the SIMPLE recovery model?
> I am quite happy to use: backup log my_db with truncate_only
> The database isn't used very much, and I only backed it up about 1 1/2 hours
> ago, so as long as I can recover that should something go wrong that would be
> fine....but perhaps that isn't possible with the backup I have?!!?
>
> Cheers, Mike.
> "Ben Nevarez" wrote:
|||The answer to select databasepropertyex('my_db', 'recovery')
is FULL.
I tried the '...try the backup log with truncate_only and then shrink the
log files' as you suggested and it WORKED!
I am now back to 7GB of space, plenty of room for growth for all the DB's,
and have restricted the size of all log files on the server.
Thanks for all your help, you got me out of a scrape!
Have a good weekend, cheers, Mike.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Looks like you are not using the SIMPLE recovery model but try this
> select databasepropertyex('my_db', 'recovery')
> And also try the backup log with truncate_only and then shrink the log files.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
|||Be careful about restricting the size of the log files ... SQL Server may
need this disk space.
Instead I would recommend scheduling transaction log backups periodically,
minimum daily. This will allow SQL Server to reuse the disk space used by the
transaction log files.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
[vbcol=seagreen]
> The answer to select databasepropertyex('my_db', 'recovery')
> is FULL.
> I tried the '...try the backup log with truncate_only and then shrink the
> log files' as you suggested and it WORKED!
> I am now back to 7GB of space, plenty of room for growth for all the DB's,
> and have restricted the size of all log files on the server.
> Thanks for all your help, you got me out of a scrape!
> Have a good weekend, cheers, Mike.
>
> "Ben Nevarez" wrote:
|||You can also create an alert that will run the log backup if you fill it
over a certain percent. Providing that you do not have any very large
transactions this should keep you safe.
Chris
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:7BBF5A0F-45E0-4493-9A95-F50FF7D05D20@.microsoft.com...[vbcol=seagreen]
> Be careful about restricting the size of the log files ... SQL Server may
> need this disk space.
> Instead I would recommend scheduling transaction log backups periodically,
> minimum daily. This will allow SQL Server to reuse the disk space used by
> the
> transaction log files.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
I have just come across a bit of a problem of my Windows 200 server running
SQL Server 2000....I am running out of space!
I have aout 2GB left. I have know this for some time, and for technical
reasons just found out I can't add a new disk...but that's another story.
I have looked at what I can reduce to increase the space, and it seems one
of the smaller database I have on the server, i.e. 16MB, has a log file of
4.8GB!
I am not sure why this has occured, possibly due to not setting a checkpoint
during backup or something!?!?!
...anyway I am desperately tying to reduce the size of this file, and have
tried running the following:
USE Comms
GO
DBCC SHRINKFILE (Comms_log, 10)
and it just comes back and tells me:
- CurrentSize 608616
- Minimum Size 63
- UsedPages 608616
- EstimatedPages 56
with no reduction in LogFile size.
I also tried backing up just the databse and then restoring it under a
different name (so I can completely delete the old one and it's log!), but
when I try to restore it the system just comes back and tells me it hasn't
got enough space, even though it has just over 2GB (presumably it is trying,
or thinks it is trying to restore the log file as well, but goodness knows
where it's getting it from?!).
Any ideas?
Cheers, desperate Mike.
Mike,
What is the database recovery model? Is the log space unrestricted? Do you
take log backups?
If this is a Full recovery model DB then take a log backup. If the log in
unrestricted filegrowth give it a maximum size, just bigger than it is now.
Then you need to follow this: - http://support.microsoft.com/kb/873235/en-us
Chris
"Mike Owen" <whatnospam@.nospam.nospam> wrote in message
news:8CFFFB57-6B03-4DA3-95E7-BFD6B27C95A3@.microsoft.com...
> Hi,
> I have just come across a bit of a problem of my Windows 200 server
> running
> SQL Server 2000....I am running out of space!
> I have aout 2GB left. I have know this for some time, and for technical
> reasons just found out I can't add a new disk...but that's another story.
> I have looked at what I can reduce to increase the space, and it seems one
> of the smaller database I have on the server, i.e. 16MB, has a log file of
> 4.8GB!
> I am not sure why this has occured, possibly due to not setting a
> checkpoint
> during backup or something!?!?!
> ...anyway I am desperately tying to reduce the size of this file, and have
> tried running the following:
> USE Comms
> GO
> DBCC SHRINKFILE (Comms_log, 10)
> and it just comes back and tells me:
> - CurrentSize 608616
> - Minimum Size 63
> - UsedPages 608616
> - EstimatedPages 56
> with no reduction in LogFile size.
> I also tried backing up just the databse and then restoring it under a
> different name (so I can completely delete the old one and it's log!), but
> when I try to restore it the system just comes back and tells me it hasn't
> got enough space, even though it has just over 2GB (presumably it is
> trying,
> or thinks it is trying to restore the log file as well, but goodness knows
> where it's getting it from?!).
> Any ideas?
> Cheers, desperate Mike.
>
|||Hi Mike,
Just do a transaction log backup and then try the shrink operation again.
If that does not work check if you have transactions or jobs running for a
long time.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
> Hi,
> I have just come across a bit of a problem of my Windows 200 server running
> SQL Server 2000....I am running out of space!
> I have aout 2GB left. I have know this for some time, and for technical
> reasons just found out I can't add a new disk...but that's another story.
> I have looked at what I can reduce to increase the space, and it seems one
> of the smaller database I have on the server, i.e. 16MB, has a log file of
> 4.8GB!
> I am not sure why this has occured, possibly due to not setting a checkpoint
> during backup or something!?!?!
> ...anyway I am desperately tying to reduce the size of this file, and have
> tried running the following:
> USE Comms
> GO
> DBCC SHRINKFILE (Comms_log, 10)
> and it just comes back and tells me:
> - CurrentSize 608616
> - Minimum Size 63
> - UsedPages 608616
> - EstimatedPages 56
> with no reduction in LogFile size.
> I also tried backing up just the databse and then restoring it under a
> different name (so I can completely delete the old one and it's log!), but
> when I try to restore it the system just comes back and tells me it hasn't
> got enough space, even though it has just over 2GB (presumably it is trying,
> or thinks it is trying to restore the log file as well, but goodness knows
> where it's getting it from?!).
> Any ideas?
> Cheers, desperate Mike.
>
|||Thanks for you reply Ben, and you Chris. I don't think I can take a log file
backup because I can oly do it on the local server and I haven't the space,
i.e. 2GB space and 4GB+ log file.
I have got a netwrok drive to a different server with space, but the SQL
server backp program doesn't see netwrok drives.
Any other ideas or a way I can backup to the network drive?
Cheers, Mike.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Mike,
> Just do a transaction log backup and then try the shrink operation again.
> If that does not work check if you have transactions or jobs running for a
> long time.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
|||If you do not have enough disk space then use the TRUNCATE_ONLY option like
in
backup log my_db with truncate_only
We are supposing you are not running in the SIMPLE recovery model. Is this
correct?
If that works, you must do a database backup at the end because by using
TRUNCATE_ONLY you are breaking the log chain.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
[vbcol=seagreen]
> Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> backup because I can oly do it on the local server and I haven't the space,
> i.e. 2GB space and 4GB+ log file.
> I have got a netwrok drive to a different server with space, but the SQL
> server backp program doesn't see netwrok drives.
> Any other ideas or a way I can backup to the network drive?
>
> Cheers, Mike.
> "Ben Nevarez" wrote:
|||Excuse my ignorance - It knows no bounds!
How do I know if I am using the SIMPLE recovery model?
I am quite happy to use: backup log my_db with truncate_only
The database isn't used very much, and I only backed it up about 1 1/2 hours
ago, so as long as I can recover that should something go wrong that would be
fine....but perhaps that isn't possible with the backup I have?!!?
Cheers, Mike.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> If you do not have enough disk space then use the TRUNCATE_ONLY option like
> in
> backup log my_db with truncate_only
> We are supposing you are not running in the SIMPLE recovery model. Is this
> correct?
> If that works, you must do a database backup at the end because by using
> TRUNCATE_ONLY you are breaking the log chain.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
|||Looks like you are not using the SIMPLE recovery model but try this
select databasepropertyex('my_db', 'recovery')
And also try the backup log with truncate_only and then shrink the log files.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
[vbcol=seagreen]
> Excuse my ignorance - It knows no bounds!
> How do I know if I am using the SIMPLE recovery model?
> I am quite happy to use: backup log my_db with truncate_only
> The database isn't used very much, and I only backed it up about 1 1/2 hours
> ago, so as long as I can recover that should something go wrong that would be
> fine....but perhaps that isn't possible with the backup I have?!!?
>
> Cheers, Mike.
> "Ben Nevarez" wrote:
|||The answer to select databasepropertyex('my_db', 'recovery')
is FULL.
I tried the '...try the backup log with truncate_only and then shrink the
log files' as you suggested and it WORKED!
I am now back to 7GB of space, plenty of room for growth for all the DB's,
and have restricted the size of all log files on the server.
Thanks for all your help, you got me out of a scrape!
Have a good weekend, cheers, Mike.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Looks like you are not using the SIMPLE recovery model but try this
> select databasepropertyex('my_db', 'recovery')
> And also try the backup log with truncate_only and then shrink the log files.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
|||Be careful about restricting the size of the log files ... SQL Server may
need this disk space.
Instead I would recommend scheduling transaction log backups periodically,
minimum daily. This will allow SQL Server to reuse the disk space used by the
transaction log files.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
[vbcol=seagreen]
> The answer to select databasepropertyex('my_db', 'recovery')
> is FULL.
> I tried the '...try the backup log with truncate_only and then shrink the
> log files' as you suggested and it WORKED!
> I am now back to 7GB of space, plenty of room for growth for all the DB's,
> and have restricted the size of all log files on the server.
> Thanks for all your help, you got me out of a scrape!
> Have a good weekend, cheers, Mike.
>
> "Ben Nevarez" wrote:
|||You can also create an alert that will run the log backup if you fill it
over a certain percent. Providing that you do not have any very large
transactions this should keep you safe.
Chris
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:7BBF5A0F-45E0-4493-9A95-F50FF7D05D20@.microsoft.com...[vbcol=seagreen]
> Be careful about restricting the size of the log files ... SQL Server may
> need this disk space.
> Instead I would recommend scheduling transaction log backups periodically,
> minimum daily. This will allow SQL Server to reuse the disk space used by
> the
> transaction log files.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
Live server running out of space - Can't reduce Transaction Log!
Hi,
I have just come across a bit of a problem of my Windows 200 server running
SQL Server 2000....I am running out of space!
I have aout 2GB left. I have know this for some time, and for technical
reasons just found out I can't add a new disk...but that's another story.
I have looked at what I can reduce to increase the space, and it seems one
of the smaller database I have on the server, i.e. 16MB, has a log file of
4.8GB!
I am not sure why this has occured, possibly due to not setting a checkpoint
during backup or something!?!?!
...anyway I am desperately tying to reduce the size of this file, and have
tried running the following:
USE Comms
GO
DBCC SHRINKFILE (Comms_log, 10)
and it just comes back and tells me:
- CurrentSize 608616
- Minimum Size 63
- UsedPages 608616
- EstimatedPages 56
with no reduction in LogFile size.
I also tried backing up just the databse and then restoring it under a
different name (so I can completely delete the old one and it's log!), but
when I try to restore it the system just comes back and tells me it hasn't
got enough space, even though it has just over 2GB (presumably it is trying,
or thinks it is trying to restore the log file as well, but goodness knows
where it's getting it from?!).
Any ideas?
Cheers, desperate Mike.Mike,
What is the database recovery model? Is the log space unrestricted? Do you
take log backups?
If this is a Full recovery model DB then take a log backup. If the log in
unrestricted filegrowth give it a maximum size, just bigger than it is now.
Then you need to follow this: - http://support.microsoft.com/kb/873235/en-us
Chris
"Mike Owen" <whatnospam@.nospam.nospam> wrote in message
news:8CFFFB57-6B03-4DA3-95E7-BFD6B27C95A3@.microsoft.com...
> Hi,
> I have just come across a bit of a problem of my Windows 200 server
> running
> SQL Server 2000....I am running out of space!
> I have aout 2GB left. I have know this for some time, and for technical
> reasons just found out I can't add a new disk...but that's another story.
> I have looked at what I can reduce to increase the space, and it seems one
> of the smaller database I have on the server, i.e. 16MB, has a log file of
> 4.8GB!
> I am not sure why this has occured, possibly due to not setting a
> checkpoint
> during backup or something!?!?!
> ...anyway I am desperately tying to reduce the size of this file, and have
> tried running the following:
> USE Comms
> GO
> DBCC SHRINKFILE (Comms_log, 10)
> and it just comes back and tells me:
> - CurrentSize 608616
> - Minimum Size 63
> - UsedPages 608616
> - EstimatedPages 56
> with no reduction in LogFile size.
> I also tried backing up just the databse and then restoring it under a
> different name (so I can completely delete the old one and it's log!), but
> when I try to restore it the system just comes back and tells me it hasn't
> got enough space, even though it has just over 2GB (presumably it is
> trying,
> or thinks it is trying to restore the log file as well, but goodness knows
> where it's getting it from?!).
> Any ideas?
> Cheers, desperate Mike.
>|||Hi Mike,
Just do a transaction log backup and then try the shrink operation again.
If that does not work check if you have transactions or jobs running for a
long time.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
> Hi,
> I have just come across a bit of a problem of my Windows 200 server running
> SQL Server 2000....I am running out of space!
> I have aout 2GB left. I have know this for some time, and for technical
> reasons just found out I can't add a new disk...but that's another story.
> I have looked at what I can reduce to increase the space, and it seems one
> of the smaller database I have on the server, i.e. 16MB, has a log file of
> 4.8GB!
> I am not sure why this has occured, possibly due to not setting a checkpoint
> during backup or something!?!?!
> ...anyway I am desperately tying to reduce the size of this file, and have
> tried running the following:
> USE Comms
> GO
> DBCC SHRINKFILE (Comms_log, 10)
> and it just comes back and tells me:
> - CurrentSize 608616
> - Minimum Size 63
> - UsedPages 608616
> - EstimatedPages 56
> with no reduction in LogFile size.
> I also tried backing up just the databse and then restoring it under a
> different name (so I can completely delete the old one and it's log!), but
> when I try to restore it the system just comes back and tells me it hasn't
> got enough space, even though it has just over 2GB (presumably it is trying,
> or thinks it is trying to restore the log file as well, but goodness knows
> where it's getting it from?!).
> Any ideas?
> Cheers, desperate Mike.
>|||Thanks for you reply Ben, and you Chris. I don't think I can take a log file
backup because I can oly do it on the local server and I haven't the space,
i.e. 2GB space and 4GB+ log file.
I have got a netwrok drive to a different server with space, but the SQL
server backp program doesn't see netwrok drives.
Any other ideas or a way I can backup to the network drive?
Cheers, Mike.
"Ben Nevarez" wrote:
> Hi Mike,
> Just do a transaction log backup and then try the shrink operation again.
> If that does not work check if you have transactions or jobs running for a
> long time.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
> > Hi,
> >
> > I have just come across a bit of a problem of my Windows 200 server running
> > SQL Server 2000....I am running out of space!
> >
> > I have aout 2GB left. I have know this for some time, and for technical
> > reasons just found out I can't add a new disk...but that's another story.
> >
> > I have looked at what I can reduce to increase the space, and it seems one
> > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > 4.8GB!
> >
> > I am not sure why this has occured, possibly due to not setting a checkpoint
> > during backup or something!?!?!
> >
> > ...anyway I am desperately tying to reduce the size of this file, and have
> > tried running the following:
> >
> > USE Comms
> > GO
> > DBCC SHRINKFILE (Comms_log, 10)
> >
> > and it just comes back and tells me:
> > - CurrentSize 608616
> > - Minimum Size 63
> > - UsedPages 608616
> > - EstimatedPages 56
> >
> > with no reduction in LogFile size.
> >
> > I also tried backing up just the databse and then restoring it under a
> > different name (so I can completely delete the old one and it's log!), but
> > when I try to restore it the system just comes back and tells me it hasn't
> > got enough space, even though it has just over 2GB (presumably it is trying,
> > or thinks it is trying to restore the log file as well, but goodness knows
> > where it's getting it from?!).
> >
> > Any ideas?
> >
> > Cheers, desperate Mike.
> >|||If you do not have enough disk space then use the TRUNCATE_ONLY option like
in
backup log my_db with truncate_only
We are supposing you are not running in the SIMPLE recovery model. Is this
correct?
If that works, you must do a database backup at the end because by using
TRUNCATE_ONLY you are breaking the log chain.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
> Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> backup because I can oly do it on the local server and I haven't the space,
> i.e. 2GB space and 4GB+ log file.
> I have got a netwrok drive to a different server with space, but the SQL
> server backp program doesn't see netwrok drives.
> Any other ideas or a way I can backup to the network drive?
>
> Cheers, Mike.
> "Ben Nevarez" wrote:
> >
> > Hi Mike,
> >
> > Just do a transaction log backup and then try the shrink operation again.
> >
> > If that does not work check if you have transactions or jobs running for a
> > long time.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Mike Owen" wrote:
> >
> > > Hi,
> > >
> > > I have just come across a bit of a problem of my Windows 200 server running
> > > SQL Server 2000....I am running out of space!
> > >
> > > I have aout 2GB left. I have know this for some time, and for technical
> > > reasons just found out I can't add a new disk...but that's another story.
> > >
> > > I have looked at what I can reduce to increase the space, and it seems one
> > > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > > 4.8GB!
> > >
> > > I am not sure why this has occured, possibly due to not setting a checkpoint
> > > during backup or something!?!?!
> > >
> > > ...anyway I am desperately tying to reduce the size of this file, and have
> > > tried running the following:
> > >
> > > USE Comms
> > > GO
> > > DBCC SHRINKFILE (Comms_log, 10)
> > >
> > > and it just comes back and tells me:
> > > - CurrentSize 608616
> > > - Minimum Size 63
> > > - UsedPages 608616
> > > - EstimatedPages 56
> > >
> > > with no reduction in LogFile size.
> > >
> > > I also tried backing up just the databse and then restoring it under a
> > > different name (so I can completely delete the old one and it's log!), but
> > > when I try to restore it the system just comes back and tells me it hasn't
> > > got enough space, even though it has just over 2GB (presumably it is trying,
> > > or thinks it is trying to restore the log file as well, but goodness knows
> > > where it's getting it from?!).
> > >
> > > Any ideas?
> > >
> > > Cheers, desperate Mike.
> > >|||Excuse my ignorance - It knows no bounds!
How do I know if I am using the SIMPLE recovery model?
I am quite happy to use: backup log my_db with truncate_only
The database isn't used very much, and I only backed it up about 1 1/2 hours
ago, so as long as I can recover that should something go wrong that would be
fine....but perhaps that isn't possible with the backup I have?!!?
Cheers, Mike.
"Ben Nevarez" wrote:
> If you do not have enough disk space then use the TRUNCATE_ONLY option like
> in
> backup log my_db with truncate_only
> We are supposing you are not running in the SIMPLE recovery model. Is this
> correct?
> If that works, you must do a database backup at the end because by using
> TRUNCATE_ONLY you are breaking the log chain.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
> > Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> > backup because I can oly do it on the local server and I haven't the space,
> > i.e. 2GB space and 4GB+ log file.
> >
> > I have got a netwrok drive to a different server with space, but the SQL
> > server backp program doesn't see netwrok drives.
> >
> > Any other ideas or a way I can backup to the network drive?
> >
> >
> > Cheers, Mike.
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > Hi Mike,
> > >
> > > Just do a transaction log backup and then try the shrink operation again.
> > >
> > > If that does not work check if you have transactions or jobs running for a
> > > long time.
> > >
> > > Hope this helps,
> > >
> > > Ben Nevarez
> > > Senior Database Administrator
> > > AIG SunAmerica
> > >
> > >
> > >
> > > "Mike Owen" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have just come across a bit of a problem of my Windows 200 server running
> > > > SQL Server 2000....I am running out of space!
> > > >
> > > > I have aout 2GB left. I have know this for some time, and for technical
> > > > reasons just found out I can't add a new disk...but that's another story.
> > > >
> > > > I have looked at what I can reduce to increase the space, and it seems one
> > > > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > > > 4.8GB!
> > > >
> > > > I am not sure why this has occured, possibly due to not setting a checkpoint
> > > > during backup or something!?!?!
> > > >
> > > > ...anyway I am desperately tying to reduce the size of this file, and have
> > > > tried running the following:
> > > >
> > > > USE Comms
> > > > GO
> > > > DBCC SHRINKFILE (Comms_log, 10)
> > > >
> > > > and it just comes back and tells me:
> > > > - CurrentSize 608616
> > > > - Minimum Size 63
> > > > - UsedPages 608616
> > > > - EstimatedPages 56
> > > >
> > > > with no reduction in LogFile size.
> > > >
> > > > I also tried backing up just the databse and then restoring it under a
> > > > different name (so I can completely delete the old one and it's log!), but
> > > > when I try to restore it the system just comes back and tells me it hasn't
> > > > got enough space, even though it has just over 2GB (presumably it is trying,
> > > > or thinks it is trying to restore the log file as well, but goodness knows
> > > > where it's getting it from?!).
> > > >
> > > > Any ideas?
> > > >
> > > > Cheers, desperate Mike.
> > > >|||Looks like you are not using the SIMPLE recovery model but try this
select databasepropertyex('my_db', 'recovery')
And also try the backup log with truncate_only and then shrink the log files.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
> Excuse my ignorance - It knows no bounds!
> How do I know if I am using the SIMPLE recovery model?
> I am quite happy to use: backup log my_db with truncate_only
> The database isn't used very much, and I only backed it up about 1 1/2 hours
> ago, so as long as I can recover that should something go wrong that would be
> fine....but perhaps that isn't possible with the backup I have?!!?
>
> Cheers, Mike.
> "Ben Nevarez" wrote:
> >
> > If you do not have enough disk space then use the TRUNCATE_ONLY option like
> > in
> >
> > backup log my_db with truncate_only
> >
> > We are supposing you are not running in the SIMPLE recovery model. Is this
> > correct?
> >
> > If that works, you must do a database backup at the end because by using
> > TRUNCATE_ONLY you are breaking the log chain.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Mike Owen" wrote:
> >
> > > Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> > > backup because I can oly do it on the local server and I haven't the space,
> > > i.e. 2GB space and 4GB+ log file.
> > >
> > > I have got a netwrok drive to a different server with space, but the SQL
> > > server backp program doesn't see netwrok drives.
> > >
> > > Any other ideas or a way I can backup to the network drive?
> > >
> > >
> > > Cheers, Mike.
> > >
> > > "Ben Nevarez" wrote:
> > >
> > > >
> > > > Hi Mike,
> > > >
> > > > Just do a transaction log backup and then try the shrink operation again.
> > > >
> > > > If that does not work check if you have transactions or jobs running for a
> > > > long time.
> > > >
> > > > Hope this helps,
> > > >
> > > > Ben Nevarez
> > > > Senior Database Administrator
> > > > AIG SunAmerica
> > > >
> > > >
> > > >
> > > > "Mike Owen" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have just come across a bit of a problem of my Windows 200 server running
> > > > > SQL Server 2000....I am running out of space!
> > > > >
> > > > > I have aout 2GB left. I have know this for some time, and for technical
> > > > > reasons just found out I can't add a new disk...but that's another story.
> > > > >
> > > > > I have looked at what I can reduce to increase the space, and it seems one
> > > > > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > > > > 4.8GB!
> > > > >
> > > > > I am not sure why this has occured, possibly due to not setting a checkpoint
> > > > > during backup or something!?!?!
> > > > >
> > > > > ...anyway I am desperately tying to reduce the size of this file, and have
> > > > > tried running the following:
> > > > >
> > > > > USE Comms
> > > > > GO
> > > > > DBCC SHRINKFILE (Comms_log, 10)
> > > > >
> > > > > and it just comes back and tells me:
> > > > > - CurrentSize 608616
> > > > > - Minimum Size 63
> > > > > - UsedPages 608616
> > > > > - EstimatedPages 56
> > > > >
> > > > > with no reduction in LogFile size.
> > > > >
> > > > > I also tried backing up just the databse and then restoring it under a
> > > > > different name (so I can completely delete the old one and it's log!), but
> > > > > when I try to restore it the system just comes back and tells me it hasn't
> > > > > got enough space, even though it has just over 2GB (presumably it is trying,
> > > > > or thinks it is trying to restore the log file as well, but goodness knows
> > > > > where it's getting it from?!).
> > > > >
> > > > > Any ideas?
> > > > >
> > > > > Cheers, desperate Mike.
> > > > >|||The answer to select databasepropertyex('my_db', 'recovery')
is FULL.
I tried the '...try the backup log with truncate_only and then shrink the
log files' as you suggested and it WORKED!
I am now back to 7GB of space, plenty of room for growth for all the DB's,
and have restricted the size of all log files on the server.
Thanks for all your help, you got me out of a scrape!
Have a good weekend, cheers, Mike.
"Ben Nevarez" wrote:
> Looks like you are not using the SIMPLE recovery model but try this
> select databasepropertyex('my_db', 'recovery')
> And also try the backup log with truncate_only and then shrink the log files.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
> > Excuse my ignorance - It knows no bounds!
> >
> > How do I know if I am using the SIMPLE recovery model?
> >
> > I am quite happy to use: backup log my_db with truncate_only
> >
> > The database isn't used very much, and I only backed it up about 1 1/2 hours
> > ago, so as long as I can recover that should something go wrong that would be
> > fine....but perhaps that isn't possible with the backup I have?!!?
> >
> >
> > Cheers, Mike.
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > If you do not have enough disk space then use the TRUNCATE_ONLY option like
> > > in
> > >
> > > backup log my_db with truncate_only
> > >
> > > We are supposing you are not running in the SIMPLE recovery model. Is this
> > > correct?
> > >
> > > If that works, you must do a database backup at the end because by using
> > > TRUNCATE_ONLY you are breaking the log chain.
> > >
> > > Hope this helps,
> > >
> > > Ben Nevarez
> > > Senior Database Administrator
> > > AIG SunAmerica
> > >
> > >
> > >
> > > "Mike Owen" wrote:
> > >
> > > > Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> > > > backup because I can oly do it on the local server and I haven't the space,
> > > > i.e. 2GB space and 4GB+ log file.
> > > >
> > > > I have got a netwrok drive to a different server with space, but the SQL
> > > > server backp program doesn't see netwrok drives.
> > > >
> > > > Any other ideas or a way I can backup to the network drive?
> > > >
> > > >
> > > > Cheers, Mike.
> > > >
> > > > "Ben Nevarez" wrote:
> > > >
> > > > >
> > > > > Hi Mike,
> > > > >
> > > > > Just do a transaction log backup and then try the shrink operation again.
> > > > >
> > > > > If that does not work check if you have transactions or jobs running for a
> > > > > long time.
> > > > >
> > > > > Hope this helps,
> > > > >
> > > > > Ben Nevarez
> > > > > Senior Database Administrator
> > > > > AIG SunAmerica
> > > > >
> > > > >
> > > > >
> > > > > "Mike Owen" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have just come across a bit of a problem of my Windows 200 server running
> > > > > > SQL Server 2000....I am running out of space!
> > > > > >
> > > > > > I have aout 2GB left. I have know this for some time, and for technical
> > > > > > reasons just found out I can't add a new disk...but that's another story.
> > > > > >
> > > > > > I have looked at what I can reduce to increase the space, and it seems one
> > > > > > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > > > > > 4.8GB!
> > > > > >
> > > > > > I am not sure why this has occured, possibly due to not setting a checkpoint
> > > > > > during backup or something!?!?!
> > > > > >
> > > > > > ...anyway I am desperately tying to reduce the size of this file, and have
> > > > > > tried running the following:
> > > > > >
> > > > > > USE Comms
> > > > > > GO
> > > > > > DBCC SHRINKFILE (Comms_log, 10)
> > > > > >
> > > > > > and it just comes back and tells me:
> > > > > > - CurrentSize 608616
> > > > > > - Minimum Size 63
> > > > > > - UsedPages 608616
> > > > > > - EstimatedPages 56
> > > > > >
> > > > > > with no reduction in LogFile size.
> > > > > >
> > > > > > I also tried backing up just the databse and then restoring it under a
> > > > > > different name (so I can completely delete the old one and it's log!), but
> > > > > > when I try to restore it the system just comes back and tells me it hasn't
> > > > > > got enough space, even though it has just over 2GB (presumably it is trying,
> > > > > > or thinks it is trying to restore the log file as well, but goodness knows
> > > > > > where it's getting it from?!).
> > > > > >
> > > > > > Any ideas?
> > > > > >
> > > > > > Cheers, desperate Mike.
> > > > > >|||Be careful about restricting the size of the log files ... SQL Server may
need this disk space.
Instead I would recommend scheduling transaction log backups periodically,
minimum daily. This will allow SQL Server to reuse the disk space used by the
transaction log files.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
> The answer to select databasepropertyex('my_db', 'recovery')
> is FULL.
> I tried the '...try the backup log with truncate_only and then shrink the
> log files' as you suggested and it WORKED!
> I am now back to 7GB of space, plenty of room for growth for all the DB's,
> and have restricted the size of all log files on the server.
> Thanks for all your help, you got me out of a scrape!
> Have a good weekend, cheers, Mike.
>
> "Ben Nevarez" wrote:
> >
> > Looks like you are not using the SIMPLE recovery model but try this
> >
> > select databasepropertyex('my_db', 'recovery')
> >
> > And also try the backup log with truncate_only and then shrink the log files.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Mike Owen" wrote:
> >
> > > Excuse my ignorance - It knows no bounds!
> > >
> > > How do I know if I am using the SIMPLE recovery model?
> > >
> > > I am quite happy to use: backup log my_db with truncate_only
> > >
> > > The database isn't used very much, and I only backed it up about 1 1/2 hours
> > > ago, so as long as I can recover that should something go wrong that would be
> > > fine....but perhaps that isn't possible with the backup I have?!!?
> > >
> > >
> > > Cheers, Mike.
> > >
> > > "Ben Nevarez" wrote:
> > >
> > > >
> > > > If you do not have enough disk space then use the TRUNCATE_ONLY option like
> > > > in
> > > >
> > > > backup log my_db with truncate_only
> > > >
> > > > We are supposing you are not running in the SIMPLE recovery model. Is this
> > > > correct?
> > > >
> > > > If that works, you must do a database backup at the end because by using
> > > > TRUNCATE_ONLY you are breaking the log chain.
> > > >
> > > > Hope this helps,
> > > >
> > > > Ben Nevarez
> > > > Senior Database Administrator
> > > > AIG SunAmerica
> > > >
> > > >
> > > >
> > > > "Mike Owen" wrote:
> > > >
> > > > > Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> > > > > backup because I can oly do it on the local server and I haven't the space,
> > > > > i.e. 2GB space and 4GB+ log file.
> > > > >
> > > > > I have got a netwrok drive to a different server with space, but the SQL
> > > > > server backp program doesn't see netwrok drives.
> > > > >
> > > > > Any other ideas or a way I can backup to the network drive?
> > > > >
> > > > >
> > > > > Cheers, Mike.
> > > > >
> > > > > "Ben Nevarez" wrote:
> > > > >
> > > > > >
> > > > > > Hi Mike,
> > > > > >
> > > > > > Just do a transaction log backup and then try the shrink operation again.
> > > > > >
> > > > > > If that does not work check if you have transactions or jobs running for a
> > > > > > long time.
> > > > > >
> > > > > > Hope this helps,
> > > > > >
> > > > > > Ben Nevarez
> > > > > > Senior Database Administrator
> > > > > > AIG SunAmerica
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Mike Owen" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I have just come across a bit of a problem of my Windows 200 server running
> > > > > > > SQL Server 2000....I am running out of space!
> > > > > > >
> > > > > > > I have aout 2GB left. I have know this for some time, and for technical
> > > > > > > reasons just found out I can't add a new disk...but that's another story.
> > > > > > >
> > > > > > > I have looked at what I can reduce to increase the space, and it seems one
> > > > > > > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > > > > > > 4.8GB!
> > > > > > >
> > > > > > > I am not sure why this has occured, possibly due to not setting a checkpoint
> > > > > > > during backup or something!?!?!
> > > > > > >
> > > > > > > ...anyway I am desperately tying to reduce the size of this file, and have
> > > > > > > tried running the following:
> > > > > > >
> > > > > > > USE Comms
> > > > > > > GO
> > > > > > > DBCC SHRINKFILE (Comms_log, 10)
> > > > > > >
> > > > > > > and it just comes back and tells me:
> > > > > > > - CurrentSize 608616
> > > > > > > - Minimum Size 63
> > > > > > > - UsedPages 608616
> > > > > > > - EstimatedPages 56
> > > > > > >
> > > > > > > with no reduction in LogFile size.
> > > > > > >
> > > > > > > I also tried backing up just the databse and then restoring it under a
> > > > > > > different name (so I can completely delete the old one and it's log!), but
> > > > > > > when I try to restore it the system just comes back and tells me it hasn't
> > > > > > > got enough space, even though it has just over 2GB (presumably it is trying,
> > > > > > > or thinks it is trying to restore the log file as well, but goodness knows
> > > > > > > where it's getting it from?!).
> > > > > > >
> > > > > > > Any ideas?
> > > > > > >
> > > > > > > Cheers, desperate Mike.
> > > > > > >|||You can also create an alert that will run the log backup if you fill it
over a certain percent. Providing that you do not have any very large
transactions this should keep you safe.
Chris
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:7BBF5A0F-45E0-4493-9A95-F50FF7D05D20@.microsoft.com...
> Be careful about restricting the size of the log files ... SQL Server may
> need this disk space.
> Instead I would recommend scheduling transaction log backups periodically,
> minimum daily. This will allow SQL Server to reuse the disk space used by
> the
> transaction log files.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
>> The answer to select databasepropertyex('my_db', 'recovery')
>> is FULL.
>> I tried the '...try the backup log with truncate_only and then shrink
>> the
>> log files' as you suggested and it WORKED!
>> I am now back to 7GB of space, plenty of room for growth for all the
>> DB's,
>> and have restricted the size of all log files on the server.
>> Thanks for all your help, you got me out of a scrape!
>> Have a good weekend, cheers, Mike.
>>
>> "Ben Nevarez" wrote:
>> >
>> > Looks like you are not using the SIMPLE recovery model but try this
>> >
>> > select databasepropertyex('my_db', 'recovery')
>> >
>> > And also try the backup log with truncate_only and then shrink the log
>> > files.
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> > Senior Database Administrator
>> > AIG SunAmerica
>> >
>> >
>> >
>> > "Mike Owen" wrote:
>> >
>> > > Excuse my ignorance - It knows no bounds!
>> > >
>> > > How do I know if I am using the SIMPLE recovery model?
>> > >
>> > > I am quite happy to use: backup log my_db with truncate_only
>> > >
>> > > The database isn't used very much, and I only backed it up about 1
>> > > 1/2 hours
>> > > ago, so as long as I can recover that should something go wrong that
>> > > would be
>> > > fine....but perhaps that isn't possible with the backup I have?!!?
>> > >
>> > >
>> > > Cheers, Mike.
>> > >
>> > > "Ben Nevarez" wrote:
>> > >
>> > > >
>> > > > If you do not have enough disk space then use the TRUNCATE_ONLY
>> > > > option like
>> > > > in
>> > > >
>> > > > backup log my_db with truncate_only
>> > > >
>> > > > We are supposing you are not running in the SIMPLE recovery model.
>> > > > Is this
>> > > > correct?
>> > > >
>> > > > If that works, you must do a database backup at the end because by
>> > > > using
>> > > > TRUNCATE_ONLY you are breaking the log chain.
>> > > >
>> > > > Hope this helps,
>> > > >
>> > > > Ben Nevarez
>> > > > Senior Database Administrator
>> > > > AIG SunAmerica
>> > > >
>> > > >
>> > > >
>> > > > "Mike Owen" wrote:
>> > > >
>> > > > > Thanks for you reply Ben, and you Chris. I don't think I can
>> > > > > take a log file
>> > > > > backup because I can oly do it on the local server and I haven't
>> > > > > the space,
>> > > > > i.e. 2GB space and 4GB+ log file.
>> > > > >
>> > > > > I have got a netwrok drive to a different server with space, but
>> > > > > the SQL
>> > > > > server backp program doesn't see netwrok drives.
>> > > > >
>> > > > > Any other ideas or a way I can backup to the network drive?
>> > > > >
>> > > > >
>> > > > > Cheers, Mike.
>> > > > >
>> > > > > "Ben Nevarez" wrote:
>> > > > >
>> > > > > >
>> > > > > > Hi Mike,
>> > > > > >
>> > > > > > Just do a transaction log backup and then try the shrink
>> > > > > > operation again.
>> > > > > >
>> > > > > > If that does not work check if you have transactions or jobs
>> > > > > > running for a
>> > > > > > long time.
>> > > > > >
>> > > > > > Hope this helps,
>> > > > > >
>> > > > > > Ben Nevarez
>> > > > > > Senior Database Administrator
>> > > > > > AIG SunAmerica
>> > > > > >
>> > > > > >
>> > > > > >
>> > > > > > "Mike Owen" wrote:
>> > > > > >
>> > > > > > > Hi,
>> > > > > > >
>> > > > > > > I have just come across a bit of a problem of my Windows 200
>> > > > > > > server running
>> > > > > > > SQL Server 2000....I am running out of space!
>> > > > > > >
>> > > > > > > I have aout 2GB left. I have know this for some time, and
>> > > > > > > for technical
>> > > > > > > reasons just found out I can't add a new disk...but that's
>> > > > > > > another story.
>> > > > > > >
>> > > > > > > I have looked at what I can reduce to increase the space, and
>> > > > > > > it seems one
>> > > > > > > of the smaller database I have on the server, i.e. 16MB, has
>> > > > > > > a log file of
>> > > > > > > 4.8GB!
>> > > > > > >
>> > > > > > > I am not sure why this has occured, possibly due to not
>> > > > > > > setting a checkpoint
>> > > > > > > during backup or something!?!?!
>> > > > > > >
>> > > > > > > ...anyway I am desperately tying to reduce the size of this
>> > > > > > > file, and have
>> > > > > > > tried running the following:
>> > > > > > >
>> > > > > > > USE Comms
>> > > > > > > GO
>> > > > > > > DBCC SHRINKFILE (Comms_log, 10)
>> > > > > > >
>> > > > > > > and it just comes back and tells me:
>> > > > > > > - CurrentSize 608616
>> > > > > > > - Minimum Size 63
>> > > > > > > - UsedPages 608616
>> > > > > > > - EstimatedPages 56
>> > > > > > >
>> > > > > > > with no reduction in LogFile size.
>> > > > > > >
>> > > > > > > I also tried backing up just the databse and then restoring
>> > > > > > > it under a
>> > > > > > > different name (so I can completely delete the old one and
>> > > > > > > it's log!), but
>> > > > > > > when I try to restore it the system just comes back and tells
>> > > > > > > me it hasn't
>> > > > > > > got enough space, even though it has just over 2GB
>> > > > > > > (presumably it is trying,
>> > > > > > > or thinks it is trying to restore the log file as well, but
>> > > > > > > goodness knows
>> > > > > > > where it's getting it from?!).
>> > > > > > >
>> > > > > > > Any ideas?
>> > > > > > >
>> > > > > > > Cheers, desperate Mike.
>> > > > > > >
I have just come across a bit of a problem of my Windows 200 server running
SQL Server 2000....I am running out of space!
I have aout 2GB left. I have know this for some time, and for technical
reasons just found out I can't add a new disk...but that's another story.
I have looked at what I can reduce to increase the space, and it seems one
of the smaller database I have on the server, i.e. 16MB, has a log file of
4.8GB!
I am not sure why this has occured, possibly due to not setting a checkpoint
during backup or something!?!?!
...anyway I am desperately tying to reduce the size of this file, and have
tried running the following:
USE Comms
GO
DBCC SHRINKFILE (Comms_log, 10)
and it just comes back and tells me:
- CurrentSize 608616
- Minimum Size 63
- UsedPages 608616
- EstimatedPages 56
with no reduction in LogFile size.
I also tried backing up just the databse and then restoring it under a
different name (so I can completely delete the old one and it's log!), but
when I try to restore it the system just comes back and tells me it hasn't
got enough space, even though it has just over 2GB (presumably it is trying,
or thinks it is trying to restore the log file as well, but goodness knows
where it's getting it from?!).
Any ideas?
Cheers, desperate Mike.Mike,
What is the database recovery model? Is the log space unrestricted? Do you
take log backups?
If this is a Full recovery model DB then take a log backup. If the log in
unrestricted filegrowth give it a maximum size, just bigger than it is now.
Then you need to follow this: - http://support.microsoft.com/kb/873235/en-us
Chris
"Mike Owen" <whatnospam@.nospam.nospam> wrote in message
news:8CFFFB57-6B03-4DA3-95E7-BFD6B27C95A3@.microsoft.com...
> Hi,
> I have just come across a bit of a problem of my Windows 200 server
> running
> SQL Server 2000....I am running out of space!
> I have aout 2GB left. I have know this for some time, and for technical
> reasons just found out I can't add a new disk...but that's another story.
> I have looked at what I can reduce to increase the space, and it seems one
> of the smaller database I have on the server, i.e. 16MB, has a log file of
> 4.8GB!
> I am not sure why this has occured, possibly due to not setting a
> checkpoint
> during backup or something!?!?!
> ...anyway I am desperately tying to reduce the size of this file, and have
> tried running the following:
> USE Comms
> GO
> DBCC SHRINKFILE (Comms_log, 10)
> and it just comes back and tells me:
> - CurrentSize 608616
> - Minimum Size 63
> - UsedPages 608616
> - EstimatedPages 56
> with no reduction in LogFile size.
> I also tried backing up just the databse and then restoring it under a
> different name (so I can completely delete the old one and it's log!), but
> when I try to restore it the system just comes back and tells me it hasn't
> got enough space, even though it has just over 2GB (presumably it is
> trying,
> or thinks it is trying to restore the log file as well, but goodness knows
> where it's getting it from?!).
> Any ideas?
> Cheers, desperate Mike.
>|||Hi Mike,
Just do a transaction log backup and then try the shrink operation again.
If that does not work check if you have transactions or jobs running for a
long time.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
> Hi,
> I have just come across a bit of a problem of my Windows 200 server running
> SQL Server 2000....I am running out of space!
> I have aout 2GB left. I have know this for some time, and for technical
> reasons just found out I can't add a new disk...but that's another story.
> I have looked at what I can reduce to increase the space, and it seems one
> of the smaller database I have on the server, i.e. 16MB, has a log file of
> 4.8GB!
> I am not sure why this has occured, possibly due to not setting a checkpoint
> during backup or something!?!?!
> ...anyway I am desperately tying to reduce the size of this file, and have
> tried running the following:
> USE Comms
> GO
> DBCC SHRINKFILE (Comms_log, 10)
> and it just comes back and tells me:
> - CurrentSize 608616
> - Minimum Size 63
> - UsedPages 608616
> - EstimatedPages 56
> with no reduction in LogFile size.
> I also tried backing up just the databse and then restoring it under a
> different name (so I can completely delete the old one and it's log!), but
> when I try to restore it the system just comes back and tells me it hasn't
> got enough space, even though it has just over 2GB (presumably it is trying,
> or thinks it is trying to restore the log file as well, but goodness knows
> where it's getting it from?!).
> Any ideas?
> Cheers, desperate Mike.
>|||Thanks for you reply Ben, and you Chris. I don't think I can take a log file
backup because I can oly do it on the local server and I haven't the space,
i.e. 2GB space and 4GB+ log file.
I have got a netwrok drive to a different server with space, but the SQL
server backp program doesn't see netwrok drives.
Any other ideas or a way I can backup to the network drive?
Cheers, Mike.
"Ben Nevarez" wrote:
> Hi Mike,
> Just do a transaction log backup and then try the shrink operation again.
> If that does not work check if you have transactions or jobs running for a
> long time.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
> > Hi,
> >
> > I have just come across a bit of a problem of my Windows 200 server running
> > SQL Server 2000....I am running out of space!
> >
> > I have aout 2GB left. I have know this for some time, and for technical
> > reasons just found out I can't add a new disk...but that's another story.
> >
> > I have looked at what I can reduce to increase the space, and it seems one
> > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > 4.8GB!
> >
> > I am not sure why this has occured, possibly due to not setting a checkpoint
> > during backup or something!?!?!
> >
> > ...anyway I am desperately tying to reduce the size of this file, and have
> > tried running the following:
> >
> > USE Comms
> > GO
> > DBCC SHRINKFILE (Comms_log, 10)
> >
> > and it just comes back and tells me:
> > - CurrentSize 608616
> > - Minimum Size 63
> > - UsedPages 608616
> > - EstimatedPages 56
> >
> > with no reduction in LogFile size.
> >
> > I also tried backing up just the databse and then restoring it under a
> > different name (so I can completely delete the old one and it's log!), but
> > when I try to restore it the system just comes back and tells me it hasn't
> > got enough space, even though it has just over 2GB (presumably it is trying,
> > or thinks it is trying to restore the log file as well, but goodness knows
> > where it's getting it from?!).
> >
> > Any ideas?
> >
> > Cheers, desperate Mike.
> >|||If you do not have enough disk space then use the TRUNCATE_ONLY option like
in
backup log my_db with truncate_only
We are supposing you are not running in the SIMPLE recovery model. Is this
correct?
If that works, you must do a database backup at the end because by using
TRUNCATE_ONLY you are breaking the log chain.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
> Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> backup because I can oly do it on the local server and I haven't the space,
> i.e. 2GB space and 4GB+ log file.
> I have got a netwrok drive to a different server with space, but the SQL
> server backp program doesn't see netwrok drives.
> Any other ideas or a way I can backup to the network drive?
>
> Cheers, Mike.
> "Ben Nevarez" wrote:
> >
> > Hi Mike,
> >
> > Just do a transaction log backup and then try the shrink operation again.
> >
> > If that does not work check if you have transactions or jobs running for a
> > long time.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Mike Owen" wrote:
> >
> > > Hi,
> > >
> > > I have just come across a bit of a problem of my Windows 200 server running
> > > SQL Server 2000....I am running out of space!
> > >
> > > I have aout 2GB left. I have know this for some time, and for technical
> > > reasons just found out I can't add a new disk...but that's another story.
> > >
> > > I have looked at what I can reduce to increase the space, and it seems one
> > > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > > 4.8GB!
> > >
> > > I am not sure why this has occured, possibly due to not setting a checkpoint
> > > during backup or something!?!?!
> > >
> > > ...anyway I am desperately tying to reduce the size of this file, and have
> > > tried running the following:
> > >
> > > USE Comms
> > > GO
> > > DBCC SHRINKFILE (Comms_log, 10)
> > >
> > > and it just comes back and tells me:
> > > - CurrentSize 608616
> > > - Minimum Size 63
> > > - UsedPages 608616
> > > - EstimatedPages 56
> > >
> > > with no reduction in LogFile size.
> > >
> > > I also tried backing up just the databse and then restoring it under a
> > > different name (so I can completely delete the old one and it's log!), but
> > > when I try to restore it the system just comes back and tells me it hasn't
> > > got enough space, even though it has just over 2GB (presumably it is trying,
> > > or thinks it is trying to restore the log file as well, but goodness knows
> > > where it's getting it from?!).
> > >
> > > Any ideas?
> > >
> > > Cheers, desperate Mike.
> > >|||Excuse my ignorance - It knows no bounds!
How do I know if I am using the SIMPLE recovery model?
I am quite happy to use: backup log my_db with truncate_only
The database isn't used very much, and I only backed it up about 1 1/2 hours
ago, so as long as I can recover that should something go wrong that would be
fine....but perhaps that isn't possible with the backup I have?!!?
Cheers, Mike.
"Ben Nevarez" wrote:
> If you do not have enough disk space then use the TRUNCATE_ONLY option like
> in
> backup log my_db with truncate_only
> We are supposing you are not running in the SIMPLE recovery model. Is this
> correct?
> If that works, you must do a database backup at the end because by using
> TRUNCATE_ONLY you are breaking the log chain.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
> > Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> > backup because I can oly do it on the local server and I haven't the space,
> > i.e. 2GB space and 4GB+ log file.
> >
> > I have got a netwrok drive to a different server with space, but the SQL
> > server backp program doesn't see netwrok drives.
> >
> > Any other ideas or a way I can backup to the network drive?
> >
> >
> > Cheers, Mike.
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > Hi Mike,
> > >
> > > Just do a transaction log backup and then try the shrink operation again.
> > >
> > > If that does not work check if you have transactions or jobs running for a
> > > long time.
> > >
> > > Hope this helps,
> > >
> > > Ben Nevarez
> > > Senior Database Administrator
> > > AIG SunAmerica
> > >
> > >
> > >
> > > "Mike Owen" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have just come across a bit of a problem of my Windows 200 server running
> > > > SQL Server 2000....I am running out of space!
> > > >
> > > > I have aout 2GB left. I have know this for some time, and for technical
> > > > reasons just found out I can't add a new disk...but that's another story.
> > > >
> > > > I have looked at what I can reduce to increase the space, and it seems one
> > > > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > > > 4.8GB!
> > > >
> > > > I am not sure why this has occured, possibly due to not setting a checkpoint
> > > > during backup or something!?!?!
> > > >
> > > > ...anyway I am desperately tying to reduce the size of this file, and have
> > > > tried running the following:
> > > >
> > > > USE Comms
> > > > GO
> > > > DBCC SHRINKFILE (Comms_log, 10)
> > > >
> > > > and it just comes back and tells me:
> > > > - CurrentSize 608616
> > > > - Minimum Size 63
> > > > - UsedPages 608616
> > > > - EstimatedPages 56
> > > >
> > > > with no reduction in LogFile size.
> > > >
> > > > I also tried backing up just the databse and then restoring it under a
> > > > different name (so I can completely delete the old one and it's log!), but
> > > > when I try to restore it the system just comes back and tells me it hasn't
> > > > got enough space, even though it has just over 2GB (presumably it is trying,
> > > > or thinks it is trying to restore the log file as well, but goodness knows
> > > > where it's getting it from?!).
> > > >
> > > > Any ideas?
> > > >
> > > > Cheers, desperate Mike.
> > > >|||Looks like you are not using the SIMPLE recovery model but try this
select databasepropertyex('my_db', 'recovery')
And also try the backup log with truncate_only and then shrink the log files.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
> Excuse my ignorance - It knows no bounds!
> How do I know if I am using the SIMPLE recovery model?
> I am quite happy to use: backup log my_db with truncate_only
> The database isn't used very much, and I only backed it up about 1 1/2 hours
> ago, so as long as I can recover that should something go wrong that would be
> fine....but perhaps that isn't possible with the backup I have?!!?
>
> Cheers, Mike.
> "Ben Nevarez" wrote:
> >
> > If you do not have enough disk space then use the TRUNCATE_ONLY option like
> > in
> >
> > backup log my_db with truncate_only
> >
> > We are supposing you are not running in the SIMPLE recovery model. Is this
> > correct?
> >
> > If that works, you must do a database backup at the end because by using
> > TRUNCATE_ONLY you are breaking the log chain.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Mike Owen" wrote:
> >
> > > Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> > > backup because I can oly do it on the local server and I haven't the space,
> > > i.e. 2GB space and 4GB+ log file.
> > >
> > > I have got a netwrok drive to a different server with space, but the SQL
> > > server backp program doesn't see netwrok drives.
> > >
> > > Any other ideas or a way I can backup to the network drive?
> > >
> > >
> > > Cheers, Mike.
> > >
> > > "Ben Nevarez" wrote:
> > >
> > > >
> > > > Hi Mike,
> > > >
> > > > Just do a transaction log backup and then try the shrink operation again.
> > > >
> > > > If that does not work check if you have transactions or jobs running for a
> > > > long time.
> > > >
> > > > Hope this helps,
> > > >
> > > > Ben Nevarez
> > > > Senior Database Administrator
> > > > AIG SunAmerica
> > > >
> > > >
> > > >
> > > > "Mike Owen" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have just come across a bit of a problem of my Windows 200 server running
> > > > > SQL Server 2000....I am running out of space!
> > > > >
> > > > > I have aout 2GB left. I have know this for some time, and for technical
> > > > > reasons just found out I can't add a new disk...but that's another story.
> > > > >
> > > > > I have looked at what I can reduce to increase the space, and it seems one
> > > > > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > > > > 4.8GB!
> > > > >
> > > > > I am not sure why this has occured, possibly due to not setting a checkpoint
> > > > > during backup or something!?!?!
> > > > >
> > > > > ...anyway I am desperately tying to reduce the size of this file, and have
> > > > > tried running the following:
> > > > >
> > > > > USE Comms
> > > > > GO
> > > > > DBCC SHRINKFILE (Comms_log, 10)
> > > > >
> > > > > and it just comes back and tells me:
> > > > > - CurrentSize 608616
> > > > > - Minimum Size 63
> > > > > - UsedPages 608616
> > > > > - EstimatedPages 56
> > > > >
> > > > > with no reduction in LogFile size.
> > > > >
> > > > > I also tried backing up just the databse and then restoring it under a
> > > > > different name (so I can completely delete the old one and it's log!), but
> > > > > when I try to restore it the system just comes back and tells me it hasn't
> > > > > got enough space, even though it has just over 2GB (presumably it is trying,
> > > > > or thinks it is trying to restore the log file as well, but goodness knows
> > > > > where it's getting it from?!).
> > > > >
> > > > > Any ideas?
> > > > >
> > > > > Cheers, desperate Mike.
> > > > >|||The answer to select databasepropertyex('my_db', 'recovery')
is FULL.
I tried the '...try the backup log with truncate_only and then shrink the
log files' as you suggested and it WORKED!
I am now back to 7GB of space, plenty of room for growth for all the DB's,
and have restricted the size of all log files on the server.
Thanks for all your help, you got me out of a scrape!
Have a good weekend, cheers, Mike.
"Ben Nevarez" wrote:
> Looks like you are not using the SIMPLE recovery model but try this
> select databasepropertyex('my_db', 'recovery')
> And also try the backup log with truncate_only and then shrink the log files.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
> > Excuse my ignorance - It knows no bounds!
> >
> > How do I know if I am using the SIMPLE recovery model?
> >
> > I am quite happy to use: backup log my_db with truncate_only
> >
> > The database isn't used very much, and I only backed it up about 1 1/2 hours
> > ago, so as long as I can recover that should something go wrong that would be
> > fine....but perhaps that isn't possible with the backup I have?!!?
> >
> >
> > Cheers, Mike.
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > If you do not have enough disk space then use the TRUNCATE_ONLY option like
> > > in
> > >
> > > backup log my_db with truncate_only
> > >
> > > We are supposing you are not running in the SIMPLE recovery model. Is this
> > > correct?
> > >
> > > If that works, you must do a database backup at the end because by using
> > > TRUNCATE_ONLY you are breaking the log chain.
> > >
> > > Hope this helps,
> > >
> > > Ben Nevarez
> > > Senior Database Administrator
> > > AIG SunAmerica
> > >
> > >
> > >
> > > "Mike Owen" wrote:
> > >
> > > > Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> > > > backup because I can oly do it on the local server and I haven't the space,
> > > > i.e. 2GB space and 4GB+ log file.
> > > >
> > > > I have got a netwrok drive to a different server with space, but the SQL
> > > > server backp program doesn't see netwrok drives.
> > > >
> > > > Any other ideas or a way I can backup to the network drive?
> > > >
> > > >
> > > > Cheers, Mike.
> > > >
> > > > "Ben Nevarez" wrote:
> > > >
> > > > >
> > > > > Hi Mike,
> > > > >
> > > > > Just do a transaction log backup and then try the shrink operation again.
> > > > >
> > > > > If that does not work check if you have transactions or jobs running for a
> > > > > long time.
> > > > >
> > > > > Hope this helps,
> > > > >
> > > > > Ben Nevarez
> > > > > Senior Database Administrator
> > > > > AIG SunAmerica
> > > > >
> > > > >
> > > > >
> > > > > "Mike Owen" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have just come across a bit of a problem of my Windows 200 server running
> > > > > > SQL Server 2000....I am running out of space!
> > > > > >
> > > > > > I have aout 2GB left. I have know this for some time, and for technical
> > > > > > reasons just found out I can't add a new disk...but that's another story.
> > > > > >
> > > > > > I have looked at what I can reduce to increase the space, and it seems one
> > > > > > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > > > > > 4.8GB!
> > > > > >
> > > > > > I am not sure why this has occured, possibly due to not setting a checkpoint
> > > > > > during backup or something!?!?!
> > > > > >
> > > > > > ...anyway I am desperately tying to reduce the size of this file, and have
> > > > > > tried running the following:
> > > > > >
> > > > > > USE Comms
> > > > > > GO
> > > > > > DBCC SHRINKFILE (Comms_log, 10)
> > > > > >
> > > > > > and it just comes back and tells me:
> > > > > > - CurrentSize 608616
> > > > > > - Minimum Size 63
> > > > > > - UsedPages 608616
> > > > > > - EstimatedPages 56
> > > > > >
> > > > > > with no reduction in LogFile size.
> > > > > >
> > > > > > I also tried backing up just the databse and then restoring it under a
> > > > > > different name (so I can completely delete the old one and it's log!), but
> > > > > > when I try to restore it the system just comes back and tells me it hasn't
> > > > > > got enough space, even though it has just over 2GB (presumably it is trying,
> > > > > > or thinks it is trying to restore the log file as well, but goodness knows
> > > > > > where it's getting it from?!).
> > > > > >
> > > > > > Any ideas?
> > > > > >
> > > > > > Cheers, desperate Mike.
> > > > > >|||Be careful about restricting the size of the log files ... SQL Server may
need this disk space.
Instead I would recommend scheduling transaction log backups periodically,
minimum daily. This will allow SQL Server to reuse the disk space used by the
transaction log files.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
> The answer to select databasepropertyex('my_db', 'recovery')
> is FULL.
> I tried the '...try the backup log with truncate_only and then shrink the
> log files' as you suggested and it WORKED!
> I am now back to 7GB of space, plenty of room for growth for all the DB's,
> and have restricted the size of all log files on the server.
> Thanks for all your help, you got me out of a scrape!
> Have a good weekend, cheers, Mike.
>
> "Ben Nevarez" wrote:
> >
> > Looks like you are not using the SIMPLE recovery model but try this
> >
> > select databasepropertyex('my_db', 'recovery')
> >
> > And also try the backup log with truncate_only and then shrink the log files.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Mike Owen" wrote:
> >
> > > Excuse my ignorance - It knows no bounds!
> > >
> > > How do I know if I am using the SIMPLE recovery model?
> > >
> > > I am quite happy to use: backup log my_db with truncate_only
> > >
> > > The database isn't used very much, and I only backed it up about 1 1/2 hours
> > > ago, so as long as I can recover that should something go wrong that would be
> > > fine....but perhaps that isn't possible with the backup I have?!!?
> > >
> > >
> > > Cheers, Mike.
> > >
> > > "Ben Nevarez" wrote:
> > >
> > > >
> > > > If you do not have enough disk space then use the TRUNCATE_ONLY option like
> > > > in
> > > >
> > > > backup log my_db with truncate_only
> > > >
> > > > We are supposing you are not running in the SIMPLE recovery model. Is this
> > > > correct?
> > > >
> > > > If that works, you must do a database backup at the end because by using
> > > > TRUNCATE_ONLY you are breaking the log chain.
> > > >
> > > > Hope this helps,
> > > >
> > > > Ben Nevarez
> > > > Senior Database Administrator
> > > > AIG SunAmerica
> > > >
> > > >
> > > >
> > > > "Mike Owen" wrote:
> > > >
> > > > > Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> > > > > backup because I can oly do it on the local server and I haven't the space,
> > > > > i.e. 2GB space and 4GB+ log file.
> > > > >
> > > > > I have got a netwrok drive to a different server with space, but the SQL
> > > > > server backp program doesn't see netwrok drives.
> > > > >
> > > > > Any other ideas or a way I can backup to the network drive?
> > > > >
> > > > >
> > > > > Cheers, Mike.
> > > > >
> > > > > "Ben Nevarez" wrote:
> > > > >
> > > > > >
> > > > > > Hi Mike,
> > > > > >
> > > > > > Just do a transaction log backup and then try the shrink operation again.
> > > > > >
> > > > > > If that does not work check if you have transactions or jobs running for a
> > > > > > long time.
> > > > > >
> > > > > > Hope this helps,
> > > > > >
> > > > > > Ben Nevarez
> > > > > > Senior Database Administrator
> > > > > > AIG SunAmerica
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Mike Owen" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I have just come across a bit of a problem of my Windows 200 server running
> > > > > > > SQL Server 2000....I am running out of space!
> > > > > > >
> > > > > > > I have aout 2GB left. I have know this for some time, and for technical
> > > > > > > reasons just found out I can't add a new disk...but that's another story.
> > > > > > >
> > > > > > > I have looked at what I can reduce to increase the space, and it seems one
> > > > > > > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > > > > > > 4.8GB!
> > > > > > >
> > > > > > > I am not sure why this has occured, possibly due to not setting a checkpoint
> > > > > > > during backup or something!?!?!
> > > > > > >
> > > > > > > ...anyway I am desperately tying to reduce the size of this file, and have
> > > > > > > tried running the following:
> > > > > > >
> > > > > > > USE Comms
> > > > > > > GO
> > > > > > > DBCC SHRINKFILE (Comms_log, 10)
> > > > > > >
> > > > > > > and it just comes back and tells me:
> > > > > > > - CurrentSize 608616
> > > > > > > - Minimum Size 63
> > > > > > > - UsedPages 608616
> > > > > > > - EstimatedPages 56
> > > > > > >
> > > > > > > with no reduction in LogFile size.
> > > > > > >
> > > > > > > I also tried backing up just the databse and then restoring it under a
> > > > > > > different name (so I can completely delete the old one and it's log!), but
> > > > > > > when I try to restore it the system just comes back and tells me it hasn't
> > > > > > > got enough space, even though it has just over 2GB (presumably it is trying,
> > > > > > > or thinks it is trying to restore the log file as well, but goodness knows
> > > > > > > where it's getting it from?!).
> > > > > > >
> > > > > > > Any ideas?
> > > > > > >
> > > > > > > Cheers, desperate Mike.
> > > > > > >|||You can also create an alert that will run the log backup if you fill it
over a certain percent. Providing that you do not have any very large
transactions this should keep you safe.
Chris
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:7BBF5A0F-45E0-4493-9A95-F50FF7D05D20@.microsoft.com...
> Be careful about restricting the size of the log files ... SQL Server may
> need this disk space.
> Instead I would recommend scheduling transaction log backups periodically,
> minimum daily. This will allow SQL Server to reuse the disk space used by
> the
> transaction log files.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
>> The answer to select databasepropertyex('my_db', 'recovery')
>> is FULL.
>> I tried the '...try the backup log with truncate_only and then shrink
>> the
>> log files' as you suggested and it WORKED!
>> I am now back to 7GB of space, plenty of room for growth for all the
>> DB's,
>> and have restricted the size of all log files on the server.
>> Thanks for all your help, you got me out of a scrape!
>> Have a good weekend, cheers, Mike.
>>
>> "Ben Nevarez" wrote:
>> >
>> > Looks like you are not using the SIMPLE recovery model but try this
>> >
>> > select databasepropertyex('my_db', 'recovery')
>> >
>> > And also try the backup log with truncate_only and then shrink the log
>> > files.
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> > Senior Database Administrator
>> > AIG SunAmerica
>> >
>> >
>> >
>> > "Mike Owen" wrote:
>> >
>> > > Excuse my ignorance - It knows no bounds!
>> > >
>> > > How do I know if I am using the SIMPLE recovery model?
>> > >
>> > > I am quite happy to use: backup log my_db with truncate_only
>> > >
>> > > The database isn't used very much, and I only backed it up about 1
>> > > 1/2 hours
>> > > ago, so as long as I can recover that should something go wrong that
>> > > would be
>> > > fine....but perhaps that isn't possible with the backup I have?!!?
>> > >
>> > >
>> > > Cheers, Mike.
>> > >
>> > > "Ben Nevarez" wrote:
>> > >
>> > > >
>> > > > If you do not have enough disk space then use the TRUNCATE_ONLY
>> > > > option like
>> > > > in
>> > > >
>> > > > backup log my_db with truncate_only
>> > > >
>> > > > We are supposing you are not running in the SIMPLE recovery model.
>> > > > Is this
>> > > > correct?
>> > > >
>> > > > If that works, you must do a database backup at the end because by
>> > > > using
>> > > > TRUNCATE_ONLY you are breaking the log chain.
>> > > >
>> > > > Hope this helps,
>> > > >
>> > > > Ben Nevarez
>> > > > Senior Database Administrator
>> > > > AIG SunAmerica
>> > > >
>> > > >
>> > > >
>> > > > "Mike Owen" wrote:
>> > > >
>> > > > > Thanks for you reply Ben, and you Chris. I don't think I can
>> > > > > take a log file
>> > > > > backup because I can oly do it on the local server and I haven't
>> > > > > the space,
>> > > > > i.e. 2GB space and 4GB+ log file.
>> > > > >
>> > > > > I have got a netwrok drive to a different server with space, but
>> > > > > the SQL
>> > > > > server backp program doesn't see netwrok drives.
>> > > > >
>> > > > > Any other ideas or a way I can backup to the network drive?
>> > > > >
>> > > > >
>> > > > > Cheers, Mike.
>> > > > >
>> > > > > "Ben Nevarez" wrote:
>> > > > >
>> > > > > >
>> > > > > > Hi Mike,
>> > > > > >
>> > > > > > Just do a transaction log backup and then try the shrink
>> > > > > > operation again.
>> > > > > >
>> > > > > > If that does not work check if you have transactions or jobs
>> > > > > > running for a
>> > > > > > long time.
>> > > > > >
>> > > > > > Hope this helps,
>> > > > > >
>> > > > > > Ben Nevarez
>> > > > > > Senior Database Administrator
>> > > > > > AIG SunAmerica
>> > > > > >
>> > > > > >
>> > > > > >
>> > > > > > "Mike Owen" wrote:
>> > > > > >
>> > > > > > > Hi,
>> > > > > > >
>> > > > > > > I have just come across a bit of a problem of my Windows 200
>> > > > > > > server running
>> > > > > > > SQL Server 2000....I am running out of space!
>> > > > > > >
>> > > > > > > I have aout 2GB left. I have know this for some time, and
>> > > > > > > for technical
>> > > > > > > reasons just found out I can't add a new disk...but that's
>> > > > > > > another story.
>> > > > > > >
>> > > > > > > I have looked at what I can reduce to increase the space, and
>> > > > > > > it seems one
>> > > > > > > of the smaller database I have on the server, i.e. 16MB, has
>> > > > > > > a log file of
>> > > > > > > 4.8GB!
>> > > > > > >
>> > > > > > > I am not sure why this has occured, possibly due to not
>> > > > > > > setting a checkpoint
>> > > > > > > during backup or something!?!?!
>> > > > > > >
>> > > > > > > ...anyway I am desperately tying to reduce the size of this
>> > > > > > > file, and have
>> > > > > > > tried running the following:
>> > > > > > >
>> > > > > > > USE Comms
>> > > > > > > GO
>> > > > > > > DBCC SHRINKFILE (Comms_log, 10)
>> > > > > > >
>> > > > > > > and it just comes back and tells me:
>> > > > > > > - CurrentSize 608616
>> > > > > > > - Minimum Size 63
>> > > > > > > - UsedPages 608616
>> > > > > > > - EstimatedPages 56
>> > > > > > >
>> > > > > > > with no reduction in LogFile size.
>> > > > > > >
>> > > > > > > I also tried backing up just the databse and then restoring
>> > > > > > > it under a
>> > > > > > > different name (so I can completely delete the old one and
>> > > > > > > it's log!), but
>> > > > > > > when I try to restore it the system just comes back and tells
>> > > > > > > me it hasn't
>> > > > > > > got enough space, even though it has just over 2GB
>> > > > > > > (presumably it is trying,
>> > > > > > > or thinks it is trying to restore the log file as well, but
>> > > > > > > goodness knows
>> > > > > > > where it's getting it from?!).
>> > > > > > >
>> > > > > > > Any ideas?
>> > > > > > >
>> > > > > > > Cheers, desperate Mike.
>> > > > > > >
Live server running out of space - Can't reduce Transaction Log!
Hi,
I have just come across a bit of a problem of my Windows 200 server running
SQL Server 2000....I am running out of space!
I have aout 2GB left. I have know this for some time, and for technical
reasons just found out I can't add a new disk...but that's another story.
I have looked at what I can reduce to increase the space, and it seems one
of the smaller database I have on the server, i.e. 16MB, has a log file of
4.8GB!
I am not sure why this has occured, possibly due to not setting a checkpoint
during backup or something!?!?!
...anyway I am desperately tying to reduce the size of this file, and have
tried running the following:
USE Comms
GO
DBCC SHRINKFILE (Comms_log, 10)
and it just comes back and tells me:
- CurrentSize 608616
- Minimum Size 63
- UsedPages 608616
- EstimatedPages 56
with no reduction in LogFile size.
I also tried backing up just the databse and then restoring it under a
different name (so I can completely delete the old one and it's log!), but
when I try to restore it the system just comes back and tells me it hasn't
got enough space, even though it has just over 2GB (presumably it is trying,
or thinks it is trying to restore the log file as well, but goodness knows
where it's getting it from?!).
Any ideas?
Cheers, desperate Mike.Mike,
What is the database recovery model? Is the log space unrestricted? Do you
take log backups?
If this is a Full recovery model DB then take a log backup. If the log in
unrestricted filegrowth give it a maximum size, just bigger than it is now.
Then you need to follow this: - http://support.microsoft.com/kb/873235/en-us
Chris
"Mike Owen" <whatnospam@.nospam.nospam> wrote in message
news:8CFFFB57-6B03-4DA3-95E7-BFD6B27C95A3@.microsoft.com...
> Hi,
> I have just come across a bit of a problem of my Windows 200 server
> running
> SQL Server 2000....I am running out of space!
> I have aout 2GB left. I have know this for some time, and for technical
> reasons just found out I can't add a new disk...but that's another story.
> I have looked at what I can reduce to increase the space, and it seems one
> of the smaller database I have on the server, i.e. 16MB, has a log file of
> 4.8GB!
> I am not sure why this has occured, possibly due to not setting a
> checkpoint
> during backup or something!?!?!
> ...anyway I am desperately tying to reduce the size of this file, and have
> tried running the following:
> USE Comms
> GO
> DBCC SHRINKFILE (Comms_log, 10)
> and it just comes back and tells me:
> - CurrentSize 608616
> - Minimum Size 63
> - UsedPages 608616
> - EstimatedPages 56
> with no reduction in LogFile size.
> I also tried backing up just the databse and then restoring it under a
> different name (so I can completely delete the old one and it's log!), but
> when I try to restore it the system just comes back and tells me it hasn't
> got enough space, even though it has just over 2GB (presumably it is
> trying,
> or thinks it is trying to restore the log file as well, but goodness knows
> where it's getting it from?!).
> Any ideas?
> Cheers, desperate Mike.
>|||Hi Mike,
Just do a transaction log backup and then try the shrink operation again.
If that does not work check if you have transactions or jobs running for a
long time.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
> Hi,
> I have just come across a bit of a problem of my Windows 200 server runnin
g
> SQL Server 2000....I am running out of space!
> I have aout 2GB left. I have know this for some time, and for technical
> reasons just found out I can't add a new disk...but that's another story.
> I have looked at what I can reduce to increase the space, and it seems one
> of the smaller database I have on the server, i.e. 16MB, has a log file of
> 4.8GB!
> I am not sure why this has occured, possibly due to not setting a checkpoi
nt
> during backup or something!?!?!
> ...anyway I am desperately tying to reduce the size of this file, and have
> tried running the following:
> USE Comms
> GO
> DBCC SHRINKFILE (Comms_log, 10)
> and it just comes back and tells me:
> - CurrentSize 608616
> - Minimum Size 63
> - UsedPages 608616
> - EstimatedPages 56
> with no reduction in LogFile size.
> I also tried backing up just the databse and then restoring it under a
> different name (so I can completely delete the old one and it's log!), but
> when I try to restore it the system just comes back and tells me it hasn't
> got enough space, even though it has just over 2GB (presumably it is tryin
g,
> or thinks it is trying to restore the log file as well, but goodness knows
> where it's getting it from?!).
> Any ideas?
> Cheers, desperate Mike.
>
I have just come across a bit of a problem of my Windows 200 server running
SQL Server 2000....I am running out of space!
I have aout 2GB left. I have know this for some time, and for technical
reasons just found out I can't add a new disk...but that's another story.
I have looked at what I can reduce to increase the space, and it seems one
of the smaller database I have on the server, i.e. 16MB, has a log file of
4.8GB!
I am not sure why this has occured, possibly due to not setting a checkpoint
during backup or something!?!?!
...anyway I am desperately tying to reduce the size of this file, and have
tried running the following:
USE Comms
GO
DBCC SHRINKFILE (Comms_log, 10)
and it just comes back and tells me:
- CurrentSize 608616
- Minimum Size 63
- UsedPages 608616
- EstimatedPages 56
with no reduction in LogFile size.
I also tried backing up just the databse and then restoring it under a
different name (so I can completely delete the old one and it's log!), but
when I try to restore it the system just comes back and tells me it hasn't
got enough space, even though it has just over 2GB (presumably it is trying,
or thinks it is trying to restore the log file as well, but goodness knows
where it's getting it from?!).
Any ideas?
Cheers, desperate Mike.Mike,
What is the database recovery model? Is the log space unrestricted? Do you
take log backups?
If this is a Full recovery model DB then take a log backup. If the log in
unrestricted filegrowth give it a maximum size, just bigger than it is now.
Then you need to follow this: - http://support.microsoft.com/kb/873235/en-us
Chris
"Mike Owen" <whatnospam@.nospam.nospam> wrote in message
news:8CFFFB57-6B03-4DA3-95E7-BFD6B27C95A3@.microsoft.com...
> Hi,
> I have just come across a bit of a problem of my Windows 200 server
> running
> SQL Server 2000....I am running out of space!
> I have aout 2GB left. I have know this for some time, and for technical
> reasons just found out I can't add a new disk...but that's another story.
> I have looked at what I can reduce to increase the space, and it seems one
> of the smaller database I have on the server, i.e. 16MB, has a log file of
> 4.8GB!
> I am not sure why this has occured, possibly due to not setting a
> checkpoint
> during backup or something!?!?!
> ...anyway I am desperately tying to reduce the size of this file, and have
> tried running the following:
> USE Comms
> GO
> DBCC SHRINKFILE (Comms_log, 10)
> and it just comes back and tells me:
> - CurrentSize 608616
> - Minimum Size 63
> - UsedPages 608616
> - EstimatedPages 56
> with no reduction in LogFile size.
> I also tried backing up just the databse and then restoring it under a
> different name (so I can completely delete the old one and it's log!), but
> when I try to restore it the system just comes back and tells me it hasn't
> got enough space, even though it has just over 2GB (presumably it is
> trying,
> or thinks it is trying to restore the log file as well, but goodness knows
> where it's getting it from?!).
> Any ideas?
> Cheers, desperate Mike.
>|||Hi Mike,
Just do a transaction log backup and then try the shrink operation again.
If that does not work check if you have transactions or jobs running for a
long time.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
> Hi,
> I have just come across a bit of a problem of my Windows 200 server runnin
g
> SQL Server 2000....I am running out of space!
> I have aout 2GB left. I have know this for some time, and for technical
> reasons just found out I can't add a new disk...but that's another story.
> I have looked at what I can reduce to increase the space, and it seems one
> of the smaller database I have on the server, i.e. 16MB, has a log file of
> 4.8GB!
> I am not sure why this has occured, possibly due to not setting a checkpoi
nt
> during backup or something!?!?!
> ...anyway I am desperately tying to reduce the size of this file, and have
> tried running the following:
> USE Comms
> GO
> DBCC SHRINKFILE (Comms_log, 10)
> and it just comes back and tells me:
> - CurrentSize 608616
> - Minimum Size 63
> - UsedPages 608616
> - EstimatedPages 56
> with no reduction in LogFile size.
> I also tried backing up just the databse and then restoring it under a
> different name (so I can completely delete the old one and it's log!), but
> when I try to restore it the system just comes back and tells me it hasn't
> got enough space, even though it has just over 2GB (presumably it is tryin
g,
> or thinks it is trying to restore the log file as well, but goodness knows
> where it's getting it from?!).
> Any ideas?
> Cheers, desperate Mike.
>
Friday, March 23, 2012
Little bit confused about databases on CE
Hi there,
i need a database for my Windows CE application which i can update from a desktop application.
I tried the SqlCeConnection. This works good on the device, but i found out, that i need a sql server on the desktop or someone else to get access to the device server. This is a problem for me, because i cannot insall such a sever on the desktop.
So i searched and searched....I found infos about the ole connection, but i cant find the namespace?!?
Can anyone give me a hint what the best solution could be?
Im using Visual Studio 2005 and a CE device.
Thanks a lot
You can use VS 2005 'Add New Datasource Wizard' under Data menu OR in Server Explorer ==> Add Connection to update the database on device. Also, with introduction of SQL Server Compact/Mobile/Everywhere Edition, you can copy the database file to a desktop and you can use System.Data.SqlServerCe.dll that is installed on desktop (If not installed, download and install this http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=898913&SiteID=1).
Thanks,
Laxmi
Lite speed 2005 on 64 bit OS
We have 64 bit OS / 32 bit SQL-Server on a Windows 2003 server. Lite speed
2005 doesn't seems to work on it. Is this setting supported by Lite speed
2005 ?. I know it is NOT a good idea to have 32 bit SQL Server 2000 on 64 bit
OS but we had to do this because we did not get the Processor SQL Server 64
bit addition requires.
Any thoughts ? Thanks.Are you getting any errors?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:655307A7-3C87-492B-A213-9E2DDC1A7E2A@.microsoft.com...
We have 64 bit OS / 32 bit SQL-Server on a Windows 2003 server. Lite speed
2005 doesn't seems to work on it. Is this setting supported by Lite speed
2005 ?. I know it is NOT a good idea to have 32 bit SQL Server 2000 on 64
bit
OS but we had to do this because we did not get the Processor SQL Server 64
bit addition requires.
Any thoughts ? Thanks.
2005 doesn't seems to work on it. Is this setting supported by Lite speed
2005 ?. I know it is NOT a good idea to have 32 bit SQL Server 2000 on 64 bit
OS but we had to do this because we did not get the Processor SQL Server 64
bit addition requires.
Any thoughts ? Thanks.Are you getting any errors?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:655307A7-3C87-492B-A213-9E2DDC1A7E2A@.microsoft.com...
We have 64 bit OS / 32 bit SQL-Server on a Windows 2003 server. Lite speed
2005 doesn't seems to work on it. Is this setting supported by Lite speed
2005 ?. I know it is NOT a good idea to have 32 bit SQL Server 2000 on 64
bit
OS but we had to do this because we did not get the Processor SQL Server 64
bit addition requires.
Any thoughts ? Thanks.
Lite speed 2005 on 64 bit OS
We have 64 bit OS / 32 bit SQL-Server on a Windows 2003 server. Lite speed
2005 doesn't seems to work on it. Is this setting supported by Lite speed
2005 ?. I know it is NOT a good idea to have 32 bit SQL Server 2000 on 64 bi
t
OS but we had to do this because we did not get the Processor SQL Server 64
bit addition requires.
Any thoughts ? Thanks.Are you getting any errors?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:655307A7-3C87-492B-A213-9E2DDC1A7E2A@.microsoft.com...
We have 64 bit OS / 32 bit SQL-Server on a Windows 2003 server. Lite speed
2005 doesn't seems to work on it. Is this setting supported by Lite speed
2005 ?. I know it is NOT a good idea to have 32 bit SQL Server 2000 on 64
bit
OS but we had to do this because we did not get the Processor SQL Server 64
bit addition requires.
Any thoughts ? Thanks.
2005 doesn't seems to work on it. Is this setting supported by Lite speed
2005 ?. I know it is NOT a good idea to have 32 bit SQL Server 2000 on 64 bi
t
OS but we had to do this because we did not get the Processor SQL Server 64
bit addition requires.
Any thoughts ? Thanks.Are you getting any errors?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:655307A7-3C87-492B-A213-9E2DDC1A7E2A@.microsoft.com...
We have 64 bit OS / 32 bit SQL-Server on a Windows 2003 server. Lite speed
2005 doesn't seems to work on it. Is this setting supported by Lite speed
2005 ?. I know it is NOT a good idea to have 32 bit SQL Server 2000 on 64
bit
OS but we had to do this because we did not get the Processor SQL Server 64
bit addition requires.
Any thoughts ? Thanks.
listingreports that user has access
How can I get the list of reports that a user has only view reports access ?
the user is in windows server 2003 active directory.
reporting services 2000 is being used.
within the intranet there is a portal.
in the portal I want to list the available reports to the user
that he can access. I do not want to display all the reports to him and let
him choose. I want him to be able to see only the reports he is allowed to
see?
how can I do this using asp.net 1.1
thanx
rifatis it possible or not?
can someone answer please ?
thanx
rifatsql
the user is in windows server 2003 active directory.
reporting services 2000 is being used.
within the intranet there is a portal.
in the portal I want to list the available reports to the user
that he can access. I do not want to display all the reports to him and let
him choose. I want him to be able to see only the reports he is allowed to
see?
how can I do this using asp.net 1.1
thanx
rifatis it possible or not?
can someone answer please ?
thanx
rifatsql
Wednesday, March 21, 2012
Listing members of NT Group login
Does anybody have any experience with listing all the members who belong to
a
login that is a Windows Authenticated group using SQL Server 2k. My hunch i
s
that I will probably have to write some .net code using the activedirectory
namespace. anybody'exec xp_logininfo 'domain\group','members'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
wrote in message news:4D2CBEAB-6E96-46CE-B088-D7EBDCACAA11@.microsoft.com...
> Does anybody have any experience with listing all the members who belong
> to a
> login that is a Windows Authenticated group using SQL Server 2k. My hunch
> is
> that I will probably have to write some .net code using the
> activedirectory
> namespace. anybody'|||Thanks - I took it a little further because I needed the results in a table
-
create table ##li(
an nchar(128),
ty char(8),
priv char(9),
mln nchar(128),
pp nchar(128))
INSERT INTO ##li
EXEC xp_logininfo 'SAIC-US-WEST\CorporateITSDBA_Admins', 'members'
select * from ##li
"Jasper Smith" wrote:
> exec xp_logininfo 'domain\group','members'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
> wrote in message news:4D2CBEAB-6E96-46CE-B088-D7EBDCACAA11@.microsoft.com..
.
>
>
a
login that is a Windows Authenticated group using SQL Server 2k. My hunch i
s
that I will probably have to write some .net code using the activedirectory
namespace. anybody'exec xp_logininfo 'domain\group','members'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
wrote in message news:4D2CBEAB-6E96-46CE-B088-D7EBDCACAA11@.microsoft.com...
> Does anybody have any experience with listing all the members who belong
> to a
> login that is a Windows Authenticated group using SQL Server 2k. My hunch
> is
> that I will probably have to write some .net code using the
> activedirectory
> namespace. anybody'|||Thanks - I took it a little further because I needed the results in a table
-
create table ##li(
an nchar(128),
ty char(8),
priv char(9),
mln nchar(128),
pp nchar(128))
INSERT INTO ##li
EXEC xp_logininfo 'SAIC-US-WEST\CorporateITSDBA_Admins', 'members'
select * from ##li
"Jasper Smith" wrote:
> exec xp_logininfo 'domain\group','members'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
> wrote in message news:4D2CBEAB-6E96-46CE-B088-D7EBDCACAA11@.microsoft.com..
.
>
>
Subscribe to:
Posts (Atom)