MSSQL Server connectivity

Hi,

I have two Windows machines in the Local Area Network. One of the machines has MSSQL Enterprise Server 8.0 installed whereas the other has MSSQL Server 2005 installed. When I try to establish the connectivity between these two servers, I can not do so. When I try to look up Network servers, the two machines can't see each other. How can I make them talk to each other? Is there some type of driver or some such thing to be installed which can help the matter?

Please let me know.

Need a bit more information to help you:

Are the instances default or named?
Do you have any firewall software running on either server?
What is the error msg you're getting?
Can you use SQLCMD to connect to the remote server?

|||

Thanks for the response.

Answers to your questions:

1. The MSSQL Server instances are the default ones.

2. I don't think I have a firewall running on the servers.

3. When I go to SQL Server Management Studio and try to register the server by looking on the network, I can not find my other machine with Enterprise Manager 8.0. So I don't get any error, but I can not see the machine either.

4. Login failed for user 'xyz'.
HResult 0x2746, Level 16, State 1
TCP Provider: An existing connection was forcibly closed by the remote host.

Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.

This is the error that I see on the command prompt.

Here's what I suggest: You may want to go through my answers above, but I think I don't know much about this new SQL version? Can you quickly list a set of steps I can follow to connect from SQL Server 2005 to another machine having SQL Enterprise Manager 8.0 running on it? Thanks in advance.

|||

Enterprise Manager has nothing to do with remote connectivity. This is what I understand you're doing:

Machine 1: Running SQL Server 2000, default instance

Machine 2: Running SQL Server 2005, default instance

From Machine 2 running SQL Server Management Studio you want to connect to Machine 1.

First, make sure that Machine 1 is setup to accept remote connections. This is documented in Books Online for SQL Server 2000.

You'll want to make sure you're providing the correct credentials: Windows or SQL Server depending on how the instance is configured.

|||

Hi Dan,

Thanks for the response. However, we somehow managed to do the connectivity between the 2 servers. Apparently, the SQL user which we were using to establish the connectivity did not work initially. We dropped and recreated the user and then it went through fine. Don't know why it did not connect initially.

I have another problem though. And I would really appreciate if someone could help with this real quick.

So, there's this MSSQL 2005 installed on a new machine, which has 40 G on C Drive and 250+ G on a D Drive. But all of my C Drive has been taken up by "tempdb.mdf". It has taken up nearly 26 G of space on this drive. Why should that be the case? I know that this is a System database that gets created when one installs MSSQL Server, but I have never seen it this big. Is there a problem with the installation that I can fix? Please let me know.

Thanks.

|||This is certainly suspicious. I'm not expert on tempdb so I recommend you post this issue to http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1. Someone there will certainly be able to help you.

MSSQL Server Backups

I would like to modify the amount of backups that the sql server keeps on
record. My reasoning is that I am trying to minimize the size of my full
backups. I was made aware that the server keeps a certain time period of
backups and that I could modify this and lessen the time period that it keeps
backups. I hope this makes sense. Thanks in advance.
hi,
cc.az wrote:
> I would like to modify the amount of backups that the sql server
> keeps on record. My reasoning is that I am trying to minimize the
> size of my full backups. I was made aware that the server keeps a
> certain time period of backups and that I could modify this and
> lessen the time period that it keeps backups. I hope this makes
> sense. Thanks in advance.
all the retaintion for backups can be set manually in the Backup
operation...
you can specify the
RETAINDAYS = 1 -- no of days
or
EXPIREDATE = N'2005/02/03' -- an actual date...
if those 2 settings are not provided, the default expiration is determined
by the media retention configuration setting of sp_configure..
http://msdn.microsoft.com/library/de...ba-bz_35ww.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSSQL Server accounting

Hello,

How can I see which login make more traffic to MSSQL ? Not by IP and
external firewall , but with binding to database loginHi

I think you may want to look at SQL profiler. More information is in Books
Online

John
"Andy" <djANTISPAMhd@.computerman.de> wrote in message
news:btp354$83u$06$1@.news.t-online.com...
> Hello,
> How can I see which login make more traffic to MSSQL ? Not by IP and
> external firewall , but with binding to database login|||External to the database network operational traffic?

"Andy" <djANTISPAMhd@.computerman.de> wrote in message
news:btp354$83u$06$1@.news.t-online.com...
> Hello,
> How can I see which login make more traffic to MSSQL ? Not by IP and
> external firewall , but with binding to database login

MSSQL Server 2005 reported account locked out for user 'sa'

Greetings,

