Can't connect

I've installed Visual Studio 2005 (including SQL Express) on a XP Home
machine. I've currently problems to connect to the SQL-Server getting the
following error message
C:\Dokumente und Einstellungen\Robert>sqlcmd
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
establi
shing a connection to the server. When connecting to SQL Server 2005, this
failu
re may be caused by the fact that under the default settings SQL Server does
not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
I've allready
- enabled the Protocolls via the SQL Server Configuration Manager
- stopped the firewall
- tried it via Access, VS 2005 Server Explorer, ...
I get access to the database when I use SSEUTIL ! Using this tool it was
e.g. possible to attach/dettach a database.
Any idea whats wrong?
Are you in fact trying to connect remotely? If you are, you will need to
enable and start SQLBrowser also. If not, are you using .\sqlexpress as
your server name in the connection string?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:B34E3E09-A80B-43C8-B164-4D4B299C10BF@.microsoft.com...
> I've installed Visual Studio 2005 (including SQL Express) on a XP Home
> machine. I've currently problems to connect to the SQL-Server getting the
> following error message
> C:\Dokumente und Einstellungen\Robert>sqlcmd
> HResult 0x2, Level 16, State 1
> Named Pipes Provider: Could not open a connection to SQL Server [2].
> Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
> establi
> shing a connection to the server. When connecting to SQL Server 2005, this
> failu
> re may be caused by the fact that under the default settings SQL Server
> does
> not
> allow remote connections..
> Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
> I've allready
> - enabled the Protocolls via the SQL Server Configuration Manager
> - stopped the firewall
> - tried it via Access, VS 2005 Server Explorer, ...
> I get access to the database when I use SSEUTIL ! Using this tool it was
> e.g. possible to attach/dettach a database.
> Any idea whats wrong?
|||Hi Roger,
thank you very much. I missed the ".\sqlexpress". Adding this to the
connection string everything worked.
"Roger Wolter[MSFT]" wrote:

> Are you in fact trying to connect remotely? If you are, you will need to
> enable and start SQLBrowser also. If not, are you using .\sqlexpress as
> your server name in the connection string?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:B34E3E09-A80B-43C8-B164-4D4B299C10BF@.microsoft.com...
>
>

can't configure mirroring

Hi,
I've already configured a few databases from our server A to use mirroring
to our DR server. Now I've got a different primary server B and I am trying
to configure it so it mirrors to the same DR server. But I can't.
Is it supposed to work on the same port number as the first mirroring
connection between A & DR? or B & DR has to use a different port number? I've
also tried using a different port number (first check with netstat that it's
not used), but the Wizard doesn't allow me to enter a different port number
it still defaults to the same as between A & DR.
The error says that: The server network address "TCP://DR-server.local:5022"
can not be reached or does not exist. Check the network address name and that
the ports for the local and remote endpoints are operational. (Microsoft SQL
Server, Error: 1418)
Any ideas? Thanks.
Panos.
I have the same problem. Three new servers setting up a mirror from a to b
with c as the witness. I have verified that I can connect to each from each
by going to a DOS prompt on each and executing "osql -Usa
-SserverDNSname,5022" It returns with an osql command prompt. I am using SQL
server 2005 Standard with SP2 on windows server 2003 sp1.
While going through the mirror setup wizard I get the error:
An error occurred while starting mirroring.
Additional information:
Alter fail for database 'test'. (Microsoft.sqlserver.smo)
An exception occurred while executing a transact-sql statement or batch.
(Micorosft.sqlserver.connectioninfo)
The server networkaddress "TCP://address.given.here.com:5022" can not be
reached or does not exists. Check the network address name and that the ports
for the local and remote endpoints are operational. (Microsoft sql server,
Error: 1418)
I did verify that the dsn names resolve. I used both integrated NT accounts
for authentication and the SQL Server sa account for setting up security
between all the servers and the same message results.
I used a port scanner and netstat to verify that 5022 is live.
It gets this message when it attempts to connect to the local host where
mirroring is being initiated from. There is no opportunity to put in
authentication information for the local host when setting up mirroring. I am
using an account that is in the administrators group on all three servers.
These servers are not part of a domain.
Seems to me there is a fundamental flaw with mirror setup.
Suggestions for further diagnosing this problem would be helpful.
|||Well mine is slightly different as the DR server (B) I am trying to make the
target has already been set-up successfully for mirroring with another server
(A). I am now trying to setup mirroring from Server C to server B but I get
the error message. Are you sure you can use mirroring with Developer edition?
I thought you need Enterprise but maybe that was before SP2. Check this
anyway.
Panos.
"DBA449" wrote:

> I have the same problem. Three new servers setting up a mirror from a to b
> with c as the witness. I have verified that I can connect to each from each
> by going to a DOS prompt on each and executing "osql -Usa
> -SserverDNSname,5022" It returns with an osql command prompt. I am using SQL
> server 2005 Standard with SP2 on windows server 2003 sp1.
> While going through the mirror setup wizard I get the error:
> An error occurred while starting mirroring.
> Additional information:
> Alter fail for database 'test'. (Microsoft.sqlserver.smo)
> An exception occurred while executing a transact-sql statement or batch.
> (Micorosft.sqlserver.connectioninfo)
> The server networkaddress "TCP://address.given.here.com:5022" can not be
> reached or does not exists. Check the network address name and that the ports
> for the local and remote endpoints are operational. (Microsoft sql server,
> Error: 1418)
> I did verify that the dsn names resolve. I used both integrated NT accounts
> for authentication and the SQL Server sa account for setting up security
> between all the servers and the same message results.
> I used a port scanner and netstat to verify that 5022 is live.
> It gets this message when it attempts to connect to the local host where
> mirroring is being initiated from. There is no opportunity to put in
> authentication information for the local host when setting up mirroring. I am
> using an account that is in the administrators group on all three servers.
> These servers are not part of a domain.
> Seems to me there is a fundamental flaw with mirror setup.
> Suggestions for further diagnosing this problem would be helpful.
|||Mirroring is now supported for SQL Server Standard edition 2005 with SP2
installed. I saw this on the Microsoft web site.
"Panos Stavroulis." wrote:
[vbcol=seagreen]
> Well mine is slightly different as the DR server (B) I am trying to make the
> target has already been set-up successfully for mirroring with another server
> (A). I am now trying to setup mirroring from Server C to server B but I get
> the error message. Are you sure you can use mirroring with Developer edition?
> I thought you need Enterprise but maybe that was before SP2. Check this
> anyway.
> Panos.
> "DBA449" wrote:
|||Microsoft?
I think we need a little help here?
What da ya sa...
"Panos Stavroulis." wrote:

> Hi,
> I've already configured a few databases from our server A to use mirroring
> to our DR server. Now I've got a different primary server B and I am trying
> to configure it so it mirrors to the same DR server. But I can't.
> Is it supposed to work on the same port number as the first mirroring
> connection between A & DR? or B & DR has to use a different port number? I've
> also tried using a different port number (first check with netstat that it's
> not used), but the Wizard doesn't allow me to enter a different port number
> it still defaults to the same as between A & DR.
>
> The error says that: The server network address "TCP://DR-server.local:5022"
> can not be reached or does not exist. Check the network address name and that
> the ports for the local and remote endpoints are operational. (Microsoft SQL
> Server, Error: 1418)
> Any ideas? Thanks.
> Panos.
|||Solution found.
In my case the problem was that I had used the SQL Server Configuration
Manager to configure SQL Server to listen on port 5022. The documentation had
led me to believe that this had to be confgured manually when I couldn't
originally get it to work.
Apparently SQLServer will automatically set up that port to listen on when
mirroring is configured. If you set it up manually, it conflicts with the
automatic configuration.
I opened a case with Microsoft to resolve this issue, and they led me down a
path to configure SQLServer to run under a domain account. Previously I had
it configured to run as a local account in the administrators group. This
path led nowhere, the same error always resulted. Currently I have SQL server
to run under a local account that is part of the administrators group on all
three servers, principle, mirror and witness. And it seems to be functioning
fine.
Further, I was led to believe that you had to log in to the server using the
same account that SQL Server was running under in order to properly configure
SQLServer. This is not true either. I use one account to log into the server
for administrative purposes, and SQLServer runs under a different account.
"DBA449" wrote:
[vbcol=seagreen]
> Mirroring is now supported for SQL Server Standard edition 2005 with SP2
> installed. I saw this on the Microsoft web site.
> "Panos Stavroulis." wrote:
|||I had to revisit this!! The issue is resolved by creating a certificate on
the server, then linking it with the end point. Transferring the file to the
target, load and link with the other end point.
Panos.
"DBA449" wrote:
[vbcol=seagreen]
> Microsoft?
> I think we need a little help here?
> What da ya sa...
> "Panos Stavroulis." wrote:
|||that is what microsoft told me to do. But as indicated in the previous post,
it was not necessary
"Panos Stavroulis." wrote:
[vbcol=seagreen]
> I had to revisit this!! The issue is resolved by creating a certificate on
> the server, then linking it with the end point. Transferring the file to the
> target, load and link with the other end point.
> Panos.
> "DBA449" wrote:
sql

Cant attach database

I've created a database inside my ASP.net project, the database is in the App_Data folder as exepcted.

I can't run the aspnet_regsql until I have attached it using SQL Server Management Studio, but when I try to attach the database it doesn't let me navigate through my folders properly.

The database is in C:\Users\Wardie\Documents\FirstProject\App_Data but SQL SMS only lets me navigate to the folder C:\Users\Wardie and no further for some reason, meaning I can't attach the database and can't use the aspnet_regsql service to create the membership tables.

Does anyone know how to fix this?

I had this problem and discovered that SQL Server Management Studio doesnt navigate long directory paths off windows user directories like "My Documents". I ended up creating all my web projects in a sub directory off C:\ something like C:\My Websites\Web1\App_Data\MyDatabase.MDF will work fine. Sorry, but as far as I'm aware you'll have to move your project directory to a shorter path off the root directory.

|||

I guess i'll just have to do the same then, cheers smc.

A bit of a stupid thing though isn't that really?

|||

I agree. There's probably a logical reason... maybe its to do with virtual paths that are used for user "My Directory" folders not being supported. I.e. the "My Documnets" directory is actually something like "C:\Documents and Settings\UserName\My Documents"

Please mark as answered if I was helpful in someway to you.

Can't achieve backups with SQL Server 7.00.1063 on Win2k

Good morning,
On a brand new powerful server, I've had this intermittent problem with
the following message for over a week, when I tried to make a manual or
scheduled backup. I get this message instantly when I start the backup as a
pop-up in Enterprise Manager and in the SQL Server logs.
BackupMedium::ReportIoError: write failure on backup device
'd:\MSSQL7\BACKUP\FmdtestPascal.BAK'. Operating system error
1453(Insufficient quota to complete the requested service.).
The backup to be made is on a small 1.5 gb database. On the array, there's
about 189 Gb of free space on the hard drive. It behaves like a hardware
problem
Hi
Check that disk quotas are not enabled for the destination drive at OS level.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Pascal" wrote:

> Good morning,
> On a brand new powerful server, I've had this intermittent problem with
> the following message for over a week, when I tried to make a manual or
> scheduled backup. I get this message instantly when I start the backup as a
> pop-up in Enterprise Manager and in the SQL Server logs.
> BackupMedium::ReportIoError: write failure on backup device
> 'd:\MSSQL7\BACKUP\FmdtestPascal.BAK'. Operating system error
> 1453(Insufficient quota to complete the requested service.).
> The backup to be made is on a small 1.5 gb database. On the array, there's
> about 189 Gb of free space on the hard drive. It behaves like a hardware
> problem

