Bind failed on TCP port 1433

I installed SP3a and now I am getting this error message in the application
event log. Also, I can only connect to my server using namepipes. But
TCP/IP won't connect, even using 127.0.0.1 or the IP of my machine. The
event logs says that SQL is listening on 127.0.0.1:1433.
Can someone please help. I have not seen the answer and I have been
searching for 2 days now.
Log:
SQL server listening on 127.0.0.1: 1433.
SuperSocket Info: Bind failed on TCP port 1433.
SuperSocket info: (SpnRegister) : Error 1355.
Thanks in advance!
Dolph,
There are two scenarios that I can think of that will cause this situation.
1) You have bounced the SQL Server service programmatically (probably in
C++) and the service has been stopped and restarted too quickly. SQL
Server does not release the port quickly enough before it is restarted
and thinks that another service is using port 1433. The bind, then fails.
2) Another application is using port 1433.
You can check your ports using this on the commandline:
netstat -a
Look at the local address column for <machinename>:ms-sql-s or
<machinename>:1433
On my Inspiron laptop I get these results:
Active Connections
Proto Local Address Foreign Address State
TCP inspiron4100:ftp inspiron4100:0 LISTENING
TCP inspiron4100:http inspiron4100:0 LISTENING
TCP inspiron4100:epmap inspiron4100:0 LISTENING
TCP inspiron4100:https inspiron4100:0 LISTENING
TCP inspiron4100:microsoft-ds inspiron4100:0 LISTENING
TCP inspiron4100:1025 inspiron4100:0 LISTENING
TCP inspiron4100:ms-sql-s inspiron4100:0 LISTENING
TCP inspiron4100:2869 inspiron4100:0 LISTENING
TCP inspiron4100:3389 inspiron4100:0 LISTENING
TCP inspiron4100:1028 inspiron4100:0 LISTENING
TCP inspiron4100:1061 inspiron4100:0 LISTENING
TCP inspiron4100:1134 localhost:1135 ESTABLISHED
TCP inspiron4100:1135 localhost:1134 ESTABLISHED
TCP inspiron4100:netbios-ssn inspiron4100:0 LISTENING
TCP inspiron4100:1056 baym-cs91.msgr.hotmail.com:1863
ESTABLISHED
TCP inspiron4100:1130 82.152.34.166:50002 ESTABLISHED
TCP inspiron4100:1173 msnews.microsoft.com:nntp ESTABLISHED
TCP inspiron4100:1205 64.233.161.107:http ESTABLISHED
UDP inspiron4100:microsoft-ds *:*
UDP inspiron4100:isakmp *:*
UDP inspiron4100:1026 *:*
UDP inspiron4100:1069 *:*
UDP inspiron4100:1070 *:*
UDP inspiron4100:1131 *:*
UDP inspiron4100:ms-sql-m *:*
UDP inspiron4100:3456 *:*
UDP inspiron4100:4500 *:*
UDP inspiron4100:ntp *:*
UDP inspiron4100:1050 *:*
UDP inspiron4100:1057 *:*
UDP inspiron4100:1156 *:*
UDP inspiron4100:1900 *:*
UDP inspiron4100:ntp *:*
UDP inspiron4100:netbios-ns *:*
UDP inspiron4100:netbios-dgm *:*
UDP inspiron4100:1900 *:*
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Dolph Larson wrote:
> I installed SP3a and now I am getting this error message in the application
> event log. Also, I can only connect to my server using namepipes. But
> TCP/IP won't connect, even using 127.0.0.1 or the IP of my machine. The
> event logs says that SQL is listening on 127.0.0.1:1433.
> Can someone please help. I have not seen the answer and I have been
> searching for 2 days now.
> Log:
> SQL server listening on 127.0.0.1: 1433.
> SuperSocket Info: Bind failed on TCP port 1433.
> SuperSocket info: (SpnRegister) : Error 1355.
> Thanks in advance!
|||I didn't finish. :-/
If you still cannot find the cause of the problem, I suggest that you
reboot the server.
In the case of 1) the solution is to put a small pause in your code
before starting the MSSQLServer service again.
In the case of 2) you will need to ensure that your application uses
another port - perhaps there is a setting somewhere (maybe the registry)
where you can tweak this.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Mark Allison wrote:
> Dolph,
> There are two scenarios that I can think of that will cause this situation.
> 1) You have bounced the SQL Server service programmatically (probably in
> C++) and the service has been stopped and restarted too quickly. SQL
> Server does not release the port quickly enough before it is restarted
> and thinks that another service is using port 1433. The bind, then fails.
> 2) Another application is using port 1433.
> You can check your ports using this on the commandline:
> netstat -a
> Look at the local address column for <machinename>:ms-sql-s or
> <machinename>:1433
> On my Inspiron laptop I get these results:
>
> Active Connections
> Proto Local Address Foreign Address State
> TCP inspiron4100:ftp inspiron4100:0 LISTENING
> TCP inspiron4100:http inspiron4100:0 LISTENING
> TCP inspiron4100:epmap inspiron4100:0 LISTENING
> TCP inspiron4100:https inspiron4100:0 LISTENING
> TCP inspiron4100:microsoft-ds inspiron4100:0 LISTENING
> TCP inspiron4100:1025 inspiron4100:0 LISTENING
> TCP inspiron4100:ms-sql-s inspiron4100:0 LISTENING
> TCP inspiron4100:2869 inspiron4100:0 LISTENING
> TCP inspiron4100:3389 inspiron4100:0 LISTENING
> TCP inspiron4100:1028 inspiron4100:0 LISTENING
> TCP inspiron4100:1061 inspiron4100:0 LISTENING
> TCP inspiron4100:1134 localhost:1135 ESTABLISHED
> TCP inspiron4100:1135 localhost:1134 ESTABLISHED
> TCP inspiron4100:netbios-ssn inspiron4100:0 LISTENING
> TCP inspiron4100:1056 baym-cs91.msgr.hotmail.com:1863 ESTABLISHED
> TCP inspiron4100:1130 82.152.34.166:50002 ESTABLISHED
> TCP inspiron4100:1173 msnews.microsoft.com:nntp ESTABLISHED
> TCP inspiron4100:1205 64.233.161.107:http ESTABLISHED
> UDP inspiron4100:microsoft-ds *:*
> UDP inspiron4100:isakmp *:*
> UDP inspiron4100:1026 *:*
> UDP inspiron4100:1069 *:*
> UDP inspiron4100:1070 *:*
> UDP inspiron4100:1131 *:*
> UDP inspiron4100:ms-sql-m *:*
> UDP inspiron4100:3456 *:*
> UDP inspiron4100:4500 *:*
> UDP inspiron4100:ntp *:*
> UDP inspiron4100:1050 *:*
> UDP inspiron4100:1057 *:*
> UDP inspiron4100:1156 *:*
> UDP inspiron4100:1900 *:*
> UDP inspiron4100:ntp *:*
> UDP inspiron4100:netbios-ns *:*
> UDP inspiron4100:netbios-dgm *:*
> UDP inspiron4100:1900 *:*
>
>
>
|||Thanks for trying to help. Sadly, it can not be either case. I am not
doing anything with C++ and I have not downloaded any application lately that
is build on it. Esp. working with SQL server. I looked at the ports with
netstat -an and I see the port being listened by SQL Server. The server just
wont bind to that port. If I can't find a solution soon, I will be left to
do a re-install.
Dolph
"Mark Allison" wrote:

> I didn't finish. :-/
> If you still cannot find the cause of the problem, I suggest that you
> reboot the server.
> In the case of 1) the solution is to put a small pause in your code
> before starting the MSSQLServer service again.
> In the case of 2) you will need to ensure that your application uses
> another port - perhaps there is a setting somewhere (maybe the registry)
> where you can tweak this.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Mark Allison wrote:
>
|||Dolph Larson wrote:
> Can someone please help. I have not seen the answer and I have been
> searching for 2 days now.
For the benefit of any other poor ***s trying to work this one out,
here's what I just found...
We got the "SuperSocket Info: Bind failed on TCP port 1433" error, yet
netstat says the socket _is_ listening. TCPView (from the excellent
sysinternals site) confirms that it's listening, and that it's SQL
server that doing it.
Stumped despite much googling, I suddenly realised we'd recently
installed a new remote access _client_ on this server, Windows Secure
Application Manager from netscreen. This seems to work by hooking the
TCP stack with an LSP. Needless to say, it was this that was messing
up SQL server - using the diagnostics tool to remove the LSP sorted it
out straight away.
Thinking back, I've seen problems with an LSP before: some weird
problem with Norton Antivirus left a machine in a state where ipconfig
said it was dialled up OK, it could ping, but it couldn't do email or
http... Zapping the LSP sorted it (not that I can remember how I
removed it!)
Cheers,
Martin.

