How to shrink log file manually

Hello !
Is there any t-sql statement to shrink log file?
--
S.JCheck out DBCC SHRINKFILE. Also, about in the middle for below article, you find some links
regarding shrinking of log files:
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/
"MS" <fastaccess@.hotmail.com> wrote in message news:Ouue84ChEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Hello !
> Is there any t-sql statement to shrink log file?
> --
> S.J
>

How to shrink log file manually

Hello !
Is there any t-sql statement to shrink log file?
S.J
Check out DBCC SHRINKFILE. Also, about in the middle for below article, you find some links
regarding shrinking of log files:
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/
"MS" <fastaccess@.hotmail.com> wrote in message news:Ouue84ChEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Hello !
> Is there any t-sql statement to shrink log file?
> --
> S.J
>
sql

How to shrink log file manually

Hello !
Is there any t-sql statement to shrink log file?
S.JCheck out DBCC SHRINKFILE. Also, about in the middle for below article, you
find some links
regarding shrinking of log files:
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/
"MS" <fastaccess@.hotmail.com> wrote in message news:Ouue84ChEHA.1356@.TK2MSFTNGP09.phx.gbl...

> Hello !
> Is there any t-sql statement to shrink log file?
> --
> S.J
>

How to set transactions timeout?

Is there a way for a client to tell a server to rollback a transaction after a certain time elapses?

The scenario: I execute an update statement in a RepeatableRead transaction, but the client may lose network connectivity before the Commit is issued. If this occurs after ExecuteNonQuery, then the process on the sql server is holding a large amount of locks, and the server takes several minutes before it releases those locks.

Meanwhile, other transactions are attempting to run, but are getting selected as the deadlock victims because they are waiting on resources that the disconnected client had locked. This really backs things up.

I have found some settings that can be done on the server side that will decrease the time it takes for a transaction WAITING on resources to timeout. I.E. this just makes those that are being blocked timeout faster. But it is the blockING process that I want rolledback sooner. It doesn't get selected as the deadlock victim because it has all the resources it needs. It is not waiting on any resources, but instead the server is simply waiting for another query or a commit, which it never gets because the client lost network connectivity. The command timeout doesn't seem to have an affect, because the command itself completes.

I tested this by having a client display a modal dialog box just before the commit statement, and then I unplugged the network cable. I then used activity monitor on the server to view the locks being held. I refreshed it several times, and I believe at least 5 minutes pasted before the locks were released.

In the production environment, users will be losing connectivity very often. I'm sure someone will suggest a different architecture where the database logic is server side, and data is sent to/from clients in another manner so that this situation can be handled by the server side app. Right now that is not an option. I am running the database in 2000, but sql server 2005 with 2000 compatibility mode is also possible.

Thanks in advance.

If you are using distributed transactions, the timeout can be configured via Control Panel->Administrative Tools->Component Services.

See also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772404&SiteID=1

|||I'm not using distributed transactions unless that's the default for ADO.NET, but I'll read up on them and see if maybe that's what I should use. Thanks.

How to set transactions timeout?

Is there a way for a client to tell a server to rollback a transaction after a certain time elapses?

The scenario: I execute an update statement in a RepeatableRead transaction, but the client may lose network connectivity before the Commit is issued. If this occurs after ExecuteNonQuery, then the process on the sql server is holding a large amount of locks, and the server takes several minutes before it releases those locks.

Meanwhile, other transactions are attempting to run, but are getting selected as the deadlock victims because they are waiting on resources that the disconnected client had locked. This really backs things up.

I have found some settings that can be done on the server side that will decrease the time it takes for a transaction WAITING on resources to timeout. I.E. this just makes those that are being blocked timeout faster. But it is the blockING process that I want rolledback sooner. It doesn't get selected as the deadlock victim because it has all the resources it needs. It is not waiting on any resources, but instead the server is simply waiting for another query or a commit, which it never gets because the client lost network connectivity. The command timeout doesn't seem to have an affect, because the command itself completes.

I tested this by having a client display a modal dialog box just before the commit statement, and then I unplugged the network cable. I then used activity monitor on the server to view the locks being held. I refreshed it several times, and I believe at least 5 minutes pasted before the locks were released.

In the production environment, users will be losing connectivity very often. I'm sure someone will suggest a different architecture where the database logic is server side, and data is sent to/from clients in another manner so that this situation can be handled by the server side app. Right now that is not an option. I am running the database in 2000, but sql server 2005 with 2000 compatibility mode is also possible.

Thanks in advance.

If you are using distributed transactions, the timeout can be configured via Control Panel->Administrative Tools->Component Services.

