reduce databse size: running

Showing posts with label running. Show all posts

Friday, March 30, 2012

Regarding Full-Text search

Hello,
we have full-text search service running under local system account and we
removed Builtin/Administrators
from our logins and now our full-text catalogs are not getting populated and
now the only resolution i see is
adding NT Authority/System as a login and granting sysadmin privileges .are
there any pros and cons in doing this ..in terms of security..
Thanks In Advance!
No, basically the reason dba's remove the builtin\admin account is to ensure
that NT admin's are automatically system admins on the SQL Server box. NT
Authority/System merely gives the system account rights to SQL Server which
MSSearch requires to contact SQL Server and vice versa. With NT
Authority/System the NT admin's can no longer automatically administer your
SQL Server.
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
"msqldba m via droptable.com" <forum@.droptable.com> wrote in message
news:5111AFEC65E20@.droptable.com...
> Hello,
> we have full-text search service running under local system account and we
> removed Builtin/Administrators
> from our logins and now our full-text catalogs are not getting populated
and
> now the only resolution i see is
> adding NT Authority/System as a login and granting sysadmin privileges
..are
> there any pros and cons in doing this ..in terms of security..
>
> Thanks In Advance!
|||Hilary,
So there are no problems in making NT Authority/System a sysadmin(SA) in sql
server
Thanks,
Hilary Cotter wrote:[vbcol=seagreen]
>No, basically the reason dba's remove the builtin\admin account is to ensure
>that NT admin's are automatically system admins on the SQL Server box. NT
>Authority/System merely gives the system account rights to SQL Server which
>MSSearch requires to contact SQL Server and vice versa. With NT
>Authority/System the NT admin's can no longer automatically administer your
>SQL Server.
>[quoted text clipped - 6 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...earch/200507/1
|||msqldba,
No there is no problem in making NT Authority/System login have sysadmin
privileges, in fact it is required if you have removed the
BUILTIN\Administrators login as the external MSSearch services needs
sysadmin privileges to access SQL Server. Note, this is by design. You can
implement this via the following T-SQL code:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
Additionally, see KB article Q263712 "INF: How To Prevent Windows NT
Administrators From Administering a Clustered SQL Server" at
http://support.microsoft.com/default...;EN-US;q263712 as this KB
article recommends: "If a full-text search will be used on the cluster, you
must add the [NT Authority\System] account to the server's "sysadmin"
group."
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"msqldba m via droptable.com" <forum@.droptable.com> wrote in message
news:5116CE83ADBA0@.droptable.com...
> Hilary,
> So there are no problems in making NT Authority/System a sysadmin(SA) in
sql[vbcol=seagreen]
> server
> Thanks,
>
> Hilary Cotter wrote:
ensure[vbcol=seagreen]
which[vbcol=seagreen]
your
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...earch/200507/1

Regarding dblib

Try some SQL Engine specific forum, e.g. http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1

This forum is for Integration Services component, not sure if there are appropriate people to respond to this question here.

sql

Regarding dblib

hi..I am running DBLIB (sample application of SQL Server)
between 2k and 2k3. Well seems working perfect..but want
to know whats going on in the Database..I think some
DB configuration (like DB connection or Table creation)
is required to see the effect of executed/commited
transactions..need some guidence.
Thanks in advance
Umer
You shouldn't really invest any more time in DBLib, and move to some other
data access APIs (ADO.NET, OLEDB, ODBC, etc). You might be talking about
SQL2000, but consider what SQL2005 BOL says about Db-Library:
"Although the SQL Server 2005 Database Engine still supports connections
from existing applications using the DB-Library and Embedded SQL APIs, it
does not include the files or documentation needed to do programming work on
applications that use these APIs." on page
http://msdn2.microsoft.com/en-us/library/ms143729(en-US,SQL.90).aspx
Linchi
<umer.zafar@.gmail.com> wrote in message
news:1131628582.386464.75780@.g47g2000cwa.googlegro ups.com...
> hi..I am running DBLIB (sample application of SQL Server)
> between 2k and 2k3. Well seems working perfect..but want
> to know whats going on in the Database..I think some
> DB configuration (like DB connection or Table creation)
> is required to see the effect of executed/commited
> transactions..need some guidence.
> Thanks in advance
> Umer
>

Regarding dblib

hi..I am running DBLIB (sample application of SQL Server)
between 2k and 2k3. Well seems working perfect..but want
to know whats going on in the Database..I think some
DB configuration (like DB connection or Table creation)
is required to see the effect of executed/commited
transactions..need some guidence.
Thanks in advance
UmerTry running the SQL Profiler to trace the database activity.|||(umer.zafar@.gmail.com) writes:
> hi..I am running DBLIB (sample application of SQL Server)
> between 2k and 2k3. Well seems working perfect..but want
> to know whats going on in the Database..I think some
> DB configuration (like DB connection or Table creation)
> is required to see the effect of executed/commited
> transactions..need some guidence.
Difficult to understand what you mean. One very nice feature of DB-Library
is that it does not do things behind your back like those modern API are
fond of doing.
But, alas, Microsoft has put DB-Library on the deprecation list, and
DB-Library does not have support for features added to SQL 7 and later.
While you still connect to SQL 2005 with DB-Library, SQL 2005 does not
come with any files to support DB-Library. A future version of SQL Server
will not support DB-Library at all.
Thus, unless you have some legacy application to maintain, you are best
off staying away from DB-Library.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks..helping to get me my answer

Regarding dblib

Monday, March 26, 2012

Refreshing the field list

Hi all,
running RS with the serve on sp2 - have a question about refreshing the
field list in the data tab of the designer.
I have copied (and changed slightly) a stored procedure that one of our
reports runs on. All of the field names are the same, that didn't change...I
can run the SP in the designer, and was attempting to change the SP the
report uses to the newer version. I can get no fields in the field list, and
if I try to preview the report I have boatloads of errors (one for every
field, because it doesn't know where the data is).
If you create a new sp for a report to run on, but the new sp has all of the
same field names, etc. , shouldn't you be able to 'plug and play') - the
field names being returned when I run the query in the RS data tab are the
same as the fieldd names from the original query.
Cheers,Execute the SP from the Data source area and the fields will be refreshed..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Myles" <Myles@.discussions.microsoft.com> wrote in message
news:CA47B9E1-8F90-41DA-91B5-34D5F27641C2@.microsoft.com...
> Hi all,
> running RS with the serve on sp2 - have a question about refreshing the
> field list in the data tab of the designer.
> I have copied (and changed slightly) a stored procedure that one of our
> reports runs on. All of the field names are the same, that didn't
> change...I
> can run the SP in the designer, and was attempting to change the SP the
> report uses to the newer version. I can get no fields in the field list,
> and
> if I try to preview the report I have boatloads of errors (one for every
> field, because it doesn't know where the data is).
> If you create a new sp for a report to run on, but the new sp has all of
> the
> same field names, etc. , shouldn't you be able to 'plug and play') - the
> field names being returned when I run the query in the RS data tab are the
> same as the fieldd names from the original query.
>
> Cheers,
>
>

refreshing database before restart

i am running a game from a dedi box .. ms sql..

and every time i restart and put it back up ,,,,it rewinds the database

the only way i can prevent this is if i restart the server

wait a phew hours then put it back up ,,"which makes it the same way it was when i logged off"

i was told that sql database servers refresh every hour.

is there a way i can make it refresh before i restart it so then i can put the server straight back up

if i restart the server back up straight away it does a rewind for some reason.

tech ::

the server does a rewind because it wouldn't have saved the game so to speak - as in updated everyone's characters. So yes waiting is the only way as far as I know to save the game.

This must be wrapped up in the game's logic where it keeps one database transaction open for a long time (like an hour).

Proper database behavior is that if an update transaction is never committed by the application, the updates are rolled back (rewound) if for any reason the connection is broken.

There is NOTHING in SQL that limits updates to that kind of frequency.

Unfortunately, you need to talk to whoever made/runs the game. They can chat with us and we'll help them.

|||ok thanks for ypur help anyway

Refresh the properties of as server

I need to check if a server is running and then start the server if it is not running. I am having trouble with the ServiceState never updating. Ie... after I start the service I cannot check if its actually running using ServiceState.

In the following example I start a server that is stopped wait 30 seconds and then display its ServiceState it is allways comming back as stopped even when I know the service is running. If I make a new service object and set it to the server the server state property will correctly return running.

I am baffled by this any ideas on this?

Dim myComp As New ManagedComputer(System.Environment.MachineName)

Dim myservice As Service

myservice = myComp.Services("MSSQL$KBMSS")

myservice.Start()

Dim StartTime As TimeSpan = Date.Now.TimeOfDay

Do While Date.Now.TimeOfDay.Subtract(StartTime).TotalSeconds < 30

If myservice.ServiceState = ServiceState.Running Then Exit Do

Loop

MessageBox.Show(myservice.ServiceState.ToString)

Add myservice.Refresh() before the service state check and you're golden.

SMO is a cached object model, so it means you need to refresh the object state if needed.

Friday, March 23, 2012

Refresh Memory Usage History in Taskman

Hi,
After some time, my server Memory Usage History run up to 80%-90% even the
server does not have any connection in it.
This is server running only MS SQL 2000 + IIS in Win2K Server.
I was wondering that, by using the Win / WMI command to refresh / reset
this Memory Usage History.
Does anyone try this before or any ideal from you all?
Thank you.....
--
Regard,
Kzeto ngHave a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kzeto ng" <kzetong@.yahoo.com> wrote in message news:eVe2I3kuDHA.3196@.TK2MSFTNGP11.phx.gbl...
> Hi,
> After some time, my server Memory Usage History run up to 80%-90% even the
> server does not have any connection in it.
> This is server running only MS SQL 2000 + IIS in Win2K Server.
> I was wondering that, by using the Win / WMI command to refresh / reset
> this Memory Usage History.
> Does anyone try this before or any ideal from you all?
> Thank you.....
> --
> Regard,
> Kzeto ng
>
>|||How much memory do you have, how much is assigned to SQL (see SQL
configuration parameters)?
And how much is the threshold set for IIS (check your machine.config file)?
Willy.
"Kzeto ng" <kzetong@.yahoo.com> wrote in message
news:eVe2I3kuDHA.3196@.TK2MSFTNGP11.phx.gbl...
> Hi,
> After some time, my server Memory Usage History run up to 80%-90% even
the
> server does not have any connection in it.
> This is server running only MS SQL 2000 + IIS in Win2K Server.
> I was wondering that, by using the Win / WMI command to refresh / reset
> this Memory Usage History.
> Does anyone try this before or any ideal from you all?
> Thank you.....
> --
> Regard,
> Kzeto ng
>
>

Wednesday, March 7, 2012

Redundant SQL servers - Environment

Here is what we are trying to accomplish, and please let me know if we are
going down the right path.
We have 2 Windows 2000 Servers running SQL 2000 for a website. The SQL
database is on one of the two servers and we want to create a redundant
environment so when one of the SQL servers fails, the other will take over,
or continue to provide the functions for the website.
We are thinking of putting the SQL database on a NAS device and then point
each server to it and if one goes down the other can perform the operations
as needed.
Can this be done by clustering the two SQL servers and then pointing them to
the SQL database on the NAS device?
Do you know of any other way to create a redundant SQL environment?
Hopefully I've provided you with enough information, if not please let me
know if you need more details.
Thanks,
CC
SQL Server does not support NAS. If you can use a SAN, then you have two
choices. First, you can go with log shipping. Second, you can go with a
cluster solution, as long as the hardware is supported for clustering.
However, you can't just cluster an existing single-server instance. What
you could do is cluster the server that is not being used right now, migrate
the old instance to it and then remove the old instance. At this point, you
have a single-node cluster. Take the old server and add it to the cluster
and you're there.
For a bit more on clustering, check out:
http://www.microsoft.com/technet/technetmag/issues/2007/03/SQLClusters/default.aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"VIT" <VIT@.discussions.microsoft.com> wrote in message
news:D7E35BA4-EDE6-4CEB-B474-652CBD9DD64F@.microsoft.com...
Here is what we are trying to accomplish, and please let me know if we are
going down the right path.
We have 2 Windows 2000 Servers running SQL 2000 for a website. The SQL
database is on one of the two servers and we want to create a redundant
environment so when one of the SQL servers fails, the other will take over,
or continue to provide the functions for the website.
We are thinking of putting the SQL database on a NAS device and then point
each server to it and if one goes down the other can perform the operations
as needed.
Can this be done by clustering the two SQL servers and then pointing them to
the SQL database on the NAS device?
Do you know of any other way to create a redundant SQL environment?
Hopefully I've provided you with enough information, if not please let me
know if you need more details.
Thanks,
CC
|||There are several techniques for creating a redundnt SQL environment.
Clustering using a NAS box is not one of them. Clustering using a supported
storage unit is one possible technique. Only a very few NAS systems are
supported for SQL and none are supported for clustering.
Another possibility is database mirroring. This technique does not require
a shared storage system, but is more resource intensive and has some other
unique requirements.
Here is the Microsoft SQL 2005 High Availability home page:
http://www.microsoft.com/sql/technologies/highavailability/default.mspx
It contains links to FAQs, white papers, etc. on SQL High Availability.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VIT" <VIT@.discussions.microsoft.com> wrote in message
news:D7E35BA4-EDE6-4CEB-B474-652CBD9DD64F@.microsoft.com...
> Here is what we are trying to accomplish, and please let me know if we are
> going down the right path.
> We have 2 Windows 2000 Servers running SQL 2000 for a website. The SQL
> database is on one of the two servers and we want to create a redundant
> environment so when one of the SQL servers fails, the other will take
> over,
> or continue to provide the functions for the website.
> We are thinking of putting the SQL database on a NAS device and then point
> each server to it and if one goes down the other can perform the
> operations
> as needed.
> Can this be done by clustering the two SQL servers and then pointing them
> to
> the SQL database on the NAS device?
> Do you know of any other way to create a redundant SQL environment?
> Hopefully I've provided you with enough information, if not please let me
> know if you need more details.
> Thanks,
> CC
|||In addition to what the others have said, I just want to add that NAS is
built on top of CIFS, and CIFS is a horrible protocol for block I/Os. You'd
be better with considering iSCSI.
Linchi
"VIT" wrote:

> Here is what we are trying to accomplish, and please let me know if we are
> going down the right path.
> We have 2 Windows 2000 Servers running SQL 2000 for a website. The SQL
> database is on one of the two servers and we want to create a redundant
> environment so when one of the SQL servers fails, the other will take over,
> or continue to provide the functions for the website.
> We are thinking of putting the SQL database on a NAS device and then point
> each server to it and if one goes down the other can perform the operations
> as needed.
> Can this be done by clustering the two SQL servers and then pointing them to
> the SQL database on the NAS device?
> Do you know of any other way to create a redundant SQL environment?
> Hopefully I've provided you with enough information, if not please let me
> know if you need more details.
> Thanks,
> CC
|||So based on what we have what is the best, most cost effective way to achieve
a redundant SQL environment?
Thanks,
CC
"Linchi Shea" wrote:
[vbcol=seagreen]
> In addition to what the others have said, I just want to add that NAS is
> built on top of CIFS, and CIFS is a horrible protocol for block I/Os. You'd
> be better with considering iSCSI.
> Linchi
> "VIT" wrote:
|||What are the requirements of the applications?
Log Shipping is cost-effective, but in the event of an outage, manual
intervention would be required to reconfigure the applications to point to
the failsafe server.
SQL Server 2005 Database Mirroring is also cost-effective but will require
some beefier hardware, an upgraded network to reduce latency, an upgrade to
SQL Server 2005, and a reconfiguration of the applications to the later MDAC
or SQL Native Client protocol. Failover would be automatic.
Finally, Failover Clustering would provide better availability and
automation and is supported under both SQL Server 2000 and 2005; however,
this is not as cost-effective and requires a shared storage array
environment. SQL Server 2005, however, has introduced support for failover
clusters on the Standard Edition, which helps reduce the expense some.
Sincerely,
Anthony Thomas

"VIT" <VIT@.discussions.microsoft.com> wrote in message
news:353890CD-84AA-4E3B-A6FB-8A6580611216@.microsoft.com...
> So based on what we have what is the best, most cost effective way to
achieve[vbcol=seagreen]
> a redundant SQL environment?
> Thanks,
> CC
> "Linchi Shea" wrote:
You'd[vbcol=seagreen]
are[vbcol=seagreen]
SQL[vbcol=seagreen]
redundant[vbcol=seagreen]
over,[vbcol=seagreen]
point[vbcol=seagreen]
operations[vbcol=seagreen]
them to[vbcol=seagreen]
me[vbcol=seagreen]

Redundancy Question

Dear All
We have a server running Windows 2000 Server with SQL 2000. This is our web
server, and what I would like to do is implement some form of redundancy
server within this environment. Ideally, what I would like to achieve is a
scenario where is if there is a failure on one of the servers another one
would start up ad take on the exact same role with the exact same access and
sql database and applications.
Does anyone have any idea how this would be achieved, and point in the right
direction.
Thanks
Neil
Hi,
You can go for below options:-
1. Transactional Replication
2. Logshipping every 5 minutes.
In the above cases you have to change your IP Address of new server with old
server when there is a requirement
Thanks
Hari
MCDBA
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>
|||Have a look at
http://www.microsoft.com/sql/techinf...ailability.asp
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>

Redundancy Question

Dear All
We have a server running Windows 2000 Server with SQL 2000. This is our web
server, and what I would like to do is implement some form of redundancy
server within this environment. Ideally, what I would like to achieve is a
scenario where is if there is a failure on one of the servers another one
would start up ad take on the exact same role with the exact same access and
sql database and applications.
Does anyone have any idea how this would be achieved, and point in the right
direction.
Thanks
NeilHi,
You can go for below options:-
1. Transactional Replication
2. Logshipping every 5 minutes.
In the above cases you have to change your IP Address of new server with old
server when there is a requirement
Thanks
Hari
MCDBA
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>|||Have a look at
http://www.microsoft.com/sql/techin...vailability.asp
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>

Redundancy Question

Dear All
We have a server running Windows 2000 Server with SQL 2000. This is our web
server, and what I would like to do is implement some form of redundancy
server within this environment. Ideally, what I would like to achieve is a
scenario where is if there is a failure on one of the servers another one
would start up ad take on the exact same role with the exact same access and
sql database and applications.
Does anyone have any idea how this would be achieved, and point in the right
direction.
Thanks
NeilHave you looked at Replication yet ?
With this you can automatically copy information for one
database to another automatically.
Look up Snapshot and Transactional Replication.
J
>--Original Message--
>Dear All
>We have a server running Windows 2000 Server with SQL
2000. This is our web
>server, and what I would like to do is implement some
form of redundancy
>server within this environment. Ideally, what I would
like to achieve is a
>scenario where is if there is a failure on one of the
servers another one
>would start up ad take on the exact same role with the
exact same access and
>sql database and applications.
>Does anyone have any idea how this would be achieved, and
point in the right
>direction.
>Thanks
>Neil
>
>.
>|||Hi,
You can go for below options:-
1. Transactional Replication
2. Logshipping every 5 minutes.
In the above cases you have to change your IP Address of new server with old
server when there is a requirement
Thanks
Hari
MCDBA
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>|||Have a look at
http://www.microsoft.com/sql/techinfo/administration/2000/availability.asp
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>

Redundancy

Hi All,

After some feedback from some of you who might have done something similar:

We are going to be having 3 SQL servers (running SQL Std licence).
2 Are live
1 is a hot swap in the event that we have a total loss of either of the SQL boxes.
Basically what I am wanting to do, is have the hot swap being updated periodically so that the databases are being replicated on this box, so that if the live one fell over, we could very quickly get the hot swap into take over.

Can anyone offer any perspectives on the best method of attack for this?

Thanks in advance for your thoughts.

CheersTry to implement your own log shipping by referring to this article http://www.sql-server-performance.com/sql_server_log_shipping.asp and this http://support.microsoft.com/default.aspx?scid=kb;en-us;314515 KBA for FAQs about LS.|||Brilliant - thanks this is exactly what I was after.

Thanks!|||This is only a thought, but you could also do transactional replication to the third box. If one of the two other servers dies, all you need to do is drop all of the replication and rename the third box to take the place of the downed server.

This buys two advantages from my perspective... First, it gives you a "live" set of data that you can query, etc. to take load off of your production database(s). As a nice side benefit, it gives you literally "up to the minute" data, there is no loss due to log shipping delays if you ever need to use it!

-PatP

Saturday, February 25, 2012

Reducing Size of MSDB

Are there any scripts that can be used to archive/remove older SQL agent job
runs in MSDB? We are running into larger and larger MSDB sizes and want to
control its growth.
Larry Menzin
American Techsystems Corp.
Agent does this for you, if you refer to job history. It is configurable, on one of the tabs in EM,
for agent, properties. What possibly is causing this isn't job history, but backup history. Check
out sp_delete_backuphistory.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
> Are there any scripts that can be used to archive/remove older SQL agent job
> runs in MSDB? We are running into larger and larger MSDB sizes and want to
> control its growth.
> --
> Larry Menzin
> American Techsystems Corp.
|||Just to add to Tibor's answer: If you have a lot of DTS packages, and if
each DTS package has several versions saved, then your MSDB will be huge.
You can right click on a DTS package in Enterprise Manager and select
'Versions' from the popup menu to see how many versions that package has
got, and delete the older versions if you don't need them anymore.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
Are there any scripts that can be used to archive/remove older SQL agent job
runs in MSDB? We are running into larger and larger MSDB sizes and want to
control its growth.
Larry Menzin
American Techsystems Corp.
|||The only safe way I know of is to manually delete them. You can however set
jobs to delete after they complete. When you go to the notifications tab of
a scheduled job, you can schedule Automatically Delete Job to have it be
deleted when it completes.
If you are scripting the creation of a job, set the delete_level parameter
of sp_addjob to 1.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
> Are there any scripts that can be used to archive/remove older SQL agent
> job
> runs in MSDB? We are running into larger and larger MSDB sizes and want to
> control its growth.
> --
> Larry Menzin
> American Techsystems Corp.

Reducing Size of MSDB

Are there any scripts that can be used to archive/remove older SQL agent job
runs in MSDB? We are running into larger and larger MSDB sizes and want to
control its growth.
--
Larry Menzin
American Techsystems Corp.Agent does this for you, if you refer to job history. It is configurable, on one of the tabs in EM,
for agent, properties. What possibly is causing this isn't job history, but backup history. Check
out sp_delete_backuphistory.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
> Are there any scripts that can be used to archive/remove older SQL agent job
> runs in MSDB? We are running into larger and larger MSDB sizes and want to
> control its growth.
> --
> Larry Menzin
> American Techsystems Corp.|||Just to add to Tibor's answer: If you have a lot of DTS packages, and if
each DTS package has several versions saved, then your MSDB will be huge.
You can right click on a DTS package in Enterprise Manager and select
'Versions' from the popup menu to see how many versions that package has
got, and delete the older versions if you don't need them anymore.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
Are there any scripts that can be used to archive/remove older SQL agent job
runs in MSDB? We are running into larger and larger MSDB sizes and want to
control its growth.
--
Larry Menzin
American Techsystems Corp.|||The only safe way I know of is to manually delete them. You can however set
jobs to delete after they complete. When you go to the notifications tab of
a scheduled job, you can schedule Automatically Delete Job to have it be
deleted when it completes.
If you are scripting the creation of a job, set the delete_level parameter
of sp_addjob to 1.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
> Are there any scripts that can be used to archive/remove older SQL agent
> job
> runs in MSDB? We are running into larger and larger MSDB sizes and want to
> control its growth.
> --
> Larry Menzin
> American Techsystems Corp.

Reducing Size of MSDB

Are there any scripts that can be used to archive/remove older SQL agent job
runs in MSDB? We are running into larger and larger MSDB sizes and want to
control its growth.
Larry Menzin
American Techsystems Corp.Agent does this for you, if you refer to job history. It is configurable, on
one of the tabs in EM,
for agent, properties. What possibly is causing this isn't job history, but
backup history. Check
out sp_delete_backuphistory.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
> Are there any scripts that can be used to archive/remove older SQL agent j
ob
> runs in MSDB? We are running into larger and larger MSDB sizes and want to
> control its growth.
> --
> Larry Menzin
> American Techsystems Corp.|||Just to add to Tibor's answer: If you have a lot of DTS packages, and if
each DTS package has several versions saved, then your MSDB will be huge.
You can right click on a DTS package in Enterprise Manager and select
'Versions' from the popup menu to see how many versions that package has
got, and delete the older versions if you don't need them anymore.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
Are there any scripts that can be used to archive/remove older SQL agent job
runs in MSDB? We are running into larger and larger MSDB sizes and want to
control its growth.
Larry Menzin
American Techsystems Corp.|||The only safe way I know of is to manually delete them. You can however set
jobs to delete after they complete. When you go to the notifications tab of
a scheduled job, you can schedule Automatically Delete Job to have it be
deleted when it completes.
If you are scripting the creation of a job, set the delete_level parameter
of sp_addjob to 1.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
> Are there any scripts that can be used to archive/remove older SQL agent
> job
> runs in MSDB? We are running into larger and larger MSDB sizes and want to
> control its growth.
> --
> Larry Menzin
> American Techsystems Corp.

reducing size of log file

Hi,
I have SQL 2000 running on W2000.
Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
Under properties, it reports that it is 95% space.
How can I reduce the size of these?
I have tried the shrink option bu it doesn't help.
Many thanks
NEILYou need to backup log before shrinking it
BACKUP LOG database_name WITH TRUNCATE_ONLY
On Jan 25, 1:05 pm, "Neil Jarman" <n...@.tNOiSPAMvPLEASEy.co.uk> wrote:
> Hi,
> I have SQL 2000 running on W2000.
> Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
> Under properties, it reports that it is 95% space.
> How can I reduce the size of these?
> I have tried the shrink option bu it doesn't help.
> Many thanks
> NEIL|||Neil
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epa9v6$oo8$1$8300dec7@.news.demon.co.uk...
> Hi,
> I have SQL 2000 running on W2000.
> Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
> Under properties, it reports that it is 95% space.
> How can I reduce the size of these?
> I have tried the shrink option bu it doesn't help.
> Many thanks
> NEIL
>|||Thanks for both your replies.
I have bvacked up log - using EM Backup Database... and this has reduced the
actual used size even miore, however it still occupies 5Gb of disk space.
I can't seem to use the DBCC commands described in the article supplied, can
you tell me how to do this? If I type DBCC followed by anything, I get dbcc
is not recognised ...
cheers,
NEIL|||Hello,
Backup Log will clear up all the inactive postion of the log but will not
reduce the physical file. To reduce the physical
file you may need to SHRINK the LDF file using DBCC SHRINKFILE command. Take
a look into books online about
DBCC SHRINKFILE.
One more thing is schedule a frequent transaction log backup. This will
ensure that your LDF is not growing heavily as well as
make sure you have backup files to recover the database when required.
Thanks
Hari
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> Thanks for both your replies.
> I have bvacked up log - using EM Backup Database... and this has reduced
> the actual used size even miore, however it still occupies 5Gb of disk
> space.
> I can't seem to use the DBCC commands described in the article supplied,
> can you tell me how to do this? If I type DBCC followed by anything, I get
> dbcc is not recognised ...
> cheers,
> NEIL
>|||> If I type DBCC followed by anything, I get dbcc is not recognised ...
What application are you using to execute the DBCC command?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> Thanks for both your replies.
> I have bvacked up log - using EM Backup Database... and this has reduced t
he actual used size even
> miore, however it still occupies 5Gb of disk space.
> I can't seem to use the DBCC commands described in the article supplied, c
an you tell me how to do
> this? If I type DBCC followed by anything, I get dbcc is not recognised ..
.
> cheers,
> NEIL
>|||Hi Tibor,
I used a CMD screen to do this - I presumed it was a DOS type command.
NEIL
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
> What application are you using to execute the DBCC command?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
>|||Nope, it is a TSQL command. Use Query Analyzer/SQL Server Management Studio.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaph8$f6s$1$8300dec7@.news.demon.co.uk...
> Hi Tibor,
> I used a CMD screen to do this - I presumed it was a DOS type command.
> NEIL
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
>|||Lines: 68
NNTP-Posting-Host: nbtdance.demon.co.uk
X-Trace: news.demon.co.uk 1170082069 7290 80.177.3.126 (29 Jan 2007 14:47:49
GMT)
X-Complaints-To: abuse@.demon.net
NNTP-Posting-Date: Mon, 29 Jan 2007 14:47:49 +0000 (UTC)
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1807
X-Priority: 3
X-Newsreader: Microsoft Outlook Express 6.00.2800.1807
X-MSMail-Priority: Normal
Xref: leafnode.mcse.ms microsoft.public.sqlserver.server:25631
Hi Tibor,
Many thanks for the help - my log file is massively reduced now.
regards,
NEIL
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23VKhZhKQHHA.3944@.TK2MSFTNGP06.phx.gbl...
> Nope, it is a TSQL command. Use Query Analyzer/SQL Server Management
Studio.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> news:epaph8$f6s$1$8300dec7@.news.demon.co.uk...
in message[vbcol=seagreen]
reduced the actual used size[vbcol=seagreen]
supplied, can you tell me how to[vbcol=seagreen]
recognised ...[vbcol=seagreen]
>

reducing size of log file

Hi,
I have SQL 2000 running on W2000.
Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
Under properties, it reports that it is 95% space.
How can I reduce the size of these?
I have tried the shrink option bu it doesn't help.
Many thanks
NEILYou need to backup log before shrinking it
BACKUP LOG database_name WITH TRUNCATE_ONLY
On Jan 25, 1:05 pm, "Neil Jarman" <n...@.tNOiSPAMvPLEASEy.co.uk> wrote:
> Hi,
> I have SQL 2000 running on W2000.
> Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
> Under properties, it reports that it is 95% space.
> How can I reduce the size of these?
> I have tried the shrink option bu it doesn't help.
> Many thanks
> NEIL|||Neil
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epa9v6$oo8$1$8300dec7@.news.demon.co.uk...
> Hi,
> I have SQL 2000 running on W2000.
> Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
> Under properties, it reports that it is 95% space.
> How can I reduce the size of these?
> I have tried the shrink option bu it doesn't help.
> Many thanks
> NEIL
>|||Thanks for both your replies.
I have bvacked up log - using EM Backup Database... and this has reduced the
actual used size even miore, however it still occupies 5Gb of disk space.
I can't seem to use the DBCC commands described in the article supplied, can
you tell me how to do this? If I type DBCC followed by anything, I get dbcc
is not recognised ...
cheers,
NEIL|||Hello,
Backup Log will clear up all the inactive postion of the log but will not
reduce the physical file. To reduce the physical
file you may need to SHRINK the LDF file using DBCC SHRINKFILE command. Take
a look into books online about
DBCC SHRINKFILE.
One more thing is schedule a frequent transaction log backup. This will
ensure that your LDF is not growing heavily as well as
make sure you have backup files to recover the database when required.
Thanks
Hari
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> Thanks for both your replies.
> I have bvacked up log - using EM Backup Database... and this has reduced
> the actual used size even miore, however it still occupies 5Gb of disk
> space.
> I can't seem to use the DBCC commands described in the article supplied,
> can you tell me how to do this? If I type DBCC followed by anything, I get
> dbcc is not recognised ...
> cheers,
> NEIL
>|||> If I type DBCC followed by anything, I get dbcc is not recognised ...
What application are you using to execute the DBCC command?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> Thanks for both your replies.
> I have bvacked up log - using EM Backup Database... and this has reduced the actual used size even
> miore, however it still occupies 5Gb of disk space.
> I can't seem to use the DBCC commands described in the article supplied, can you tell me how to do
> this? If I type DBCC followed by anything, I get dbcc is not recognised ...
> cheers,
> NEIL
>|||Hi Tibor,
I used a CMD screen to do this - I presumed it was a DOS type command.
NEIL
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
>> If I type DBCC followed by anything, I get dbcc is not recognised ...
> What application are you using to execute the DBCC command?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
>> Thanks for both your replies.
>> I have bvacked up log - using EM Backup Database... and this has reduced
>> the actual used size even miore, however it still occupies 5Gb of disk
>> space.
>> I can't seem to use the DBCC commands described in the article supplied,
>> can you tell me how to do this? If I type DBCC followed by anything, I
>> get dbcc is not recognised ...
>> cheers,
>> NEIL
>|||Nope, it is a TSQL command. Use Query Analyzer/SQL Server Management Studio.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaph8$f6s$1$8300dec7@.news.demon.co.uk...
> Hi Tibor,
> I used a CMD screen to do this - I presumed it was a DOS type command.
> NEIL
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
>> If I type DBCC followed by anything, I get dbcc is not recognised ...
>> What application are you using to execute the DBCC command?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
>> news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
>> Thanks for both your replies.
>> I have bvacked up log - using EM Backup Database... and this has reduced the actual used size
>> even miore, however it still occupies 5Gb of disk space.
>> I can't seem to use the DBCC commands described in the article supplied, can you tell me how to
>> do this? If I type DBCC followed by anything, I get dbcc is not recognised ...
>> cheers,
>> NEIL
>>
>|||Hi Tibor,
Many thanks for the help - my log file is massively reduced now.
regards,
NEIL
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23VKhZhKQHHA.3944@.TK2MSFTNGP06.phx.gbl...
> Nope, it is a TSQL command. Use Query Analyzer/SQL Server Management
Studio.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> news:epaph8$f6s$1$8300dec7@.news.demon.co.uk...
> > Hi Tibor,
> >
> > I used a CMD screen to do this - I presumed it was a DOS type command.
> >
> > NEIL
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in message
> > news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
> >> If I type DBCC followed by anything, I get dbcc is not recognised ...
> >>
> >> What application are you using to execute the DBCC command?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> >> news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> >> Thanks for both your replies.
> >>
> >> I have bvacked up log - using EM Backup Database... and this has
reduced the actual used size
> >> even miore, however it still occupies 5Gb of disk space.
> >>
> >> I can't seem to use the DBCC commands described in the article
supplied, can you tell me how to
> >> do this? If I type DBCC followed by anything, I get dbcc is not
recognised ...
> >>
> >> cheers,
> >>
> >> NEIL
> >>
> >>
> >
> >
>

Friday, March 30, 2012

Regarding Full-Text search

Regarding dblib

Regarding dblib

Regarding dblib

Regarding dblib

Monday, March 26, 2012

Refreshing the field list

refreshing database before restart

Refresh the properties of as server

Friday, March 23, 2012

Refresh Memory Usage History in Taskman

Wednesday, March 7, 2012

Redundant SQL servers - Environment

Redundancy Question

Redundancy Question

Redundancy Question

Redundancy

Saturday, February 25, 2012

Reducing Size of MSDB

Reducing Size of MSDB

Reducing Size of MSDB

reducing size of log file

reducing size of log file

reducing size of log file

reduce databse size

Blog Archive

About Me