Can't Access Named Instanced With Network Protocol Disabled

Hi there,
I have a little question here.
I've installed a named instance of MSDE SP4 called mydb, with network
protocols disabled.
Then I noticed that I can't connect to it using OSQL
I tried
'osl -S (local)\mydb -E'
'osl -S localhost\mydb -E'
and both of them can't connect to the database, however.
'osql -S mypcname\mydb -E'
works perfectly... I don't want to manually fill in the machine name for
each connection string can someone tell me why (local) doesn't seem to
connect to the local machine? Does it have anything to do with network
protocols disabled?
Thanks.
Hi
(local) and localhost rely on the network stack to resolve. If you network
is disabled, there is nothing there to resolve the name. localhost resolves
to 127.0.0.1 by TCP/IP.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Marauderz" <marauderz@.nospam.com> wrote in message
news:u1dlfrAqFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi there,
> I have a little question here.
> I've installed a named instance of MSDE SP4 called mydb, with network
> protocols disabled.
> Then I noticed that I can't connect to it using OSQL
> I tried
> 'osl -S (local)\mydb -E'
> 'osl -S localhost\mydb -E'
> and both of them can't connect to the database, however.
> 'osql -S mypcname\mydb -E'
> works perfectly... I don't want to manually fill in the machine name for
> each connection string can someone tell me why (local) doesn't seem to
> connect to the local machine? Does it have anything to do with network
> protocols disabled?
> Thanks.
>

Can't access msde installation from network