I receive an error message in event log when i try to connect to the Database Server using ODBC on a client machine. The database server is running on Windows 2003 Server Standard Edition and the client machine is Windows XP Professional. Following is the error message from the event log:

2147467259 - [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it.

What causes the error to occur and how to resolve it?Appreciate for your assistence.

Thanks and regards,

Viknes

That error message means that the number of unsuccessful attempts to connect as the ‘sa’ account on your server and triggered the lockout policy on SQL Server 2005 for this account.

I would recommend verifying the logs and trying to find out the reason why the account was locked out. It is possible that one of your applications is using an outdated password and it needs to be fixed, but it may also be possible it was an automated attack trying to guess the SA password.

The following links will hopefully help you to resolve your problem, but if you have any further question or if the documentation is not clear enough, please let us know:

· Password Policy (http://msdn2.microsoft.com/en-us/library/ms161959.aspx)

· Alter Login (TSQL) (http://msdn2.microsoft.com/en-us/library/ms189828.aspx)

· Changing password programmatically (http://msdn2.microsoft.com/en-us/library/ms131024.aspx)

-Raul Garcia

SDE/T

SQL Server Engine

|||Also remember that SQL Server accounts can now use Windows Security policies on whiche SQL Server is running(Password complexity, password expiration, etc.) This only works for Windows Server 2003

MSSQL Server 2005 Not Reporting Errors to Windows Event Log

Hello All,

I just performed a fresh install of MSSQL 2005 Server Developer Edition on my XPSP2 machine along with MSSQL 2005 SP1 and I noticed that errors are not being reported in the Windows Event Viewer. Is there a way to enable/disable logging here?

I need to view these logs because when the tasks > 'copy database' task fails it submits its error in the Windows Event Log however since I reinstalled MSSQL 2005 these log entries don't appear here.

thanks in advance.

Hi,

if you are using a job for this you can switch the Windows event logging on.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Its not a job. I just manually select the task from Management Studio. In the last installation performing this task created log entries in the windows event log but for some reason they aren't appearing now.

|||

Hi:

The main log for SQL Server 2005 installation has changed, and placed in the following location:

%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt

For more information, please refer to the following MSDN reference.

http://msdn2.microsoft.com/en-us/library/ms143702.aspx

Hope this helps. :-)

Thanks.

|||

Interesting... I wonder why events don't show up in the Windows Event Log anymore? Especially since MSSQL 2005 specifically says that it will make log entries in the Windows Event Log in the Copy Database task wizard.

|||

Hi,

If the Severity Level is 19 or Higher then only it will be logged in the Windows Event Log.

HTH

Hemantgiri S. Goswami

MSSQL Server 2000 Memory Grows Incrementally

Hi All
My SQL Server keeps on taking memory(RAM) on the server without
releasing it.
There seems to be a memory leak.
On every query that gets executed the memory occupied by MSSQL grows.
Does this make the subsequent queries execute slowly?
Also even though i execute the same query multiple time the memory size
still increases.
What could be the possible reason for this?
Thanks & Regards
Vishal.
Vishal wrote:
> Hi All
> My SQL Server keeps on taking memory(RAM) on the server without
> releasing it.
> There seems to be a memory leak.
> On every query that gets executed the memory occupied by MSSQL grows.
> Does this make the subsequent queries execute slowly?
> Also even though i execute the same query multiple time the memory size
> still increases.
> What could be the possible reason for this?
> Thanks & Regards
> Vishal.
>
This is normal behavior. SQL Server will cache data pages in memory as
they are loaded from disk. It won't release memory unless it is
configured for dynamic memory allocation, AND the OS requests it.
Having SQL data cached in memory is a good thing, let it use as much as
it needs.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||The behaviour you are seeing is by design. Here is a KB article on the
subject:
INF: SQL Server Memory Usage
http://support.microsoft.com/default...;en-us;q321363
Keith Kratochvil
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1160134477.258235.198120@.h48g2000cwc.googlegr oups.com...
> Hi All
> My SQL Server keeps on taking memory(RAM) on the server without
> releasing it.
> There seems to be a memory leak.
> On every query that gets executed the memory occupied by MSSQL grows.
> Does this make the subsequent queries execute slowly?
> Also even though i execute the same query multiple time the memory size
> still increases.
> What could be the possible reason for this?
> Thanks & Regards
> Vishal.
>
|||Dear Tracy,
How to set up the "dynamic memory allocation" ?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4526486E.4090509@.realsqlguy.com...
> Vishal wrote:
> This is normal behavior. SQL Server will cache data pages in memory as
> they are loaded from disk. It won't release memory unless it is
> configured for dynamic memory allocation, AND the OS requests it. Having
> SQL data cached in memory is a good thing, let it use as much as it needs.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Thanks Tracy
How to configure dynamic Memory Allocation?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> This is normal behavior. SQL Server will cache data pages in memory as
> they are loaded from disk. It won't release memory unless it is
> configured for dynamic memory allocation, AND the OS requests it.
> Having SQL data cached in memory is a good thing, let it use as much as
> it needs.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Vishal wrote:
> Thanks Tracy
> How to configure dynamic Memory Allocation?
See "Server Memory Options" under "Setting Configuration Options" in
Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Dear Tracy,
Thank you for your advice.
Peter
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:452B94DC.8000706@.realsqlguy.com...
> Vishal wrote:
> See "Server Memory Options" under "Setting Configuration Options" in Books
> Online...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

MSSQL Server 2000 Memory Grows Incrementally

Hi All
My SQL Server keeps on taking memory(RAM) on the server without
releasing it.
There seems to be a memory leak.
On every query that gets executed the memory occupied by MSSQL grows.
Does this make the subsequent queries execute slowly?
Also even though i execute the same query multiple time the memory size
still increases.
What could be the possible reason for this?
Thanks & Regards
Vishal.Vishal wrote:
> Hi All
> My SQL Server keeps on taking memory(RAM) on the server without
> releasing it.
> There seems to be a memory leak.
> On every query that gets executed the memory occupied by MSSQL grows.
> Does this make the subsequent queries execute slowly?
> Also even though i execute the same query multiple time the memory size
> still increases.
> What could be the possible reason for this?
> Thanks & Regards
> Vishal.
>
This is normal behavior. SQL Server will cache data pages in memory as
they are loaded from disk. It won't release memory unless it is
configured for dynamic memory allocation, AND the OS requests it.
Having SQL data cached in memory is a good thing, let it use as much as
it needs.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The behaviour you are seeing is by design. Here is a KB article on the
subject:
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
--
Keith Kratochvil
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1160134477.258235.198120@.h48g2000cwc.googlegroups.com...
> Hi All
> My SQL Server keeps on taking memory(RAM) on the server without
> releasing it.
> There seems to be a memory leak.
> On every query that gets executed the memory occupied by MSSQL grows.
> Does this make the subsequent queries execute slowly?
> Also even though i execute the same query multiple time the memory size
> still increases.
> What could be the possible reason for this?
> Thanks & Regards
> Vishal.
>|||Dear Tracy,
How to set up the "dynamic memory allocation" ?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4526486E.4090509@.realsqlguy.com...
> Vishal wrote:
>> Hi All
>> My SQL Server keeps on taking memory(RAM) on the server without
>> releasing it.
>> There seems to be a memory leak.
>> On every query that gets executed the memory occupied by MSSQL grows.
>> Does this make the subsequent queries execute slowly?
>> Also even though i execute the same query multiple time the memory size
>> still increases.
>> What could be the possible reason for this?
>> Thanks & Regards
>> Vishal.
> This is normal behavior. SQL Server will cache data pages in memory as
> they are loaded from disk. It won't release memory unless it is
> configured for dynamic memory allocation, AND the OS requests it. Having
> SQL data cached in memory is a good thing, let it use as much as it needs.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks Tracy
How to configure dynamic Memory Allocation'
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> > Hi All
> >
> > My SQL Server keeps on taking memory(RAM) on the server without
> > releasing it.
> > There seems to be a memory leak.
> > On every query that gets executed the memory occupied by MSSQL grows.
> > Does this make the subsequent queries execute slowly?
> >
> > Also even though i execute the same query multiple time the memory size
> > still increases.
> > What could be the possible reason for this?
> >
> > Thanks & Regards
> > Vishal.
> >
> This is normal behavior. SQL Server will cache data pages in memory as
> they are loaded from disk. It won't release memory unless it is
> configured for dynamic memory allocation, AND the OS requests it.
> Having SQL data cached in memory is a good thing, let it use as much as
> it needs.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Vishal wrote:
> Thanks Tracy
> How to configure dynamic Memory Allocation'
See "Server Memory Options" under "Setting Configuration Options" in
Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Dear Tracy,
Thank you for your advice.
Peter
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:452B94DC.8000706@.realsqlguy.com...
> Vishal wrote:
>> Thanks Tracy
>> How to configure dynamic Memory Allocation'
> See "Server Memory Options" under "Setting Configuration Options" in Books
> Online...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com