How to alter the transaction logfile size in MSDE 2000?

Hi there,

I recently saw that the transaction log files of user dbs grow undefinitely in SQL Server 2000 - one of our customers had a 11 GB log file which totally slowed down the server.
Another customer of ours uses one of my applications logging all actions in a MSDE database file and I fear that the corresponding transaction log file will grow and block the system too - is there any way that I could shrink and set the max size of the transaction log file through SQL?

I already know the command "SHRINK FILE ('filename')" but I haven't found a SQL command to set the max size.

Thank you for any hints!

SaschaTo set the growth, UOM for increment, increment value, allocated size, and max sizeeeee, you need to use ALTER DATABASE. However, the claim that the size of the log file slows down the server or even "block" it is somewhat strange. I've never heard of the size of the log file having such detremental effect on the system. Anyone has other opinion?|||hi rdjabarov,

thank you for your comment. How should the ALTER DATABASE command look like? It's not a part of the standard SQL command, that's why I haven't found any documentation in standard SQL books.
The server slowed down because there were only 180 kb free space on the drive and windows needs some space for swapping, running other programs and other stuff.
I'll try to see if I can get some info on ALTER DATABASE with MS SQL Server.|||Now THAT (!!!) is a different story! In other words, your db is on the verge of becoming Suspect. BTW, your OS doesn't need any more room for swap file, unless you're bogging down the memory as well.

BOL (ALTER DATABASE syntax):

MODIFY FILE
Specifies the given file that should be modified, including the FILENAME, SIZE, FILEGROWTH, and MAXSIZE options.

How to add services to SQL Server Service Manager

I have 2 SQL Servers.
On the 1st server, when I go to the Service Manager, I can se all the 4
services:
1. Distributed Transaction Coordinator
2. Microsoft Search
3. SQL Server
4. SQL Server Agent
On the 2nd server, when I go to the Service Manager, I only see the last two
services! How can I add the DTC and the MS Search to the Service manager?
Thank you
BlackpitHi
Have you checked that these are installed?
John
"blackpit" wrote:

> I have 2 SQL Servers.
> On the 1st server, when I go to the Service Manager, I can se all the 4
> services:
> 1. Distributed Transaction Coordinator
> 2. Microsoft Search
> 3. SQL Server
> 4. SQL Server Agent
> On the 2nd server, when I go to the Service Manager, I only see the last t
wo
> services! How can I add the DTC and the MS Search to the Service manager?
> Thank you
> Blackpit|||Hello John!
I did checked this out and, yes, they are installed.
Actually I have a suspicion that the fault is on the Security Policy applied
to the server that misses these 2 services on the Service Manager. The 2
servers are in diffferent Active Directory OUs, so different security
policies are applied in each case.
The problem is that I don't know what the exact security issue is the one
that creates the problem (e.g. NTFS permissions? Where and for Who?). If I
did, I could let the System Administrator know and he would be able to help
me grant/revoke the necessary privileges.
Do you have any idea, who needs What privileges on Where so that the 2
missing services can appear in the Service Manager?
Thank you for your time.
Vagelis
Ο χρ?στη? "John Bell" _γγραψε:
[vbcol=seagreen]
> Hi
> Have you checked that these are installed?
> John
> "blackpit" wrote:
>|||Hi
I am not sure where the permissions could be causing a problem, but I would
guess it would be permissions on a specific registry entry that causes this
.
Can you stop/start these services from the services applet on the
administrators menu?
John
"blackpit" wrote:
[vbcol=seagreen]
> Hello John!
> I did checked this out and, yes, they are installed.
> Actually I have a suspicion that the fault is on the Security Policy appli
ed
> to the server that misses these 2 services on the Service Manager. The 2
> servers are in diffferent Active Directory OUs, so different security
> policies are applied in each case.
> The problem is that I don't know what the exact security issue is the one
> that creates the problem (e.g. NTFS permissions? Where and for Who?). If I
> did, I could let the System Administrator know and he would be able to he
lp
> me grant/revoke the necessary privileges.
> Do you have any idea, who needs What privileges on Where so that the 2
> missing services can appear in the Service Manager?
> Thank you for your time.
> Vagelis
> Ο χρ?στη? "John Bell" _γγραψε:
>|||When I tried to start the Microsoft Search Service (via services applet)
using the same account that the MSSQLSERVER service starts, it didn't
startup. I guess I must find out where to give permissions for this account.
At least we found out that it is a security issue.
I will update the post when I find out the exact grants.
Thanks for your help.
Ο χρ?στη? "John Bell" _γγραψε:
[vbcol=seagreen]
> Hi
> I am not sure where the permissions could be causing a problem, but I woul
d
> guess it would be permissions on a specific registry entry that causes th
is.
> Can you stop/start these services from the services applet on the
> administrators menu?
> John
> "blackpit" wrote:
>