I've installed a msde copy on a Win2000 pro computer, but when I try to
connect to it from another computer, it doesn't allow me to log on. Is it a
internal limit?
MSDE doesn't by default have network connectivity turned on. Enable using svrnetcn.exe.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Hawz" <ohawz@.hotmail.com> wrote in message news:uoGN99uNEHA.3988@.TK2MSFTNGP09.phx.gbl...
> I've installed a msde copy on a Win2000 pro computer, but when I try to
> connect to it from another computer, it doesn't allow me to log on. Is it a
> internal limit?
>
|||I've enabled tcp/ip, named pipe and multi-protocal. But still can't get
connected. It says "General network error. Please check network document
ConnectionOpen (PreLoginHandshake()"
And I also can't use telnet to connect to 1433 port.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ObJ9CvyNEHA.2708@.TK2MSFTNGP11.phx.gbl...
> MSDE doesn't by default have network connectivity turned on. Enable using
svrnetcn.exe.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Hawz" <ohawz@.hotmail.com> wrote in message
news:uoGN99uNEHA.3988@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
it a
>
|||Perhaps there's a firewall in between?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Hawz" <ohawz@.hotmail.com> wrote in message news:%2324RZQ7NEHA.644@.tk2msftngp13.phx.gbl...
> I've enabled tcp/ip, named pipe and multi-protocal. But still can't get
> connected. It says "General network error. Please check network document
> ConnectionOpen (PreLoginHandshake()"
> And I also can't use telnet to connect to 1433 port.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:ObJ9CvyNEHA.2708@.TK2MSFTNGP11.phx.gbl...
> svrnetcn.exe.
> news:uoGN99uNEHA.3988@.TK2MSFTNGP09.phx.gbl...
> it a
>
|||This might help:
http://support.microsoft.com/default...b;en-us;826829
-oj
http://www.rac4sql.net
"Hawz" <ohawz@.hotmail.com> wrote in message
news:%2324RZQ7NEHA.644@.tk2msftngp13.phx.gbl...
> I've enabled tcp/ip, named pipe and multi-protocal. But still can't get
> connected. It says "General network error. Please check network document
> ConnectionOpen (PreLoginHandshake()"
> And I also can't use telnet to connect to 1433 port.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:ObJ9CvyNEHA.2708@.TK2MSFTNGP11.phx.gbl...
> svrnetcn.exe.
> news:uoGN99uNEHA.3988@.TK2MSFTNGP09.phx.gbl...
> it a
>

Can't access merge agent properties

I'm trying to set the logging level on a merge agent. I've read
KB312292. When I right click on the agent and select properties some
text flashes in the status bar but no dialog is displayed. I'm using
SQL 2K SP4.
TIA
Is this for pulls configure through ActiveX scripts/programming? or WSM
pulls?
You can't do it for these.
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
"Darren" <darren@.nospam.com> wrote in message
news:eiaIuotFGHA.3120@.TK2MSFTNGP10.phx.gbl...
> I'm trying to set the logging level on a merge agent. I've read KB312292.
> When I right click on the agent and select properties some text flashes in
> the status bar but no dialog is displayed. I'm using SQL 2K SP4.
> TIA
|||Hilary Cotter wrote:
> Is this for pulls configure through ActiveX scripts/programming? or WSM
> pulls?
> You can't do it for these.
>
It was created from a C# app using the merge object. So how can I
change the logging level for that agent without using what is described
in the KB article?
|||use the status event to get messages. You will probably have to configure
the agent to use the verbose profile to see these messages. I would restart
on failure to use the new profile; running with the verbose profile does not
perform well.
Let me know if you need code for this.
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
"Darren" <Darren@.nospam.nospam> wrote in message
news:elcSkBvFGHA.3200@.tk2msftngp13.phx.gbl...
> Hilary Cotter wrote:
> It was created from a C# app using the merge object. So how can I change
> the logging level for that agent without using what is described in the KB
> article?
|||Thanks.
I don't this the status event callback provides enough detail.
I'm trying to get the equivalent of the following options
-output c:\log.txt -outverboselevel 3
I don't see either in the merge profile options.
Hilary Cotter wrote:
> use the status event to get messages. You will probably have to configure
> the agent to use the verbose profile to see these messages. I would restart
> on failure to use the new profile; running with the verbose profile does not
> perform well.
> Let me know if you need code for this.
>

Cannot view log for SQL Agent job (SQL2005)

Hi,
I've set up jobs and output files in SQL2005. When the job is completed, I
would like to check the output file by clicking VIEW button. However, the
button is grey-out.
The owner of the job is sa. What is other permission I missed ?
Thanks.
Hi Johnny
"Johnny" wrote:

> Hi,
> I've set up jobs and output files in SQL2005. When the job is completed, I
> would like to check the output file by clicking VIEW button. However, the
> button is grey-out.
> The owner of the job is sa. What is other permission I missed ?
> Thanks.
According to Books Online it is not available in SQL 2005
From Books Online:
View
In Microsoft SQL Server 2005, this button is disabled for viewing output
files. Instead, use Notepad to view job step output files. In Microsoft SQL
Server 2000, this launches Notepad to view the output file.
John
|||Hi John,
Thank you. Why leave the button there ?
Regards,
Johnny
"John Bell" wrote:

> Hi Johnny
> "Johnny" wrote:
> According to Books Online it is not available in SQL 2005
> From Books Online:
> View
> In Microsoft SQL Server 2005, this button is disabled for viewing output
> files. Instead, use Notepad to view job step output files. In Microsoft SQL
> Server 2000, this launches Notepad to view the output file.
>
> John
>
|||Hi Johnny
"Johnny" wrote:
[vbcol=seagreen]
> Hi John,
> Thank you. Why leave the button there ?
> Regards,
> Johnny
> "John Bell" wrote:
I guess that if you are looking at a SQL 2000 instance of SQL Agent then it
will bring up the log file in Notepad!!!!
John
|||Yes, I always check the output file for any jobs in SQL2000 Agent job.
"John Bell" wrote:

> Hi Johnny
> "Johnny" wrote:
>
> I guess that if you are looking at a SQL 2000 instance of SQL Agent then it
> will bring up the log file in Notepad!!!!
> John
|||"John Bell" wrote:
> I guess that if you are looking at a SQL 2000 instance of SQL Agent then it
> will bring up the log file in Notepad!!!!
> John
And it does!!
John

cannot start SQL server

I was using SQL server service manager with no problem.
I've installed "Business Contact Manager" and now service manager cannot
connect to the server.
Thanks for any advise.
Is the problem that you cannot connect to sql server or that the service
itself cannot start. You can try starting the service through the services
MMC instead of through service manager, by going to:
start->run->services.msc, look for the MSSQLServer (or MSSQL$INSTANCENAME)
service and do a right click->start.
What error are you seeing?
You can check this KB for some suggestions on how to troubleshoot SQL
connectivity:
How to troubleshoot connectivity issues in SQL Server 2000 - ID: 827422
http://support.microsoft.com/?id=827422
Also, check the latest SQL Error logs (c:\program files\microsoft sql
server\mssql\LOG\errorlog - the file with no extension is the latest sql
errorlog) and the application system event logs (in event viewer) for any
errors which occur during the time you attempted to start the SQL Service.
HTH,
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Cannot shrink Transaction Log

Hello. I've got a situation where a live database has stopped working because
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions! I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!
1. Consider the recovery model for the database. If not "simple" then SQL Server will not empty the
log files automatically, and the log will keep growing. When not "simple", only backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log backups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as documented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!
|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:

> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!

Cannot send attachment using sp_send_dbmail

Hi,
I've been scraching my head on this for so long. We have two servers (both
running SQL 2005 64-bit Enterprise Edn SP2 on Windows 2003 Standard 64-bit),
one of the servers is able to send the attachment in the email, whereas the
other is failing with Msg 22051 error:
Msg 22051, Level 16, State 1, Line 0
Attachment file
\\srvr2\data$\Departments\Administration1\Finance\ shared1\New
Test\NewProd\Backup\test.txt is invalid.
The hardware configuration of both of these servers is exactly the same and
the SQL 2005 configuration is also exactly the same. Here is the command
which runs fine on one server but doesn't on the other server:
msdb.dbo.sp_send_dbmail
@.profile_name = N'LOCAL',
@.recipients = N'smarty@.testworld.com',
@.subject = N'TEST',
@.body = N'TEST',
@.file_attachments =
N'\\srvr2\data$\Departments\Administration1\Financ e\shared1\New
Test\NewProd\Backup\test.txt'
Database Mail settings on both the servers is also exactly the same. If I
don't specify the attachment, I receive the email in my inbox. I have also
tried mapping the drive, but still the same error. The strange thing is that
it works on the other server. Very odd. Did anybody come across this
situation..?
Thanks,
My guess would be that the account that sql server runs under on the server
in which this fails does not have access to that share. Is it a domain
account and does that account have the correct perms to access that share.
Andrew J. Kelly SQL MVP
"Smarty" <Smarty@.discussions.microsoft.com> wrote in message
news:B0A66154-E4A3-49AA-9729-68C840868A9C@.microsoft.com...
> Hi,
> I've been scraching my head on this for so long. We have two servers (both
> running SQL 2005 64-bit Enterprise Edn SP2 on Windows 2003 Standard
> 64-bit),
> one of the servers is able to send the attachment in the email, whereas
> the
> other is failing with Msg 22051 error:
> Msg 22051, Level 16, State 1, Line 0
> Attachment file
> \\srvr2\data$\Departments\Administration1\Finance\ shared1\New
> Test\NewProd\Backup\test.txt is invalid.
> The hardware configuration of both of these servers is exactly the same
> and
> the SQL 2005 configuration is also exactly the same. Here is the command
> which runs fine on one server but doesn't on the other server:
> msdb.dbo.sp_send_dbmail
> @.profile_name = N'LOCAL',
> @.recipients = N'smarty@.testworld.com',
> @.subject = N'TEST',
> @.body = N'TEST',
> @.file_attachments =
> N'\\srvr2\data$\Departments\Administration1\Financ e\shared1\New
> Test\NewProd\Backup\test.txt'
> Database Mail settings on both the servers is also exactly the same. If I
> don't specify the attachment, I receive the email in my inbox. I have also
> tried mapping the drive, but still the same error. The strange thing is
> that
> it works on the other server. Very odd. Did anybody come across this
> situation..?
> Thanks,
>
|||Here is your answer Smarty.
Database Mail uses the Microsoft Windows security context of the current
user to control access to files. Therefore, users who are authenticated with
SQL Server Authentication cannot attach files using @.file_attachments.
Chris
"Smarty" <Smarty@.discussions.microsoft.com> wrote in message
news:B0A66154-E4A3-49AA-9729-68C840868A9C@.microsoft.com...
> Hi,
> I've been scraching my head on this for so long. We have two servers (both
> running SQL 2005 64-bit Enterprise Edn SP2 on Windows 2003 Standard
> 64-bit),
> one of the servers is able to send the attachment in the email, whereas
> the
> other is failing with Msg 22051 error:
> Msg 22051, Level 16, State 1, Line 0
> Attachment file
> \\srvr2\data$\Departments\Administration1\Finance\ shared1\New
> Test\NewProd\Backup\test.txt is invalid.
> The hardware configuration of both of these servers is exactly the same
> and
> the SQL 2005 configuration is also exactly the same. Here is the command
> which runs fine on one server but doesn't on the other server:
> msdb.dbo.sp_send_dbmail
> @.profile_name = N'LOCAL',
> @.recipients = N'smarty@.testworld.com',
> @.subject = N'TEST',
> @.body = N'TEST',
> @.file_attachments =
> N'\\srvr2\data$\Departments\Administration1\Financ e\shared1\New
> Test\NewProd\Backup\test.txt'
> Database Mail settings on both the servers is also exactly the same. If I
> don't specify the attachment, I receive the email in my inbox. I have also
> tried mapping the drive, but still the same error. The strange thing is
> that
> it works on the other server. Very odd. Did anybody come across this
> situation..?
> Thanks,
>
|||> Database Mail settings on both the servers is also exactly the same. If I
> don't specify the attachment, I receive the email in my inbox. I have also
> tried mapping the drive, but still the same error.
Mapping a drive creates a drive letter for *your* profile. You need to make
sure the SQL service account has access to the share, not you.