See also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772404&SiteID=1

|||I'm not using distributed transactions unless that's the default for ADO.NET, but I'll read up on them and see if maybe that's what I should use. Thanks.

how to set multiple local variable from single select statement

is it possible to set the value of multiple local variables using just 1
statement. For example assume a simple table 'E' with 3 columns 'empID',
'empName', 'empToken'. I can get their values with
declare @.ENAME as char(30), @.ETOKEN as int
SET @.ENAME = (SELECT empName FROM E WHERE empID = 1)
SET @.ETOKEN = (SELECT empToken FROM E WHERE empID = 1)
What I want to do is compine the two assignments into 1 single statement so
that the db never has to be looked up more than once. e.g.
SET @.ENAME, @.ETOKEN = (SELECT empName, empToken FROM E WHERE empID = 1)
is this possible and if it is, what is the synthax?try this.. hope this helps.
SELECT @.ENAME = empName,
@.ETOKEN = empToken
FROM E WHERE empID = 1
--|||HI,
sure:
SELECT @.ENAME = empName , @.ETOKEN = emptoken FROM E WHERE empID = 1
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--

How to set date format as mm/dd/yyyyThh:mm:ss.ttttZ+00:00?

Hi,
I use "For XML" statement to export xml, all are my expected. But the
datetime field value is not my expected format(e.g.
ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?
You would have to use CONVERT(nvarchar(40), datetimeval, 127) + N'Z' in the
select clause since SQL Server currently does not know what timezone you
could possibly want.
Best regards
Michael
"ABC" <abc@.abc.com> wrote in message
news:urvzmAilHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use "For XML" statement to export xml, all are my expected. But the
> datetime field value is not my expected format(e.g.
> ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
> date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?
>
>

How to set date format as mm/dd/yyyyThh:mm:ss.ttttZ+00:00?

Hi,
I use "For XML" statement to export xml, all are my expected. But the
datetime field value is not my expected format(e.g.
ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?You would have to use CONVERT(nvarchar(40), datetimeval, 127) + N'Z' in the
select clause since SQL Server currently does not know what timezone you
could possibly want.
Best regards
Michael
"ABC" <abc@.abc.com> wrote in message
news:urvzmAilHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use "For XML" statement to export xml, all are my expected. But the
> datetime field value is not my expected format(e.g.
> ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
> date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?
>
>

how to send sql mail using condition statement

I try to run the following code in DTS, but i not work. What i want is
if do not have duplicate data, then the table will be dropped. While
if have duplicate data, the table will not be dropped but an email
notification will be sent. Why when there is no duplicate data, the
email still will be sent out?
if not exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
drop table [dbo].[Rewards_CatalogProducts_CS]
end
else
if exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
use master
go
declare @.FROM NVARCHAR(4000),
@.FROM_NAME NVARCHAR(4000),
@.TO NVARCHAR(4000),
@.CC NVARCHAR(4000),
@.BCC NVARCHAR(4000),
@.priority NVARCHAR(10),
@.subject NVARCHAR(4000),
@.message NVARCHAR(4000),
@.type NVARCHAR(100),
@.attachments NVARCHAR(4000),
@.codepage INT,
@.rc INT
select @.FROM = N'sqlmail@.cyber-village.net',
@.FROM_NAME = N'ChangMian',
@.TO = N'tchangmian@.yahoo.com.sg',
@.CC = N'changmian@.cyber-village.net',
@.priority = N'High',
@.subject = N'Ei,can wor',
@.message = N'&

---

Hello SQL Server SMTP SQL
Mail

---

',
@.type = N'text/html',
@.attachments = N'',
@.codepage = 0
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.FROM,
@.TO = @.TO,
@.CC = @.CC,
@.priority = @.priority,
@.subject = @.subject,
@.message = @.message,
@.type = @.type,
@.attachments = @.attachments,
@.codepage = @.codepage,
@.server = N'mail.cyber-village.net'
select RC = @.rc
goYour send mail script is in a different batch because of the 'GO' batch
terminator. Consider revising your script like the example below:
declare @.FROM NVARCHAR(4000),
@.FROM_NAME NVARCHAR(4000),
@.TO NVARCHAR(4000),
@.CC NVARCHAR(4000),
@.BCC NVARCHAR(4000),
@.priority NVARCHAR(10),
@.subject NVARCHAR(4000),
@.message NVARCHAR(4000),
@.type NVARCHAR(100),
@.attachments NVARCHAR(4000),
@.codepage INT,
@.rc INT
if not exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
drop table [dbo].[Rewards_CatalogProducts_CS]
end
else
begin
select @.FROM = N'sqlmail@.cyber-village.net',
@.FROM_NAME = N'ChangMian',
@.TO = N'tchangmian@.yahoo.com.sg',
@.CC = N'changmian@.cyber-village.net',
@.priority = N'High',
@.subject = N'Ei,can wor',
@.message = N'&

---

Hello SQL Server SMTP SQL
Mail

---

',
@.type = N'text/html',
@.attachments = N'',
@.codepage = 0
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.FROM,
@.TO = @.TO,
@.CC = @.CC,
@.priority = @.priority,
@.subject = @.subject,
@.message = @.message,
@.type = @.type,
@.attachments = @.attachments,
@.codepage = @.codepage,
@.server = N'mail.cyber-village.net'
select RC = @.rc
end
go
--
Hope this helps.
Dan Guzman
SQL Server MVP
"tchangmian" <tchangmian@.yahoo.com.sg> wrote in message
news:6447ee25.0410042022.52f52ac5@.posting.google.com...
>I try to run the following code in DTS, but i not work. What i want is
> if do not have duplicate data, then the table will be dropped. While
> if have duplicate data, the table will not be dropped but an email
> notification will be sent. Why when there is no duplicate data, the
> email still will be sent out?
>
> if not exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
> begin
> drop table [dbo].[Rewards_CatalogProducts_CS]
> end
>
> else
> if exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
>
> use master
> go
> declare @.FROM NVARCHAR(4000),
> @.FROM_NAME NVARCHAR(4000),
> @.TO NVARCHAR(4000),
> @.CC NVARCHAR(4000),
> @.BCC NVARCHAR(4000),
> @.priority NVARCHAR(10),
> @.subject NVARCHAR(4000),
> @.message NVARCHAR(4000),
> @.type NVARCHAR(100),
> @.attachments NVARCHAR(4000),
> @.codepage INT,
> @.rc INT
> select @.FROM = N'sqlmail@.cyber-village.net',
> @.FROM_NAME = N'ChangMian',
> @.TO = N'tchangmian@.yahoo.com.sg',
> @.CC = N'changmian@.cyber-village.net',
> @.priority = N'High',
> @.subject = N'Ei,can wor',
> @.message = N'<HTML><H1>Hello SQL Server SMTP SQL
> Mail</H1></HTML>',
> @.type = N'text/html',
> @.attachments = N'',
> @.codepage = 0
> exec @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = @.FROM,
> @.TO = @.TO,
> @.CC = @.CC,
> @.priority = @.priority,
> @.subject = @.subject,
> @.message = @.message,
> @.type = @.type,
> @.attachments = @.attachments,
> @.codepage = @.codepage,
> @.server = N'mail.cyber-village.net'
> select RC = @.rc
> go

how to send sql mail using condition statement

I try to run the following code in DTS, but i not work. What i want is
if do not have duplicate data, then the table will be dropped. While
if have duplicate data, the table will not be dropped but an email
notification will be sent. Why when there is no duplicate data, the
email still will be sent out?
if not exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
drop table [dbo].[Rewards_CatalogProducts_CS]
end
else
if exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
use master
go
declare @.FROM NVARCHAR(4000),
@.FROM_NAME NVARCHAR(4000),
@.TO NVARCHAR(4000),
@.CC NVARCHAR(4000),
@.BCC NVARCHAR(4000),
@.priority NVARCHAR(10),
@.subject NVARCHAR(4000),
@.message NVARCHAR(4000),
@.type NVARCHAR(100),
@.attachments NVARCHAR(4000),
@.codepage INT,
@.rc INT
select @.FROM = N'sqlmail@.cyber-village.net',
@.FROM_NAME = N'ChangMian',
@.TO = N'tchangmian@.yahoo.com.sg',
@.CC = N'changmian@.cyber-village.net',
@.priority = N'High',
@.subject = N'Ei,can wor',
@.message = N'<HTML><H1>Hello SQL Server SMTP SQL
Mail</H1></HTML>',
@.type = N'text/html',
@.attachments = N'',
@.codepage = 0
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.FROM,
@.TO = @.TO,
@.CC = @.CC,
@.priority = @.priority,
@.subject = @.subject,
@.message = @.message,
@.type = @.type,
@.attachments = @.attachments,
@.codepage = @.codepage,
@.server = N'mail.cyber-village.net'
select RC = @.rc
go
Your send mail script is in a different batch because of the 'GO' batch
terminator. Consider revising your script like the example below:
declare @.FROM NVARCHAR(4000),
@.FROM_NAME NVARCHAR(4000),
@.TO NVARCHAR(4000),
@.CC NVARCHAR(4000),
@.BCC NVARCHAR(4000),
@.priority NVARCHAR(10),
@.subject NVARCHAR(4000),
@.message NVARCHAR(4000),
@.type NVARCHAR(100),
@.attachments NVARCHAR(4000),
@.codepage INT,
@.rc INT
if not exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
drop table [dbo].[Rewards_CatalogProducts_CS]
end
else
begin
select @.FROM = N'sqlmail@.cyber-village.net',
@.FROM_NAME = N'ChangMian',
@.TO = N'tchangmian@.yahoo.com.sg',
@.CC = N'changmian@.cyber-village.net',
@.priority = N'High',
@.subject = N'Ei,can wor',
@.message = N'<HTML><H1>Hello SQL Server SMTP SQL
Mail</H1></HTML>',
@.type = N'text/html',
@.attachments = N'',
@.codepage = 0
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.FROM,
@.TO = @.TO,
@.CC = @.CC,
@.priority = @.priority,
@.subject = @.subject,
@.message = @.message,
@.type = @.type,
@.attachments = @.attachments,
@.codepage = @.codepage,
@.server = N'mail.cyber-village.net'
select RC = @.rc
end
go
Hope this helps.
Dan Guzman
SQL Server MVP
"tchangmian" <tchangmian@.yahoo.com.sg> wrote in message
news:6447ee25.0410042022.52f52ac5@.posting.google.c om...
>I try to run the following code in DTS, but i not work. What i want is
> if do not have duplicate data, then the table will be dropped. While
> if have duplicate data, the table will not be dropped but an email
> notification will be sent. Why when there is no duplicate data, the
> email still will be sent out?
>
> if not exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
> begin
> drop table [dbo].[Rewards_CatalogProducts_CS]
> end
>
> else
> if exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
>
> use master
> go
> declare @.FROM NVARCHAR(4000),
> @.FROM_NAME NVARCHAR(4000),
> @.TO NVARCHAR(4000),
> @.CC NVARCHAR(4000),
> @.BCC NVARCHAR(4000),
> @.priority NVARCHAR(10),
> @.subject NVARCHAR(4000),
> @.message NVARCHAR(4000),
> @.type NVARCHAR(100),
> @.attachments NVARCHAR(4000),
> @.codepage INT,
> @.rc INT
> select @.FROM = N'sqlmail@.cyber-village.net',
> @.FROM_NAME = N'ChangMian',
> @.TO = N'tchangmian@.yahoo.com.sg',
> @.CC = N'changmian@.cyber-village.net',
> @.priority = N'High',
> @.subject = N'Ei,can wor',
> @.message = N'<HTML><H1>Hello SQL Server SMTP SQL
> Mail</H1></HTML>',
> @.type = N'text/html',
> @.attachments = N'',
> @.codepage = 0
> exec @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = @.FROM,
> @.TO = @.TO,
> @.CC = @.CC,
> @.priority = @.priority,
> @.subject = @.subject,
> @.message = @.message,
> @.type = @.type,
> @.attachments = @.attachments,
> @.codepage = @.codepage,
> @.server = N'mail.cyber-village.net'
> select RC = @.rc
> go

How to send mails from sql server?

Hello,

Problem statement ::

1) I have a birthday database which stores name s and date of birth of the persons.

2) Whenever sysdate and date of birth are equal then i want the sqls erver to send a mail/alert to my mail id saying that to day is birthday of so and so person.

3) I ma working on YUKON.

4) Please let me know how to accomplish this task?

Thanks&Regards,

Sreekanth Ammisetty

ask_sreekanth@.hotmail.com

Hi Sreekanth.

You want to make use of Database Mail for this type of thing. See the following topic in SQL Server 2005 Books Online for more information, and repost any specific questions you may have after that:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/14cbf88f-d9d1-41a5-994e-532e2973ac9e.htm

|||

Hi Chad,

The link that you have provided is not working, however I ahve got solution for this to some extent. I am using "EXEC msdb.dbo.sp_send_dbmail ....." command to send the mails, But the problem I am getting is that SMTP service is not there in my desktop, I tried by installing the same, But still i could able to achieve the desired bahaviour, bcoz it is giving error related to SMTP.

The following is the error I am getting "A severe error occurred on the current command. The results, if any, should be discarded."

Please let me know the solution for this.

|||May try XPSMTP from http://www.sqldev.net/xp/xpsmtp.htm here.