Bind failed on TCP port 1433

I installed SP3a and now I am getting this error message in the application
event log. Also, I can only connect to my server using namepipes. But
TCP/IP won't connect, even using 127.0.0.1 or the IP of my machine. The
event logs says that SQL is listening on 127.0.0.1:1433.
Can someone please help. I have not seen the answer and I have been
searching for 2 days now.
Log:
SQL server listening on 127.0.0.1: 1433.
SuperSocket Info: Bind failed on TCP port 1433.
SuperSocket info: (SpnRegister) : Error 1355.
Thanks in advance!Dolph,
There are two scenarios that I can think of that will cause this situation.
1) You have bounced the SQL Server service programmatically (probably in
C++) and the service has been stopped and restarted too quickly. SQL
Server does not release the port quickly enough before it is restarted
and thinks that another service is using port 1433. The bind, then fails.
2) Another application is using port 1433.
You can check your ports using this on the commandline:
netstat -a
Look at the local address column for <machinename>:ms-sql-s or
<machinename>:1433
On my Inspiron laptop I get these results:
Active Connections
Proto Local Address Foreign Address State
TCP inspiron4100:ftp inspiron4100:0 LISTENING
TCP inspiron4100:http inspiron4100:0 LISTENING
TCP inspiron4100:epmap inspiron4100:0 LISTENING
TCP inspiron4100:https inspiron4100:0 LISTENING
TCP inspiron4100:microsoft-ds inspiron4100:0 LISTENING
TCP inspiron4100:1025 inspiron4100:0 LISTENING
TCP inspiron4100:ms-sql-s inspiron4100:0 LISTENING
TCP inspiron4100:2869 inspiron4100:0 LISTENING
TCP inspiron4100:3389 inspiron4100:0 LISTENING
TCP inspiron4100:1028 inspiron4100:0 LISTENING
TCP inspiron4100:1061 inspiron4100:0 LISTENING
TCP inspiron4100:1134 localhost:1135 ESTABLISHED
TCP inspiron4100:1135 localhost:1134 ESTABLISHED
TCP inspiron4100:netbios-ssn inspiron4100:0 LISTENING
TCP inspiron4100:1056 baym-cs91.msgr.hotmail.com:1863
ESTABLISHED
TCP inspiron4100:1130 82.152.34.166:50002 ESTABLISHED
TCP inspiron4100:1173 msnews.microsoft.com:nntp ESTABLISHED
TCP inspiron4100:1205 64.233.161.107:http ESTABLISHED
UDP inspiron4100:microsoft-ds *:*
UDP inspiron4100:isakmp *:*
UDP inspiron4100:1026 *:*
UDP inspiron4100:1069 *:*
UDP inspiron4100:1070 *:*
UDP inspiron4100:1131 *:*
UDP inspiron4100:ms-sql-m *:*
UDP inspiron4100:3456 *:*
UDP inspiron4100:4500 *:*
UDP inspiron4100:ntp *:*
UDP inspiron4100:1050 *:*
UDP inspiron4100:1057 *:*
UDP inspiron4100:1156 *:*
UDP inspiron4100:1900 *:*
UDP inspiron4100:ntp *:*
UDP inspiron4100:netbios-ns *:*
UDP inspiron4100:netbios-dgm *:*
UDP inspiron4100:1900 *:*
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Dolph Larson wrote:
> I installed SP3a and now I am getting this error message in the applicatio
n
> event log. Also, I can only connect to my server using namepipes. But
> TCP/IP won't connect, even using 127.0.0.1 or the IP of my machine. The
> event logs says that SQL is listening on 127.0.0.1:1433.
> Can someone please help. I have not seen the answer and I have been
> searching for 2 days now.
> Log:
> SQL server listening on 127.0.0.1: 1433.
> SuperSocket Info: Bind failed on TCP port 1433.
> SuperSocket info: (SpnRegister) : Error 1355.
> Thanks in advance!|||I didn't finish. :-/
If you still cannot find the cause of the problem, I suggest that you
reboot the server.
In the case of 1) the solution is to put a small pause in your code
before starting the MSSQLServer service again.
In the case of 2) you will need to ensure that your application uses
another port - perhaps there is a setting somewhere (maybe the registry)
where you can tweak this.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Mark Allison wrote:
> Dolph,
> There are two scenarios that I can think of that will cause this situation
.
> 1) You have bounced the SQL Server service programmatically (probably in
> C++) and the service has been stopped and restarted too quickly. SQL
> Server does not release the port quickly enough before it is restarted
> and thinks that another service is using port 1433. The bind, then fails.
> 2) Another application is using port 1433.
> You can check your ports using this on the commandline:
> netstat -a
> Look at the local address column for <machinename>:ms-sql-s or
> <machinename>:1433
> On my Inspiron laptop I get these results:
>
> Active Connections
> Proto Local Address Foreign Address State
> TCP inspiron4100:ftp inspiron4100:0 LISTENING
> TCP inspiron4100:http inspiron4100:0 LISTENING
> TCP inspiron4100:epmap inspiron4100:0 LISTENING
> TCP inspiron4100:https inspiron4100:0 LISTENING
> TCP inspiron4100:microsoft-ds inspiron4100:0 LISTENING
> TCP inspiron4100:1025 inspiron4100:0 LISTENING
> TCP inspiron4100:ms-sql-s inspiron4100:0 LISTENING
> TCP inspiron4100:2869 inspiron4100:0 LISTENING
> TCP inspiron4100:3389 inspiron4100:0 LISTENING
> TCP inspiron4100:1028 inspiron4100:0 LISTENING
> TCP inspiron4100:1061 inspiron4100:0 LISTENING
> TCP inspiron4100:1134 localhost:1135 ESTABLISHED
> TCP inspiron4100:1135 localhost:1134 ESTABLISHED
> TCP inspiron4100:netbios-ssn inspiron4100:0 LISTENING
> TCP inspiron4100:1056 baym-cs91.msgr.hotmail.com:1863 ESTABLISHE
D
> TCP inspiron4100:1130 82.152.34.166:50002 ESTABLISHED
> TCP inspiron4100:1173 msnews.microsoft.com:nntp ESTABLISHED
> TCP inspiron4100:1205 64.233.161.107:http ESTABLISHED
> UDP inspiron4100:microsoft-ds *:*
> UDP inspiron4100:isakmp *:*
> UDP inspiron4100:1026 *:*
> UDP inspiron4100:1069 *:*
> UDP inspiron4100:1070 *:*
> UDP inspiron4100:1131 *:*
> UDP inspiron4100:ms-sql-m *:*
> UDP inspiron4100:3456 *:*
> UDP inspiron4100:4500 *:*
> UDP inspiron4100:ntp *:*
> UDP inspiron4100:1050 *:*
> UDP inspiron4100:1057 *:*
> UDP inspiron4100:1156 *:*
> UDP inspiron4100:1900 *:*
> UDP inspiron4100:ntp *:*
> UDP inspiron4100:netbios-ns *:*
> UDP inspiron4100:netbios-dgm *:*
> UDP inspiron4100:1900 *:*
>
>
>|||Thanks for trying to help. Sadly, it can not be either case. I am not
doing anything with C++ and I have not downloaded any application lately tha
t
is build on it. Esp. working with SQL server. I looked at the ports with
netstat -an and I see the port being listened by SQL Server. The server jus
t
wont bind to that port. If I can't find a solution soon, I will be left to
do a re-install.
Dolph
"Mark Allison" wrote:

> I didn't finish. :-/
> If you still cannot find the cause of the problem, I suggest that you
> reboot the server.
> In the case of 1) the solution is to put a small pause in your code
> before starting the MSSQLServer service again.
> In the case of 2) you will need to ensure that your application uses
> another port - perhaps there is a setting somewhere (maybe the registry)
> where you can tweak this.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Mark Allison wrote:
>|||Dolph Larson wrote:
> Can someone please help. I have not seen the answer and I have been
> searching for 2 days now.
For the benefit of any other poor ***s trying to work this one out,
here's what I just found...
We got the "SuperSocket Info: Bind failed on TCP port 1433" error, yet
netstat says the socket _is_ listening. TCPView (from the excellent
sysinternals site) confirms that it's listening, and that it's SQL
server that doing it.
Stumped despite much googling, I suddenly realised we'd recently
installed a new remote access _client_ on this server, Windows Secure
Application Manager from netscreen. This seems to work by hooking the
TCP stack with an LSP. Needless to say, it was this that was messing
up SQL server - using the diagnostics tool to remove the LSP sorted it
out straight away.
Thinking back, I've seen problems with an LSP before: some weird
problem with Norton Antivirus left a machine in a state where ipconfig
said it was dialled up OK, it could ping, but it couldn't do email or
http... Zapping the LSP sorted it (not that I can remember how I
removed it!)
Cheers,
Martin.

Bind failed on TCP port 1433

I installed SP3a and now I am getting this error message in the application
event log. Also, I can only connect to my server using namepipes. But
TCP/IP won't connect, even using 127.0.0.1 or the IP of my machine. The
event logs says that SQL is listening on 127.0.0.1:1433.
Can someone please help. I have not seen the answer and I have been
searching for 2 days now.
Log:
SQL server listening on 127.0.0.1: 1433.
SuperSocket Info: Bind failed on TCP port 1433.
SuperSocket info: (SpnRegister) : Error 1355.
Thanks in advance!Dolph,
There are two scenarios that I can think of that will cause this situation.
1) You have bounced the SQL Server service programmatically (probably in
C++) and the service has been stopped and restarted too quickly. SQL
Server does not release the port quickly enough before it is restarted
and thinks that another service is using port 1433. The bind, then fails.
2) Another application is using port 1433.
You can check your ports using this on the commandline:
netstat -a
Look at the local address column for <machinename>:ms-sql-s or
<machinename>:1433
On my Inspiron laptop I get these results:
Active Connections
Proto Local Address Foreign Address State
TCP inspiron4100:ftp inspiron4100:0 LISTENING
TCP inspiron4100:http inspiron4100:0 LISTENING
TCP inspiron4100:epmap inspiron4100:0 LISTENING
TCP inspiron4100:https inspiron4100:0 LISTENING
TCP inspiron4100:microsoft-ds inspiron4100:0 LISTENING
TCP inspiron4100:1025 inspiron4100:0 LISTENING
TCP inspiron4100:ms-sql-s inspiron4100:0 LISTENING
TCP inspiron4100:2869 inspiron4100:0 LISTENING
TCP inspiron4100:3389 inspiron4100:0 LISTENING
TCP inspiron4100:1028 inspiron4100:0 LISTENING
TCP inspiron4100:1061 inspiron4100:0 LISTENING
TCP inspiron4100:1134 localhost:1135 ESTABLISHED
TCP inspiron4100:1135 localhost:1134 ESTABLISHED
TCP inspiron4100:netbios-ssn inspiron4100:0 LISTENING
TCP inspiron4100:1056 baym-cs91.msgr.hotmail.com:1863
ESTABLISHED
TCP inspiron4100:1130 82.152.34.166:50002 ESTABLISHED
TCP inspiron4100:1173 msnews.microsoft.com:nntp ESTABLISHED
TCP inspiron4100:1205 64.233.161.107:http ESTABLISHED
UDP inspiron4100:microsoft-ds *:*
UDP inspiron4100:isakmp *:*
UDP inspiron4100:1026 *:*
UDP inspiron4100:1069 *:*
UDP inspiron4100:1070 *:*
UDP inspiron4100:1131 *:*
UDP inspiron4100:ms-sql-m *:*
UDP inspiron4100:3456 *:*
UDP inspiron4100:4500 *:*
UDP inspiron4100:ntp *:*
UDP inspiron4100:1050 *:*
UDP inspiron4100:1057 *:*
UDP inspiron4100:1156 *:*
UDP inspiron4100:1900 *:*
UDP inspiron4100:ntp *:*
UDP inspiron4100:netbios-ns *:*
UDP inspiron4100:netbios-dgm *:*
UDP inspiron4100:1900 *:*
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Dolph Larson wrote:
> I installed SP3a and now I am getting this error message in the application
> event log. Also, I can only connect to my server using namepipes. But
> TCP/IP won't connect, even using 127.0.0.1 or the IP of my machine. The
> event logs says that SQL is listening on 127.0.0.1:1433.
> Can someone please help. I have not seen the answer and I have been
> searching for 2 days now.
> Log:
> SQL server listening on 127.0.0.1: 1433.
> SuperSocket Info: Bind failed on TCP port 1433.
> SuperSocket info: (SpnRegister) : Error 1355.
> Thanks in advance!|||I didn't finish. :-/
If you still cannot find the cause of the problem, I suggest that you
reboot the server.
In the case of 1) the solution is to put a small pause in your code
before starting the MSSQLServer service again.
In the case of 2) you will need to ensure that your application uses
another port - perhaps there is a setting somewhere (maybe the registry)
where you can tweak this.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Mark Allison wrote:
> Dolph,
> There are two scenarios that I can think of that will cause this situation.
> 1) You have bounced the SQL Server service programmatically (probably in
> C++) and the service has been stopped and restarted too quickly. SQL
> Server does not release the port quickly enough before it is restarted
> and thinks that another service is using port 1433. The bind, then fails.
> 2) Another application is using port 1433.
> You can check your ports using this on the commandline:
> netstat -a
> Look at the local address column for <machinename>:ms-sql-s or
> <machinename>:1433
> On my Inspiron laptop I get these results:
>
> Active Connections
> Proto Local Address Foreign Address State
> TCP inspiron4100:ftp inspiron4100:0 LISTENING
> TCP inspiron4100:http inspiron4100:0 LISTENING
> TCP inspiron4100:epmap inspiron4100:0 LISTENING
> TCP inspiron4100:https inspiron4100:0 LISTENING
> TCP inspiron4100:microsoft-ds inspiron4100:0 LISTENING
> TCP inspiron4100:1025 inspiron4100:0 LISTENING
> TCP inspiron4100:ms-sql-s inspiron4100:0 LISTENING
> TCP inspiron4100:2869 inspiron4100:0 LISTENING
> TCP inspiron4100:3389 inspiron4100:0 LISTENING
> TCP inspiron4100:1028 inspiron4100:0 LISTENING
> TCP inspiron4100:1061 inspiron4100:0 LISTENING
> TCP inspiron4100:1134 localhost:1135 ESTABLISHED
> TCP inspiron4100:1135 localhost:1134 ESTABLISHED
> TCP inspiron4100:netbios-ssn inspiron4100:0 LISTENING
> TCP inspiron4100:1056 baym-cs91.msgr.hotmail.com:1863 ESTABLISHED
> TCP inspiron4100:1130 82.152.34.166:50002 ESTABLISHED
> TCP inspiron4100:1173 msnews.microsoft.com:nntp ESTABLISHED
> TCP inspiron4100:1205 64.233.161.107:http ESTABLISHED
> UDP inspiron4100:microsoft-ds *:*
> UDP inspiron4100:isakmp *:*
> UDP inspiron4100:1026 *:*
> UDP inspiron4100:1069 *:*
> UDP inspiron4100:1070 *:*
> UDP inspiron4100:1131 *:*
> UDP inspiron4100:ms-sql-m *:*
> UDP inspiron4100:3456 *:*
> UDP inspiron4100:4500 *:*
> UDP inspiron4100:ntp *:*
> UDP inspiron4100:1050 *:*
> UDP inspiron4100:1057 *:*
> UDP inspiron4100:1156 *:*
> UDP inspiron4100:1900 *:*
> UDP inspiron4100:ntp *:*
> UDP inspiron4100:netbios-ns *:*
> UDP inspiron4100:netbios-dgm *:*
> UDP inspiron4100:1900 *:*
>
>
>|||Thanks for trying to help. Sadly, it can not be either case. I am not
doing anything with C++ and I have not downloaded any application lately that
is build on it. Esp. working with SQL server. I looked at the ports with
netstat -an and I see the port being listened by SQL Server. The server just
wont bind to that port. If I can't find a solution soon, I will be left to
do a re-install. :(
Dolph
"Mark Allison" wrote:
> I didn't finish. :-/
> If you still cannot find the cause of the problem, I suggest that you
> reboot the server.
> In the case of 1) the solution is to put a small pause in your code
> before starting the MSSQLServer service again.
> In the case of 2) you will need to ensure that your application uses
> another port - perhaps there is a setting somewhere (maybe the registry)
> where you can tweak this.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Mark Allison wrote:
> > Dolph,
> >
> > There are two scenarios that I can think of that will cause this situation.
> >
> > 1) You have bounced the SQL Server service programmatically (probably in
> > C++) and the service has been stopped and restarted too quickly. SQL
> > Server does not release the port quickly enough before it is restarted
> > and thinks that another service is using port 1433. The bind, then fails.
> >
> > 2) Another application is using port 1433.
> >
> > You can check your ports using this on the commandline:
> >
> > netstat -a
> >
> > Look at the local address column for <machinename>:ms-sql-s or
> > <machinename>:1433
> >
> > On my Inspiron laptop I get these results:
> >
> >
> > Active Connections
> >
> > Proto Local Address Foreign Address State
> > TCP inspiron4100:ftp inspiron4100:0 LISTENING
> > TCP inspiron4100:http inspiron4100:0 LISTENING
> > TCP inspiron4100:epmap inspiron4100:0 LISTENING
> > TCP inspiron4100:https inspiron4100:0 LISTENING
> > TCP inspiron4100:microsoft-ds inspiron4100:0 LISTENING
> > TCP inspiron4100:1025 inspiron4100:0 LISTENING
> > TCP inspiron4100:ms-sql-s inspiron4100:0 LISTENING
> > TCP inspiron4100:2869 inspiron4100:0 LISTENING
> > TCP inspiron4100:3389 inspiron4100:0 LISTENING
> > TCP inspiron4100:1028 inspiron4100:0 LISTENING
> > TCP inspiron4100:1061 inspiron4100:0 LISTENING
> > TCP inspiron4100:1134 localhost:1135 ESTABLISHED
> > TCP inspiron4100:1135 localhost:1134 ESTABLISHED
> > TCP inspiron4100:netbios-ssn inspiron4100:0 LISTENING
> > TCP inspiron4100:1056 baym-cs91.msgr.hotmail.com:1863 ESTABLISHED
> > TCP inspiron4100:1130 82.152.34.166:50002 ESTABLISHED
> > TCP inspiron4100:1173 msnews.microsoft.com:nntp ESTABLISHED
> > TCP inspiron4100:1205 64.233.161.107:http ESTABLISHED
> > UDP inspiron4100:microsoft-ds *:*
> > UDP inspiron4100:isakmp *:*
> > UDP inspiron4100:1026 *:*
> > UDP inspiron4100:1069 *:*
> > UDP inspiron4100:1070 *:*
> > UDP inspiron4100:1131 *:*
> > UDP inspiron4100:ms-sql-m *:*
> > UDP inspiron4100:3456 *:*
> > UDP inspiron4100:4500 *:*
> > UDP inspiron4100:ntp *:*
> > UDP inspiron4100:1050 *:*
> > UDP inspiron4100:1057 *:*
> > UDP inspiron4100:1156 *:*
> > UDP inspiron4100:1900 *:*
> > UDP inspiron4100:ntp *:*
> > UDP inspiron4100:netbios-ns *:*
> > UDP inspiron4100:netbios-dgm *:*
> > UDP inspiron4100:1900 *:*
> >
> >
> >
> >
> >
>|||Dolph Larson wrote:
> Can someone please help. I have not seen the answer and I have been
> searching for 2 days now.
For the benefit of any other poor ***s trying to work this one out,
here's what I just found...
We got the "SuperSocket Info: Bind failed on TCP port 1433" error, yet
netstat says the socket _is_ listening. TCPView (from the excellent
sysinternals site) confirms that it's listening, and that it's SQL
server that doing it.
Stumped despite much googling, I suddenly realised we'd recently
installed a new remote access _client_ on this server, Windows Secure
Application Manager from netscreen. This seems to work by hooking the
TCP stack with an LSP. Needless to say, it was this that was messing
up SQL server - using the diagnostics tool to remove the LSP sorted it
out straight away.
Thinking back, I've seen problems with an LSP before: some weird
problem with Norton Antivirus left a machine in a state where ipconfig
said it was dialled up OK, it could ping, but it couldn't do email or
http... Zapping the LSP sorted it (not that I can remember how I
removed it!)
Cheers,
Martin.

Bigger log file a database

I have a database, the log file is very long (1 gb)
how to do to reduce the log file?
thank
Check in SEM, to see if your database is in full recovery mode... If so,
Backup the transaction log
ie
Backup log prod to disk="c:\lkjlklj.log" with init
then (until it shrinks) in Query Analyzer
Use prod
go
DBCC Shrinkfile (logfilename, neededsizeinMB)
GO
If the database is in simple recovery mode, just do the shrink
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
"alexander" <alexander@.discussions.microsoft.com> wrote in message
news:8B1EF531-933E-4422-90D9-FFC040F2E2CF@.microsoft.com...
> I have a database, the log file is very long (1 gb)
> how to do to reduce the log file?
>
> thank

Bigger log file a database

I have a database, the log file is very long (1 gb)
how to do to reduce the log file'
thankCheck in SEM, to see if your database is in full recovery mode... If so,
Backup the transaction log
ie
Backup log prod to disk="c:\lkjlklj.log" with init
then (until it shrinks) in Query Analyzer
Use prod
go
DBCC Shrinkfile (logfilename, neededsizeinMB)
GO
If the database is in simple recovery mode, just do the shrink
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
"alexander" <alexander@.discussions.microsoft.com> wrote in message
news:8B1EF531-933E-4422-90D9-FFC040F2E2CF@.microsoft.com...
> I have a database, the log file is very long (1 gb)
> how to do to reduce the log file'
>
> thank

Big Log file that won't shrink....

I have a fairly simple DB, One Log file, one data file, its got half a dozen tables in it that are used for a web based app that allows users to look each other up (think online telephone directory with a few extra bits and you are there), there's a data feed that adds new lines each day as users data changes or new people get added. But this is a couple of hundred or so lines a day, the tables hold about 40,000 data lines as we never delete data just retire it.

The problem is the main data file is about 200Mb, the Log file however is 3.8Gb. Now we did import and re-import various bits initially and clear down tables and such. But I don't understand why i can't shrink the log file. I've tried everything I can think of. I've even detached and re-attached the DB. Still no joy.

The file size/usage is

data file 200Mb size in use 117Mb

log file 3749.99Mb size in use 3719.89Mb

Anyone have any suggestions for things to look at?

many thanks

Steve

Hi,

look here:

http://www.aspfaq.com/show.asp?id=2471

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Big log file in backup

I have backup of database which have log file (LDF file) big around 7GB. At
the moment it seem that I don't have enough space to restore it, Is it
possible that I some hove restore only MDF file or reduce size of LDF in
backup?
Johny
If you don't 'need' the LOG file , you can detach the database , remove the
log file and attach the database with the option as single file (only .mdf)
I'd recommed you to start perfoming BACKUP LOG file on regular period of
time so you will be able to manage the size of the log as well as having a
posibility to restore the data at point of time .
"Johny" <goky2002@.beograd.com> wrote in message
news:%23VoAafBMGHA.532@.TK2MSFTNGP15.phx.gbl...
>I have backup of database which have log file (LDF file) big around 7GB. At
> the moment it seem that I don't have enough space to restore it, Is it
> possible that I some hove restore only MDF file or reduce size of LDF in
> backup?
>

Big log file in backup

I have backup of database which have log file (LDF file) big around 7GB. At
the moment it seem that I don't have enough space to restore it, Is it
possible that I some hove restore only MDF file or reduce size of LDF in
backup?Johny
If you don't 'need' the LOG file , you can detach the database , remove the
log file and attach the database with the option as single file (only .mdf)
I'd recommed you to start perfoming BACKUP LOG file on regular period of
time so you will be able to manage the size of the log as well as having a
posibility to restore the data at point of time .
"Johny" <goky2002@.beograd.com> wrote in message
news:%23VoAafBMGHA.532@.TK2MSFTNGP15.phx.gbl...
>I have backup of database which have log file (LDF file) big around 7GB. At
> the moment it seem that I don't have enough space to restore it, Is it
> possible that I some hove restore only MDF file or reduce size of LDF in
> backup?
>

Big log file in backup

I have backup of database which have log file (LDF file) big around 7GB. At
the moment it seem that I don't have enough space to restore it, Is it
possible that I some hove restore only MDF file or reduce size of LDF in
backup?Johny
If you don't 'need' the LOG file , you can detach the database , remove the
log file and attach the database with the option as single file (only .mdf)
I'd recommed you to start perfoming BACKUP LOG file on regular period of
time so you will be able to manage the size of the log as well as having a
posibility to restore the data at point of time .
"Johny" <goky2002@.beograd.com> wrote in message
news:%23VoAafBMGHA.532@.TK2MSFTNGP15.phx.gbl...
>I have backup of database which have log file (LDF file) big around 7GB. At
> the moment it seem that I don't have enough space to restore it, Is it
> possible that I some hove restore only MDF file or reduce size of LDF in
> backup?
>

Big Log File Help

hi All
in my database The log File Is to Big How can i Trancate
or deleted Pelase Help me
use SQL2000The log is truncated when you back it up. If you don't want to do log
backups then run under the Simple Recovery model and just shrink the
log file as required.
More information:
http://www.aspfaq.com/show.asp?id=2471
David Portas
SQL Server MVP
--

big log file

Hello:
I have a db with 2 data files and 2 log file, but one log files is about
9GB, I would like first reduce the db to only one pair of file, one data and
one log and second reduce the size of log about 1gb or 2. how can I do that?
Best regards,
Owen.Firstly to remove 1 of the log files use
DBCC SHRINKFILE with the EMPTYFILE option.
Then ALTER DATABASE to remove it.
As for shrinking the other log, there are numerous articles on this, here a
few...
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH. Ryan
"Owen" <anibal@.prensa-latina.cu> wrote in message
news:O0nvZdRHGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Hello:
> I have a db with 2 data files and 2 log file, but one log files is about
> 9GB, I would like first reduce the db to only one pair of file, one data
> and
> one log and second reduce the size of log about 1gb or 2. how can I do
> that?
> Best regards,
> Owen.
>

Big Database needs pruning

I have a big sql database running on sql server 2000. The manager wasn't
watching the file sizes and the db made it up to 144 gig and the log is up
to 80 gig. We have been working on pruning this pig but it just has
completely consumed the disk. My expertise is not SQL, but I am the guy for
this issue.
So I am in a quandary. A Delete statement was run to prune the db down, but
that just caused the tlog to chew up the disk space that was left on the
disk. So things got slow so we ended up restarting the sql service and
figure the Checkpoint piece is running in the background rebuilding the
pointers.
Question #1)
Is there a way to determine if Checkpointing is going on rebuilding the
pointers,etc... ? Reason for the curiosity is that Enterprise Manager (EM)
won't respond right now. I can manage the DB's (except for the DB pig) via
SQL Analyzer. It is amazing how quick you can learn tsql when you gotta
keep things up and running.
Question #2)
If/When I get control back I want to chuck the Log via
a) sp_detach_db
b) Delete the Log file
c) sp_attach_single_file_db
Will this work?
Question #3)
There are months and months of data that is easily purgable by date but I
want to avoid making the log file large and clogging up the system again.
Can I use a statement like
Delete from tableA where date_time between '2006-10-01 00:00:00' and
'2006-10-14 23:59:59'
I am unsure how the SQL server will handle this statement. If there are
only 5% of the records in the DB that match this query will the log file
only grow by that amount? I assume it must for backout purposes.
--
Thanks for any help
Paul"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
> --
I've always used the following statements to shrink big logs.
backup log DBName with truncate_only
go
use DBName
go
dbcc shrinkfile(2, 100, TRUNCATEONLY)
go
You should also look into backing up the log file on a regular basis or
changing to the Simple recovery model if your model allows it.|||This really isn't providing any answers to my questions. Do you know about
the Checkpoint, etc...?
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:efbR2j8dHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>>I have a big sql database running on sql server 2000. The manager wasn't
>>watching the file sizes and the db made it up to 144 gig and the log is up
>>to 80 gig. We have been working on pruning this pig but it just has
>>completely consumed the disk. My expertise is not SQL, but I am the guy
>>for this issue.
>> So I am in a quandary. A Delete statement was run to prune the db down,
>> but that just caused the tlog to chew up the disk space that was left on
>> the disk. So things got slow so we ended up restarting the sql service
>> and figure the Checkpoint piece is running in the background rebuilding
>> the pointers.
>> Question #1)
>> Is there a way to determine if Checkpointing is going on rebuilding the
>> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
>> (EM) won't respond right now. I can manage the DB's (except for the DB
>> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
>> you gotta keep things up and running.
>> Question #2)
>> If/When I get control back I want to chuck the Log via
>> a) sp_detach_db
>> b) Delete the Log file
>> c) sp_attach_single_file_db
>> Will this work?
>>
>> Question #3)
>> There are months and months of data that is easily purgable by date but I
>> want to avoid making the log file large and clogging up the system again.
>> Can I use a statement like
>> Delete from tableA where date_time between '2006-10-01 00:00:00' and
>> '2006-10-14 23:59:59'
>> I am unsure how the SQL server will handle this statement. If there are
>> only 5% of the records in the DB that match this query will the log file
>> only grow by that amount? I assume it must for backout purposes.
>> --
> I've always used the following statements to shrink big logs.
> backup log DBName with truncate_only
> go
> use DBName
> go
> dbcc shrinkfile(2, 100, TRUNCATEONLY)
> go
> You should also look into backing up the log file on a regular basis or
> changing to the Simple recovery model if your model allows it.
>|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
> --
> Thanks for any help
> Paul
>|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
Check the SQL Error log, but my guess is that DB is still in recovery mode.
(sorry, I don't recall off the top of my head the command to get this via
QA.
I THINK
select * from master.dbo.sysdatabases w
will give the info, but I don't recall which status you need to look for.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
It MIGHT. But don't do that. It's one of those, "99% of the time it'll
work.. and 1% you're hosed.)
You may want to do an actual transaction log backup (especially if up to now
you have a consistent chain of backups.)
Otherwise, do a backup log PigDB with truncate_only
Then DBCC shrinkfile on the DB log.
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
>
Certainly.
You can even do this in a loop with a SET ROWCOUNT and do backups of the log
in between.
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
Unfortunately, off the top of my head, I'm not 100% sure how to calculate
this (there are ways) but I'd start with that assumption.
Hope this helps.
> --
> Thanks for any help
> Paul
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||I can't believe I didn't think to look in the logs, duh... Only 72 hours to
go, what a stinking pig
I have a follow up but it refers to code pruning it self. I will repost
Paul
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23RdOZD9dHHA.1240@.TK2MSFTNGP04.phx.gbl...
>
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>>I have a big sql database running on sql server 2000. The manager wasn't
>>watching the file sizes and the db made it up to 144 gig and the log is up
>>to 80 gig. We have been working on pruning this pig but it just has
>>completely consumed the disk. My expertise is not SQL, but I am the guy
>>for this issue.
>> So I am in a quandary. A Delete statement was run to prune the db down,
>> but that just caused the tlog to chew up the disk space that was left on
>> the disk. So things got slow so we ended up restarting the sql service
>> and figure the Checkpoint piece is running in the background rebuilding
>> the pointers.
>> Question #1)
>> Is there a way to determine if Checkpointing is going on rebuilding the
>> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
>> (EM) won't respond right now. I can manage the DB's (except for the DB
>> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
>> you gotta keep things up and running.
> Check the SQL Error log, but my guess is that DB is still in recovery
> mode.
> (sorry, I don't recall off the top of my head the command to get this via
> QA.
> I THINK
> select * from master.dbo.sysdatabases w
> will give the info, but I don't recall which status you need to look for.
>
>> Question #2)
>> If/When I get control back I want to chuck the Log via
>> a) sp_detach_db
>> b) Delete the Log file
>> c) sp_attach_single_file_db
>> Will this work?
> It MIGHT. But don't do that. It's one of those, "99% of the time it'll
> work.. and 1% you're hosed.)
> You may want to do an actual transaction log backup (especially if up to
> now you have a consistent chain of backups.)
> Otherwise, do a backup log PigDB with truncate_only
> Then DBCC shrinkfile on the DB log.
>>
>> Question #3)
>> There are months and months of data that is easily purgable by date but I
>> want to avoid making the log file large and clogging up the system again.
>> Can I use a statement like
>> Delete from tableA where date_time between '2006-10-01 00:00:00' and
>> '2006-10-14 23:59:59'
> Certainly.
> You can even do this in a loop with a SET ROWCOUNT and do backups of the
> log in between.
>
>> I am unsure how the SQL server will handle this statement. If there are
>> only 5% of the records in the DB that match this query will the log file
>> only grow by that amount? I assume it must for backout purposes.
> Unfortunately, off the top of my head, I'm not 100% sure how to calculate
> this (there are ways) but I'd start with that assumption.
>
> Hope this helps.
>
>> --
>> Thanks for any help
>> Paul
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||Paul Bergson [MVP-DS] wrote:
> I have a big sql database running on sql server 2000. The manager wasn't
> watching the file sizes and the db made it up to 144 gig and the log is up
> to 80 gig. We have been working on pruning this pig but it just has
> completely consumed the disk. My expertise is not SQL, but I am the guy for
> this issue.
> So I am in a quandary. A Delete statement was run to prune the db down, but
> that just caused the tlog to chew up the disk space that was left on the
> disk. So things got slow so we ended up restarting the sql service and
> figure the Checkpoint piece is running in the background rebuilding the
> pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager (EM)
> won't respond right now. I can manage the DB's (except for the DB pig) via
> SQL Analyzer. It is amazing how quick you can learn tsql when you gotta
> keep things up and running.
From Books Online
Table master..sysdatabases:
Field [status]:
=====Status bits, some of which can be set by the user with sp_dboption (read
only, dbo use only, single user, and so on):
1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.
Multiple bits can be on at the same time.
=========field [status2]:
16384 = ANSI null default; set with sp_dboption.
65536 = concat null yields null , set with sp_dboption.
131072 = recursive triggers, set with sp_dboption.
1048576 = default to local cursor, set with sp_dboption.
8388608 = quoted identifier, set with
sp_dboption.
33554432 = cursor close on commit, set with sp_dboption.
67108864 = ANSI nulls, set with sp_dboption.
268435456 = ANSI warnings, set with sp_dboption.
536870912 = full text enabled, set with sp_fulltext_database.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
You better do the following:
backup log <dbname> with no_log
dbcc shrinkdatabase('<dbname>',TRUNCATEONLY)
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
Yes
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OSMpANFeHHA.1244@.TK2MSFTNGP04.phx.gbl...
>I can't believe I didn't think to look in the logs, duh... Only 72 hours
>to go, what a stinking pig
>
Yeah, I've done the same thing regarding forgetting ;-)
> I have a follow up but it refers to code pruning it self. I will repost
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Big Database needs pruning

I have a big sql database running on sql server 2000. The manager wasn't
watching the file sizes and the db made it up to 144 gig and the log is up
to 80 gig. We have been working on pruning this pig but it just has
completely consumed the disk. My expertise is not SQL, but I am the guy for
this issue.
So I am in a quandary. A Delete statement was run to prune the db down, but
that just caused the tlog to chew up the disk space that was left on the
disk. So things got slow so we ended up restarting the sql service and
figure the Checkpoint piece is running in the background rebuilding the
pointers.
Question #1)
Is there a way to determine if Checkpointing is going on rebuilding the
pointers,etc... ? Reason for the curiosity is that Enterprise Manager (EM)
won't respond right now. I can manage the DB's (except for the DB pig) via
SQL Analyzer. It is amazing how quick you can learn tsql when you gotta
keep things up and running.
Question #2)
If/When I get control back I want to chuck the Log via
a) sp_detach_db
b) Delete the Log file
c) sp_attach_single_file_db
Will this work?
Question #3)
There are months and months of data that is easily purgable by date but I
want to avoid making the log file large and clogging up the system again.
Can I use a statement like
Delete from tableA where date_time between '2006-10-01 00:00:00' and
'2006-10-14 23:59:59'
I am unsure how the SQL server will handle this statement. If there are
only 5% of the records in the DB that match this query will the log file
only grow by that amount? I assume it must for backout purposes.
Thanks for any help
Paul
"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
> --
I've always used the following statements to shrink big logs.
backup log DBName with truncate_only
go
use DBName
go
dbcc shrinkfile(2, 100, TRUNCATEONLY)
go
You should also look into backing up the log file on a regular basis or
changing to the Simple recovery model if your model allows it.
|||This really isn't providing any answers to my questions. Do you know about
the Checkpoint, etc...?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:efbR2j8dHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
> I've always used the following statements to shrink big logs.
> backup log DBName with truncate_only
> go
> use DBName
> go
> dbcc shrinkfile(2, 100, TRUNCATEONLY)
> go
> You should also look into backing up the log file on a regular basis or
> changing to the Simple recovery model if your model allows it.
>
|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
> --
> Thanks for any help
> Paul
>
|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
Check the SQL Error log, but my guess is that DB is still in recovery mode.
(sorry, I don't recall off the top of my head the command to get this via
QA.
I THINK
select * from master.dbo.sysdatabases w
will give the info, but I don't recall which status you need to look for.

> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
It MIGHT. But don't do that. It's one of those, "99% of the time it'll
work.. and 1% you're hosed.)
You may want to do an actual transaction log backup (especially if up to now
you have a consistent chain of backups.)
Otherwise, do a backup log PigDB with truncate_only
Then DBCC shrinkfile on the DB log.
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
>
Certainly.
You can even do this in a loop with a SET ROWCOUNT and do backups of the log
in between.

> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
Unfortunately, off the top of my head, I'm not 100% sure how to calculate
this (there are ways) but I'd start with that assumption.
Hope this helps.

> --
> Thanks for any help
> Paul
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||I can't believe I didn't think to look in the logs, duh... Only 72 hours to
go, what a stinking pig
I have a follow up but it refers to code pruning it self. I will repost

Paul
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23RdOZD9dHHA.1240@.TK2MSFTNGP04.phx.gbl...
>
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
> Check the SQL Error log, but my guess is that DB is still in recovery
> mode.
> (sorry, I don't recall off the top of my head the command to get this via
> QA.
> I THINK
> select * from master.dbo.sysdatabases w
> will give the info, but I don't recall which status you need to look for.
>
> It MIGHT. But don't do that. It's one of those, "99% of the time it'll
> work.. and 1% you're hosed.)
> You may want to do an actual transaction log backup (especially if up to
> now you have a consistent chain of backups.)
> Otherwise, do a backup log PigDB with truncate_only
> Then DBCC shrinkfile on the DB log.
> Certainly.
> You can even do this in a loop with a SET ROWCOUNT and do backups of the
> log in between.
>
> Unfortunately, off the top of my head, I'm not 100% sure how to calculate
> this (there are ways) but I'd start with that assumption.
>
> Hope this helps.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
|||Paul Bergson [MVP-DS] wrote:
> I have a big sql database running on sql server 2000. The manager wasn't
> watching the file sizes and the db made it up to 144 gig and the log is up
> to 80 gig. We have been working on pruning this pig but it just has
> completely consumed the disk. My expertise is not SQL, but I am the guy for
> this issue.
> So I am in a quandary. A Delete statement was run to prune the db down, but
> that just caused the tlog to chew up the disk space that was left on the
> disk. So things got slow so we ended up restarting the sql service and
> figure the Checkpoint piece is running in the background rebuilding the
> pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager (EM)
> won't respond right now. I can manage the DB's (except for the DB pig) via
> SQL Analyzer. It is amazing how quick you can learn tsql when you gotta
> keep things up and running.
From Books Online
Table master..sysdatabases:
Field [status]:
=====
Status bits, some of which can be set by the user with sp_dboption (read
only, dbo use only, single user, and so on):
1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.
Multiple bits can be on at the same time.
=========
field [status2]:
16384 = ANSI null default; set with sp_dboption.
65536 = concat null yields null , set with sp_dboption.
131072 = recursive triggers, set with sp_dboption.
1048576 = default to local cursor, set with sp_dboption.
8388608 = quoted identifier, set with
sp_dboption.
33554432 = cursor close on commit, set with sp_dboption.
67108864 = ANSI nulls, set with sp_dboption.
268435456 = ANSI warnings, set with sp_dboption.
536870912 = full text enabled, set with sp_fulltext_database.

> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
You better do the following:
backup log <dbname> with no_log
dbcc shrinkdatabase('<dbname>',TRUNCATEONLY)

> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
Yes

> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OSMpANFeHHA.1244@.TK2MSFTNGP04.phx.gbl...
>I can't believe I didn't think to look in the logs, duh... Only 72 hours
>to go, what a stinking pig
>
Yeah, I've done the same thing regarding forgetting ;-)

> I have a follow up but it refers to code pruning it self. I will repost
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Big Database needs pruning

I have a big sql database running on sql server 2000. The manager wasn't
watching the file sizes and the db made it up to 144 gig and the log is up
to 80 gig. We have been working on pruning this pig but it just has
completely consumed the disk. My expertise is not SQL, but I am the guy for
this issue.
So I am in a quandary. A Delete statement was run to prune the db down, but
that just caused the tlog to chew up the disk space that was left on the
disk. So things got slow so we ended up restarting the sql service and
figure the Checkpoint piece is running in the background rebuilding the
pointers.
Question #1)
Is there a way to determine if Checkpointing is going on rebuilding the
pointers,etc... ? Reason for the curiosity is that Enterprise Manager (EM)
won't respond right now. I can manage the DB's (except for the DB pig) via
SQL Analyzer. It is amazing how quick you can learn tsql when you gotta
keep things up and running.
Question #2)
If/When I get control back I want to chuck the Log via
a) sp_detach_db
b) Delete the Log file
c) sp_attach_single_file_db
Will this work?
Question #3)
There are months and months of data that is easily purgable by date but I
want to avoid making the log file large and clogging up the system again.
Can I use a statement like
Delete from tableA where date_time between '2006-10-01 00:00:00' and
'2006-10-14 23:59:59'
I am unsure how the SQL server will handle this statement. If there are
only 5% of the records in the DB that match this query will the log file
only grow by that amount? I assume it must for backout purposes.
Thanks for any help
Paul"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
> --
I've always used the following statements to shrink big logs.
backup log DBName with truncate_only
go
use DBName
go
dbcc shrinkfile(2, 100, TRUNCATEONLY)
go
You should also look into backing up the log file on a regular basis or
changing to the Simple recovery model if your model allows it.|||This really isn't providing any answers to my questions. Do you know about
the Checkpoint, etc...?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:efbR2j8dHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
> I've always used the following statements to shrink big logs.
> backup log DBName with truncate_only
> go
> use DBName
> go
> dbcc shrinkfile(2, 100, TRUNCATEONLY)
> go
> You should also look into backing up the log file on a regular basis or
> changing to the Simple recovery model if your model allows it.
>|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
> --
> Thanks for any help
> Paul
>|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
Check the SQL Error log, but my guess is that DB is still in recovery mode.
(sorry, I don't recall off the top of my head the command to get this via
QA.
I THINK
select * from master.dbo.sysdatabases w
will give the info, but I don't recall which status you need to look for.

> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
It MIGHT. But don't do that. It's one of those, "99% of the time it'll
work.. and 1% you're hosed.)
You may want to do an actual transaction log backup (especially if up to now
you have a consistent chain of backups.)
Otherwise, do a backup log PigDB with truncate_only
Then DBCC shrinkfile on the DB log.
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
>
Certainly.
You can even do this in a loop with a SET ROWCOUNT and do backups of the log
in between.

> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
Unfortunately, off the top of my head, I'm not 100% sure how to calculate
this (there are ways) but I'd start with that assumption.
Hope this helps.

> --
> Thanks for any help
> Paul
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||I can't believe I didn't think to look in the logs, duh... Only 72 hours to
go, what a stinking pig
I have a follow up but it refers to code pruning it self. I will repost
Paul
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23RdOZD9dHHA.1240@.TK2MSFTNGP04.phx.gbl...
>
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
> Check the SQL Error log, but my guess is that DB is still in recovery
> mode.
> (sorry, I don't recall off the top of my head the command to get this via
> QA.
> I THINK
> select * from master.dbo.sysdatabases w
> will give the info, but I don't recall which status you need to look for.
>
> It MIGHT. But don't do that. It's one of those, "99% of the time it'll
> work.. and 1% you're hosed.)
> You may want to do an actual transaction log backup (especially if up to
> now you have a consistent chain of backups.)
> Otherwise, do a backup log PigDB with truncate_only
> Then DBCC shrinkfile on the DB log.
> Certainly.
> You can even do this in a loop with a SET ROWCOUNT and do backups of the
> log in between.
>
> Unfortunately, off the top of my head, I'm not 100% sure how to calculate
> this (there are ways) but I'd start with that assumption.
>
> Hope this helps.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||Paul Bergson [MVP-DS] wrote:
> I have a big sql database running on sql server 2000. The manager wasn't
> watching the file sizes and the db made it up to 144 gig and the log is up
> to 80 gig. We have been working on pruning this pig but it just has
> completely consumed the disk. My expertise is not SQL, but I am the guy f
or
> this issue.
> So I am in a quandary. A Delete statement was run to prune the db down, b
ut
> that just caused the tlog to chew up the disk space that was left on the
> disk. So things got slow so we ended up restarting the sql service and
> figure the Checkpoint piece is running in the background rebuilding the
> pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager (EM
)
> won't respond right now. I can manage the DB's (except for the DB pig) vi
a
> SQL Analyzer. It is amazing how quick you can learn tsql when you gotta
> keep things up and running.
From Books Online
Table master..sysdatabases:
Field [status]:
=====
Status bits, some of which can be set by the user with sp_dboption (read
only, dbo use only, single user, and so on):
1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.
Multiple bits can be on at the same time.
=========
field [status2]:
16384 = ANSI null default; set with sp_dboption.
65536 = concat null yields null , set with sp_dboption.
131072 = recursive triggers, set with sp_dboption.
1048576 = default to local cursor, set with sp_dboption.
8388608 = quoted identifier, set with
sp_dboption.
33554432 = cursor close on commit, set with sp_dboption.
67108864 = ANSI nulls, set with sp_dboption.
268435456 = ANSI warnings, set with sp_dboption.
536870912 = full text enabled, set with sp_fulltext_database.

> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
You better do the following:
backup log <dbname> with no_log
dbcc shrinkdatabase('<dbname>',TRUNCATEONLY)

> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
Yes

> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OSMpANFeHHA.1244@.TK2MSFTNGP04.phx.gbl...
>I can't believe I didn't think to look in the logs, duh... Only 72 hours
>to go, what a stinking pig
>
Yeah, I've done the same thing regarding forgetting ;-)

> I have a follow up but it refers to code pruning it self. I will repost
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Best way to select and insert a huge table in the same database

insert A (...) select ... from B inner join C on ....
will create huge transact log. Any better way?See 'Minimally Logged Operations' and 'Bulk-Logged Recovery Model' on BOL to
see if that can help in your specific case.
Ben Nevarez, MCDBA, OCP
Database Administrator
"nick" wrote:

> insert A (...) select ... from B inner join C on ....
> will create huge transact log. Any better way?|||On Sun, 26 Mar 2006 14:49:44 -0800, nick wrote:

>insert A (...) select ... from B inner join C on ....
>will create huge transact log. Any better way?
Hi Nick,
If you can't use bulk insert in your situation, try doing it in batches:
INSERT A (...)
SELECT ...
FROM B
INNER JOIN C
ON ...
WHERE SomeColumn <= 10000
CHECKPOINT
-- BACKUP LOG <database_name> TO <backup_device>
INSERT A (...)
SELECT ...
FROM B
INNER JOIN C
ON ...
WHERE SomeColumn > 10000
AND SomeColumn <= 20000
CHECKPOINT
-- BACKUP LOG <database_name> TO <backup_device>
(...)
INSERT A (...)
SELECT ...
FROM B
INNER JOIN C
ON ...
WHERE SomeColumn > 90000
CHECKPOINT
-- BACKUP LOG <database_name> TO <backup_device>
If your recovery model is simple, you don't need the backup log
commands. For full or bulk-logged recovery, uncomment these statements.
Make sure that this batch is NOT included in a transaction. If you need
to be able to rollback the entire operation in case of any error, make a
full database backup just before starting and restore to it if things go
wrong.
Hugo Kornelis, SQL Server MVP

Best way to maintain up to date SQL Jobs and SSIS on failover Serv

Hi,
on SQL 2005 we use log shipping to maintain our production server to an
alternate server. Now I would like to do something similar with our SQL
Agent jobs and SSIS. While those jobs are enabled on the production server,
I would like them to become disable on the alternate server, this process
would be scheduled once per day.
I'm sure there are many ways to achieve this, but I am wondering what would
be the most efficient.
any ideas ?
Thanks
Fred
ok, I just figured out how to transfer/disable jobs using the Transfer Job
task in SSIS, it's quite nice actually.
But, I am still in the dark on how to transfer SSIS from one server to
another.
BTW, a backup and restore of MSDB is out of the question.
Thanks
"FredDBA" wrote:

> Hi,
> on SQL 2005 we use log shipping to maintain our production server to an
> alternate server. Now I would like to do something similar with our SQL
> Agent jobs and SSIS. While those jobs are enabled on the production server,
> I would like them to become disable on the alternate server, this process
> would be scheduled once per day.
> I'm sure there are many ways to achieve this, but I am wondering what would
> be the most efficient.
> any ideas ?
> Thanks
> Fred

Best way to maintain up to date SQL Jobs and SSIS on failover Serv

Hi,
on SQL 2005 we use log shipping to maintain our production server to an
alternate server. Now I would like to do something similar with our SQL
Agent jobs and SSIS. While those jobs are enabled on the production server,
I would like them to become disable on the alternate server, this process
would be scheduled once per day.
I'm sure there are many ways to achieve this, but I am wondering what would
be the most efficient.
any ideas ?
Thanks
Fredok, I just figured out how to transfer/disable jobs using the Transfer Job
task in SSIS, it's quite nice actually.
But, I am still in the dark on how to transfer SSIS from one server to
another.
BTW, a backup and restore of MSDB is out of the question.
Thanks
"FredDBA" wrote:

> Hi,
> on SQL 2005 we use log shipping to maintain our production server to an
> alternate server. Now I would like to do something similar with our SQL
> Agent jobs and SSIS. While those jobs are enabled on the production serve
r,
> I would like them to become disable on the alternate server, this process
> would be scheduled once per day.
> I'm sure there are many ways to achieve this, but I am wondering what woul
d
> be the most efficient.
> any ideas ?
> Thanks
> Fred|||If its not too late, consider storing your SSIS packages in the
filesystem and just keep copies on your DR machines
Terry|||Anotther thing we do here (mostly for legacy dts packages) is to
restore the MSDB as another name. During DR, rename that database to
MSDB and all the objects will be there.
Terry

Best way to maintain up to date SQL Jobs and SSIS on failover Serv

Hi,
on SQL 2005 we use log shipping to maintain our production server to an
alternate server. Now I would like to do something similar with our SQL
Agent jobs and SSIS. While those jobs are enabled on the production server,
I would like them to become disable on the alternate server, this process
would be scheduled once per day.
I'm sure there are many ways to achieve this, but I am wondering what would
be the most efficient.
any ideas ?
Thanks
Fredok, I just figured out how to transfer/disable jobs using the Transfer Job
task in SSIS, it's quite nice actually.
But, I am still in the dark on how to transfer SSIS from one server to
another.
BTW, a backup and restore of MSDB is out of the question.
Thanks
"FredDBA" wrote:
> Hi,
> on SQL 2005 we use log shipping to maintain our production server to an
> alternate server. Now I would like to do something similar with our SQL
> Agent jobs and SSIS. While those jobs are enabled on the production server,
> I would like them to become disable on the alternate server, this process
> would be scheduled once per day.
> I'm sure there are many ways to achieve this, but I am wondering what would
> be the most efficient.
> any ideas ?
> Thanks
> Fred|||If its not too late, consider storing your SSIS packages in the
filesystem and just keep copies on your DR machines
Terry|||Anotther thing we do here (mostly for legacy dts packages) is to
restore the MSDB as another name. During DR, rename that database to
MSDB and all the objects will be there.
Terry

best way to log out when running the job

Hi,
When I run the job, I am not sure whether it could be done at the table
level to log users out. I would not want to keep them out of the whole
database...only the tables I are updating. Also, once the job is running, I
would like to prevent any users from accessing the tables I am updating unti
l
the end of the job.
Is there anyway to do that?
Thanks
EdYou can take an Exclusive lock on the table and hold it until you are done.
That will not allow anyone to access the table as they will be blocked. Not
sure if they will like you for doing that though<g>.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:933F98D6-BA8A-46D6-A136-35C59BE7F5D6@.microsoft.com...
> Hi,
> When I run the job, I am not sure whether it could be done at the table
> level to log users out. I would not want to keep them out of the whole
> database...only the tables I are updating. Also, once the job is running,
> I
> would like to prevent any users from accessing the tables I am updating
> until
> the end of the job.
> Is there anyway to do that?
> Thanks
> Ed|||Andrew,
Thanks for your answer. I would also like to know what is the best way to
log users out before the job is running. Again, I only need to log them out
of the tables that I will be updating not the whole database.
Thanks again
Ed
"Andrew J. Kelly" wrote:

> You can take an Exclusive lock on the table and hold it until you are done
.
> That will not allow anyone to access the table as they will be blocked. N
ot
> sure if they will like you for doing that though<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:933F98D6-BA8A-46D6-A136-35C59BE7F5D6@.microsoft.com...
>
>|||You can't "Log out" users from a table. They log in to the server and
potentially have access to one or more db's in that server. A user only
takes out locks while they are accessing the data. So any locks they may
have on any specific table are released once they are done with the query or
the transaction is committed or rolled back. Why do you care if they are in
the table or not? Maybe if you explain in more detail what you are
attempting to do we can suggest an appropriate action.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:3937FA07-1F29-497C-99AA-608E89D0E807@.microsoft.com...
> Andrew,
> Thanks for your answer. I would also like to know what is the best way
> to
> log users out before the job is running. Again, I only need to log them
> out
> of the tables that I will be updating not the whole database.
> Thanks again
> Ed
> "Andrew J. Kelly" wrote:
>|||The major reason why I would like to "Log Out" users is because the job has
to be run in the daytime and the job has to be finished as quick as
possible. with so many users using the resources of the server (hugh data
warehouse to be accessed by so many people), I would like to "Log Out" all
users and block the access of all updating tables except the one running by
the job.
any suggestion
Thanks
Ed
"Andrew J. Kelly" wrote:

> You can't "Log out" users from a table. They log in to the server and
> potentially have access to one or more db's in that server. A user only
> takes out locks while they are accessing the data. So any locks they may
> have on any specific table are released once they are done with the query
or
> the transaction is committed or rolled back. Why do you care if they are
in
> the table or not? Maybe if you explain in more detail what you are
> attempting to do we can suggest an appropriate action.
> --
> Andrew J. Kelly SQL MVP
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:3937FA07-1F29-497C-99AA-608E89D0E807@.microsoft.com...
>
>|||Well you can KILL a connection that has a lock in the table you want but
that obviously ruins their day. Other than that there is no way to remove a
users locks on a table until that user has finished accessing it and
committed or rolled back their transaction if they had one. As I stated
earlier the only real way to prevent them from accessing the table is to
take out an exclusive lock. You may have to wait a while until all current
users in that table have finished before the lock takes hold. But no new
users can access it either. It almost sounds like you need to place the db
in a single user mode, do the work and return it as soon as possible. You
can specify a termination option to kick out the current users after a
period of time with the alter database command.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:5C6CCABD-739B-472F-892B-4F3B3F53DB31@.microsoft.com...
> The major reason why I would like to "Log Out" users is because the job
> has
> to be run in the daytime and the job has to be finished as quick as
> possible. with so many users using the resources of the server (hugh data
> warehouse to be accessed by so many people), I would like to "Log Out" all
> users and block the access of all updating tables except the one running
> by
> the job.
> any suggestion
> Thanks
> Ed
> "Andrew J. Kelly" wrote:
>