How to add services to SQL Server Service Manager

I have 2 SQL Servers.
On the 1st server, when I go to the Service Manager, I can se all the 4
services:
1. Distributed Transaction Coordinator
2. Microsoft Search
3. SQL Server
4. SQL Server Agent
On the 2nd server, when I go to the Service Manager, I only see the last two
services! How can I add the DTC and the MS Search to the Service manager?
Thank you
BlackpitHi
Have you checked that these are installed?
John
"blackpit" wrote:
> I have 2 SQL Servers.
> On the 1st server, when I go to the Service Manager, I can se all the 4
> services:
> 1. Distributed Transaction Coordinator
> 2. Microsoft Search
> 3. SQL Server
> 4. SQL Server Agent
> On the 2nd server, when I go to the Service Manager, I only see the last two
> services! How can I add the DTC and the MS Search to the Service manager?
> Thank you
> Blackpit|||Hello John!
I did checked this out and, yes, they are installed.
Actually I have a suspicion that the fault is on the Security Policy applied
to the server that misses these 2 services on the Service Manager. The 2
servers are in diffferent Active Directory OUs, so different security
policies are applied in each case.
The problem is that I don't know what the exact security issue is the one
that creates the problem (e.g. NTFS permissions? Where and for Who?). If I
did, I could let the System Administrator know and he would be able to help
me grant/revoke the necessary privileges.
Do you have any idea, who needs What privileges on Where so that the 2
missing services can appear in the Service Manager?
Thank you for your time.
Vagelis
Î? Ï?Ï?ήÏ?Ï?ηÏ? "John Bell" έγγÏ?αÏ?ε:
> Hi
> Have you checked that these are installed?
> John
> "blackpit" wrote:
> > I have 2 SQL Servers.
> >
> > On the 1st server, when I go to the Service Manager, I can se all the 4
> > services:
> > 1. Distributed Transaction Coordinator
> > 2. Microsoft Search
> > 3. SQL Server
> > 4. SQL Server Agent
> >
> > On the 2nd server, when I go to the Service Manager, I only see the last two
> > services! How can I add the DTC and the MS Search to the Service manager?
> >
> > Thank you
> > Blackpit|||Hi
I am not sure where the permissions could be causing a problem, but I would
guess it would be permissions on a specific registry entry that causes this.
Can you stop/start these services from the services applet on the
administrators menu?
John
"blackpit" wrote:
> Hello John!
> I did checked this out and, yes, they are installed.
> Actually I have a suspicion that the fault is on the Security Policy applied
> to the server that misses these 2 services on the Service Manager. The 2
> servers are in diffferent Active Directory OUs, so different security
> policies are applied in each case.
> The problem is that I don't know what the exact security issue is the one
> that creates the problem (e.g. NTFS permissions? Where and for Who?). If I
> did, I could let the System Administrator know and he would be able to help
> me grant/revoke the necessary privileges.
> Do you have any idea, who needs What privileges on Where so that the 2
> missing services can appear in the Service Manager?
> Thank you for your time.
> Vagelis
> Î? Ï?Ï?ήÏ?Ï?ηÏ? "John Bell" έγγÏ?αÏ?ε:
> > Hi
> >
> > Have you checked that these are installed?
> >
> > John
> >
> > "blackpit" wrote:
> >
> > > I have 2 SQL Servers.
> > >
> > > On the 1st server, when I go to the Service Manager, I can se all the 4
> > > services:
> > > 1. Distributed Transaction Coordinator
> > > 2. Microsoft Search
> > > 3. SQL Server
> > > 4. SQL Server Agent
> > >
> > > On the 2nd server, when I go to the Service Manager, I only see the last two
> > > services! How can I add the DTC and the MS Search to the Service manager?
> > >
> > > Thank you
> > > Blackpit|||When I tried to start the Microsoft Search Service (via services applet)
using the same account that the MSSQLSERVER service starts, it didn't
startup. I guess I must find out where to give permissions for this account.
At least we found out that it is a security issue.
I will update the post when I find out the exact grants.
Thanks for your help.
Î? Ï?Ï?ήÏ?Ï?ηÏ? "John Bell" έγγÏ?αÏ?ε:
> Hi
> I am not sure where the permissions could be causing a problem, but I would
> guess it would be permissions on a specific registry entry that causes this.
> Can you stop/start these services from the services applet on the
> administrators menu?
> John
> "blackpit" wrote:
> > Hello John!
> >
> > I did checked this out and, yes, they are installed.
> >
> > Actually I have a suspicion that the fault is on the Security Policy applied
> > to the server that misses these 2 services on the Service Manager. The 2
> > servers are in diffferent Active Directory OUs, so different security
> > policies are applied in each case.
> >
> > The problem is that I don't know what the exact security issue is the one
> > that creates the problem (e.g. NTFS permissions? Where and for Who?). If I
> > did, I could let the System Administrator know and he would be able to help
> > me grant/revoke the necessary privileges.
> >
> > Do you have any idea, who needs What privileges on Where so that the 2
> > missing services can appear in the Service Manager?
> >
> > Thank you for your time.
> > Vagelis
> >
> > Î? Ï?Ï?ήÏ?Ï?ηÏ? "John Bell" έγγÏ?αÏ?ε:
> >
> > > Hi
> > >
> > > Have you checked that these are installed?
> > >
> > > John
> > >
> > > "blackpit" wrote:
> > >
> > > > I have 2 SQL Servers.
> > > >
> > > > On the 1st server, when I go to the Service Manager, I can se all the 4
> > > > services:
> > > > 1. Distributed Transaction Coordinator
> > > > 2. Microsoft Search
> > > > 3. SQL Server
> > > > 4. SQL Server Agent
> > > >
> > > > On the 2nd server, when I go to the Service Manager, I only see the last two
> > > > services! How can I add the DTC and the MS Search to the Service manager?
> > > >
> > > > Thank you
> > > > Blackpit

How to activate the MSDTC service?

Hello,

As newbie in server country I try a course example to test a distributed transaction accessing both SQL Server 2005 and Sql Server 2005 Express.

But I get an error message saying:
>MSDTC on server is not available

I Internetted somewhere that MSDTC stands for TransactionManager service.
If I look in the 'Services' list I do not see something like MSDTC, so I think it is not installed or started.

How do I install or activate the MSDTC service?

Thanks in advange,
Henk

Hi,

What OS are you using? I have Win2K3 and I do see "Distributed Transaction Coordinator" as a service.

Thanks

Waseem

|||

In the services list it should be named "Distributed Transaction Coordinator".

You should check ControlPanel->AdministrativeTools->Component Services->Computers->My Computer->Properties.

Have a look at http://msdn2.microsoft.com/en-us/library/ms684146.aspx for a general documentation.

|||

Thanks both of you!

I'm using WinXP Prof and I see a 'Distributed Transaction Coordinator' service thats waiting to be started....
After starting it, my program runs fine!

regards,

Henk