> The strange thing is that
> it works on the other server. Very odd.
Most likely the service accounts are different... one is a domain account
thathas access to the share, and the other one is a local account that does
not.
|||Thanks Andrew for your answer. The account SQL server runs under is a Domain
Service Account with high-level privileges and it has full permissions on the
server. Both the servers run under the same Domain Service Account
"Andrew J. Kelly" wrote:

> My guess would be that the account that sql server runs under on the server
> in which this fails does not have access to that share. Is it a domain
> account and does that account have the correct perms to access that share.
> --
> Andrew J. Kelly SQL MVP
> "Smarty" <Smarty@.discussions.microsoft.com> wrote in message
> news:B0A66154-E4A3-49AA-9729-68C840868A9C@.microsoft.com...
>
>
|||Hi Chris,
We have Windows Authentication enabled on the server. Currently we are
testing this server and there are no users accessing this server except
myself and I have admin permissions on the box and to the share. I also tried
logging in to the server using the Domain Service Account under which SQL
Server and SQL Agent runs, but still I get the same error.
Thanks..
"Chris Wood" wrote:

> Here is your answer Smarty.
> Database Mail uses the Microsoft Windows security context of the current
> user to control access to files. Therefore, users who are authenticated with
> SQL Server Authentication cannot attach files using @.file_attachments.
> Chris
> "Smarty" <Smarty@.discussions.microsoft.com> wrote in message
> news:B0A66154-E4A3-49AA-9729-68C840868A9C@.microsoft.com...
>
>
|||Thanks Aaron for your answer. I tried creating mapped drive using my account
and as well as the SQL Service account, but still no luck. The service
account has full access to the share.
The service accounts under which SQL Server and SQL Agent runs are same on
both the servers, and it is a domain accuont with access to the share. I have
checked it so many times, I am going insane.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Mapping a drive creates a drive letter for *your* profile. You need to make
> sure the SQL service account has access to the share, not you.
>
> Most likely the service accounts are different... one is a domain account
> thathas access to the share, and the other one is a local account that does
> not.
>
>

Cannot See SAN Disk Drives

I've installed sql server 2005active/passive cluster on Win 2003 Ent Edition
boxes connected to 3 SAN drives L, M, S.
When setup asked me where to install datafiles i choose the S drive.
SP1 installed, everything looks good, failover succesfully both ways.
However in SQL Server Managment Studio i don't see L and M drives.
In cluster admin under resources I see all 3 drives which are owned by Sql
Server 2005 group.
But when i try to detach and move the tran log from S to M i don't see M.
When i go to attach db i only see the S drive.
How can i add the L and M drives to be visible to Sql Server?
Thank you
Stop the SQL Server service, add the M & L drives as dependencies to the
resource. Restart everything and use away!
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Alex S" <alexsdba@.aol.com> wrote in message
news:OlJ46FZ6GHA.3288@.TK2MSFTNGP02.phx.gbl...
> I've installed sql server 2005active/passive cluster on Win 2003 Ent
> Edition boxes connected to 3 SAN drives L, M, S.
> When setup asked me where to install datafiles i choose the S drive.
> SP1 installed, everything looks good, failover succesfully both ways.
> However in SQL Server Managment Studio i don't see L and M drives.
> In cluster admin under resources I see all 3 drives which are owned by Sql
> Server 2005 group.
> But when i try to detach and move the tran log from S to M i don't see M.
> When i go to attach db i only see the S drive.
>
> How can i add the L and M drives to be visible to Sql Server?
>
> Thank you
>
>
|||Most likely your SQL Server resource does not depend on the drive L and M
disk resources. In Cluster Admin, bring the SQL Server resource offline, and
add those two disk resources as its dependencies. Bring the SQL Server
resource online, you should be ready to go.
Linchi
"Alex S" wrote:

> I've installed sql server 2005active/passive cluster on Win 2003 Ent Edition
> boxes connected to 3 SAN drives L, M, S.
> When setup asked me where to install datafiles i choose the S drive.
> SP1 installed, everything looks good, failover succesfully both ways.
> However in SQL Server Managment Studio i don't see L and M drives.
> In cluster admin under resources I see all 3 drives which are owned by Sql
> Server 2005 group.
> But when i try to detach and move the tran log from S to M i don't see M.
> When i go to attach db i only see the S drive.
>
> How can i add the L and M drives to be visible to Sql Server?
>
> Thank you
>
>
|||IT WORKED.
THANK YOU
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%23JKiWNZ6GHA.4608@.TK2MSFTNGP05.phx.gbl...
> Stop the SQL Server service, add the M & L drives as dependencies to the
> resource. Restart everything and use away!
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://www.msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Alex S" <alexsdba@.aol.com> wrote in message
> news:OlJ46FZ6GHA.3288@.TK2MSFTNGP02.phx.gbl...
>
|||IT WORKED.
THANK YOU
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:2E663D4D-3A0E-4F09-81D1-F348DA2F15AF@.microsoft.com...[vbcol=seagreen]
> Most likely your SQL Server resource does not depend on the drive L and M
> disk resources. In Cluster Admin, bring the SQL Server resource offline,
> and
> add those two disk resources as its dependencies. Bring the SQL Server
> resource online, you should be ready to go.
> Linchi
> "Alex S" wrote: